oceanbase

1
--disable_query_log
2
set @@session.explicit_defaults_for_timestamp=off;
3
--enable_query_log
4
# owner: xiaoyi.xy
5
# owner group: SQL3
6
# description: 测试where比较条件的delete stmt, 单行删除
7
# tags: delete,dml
8

9
--disable_abort_on_error
10

11
--echo can't determine a row
12

13
##case1
14
--disable_warnings
15
drop table if exists t1;
16
--enable_warnings
17
create table t1(c1 int, c2 int, primary key(c1));
18

19
insert into t1 values(1,1),(2,2),(3,3);
20
delete from t1 where c1 in(1);
21
select * from t1;
22
delete from t1 where c1 in(1,2);
23
select * from t1;
24
delete from t1 where c1>=3;
25
select * from t1;
26

27
insert into t1 values(1,1),(2,2),(3,3);
28
delete from t1 where c1<=1;
29
select * from t1;
30
delete from t1 where 1>=c1;
31
select * from t1;
32
delete from t1 where 3<=c1;
33
select * from t1;
34
delete from t1 where 2<=c1;
35
select * from t1;
36

37
insert into t1 values(1,1),(2,2),(3,3);
38
delete from t1 where c1>1 and c1<3;
39
select * from t1;
40
delete from t1 where c1=1 or c1=2;
41
select * from t1;
42
delete from t1 where c1<1 or c1<2;
43
select * from t1;
44
delete from t1 where c1=1 and c1=2;
45
select * from t1;
46
delete from t1 where c1>1 and c1=2;
47
select * from t1;
48

49
##case2
50
--disable_warnings
51
drop table if exists t1;
52
--enable_warnings
53
create table t1(c1 int, c2 int, primary key(c1));
54

55
insert into t1 values(1,1),(2,2),(3,3);
56
delete from t1 where c2 in(1);
57
select * from t1;
58
delete from t1 where c2 in(1,2);
59
select * from t1;
60
delete from t1 where c2>=3;
61
select * from t1;
62

63
insert into t1 values(1,1),(2,2),(3,3);
64
delete from t1 where c2<=1;
65
select * from t1;
66
delete from t1 where 1>=c2;
67
select * from t1;
68
delete from t1 where 3<=c2;
69
select * from t1;
70
delete from t1 where c2>1 and c2<3;
71
select * from t1;
72
delete from t1 where c2=1 or c2=2;
73
select * from t1;
74
delete from t1 where c2<1 or c2<2;
75
select * from t1;
76
delete from t1 where c2=1 and c2=2;
77
select * from t1;
78
delete from t1 where c2>1 and c2=2;
79
select * from t1;
80

81
##case3
82
--disable_warnings
83
drop table if exists t1;
84
--enable_warnings
85
create table t1(c1 int, c2 int, primary key(c1));
86

87
insert into t1 values(1,1),(2,2),(3,3);
88
delete from t1 where (c1,c2) in((1,1));
89
select * from t1;
90
delete from t1 where (c1,c2) in((1,1),(2,2));
91
select * from t1;
92
delete from t1 where (c2,c1) in((1,1));
93
select * from t1;
94
delete from t1 where (c2,c1) in((1,1),(2,2));
95
select * from t1;
96
delete from t1 where c1 > 1 and c2 >10 and c1=1;
97
select * from t1;
98
delete from t1 where c1 > 1 or c2 >10 and c1=1;
99
select * from t1;
100

101
##case4
102
--disable_warnings
103
drop table if exists t1;
104
--enable_warnings
105
create table t1(c1 int, c2 int, primary key(c1));
106
insert into t1 values(1,1),(2,2),(3,3);
107
delete from t1 where c2<=1;
108
select * from t1;
109
delete from t1 where 1>=c2;
110
select * from t1;
111
delete from t1 where 3<=c2;
112
select * from t1;
113
delete from t1 where c2>1 and c2<3;
114
select * from t1;
115
insert into t1 values(1,1),(2,2),(3,3);
116
delete from t1 where c2=1 or c2=2;
117
select * from t1;
118
delete from t1 where c2<1 or c2<2;
119
select * from t1;
120
delete from t1 where c2=1 and c2=2;
121
select * from t1;
122
delete from t1 where c2>1 and c2=2;
123
select * from t1;
124
--disable_warnings
125
drop table if exists t1;
126
--enable_warnings
127