oceanbase

1
--disable_query_log
2
set @@session.explicit_defaults_for_timestamp=off;
3
--enable_query_log
4
#owner: bin.lb
5
#owner group: sql1
6
#tags: optimizer
7
#description:
8

9
##
10
# test index union
11
##
12
--disable_query_log
13
--disable_abort_on_error
14
--disable_warnings
15
--source mysql_test/include/index_quick_major.inc
16
--real_sleep 1
17
--disable_warnings
18
drop table if exists t1,t2;
19
--enable_warnings
20

21
create table t1 (pk1 int,pk2 varchar(124),pk3  timestamp(6) default "2012-01-01 12:00:00", c1 int,c2 varchar(1024),c3  timestamp(6) default "2012-01-01 12:00:00",c4 int,c5 int,c6 int,primary key(pk1,pk2,pk3));
22
--disable_query_log
23
create index i1 on t1(c1);
24
create index i2 on t1(c1,c2);
25
create index i3 on t1(c1,c2,c3);
26
create index i4 on t1(c4,c5,c6);
27

28
create table t2 (pk1 int primary key, c1 int not null,c2 varchar(1024) not null,c3  timestamp(6) default "2012-01-01 12:00:00",c4 int,c5 int,c6 int);
29
--disable_query_log
30
create index i11 on t2(c1) storing(c2);
31
create index i12 on t2(c4,c5) storing(c3);
32

33
--source mysql_test/include/check_all_idx_ok.inc
34

35
--disable_query_log
36
insert into t1 values
37
(1, 'primarykey1', '2013-12-11 01:01:01',44,'column11','2013-12-11 11:11:11',4,2,3),
38
(2, 'primarykey2', '2013-12-11 01:01:02',33,'column21','2013-12-11 11:11:12',3,1,3),
39
(3, 'primarykey3', '2013-12-11 01:01:03',22,'column32','2013-12-11 11:11:13',2,1,2),
40
(4, 'primarykey4', '2013-12-11 01:01:04',11,'column42','2013-12-11 11:11:14',1,3,2);
41

42
insert into t2 values
43
(1,44,'column11','2013-12-11 11:11:11',4,2,3),
44
(2,10,'column21','2013-12-11 11:11:12',3,1,2),
45
(3,22,'column32','2013-12-11 11:11:13',2,0,1),
46
(4,11,'column43','2013-12-11 11:11:14',1,-1,0),
47
(5,33,'column54','2013-12-11 11:11:15',0,-2,-1),
48
(6,33,'column65','2013-12-11 11:11:16',3,0,3);
49

50
--enable_query_log
51

52
##############one table union #############
53
#all use index 
54
--sorted_result
55
(select /*+index(t1 i1) */ pk1,c1 from t1 where c1<30 order by c1) union all (select /*+index(t1 i1) */ pk1,c1 from t1 where c1 > 10 order by c1); 
56
select * from ((select /*+index(t1 i1) */ pk1,c1 from t1 where c1<30 order by c1) union all (select /*+index(t1 i1) */ pk1,c1 from t2 where c1 > 10 order by c1) ) a order by pk1, a.c1; 
57
--sorted_result
58
(select /*+index(t1 i1) */ pk1,c1 from t1 where c1<30 order by c1) union  (select /*+index(t1 i1) */ pk1,c1 from t1 where c1 > 10 order by c1); 
59
--sorted_result
60
(select /*+index(t1 i1) */ pk1,c1 from t1 where c1<30 order by c1) except (select /*+index(t1 i1) */ pk1,c1 from t1 where c1 > 10 order by c1); 
61
--sorted_result
62
(select /*+index(t1 i1) */ pk1,c1 from t1 where c1<30 order by c1) intersect (select /*+index(t1 i1) */ pk1,c1 from t1 where c1 > 10 order by c1); 
63

64
--sorted_result
65
(select c1,c2,c3 from t1 where c1=11 order by c2) union (select c1,c2,c3 from t1 where c1=22 and c2 like 'column3_' order by c1) union distinct select c1,c2,c3 from t1 where c1>22 and c2 is not null and c3>='2013-12-11 11:11:11' union all (select c1,c2,c3 from t1 where c4<2 and c5>1 and c6=3 order by c4) except  (select c1,c2,c3 from t1 where c3 <= '2013-12-11 11:11:11' order by c3);
66

67
--sorted_result
68
select * from ((select * from t1 where c1=44) union (select * from t1 where c1=33 and c2 like 'column%') union all (select * from t1 where c4>2 and c5<3 and c6 != 2)) as a;
69
--sorted_result
70
select * from ((select * from t1 where c1=44) union all (select * from t1 where c1=33 and c2 like 'column%') except (select * from t1 where c4>2 and c5<3 and c6 != 2)) as a;
71
--sorted_result
72
select * from ((select * from t1 where c1=44) intersect (select * from t1 where c1=33 and c2 like 'column%') union all (select * from t1 where c4>2 and c5<3 and c6 != 2)) as a;
73

