diff options
| author | Varun Gupta <varunraiko1803@gmail.com> | 2018-07-27 11:34:34 +0530 |
|---|---|---|
| committer | Varun Gupta <varunraiko1803@gmail.com> | 2018-07-27 11:34:34 +0530 |
| commit | bd0b368119b48ffbb1e5ab3cd2887270c5c6840e (patch) | |
| tree | 76e7e9ed294cc84f5fe64fe3a4b468c7d54a9d99 /mysql-test | |
| parent | 37dee22d27c55c14f0be7005c3e5bdbb09e1fc92 (diff) | |
| download | mariadb-git-bd0b368119b48ffbb1e5ab3cd2887270c5c6840e.tar.gz | |
Fix added along with a test for a case missed in the patch for MDEV-16751
Diffstat (limited to 'mysql-test')
| -rw-r--r-- | mysql-test/r/subselect_mat.result | 17 | ||||
| -rw-r--r-- | mysql-test/r/subselect_sj_mat.result | 17 | ||||
| -rw-r--r-- | mysql-test/t/subselect_sj_mat.test | 9 |
3 files changed, 43 insertions, 0 deletions
diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result index 7f39f1fb788..eca3b760b65 100644 --- a/mysql-test/r/subselect_mat.result +++ b/mysql-test/r/subselect_mat.result @@ -2436,6 +2436,23 @@ a drop procedure prepare_data; set @@optimizer_switch= @save_optimizer_switch; drop table t1,t2,t3; +CREATE TABLE t1 ( id int NOT NULL, key(id)); +INSERT INTO t1 VALUES (11),(12),(13),(14),(15),(16),(17),(18),(19); +CREATE TABLE t2 (i1 int NOT NULL, i2 int NOT NULL); +INSERT INTO t2 VALUES (11,11),(12,12),(13,13); +CREATE VIEW v1 AS SELECT t2.i1 FROM t2 where t2.i1 = t2.i2; +explain SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 +1 PRIMARY t1 ref id id 4 test.t2.i1 2 Using index +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); +1 +1 +1 +1 +drop table t1,t2; +drop view v1; # End of 5.5 tests set @subselect_mat_test_optimizer_switch_value=null; set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=off'; diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result index 44006eadfc6..180c182a51a 100644 --- a/mysql-test/r/subselect_sj_mat.result +++ b/mysql-test/r/subselect_sj_mat.result @@ -2476,4 +2476,21 @@ a drop procedure prepare_data; set @@optimizer_switch= @save_optimizer_switch; drop table t1,t2,t3; +CREATE TABLE t1 ( id int NOT NULL, key(id)); +INSERT INTO t1 VALUES (11),(12),(13),(14),(15),(16),(17),(18),(19); +CREATE TABLE t2 (i1 int NOT NULL, i2 int NOT NULL); +INSERT INTO t2 VALUES (11,11),(12,12),(13,13); +CREATE VIEW v1 AS SELECT t2.i1 FROM t2 where t2.i1 = t2.i2; +explain SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 +1 PRIMARY t1 ref id id 4 test.t2.i1 2 Using index +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 3 Using where +SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); +1 +1 +1 +1 +drop table t1,t2; +drop view v1; # End of 5.5 tests diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test index 794545b53a7..c82c1e7acec 100644 --- a/mysql-test/t/subselect_sj_mat.test +++ b/mysql-test/t/subselect_sj_mat.test @@ -2221,4 +2221,13 @@ drop procedure prepare_data; set @@optimizer_switch= @save_optimizer_switch; drop table t1,t2,t3; +CREATE TABLE t1 ( id int NOT NULL, key(id)); +INSERT INTO t1 VALUES (11),(12),(13),(14),(15),(16),(17),(18),(19); +CREATE TABLE t2 (i1 int NOT NULL, i2 int NOT NULL); +INSERT INTO t2 VALUES (11,11),(12,12),(13,13); +CREATE VIEW v1 AS SELECT t2.i1 FROM t2 where t2.i1 = t2.i2; +explain SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); +SELECT 1 FROM t1 where t1.id IN (SELECT v1.i1 from v1); +drop table t1,t2; +drop view v1; --echo # End of 5.5 tests |