Skip to content

Commit e749215

Browse files
author
Sreeharsha Ramanavarapu
committed
Bug #22305361: QUERY WITH MATERIALIZED TABLE RETURNS
 INCORRECT RESULTS IN 5.6 Issue ----- This issue occurs with a query of the form SQ1 IN (SQ2 IN (SQ3)). To handle the first IN, a semijoin is used and the subquery is materialized. The second IN is converted to EXISTS optimization. During IN-TO-EXISTS transform for SQ3, Item_in_subselect's used_tables_cache is set to 3 instead of 2. This is because <left expr> is a direct_ref to concat(a,''). It visits Item_ref::resolved_used_tables. The used_tables_cache of concat(a,'') is set to 1. This difference allows for the condition to be eliminated by make_cond_for_table_from_pred, since the table, represented by used_tables_cache, is not referenced. SOLUTION: --------- This is the same problem fixed in Bug#21139722. The issue is solved by creating a new used_tables_for_level for Item_field class. This will return the table number if the column is resolved in the current query block. If it is resolved in an outer query block, the appropriate value is returned. Since constant tables are not handled in Item_field::used_tables_for_level, Bug#21139402 is also backported.
1 parent c980c29 commit e749215

29 files changed

Lines changed: 2557 additions & 35 deletions

‎mysql-test/include/subquery_sj.inc‎

Lines changed: 77 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -6603,3 +6603,80 @@ set @@optimizer_switch=@old_opt_switch;
66036603
# New tests go here.
66046604

66056605
--echo # End of 5.6 tests
6606+
6607+
--echo # Bug#21139722: Assertion failed: !(used_tables() & ((table_map) 1) ...
6608+
6609+
CREATE TABLE t1(a INTEGER) engine=innodb;
6610+
CREATE TABLE t2(b INTEGER) engine=innodb;
6611+
6612+
let $query=
6613+
SELECT 1
6614+
FROM (SELECT 1 IN (SELECT 1
6615+
FROM t1
6616+
WHERE (SELECT 1 FROM t2 HAVING b) NOT IN (SELECT 1 FROM t2)
6617+
)
6618+
FROM t2
6619+
) AS z;
6620+
6621+
eval explain $query;
6622+
eval $query;
6623+
6624+
DROP TABLE t1, t2;
6625+
6626+
# A test case containing two outer references from different query blocks
6627+
# Debugging is needed to inspect correct transformation, unfortunately.
6628+
6629+
CREATE TABLE t1(a INTEGER) engine=innodb;
6630+
6631+
let $query=
6632+
SELECT (SELECT a
6633+
FROM t1 AS t2
6634+
WHERE a IN (SELECT t1.a+t2.a
6635+
FROM t1 AS t3))
6636+
FROM t1 AS t1;
6637+
6638+
eval explain $query;
6639+
eval $query;
6640+
6641+
DROP TABLE t1;
6642+
6643+
--echo # End of test for Bug#21139722
6644+
6645+
--echo #
6646+
--echo # Bug#21139402 ASSERTION FAILED: LENGTH > 0 && KEYPARTS != 0, CRASH IN JOIN::OPTIMIZE_KEYUSE
6647+
--echo #
6648+
6649+
CREATE TABLE t1 (a INT, b INT, PRIMARY KEY(a));
6650+
CREATE TABLE t2 (c INT PRIMARY KEY);
6651+
let $query=
6652+
SELECT 1
6653+
FROM t1
6654+
WHERE 1 IN (SELECT (c IS NULL) IN (SELECT a
6655+
FROM t1
6656+
WHERE b)
6657+
FROM t2);
6658+
eval EXPLAIN $query;
6659+
eval $query;
6660+
DROP TABLE t1,t2;
6661+
6662+
--echo #
6663+
--echo # Bug #22305361: QUERY WITH MATERIALIZED TABLE RETURNS INCORRECT
6664+
--echo # RESULTS IN 5.6
6665+
--echo #
6666+
6667+
CREATE TABLE t(a INT,b INT);
6668+
INSERT INTO t VALUES (1,0),(1,0),(1,0),(1,0),(1,1);
6669+
6670+
let query=
6671+
SELECT *
6672+
FROM t AS t1
6673+
WHERE t1.a IN (SELECT t2.a
6674+
FROM t AS t2
6675+
WHERE CONCAT(t2.a,'') NOT IN (SELECT t3.a
6676+
FROM t AS t3
6677+
WHERE t3.b=1));
6678+
6679+
eval EXPLAIN extended $query;
6680+
eval $query;
6681+
6682+
DROP TABLE t;

