oceanbase

1
--disable_query_log
2
set @@session.explicit_defaults_for_timestamp=off;
3
--enable_query_log
4
# owner: yuchen.wyc
5
# owner group: SQL1
6
# Test of functions intersect
7

8
--disable_warnings
9
drop table if exists t1,t2,t3,t4,t8;
10
--enable_warnings
11

12

13
create table t1(c1 int primary key, c2 int);
14
create table t2(c1 int primary key, c2 int);
15
create table t3(c1 int, c2 int primary key);
16
create table t4(c1 int primary key, c2 int);
17

18
insert into t1 values(1,1),(2,2),(3,3),(4,4),(5,5);
19
insert into t2 values(0,0),(2,2),(4,4),(6,6);
20
insert into t3 values(1,1),(3,3),(5,5),(7,7);
21
insert into t4 values(1,0),(2,0),(3,1),(4,1);
22

23
(select * from t1 where false ) intersect (select * from t2 where false);
24
--error 1064
25
(select * from t1 where false ) intersect all (select * from t2 where false);
26
(select * from t1 where false ) intersect select * from t2;
27
--error 1064
28
(select * from t1 where false ) intersect all select * from t2;
29
select * from t1 intersect (select * from t2 where false);
30
--error 1064
31
select * from t1 intersect all (select * from t2 where false);
32
(select * from t1 where false) intersect (select * from t2 where false) intersect (select * from t3 where false);
33
--error 1064
34
(select * from t1 where false) intersect all (select * from t2 where false) intersect (select * from t3 where false);
35

36

37
# 
38
select c1 from t1 intersect select c1 from t1;
39
--error 1064
40
select c1 from t1 intersect all select c1 from t1;
41
select c1 from t1 intersect select c1 from t4;
42
--error 1064
43
select c1 from t1 intersect all select c1 from t4;
44
select c1 from t4 intersect select c1 from t1;
45
--error 1064
46
select c1 from t4 intersect all select c1 from t1;
47
select c1 from t2 intersect select c1 from t3;
48
--error 1064
49
select c1 from t2 intersect all select c1 from t3;
50
select c1 from t1 intersect select c1 from t2;
51
--error 1064
52
select c1 from t1 intersect all select c1 from t2;
53

54

55
# distinct
56
select c2 from t4 intersect select c2 from t1;
57
--error 1064
58
select c2 from t4 intersect all select c2 from t1;
59
select c2 from t1 intersect select c2 from t4;
60
--error 1064
61
select c2 from t1 intersect all select c2 from t4;
62
select * from t1 intersect (select * from t2 union select * from t3) ;
63
--error 1064
64
select * from t1 intersect all  (select * from t2 union select * from t3) ;
65
(select * from t1 intersect select * from t2) union (select * from t1 intersect select * from t3);
66
--error 1064
67
select * from t1 intersect all (select * from t2 union select * from t3) ;
68
select c2 from t1 intersect select c2 from t3 intersect select c2 from t4;
69
--error 1064
70
select c2 from t1 intersect all select c2 from t3 intersect select c2 from t4;
71
select c2 from t1 intersect (select c2 from t3 intersect select c2 from t4);
72
--error 1064
73
select c2 from t1 intersect all (select c2 from t3 intersect select c2 from t4);
74
--disable_warnings
75
drop table if exists t5,t6;
76
--enable_warnings
77
create table t5(c1 int primary key, c2 int);
78
create table t6(c1 int primary key, c2 int);
79

80
insert into t5 values(1,1),(2,2),(3,3),(4,4),(5,5),(6,2),(7,3),(8,6),(9,6),(10,4),(11,2);
81
insert into t6 values(0,0),(2,2),(4,4),(6,6),(1,2),(3,4),(5,6);
82
select c2 from t5 intersect select c2 from t6;
83
--error 1064
84
select c2 from t5 intersect  all select c2 from t6;
85
--error 1222
86
select c2 from t5 intersect  select c1,c2 from t6;
87

88

89
create table t8 (c1 BINARY(99) ,c2 INTEGER(127) ,c3 VARBINARY(71) ,c4 BINARY(65) ,c5 BIGINT(127) ,c6 INTEGER(127) ,c7 INT(127) ,c8 INTEGER(127) ,c9 NUMERIC(64,25) ,c10 VARBINARY(55) ,c11 BIGINT(127) ,c12 VARCHAR(86) ,c13 INTEGER(127) ,c14 INT(127) ,c15 INTEGER(127) ,c16 DECIMAL(64,15) ,c17 INTEGER(127) ,c18 VARBINARY(76) ,c19 INTEGER(127) , primary key(c7,c6), index i_OkcN(c19) global, index i_ahayugXY(c10,c18) global, index i_iA(c16,c2) global)  DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin PARTITION BY KEY(c7) PARTITIONS 20;
90

91
insert into t8 values('cdxLVdvvJjcoHFBECqXKkRGoMXYgNsTpJRrqYaluQdfEeB00000',0,'RetSgyIigYXIPgBXBsmZHQZQPLWcGoELCf00000','wD00000',0,0,0,0,0.0000000000000000000000000,'ieLhJAFmNCq00000',0,'DcKPncNvJjyYcNJKFmVUVhqvgUlLfINbycifYNHjzqALoLfn00000',0,0,0,0.000000000000000,0,'MojimRsQZuotWXMuvSQVDSdCmOaloSXNLRTxMYYXaxGbtuk00000',0);
92

93
explain (select /*+USE_HASH_AGGREGATION*/ c15 from (select c15 from t8) except (select c15 from t8) order by 1 limit 1000) union all (select  t8_alias1.c10 from t8 t8_alias1 limit 1);
94
(select /*+USE_HASH_AGGREGATION*/ c15 from (select c15 from t8) except (select c15 from t8) order by 1 limit 1000) union all (select  t8_alias1.c10 from t8 t8_alias1 limit 1);
95
explain (select /*+USE_HASH_AGGREGATION*/ c15 from (select c15 from t8) intersect (select c15 from t8) order by 1 limit 1000) union all (select  t8_alias1.c10 from t8 t8_alias1 limit 1);
96
(select /*+USE_HASH_AGGREGATION*/ c15 from (select c15 from t8) intersect (select c15 from t8) order by 1 limit 1000) union all (select  t8_alias1.c10 from t8 t8_alias1 limit 1);
97

98
drop table t8;
99
create table t8 (c1 BINARY(99) ,c2 INTEGER(127) ,c3 VARBINARY(71) ,c4 BINARY(65) ,c5 BIGINT(127) ,c6 INTEGER(127) ,c7 INT(127) ,c8 INTEGER(127) ,c9 NUMERIC(64,25) ,c10 VARBINARY(55) ,c11 BIGINT(127) ,c12 VARCHAR(86) ,c13 INTEGER(127) ,c14 INT(127) ,c15 INTEGER(127) ,c16 DECIMAL(64,15) ,c17 INTEGER(127) ,c18 VARBINARY(76) ,c19 INTEGER(127) , primary key(c7,c6), index i_OkcN(c19), index i_ahayugXY(c10,c18), index i_iA(c16,c2));
100
insert into t8 values('cdxLVdvvJjcoHFBECqXKkRGoMXYgNsTpJRrqYaluQdfEeB00000',0,'RetSgyIigYXIPgBXBsmZHQZQPLWcGoELCf00000','wD00000',0,0,0,0,0.0000000000000000000000000,'ieLhJAFmNCq00000',0,'DcKPncNvJjyYcNJKFmVUVhqvgUlLfINbycifYNHjzqALoLfn00000',0,0,0,0.000000000000000,0,'MojimRsQZuotWXMuvSQVDSdCmOaloSXNLRTxMYYXaxGbtuk00000',0);
101

102
explain ((select c6 from t8 where c6 < 1000) except (select c6 from t8 where c6 > -1000) order by c6 limit 1000) union all (select  t8_alias1.c10 from t8 t8_alias1 limit 1);
103

104
drop table t1,t2,t3,t4,t5,t6, t8;
105

106

107