diff options
Diffstat (limited to 'mysql-test/t/subselect_sj.test')
-rw-r--r-- | mysql-test/t/subselect_sj.test | 28 |
1 files changed, 27 insertions, 1 deletions
diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test index 2a0197a3047..4f9331be476 100644 --- a/mysql-test/t/subselect_sj.test +++ b/mysql-test/t/subselect_sj.test @@ -3,7 +3,7 @@ # --disable_warnings drop table if exists t0, t1, t2, t3, t4, t10, t11, t12; -drop view if exists v1, v2; +drop view if exists v1, v2, v3, v4; drop procedure if exists p1; --enable_warnings @@ -1459,6 +1459,32 @@ ON (t1.f3) IN ( SELECT f4 FROM t1 ) EXECUTE st1; DROP TABLE t1,t2,t3; +--echo # +--echo # BUG#803457: Wrong result with semijoin + view + outer join in maria-5.3-subqueries-mwl90 +--echo # (Original testcase) +--echo # + +CREATE TABLE t1 (f1 int, f2 int ); +INSERT INTO t1 VALUES (2,0),(4,0),(0,NULL); + +CREATE TABLE t2 (f2 int, f3 int ); +INSERT INTO t2 VALUES (NULL,NULL),(0,0); + +CREATE TABLE t3 ( f1 int, f3 int ); +INSERT INTO t3 VALUES (2,0),(4,0),(0,NULL),(4,0),(8,0); + +CREATE TABLE t4 ( f2 int, KEY (f2) ); +INSERT INTO t4 VALUES (0),(NULL); + +CREATE VIEW v4 AS SELECT DISTINCT f2 FROM t4 ; + +--echo # The following must not have outer joins: +explain extended +SELECT * FROM t1 NATURAL LEFT JOIN (t2, t3) WHERE t2.f3 IN (SELECT * FROM t4); +SELECT * FROM t1 NATURAL LEFT JOIN (t2, t3) WHERE t2.f3 IN (SELECT * FROM t4); + +drop view v4; +drop table t1, t2, t3, t4; # The following command must be the last one the file set optimizer_switch=@subselect_sj_tmp; |