oceanbase

1
--disable_query_log
2
set @@session.explicit_defaults_for_timestamp=off;
3
--enable_query_log
4
#### owner: peihan.dph
5
#### owner group: sql3
6
#### description: 测试向上取整函数(天花板函数)
7
--echo ================ expression ceil ================
8
--disable_warnings
9
drop table if exists test;
10
--enable_warnings
11
create table test (pk int primary key, c1 tinyint, c2 smallint, c3 mediumint, c4 int, c5 bigint, c6 tinyint unsigned, c7 smallint unsigned, c8 mediumint unsigned, c9 int unsigned, c10 bigint unsigned, c11 float, c12 double, c13 float unsigned, c14 double unsigned, c15 decimal(20, 10), c16 decimal(20, 10) unsigned, c17  datetime(6), c18   timestamp(6) default "2012-01-01 12:00:00", c19 date, c20 time, c21 year , c22 varchar(10000), c23 char(255), c24 varbinary(10000), c25 binary(255));
12
insert into test values (0, -128, 2, -3, 4, -5, 6, 7, 8, 9, 10, -11.49, -12.5, 13.5, 14.49, 15.99, 16.1, '2017-01-01 00:01:10.123456', '2018-02-02 00:02:20.123456', '2019-03-03', '20:04:40.123456', '2021', '22.5324', '-23.436456', '-24', '25');
13
insert into test values (1, 1, -2, 3, -4, 5, 6, 7, 8, 9, 10, -11.49, -12.5, 13.5, 14.49, 15.99, 16.1, '2017-01-01 00:01:10.123456', '2018-02-02 00:02:20.123456', '2019-03-03', '20:04:40.123456', '2021', '-22.999999', '23.00001', '24.9999', '-25.00001');
14
insert into test values (2, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
15
select ceil(pk), ceil(c1), ceil(c2), ceil(c3), ceil(c4), ceil(c5), ceil(c6), ceil(c7), ceil(c8), ceil(c9), ceil(c10), ceil(c11), ceil(c12), ceil(c13), ceil(c14), ceil(c15), ceil(c16), ceil(c22), ceil(c23), ceil(c24), ceil(c25) from test;
16
--disable_warnings
17
drop table if exists test;
18
--enable_warnings
19
select ceil(3.1415926);
20
select ceil(-3.1415926);
21
select ceil(0.00);
22
select ceil(-0.0);
23
select ceil(0.123456789);
24
select ceil(-0.123456789);
25
select ceil(123456789.123456789);
26
select ceil(-99999999.999999999);
27
select ceil(999999999.123456789);
28
select ceil(-999999999.123456789);
29
select ceil(-123456789123456789123456789.123456789);
30
select ceil(123456789123456789123456789123456789123456789123456789.123456789);
31
select ceil(-123456789123456789123456789123456789123456789123456789.123456789);
32
select ceil(123456789123456789123456789.123456789123456789123456789123456789);
33
select ceil(-123456789123456789123456789.123456789123456789123456789123456789);
34
select ceil(-123456789123456789123456789.123456789);
35
select ceil(999999999999999999999999999999999999999999999.499999999);
36
select ceil(999999999999999999999999999999999999999999999.500000001);
37
select ceil(99999999999999999999999999999999999999999999.399999999);
38
select ceil(-99999999999999999999999999999999999999999999.399999999);
39
select ceil(-99999999999999999999999999999999999999999999.399999999);
40
select ceil(999999999999999999999999999999999999999999999211111.399999999);
41
select ceil(-999999999999999999999999999999999999999999999211111.399999999);
42
select ceil(-999999999999999999999999999999999999999999999511111.399999999);
43
select ceil(-999999999999999999999999999999999999999999999499999.399999999);
44
select ceil(-1);
45
select ceil(-161);
46
select ceil(null);
47
select ceil("13547370213547370213547370213547370201354737021354737021354737021354737021354737021354737021354737021354737021354737021354737021354737021354737021354737020.0000135473702135473702135473702135473702135473702135473702135473702135473702013547370213547370201354737021354737021354737021354737021354737021354737021354737021.0000135473702135473702135473702135473702135473702111111111111111111");
48
select ceil("13547370213547370213547370213547370201354737021354737021354737021354737021354737021354737021354737021354737021354737021354737021354737021354737021354737020.0000135473702135473702135473702135473702135473702135473702135473702135473702013547370213547370201354737021354737021354737021354737021354737021354737021354737021.0000135473702135473702135473702135473702135473702catters billet chloroplast's'");
49

50
##bugfix: 
51
--disable_warnings
52
drop table if exists t1;
53
--enable_warnings
54
CREATE TABLE t1(id int,consumer char(20), price varchar(20),sal int,datetime1 DATE,datetime2 bigint);
55
INSERT INTO t1 VALUES(1,'苹果','6500',5000,'2020-09-22 12:11:59',20200923121200);
56
INSERT INTO t1 VALUES(2,'小米','3000',4000,'2020-09-21 10:11:59',20200921101159);
57
INSERT INTO t1 VALUES(3,'OPPO','5000',3000,'2020-08-21 10:11:59',20190821101159);
58
INSERT INTO t1 VALUES(4,'华为','9111',10000,'2020-02-29 10:11:59',20200228101159);
59

60

61
SELECT CEIL(rpad(price,20,sal)) FROM t1 ORDER BY id;
62
SELECT rpad(CEIL(sal),20,CEIL(price)) FROM t1 ORDER BY id;
63