#At file:///home/mysql_src/bzrrepos_new/opt-back-new/ based on revid:guilhem.bichot@stripped
3391 Guilhem Bichot 2011-06-24
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,j) and (s,j)
(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-24 13:47:09 +0000
@@ -5354,3 +5354,27 @@ 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 (f1 varchar(1));
+INSERT INTO t1 VALUES ('v'),('s');
+
+CREATE TABLE t2 (f1_key varchar(1), KEY (f1_key));
+INSERT INTO t2 VALUES ('j'),('v'),('c'),('m'),('d'),
+('d'),('y'),('t'),('d'),('s');
+
+let $query=SELECT table1.f1, table2.f1_key
+FROM t1 AS table1, t2 AS table2
+WHERE EXISTS
+(
+SELECT DISTINCT f1_key
+FROM t2
+WHERE f1_key != table2.f1_key AND f1_key >= table1.f1 );
+
+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-24 13:47:09 +0000
@@ -6507,4 +6507,52 @@ 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 (f1 varchar(1));
+INSERT INTO t1 VALUES ('v'),('s');
+CREATE TABLE t2 (f1_key varchar(1), KEY (f1_key));
+INSERT INTO t2 VALUES ('j'),('v'),('c'),('m'),('d'),
+('d'),('y'),('t'),('d'),('s');
+SELECT table1.f1, table2.f1_key
+FROM t1 AS table1, t2 AS table2
+WHERE EXISTS
+(
+SELECT DISTINCT f1_key
+FROM t2
+WHERE f1_key != table2.f1_key AND f1_key >= table1.f1 );
+f1 f1_key
+v j
+s j
+v v
+s v
+v c
+s c
+v m
+s m
+v d
+s d
+v d
+s d
+v y
+s y
+v t
+s t
+v d
+s d
+v s
+s s
+explain SELECT table1.f1, table2.f1_key
+FROM t1 AS table1, t2 AS table2
+WHERE EXISTS
+(
+SELECT DISTINCT f1_key
+FROM t2
+WHERE f1_key != table2.f1_key AND f1_key >= table1.f1 );
+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 index NULL f1_key 4 NULL 10 Using where; Using index; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY t2 range f1_key f1_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-06-24 09:39:44 +0000
+++ b/mysql-test/r/subquery_all_jcl6.result 2011-06-24 13:47:09 +0000
@@ -6511,5 +6511,53 @@ 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 (f1 varchar(1));
+INSERT INTO t1 VALUES ('v'),('s');
+CREATE TABLE t2 (f1_key varchar(1), KEY (f1_key));
+INSERT INTO t2 VALUES ('j'),('v'),('c'),('m'),('d'),
+('d'),('y'),('t'),('d'),('s');
+SELECT table1.f1, table2.f1_key
+FROM t1 AS table1, t2 AS table2
+WHERE EXISTS
+(
+SELECT DISTINCT f1_key
+FROM t2
+WHERE f1_key != table2.f1_key AND f1_key >= table1.f1 );
+f1 f1_key
+v j
+s j
+v v
+s v
+v c
+s c
+v m
+s m
+v d
+s d
+v d
+s d
+v y
+s y
+v t
+s t
+v d
+s d
+v s
+s s
+explain SELECT table1.f1, table2.f1_key
+FROM t1 AS table1, t2 AS table2
+WHERE EXISTS
+(
+SELECT DISTINCT f1_key
+FROM t2
+WHERE f1_key != table2.f1_key AND f1_key >= table1.f1 );
+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 index NULL f1_key 4 NULL 10 Using where; Using index; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY t2 range f1_key f1_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-24 13:47:09 +0000
@@ -6507,4 +6507,52 @@ 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 (f1 varchar(1));
+INSERT INTO t1 VALUES ('v'),('s');
+CREATE TABLE t2 (f1_key varchar(1), KEY (f1_key));
+INSERT INTO t2 VALUES ('j'),('v'),('c'),('m'),('d'),
+('d'),('y'),('t'),('d'),('s');
+SELECT table1.f1, table2.f1_key
+FROM t1 AS table1, t2 AS table2
+WHERE EXISTS
+(
+SELECT DISTINCT f1_key
+FROM t2
+WHERE f1_key != table2.f1_key AND f1_key >= table1.f1 );
+f1 f1_key
+v j
+s j
+v v
+s v
+v c
+s c
+v m
+s m
+v d
+s d
+v d
+s d
+v y
+s y
+v t
+s t
+v d
+s d
+v s
+s s
+explain SELECT table1.f1, table2.f1_key
+FROM t1 AS table1, t2 AS table2
+WHERE EXISTS
+(
+SELECT DISTINCT f1_key
+FROM t2
+WHERE f1_key != table2.f1_key AND f1_key >= table1.f1 );
+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 index NULL f1_key 4 NULL 10 Using where; Using index; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY t2 range f1_key f1_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-06-24 09:39:44 +0000
+++ b/mysql-test/r/subquery_nomat_nosj_jcl6.result 2011-06-24 13:47:09 +0000
@@ -6511,5 +6511,53 @@ 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 (f1 varchar(1));
+INSERT INTO t1 VALUES ('v'),('s');
+CREATE TABLE t2 (f1_key varchar(1), KEY (f1_key));
+INSERT INTO t2 VALUES ('j'),('v'),('c'),('m'),('d'),
+('d'),('y'),('t'),('d'),('s');
+SELECT table1.f1, table2.f1_key
+FROM t1 AS table1, t2 AS table2
+WHERE EXISTS
+(
+SELECT DISTINCT f1_key
+FROM t2
+WHERE f1_key != table2.f1_key AND f1_key >= table1.f1 );
+f1 f1_key
+v j
+s j
+v v
+s v
+v c
+s c
+v m
+s m
+v d
+s d
+v d
+s d
+v y
+s y
+v t
+s t
+v d
+s d
+v s
+s s
+explain SELECT table1.f1, table2.f1_key
+FROM t1 AS table1, t2 AS table2
+WHERE EXISTS
+(
+SELECT DISTINCT f1_key
+FROM t2
+WHERE f1_key != table2.f1_key AND f1_key >= table1.f1 );
+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 index NULL f1_key 4 NULL 10 Using where; Using index; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY t2 range f1_key f1_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-24 13:47:09 +0000
@@ -6506,4 +6506,52 @@ 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 (f1 varchar(1));
+INSERT INTO t1 VALUES ('v'),('s');
+CREATE TABLE t2 (f1_key varchar(1), KEY (f1_key));
+INSERT INTO t2 VALUES ('j'),('v'),('c'),('m'),('d'),
+('d'),('y'),('t'),('d'),('s');
+SELECT table1.f1, table2.f1_key
+FROM t1 AS table1, t2 AS table2
+WHERE EXISTS
+(
+SELECT DISTINCT f1_key
+FROM t2
+WHERE f1_key != table2.f1_key AND f1_key >= table1.f1 );
+f1 f1_key
+v j
+s j
+v v
+s v
+v c
+s c
+v m
+s m
+v d
+s d
+v d
+s d
+v y
+s y
+v t
+s t
+v d
+s d
+v s
+s s
+explain SELECT table1.f1, table2.f1_key
+FROM t1 AS table1, t2 AS table2
+WHERE EXISTS
+(
+SELECT DISTINCT f1_key
+FROM t2
+WHERE f1_key != table2.f1_key AND f1_key >= table1.f1 );
+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 index NULL f1_key 4 NULL 10 Using where; Using index; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY t2 range f1_key f1_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-24 13:47:09 +0000
@@ -6510,5 +6510,53 @@ 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 (f1 varchar(1));
+INSERT INTO t1 VALUES ('v'),('s');
+CREATE TABLE t2 (f1_key varchar(1), KEY (f1_key));
+INSERT INTO t2 VALUES ('j'),('v'),('c'),('m'),('d'),
+('d'),('y'),('t'),('d'),('s');
+SELECT table1.f1, table2.f1_key
+FROM t1 AS table1, t2 AS table2
+WHERE EXISTS
+(
+SELECT DISTINCT f1_key
+FROM t2
+WHERE f1_key != table2.f1_key AND f1_key >= table1.f1 );
+f1 f1_key
+v j
+s j
+v v
+s v
+v c
+s c
+v m
+s m
+v d
+s d
+v d
+s d
+v y
+s y
+v t
+s t
+v d
+s d
+v s
+s s
+explain SELECT table1.f1, table2.f1_key
+FROM t1 AS table1, t2 AS table2
+WHERE EXISTS
+(
+SELECT DISTINCT f1_key
+FROM t2
+WHERE f1_key != table2.f1_key AND f1_key >= table1.f1 );
+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 index NULL f1_key 4 NULL 10 Using where; Using index; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY t2 range f1_key f1_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-24 13:47:09 +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-20110624134709-97mfqzgkte61a8ww.bundle
| Thread |
|---|
| • bzr commit into mysql-trunk branch (guilhem.bichot:3391) Bug#12616253 | Guilhem Bichot | 25 Jun |
