Less rows returned with 5-way join, join_cache_level=7, outer join and BNL

[Philip Stoev]

The following 5-table query

SELECT COUNT(table2.pk) FROM I AS table1 LEFT JOIN O AS table2 JOIN O AS table4 ON table2.f2 = table4.f1 ON table1.pk = table2.f4 LEFT JOIN I AS table6 ON table1.f4 = table6.f4 LEFT JOIN I AS table7 ON table4 .f1 = table7 .f2 WHERE table7.f4 IS NULL;

returns less rows then when executed with join_cache_level = 0 . Maria-5.3 crashes with the set_match_flag_if_none assertion.

Test case:

SET SESSION join_cache_level = 7;
SET SESSION optimizer_switch = 'outer_join_with_cache=on';

DROP TABLE IF EXISTS O;
CREATE TABLE O (
f1 int,
pk int,
f2 int,
f4 int) ;
INSERT IGNORE INTO O VALUES ('0','9','0','2'),('0','1','0','1'),('0','1','0','1'),('0','1','0','7'),('0','1','0','6'),('0','2','0',NULL);

DROP TABLE IF EXISTS I;
CREATE TABLE I (
f2 int,
f4 int,
pk int,
KEY (f4)) ;
INSERT IGNORE INTO I VALUES ('0',NULL,'1'),('0',NULL,'2'),('0','1','6'),('0','1','7'),('0','0','8');

SELECT COUNT(table2.pk) FROM I AS table1 LEFT JOIN O AS table2 JOIN O AS table4 ON table2.f2 = table4.f1 ON table1.pk = table2.f4 LEFT JOIN I AS table6 ON table1.f4 = table6.f4 LEFT JOIN I AS table7 ON table4 .f1 = table7 .f2 WHERE table7.f4 IS NULL;
EXPLAIN SELECT COUNT(table2.pk) FROM I AS table1 LEFT JOIN O AS table2 JOIN O AS table4 ON table2.f2 = table4.f1 ON table1.pk = table2.f4 LEFT JOIN I AS table6 ON table1.f4 = table6.f4 LEFT JOIN I AS table7 ON table4 .f1 = table7 .f2 WHERE table7.f4 IS NULL;
SET SESSION join_cache_level = 0;
SELECT COUNT(table2.pk) FROM I AS table1 LEFT JOIN O AS table2 JOIN O AS table4 ON table2.f2 = table4.f1 ON table1.pk = table2.f4 LEFT JOIN I AS table6 ON table1.f4 = table6.f4 LEFT JOIN I AS table7 ON table4 .f1 = table7 .f2 WHERE table7.f4 IS NULL;
EXPLAIN SELECT COUNT(table2.pk) FROM I AS table1 LEFT JOIN O AS table2 JOIN O AS table4 ON table2.f2 = table4.f1 ON table1.pk = table2.f4 LEFT JOIN I AS table6 ON table1.f4 = table6.f4 LEFT JOIN I AS table7 ON table4 .f1 = table7 .f2 WHERE table7.f4 IS NULL;

[Igor Babaev (igorb-seattle)]

A simpler test case:
CREATE TABLE t1 (pk int, a1 int) ;
INSERT IGNORE INTO t1 VALUES (2,NULL), (8,0);

CREATE TABLE t2 (pk int, a2 int, c2 int, d2 int) ;
INSERT IGNORE INTO t2 VALUES (9,0,0,2), (1,0,0,7);

CREATE TABLE t3 (pk int, a3 int, c3 int, d3 int) ;
INSERT IGNORE INTO t3 VALUES (9,0,0,2), (1,0,0,7);

CREATE TABLE t4 (pk int, a4 int, INDEX idx(a4)) ;
INSERT IGNORE INTO t4 VALUES (2,NULL), (8,0);

CREATE TABLE t5 (pk int, a5 int) ;
INSERT IGNORE INTO t5 VALUES (2,0), (8,0);

SET SESSION optimizer_switch = 'outer_join_with_cache=on';

SET SESSION join_cache_level = 0;

EXPLAIN EXTENDED
SELECT *
  FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.c2 = t3.a3) ON t1.pk = t2.d2)
        LEFT JOIN t4 ON t1.a1 = t4.a4) LEFT JOIN t5 ON t3.a3 = t5.a5;
SELECT *
  FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.c2 = t3.a3) ON t1.pk = t2.d2)
        LEFT JOIN t4 ON t1.a1 = t4.a4) LEFT JOIN t5 ON t3.a3 = t5.a5;

SET SESSION join_cache_level = 2;

EXPLAIN EXTENDED
SELECT *
  FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.c2 = t3.a3) ON t1.pk = t2.d2)
        LEFT JOIN t4 ON t1.a1 = t4.a4) LEFT JOIN t5 ON t3.a3 = t5.a5;
SELECT *
  FROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.c2 = t3.a3) ON t1.pk = t2.d2)
        LEFT JOIN t4 ON t1.a1 = t4.a4) LEFT JOIN t5 ON t3.a3 = t5.a5;

With this test case and join_cache_level set to 1 we have a crash:

Thread 1 (Thread 22121):
#0 0x00007fb34c8685ec in pthread_kill () from /lib64/libpthread.so.0
#1 0x0000000000bcca3f in my_write_core (sig=6) at stacktrace.c:331
#2 0x00000000006af8bb in handle_segfault (sig=6) at mysqld.cc:2768
#3 <signal handler called>
#4 0x00007fb34b54b9e5 in raise () from /lib64/libc.so.6
#5 0x00007fb34b54cee6 in abort () from /lib64/libc.so.6
#6 0x00007fb34b544235 in __assert_fail () from /lib64/libc.so.6
#7 0x000000000070e76f in JOIN_CACHE::set_match_flag_if_none (this=0x1e38ae8, first_inner=0x1e35c90, rec_ptr=0x1e1dc19 "\375", <incomplete sequence \341>) at sql_join_cache.cc:2219
#8 0x0000000000712895 in JOIN_CACHE::check_match (this=0x1e38ae8, rec_ptr=0x1e1dc19 "\375", <incomplete sequence \341>) at sql_join_cache.cc:2322
#9 0x000000000070e7ef in JOIN_CACHE::generate_full_extensions (this=0x1e38ae8, rec_ptr=0x1e1dc19 "\375", <incomplete sequence \341>) at sql_join_cache.cc:2257
#10 0x000000000070e62b in JOIN_CACHE::join_matching_records (this=0x1e38ae8, skip_last=false) at sql_join_cache.cc:2159
#11 0x000000000070e114 in JOIN_CACHE::join_records (this=0x1e38ae8, skip_last=false) at sql_join_cache.cc:1967
#12 0x0000000000759ef1 in sub_select_cache (join=0x1e2dbd0, join_tab=0x1e35f70, end_of_records=true) at sql_select.cc:13235
#13 0x000000000075a109 in sub_select (join=0x1e2dbd0, join_tab=0x1e35c90, end_of_records=true) at sql_select.cc:13397
#14 0x0000000000759f17 in sub_select_cache (join=0x1e2dbd0, join_tab=0x1e35c90, end_of_records=true) at sql_select.cc:13237
#15 0x000000000075a109 in sub_select (join=0x1e2dbd0, join_tab=0x1e359b0, end_of_records=true) at sql_select.cc:13397
#16 0x00000000007596a0 in do_select (join=0x1e2dbd0, fields=0x7fb33c08fc48, table=0x0, procedure=0x0) at sql_select.cc:12993
#17 0x000000000073f42a in JOIN::exec (this=0x1e2dbd0) at sql_select.cc:2392
#18 0x000000000073fbe6 in mysql_select (...