diff options
author | unknown <timour@askmonty.org> | 2011-04-04 12:09:43 +0300 |
---|---|---|
committer | unknown <timour@askmonty.org> | 2011-04-04 12:09:43 +0300 |
commit | 5ed997904e429e20e61dbcacb2aea2f36416e1f6 (patch) | |
tree | 7a6745121e315045c4fe8a0a5c85629ac9ec84b8 /mysql-test/r/subselect4.result | |
parent | 619a16bffccd190a43c9b3c2d136f30215d29bdf (diff) | |
download | mariadb-git-5ed997904e429e20e61dbcacb2aea2f36416e1f6.tar.gz |
Test case for LP BUG#715062.
There is no patch because the bug cannot be reproduced any more.
Diffstat (limited to 'mysql-test/r/subselect4.result')
-rw-r--r-- | mysql-test/r/subselect4.result | 121 |
1 files changed, 121 insertions, 0 deletions
diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result index 50fd75df285..8cb56abe593 100644 --- a/mysql-test/r/subselect4.result +++ b/mysql-test/r/subselect4.result @@ -1454,3 +1454,124 @@ WHERE SQ2_t2.f3 AND alias1.f1) ORDER BY f3 ; f2 drop table t1,t2; +# +# LP BUG#715062: Wrong result with VIEW + UNION + subquery in maria-5.3-mwl89 +# +create table t1 (f1 int); +create table t2 (f2 int); +create table t3 (f3 int); +insert into t1 values (2); +insert into t2 values (2); +insert into t3 values (7); +CREATE VIEW v1 AS SELECT 2 UNION SELECT 2 ; +CREATE VIEW v2 AS SELECT * from t1 UNION SELECT * from t2 ; +set @save_optimizer_switch=@@optimizer_switch; +SET @@optimizer_switch = 'in_to_exists=off,semijoin=off,materialization=on'; +EXPLAIN +SELECT 'bug' FROM DUAL WHERE ( 5 ) IN ( SELECT * FROM v1 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +4 UNION NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL +SELECT 'bug' FROM DUAL WHERE ( 5 ) IN ( SELECT * FROM v1 ); +bug +EXPLAIN +SELECT ( 5 ) IN ( SELECT * FROM v1 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +4 UNION NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL +SELECT ( 5 ) IN ( SELECT * FROM v1 ); +( 5 ) IN ( SELECT * FROM v1 ) +0 +EXPLAIN +SELECT 'bug' FROM DUAL WHERE ( 5 ) IN (SELECT * FROM v2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +3 DERIVED t1 system NULL NULL NULL NULL 1 +4 UNION t2 system NULL NULL NULL NULL 1 +NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL +SELECT 'bug' FROM DUAL WHERE ( 5 ) IN (SELECT * FROM v2); +bug +EXPLAIN +SELECT 'bug' FROM t3 WHERE ( 5 ) IN (SELECT * FROM v2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 system NULL NULL NULL NULL 1 +2 SUBQUERY <derived3> system NULL NULL NULL NULL 1 +3 DERIVED t1 system NULL NULL NULL NULL 1 +4 UNION t2 system NULL NULL NULL NULL 1 +NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL +SELECT 'bug' FROM t3 WHERE ( 5 ) IN (SELECT * FROM v2); +bug +EXPLAIN +SELECT ( 5 ) IN ( SELECT * FROM v2 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +3 DERIVED t1 system NULL NULL NULL NULL 1 +4 UNION t2 system NULL NULL NULL NULL 1 +NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL +SELECT ( 5 ) IN ( SELECT * FROM v2 ); +( 5 ) IN ( SELECT * FROM v2 ) +0 +SET @@optimizer_switch = 'in_to_exists=on,semijoin=off,materialization=off'; +EXPLAIN +SELECT 'bug' FROM DUAL WHERE ( 5 ) IN ( SELECT * FROM v1 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +4 UNION NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL +SELECT 'bug' FROM DUAL WHERE ( 5 ) IN ( SELECT * FROM v1 ); +bug +EXPLAIN +SELECT ( 5 ) IN ( SELECT * FROM v1 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +4 UNION NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL +SELECT ( 5 ) IN ( SELECT * FROM v1 ); +( 5 ) IN ( SELECT * FROM v1 ) +0 +EXPLAIN +SELECT 'bug' FROM DUAL WHERE ( 5 ) IN (SELECT * FROM v2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +3 DERIVED t1 system NULL NULL NULL NULL 1 +4 UNION t2 system NULL NULL NULL NULL 1 +NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL +SELECT 'bug' FROM DUAL WHERE ( 5 ) IN (SELECT * FROM v2); +bug +EXPLAIN +SELECT 'bug' FROM t3 WHERE ( 5 ) IN (SELECT * FROM v2); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t3 system NULL NULL NULL NULL 1 +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +3 DERIVED t1 system NULL NULL NULL NULL 1 +4 UNION t2 system NULL NULL NULL NULL 1 +NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL +SELECT 'bug' FROM t3 WHERE ( 5 ) IN (SELECT * FROM v2); +bug +EXPLAIN +SELECT ( 5 ) IN ( SELECT * FROM v2 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +3 DERIVED t1 system NULL NULL NULL NULL 1 +4 UNION t2 system NULL NULL NULL NULL 1 +NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL +SELECT ( 5 ) IN ( SELECT * FROM v2 ); +( 5 ) IN ( SELECT * FROM v2 ) +0 +set @@optimizer_switch=@save_optimizer_switch; +drop table t1,t2,t3; +drop view v1,v2; |