As you can see in the attached test case a query behaves differently in 10.1 vs 10.2 and 10.3
10.1
select distinct concat(table1.T1_VAL,table1.T1_VAR) as CC, concat(table1.T1_VAL,table1.T1_VAR) = 'VALB' as chk, group_concat(table2.T3_ID) as id from test.table1 LEFT JOIN (test.table2,test.table3) on concat(T1_VAL,T1_VAR) = table2.T2_VALVAR and table2.T3_ID = table3.T3_ID group by T1_ID having CC = 'VALB';
+------+-----+------+
| CC | chk | id |
+------+-----+------+
| VALB | 1 | 1 |
+------+-----+------+
1 row in set (0.00 sec)
10.2 & 10.3
select distinct concat(table1.T1_VAL,table1.T1_VAR) as CC, concat(table1.T1_VAL,table1.T1_VAR) = 'VALB' as chk, group_concat(table2.T3_ID) as id from test.table1 LEFT JOIN (test.table2,test.table3) on concat(T1_VAL,T1_VAR) = table2.T2_VALVAR and table2.T3_ID = table3.T3_ID group by T1_ID having CC = 'VALB';
Another observation: the following variation of SQL causes server crash:
select concat(t1.T1_VAL,t1.T1_VAR) as CC
from t1 LEFTJOIN (t2, t3) on
concat(T1_VAL,T1_VAR) = t2.T2_VALVAR and t2.T3_ID = t3.T3_ID;
Oleg Smirnov
added a comment - Another observation: the following variation of SQL causes server crash:
select concat(t1.T1_VAL,t1.T1_VAR) as CC
from t1 LEFT JOIN (t2, t3) on
concat(T1_VAL,T1_VAR) = t2.T2_VALVAR and t2.T3_ID = t3.T3_ID;
oleg.smirnov, following the discussion on the optimizer call: as the crash is a separate issue, please file a separate self-contained MDEV for it, mention its number here, assign it to yourself.
Sergei Petrunia
added a comment - oleg.smirnov , following the discussion on the optimizer call: as the crash is a separate issue, please file a separate self-contained MDEV for it, mention its number here, assign it to yourself.
Some other observations so far.
The bug reveals itself only when both conditions are satisfied:
group_concat(t2.T3_ID) is present in the field list
t2.T3_ID = t3.T3_ID equality is present
selectdistinct concat(t1.T1_VAL,t1.T1_VAR) as CC, group_concat(t2.T3_ID) as id
from t1 LEFTJOIN (t2,t3) on concat(T1_VAL,T1_VAR) = t2.T2_VALVAR and t2.T3_ID = t3.T3_ID
groupby T1_ID having CC='valb';
After removing any of them:
selectdistinct concat(t1.T1_VAL,t1.T1_VAR) as CC
from t1 LEFTJOIN (t2,t3) on concat(T1_VAL,T1_VAR) = t2.T2_VALVAR and t2.T3_ID = t3.T3_ID
groupby T1_ID having CC='valb';
selectdistinct concat(t1.T1_VAL,t1.T1_VAR) as CC, group_concat(t2.T3_ID) as id
from t1 LEFTJOIN (t2,t3) on concat(T1_VAL,T1_VAR) = t2.T2_VALVAR
groupby T1_ID having CC='valb';
the result of SQL is correct. Removing t2.T3_ID = t3.T3_ID equality leads to another execution plan (t3 accessed with "scan" against "eq_ref").
Also the result is correct after modifying the HAVING clause to "HAVING CC='valc'":
selectdistinct concat(t1.T1_VAL,t1.T1_VAR) as CC, group_concat(t2.T3_ID) as id
from t1 LEFTJOIN (t2,t3) on concat(T1_VAL,T1_VAR) = t2.T2_VALVAR and t2.T3_ID = t3.T3_ID
groupby T1_ID having CC='valc';
Oleg Smirnov
added a comment - - edited Some other observations so far.
The bug reveals itself only when both conditions are satisfied:
group_concat(t2.T3_ID) is present in the field list
t2.T3_ID = t3.T3_ID equality is present
select distinct concat(t1.T1_VAL,t1.T1_VAR) as CC, group_concat(t2.T3_ID) as id
from t1 LEFT JOIN (t2,t3) on concat(T1_VAL,T1_VAR) = t2.T2_VALVAR and t2.T3_ID = t3.T3_ID
group by T1_ID having CC= 'valb' ;
After removing any of them:
select distinct concat(t1.T1_VAL,t1.T1_VAR) as CC
from t1 LEFT JOIN (t2,t3) on concat(T1_VAL,T1_VAR) = t2.T2_VALVAR and t2.T3_ID = t3.T3_ID
group by T1_ID having CC= 'valb' ;
select distinct concat(t1.T1_VAL,t1.T1_VAR) as CC, group_concat(t2.T3_ID) as id
from t1 LEFT JOIN (t2,t3) on concat(T1_VAL,T1_VAR) = t2.T2_VALVAR
group by T1_ID having CC= 'valb' ;
the result of SQL is correct. Removing t2.T3_ID = t3.T3_ID equality leads to another execution plan (t3 accessed with "scan" against "eq_ref").
Also the result is correct after modifying the HAVING clause to "HAVING CC='valc'":
select distinct concat(t1.T1_VAL,t1.T1_VAR) as CC, group_concat(t2.T3_ID) as id
from t1 LEFT JOIN (t2,t3) on concat(T1_VAL,T1_VAR) = t2.T2_VALVAR and t2.T3_ID = t3.T3_ID
group by T1_ID having CC= 'valc' ;
Bug
Critical
Another observation: the following variation of SQL causes server crash: