1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
|
# 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);
--replace_regex /"r_total_time_ms": [0-9e\.\-+]*,/"r_total_time_ms": "REPLACED",/
ANALYZE format=json (select a,b from t1) except (select c,d from t2);
--replace_regex /"r_total_time_ms": [0-9e\.\-+]*,/"r_total_time_ms": "REPLACED",/
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);
--replace_regex /"r_total_time_ms": [0-9e\.\-+]*,/"r_total_time_ms": "REPLACED",/
ANALYZE format=json (select a,b,e,f from t1,t3) except (select c,d,g,h from t2,t4);
--replace_regex /"r_total_time_ms": [0-9e\.\-+]*,/"r_total_time_ms": "REPLACED",/
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;
--error ER_PARSE_ERROR
select 1 from dual except 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
|
