oceanbase

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

7
--source mysql_test/include/index_quick_major.inc
8
#case:int as idx
9
--disable_warnings
10
drop table if exists tbl1;
11
--enable_warnings
12
create table tbl1 (pk int,i1 int, v2 varchar(80), i3 char(20),i4 float, d4 datetime(6),i5 decimal(5,3),i6 bool, gmt_create datetime default now(), gmt_modify datetime default now(),primary key(pk));
13
insert into tbl1(pk,i1,v2,i3,i4,d4,i5,i6) values(1,1,'abc12','1agc',1.25,'2014-01-01 12:00:00',23.23,1),
14
                       (2,10,'23abc12','4agc1',-0.25,'2015-11-01 12:00:00',-23.230,0),
15
                       (3,201,'xx23abc12','cagc1',-2.25,'2015-11-21 11:20:10',3.231,1),
16
                       (4,80,'zx23abc12','zcagc1',2.25,'2013-01-21 11:20:10',-3.231,1),
17
                       (5,53,'cx23abc12','rcagc1',109.25,'2016-01-22 11:20:10',-13.131,0),
18
                       (6,78,'tx23abc12','icagc1',-109.25,'2014-09-16 16:24:00',-13.131,1),
19
                       (7,400,'yx23abc12','ocagc1',85.85,'2014-09-17 16:20:21',-93.131,0),
20
                       (8,50,'vx23abc12','wcagc1',-85.85,'2014-09-10 16:20:21',93.131,1),
21
                       (9,610,'ukx23abc12','pecagc1',-1.85,'2014-09-19 16:20:21',99.131,0),
22
                       (10,42,'kx23abc12','ecagc1',-85.85,'2014-09-18 16:20:21',-99.131,1),
23
                       (11,null,null,null,null,null,null,null);
24
create index idx_1 on tbl1(i6);
25
--source mysql_test/include/check_all_idx_ok.inc
26
select pk,i1,i6 from tbl1 where gmt_create<=date_sub(current_timestamp(), INTERVAL -6 YEAR)  and gmt_create>=date_sub(current_timestamp(), INTERVAL 6 YEAR) ;
27
select pk,i1,i6 from tbl1 where gmt_create<=date_sub(current_timestamp(), INTERVAL -6 YEAR)  and gmt_create>=date_sub(current_timestamp(), INTERVAL 6 YEAR)  and i6 in ('0','1');
28
 select pk,i1,i6 from tbl1 where gmt_create<=date_sub(current_timestamp(), INTERVAL -6 YEAR)  and gmt_create>=date_sub(current_timestamp(), INTERVAL 6 YEAR)  and i6 in ('1','0');
29
select pk,i1,i6 from tbl1 where i6=true;
30
select pk,i1,i6 from tbl1 where i6=false;
31
select pk,i1,i6 from tbl1 where gmt_create<=date_sub(current_timestamp(), INTERVAL -16 minute) and gmt_create>=date_sub(current_timestamp(), INTERVAL 16 minute) and pk<=11;
32
update tbl1 set i1=i1*2/2 where gmt_create<=date_sub(current_timestamp(), INTERVAL -16 minute) and gmt_create>=date_sub(current_timestamp(), INTERVAL 16 minute) and pk<=11;
33
select pk,i1,i6 from tbl1 where gmt_create<=date_sub(current_timestamp(), INTERVAL -16 minute) and gmt_create>=date_sub(current_timestamp(), INTERVAL 16 minute) and pk<=11;
34
delete  from tbl1 where gmt_create<=date_sub(current_timestamp(), INTERVAL -16 minute) and gmt_create>=date_sub(current_timestamp(), INTERVAL 16 minute) and pk<=11;
35
select pk,i1,i6 from tbl1 where gmt_create<=date_sub(current_timestamp(), INTERVAL -16 minute) and gmt_create>=date_sub(current_timestamp(), INTERVAL 16 minute) and pk<=11;
36