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!
