oceanbase

1
--disable_query_log
2
set @@session.explicit_defaults_for_timestamp=off;
3
--enable_query_log
4
#owner: link.zt
5
#owner group: sql1
6

7
--disable_warnings
8
set @@recyclebin = off;
9
drop table if exists t1,t2,t3,t4;
10
--enable_warnings
11

12
create table t1(pk1 int, pk2 int, pk3 int, extra int, d int primary key);
13

14
insert into t1(pk1,pk2,pk3,d) values(1,1,1,1),(2,2,2,2);
15
select * from t1;
16

17
insert into t1 values(3,null,null,NULL,3),(null,null,null,null,4);
18
select * from t1;
19

20

21
select * from t1 where extra = NULL;
22
select * from t1 where extra != NULL;
23
select * from t1 where extra <=> null;
24
select * from t1 where extra is not NULL;
25
select * from t1 where pk1 <=> null and pk2 <=> null and pk3 <=> null;
26
select * from t1 where pk1 = NULL and pk3 = NULL and pk3 = NULL;
27
select * from t1 where pk1 != NULL and pk2 != NULL and pk3 != NULL;
28
select * from t1 where pk1 is not NULL and pk2 is not NULL and pk3 is not NULL;
29
select * from t1 where pk1 in (NULL) and pk2 in (NULL) and pk3 in (NULL);
30
select * from t1 where pk1 in (NULL,1) and pk2 in (NULL, 1);
31

32
update t1 set extra=4 where pk1 = NULL and pk2 = NULL and pk3 = NULL;
33
select * from t1;
34
update t1 set extra=1 where pk1 <=> null;
35
select * from t1;
36
update t1 set extra=2 where pk1 <=> null and pk2 <=> null and pk3 <=> null;
37
select * from t1;
38
update t1 set extra=3 where pk1 is not NULL;
39
select * from t1;
40
update t1 set extra=4 where pk1 is not NULL and pk2 is not NULL and pk3 is not NULL;
41
select * from t1;
42
update t1 set extra=5 where pk1 != NULL;
43
select * from t1;
44
update t1 set extra=6 where pk1 != NULL and pk2 != NULL and pk3 != NULL;
45
select * from t1;
46

47
delete from t1 where pk1 != NULL;
48
select * from t1;
49
delete from t1 where pk1 != NULL and pk2 != NULL and pk3 != NULL;
50
select * from t1;
51
delete from t1 where pk1 = NULL and pk2 = NULL and pk3 = NULL;
52
select * from t1;
53
delete from t1 where pk1 <=> null;
54
select * from t1;
55
delete from t1 where pk1 <=> null and pk2 <=> null and pk3 <=> null;
56
select * from t1;
57
delete from t1 where pk1 is not NULL and pk2 is not NULL and pk3 is not NULL;
58
select * from t1;
59
delete from t1 where pk1 is not NULL;
60
select * from t1;
61
set @@recyclebin = off;
62
drop table t1;
63