‎mysql-test/r/subquery_sj_all.result‎

Lines changed: 97 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -10683,4 +10683,101 @@ DROP TABLE t1,t2;
1068310683
# End of test for Bug#21184091
1068410684
set @@optimizer_switch=@old_opt_switch;
1068510685
# End of 5.6 tests
10686+
# Bug#21139722: Assertion failed: !(used_tables() & ((table_map) 1) ...
10687+
CREATE TABLE t1(a INTEGER) engine=innodb;
10688+
CREATE TABLE t2(b INTEGER) engine=innodb;
10689+
explain SELECT 1
10690+
FROM (SELECT 1 IN (SELECT 1
10691+
FROM t1
10692+
WHERE (SELECT 1 FROM t2 HAVING b) NOT IN (SELECT 1 FROM t2)
10693+
)
10694+
FROM t2
10695+
) AS z;
10696+
id select_type table type possible_keys key key_len ref rows Extra
10697+
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 NULL
10698+
2 DERIVED t2 ALL NULL NULL NULL NULL 1 NULL
10699+
3 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 1 Using where
10700+
5 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 1 Using where
10701+
4 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 1 NULL
10702+
SELECT 1
10703+
FROM (SELECT 1 IN (SELECT 1
10704+
FROM t1
10705+
WHERE (SELECT 1 FROM t2 HAVING b) NOT IN (SELECT 1 FROM t2)
10706+
)
10707+
FROM t2
10708+
) AS z;
10709+
1
10710+
DROP TABLE t1, t2;
10711+
CREATE TABLE t1(a INTEGER) engine=innodb;
10712+
explain SELECT (SELECT a
10713+
FROM t1 AS t2
10714+
WHERE a IN (SELECT t1.a+t2.a
10715+
FROM t1 AS t3))
10716+
FROM t1 AS t1;
10717+
id select_type table type possible_keys key key_len ref rows Extra
10718+
1 PRIMARY t1 ALL NULL NULL NULL NULL 1 NULL
10719+
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 1 Using where
10720+
2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 1 FirstMatch(t2); Using join buffer (Block Nested Loop)
10721+
SELECT (SELECT a
10722+
FROM t1 AS t2
10723+
WHERE a IN (SELECT t1.a+t2.a
10724+
FROM t1 AS t3))
10725+
FROM t1 AS t1;
10726+
(SELECT a
10727+
FROM t1 AS t2
10728+
WHERE a IN (SELECT t1.a+t2.a
10729+
FROM t1 AS t3))
10730+
DROP TABLE t1;
10731+
# End of test for Bug#21139722
10732+
#
10733+
# Bug#21139402 ASSERTION FAILED: LENGTH > 0 && KEYPARTS != 0, CRASH IN JOIN::OPTIMIZE_KEYUSE
10734+
#
10735+
CREATE TABLE t1 (a INT, b INT, PRIMARY KEY(a));
10736+
CREATE TABLE t2 (c INT PRIMARY KEY);
10737+
EXPLAIN SELECT 1
10738+
FROM t1
10739+
WHERE 1 IN (SELECT (c IS NULL) IN (SELECT a
10740+
FROM t1
10741+
WHERE b)
10742+
FROM t2);
10743+
id select_type table type possible_keys key key_len ref rows Extra
10744+
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
10745+
3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
10746+
SELECT 1
10747+
FROM t1
10748+
WHERE 1 IN (SELECT (c IS NULL) IN (SELECT a
10749+
FROM t1
10750+
WHERE b)
10751+
FROM t2);
10752+
1
10753+
DROP TABLE t1,t2;
10754+
#
10755+
# Bug #22305361: QUERY WITH MATERIALIZED TABLE RETURNS INCORRECT
10756+
# RESULTS IN 5.6
10757+
#
10758+
CREATE TABLE t(a INT,b INT);
10759+
INSERT INTO t VALUES (1,0),(1,0),(1,0),(1,0),(1,1);
10760+
EXPLAIN extended SELECT *
10761+
FROM t AS t1
10762+
WHERE t1.a IN (SELECT t2.a
10763+
FROM t AS t2
10764+
WHERE CONCAT(t2.a,'') NOT IN (SELECT t3.a
10765+
FROM t AS t3
10766+
WHERE t3.b=1));
10767+
id select_type table type possible_keys key key_len ref rows filtered Extra
10768+
1 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 Using where
10769+
1 PRIMARY <subquery2> eq_ref <auto_key> <auto_key> 5 test.t1.a 1 100.00 NULL
10770+
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 100.00 Using where
10771+
3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 5 100.00 Using where
10772+
Warnings:
10773+
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t` `t1` semi join (`test`.`t` `t2`) where ((`<subquery2>`.`a` = `test`.`t1`.`a`) and (not(<in_optimizer>(concat(`test`.`t2`.`a`,''),<exists>(/* select#3 */ select 1 from `test`.`t` `t3` where ((`test`.`t3`.`b` = 1) and <if>(outer_field_is_not_null, ((<cache>(concat(`test`.`t2`.`a`,'')) = `test`.`t3`.`a`) or isnull(`test`.`t3`.`a`)), true)) having <if>(outer_field_is_not_null, <is_not_null_test>(`test`.`t3`.`a`), true))))))
10774+
SELECT *
10775+
FROM t AS t1
10776+
WHERE t1.a IN (SELECT t2.a
10777+
FROM t AS t2
10778+
WHERE CONCAT(t2.a,'') NOT IN (SELECT t3.a
10779+
FROM t AS t3
10780+
WHERE t3.b=1));
10781+
a b
10782+
DROP TABLE t;
1068610783
set optimizer_switch=default;

‎mysql-test/r/subquery_sj_all_bka.result‎

Lines changed: 97 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -10689,5 +10689,102 @@ DROP TABLE t1,t2;
1068910689
# End of test for Bug#21184091
1069010690
set @@optimizer_switch=@old_opt_switch;
1069110691
# End of 5.6 tests
10692+
# Bug#21139722: Assertion failed: !(used_tables() & ((table_map) 1) ...
10693+
CREATE TABLE t1(a INTEGER) engine=innodb;
10694+
CREATE TABLE t2(b INTEGER) engine=innodb;
10695+
explain SELECT 1
10696+
FROM (SELECT 1 IN (SELECT 1
10697+
FROM t1
10698+
WHERE (SELECT 1 FROM t2 HAVING b) NOT IN (SELECT 1 FROM t2)
10699+
)
10700+
FROM t2
10701+
) AS z;
10702+
id select_type table type possible_keys key key_len ref rows Extra
10703+
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 NULL
10704+
2 DERIVED t2 ALL NULL NULL NULL NULL 1 NULL
10705+
3 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 1 Using where
10706+
5 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 1 Using where
10707+
4 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 1 NULL
10708+
SELECT 1
10709+
FROM (SELECT 1 IN (SELECT 1
10710+
FROM t1
10711+
WHERE (SELECT 1 FROM t2 HAVING b) NOT IN (SELECT 1 FROM t2)
10712+
)
10713+
FROM t2
10714+
) AS z;
10715+
1
10716+
DROP TABLE t1, t2;
10717+
CREATE TABLE t1(a INTEGER) engine=innodb;
10718+
explain SELECT (SELECT a
10719+
FROM t1 AS t2
10720+
WHERE a IN (SELECT t1.a+t2.a
10721+
FROM t1 AS t3))
10722+
FROM t1 AS t1;
10723+
id select_type table type possible_keys key key_len ref rows Extra
10724+
1 PRIMARY t1 ALL NULL NULL NULL NULL 1 NULL
10725+
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 1 Using where
10726+
2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 1 FirstMatch(t2); Using join buffer (Block Nested Loop)
10727+
SELECT (SELECT a
10728+
FROM t1 AS t2
10729+
WHERE a IN (SELECT t1.a+t2.a
10730+
FROM t1 AS t3))
10731+
FROM t1 AS t1;
10732+
(SELECT a
10733+
FROM t1 AS t2
10734+
WHERE a IN (SELECT t1.a+t2.a
10735+
FROM t1 AS t3))
10736+
DROP TABLE t1;
10737+
# End of test for Bug#21139722
10738+
#
10739+
# Bug#21139402 ASSERTION FAILED: LENGTH > 0 && KEYPARTS != 0, CRASH IN JOIN::OPTIMIZE_KEYUSE
10740+
#
10741+
CREATE TABLE t1 (a INT, b INT, PRIMARY KEY(a));
10742+
CREATE TABLE t2 (c INT PRIMARY KEY);
10743+
EXPLAIN SELECT 1
10744+
FROM t1
10745+
WHERE 1 IN (SELECT (c IS NULL) IN (SELECT a
10746+
FROM t1
10747+
WHERE b)
10748+
FROM t2);
10749+
id select_type table type possible_keys key key_len ref rows Extra
10750+
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
10751+
3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
10752+
SELECT 1
10753+
FROM t1
10754+
WHERE 1 IN (SELECT (c IS NULL) IN (SELECT a
10755+
FROM t1
10756+
WHERE b)
10757+
FROM t2);
10758+
1
10759+
DROP TABLE t1,t2;
10760+
#
10761+
# Bug #22305361: QUERY WITH MATERIALIZED TABLE RETURNS INCORRECT
10762+
# RESULTS IN 5.6
10763+
#
10764+
CREATE TABLE t(a INT,b INT);
10765+
INSERT INTO t VALUES (1,0),(1,0),(1,0),(1,0),(1,1);
10766+
EXPLAIN extended SELECT *
10767+
FROM t AS t1
10768+
WHERE t1.a IN (SELECT t2.a
10769+
FROM t AS t2
10770+
WHERE CONCAT(t2.a,'') NOT IN (SELECT t3.a
10771+
FROM t AS t3
10772+
WHERE t3.b=1));
10773+
id select_type table type possible_keys key key_len ref rows filtered Extra
10774+
1 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 Using where
10775+
1 PRIMARY <subquery2> eq_ref <auto_key> <auto_key> 5 test.t1.a 1 100.00 NULL
10776+
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 100.00 Using where
10777+
3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 5 100.00 Using where
10778+
Warnings:
10779+
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t` `t1` semi join (`test`.`t` `t2`) where ((`<subquery2>`.`a` = `test`.`t1`.`a`) and (not(<in_optimizer>(concat(`test`.`t2`.`a`,''),<exists>(/* select#3 */ select 1 from `test`.`t` `t3` where ((`test`.`t3`.`b` = 1) and <if>(outer_field_is_not_null, ((<cache>(concat(`test`.`t2`.`a`,'')) = `test`.`t3`.`a`) or isnull(`test`.`t3`.`a`)), true)) having <if>(outer_field_is_not_null, <is_not_null_test>(`test`.`t3`.`a`), true))))))
10780+
SELECT *
10781+
FROM t AS t1
10782+
WHERE t1.a IN (SELECT t2.a
10783+
FROM t AS t2
10784+
WHERE CONCAT(t2.a,'') NOT IN (SELECT t3.a
10785+
FROM t AS t3
10786+
WHERE t3.b=1));
10787+
a b
10788+
DROP TABLE t;
1069210789
set optimizer_switch=default;
1069310790
set optimizer_switch=default;

