diff options
author | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-05-04 18:00:34 +0000 |
---|---|---|
committer | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-05-04 18:00:34 +0000 |
commit | 3f619478f796eddbba6e39502fe941b285dd97b1 (patch) | |
tree | e2c7b5777f728320e5b5542b6213fd3591ba51e2 /mysql-test/main/except.test | |
parent | Initial commit. (diff) | |
download | mariadb-upstream.tar.xz mariadb-upstream.zip |
Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'mysql-test/main/except.test')
-rw-r--r-- | mysql-test/main/except.test | 99 |
1 files changed, 99 insertions, 0 deletions
diff --git a/mysql-test/main/except.test b/mysql-test/main/except.test new file mode 100644 index 00000000..090826ce --- /dev/null +++ b/mysql-test/main/except.test @@ -0,0 +1,99 @@ +# For explain +--source include/default_optimizer_switch.inc +set @@join_buffer_size=256*1024; + +create table t1 (a int, b int) engine=MyISAM; +create table t2 (c int, d int) engine=MyISAM; +insert into t1 values (1,1),(2,2); +insert into t2 values (2,2),(3,3); + +(select a,b from t1) except (select c,d from t2); +EXPLAIN (select a,b from t1) except (select c,d from t2); +EXPLAIN extended (select a,b from t1) except (select c,d from t2); +EXPLAIN extended select * from ((select a,b from t1) except (select c,d from t2)) a; +EXPLAIN format=json (select a,b from t1) except (select c,d from t2); + +--source include/analyze-format.inc +ANALYZE format=json (select a,b from t1) except (select c,d from t2); +--source include/analyze-format.inc +ANALYZE format=json select * from ((select a,b from t1) except (select c,d from t2)) a; +select * from ((select a,b from t1) except (select c,d from t2)) a; + +prepare stmt from "(select a,b from t1) except (select c,d from t2)"; +execute stmt; +execute stmt; + +prepare stmt from "select * from ((select a,b from t1) except (select c,d from t2)) a"; +execute stmt; +execute stmt; + +drop tables t1,t2; + + +create table t1 (a int, b int) engine=MyISAM; +create table t2 (c int, d int) engine=MyISAM; +create table t3 (e int, f int) engine=MyISAM; +create table t4 (g int, h int) engine=MyISAM; +insert into t1 values (1,1),(2,2); +insert into t2 values (2,2),(3,3); +insert into t3 values (4,4),(5,5); +insert into t4 values (4,4),(7,7); + +(select a,b,e,f from t1,t3) except (select c,d,g,h from t2,t4); +EXPLAIN (select a,b,e,f from t1,t3) except (select c,d,g,h from t2,t4); +EXPLAIN (select a,b,e,f from t1,t3) except (select c,d,g,h from t2,t4); +EXPLAIN extended select * from ((select a,b,e,f from t1,t3) except (select c,d,g,h from t2,t4)) a; +EXPLAIN format=json (select a,b,e,f from t1,t3) except (select c,d,g,h from t2,t4); + +--source include/analyze-format.inc +ANALYZE format=json (select a,b,e,f from t1,t3) except (select c,d,g,h from t2,t4); +--source include/analyze-format.inc +ANALYZE format=json select * from ((select a,b,e,f from t1,t3) except +(select c,d,g,h from t2,t4)) a; +select * from ((select a,b,e,f from t1,t3) except (select c,d,g,h from t2,t4)) a; + +prepare stmt from "(select a,b,e,f from t1,t3) except (select c,d,g,h from t2,t4)"; +execute stmt; +execute stmt; + +prepare stmt from "select * from ((select a,b,e,f from t1,t3) except (select c,d,g,h from t2,t4)) a"; +execute stmt; +execute stmt; + +drop tables t1,t2,t3,t4; + +select 1 as a from dual except select 1 from dual; +(select 1 from dual) except (select 1 from dual); +--error ER_PARSE_ERROR +(select 1 from dual into @v) except (select 1 from dual); +--error ER_PARSE_ERROR +select 1 from dual ORDER BY 1 except select 1 from dual; + +select 1 as a from dual union all select 1 from dual; + + +create table t1 (a int, b blob, a1 int, b1 blob) engine=MyISAM; +create table t2 (c int, d blob, c1 int, d1 blob) engine=MyISAM; +insert into t1 values (1,"ddd", 1, "sdfrrwwww"),(2, "fgh", 2, "dffggtt"); +insert into t2 values (2, "fgh", 2, "dffggtt"),(3, "ffggddd", 3, "dfgg"); + +(select a,b,b1 from t1) except (select c,d,d1 from t2); +# make sure that blob is used +create table t3 (select a,b,b1 from t1) except (select c,d,d1 from t2); +show create table t3; + +drop tables t1,t2,t3; + +--echo # +--echo # MDEV-13723: Server crashes in ha_heap::find_unique_row or +--echo # Assertion `0' failed in st_select_lex_unit::optimize with INTERSECT +--echo # +CREATE TABLE t (i INT); +INSERT INTO t VALUES (1),(2); + +SELECT * FROM t WHERE i != ANY ( SELECT 3 EXCEPT SELECT 3 ); + +drop table t; + + +--echo # End of 10.3 tests |