List:Commits« Previous MessageNext Message »
From:Guilhem Bichot Date:June 21 2011 8:01am
Subject:bzr commit into mysql-trunk branch (guilhem.bichot:3387) Bug#12616253
View as plain text  
#At file:///home/mysql_src/bzrrepos_new/mysql-next-mr-opt-backporting/ based on revid:guilhem.bichot@stripped

 3387 Guilhem Bichot	2011-06-21
      Fix for BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS)
      QUICK_GROUP_MIN_MAX_SELECT::reset() didn't reset the "index scan"
      to start from the first key.
     @ mysql-test/r/subquery_all.result
        without the code fix, this and other results showed only (v,1) and (s,1)
        (two rows instead of twenty).
     @ sql/opt_range.cc
        QUICK_GROUP_MIN_MAX_SELECT is used (see EXPLAIN output in result
        files). Execution happens as described below.
        1) First row of "table1 JOIN table2" gives WHERE condition
        "<>j AND >=v" for subquery.
        When subq execution starts, sub_select() calls join_init_read_record()
        which calls QUICK_GROUP_MIN_MAX_SELECT::reset().
        Then keys are scanned using QUICK_GROUP_MIN_MAX_SELECT::get_next() and
        QUICK_GROUP_MIN_MAX_SELECT::next_prefix(): key "c" is read,
        "d", etc, none matches WHERE clause (as determined in
        evaluate_join_record()), until key "v" is read, which
        matches. EXISTS() is thus true, first row of "table2 JOIN table1" is
        emitted.
        2) Second row of "table1 JOIN table2" gives WHERE condition
        "<>v AND >=v" for subquery.
        When subq execution starts, QUICK_GROUP_MIN_MAX_SELECT::reset() is called.
        Then keys are scanned, but QUICK_GROUP_MIN_MAX_SELECT::next_prefix()
        starts where it left off at end of previous subq execution: it left at
        "v" so (see index_next_different()) it finds "y", which
        matches. EXISTS() is thus true, second row of "table1 JOIN table2" is
        emitted.
        3) Third row of "table1 JOIN table2".
        When subq execution, QUICK_GROUP_MIN_MAX_SELECT::reset() is called.
        Then keys are scanned, but starting where it left off at end of
        previous subq execution: it left at "y" so index_next_different()
        finds nothing (there is nothing after "y"). Row of "table1 JOIN
        table2" is not emitted. Same for all subsequent rows.
        
        Fix: reset seen_first_key in reset().
        The description above was without block-level nested loop join. With
        BNL, rows of "table1 JOIN table2" are created in different order, so
        the two first rows for which the subq is executed are different. But
        the bug is the same.

    modified:
      mysql-test/include/subquery.inc
      mysql-test/r/subquery_all.result
      mysql-test/r/subquery_all_jcl6.result
      mysql-test/r/subquery_nomat_nosj.result
      mysql-test/r/subquery_nomat_nosj_jcl6.result
      mysql-test/r/subquery_none.result
      mysql-test/r/subquery_none_jcl6.result
      sql/opt_range.cc
