Details
Minor
Description
Test case
CREATE TABLE t1 (a VARCHAR(3), b VARCHAR(1)) ENGINE=MyISAM; INSERT INTO t1 VALUES ('bar','b'),('aaa','a'); CREATE TABLE t2 (c VARCHAR(3)) ENGINE=MyISAM; INSERT INTO t2 VALUES ('bar'),('ccc'); CREATE TABLE t3 (d VARCHAR(2), e INT) ENGINE=MyISAM; INSERT INTO t3 VALUES ('fo',1),('ba',2); CREATE TABLE t4 (f INT) ENGINE=MyISAM; INSERT INTO t4 VALUES (1),(2); CREATE TABLE t5 ( f_key VARCHAR(3) CHARACTER SET utf8, f1 INT, f2 VARCHAR(3), f3 VARCHAR(3), f4 INT, f5 VARCHAR(1024), f6 VARCHAR(3), f7 VARCHAR(1024), f8 VARCHAR(1024), key (f_key) ) ENGINE=MyISAM; INSERT INTO t5 VALUES ('foo',1,'foo','foo',1,'foo','foo',REPEAT('foo',60),'foo'), ('bar',2,'bar','bar',2,'bar','bar',REPEAT('bar',240),'bar'), ('baz',3,'baz','baz',3,'baz','baz','baz',REPEAT('baz',250)), ('qux',4,'qux','qux',4,REPEAT('qux',220),'qux','qux','qux'); set optimizer_switch = 'mrr=on'; set join_cache_level = 6; SELECT t5.f_key, t5.f1 FROM ( ( ( t1 LEFT JOIN t2 ON t2.c = t1.a ) LEFT JOIN t3 ON t1.b = t3.d ) LEFT JOIN t4 ON t3.e = t4.f ) LEFT JOIN t5 ON t5.f_key = t2.c; DROP TABLE t1, t2, t3, t4, t5;
Actual result
f_key f1 NULL NULL NULL NULL
Expected result
f_key f1 bar 2 NULL NULL
EXPLAIN
id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 1 SIMPLE t2 hash_ALL NULL #hash#$hj 7 test.t1.a 2 100.00 Using where; Using join buffer (flat, BNLH join) 1 SIMPLE t3 hash_ALL NULL #hash#$hj 6 test.t1.b 2 100.00 Using where; Using join buffer (incremental, BNLH join) 1 SIMPLE t4 hash_ALL NULL #hash#$hj 5 test.t3.e 2 100.00 Using where; Using join buffer (incremental, BNLH join) 1 SIMPLE t5 ref f_key f_key 12 func 2 100.00 Using where; Using join buffer (incremental, BKA join); Rowid-ordered scan Warnings: Note 1003 select `test`.`t5`.`f_key` AS `f_key`,`test`.`t5`.`f1` AS `f1` from `test`.`t1` left join `test`.`t2` on(((`test`.`t2`.`c` = `test`.`t1`.`a`) and (`test`.`t1`.`a` is not null))) left join `test`.`t3` on(((`test`.`t1`.`b` = `test`.`t3`.`d`) and (`test`.`t1`.`b` is not null))) left join `test`.`t4` on(((`test`.`t4`.`f` = `test`.`t3`.`e`) and (`test`.`t3`.`e` is not null))) left join `test`.`t5` on((`test`.`t5`.`f_key` = convert(`test`.`t2`.`c` using utf8))) where 1
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
The problem disappeared from 10.0 tree after the following commit:
commit fb71449b10100e9a0f887b1585000fbfab294f3c d1c4ff2b2cd54886087b6b879a6ea23f66d6582f
Author: Sergey Petrunya <psergey@askmonty.org>
Date: Sun Jan 25 16:16:25 2015 +0100
MDEV-5719: Wrong result with GROUP BY and LEFT OUTER JOIN
Merged revision 5224 from mysql-5.6 and added a test case.
..
revno: 5224
committer: Sergey Glukhov <sergey.glukhov@oracle.com>
branch nick: mysql-5.6
timestamp: Wed 2013-06-19 14:24:08 +0400
message:
Bug#16620047 INCORRECT QUERY RESULT (AFTER SERVER UPGRADE)
In the current 10.0, I get: