oceanbase

safe_null_test.result
134 строки · 3.3 Кб
Перенос по словам
1
set @@recyclebin = off;
2
drop table if exists t1,t2,t3,t4;
3
create table t1(pk1 int, pk2 int, pk3 int, extra int, d int primary key);
4
insert into t1(pk1,pk2,pk3,d) values(1,1,1,1),(2,2,2,2);
5
select * from t1;
6
pk1	pk2	pk3	extra	d
7
1	1	1	NULL	1
8
2	2	2	NULL	2
9
insert into t1 values(3,null,null,NULL,3),(null,null,null,null,4);
10
select * from t1;
11
pk1	pk2	pk3	extra	d
12
1	1	1	NULL	1
13
2	2	2	NULL	2
14
3	NULL	NULL	NULL	3
15
NULL	NULL	NULL	NULL	4
16
select * from t1 where extra = NULL;
17
pk1	pk2	pk3	extra	d
18
select * from t1 where extra != NULL;
19
pk1	pk2	pk3	extra	d
20
select * from t1 where extra <=> null;
21
pk1	pk2	pk3	extra	d
22
1	1	1	NULL	1
23
2	2	2	NULL	2
24
3	NULL	NULL	NULL	3
25
NULL	NULL	NULL	NULL	4
26
select * from t1 where extra is not NULL;
27
pk1	pk2	pk3	extra	d
28
select * from t1 where pk1 <=> null and pk2 <=> null and pk3 <=> null;
29
pk1	pk2	pk3	extra	d
30
NULL	NULL	NULL	NULL	4
31
select * from t1 where pk1 = NULL and pk3 = NULL and pk3 = NULL;
32
pk1	pk2	pk3	extra	d
33
select * from t1 where pk1 != NULL and pk2 != NULL and pk3 != NULL;
34
pk1	pk2	pk3	extra	d
35
select * from t1 where pk1 is not NULL and pk2 is not NULL and pk3 is not NULL;
36
pk1	pk2	pk3	extra	d
37
1	1	1	NULL	1
38
2	2	2	NULL	2
39
select * from t1 where pk1 in (NULL) and pk2 in (NULL) and pk3 in (NULL);
40
pk1	pk2	pk3	extra	d
41
select * from t1 where pk1 in (NULL,1) and pk2 in (NULL, 1);
42
pk1	pk2	pk3	extra	d
43
1	1	1	NULL	1
44
update t1 set extra=4 where pk1 = NULL and pk2 = NULL and pk3 = NULL;
45
select * from t1;
46
pk1	pk2	pk3	extra	d
47
1	1	1	NULL	1
48
2	2	2	NULL	2
49
3	NULL	NULL	NULL	3
50
NULL	NULL	NULL	NULL	4
51
update t1 set extra=1 where pk1 <=> null;
52
select * from t1;
53
pk1	pk2	pk3	extra	d
54
1	1	1	NULL	1
55
2	2	2	NULL	2
56
3	NULL	NULL	NULL	3
57
NULL	NULL	NULL	1	4
58
update t1 set extra=2 where pk1 <=> null and pk2 <=> null and pk3 <=> null;
59
select * from t1;
60
pk1	pk2	pk3	extra	d
61
1	1	1	NULL	1
62
2	2	2	NULL	2
63
3	NULL	NULL	NULL	3
64
NULL	NULL	NULL	2	4
65
update t1 set extra=3 where pk1 is not NULL;
66
select * from t1;
67
pk1	pk2	pk3	extra	d
68
1	1	1	3	1
69
2	2	2	3	2
70
3	NULL	NULL	3	3
71
NULL	NULL	NULL	2	4
72
update t1 set extra=4 where pk1 is not NULL and pk2 is not NULL and pk3 is not NULL;
73
select * from t1;
74
pk1	pk2	pk3	extra	d
75
1	1	1	4	1
76
2	2	2	4	2
77
3	NULL	NULL	3	3
78
NULL	NULL	NULL	2	4
79
update t1 set extra=5 where pk1 != NULL;
80
select * from t1;
81
pk1	pk2	pk3	extra	d
82
1	1	1	4	1
83
2	2	2	4	2
84
3	NULL	NULL	3	3
85
NULL	NULL	NULL	2	4
86
update t1 set extra=6 where pk1 != NULL and pk2 != NULL and pk3 != NULL;
87
select * from t1;
88
pk1	pk2	pk3	extra	d
89
1	1	1	4	1
90
2	2	2	4	2
91
3	NULL	NULL	3	3
92
NULL	NULL	NULL	2	4
93
delete from t1 where pk1 != NULL;
94
select * from t1;
95
pk1	pk2	pk3	extra	d
96
1	1	1	4	1
97
2	2	2	4	2
98
3	NULL	NULL	3	3
99
NULL	NULL	NULL	2	4
100
delete from t1 where pk1 != NULL and pk2 != NULL and pk3 != NULL;
101
select * from t1;
102
pk1	pk2	pk3	extra	d
103
1	1	1	4	1
104
2	2	2	4	2
105
3	NULL	NULL	3	3
106
NULL	NULL	NULL	2	4
107
delete from t1 where pk1 = NULL and pk2 = NULL and pk3 = NULL;
108
select * from t1;
109
pk1	pk2	pk3	extra	d
110
1	1	1	4	1
111
2	2	2	4	2
112
3	NULL	NULL	3	3
113
NULL	NULL	NULL	2	4
114
delete from t1 where pk1 <=> null;
115
select * from t1;
116
pk1	pk2	pk3	extra	d
117
1	1	1	4	1
118
2	2	2	4	2
119
3	NULL	NULL	3	3
120
delete from t1 where pk1 <=> null and pk2 <=> null and pk3 <=> null;
121
select * from t1;
122
pk1	pk2	pk3	extra	d
123
1	1	1	4	1
124
2	2	2	4	2
125
3	NULL	NULL	3	3
126
delete from t1 where pk1 is not NULL and pk2 is not NULL and pk3 is not NULL;
127
select * from t1;
128
pk1	pk2	pk3	extra	d
129
3	NULL	NULL	3	3
130
delete from t1 where pk1 is not NULL;
131
select * from t1;
132
pk1	pk2	pk3	extra	d
133
set @@recyclebin = off;
134
drop table t1;
135
oceanbase

Использование cookies
