TEST PREPARATION: ================= create database if not exists test; drop table if exists test.table1; CREATE TABLE test.`table1` ( `T1_ID` int(11) NOT NULL AUTO_INCREMENT, `T1_VAL` varchar(20) NOT NULL, `T1_VAR` varchar(100) NOT NULL, PRIMARY KEY (`T1_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `test`.`table1` (`T1_VAL`,`T1_VAR`) VALUES ('VAL','A'),('VAL','B'),('VAL','C'); drop table if exists test.table2; CREATE TABLE test.`table2` ( `T2_ID` int(10) unsigned NOT NULL AUTO_INCREMENT, `T2_VALVAR` varchar(100) DEFAULT NULL, `T3_ID` int(10) unsigned NOT NULL, PRIMARY KEY (`T2_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC; INSERT INTO test.table2 (`T2_VALVAR`,`T3_ID`) VALUES ('VALB',1); drop table if exists test.table3; CREATE TABLE test.`table3` ( `T3_ID` int(10) unsigned NOT NULL AUTO_INCREMENT, `T3_LIB` varchar(255) NOT NULL, PRIMARY KEY (`T3_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC; insert into test.table3 (`T3_LIB`) values ('TEST'); 10.1.38: "Expected Behaviour" MariaDB [test]> status; -------------- bin/mysql Ver 15.1 Distrib 10.1.38-MariaDB, for Linux (x86_64) using readline 5.1 Connection id: 4 Current database: test Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server: MariaDB Server version: 10.1.38-MariaDB MariaDB Server Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: data/10138.sock Uptime: 1 hour 35 min 58 sec Threads: 1 Questions: 55 Slow queries: 0 Opens: 26 Flush tables: 1 Open tables: 17 Queries per second avg: 0.009 -------------- MariaDB [test]> 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) MariaDB [test]> explain extended 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'; +------+-------------+--------+--------+---------------+---------+---------+-------------------+------+----------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+--------+--------+---------------+---------+---------+-------------------+------+----------+-----------------------+ | 1 | SIMPLE | table1 | index | NULL | PRIMARY | 4 | NULL | 3 | 100.00 | Using temporary | | 1 | SIMPLE | table2 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where | | 1 | SIMPLE | table3 | eq_ref | PRIMARY | PRIMARY | 4 | test.table2.T3_ID | 1 | 100.00 | Using index; Distinct | +------+-------------+--------+--------+---------------+---------+---------+-------------------+------+----------+-----------------------+ 3 rows in set, 1 warning (0.00 sec) MariaDB [test]> show warnings\G *************************** 1. row *************************** Level: Note Code: 1003 Message: select distinct concat(`test`.`table1`.`T1_VAL`,`test`.`table1`.`T1_VAR`) AS `CC`,(concat(`test`.`table1`.`T1_VAL`,`test`.`table1`.`T1_VAR`) = 'VALB') AS `chk`,group_concat(`test`.`table2`.`T3_ID` separator ',') AS `id` from `test`.`table1` left join (`test`.`table2` join `test`.`table3`) on(((`test`.`table3`.`T3_ID` = `test`.`table2`.`T3_ID`) and (concat(`test`.`table1`.`T1_VAL`,`test`.`table1`.`T1_VAR`) = `test`.`table2`.`T2_VALVAR`) and (`test`.`table2`.`T3_ID` is not null))) where 1 group by `test`.`table1`.`T1_ID` having (`CC` = 'VALB') 1 row in set (0.00 sec) MariaDB [test]> select distinct concat(`test`.`table1`.`T1_VAL`,`test`.`table1`.`T1_VAR`) AS `CC`,(concat(`test`.`table1`.`T1_VAL`,`test`.`table1`.`T1_VAR`) = 'VALB') AS `chk`,group_concat(`test`.`table2`.`T3_ID` separator ',') AS `id` from `test`.`table1` left join (`test`.`table2` join `test`.`table3`) on(((`test`.`table3`.`T3_ID` = `test`.`table2`.`T3_ID`) and (concat(`test`.`table1`.`T1_VAL`,`test`.`table1`.`T1_VAR`) = `test`.`table2`.`T2_VALVAR`) and (`test`.`table2`.`T3_ID` is not null))) where 1 group by `test`.`table1`.`T1_ID` having (`CC` = 'VALB') -> ; +------+-----+------+ | CC | chk | id | +------+-----+------+ | VALB | 1 | 1 | +------+-----+------+ 1 row in set (0.00 sec) 10.2.26: "Something is Wrong" MariaDB [test]> status; -------------- bin/mysql Ver 15.1 Distrib 10.2.26-MariaDB, for Linux (x86_64) using readline 5.1 Connection id: 10 Current database: test Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server: MariaDB Server version: 10.2.26-MariaDB-log MariaDB Server Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: data/10226.sock Uptime: 1 hour 40 min 44 sec Threads: 8 Questions: 69 Slow queries: 0 Opens: 34 Flush tables: 1 Open tables: 28 Queries per second avg: 0.011 -------------- MariaDB [test]> 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'; Empty set (0.01 sec) -- No Results -- MariaDB [test]> explain extended 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'; +------+-------------+--------+--------+---------------+---------+---------+-------------------+------+----------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+--------+--------+---------------+---------+---------+-------------------+------+----------+---------------------------------+ | 1 | SIMPLE | table1 | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using temporary; Using filesort | | 1 | SIMPLE | table2 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where | | 1 | SIMPLE | table3 | eq_ref | PRIMARY | PRIMARY | 4 | test.table2.T3_ID | 1 | 100.00 | Using index | +------+-------------+--------+--------+---------------+---------+---------+-------------------+------+----------+---------------------------------+ 3 rows in set, 1 warning (0.00 sec) MariaDB [test]> show warnings\G *************************** 1. row *************************** Level: Note Code: 1003 Message: select distinct concat(`test`.`table1`.`T1_VAL`,`test`.`table1`.`T1_VAR`) AS `CC`,concat(`test`.`table1`.`T1_VAL`,`test`.`table1`.`T1_VAR`) = 'VALB' AS `chk`,group_concat(`test`.`table2`.`T3_ID` separator ',') AS `id` from `test`.`table1` left join (`test`.`table2` join `test`.`table3`) on(`test`.`table3`.`T3_ID` = `test`.`table2`.`T3_ID` and concat(`test`.`table1`.`T1_VAL`,`test`.`table1`.`T1_VAR`) = `test`.`table2`.`T2_VALVAR` and `test`.`table2`.`T3_ID` is not null) where 1 group by `test`.`table1`.`T1_ID` having 1 1 row in set (0.00 sec) -- Running the Rewritten Query -- MariaDB [test]> select distinct concat(`test`.`table1`.`T1_VAL`,`test`.`table1`.`T1_VAR`) AS `CC`,concat(`test`.`table1`.`T1_VAL`,`test`.`table1`.`T1_VAR`) = 'VALB' AS `chk`,group_concat(`test`.`table2`.`T3_ID` separator ',') AS `id` from `test`.`table1` left join (`test`.`table2` join `test`.`table3`) on(`test`.`table3`.`T3_ID` = `test`.`table2`.`T3_ID` and concat(`test`.`table1`.`T1_VAL`,`test`.`table1`.`T1_VAR`) = `test`.`table2`.`T2_VALVAR` and `test`.`table2`.`T3_ID` is not null) where 1 group by `test`.`table1`.`T1_ID` having 1; +------+------+------+ | CC | chk | id | +------+------+------+ | VALA | 0 | NULL | | VALB | 1 | 1 | | VALC | 0 | NULL | +------+------+------+ -- Wrong Results, 1 row expected, 3 returned -- 10.3.12: "Wrong again" MariaDB [test]> status; -------------- bin/mysql Ver 15.1 Distrib 10.2.26-MariaDB, for Linux (x86_64) using readline 5.1 Connection id: 12 Current database: test Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server: MariaDB Server version: 10.3.12-MariaDB-log MariaDB Server Protocol version: 10 Connection: Localhost via UNIX socket Insert id: 1 Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: data/10312.sock Uptime: 1 day 55 min 48 sec Threads: 8 Questions: 726 Slow queries: 0 Opens: 132 Flush tables: 1 Open tables: 122 Queries per second avg: 0.008 -------------- MariaDB [test]> 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'; Empty set (0.00 sec) MariaDB [test]> explain extended 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'; +------+-------------+--------+--------+---------------+---------+---------+-------------------+------+----------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+--------+--------+---------------+---------+---------+-------------------+------+----------+---------------------------------+ | 1 | SIMPLE | table1 | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using temporary; Using filesort | | 1 | SIMPLE | table2 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where | | 1 | SIMPLE | table3 | eq_ref | PRIMARY | PRIMARY | 4 | test.table2.T3_ID | 1 | 100.00 | Using index | +------+-------------+--------+--------+---------------+---------+---------+-------------------+------+----------+---------------------------------+ 3 rows in set, 1 warning (0.00 sec) MariaDB [test]> show warnings; +-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1003 | select distinct concat(`test`.`table1`.`T1_VAL`,`test`.`table1`.`T1_VAR`) AS `CC`,concat(`test`.`table1`.`T1_VAL`,`test`.`table1`.`T1_VAR`) = 'VALB' AS `chk`,group_concat(`test`.`table2`.`T3_ID` separator ',') AS `id` from `test`.`table1` left join (`test`.`table2` join `test`.`table3`) on(`test`.`table3`.`T3_ID` = `test`.`table2`.`T3_ID` and concat(`test`.`table1`.`T1_VAL`,`test`.`table1`.`T1_VAR`) = `test`.`table2`.`T2_VALVAR` and `test`.`table2`.`T3_ID` is not null) where 1 group by `test`.`table1`.`T1_ID` having 1 | +-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) MariaDB [test]> select distinct concat(`test`.`table1`.`T1_VAL`,`test`.`table1`.`T1_VAR`) AS `CC`,concat(`test`.`table1`.`T1_VAL`,`test`.`table1`.`T1_VAR`) = 'VALB' AS `chk`,group_concat(`test`.`table2`.`T3_ID` separator ',') AS `id` from `test`.`table1` left join (`test`.`table2` join `test`.`table3`) on(`test`.`table3`.`T3_ID` = `test`.`table2`.`T3_ID` and concat(`test`.`table1`.`T1_VAL`,`test`.`table1`.`T1_VAR`) = `test`.`table2`.`T2_VALVAR` and `test`.`table2`.`T3_ID` is not null) where 1 group by `test`.`table1`.`T1_ID` having 1 ; +------+------+------+ | CC | chk | id | +------+------+------+ | VALA | 0 | NULL | | VALB | 1 | 1 | | VALC | 0 | NULL | +------+------+------+ 3 rows in set (0.00 sec)