Getting one row insted of multiple rows when using query statment inside in() at where clause

Question

There are two tables fruits and fruits_seasons in the schema and I wanted to get all the monsoon fruits. While trying so I noticed a weird response.

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=77d52b2736a04a5adf4ffe80881cd4ab

Monsoon months

select group_concat(fruit_id) from fruits_seasons where monsoon = 1;

group_concat(fruit_id)
2,8,9,11,13,15

The query I used

SELECT 
        f.name AS name, f.price AS price
    FROM
        fruits f 
        where f.id in (select group_concat(fruit_id) from fruits_seasons where monsoon = 1);

My expected result

name	price
lemon	15.00
carrot	35.00
papaya	18.00
custard apple	15.00
mango	25.00
apple	25.00

The result I got

name	price
lemon	15.00

What is the reason am I getting a single row instead of multiple?

Answer 1

GROUP_CONCAT() returns a string which is a comma separated list of values, so your code is equivalent to:

WHERE f.id IN ('2,8,9,11,13,15')

So, you compare the id, which is an integer to the only item in the list which is a string and in this case MySql tries to convert the string to an integer.
The result of the conversion is 2 (the starting part of the string which can be successfully converted to an integer) and finally your code is equivalent to:

WHERE f.id IN (2)

If you have to use GROUP_CONCAT() for your requirement, instead of the operator IN use the function FIND_IN_SET():

SELECT f.name, f.price
FROM fruits f 
WHERE FIND_IN_SET(f.id, (SELECT GROUP_CONCAT(fruit_id) FROM fruits_seasons WHERE monsoon = 1));

But it's easier this way:

SELECT f.name, f.price
FROM fruits f 
WHERE f.id IN (SELECT fruit_id FROM fruits_seasons WHERE monsoon = 1);

See the demo.