Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
None
-
None
-
None
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;