Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
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;
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Launchpad bug id: 675095
Re: Less rows returned with 5-way join, join_cache_level=7, outer join and BNL
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 (thd=0x7fb33c08d2a8, rref_pointer_array=0x7fb33c08fd28, tables=0x1af0458, wild_num=1, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0,
having=0x0, proc_param=0x0, select_options=2147764736, result=0x1e2ba28, unit=0x7fb33c08f6c8, select_lex=0x7fb33c08fb40) at sql_select.cc:2602
#19 0x0000000000737a56 in handle_select (thd=0x7fb33c08d2a8, lex=0x7fb33c08f628, result=0x1e2ba28, setup_tables_done_option=0) at sql_select.cc:286
#20 0x00000000006cb7ad in execute_sqlcom_select (thd=0x7fb33c08d2a8, all_tables=0x1af0458) at sql_parse.cc:5102
#21 0x00000000006c2b5f in mysql_execute_command (thd=0x7fb33c08d2a8) at sql_parse.cc:2281
#22 0x00000000006ce0de in mysql_parse (thd=0x7fb33c08d2a8,
rawbuf=0x1aef9b0 "SELECT *\nFROM ((t1 LEFT JOIN (t2 JOIN t3 ON t2.c2 = t3.a3) ON t1.pk = t2.d2)\nLEFT JOIN t4 ON t1.a1 = t4.a4) LEFT JOIN t5 ON t3.a3 = t5.a5", length=137,
found_semicolon=0x7fb342a62ca8) at sql_parse.cc:6109
#23 0x00000000006c03f8 in dispatch_command (command=COM_QUERY, thd=0x7fb33c08d2a8, packet=0x7fb33c16c1f9 "", packet_length=137) at sql_parse.cc:1209
#24 0x00000000006bf782 in do_command (thd=0x7fb33c08d2a8) at sql_parse.cc:902
#25 0x00000000006bc7e8 in handle_one_connection (arg=0x7fb33c08d2a8) at sql_connect.cc:1154
#26 0x00007fb34c862a4f in start_thread () from /lib64/libpthread.so.0
#27 0x00007fb34b5e982d in clone () from /lib64/libc.so.6
#28 0x0000000000000000 in ?? ()