=== modified file 'mysql-test/include/subquery.inc'
--- a/mysql-test/include/subquery.inc	2011-05-05 07:41:53 +0000
+++ b/mysql-test/include/subquery.inc	2011-06-21 08:01:25 +0000
@@ -5354,3 +5354,31 @@ CREATE TABLE t(a VARCHAR(245) DEFAULT
 INSERT INTO t VALUES (''),(''),(''),(''),(''),(''),(''),(''),(''),(''),('');
 SELECT * FROM (SELECT default(a) FROM t GROUP BY a) d;
 DROP TABLE t;
+
+--echo #
+--echo # BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS)
+--echo #
+
+CREATE TABLE t1 (col_varchar_nokey varchar(1));
+INSERT INTO t1 VALUES ('v'),('s');
+
+CREATE TABLE t2 (
+  col_int_nokey int,
+  col_varchar_key varchar(1),
+  KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES (1,'j'),(2,'v'),(3,'c'),(4,'m'),(5,'d'),
+(6,'d'),(7,'y'),(8,'t'),(9,'d'),(10,'s');
+
+let $query=SELECT table1.col_varchar_nokey, table2.col_int_nokey
+FROM t1 AS table1, t2 AS table2
+WHERE EXISTS 
+( 
+SELECT DISTINCT col_varchar_key 
+FROM t2 
+WHERE col_varchar_key != table2.col_varchar_key AND col_varchar_key >= table1.col_varchar_nokey );
+
+eval $query;
+eval explain $query;
+
+DROP TABLE t1,t2;

=== modified file 'mysql-test/r/subquery_all.result'
--- a/mysql-test/r/subquery_all.result	2011-05-05 07:41:53 +0000
+++ b/mysql-test/r/subquery_all.result	2011-06-21 08:01:25 +0000
@@ -6507,4 +6507,56 @@ SELECT * FROM (SELECT default(a) FROM t 
 default(a)
 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
 DROP TABLE t;
+#
+# BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS)
+#
+CREATE TABLE t1 (col_varchar_nokey varchar(1));
+INSERT INTO t1 VALUES ('v'),('s');
+CREATE TABLE t2 (
+col_int_nokey int,
+col_varchar_key varchar(1),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES (1,'j'),(2,'v'),(3,'c'),(4,'m'),(5,'d'),
+(6,'d'),(7,'y'),(8,'t'),(9,'d'),(10,'s');
+SELECT table1.col_varchar_nokey, table2.col_int_nokey
+FROM t1 AS table1, t2 AS table2
+WHERE EXISTS 
+( 
+SELECT DISTINCT col_varchar_key 
+FROM t2 
+WHERE col_varchar_key != table2.col_varchar_key AND col_varchar_key >= table1.col_varchar_nokey );
+col_varchar_nokey	col_int_nokey
+v	1
+s	1
+v	2
+s	2
+v	3
+s	3
+v	4
+s	4
+v	5
+s	5
+v	6
+s	6
+v	7
+s	7
+v	8
+s	8
+v	9
+s	9
+v	10
+s	10
+explain SELECT table1.col_varchar_nokey, table2.col_int_nokey
+FROM t1 AS table1, t2 AS table2
+WHERE EXISTS 
+( 
+SELECT DISTINCT col_varchar_key 
+FROM t2 
+WHERE col_varchar_key != table2.col_varchar_key AND col_varchar_key >= table1.col_varchar_nokey );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	table1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	table2	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (BNL, incremental buffers)
+2	DEPENDENT SUBQUERY	t2	range	col_varchar_key	col_varchar_key	4	NULL	6	Using where; Using index for group-by; Using temporary
+DROP TABLE t1,t2;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_all_jcl6.result'
--- a/mysql-test/r/subquery_all_jcl6.result	2011-05-05 07:41:53 +0000
+++ b/mysql-test/r/subquery_all_jcl6.result	2011-06-21 08:01:25 +0000
@@ -6511,5 +6511,57 @@ SELECT * FROM (SELECT default(a) FROM t 
 default(a)
 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
 DROP TABLE t;
+#
+# BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS)
+#
+CREATE TABLE t1 (col_varchar_nokey varchar(1));
+INSERT INTO t1 VALUES ('v'),('s');
+CREATE TABLE t2 (
+col_int_nokey int,
+col_varchar_key varchar(1),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES (1,'j'),(2,'v'),(3,'c'),(4,'m'),(5,'d'),
+(6,'d'),(7,'y'),(8,'t'),(9,'d'),(10,'s');
+SELECT table1.col_varchar_nokey, table2.col_int_nokey
+FROM t1 AS table1, t2 AS table2
+WHERE EXISTS 
+( 
+SELECT DISTINCT col_varchar_key 
+FROM t2 
+WHERE col_varchar_key != table2.col_varchar_key AND col_varchar_key >= table1.col_varchar_nokey );
+col_varchar_nokey	col_int_nokey
+v	1
+s	1
+v	2
+s	2
+v	3
+s	3
+v	4
+s	4
+v	5
+s	5
+v	6
+s	6
+v	7
+s	7
+v	8
+s	8
+v	9
+s	9
+v	10
+s	10
+explain SELECT table1.col_varchar_nokey, table2.col_int_nokey
+FROM t1 AS table1, t2 AS table2
+WHERE EXISTS 
+( 
+SELECT DISTINCT col_varchar_key 
+FROM t2 
+WHERE col_varchar_key != table2.col_varchar_key AND col_varchar_key >= table1.col_varchar_nokey );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	table1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	table2	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (BNL, incremental buffers)
+2	DEPENDENT SUBQUERY	t2	range	col_varchar_key	col_varchar_key	4	NULL	6	Using where; Using index for group-by; Using temporary
+DROP TABLE t1,t2;
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;

=== modified file 'mysql-test/r/subquery_nomat_nosj.result'
--- a/mysql-test/r/subquery_nomat_nosj.result	2011-05-05 07:41:53 +0000
+++ b/mysql-test/r/subquery_nomat_nosj.result	2011-06-21 08:01:25 +0000
@@ -6507,4 +6507,56 @@ SELECT * FROM (SELECT default(a) FROM t 
 default(a)
 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
 DROP TABLE t;
+#
+# BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS)
+#
+CREATE TABLE t1 (col_varchar_nokey varchar(1));
+INSERT INTO t1 VALUES ('v'),('s');
+CREATE TABLE t2 (
+col_int_nokey int,
+col_varchar_key varchar(1),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES (1,'j'),(2,'v'),(3,'c'),(4,'m'),(5,'d'),
+(6,'d'),(7,'y'),(8,'t'),(9,'d'),(10,'s');
+SELECT table1.col_varchar_nokey, table2.col_int_nokey
+FROM t1 AS table1, t2 AS table2
+WHERE EXISTS 
+( 
+SELECT DISTINCT col_varchar_key 
+FROM t2 
+WHERE col_varchar_key != table2.col_varchar_key AND col_varchar_key >= table1.col_varchar_nokey );
+col_varchar_nokey	col_int_nokey
+v	1
+s	1
+v	2
+s	2
+v	3
+s	3
+v	4
+s	4
+v	5
+s	5
+v	6
+s	6
+v	7
+s	7
+v	8
+s	8
+v	9
+s	9
+v	10
+s	10
+explain SELECT table1.col_varchar_nokey, table2.col_int_nokey
+FROM t1 AS table1, t2 AS table2
+WHERE EXISTS 
+( 
+SELECT DISTINCT col_varchar_key 
+FROM t2 
+WHERE col_varchar_key != table2.col_varchar_key AND col_varchar_key >= table1.col_varchar_nokey );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	table1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	table2	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (BNL, incremental buffers)
+2	DEPENDENT SUBQUERY	t2	range	col_varchar_key	col_varchar_key	4	NULL	6	Using where; Using index for group-by; Using temporary
+DROP TABLE t1,t2;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_nomat_nosj_jcl6.result'
--- a/mysql-test/r/subquery_nomat_nosj_jcl6.result	2011-05-05 07:41:53 +0000
+++ b/mysql-test/r/subquery_nomat_nosj_jcl6.result	2011-06-21 08:01:25 +0000
@@ -6511,5 +6511,57 @@ SELECT * FROM (SELECT default(a) FROM t 
 default(a)
 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
 DROP TABLE t;
+#
+# BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS)
+#
+CREATE TABLE t1 (col_varchar_nokey varchar(1));
+INSERT INTO t1 VALUES ('v'),('s');
+CREATE TABLE t2 (
+col_int_nokey int,
+col_varchar_key varchar(1),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES (1,'j'),(2,'v'),(3,'c'),(4,'m'),(5,'d'),
+(6,'d'),(7,'y'),(8,'t'),(9,'d'),(10,'s');
+SELECT table1.col_varchar_nokey, table2.col_int_nokey
+FROM t1 AS table1, t2 AS table2
+WHERE EXISTS 
+( 
+SELECT DISTINCT col_varchar_key 
+FROM t2 
+WHERE col_varchar_key != table2.col_varchar_key AND col_varchar_key >= table1.col_varchar_nokey );
+col_varchar_nokey	col_int_nokey
+v	1
+s	1
+v	2
+s	2
+v	3
+s	3
+v	4
+s	4
+v	5
+s	5
+v	6
+s	6
+v	7
+s	7
+v	8
+s	8
+v	9
+s	9
+v	10
+s	10
+explain SELECT table1.col_varchar_nokey, table2.col_int_nokey
+FROM t1 AS table1, t2 AS table2
+WHERE EXISTS 
+( 
+SELECT DISTINCT col_varchar_key 
+FROM t2 
+WHERE col_varchar_key != table2.col_varchar_key AND col_varchar_key >= table1.col_varchar_nokey );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	table1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	table2	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (BNL, incremental buffers)
+2	DEPENDENT SUBQUERY	t2	range	col_varchar_key	col_varchar_key	4	NULL	6	Using where; Using index for group-by; Using temporary
+DROP TABLE t1,t2;
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;

=== modified file 'mysql-test/r/subquery_none.result'
--- a/mysql-test/r/subquery_none.result	2011-05-05 07:41:53 +0000
+++ b/mysql-test/r/subquery_none.result	2011-06-21 08:01:25 +0000
@@ -6506,4 +6506,56 @@ SELECT * FROM (SELECT default(a) FROM t 
 default(a)
 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
 DROP TABLE t;
+#
+# BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS)
+#
+CREATE TABLE t1 (col_varchar_nokey varchar(1));
+INSERT INTO t1 VALUES ('v'),('s');
+CREATE TABLE t2 (
+col_int_nokey int,
+col_varchar_key varchar(1),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES (1,'j'),(2,'v'),(3,'c'),(4,'m'),(5,'d'),
+(6,'d'),(7,'y'),(8,'t'),(9,'d'),(10,'s');
+SELECT table1.col_varchar_nokey, table2.col_int_nokey
+FROM t1 AS table1, t2 AS table2
+WHERE EXISTS 
+( 
+SELECT DISTINCT col_varchar_key 
+FROM t2 
+WHERE col_varchar_key != table2.col_varchar_key AND col_varchar_key >= table1.col_varchar_nokey );
+col_varchar_nokey	col_int_nokey
+v	1
+s	1
+v	2
+s	2
+v	3
+s	3
+v	4
+s	4
+v	5
+s	5
+v	6
+s	6
+v	7
+s	7
+v	8
+s	8
+v	9
+s	9
+v	10
+s	10
+explain SELECT table1.col_varchar_nokey, table2.col_int_nokey
+FROM t1 AS table1, t2 AS table2
+WHERE EXISTS 
+( 
+SELECT DISTINCT col_varchar_key 
+FROM t2 
+WHERE col_varchar_key != table2.col_varchar_key AND col_varchar_key >= table1.col_varchar_nokey );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	table1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	table2	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (BNL, incremental buffers)
+2	DEPENDENT SUBQUERY	t2	range	col_varchar_key	col_varchar_key	4	NULL	6	Using where; Using index for group-by; Using temporary
+DROP TABLE t1,t2;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_none_jcl6.result'
--- a/mysql-test/r/subquery_none_jcl6.result	2011-05-05 07:41:53 +0000
+++ b/mysql-test/r/subquery_none_jcl6.result	2011-06-21 08:01:25 +0000
@@ -6510,5 +6510,57 @@ SELECT * FROM (SELECT default(a) FROM t 
 default(a)
 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
 DROP TABLE t;
+#
+# BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS)
+#
+CREATE TABLE t1 (col_varchar_nokey varchar(1));
+INSERT INTO t1 VALUES ('v'),('s');
+CREATE TABLE t2 (
+col_int_nokey int,
+col_varchar_key varchar(1),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES (1,'j'),(2,'v'),(3,'c'),(4,'m'),(5,'d'),
+(6,'d'),(7,'y'),(8,'t'),(9,'d'),(10,'s');
+SELECT table1.col_varchar_nokey, table2.col_int_nokey
+FROM t1 AS table1, t2 AS table2
+WHERE EXISTS 
+( 
+SELECT DISTINCT col_varchar_key 
+FROM t2 
+WHERE col_varchar_key != table2.col_varchar_key AND col_varchar_key >= table1.col_varchar_nokey );
+col_varchar_nokey	col_int_nokey
+v	1
+s	1
+v	2
+s	2
+v	3
+s	3
+v	4
+s	4
+v	5
+s	5
+v	6
+s	6
+v	7
+s	7
+v	8
+s	8
+v	9
+s	9
+v	10
+s	10
+explain SELECT table1.col_varchar_nokey, table2.col_int_nokey
+FROM t1 AS table1, t2 AS table2
+WHERE EXISTS 
+( 
+SELECT DISTINCT col_varchar_key 
+FROM t2 
+WHERE col_varchar_key != table2.col_varchar_key AND col_varchar_key >= table1.col_varchar_nokey );
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	table1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	table2	ALL	NULL	NULL	NULL	NULL	10	Using where; Using join buffer (BNL, incremental buffers)
+2	DEPENDENT SUBQUERY	t2	range	col_varchar_key	col_varchar_key	4	NULL	6	Using where; Using index for group-by; Using temporary
+DROP TABLE t1,t2;
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;

=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc	2011-05-26 09:22:35 +0000
+++ b/sql/opt_range.cc	2011-06-21 08:01:25 +0000
@@ -11299,6 +11299,7 @@ int QUICK_GROUP_MIN_MAX_SELECT::reset(vo
   int result;
   DBUG_ENTER("QUICK_GROUP_MIN_MAX_SELECT::reset");
 
+  seen_first_key= false;
   head->set_keyread(TRUE); /* We need only the key attributes */
   /*
     Request ordered index access as usage of ::index_last(), 


Attachment: [text/bzr-bundle] bzr/guilhem.bichot@oracle.com-20110621080125-45ttg16rcjzl8f8y.bundle
Thread
bzr commit into mysql-trunk branch (guilhem.bichot:3387) Bug#12616253Guilhem Bichot21 Jun
  • Re: bzr commit into mysql-trunk branch (guilhem.bichot:3387) Bug#12616253Jorgen Loland22 Jun