diff options
Diffstat (limited to 'mysql-test')
| -rw-r--r-- | mysql-test/r/derived_view.result | 49 | ||||
| -rw-r--r-- | mysql-test/r/join_outer.result | 8 | ||||
| -rw-r--r-- | mysql-test/r/join_outer_jcl6.result | 8 | ||||
| -rw-r--r-- | mysql-test/r/view.result | 17 | ||||
| -rw-r--r-- | mysql-test/t/derived_view.test | 61 | ||||
| -rw-r--r-- | mysql-test/t/view.test | 21 |
6 files changed, 156 insertions, 8 deletions
diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result index a4f7a71dcb5..030b8798fad 100644 --- a/mysql-test/r/derived_view.result +++ b/mysql-test/r/derived_view.result @@ -2105,6 +2105,55 @@ a 4 drop table t1,t2; # +# MDEV-3873: Wrong result (extra rows) with NOT IN and +# a subquery from a MERGE view +# +CREATE TABLE t1 (a INT NOT NULL) ENGINE=MyISAM; +INSERT INTO t1 VALUES (4),(7),(0); +CREATE TABLE t2 (b INT NOT NULL) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1),(2); +CREATE TABLE t3 (c INT NOT NULL) ENGINE=MyISAM; +INSERT INTO t3 VALUES (4),(6),(3); +CREATE TABLE t4 (d INT NOT NULL) ENGINE=MyISAM; +INSERT INTO t4 VALUES (4),(5),(3); +CREATE TABLE tv (e INT NOT NULL) ENGINE=MyISAM; +INSERT INTO tv VALUES (1),(3); +CREATE ALGORITHM=TEMPTABLE VIEW v_temptable AS SELECT * FROM tv; +CREATE ALGORITHM=MERGE VIEW v_merge AS SELECT * FROM tv; +SELECT * FROM t1, t2 +WHERE a NOT IN ( SELECT e FROM t3 LEFT JOIN v_temptable ON (c = e) WHERE c <> b ) AND a < b; +a b +SELECT * FROM t1, t2 +WHERE a NOT IN ( SELECT e FROM t3 LEFT JOIN v_merge ON (c = e) WHERE c <> b ) AND a < b; +a b +SELECT * FROM t1, t2 +WHERE a NOT IN ( SELECT e FROM t3 LEFT JOIN (SELECT * FROM tv) as derived ON (c = e) WHERE c <> b ) AND a < b; +a b +drop view v_temptable, v_merge; +drop table t1,t2,t3,t4,tv; +# +# MDEV-3912: Wrong result (extra rows) with FROM subquery inside +# ALL subquery, LEFT JOIN, derived_merge. +# (duplicate of MDEV-3873 (above)) +# +SET @save3912_optimizer_switch=@@optimizer_switch; +SET optimizer_switch = 'derived_merge=on,in_to_exists=on'; +CREATE TABLE t1 (a INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (4),(8); +CREATE TABLE t2 (b INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (7),(0); +CREATE TABLE t3 (c INT, d INT NOT NULL) ENGINE=MyISAM; +INSERT INTO t3 VALUES (0,4),(8,6); +SELECT * FROM t1 +WHERE a >= ALL ( +SELECT d FROM t2 LEFT JOIN ( SELECT * FROM t3 ) AS alias ON ( c = b ) +WHERE b >= a +); +a +8 +set optimizer_switch=@save3912_optimizer_switch; +drop table t1, t2, t3; +# # end of 5.3 tests # set optimizer_switch=@exit_optimizer_switch; diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index 17bc705b4f3..4541cdbc752 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -1770,10 +1770,10 @@ SELECT t1.pk FROM t2 LEFT JOIN t1 ON t2.pk = t1.a WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5 ORDER BY t1.pk; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 const PRIMARY,idx PRIMARY 4 const 1 100.00 +1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 100.00 1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 100.00 Using index Warnings: -Note 1003 select 5 AS `pk` from `test`.`t2` join `test`.`t1` where ((((1 between 5 and 6) and isnull(5)) or 1)) order by 5 +Note 1003 select 5 AS `pk` from `test`.`t2` join `test`.`t1` where (1) order by 5 SELECT t1.pk FROM t2 LEFT JOIN t1 ON t2.pk = t1.a WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5 ORDER BY t1.pk; @@ -1809,10 +1809,10 @@ SELECT t1.b, t2.c, t2.d FROM t2 LEFT JOIN t1 ON t2.c = t1.a WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5 ORDER BY t1.b; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ref PRIMARY,idx idx 4 const 2 100.00 Using where; Using filesort +1 SIMPLE t1 ref idx idx 4 const 2 100.00 Using where 1 SIMPLE t2 ref c c 5 test.t1.a 2 100.00 Warnings: -Note 1003 select `test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` join `test`.`t1` where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and (((`test`.`t1`.`pk` between 5 and 6) and isnull(`test`.`t1`.`b`)) or (`test`.`t1`.`b` = 5))) order by `test`.`t1`.`b` +Note 1003 select `test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` join `test`.`t1` where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and (`test`.`t1`.`b` = 5)) order by `test`.`t1`.`b` SELECT t1.b, t2.c, t2.d FROM t2 LEFT JOIN t1 ON t2.c = t1.a WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5 ORDER BY t1.b; diff --git a/mysql-test/r/join_outer_jcl6.result b/mysql-test/r/join_outer_jcl6.result index 981e8002ea0..3272186d12f 100644 --- a/mysql-test/r/join_outer_jcl6.result +++ b/mysql-test/r/join_outer_jcl6.result @@ -1781,10 +1781,10 @@ SELECT t1.pk FROM t2 LEFT JOIN t1 ON t2.pk = t1.a WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5 ORDER BY t1.pk; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 const PRIMARY,idx PRIMARY 4 const 1 100.00 +1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 100.00 1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 100.00 Using index Warnings: -Note 1003 select 5 AS `pk` from `test`.`t2` join `test`.`t1` where ((((1 between 5 and 6) and isnull(5)) or 1)) order by 5 +Note 1003 select 5 AS `pk` from `test`.`t2` join `test`.`t1` where (1) order by 5 SELECT t1.pk FROM t2 LEFT JOIN t1 ON t2.pk = t1.a WHERE t1.b BETWEEN 5 AND 6 AND t1.pk IS NULL OR t1.pk = 5 ORDER BY t1.pk; @@ -1820,10 +1820,10 @@ SELECT t1.b, t2.c, t2.d FROM t2 LEFT JOIN t1 ON t2.c = t1.a WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5 ORDER BY t1.b; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 ref PRIMARY,idx idx 4 const 2 100.00 Using where; Using filesort +1 SIMPLE t1 ref idx idx 4 const 2 100.00 Using where 1 SIMPLE t2 ref c c 5 test.t1.a 2 100.00 Warnings: -Note 1003 select `test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` join `test`.`t1` where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and (((`test`.`t1`.`pk` between 5 and 6) and isnull(`test`.`t1`.`b`)) or (`test`.`t1`.`b` = 5))) order by `test`.`t1`.`b` +Note 1003 select `test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`test`.`t2`.`d` AS `d` from `test`.`t2` join `test`.`t1` where ((`test`.`t2`.`c` = `test`.`t1`.`a`) and (`test`.`t1`.`b` = 5)) order by `test`.`t1`.`b` SELECT t1.b, t2.c, t2.d FROM t2 LEFT JOIN t1 ON t2.c = t1.a WHERE t1.pk BETWEEN 5 AND 6 AND t1.b IS NULL OR t1.b = 5 ORDER BY t1.b; diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 74c36a2d394..4172c1620bd 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -4634,6 +4634,23 @@ f2 f1 7 NULL 8 NULL drop tables t1,t2; +# +# MDEV-3876 Wrong result (extra rows) with ALL subquery +# from a MERGE view (duplicate of MDEV-3873) +# +CREATE TABLE t1 (a INT NOT NULL) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (b INT NOT NULL) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1),(3); +CREATE OR REPLACE ALGORITHM=MERGE VIEW v1 AS SELECT * FROM t2; +SELECT a FROM t1 AS alias +WHERE a >= ALL ( +SELECT b FROM t1 LEFT JOIN v1 ON (a = b) +WHERE a = alias.a ); +a +1 +drop view v1; +drop table t1,t2; # ----------------------------------------------------------------- # -- End of 5.3 tests. # ----------------------------------------------------------------- diff --git a/mysql-test/t/derived_view.test b/mysql-test/t/derived_view.test index 30811be2934..c7705294ef2 100644 --- a/mysql-test/t/derived_view.test +++ b/mysql-test/t/derived_view.test @@ -1451,6 +1451,67 @@ select * from t1; drop table t1,t2; --echo # +--echo # MDEV-3873: Wrong result (extra rows) with NOT IN and +--echo # a subquery from a MERGE view +--echo # + +CREATE TABLE t1 (a INT NOT NULL) ENGINE=MyISAM; +INSERT INTO t1 VALUES (4),(7),(0); + +CREATE TABLE t2 (b INT NOT NULL) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1),(2); + +CREATE TABLE t3 (c INT NOT NULL) ENGINE=MyISAM; +INSERT INTO t3 VALUES (4),(6),(3); + +CREATE TABLE t4 (d INT NOT NULL) ENGINE=MyISAM; +INSERT INTO t4 VALUES (4),(5),(3); + +CREATE TABLE tv (e INT NOT NULL) ENGINE=MyISAM; +INSERT INTO tv VALUES (1),(3); + +CREATE ALGORITHM=TEMPTABLE VIEW v_temptable AS SELECT * FROM tv; +CREATE ALGORITHM=MERGE VIEW v_merge AS SELECT * FROM tv; + +SELECT * FROM t1, t2 +WHERE a NOT IN ( SELECT e FROM t3 LEFT JOIN v_temptable ON (c = e) WHERE c <> b ) AND a < b; + +SELECT * FROM t1, t2 +WHERE a NOT IN ( SELECT e FROM t3 LEFT JOIN v_merge ON (c = e) WHERE c <> b ) AND a < b; + +SELECT * FROM t1, t2 +WHERE a NOT IN ( SELECT e FROM t3 LEFT JOIN (SELECT * FROM tv) as derived ON (c = e) WHERE c <> b ) AND a < b; + +drop view v_temptable, v_merge; +drop table t1,t2,t3,t4,tv; + +--echo # +--echo # MDEV-3912: Wrong result (extra rows) with FROM subquery inside +--echo # ALL subquery, LEFT JOIN, derived_merge. +--echo # (duplicate of MDEV-3873 (above)) +--echo # + +SET @save3912_optimizer_switch=@@optimizer_switch; +SET optimizer_switch = 'derived_merge=on,in_to_exists=on'; + +CREATE TABLE t1 (a INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (4),(8); + +CREATE TABLE t2 (b INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (7),(0); + +CREATE TABLE t3 (c INT, d INT NOT NULL) ENGINE=MyISAM; +INSERT INTO t3 VALUES (0,4),(8,6); + +SELECT * FROM t1 +WHERE a >= ALL ( +SELECT d FROM t2 LEFT JOIN ( SELECT * FROM t3 ) AS alias ON ( c = b ) +WHERE b >= a +); +set optimizer_switch=@save3912_optimizer_switch; +drop table t1, t2, t3; + +--echo # --echo # end of 5.3 tests --echo # diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index 5f3bf031f8c..2a230e65493 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -4575,6 +4575,27 @@ SELECT * FROM ( drop tables t1,t2; +--echo # +--echo # MDEV-3876 Wrong result (extra rows) with ALL subquery +--echo # from a MERGE view (duplicate of MDEV-3873) +--echo # + +CREATE TABLE t1 (a INT NOT NULL) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1),(2); + +CREATE TABLE t2 (b INT NOT NULL) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1),(3); + +CREATE OR REPLACE ALGORITHM=MERGE VIEW v1 AS SELECT * FROM t2; + +SELECT a FROM t1 AS alias +WHERE a >= ALL ( +SELECT b FROM t1 LEFT JOIN v1 ON (a = b) +WHERE a = alias.a ); + +drop view v1; +drop table t1,t2; + --echo # ----------------------------------------------------------------- --echo # -- End of 5.3 tests. --echo # ----------------------------------------------------------------- |