oceanbase

1
drop table if exists t1;
2
CREATE TABLE t1
3
(
4
pk1 INT primary key,
5
key1 INT,
6
key2 VARCHAR(20),
7
key3 VARCHAR(20)
8
);
9
insert into t1 values(1,12,'a','a1'),(2,11,'a','b1'),(3,10,'b','b1'),(4,9,'c','c1'),
10
(5,8,NULL,'d1'),(6,7,NULL,'b1'),(7,6,NULL,'b1'),
11
(8,5,'a',NULL),(9,4,'a',NULL),(10,3,NULL,NULL),
12
(11,2,'',NULL),(12,1,NULL,'');
13
create index i1 on t1(key2,key3);
14
select * from t1 where key2='a' and key3=NULL;
15
pk1	key1	key2	key3
16
select pk1 from t1 where key2='a' and key3=NULL;
17
pk1
18
select * from t1 where key2='a' and key3>NULL;
19
pk1	key1	key2	key3
20
select pk1 from t1 where key2='a' and key3>NULL;
21
pk1
22
select * from t1 where key2='a' and key3<NULL;
23
pk1	key1	key2	key3
24
select pk1 from t1 where key2='a' and key3<NULL;
25
pk1
26
select * from t1 where key2='a' and key3>=NULL;
27
pk1	key1	key2	key3
28
select pk1 from t1 where key2='a' and key3>=NULL;
29
pk1
30
select * from t1 where key2='a' and key3<=NULL;
31
pk1	key1	key2	key3
32
select pk1 from t1 where key2='a' and key3<=NULL;
33
pk1
34
select * from t1 where key2=NULL and key3='b1';
35
pk1	key1	key2	key3
36
select pk1 from t1 where key2=NULL and key3='b1';
37
pk1
38
select * from t1 where key2>NULL and key3='b1';
39
pk1	key1	key2	key3
40
select pk1 from t1 where key2>NULL and key3='b1';
41
pk1
42
select * from t1 where key2<NULL and key3='b1';
43
pk1	key1	key2	key3
44
select pk1 from t1 where key2<NULL and key3='b1';
45
pk1
46
select * from t1 where key2>=NULL and key3='b1';
47
pk1	key1	key2	key3
48
select pk1 from t1 where key2>=NULL and key3='b1';
49
pk1
50
select * from t1 where key2<=NULL and key3='b1';
51
pk1	key1	key2	key3
52
select pk1 from t1 where key2<=NULL and key3='b1';
53
pk1
54
select * from t1 where key2=NULL and key3=NULL;
55
pk1	key1	key2	key3
56
select pk1 from t1 where key2=NULL and key3=NULL;
57
pk1
58
select * from t1 where key2=NULL and key3<NULL;
59
pk1	key1	key2	key3
60
select pk1 from t1 where key2=NULL and key3<NULL;
61
pk1
62
select * from t1 where key2=NULL and key3>NULL;
63
pk1	key1	key2	key3
64
select pk1 from t1 where key2=NULL and key3>NULL;
65
pk1
66
select * from t1 where key2=NULL and key3<=NULL;
67
pk1	key1	key2	key3
68
select pk1 from t1 where key2=NULL and key3<=NULL;
69
pk1
70
select * from t1 where key2=NULL and key3>=NULL;
71
pk1	key1	key2	key3
72
select pk1 from t1 where key2=NULL and key3>=NULL;
73
pk1
74