‎mysql-test/r/subquery_sj_all_bka_nixbnl.result‎

Lines changed: 97 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -10698,5 +10698,102 @@ DROP TABLE t1,t2;
1069810698
# End of test for Bug#21184091
1069910699
set @@optimizer_switch=@old_opt_switch;
1070010700
# End of 5.6 tests
10701+
# Bug#21139722: Assertion failed: !(used_tables() & ((table_map) 1) ...
10702+
CREATE TABLE t1(a INTEGER) engine=innodb;
10703+
CREATE TABLE t2(b INTEGER) engine=innodb;
10704+
explain SELECT 1
10705+
FROM (SELECT 1 IN (SELECT 1
10706+
FROM t1
10707+
WHERE (SELECT 1 FROM t2 HAVING b) NOT IN (SELECT 1 FROM t2)
10708+
)
10709+
FROM t2
10710+
) AS z;
10711+
id select_type table type possible_keys key key_len ref rows Extra
10712+
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 NULL
10713+
2 DERIVED t2 ALL NULL NULL NULL NULL 1 NULL
10714+
3 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 1 Using where
10715+
5 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 1 Using where
10716+
4 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 1 NULL
10717+
SELECT 1
10718+
FROM (SELECT 1 IN (SELECT 1
10719+
FROM t1
10720+
WHERE (SELECT 1 FROM t2 HAVING b) NOT IN (SELECT 1 FROM t2)
10721+
)
10722+
FROM t2
10723+
) AS z;
10724+
1
10725+
DROP TABLE t1, t2;
10726+
CREATE TABLE t1(a INTEGER) engine=innodb;
10727+
explain SELECT (SELECT a
10728+
FROM t1 AS t2
10729+
WHERE a IN (SELECT t1.a+t2.a
10730+
FROM t1 AS t3))
10731+
FROM t1 AS t1;
10732+
id select_type table type possible_keys key key_len ref rows Extra
10733+
1 PRIMARY t1 ALL NULL NULL NULL NULL 1 NULL
10734+
2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 1 Using where
10735+
2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 1 FirstMatch(t2)
10736+
SELECT (SELECT a
10737+
FROM t1 AS t2
10738+
WHERE a IN (SELECT t1.a+t2.a
10739+
FROM t1 AS t3))
10740+
FROM t1 AS t1;
10741+
(SELECT a
10742+
FROM t1 AS t2
10743+
WHERE a IN (SELECT t1.a+t2.a
10744+
FROM t1 AS t3))
10745+
DROP TABLE t1;
10746+
# End of test for Bug#21139722
10747+
#
10748+
# Bug#21139402 ASSERTION FAILED: LENGTH > 0 && KEYPARTS != 0, CRASH IN JOIN::OPTIMIZE_KEYUSE
10749+
#
10750+
CREATE TABLE t1 (a INT, b INT, PRIMARY KEY(a));
10751+
CREATE TABLE t2 (c INT PRIMARY KEY);
10752+
EXPLAIN SELECT 1
10753+
FROM t1
10754+
WHERE 1 IN (SELECT (c IS NULL) IN (SELECT a
10755+
FROM t1
10756+
WHERE b)
10757+
FROM t2);
10758+
id select_type table type possible_keys key key_len ref rows Extra
10759+
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
10760+
3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
10761+
SELECT 1
10762+
FROM t1
10763+
WHERE 1 IN (SELECT (c IS NULL) IN (SELECT a
10764+
FROM t1
10765+
WHERE b)
10766+
FROM t2);
10767+
1
10768+
DROP TABLE t1,t2;
10769+
#
10770+
# Bug #22305361: QUERY WITH MATERIALIZED TABLE RETURNS INCORRECT
10771+
# RESULTS IN 5.6
10772+
#
10773+
CREATE TABLE t(a INT,b INT);
10774+
INSERT INTO t VALUES (1,0),(1,0),(1,0),(1,0),(1,1);
10775+
EXPLAIN extended SELECT *
10776+
FROM t AS t1
10777+
WHERE t1.a IN (SELECT t2.a
10778+
FROM t AS t2
10779+
WHERE CONCAT(t2.a,'') NOT IN (SELECT t3.a
10780+
FROM t AS t3
10781+
WHERE t3.b=1));
10782+
id select_type table type possible_keys key key_len ref rows filtered Extra
10783+
1 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 Using where
10784+
1 PRIMARY <subquery2> eq_ref <auto_key> <auto_key> 5 test.t1.a 1 100.00 NULL
10785+
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 100.00 Using where
10786+
3 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 5 100.00 Using where
10787+
Warnings:
10788+
Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t` `t1` semi join (`test`.`t` `t2`) where ((`<subquery2>`.`a` = `test`.`t1`.`a`) and (not(<in_optimizer>(concat(`test`.`t2`.`a`,''),<exists>(/* select#3 */ select 1 from `test`.`t` `t3` where ((`test`.`t3`.`b` = 1) and <if>(outer_field_is_not_null, ((<cache>(concat(`test`.`t2`.`a`,'')) = `test`.`t3`.`a`) or isnull(`test`.`t3`.`a`)), true)) having <if>(outer_field_is_not_null, <is_not_null_test>(`test`.`t3`.`a`), true))))))
10789+
SELECT *
10790+
FROM t AS t1
10791+
WHERE t1.a IN (SELECT t2.a
10792+
FROM t AS t2
10793+
WHERE CONCAT(t2.a,'') NOT IN (SELECT t3.a
10794+
FROM t AS t3
10795+
WHERE t3.b=1));
10796+
a b
10797+
DROP TABLE t;
1070110798
set optimizer_switch=default;
1070210799
set optimizer_switch=default;

0 commit comments

Comments
 (0)