1

Hello,

so I was trying few relations between tables and I noted an unexpected behaviour when using group_concat(). Let say I have three tables: fruits, fruitstock and fruitprices. These are the structures:

create table fruits (
    id tinyint unsigned not null auto_increment primary key,
    name varchar(50) not null,
    quality varchar(50) not null
    ) engine = myisam default charset = utf8;

create table fruitstock (
    id tinyint unsigned auto_increment primary key not null,
    fruit_id tinyint unsigned not null,
    quantity tinyint unsigned not null default 0
    ) engine = myisam default charset = utf8;

create table fruitprices (
    id tinyint unsigned not null auto_increment primary key,
    fruit_id tinyint unsigned not null,
    price int unsigned not null default 0
    ) engine = myisam default charset = utf8;

And this is the data used to populate the tables:

insert into fruits (name, quality) values('apple', 'granny smith'), ('apple', 'fuji'), ('apple', 'red delicious'), ('apple', 'pink lady'), ('apple', 'jonagold'), ('apricot', 'sungiant'), ('avocado', 'hass'), ('avocado', 'gem'), ('cherry', 'autumnalis'), ('cherry', 'kanzan'), ('cheery', 'pandora');

insert into fruitstock (fruit_id, quantity) values(1, 10), (2, 23), (3, 7), (4, 100), (5, 50), (6, 0), (7, 20), (8, 1), (9, 15), (10, 21);

insert into fruitprices (fruit_id, price) values(1, 100), (2, 98), (3, 110), (5, 20), (8, 120), (10, 140), (11, 200);

Where fruit_id is used as foreing key in the last two tables. Now if I try a query to show the price of the apples in stock I run:

select f.name, group_concat(quality) quality, sum(fs.quantity) quantity, sum(fp.price*fs.quantity) price from (select * from fruits where name = 'apple') as f, fruitstock as fs, fruitprices as fp where f.id = fs.fruit_id and f.id = fp.fruit_id and f.id in(1,2,3,4,5)\G

And I get:

*************************** 1. row ***************************
name: apple
quality: granny smith,fuji,red delicious,jonagold,granny smith,fuji,red delicious,jonagold
quantity: 180
price: 10048

As expected. But if I change the name to an non-existing value, as example apples or oranges, I get one row with all values null:

> select f.name, group_concat(quality) quality, sum(fs.quantity) quantity, sum(fp.price*fs.quantity) price from (select * from fruits where name = 'apples') as f, fruitstock as fs, fruitprices as fp where f.id = fs.fruit_id and f.id = fp.fruit_id and f.id in(1,2,3,4,5);

+------+---------+----------+-------+
| name | quality | quantity | price |
+------+---------+----------+-------+
| NULL | NULL    |     NULL |  NULL |
+------+---------+----------+-------+
1 row in set (0.00 sec)

The explain for this query returns:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: fp
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 7
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: fruits
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 1
          ref: myrias_examples.fp.fruit_id
         rows: 1
        Extra: Using where
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: fs
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 20
        Extra: Using where; Using join buffer (flat, BNL join)
3 rows in set (0.00 sec)

While, if I set a wrong id in the last statement of the WHERE condition, i.e. f.id in(1,2,...) I get the first field correct and the others null:

> select f.name, group_concat(quality) quality, sum(fs.quantity) quantity, sum(fp.price*fs.quantity) price from (select * from fruits where name = 'apple') as f, fruitstock as fs, fruitprices as fp where f.id = fs.fruit_id and f.id = fp.fruit_id and f.id in(4);
+-------+---------+----------+-------+
| name  | quality | quantity | price |
+-------+---------+----------+-------+
| apple | NULL    |     NULL |  NULL |
+-------+---------+----------+-------+
1 row in set (0.00 sec)

Last example with a wrong f.id and without the subquery:

> select f.name, group_concat(distinct f.quality) quality, sum(quantity) from fruits as f, fruitstock as fs where f.id = fs.fruit_id and f.id = 30;
+------+---------+---------------+
| name | quality | sum(quantity) |
+------+---------+---------------+
| NULL | NULL    |          NULL |
+------+---------+---------------+
1 row in set (0.00 sec)

I get Impossible WHERE noticed after reading const tables as expected but I still get one row:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Impossible WHERE noticed after reading const tables
1 row in set (0.00 sec)

Since in these queries the statements are not fully satisfied (or are completely false as in the last one) I expect to get Impossible WHERE noticed after reading const tables and an Empty set (0.00 sec) but it does not happen. Why?

Here's a live example: http://sqlfiddle.com/#!2/53ed8f/5

I'm using MariaDB 10.0.4, which should be equivalent to MySQL 5.6. Obviously these tables are just examples to reproduce the problem and I'm using the subquery because in MariaDB I can cache them.

Thank you for your attention!

1
Contributor
1
Reply
33
Views
4 Years
Discussion Span
Last Post by cereal
1

Ok, checking the source (at line ~2804 of sql/item_sum.cc in MySQL 5.5) now I understand:

/*****************************************************************************
GROUP_CONCAT function

SQL SYNTAX:
GROUP_CONCAT([DISTINCT] expr,... [ORDER BY col [ASC|DESC],...]
[SEPARATOR str_const])

concat of values from "group by" operation

BUGS
Blobs doesn't work with DISTINCT or ORDER BY
*****************************************************************************/

The group by is mandatory when using group_concat() because the latter is a function of the former. Looking at the manual with fresh eyes, I realize, it was pretty obvious. (Point and laugh!) 5020680b456e96307aa312265db3c372

In fact I start to get the empty results sets as expected, however I still don't get the Impossible WHERE..., but this is unrelated. I leave an example for the log:

select f.name, group_concat(quality) quality, sum(fs.quantity) quantity, sum(fp.price*fs.quantity) price from (select * from fruits where name = 'apple') as f, fruitstock as fs, fruitprices as fp where f.id = fs.fruit_id and f.id = fp.fruit_id and f.id in(4) group by name;
Empty set (0.00 sec)

Live example: http://sqlfiddle.com/#!2/53ed8f/7

Ref: http://dev.mysql.com/doc/refman/5.6/en/group-by-functions.html#function_group-concat

Thanks for your time! ;D

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.