CREATE TABLE t1 (id int(11) NOT NULL PRIMARY KEY, name varchar(20), INDEX (name)) ENGINE=InnoDB; CREATE TABLE t2 (id int(11) NOT NULL PRIMARY KEY, fkey int(11), FOREIGN KEY (fkey) REFERENCES t2(id)) ENGINE=InnoDB; INSERT INTO t1 VALUES (1,'A1'),(2,'A2'),(3,'B'); INSERT INTO t2 VALUES (1,1),(2,2),(3,2),(4,3),(5,3); ANALYZE TABLE t1; ANALYZE TABLE t2; EXPLAIN SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id WHERE t1.name LIKE 'A%'; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 NULL range PRIMARY,name name 23 NULL 2 100.00 Using where; Using index 1 SIMPLE t2 NULL ref fkey fkey 5 test.t1.id 1 100.00 Using index Warnings: Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t2` join `test`.`t1` where ((`test`.`t2`.`fkey` = `test`.`t1`.`id`) and (`test`.`t1`.`name` like 'A%')) EXPLAIN SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id WHERE t1.name LIKE 'A%' OR FALSE; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 NULL index NULL fkey 5 NULL 5 100.00 Using index 1 SIMPLE t1 NULL eq_ref PRIMARY PRIMARY 4 test.t2.fkey 1 33.33 Using where Warnings: Note 1003 /* select#1 */ select count(0) AS `COUNT(*)` from `test`.`t2` left join `test`.`t1` on((`test`.`t1`.`id` = `test`.`t2`.`fkey`)) where (`test`.`t1`.`name` like 'A%') DROP TABLE t1,t2; # # BUG#58456: Assertion 0 in QUICK_INDEX_MERGE_SELECT::need_sorted_output # in opt_range.h # CREATE TABLE t1 ( col_int INT, col_int_key INT, pk INT NOT NULL, PRIMARY KEY (pk), KEY col_int_key (col_int_key) ) ENGINE=InnoDB; INSERT INTO t1 VALUES (NULL,1,1), (6,2,2), (5,3,3), (NULL,4,4); INSERT INTO t1 VALUES (1,NULL,6), (8,5,7), (NULL,8,8), (8,NULL,5); ANALYZE TABLE t1; CREATE TABLE t2 ( pk INT PRIMARY KEY ) ENGINE=InnoDB; EXPLAIN SELECT t1.pk FROM t2 LEFT JOIN t1 ON t2.pk = t1.col_int WHERE t1.col_int_key BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk IN (5) ORDER BY pk; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 NULL index PRIMARY PRIMARY 4 NULL 1 100.00 Using index 1 SIMPLE t1 NULL const PRIMARY,col_int_key PRIMARY 4 const 1 12.50 Using where Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t2` join `test`.`t1` where ((`test`.`t1`.`col_int` = `test`.`t2`.`pk`) and (((`test`.`t1`.`col_int_key` between 5 and 6) and isnull(`test`.`t1`.`pk`)) or (`test`.`t1`.`pk` = 5))) order by `test`.`t1`.`pk` SELECT t1.pk FROM t2 LEFT JOIN t1 ON t2.pk = t1.col_int WHERE t1.col_int_key BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk IN (5) ORDER BY pk; pk DROP TABLE t1,t2; # End BUG#58456