74
--sorted_result
75
(select c1,c4 from t1 where c1 in (33,11) order by c1) union all select c1,c4 from t1 where c1=44;
76
--sorted_result
77
(select c1,c4 from t1 where c1 in (1,44) order by c1)  union all (select c1,c4 from t1 where c1 in (33,11) order by c1);
78
--sorted_result
79
(select c1,c4 from t1 where c1 in (1,44) order by c1)  union all (select c1,c4 from t1 where c1 =33 order by c4);
80
# TODO(@linsheng): BUG
81
# (select c1,c4 from t1 where c1 in (1,44) order by c1)  union (select c1,c4 from t1 where c1 =33 order by c4);
82
# (select c1,c4 from t1 where c1 in (1,44) order by c4)  union (select c1,c4 from t1 where c1 =33 order by c4);
83
# (select c1,c4 from t1 where c1 in (1,44) order by c1)  except (select c1,c4 from t1 where c1 =33 order by c4);
84
# (select c1,c4 from t1 where c1 in (1,44) order by c4)   intersect  (select c1,c4 from t1 where c1 =33 order by c4);
85
--sorted_result
86
select c1,c4 from t1 where c1=44 union (select c1,c4 from t1 where c1 not in (33,11) order by c1);
87
--sorted_result
88
(select c1,c4 from t1 where c1 not in (33,11) order by c1) except select c1,c4 from t1 where c1=44;
89
--sorted_result
90
(select c1,c4 from t1 where c1 not in (33,11) order by c1) intersect select c1,c4 from t1 where c1=44;
91

92

93
(select c4,c5,c6 from t1 order by c1 limit 2)  union all (select c4,c5,c6 from t1 order by c4 limit 1) order by c4 desc;
94
(select c4,c5,c6 from t1 order by c4 limit 2)  union (select c4,c5,c6 from t1 order by c4 limit 1) order by c4 desc;
95
(select c4,c5,c6 from t1 order by c2 limit 2)  except (select c4,c5,c6 from t1 order by c4 limit 1) order by c4 desc;
96
(select c4,c5,c6 from t1  order by c4 limit 2)  intersect (select c4,c5,c6 from t1 order by c4 limit 1) order by c4 desc;
97

98
################ multi table union #############
99
#all use index
100
--sorted_result
101
select /*+index(t1 i1)*/ pk1,c1 from t1 union select /*+index(t2 i11)*/ pk1,c1 from t2;
102
--sorted_result
103
select /*+index(t1 i1)*/ pk1,c1 from t1 union all select /*+index(t2 i11)*/ pk1,c1 from t2;
104
--sorted_result
105
select /*+index(t1 i1)*/ pk1,c1 from t1 except select /*+index(t2 i11)*/ pk1,c1 from t2;
106
--sorted_result
107
select /*+index(t1 i1)*/ pk1,c1 from t1 intersect select /*+index(t2 i11)*/ pk1,c1 from t2;
108

109
--sorted_result
110
(select t1.c1,t2.c4 from t1,t2 where t1.c1=t2.c1 order by t1.c1) union (select c1,c4 from t2 where c1=44 order by c1);
111
--sorted_result
112
(select c1,c4 from t2 where c1=44 order by c1) union (select t1.c1,t2.c4 from t1,t2 where t1.c1=t2.c1 order by t1.c1);
113
--sorted_result
114
(select t1.c1,t2.c4 from t1,t2 where t1.c1=t2.c1 order by t2.c1) union all (select c1,c4 from t2 where c1=44 order by c1);
115
--sorted_result
116
(select t1.c1,t2.c4 from t1,t2 where t1.c1=t2.c1 order by t1.c1) except (select c1,c4 from t2 where c1=44 order by c2);
117
--sorted_result
118
(select t1.c1,t2.c4 from t1,t2 where t1.c1=t2.c1 order by t2.c1) intersect (select c1,c4 from t2 where c1=44 order by c1);
119

120
###use two order by in from sql
121
select * from (select * from t1 where pk1 in(1,2,3,4,5,6)) a order by pk2;
122
select * from (select * from t1 where pk1 in(1,2,3,4,5,6) order by pk1) a order by pk2;
123
select * from (select * from t1 where pk1 in(1,2,3,4,5,6) order by pk1) a order by pk1;
124
select * from (select * from t1 where pk1 in(1,2,3,4,5,6) order by pk1) a  where a.c2>='abc' order by pk1;
125
select * from (select * from t1 where pk1 in(1,2,3,4,5,6) order by pk1) a  where a.c2>='abc' order by pk2;
126
--enable_abort_on_error
127