oceanbase

1
--disable_query_log
2
set @@session.explicit_defaults_for_timestamp=off;
3
--enable_query_log
4
#### owner: yuchen.wyc
5
#### owner group: sql3
6
#### description: 用于测试NULL上的索引相关
7
--disable_warnings
8
drop table if exists t1;
9
--enable_warnings
10

11
CREATE TABLE t1
12
(
13
  pk1 INT primary key,
14
  key1 INT,
15
  key2 VARCHAR(20),
16
  key3 VARCHAR(20)
17
);
18

19
insert into t1 values(1,12,'a','a1'),(2,11,'a','b1'),(3,10,'b','b1'),(4,9,'c','c1'),
20
(5,8,NULL,'d1'),(6,7,NULL,'b1'),(7,6,NULL,'b1'),
21
(8,5,'a',NULL),(9,4,'a',NULL),(10,3,NULL,NULL),
22
(11,2,'',NULL),(12,1,NULL,'');
23

24
create index i1 on t1(key2,key3);
25

26
let $index_name=__idx_3003_i1;
27
--source mysql_test/include/check_all_idx_ok.inc
28

29
##
30
select * from t1 where key2='a' and key3=NULL;
31
select pk1 from t1 where key2='a' and key3=NULL;
32

33
##
34
select * from t1 where key2='a' and key3>NULL;
35
select pk1 from t1 where key2='a' and key3>NULL;
36

37
##
38
select * from t1 where key2='a' and key3<NULL;
39
select pk1 from t1 where key2='a' and key3<NULL;
40

41
##
42
select * from t1 where key2='a' and key3>=NULL;
43
select pk1 from t1 where key2='a' and key3>=NULL;
44

45
##
46
select * from t1 where key2='a' and key3<=NULL;
47
select pk1 from t1 where key2='a' and key3<=NULL;
48

49
##
50
select * from t1 where key2=NULL and key3='b1';
51
select pk1 from t1 where key2=NULL and key3='b1';
52

53
##
54
select * from t1 where key2>NULL and key3='b1';
55
select pk1 from t1 where key2>NULL and key3='b1';
56

57
##
58
select * from t1 where key2<NULL and key3='b1';
59
select pk1 from t1 where key2<NULL and key3='b1';
60

61
##
62
select * from t1 where key2>=NULL and key3='b1';
63
select pk1 from t1 where key2>=NULL and key3='b1';
64

65
##
66
select * from t1 where key2<=NULL and key3='b1';
67
select pk1 from t1 where key2<=NULL and key3='b1';
68

69
##
70
select * from t1 where key2=NULL and key3=NULL;
71
select pk1 from t1 where key2=NULL and key3=NULL;
72

73
##
74
select * from t1 where key2=NULL and key3<NULL;
75
select pk1 from t1 where key2=NULL and key3<NULL;
76

77
##
78
select * from t1 where key2=NULL and key3>NULL;
79
select pk1 from t1 where key2=NULL and key3>NULL;
80

81
##
82
select * from t1 where key2=NULL and key3<=NULL;
83
select pk1 from t1 where key2=NULL and key3<=NULL;
84

85
##
86
select * from t1 where key2=NULL and key3>=NULL;
87
select pk1 from t1 where key2=NULL and key3>=NULL;
88

89

90