List:Commits« Previous MessageNext Message »
From:Guilhem Bichot Date:March 22 2012 2:52pm
Subject:bzr push into mysql-trunk branch (guilhem.bichot:3833 to 3834) Bug#13838501
View as plain text  
 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#13838501Guilhem Bichot22 Mar