1

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?

forpas
  • 160,666
  • 10
  • 38
  • 76
Suvel Mano
  • 23
  • 4

1 Answers1

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.

forpas
  • 160,666
  • 10
  • 38
  • 76
  • Thanks you for explaining it so well. I came to know about type casting, find_in_set function and very importantly that I can straight forwardly write query in IN() in MySQL. – Suvel Mano Sep 26 '21 at 03:20