diff options
| author | Sergey Petrunya <psergey@askmonty.org> | 2012-01-25 18:33:57 +0400 |
|---|---|---|
| committer | Sergey Petrunya <psergey@askmonty.org> | 2012-01-25 18:33:57 +0400 |
| commit | 73cc529b51dd4262e81df86491e3f2803946339c (patch) | |
| tree | 2706fa7435c85b4c80e9161d75876204441a32e3 /mysql-test/r | |
| parent | e10816118a35cdb67cd9e1e7b671c946741d3f7c (diff) | |
| download | mariadb-git-73cc529b51dd4262e81df86491e3f2803946339c.tar.gz | |
BUG#920255: Wrong result (extra rows) with loosescan and IN subquery
The problem was that LooseScan execution code assumed that tab->key holds
the index used for looseScan. This is only true when range or full index
scan are used. In case of ref access, the index is in tab->ref.key (and
tab->index==0 which explains how LooseScan passed tests with ref access: they
used one index)
Fixed by setting/using loosescan_key, which always the correct index#.
Diffstat (limited to 'mysql-test/r')
| -rw-r--r-- | mysql-test/r/subselect_sj.result | 34 | ||||
| -rw-r--r-- | mysql-test/r/subselect_sj_jcl6.result | 34 |
2 files changed, 68 insertions, 0 deletions
diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result index f5c3f84bcd7..9e81a0fa1b5 100644 --- a/mysql-test/r/subselect_sj.result +++ b/mysql-test/r/subselect_sj.result @@ -2169,4 +2169,38 @@ WHERE c = b AND b = a a COUNT(*) NULL 0 DROP TABLE t1, t2, t3; +# +# BUG#920255: Wrong result (extra rows) with loosescan and IN subquery +# +CREATE TABLE t1 ( a INT PRIMARY KEY, b INT, KEY(b) ); +INSERT INTO t1 VALUES +(1,2),(2,1),(3,3),(4,2),(5,5), +(6,3),(7,1),(8,4),(9,3),(10,2); +CREATE TABLE t2 ( c INT, d INT, UNIQUE KEY(c) ); +INSERT INTO t2 VALUES +(1,2),(2,1),(3,3),(4,2),(5,5),(6,3),(7,1); +SELECT a, b, d FROM t1, t2 +WHERE ( b, d ) IN +( SELECT b, d FROM t1, t2 WHERE b = c ); +a b d +2 1 2 +7 1 2 +2 1 2 +7 1 2 +1 2 1 +4 2 1 +10 2 1 +1 2 1 +4 2 1 +10 2 1 +3 3 3 +6 3 3 +9 3 3 +3 3 3 +6 3 3 +9 3 3 +8 4 2 +8 4 2 +5 5 5 +DROP TABLE t1, t2; set optimizer_switch=@subselect_sj_tmp; diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result index faa4140d375..f632d3bc89d 100644 --- a/mysql-test/r/subselect_sj_jcl6.result +++ b/mysql-test/r/subselect_sj_jcl6.result @@ -2183,6 +2183,40 @@ WHERE c = b AND b = a a COUNT(*) NULL 0 DROP TABLE t1, t2, t3; +# +# BUG#920255: Wrong result (extra rows) with loosescan and IN subquery +# +CREATE TABLE t1 ( a INT PRIMARY KEY, b INT, KEY(b) ); +INSERT INTO t1 VALUES +(1,2),(2,1),(3,3),(4,2),(5,5), +(6,3),(7,1),(8,4),(9,3),(10,2); +CREATE TABLE t2 ( c INT, d INT, UNIQUE KEY(c) ); +INSERT INTO t2 VALUES +(1,2),(2,1),(3,3),(4,2),(5,5),(6,3),(7,1); +SELECT a, b, d FROM t1, t2 +WHERE ( b, d ) IN +( SELECT b, d FROM t1, t2 WHERE b = c ); +a b d +1 2 1 +1 2 1 +2 1 2 +2 1 2 +3 3 3 +3 3 3 +4 2 1 +4 2 1 +5 5 5 +6 3 3 +6 3 3 +7 1 2 +7 1 2 +8 4 2 +8 4 2 +9 3 3 +9 3 3 +10 2 1 +10 2 1 +DROP TABLE t1, t2; set optimizer_switch=@subselect_sj_tmp; # # BUG#49129: Wrong result with IN-subquery with join_cache_level=6 and firstmatch=off |