Bug #88053 too few rows when query contains group_concat, left join, and no group by

• Submitted: 11 Oct 2017 9:05
• Modified: 12 Oct 2017 5:08
• Reporter: Shane Bester (Platinum Quality Contributor)
• Status: Not a Bug
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.7.19
• OS: Any
• CPU Architecture: Any

[11 Oct 2017 9:05] Shane Bester

Description:
Affects 5.0, 5.1, 5.5, 5.6, 5.7, 8.0, 9.0

The part of the testcase result that confused me was:

mysql> select a,group_concat(d)q from t left join s on c=a;
+---+------+
| a | q    |
+---+------+
| 1 | NULL |
+---+------+
1 row in set (0.00 sec)

mysql> select a,group_concat(d)q from t left join s on c=a group by a;
+---+------+
| a | q    |
+---+------+
| 1 | NULL |
| 2 | NULL |
| 3 | NULL |
| 4 | NULL |
+---+------+
4 rows in set (0.00 sec)

Full output:
https://pastebin.com/VBH4Rv8B

How to repeat:
set sql_mode='';

drop table if exists t,s;
drop view if exists t,s;
create table t (a int primary key) engine=innodb ;
insert into t values(1),(2),(3),(4);
analyze table t;

create table s(b int auto_increment primary key,c int,d int,key(c))engine=innodb;
insert into s(c) values(1),(2),(3),(4);
analyze table s;

select a,d from t left join s on c=a;
select a,group_concat(d)q from t left join s on c=a;
select a,group_concat(d)q from t left join s on c=a group by a;

set sql_mode='only_full_group_by';

select a,d from t left join s on c=a;
select a,group_concat(d)q from t left join s on c=a;
select a,group_concat(d)q from t left join s on c=a group by a;

select version();

[11 Oct 2017 9:15] MySQL Verification Team

Hello Shane,

Thank you for the report and test case.
Observed with 5.5.57, 5.6.37 and 5.7.19.

Thanks,
Umesh

[12 Oct 2017 5:08] MySQL Verification Team

*** RLYSENG  roy.lyseng 10/11/17 04:13 am ***
This is not a bug:

The reason for select a,group_concat(d)q from t left join s on c=a; to output
a single row only is that there is no GROUP BY clause, and due to the
group_concat() function, the query is implicitly aggregated into one row. The
value chosen for 'a' is an arbitrary one.