3834 Guilhem Bichot 2012-03-22
Fix for Bug#13838501 ASSERTION `TABLE->FILE->INITED' FAILED IN
SUBSELECT_HASH_SJ_ENGINE::EXEC
@ sql/item_subselect.cc
Scenario of the bug.
We are evaluating:
763078661862588416 IN (SELECT int_column ...).
Subquery materialization is used: subselect_hash_sj_engine::exec() is
called, which calls subselect_indexsubquery_engine::exec(). There,
convert_error is true (because 763078661862588416 is too big for INT)
so the function concludes that an exact match is impossible - and
that it does not need to do an index lookup.
Back to subselect_hash_sj_engine::exec(): it tries to find a partial match:
wants to search for an inner NULL; expected the index lookup to have
been initialized by subselect_indexsubquery_engine::exec(). But in
that case, index lookup was not initialized.
Fix: when searching for NULL, initialize index lookup if needed.
Note: I verified, without using subqueries, that in MySQL and Oracle,
(763078661862588416 = NULL_value_in_int_column)
is UNKNOWN (even though one might have thought that, as
763078661862588416 cannot match any unknown INT value, the result
would be FALSE...).
Thus it is correct that subselect_hash_sj_engine::exec() searches for
inner NULLs in the bug's case.
modified:
mysql-test/include/subquery_mat.inc
mysql-test/r/subquery_mat.result
mysql-test/r/subquery_mat_all.result
mysql-test/r/subquery_mat_none.result
sql/item_subselect.cc
3833 Marko Mäkelä 2012-03-22
Bug#13875241 INNODB-INDEX-ONLINE-PURGE.TEST TIMES OUT RANDOMLY
innodb-index-online-purge.test: Remove nondeterminism. Only one DEBUG_SYNC
signal can be active at a time, otherwise the previous SIGNAL may be
overwritten before it is consumed by a WAIT_FOR.
innodb-index-online.test: Enable a trace file, so that we get more
information what is going wrong.
modified:
mysql-test/suite/innodb/r/innodb-index-online.result
mysql-test/suite/innodb/t/disabled.def
mysql-test/suite/innodb/t/innodb-index-online-purge.test
mysql-test/suite/innodb/t/innodb-index-online.test
=== modified file 'mysql-test/include/subquery_mat.inc'
--- a/mysql-test/include/subquery_mat.inc 2012-02-29 11:17:52 +0000
+++ b/mysql-test/include/subquery_mat.inc 2012-03-22 14:52:04 +0000
@@ -1384,4 +1384,33 @@ eval $query;
DROP TABLE t1, t2;
+--echo #
+--echo # Bug#13838501 ASSERTION `TABLE->FILE->INITED' FAILED IN
+--echo # SUBSELECT_HASH_SJ_ENGINE::EXEC
+--echo #
+
+CREATE TABLE t1
+(c1 bigint,c2 char,pk INT,c3 char,c4 int,c5 INT,key (c5))
+ENGINE=InnoDB;
+INSERT INTO t1 VALUES (763078661862588416,0,1,'',1,'');
+CREATE TABLE t2 (c4k int,c4 int,cminnuk INT,key (cminnuk)) ENGINE=InnoDB;
+INSERT INTO t2 VALUES(0,'','');
+CREATE TABLE t3
+(c4 int,pk INT,c1 bigint,cyk year,cy year,key (cyk))
+ENGINE=InnoDB;
+INSERT INTO t3 VALUES(0,8,'',0,'');
+let $query=
+SELECT o.c2 AS x FROM t1 AS o
+WHERE o.c1 IN
+ (SELECT innr.c4 AS y
+ FROM t2 AS innr2 JOIN t3 AS innr
+ ON (innr2.c4k=innr.c4)
+ WHERE innr.c1=6 OR NOT innr.c1=innr.pk
+ ORDER BY innr.c4)
+ AND o.c4=7 XOR o.pk=3 ORDER BY o.pk;
+eval EXPLAIN $query;
+eval $query;
+
+DROP TABLE t1,t2,t3;
+
--echo # End of 5.6 tests
=== modified file 'mysql-test/r/subquery_mat.result'
--- a/mysql-test/r/subquery_mat.result 2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/subquery_mat.result 2012-03-22 14:52:04 +0000
@@ -1819,5 +1819,49 @@ HAVING x = '2000-09-09'
ORDER BY col_time_key;
x
DROP TABLE t1, t2;
+#
+# Bug#13838501 ASSERTION `TABLE->FILE->INITED' FAILED IN
+# SUBSELECT_HASH_SJ_ENGINE::EXEC
+#
+CREATE TABLE t1
+(c1 bigint,c2 char,pk INT,c3 char,c4 int,c5 INT,key (c5))
+ENGINE=InnoDB;
+INSERT INTO t1 VALUES (763078661862588416,0,1,'',1,'');
+Warnings:
+Warning 1366 Incorrect integer value: '' for column 'c5' at row 1
+CREATE TABLE t2 (c4k int,c4 int,cminnuk INT,key (cminnuk)) ENGINE=InnoDB;
+INSERT INTO t2 VALUES(0,'','');
+Warnings:
+Warning 1366 Incorrect integer value: '' for column 'c4' at row 1
+Warning 1366 Incorrect integer value: '' for column 'cminnuk' at row 1
+CREATE TABLE t3
+(c4 int,pk INT,c1 bigint,cyk year,cy year,key (cyk))
+ENGINE=InnoDB;
+INSERT INTO t3 VALUES(0,8,'',0,'');
+Warnings:
+Warning 1366 Incorrect integer value: '' for column 'c1' at row 1
+Warning 1366 Incorrect integer value: '' for column 'cy' at row 1
+EXPLAIN SELECT o.c2 AS x FROM t1 AS o
+WHERE o.c1 IN
+(SELECT innr.c4 AS y
+FROM t2 AS innr2 JOIN t3 AS innr
+ON (innr2.c4k=innr.c4)
+WHERE innr.c1=6 OR NOT innr.c1=innr.pk
+ORDER BY innr.c4)
+AND o.c4=7 XOR o.pk=3 ORDER BY o.pk;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY o ALL NULL NULL NULL NULL 1 Using where; Using filesort
+2 SUBQUERY innr2 ALL NULL NULL NULL NULL 1 NULL
+2 SUBQUERY innr ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
+SELECT o.c2 AS x FROM t1 AS o
+WHERE o.c1 IN
+(SELECT innr.c4 AS y
+FROM t2 AS innr2 JOIN t3 AS innr
+ON (innr2.c4k=innr.c4)
+WHERE innr.c1=6 OR NOT innr.c1=innr.pk
+ORDER BY innr.c4)
+AND o.c4=7 XOR o.pk=3 ORDER BY o.pk;
+x
+DROP TABLE t1,t2,t3;
# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_mat_all.result'
--- a/mysql-test/r/subquery_mat_all.result 2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/subquery_mat_all.result 2012-03-22 14:52:04 +0000
@@ -1818,5 +1818,49 @@ HAVING x = '2000-09-09'
ORDER BY col_time_key;
x
DROP TABLE t1, t2;
+#
+# Bug#13838501 ASSERTION `TABLE->FILE->INITED' FAILED IN
+# SUBSELECT_HASH_SJ_ENGINE::EXEC
+#
+CREATE TABLE t1
+(c1 bigint,c2 char,pk INT,c3 char,c4 int,c5 INT,key (c5))
+ENGINE=InnoDB;
+INSERT INTO t1 VALUES (763078661862588416,0,1,'',1,'');
+Warnings:
+Warning 1366 Incorrect integer value: '' for column 'c5' at row 1
+CREATE TABLE t2 (c4k int,c4 int,cminnuk INT,key (cminnuk)) ENGINE=InnoDB;
+INSERT INTO t2 VALUES(0,'','');
+Warnings:
+Warning 1366 Incorrect integer value: '' for column 'c4' at row 1
+Warning 1366 Incorrect integer value: '' for column 'cminnuk' at row 1
+CREATE TABLE t3
+(c4 int,pk INT,c1 bigint,cyk year,cy year,key (cyk))
+ENGINE=InnoDB;
+INSERT INTO t3 VALUES(0,8,'',0,'');
+Warnings:
+Warning 1366 Incorrect integer value: '' for column 'c1' at row 1
+Warning 1366 Incorrect integer value: '' for column 'cy' at row 1
+EXPLAIN SELECT o.c2 AS x FROM t1 AS o
+WHERE o.c1 IN
+(SELECT innr.c4 AS y
+FROM t2 AS innr2 JOIN t3 AS innr
+ON (innr2.c4k=innr.c4)
+WHERE innr.c1=6 OR NOT innr.c1=innr.pk
+ORDER BY innr.c4)
+AND o.c4=7 XOR o.pk=3 ORDER BY o.pk;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY o ALL NULL NULL NULL NULL 1 Using where; Using filesort
+2 SUBQUERY innr2 ALL NULL NULL NULL NULL 1 NULL
+2 SUBQUERY innr ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
+SELECT o.c2 AS x FROM t1 AS o
+WHERE o.c1 IN
+(SELECT innr.c4 AS y
+FROM t2 AS innr2 JOIN t3 AS innr
+ON (innr2.c4k=innr.c4)
+WHERE innr.c1=6 OR NOT innr.c1=innr.pk
+ORDER BY innr.c4)
+AND o.c4=7 XOR o.pk=3 ORDER BY o.pk;
+x
+DROP TABLE t1,t2,t3;
# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_mat_none.result'
--- a/mysql-test/r/subquery_mat_none.result 2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/subquery_mat_none.result 2012-03-22 14:52:04 +0000
@@ -1816,5 +1816,49 @@ HAVING x = '2000-09-09'
ORDER BY col_time_key;
x
DROP TABLE t1, t2;
+#
+# Bug#13838501 ASSERTION `TABLE->FILE->INITED' FAILED IN
+# SUBSELECT_HASH_SJ_ENGINE::EXEC
+#
+CREATE TABLE t1
+(c1 bigint,c2 char,pk INT,c3 char,c4 int,c5 INT,key (c5))
+ENGINE=InnoDB;
+INSERT INTO t1 VALUES (763078661862588416,0,1,'',1,'');
+Warnings:
+Warning 1366 Incorrect integer value: '' for column 'c5' at row 1
+CREATE TABLE t2 (c4k int,c4 int,cminnuk INT,key (cminnuk)) ENGINE=InnoDB;
+INSERT INTO t2 VALUES(0,'','');
+Warnings:
+Warning 1366 Incorrect integer value: '' for column 'c4' at row 1
+Warning 1366 Incorrect integer value: '' for column 'cminnuk' at row 1
+CREATE TABLE t3
+(c4 int,pk INT,c1 bigint,cyk year,cy year,key (cyk))
+ENGINE=InnoDB;
+INSERT INTO t3 VALUES(0,8,'',0,'');
+Warnings:
+Warning 1366 Incorrect integer value: '' for column 'c1' at row 1
+Warning 1366 Incorrect integer value: '' for column 'cy' at row 1
+EXPLAIN SELECT o.c2 AS x FROM t1 AS o
+WHERE o.c1 IN
+(SELECT innr.c4 AS y
+FROM t2 AS innr2 JOIN t3 AS innr
+ON (innr2.c4k=innr.c4)
+WHERE innr.c1=6 OR NOT innr.c1=innr.pk
+ORDER BY innr.c4)
+AND o.c4=7 XOR o.pk=3 ORDER BY o.pk;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY o ALL NULL NULL NULL NULL 1 Using where; Using filesort
+2 DEPENDENT SUBQUERY innr2 ALL NULL NULL NULL NULL 1 Using where
+2 DEPENDENT SUBQUERY innr ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
+SELECT o.c2 AS x FROM t1 AS o
+WHERE o.c1 IN
+(SELECT innr.c4 AS y
+FROM t2 AS innr2 JOIN t3 AS innr
+ON (innr2.c4k=innr.c4)
+WHERE innr.c1=6 OR NOT innr.c1=innr.pk
+ORDER BY innr.c4)
+AND o.c4=7 XOR o.pk=3 ORDER BY o.pk;
+x
+DROP TABLE t1,t2,t3;
# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'sql/item_subselect.cc'
--- a/sql/item_subselect.cc 2012-03-06 14:29:42 +0000
+++ b/sql/item_subselect.cc 2012-03-22 14:52:04 +0000
@@ -3586,8 +3586,10 @@ err:
if (mat_table_has_nulls == NEX_UNKNOWN) // We do not know yet
{
// Search for NULL inside tmp table, and remember the outcome.
- DBUG_ASSERT(table->file->inited);
*tab->ref.null_ref_key= 1;
+ if (!table->file->inited &&
+ table->file->ha_index_init(tab->ref.key, false /* sorted */))
+ DBUG_RETURN(true);
if (safe_index_read(tab) == 1)
DBUG_RETURN(true);
*tab->ref.null_ref_key= 0; // prepare for next searches of non-NULL
No bundle (reason: useless for push emails).| Thread |
|---|
| • bzr push into mysql-trunk branch (guilhem.bichot:3833 to 3834) Bug#13838501 | Guilhem Bichot | 22 Mar |
