diff options
| author | Sergey Petrunya <psergey@askmonty.org> | 2011-01-14 13:51:30 +0300 |
|---|---|---|
| committer | Sergey Petrunya <psergey@askmonty.org> | 2011-01-14 13:51:30 +0300 |
| commit | b1e0e22e6d4475e69e03f9219d989c4c818dfb97 (patch) | |
| tree | 20ce344344b411160e8a8f1eea4057ab8566fa24 /mysql-test | |
| parent | da5db2e19f775efcda5ea002670ff866e499ae64 (diff) | |
| download | mariadb-git-b1e0e22e6d4475e69e03f9219d989c4c818dfb97.tar.gz | |
Testcase backport: BUG#46548 IN-subqueries return 0 rows with materialization=on
(the bug itself was fixed during the subquery code backport)
Diffstat (limited to 'mysql-test')
| -rw-r--r-- | mysql-test/r/subselect_mat.result | 27 | ||||
| -rw-r--r-- | mysql-test/t/subselect_mat.test | 23 |
2 files changed, 50 insertions, 0 deletions
diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result index dab00e3be19..e08872a648b 100644 --- a/mysql-test/r/subselect_mat.result +++ b/mysql-test/r/subselect_mat.result @@ -1344,3 +1344,30 @@ EXECUTE st1; c2 c2 10 10 DROP TABLE t1, t2; +# +# Testcase backport: BUG#46548 IN-subqueries return 0 rows with materialization=on +# +CREATE TABLE t1 ( +pk int, +a varchar(1), +b varchar(4), +c varchar(4), +d varchar(4), +PRIMARY KEY (pk) +); +INSERT INTO t1 VALUES (1,'o','ffff','ffff','ffoo'),(2,'f','ffff','ffff','ffff'); +CREATE TABLE t2 LIKE t1; +INSERT INTO t2 VALUES (1,'i','iiii','iiii','iiii'),(2,'f','ffff','ffff','ffff'); +SET @@optimizer_switch='default,semijoin=on,materialization=on'; +EXPLAIN SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +1 PRIMARY subselect2 eq_ref unique_key unique_key 5 func 1 +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using MRR +SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0); +pk +2 +SELECT pk FROM t1 WHERE (b,c,d) IN (SELECT b,c,d FROM t2 WHERE pk > 0); +pk +2 +DROP TABLE t1, t2; diff --git a/mysql-test/t/subselect_mat.test b/mysql-test/t/subselect_mat.test index 81a33c95424..e564ed36040 100644 --- a/mysql-test/t/subselect_mat.test +++ b/mysql-test/t/subselect_mat.test @@ -988,3 +988,26 @@ EXECUTE st1; EXECUTE st1; DROP TABLE t1, t2; + +--echo # +--echo # Testcase backport: BUG#46548 IN-subqueries return 0 rows with materialization=on +--echo # +CREATE TABLE t1 ( + pk int, + a varchar(1), + b varchar(4), + c varchar(4), + d varchar(4), + PRIMARY KEY (pk) +); +INSERT INTO t1 VALUES (1,'o','ffff','ffff','ffoo'),(2,'f','ffff','ffff','ffff'); + +CREATE TABLE t2 LIKE t1; +INSERT INTO t2 VALUES (1,'i','iiii','iiii','iiii'),(2,'f','ffff','ffff','ffff'); + +SET @@optimizer_switch='default,semijoin=on,materialization=on'; +EXPLAIN SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0); +SELECT pk FROM t1 WHERE (a) IN (SELECT a FROM t2 WHERE pk > 0); +SELECT pk FROM t1 WHERE (b,c,d) IN (SELECT b,c,d FROM t2 WHERE pk > 0); +DROP TABLE t1, t2; + |