oceanbase

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

7
#test:
8
#create index objecting to columns 
9
###TODO regenerate result file after physicalplan  output is ready
10
--disable_query_log
11
--source mysql_test/include/index_quick_major.inc 
12
--real_sleep 1
13
--disable_warnings
14
drop table if exists t1;
15
--enable_warnings
16
--enable_query_log
17

18
#init data table and index table
19
create table t1 (c1 int, c2 int, c3 int, c4 int, primary key(c1));
20
create index i1 on t1 (c2);
21
create index i2 on t1 (c3, c4);
22
create index i3 on t1 (c3) storing(c4);
23
replace into t1 values (1,1,2,1),(2,2,1,null),(3,3,null,null),(4,null,null,null);
24
select * from t1;
25

26
select  /*+ index(t1 i2) */ * from t1 where c3=1;
27

28
let $index_name = __idx_3003_i3;
29
--source mysql_test/include/check_all_idx_ok.inc
30

31
select * from t1 where c3=1;
32
select  /*+ index(a1 i2) */ * from t1 as a1 where c3 in(1,2);
33
select * from t1 as a1 where a1.c3 in (1,2);
34
select  /*+ index(t1 primary) */ * from t1 where c3 in(1,2);
35

36
select * from t1 where c3 is null;
37
select  /*+ index(t1 i2) */ * from t1 where c3 is null;
38
select  /*+ index(t1 primary) */ * from t1 where c3 is null;
39

40
select /*+ index(t1 i4) */ * from t1 where c3 is null;
41

42
drop table t1;
43