github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/function/func_math_ceil.test (about)

     1  #SELECT,科学计数
     2  select ceiling(5.5),ceiling(-5.5);
     3  select ceiling(cast(-2 as unsigned)), ceiling(18446744073709551614), ceiling(-2);
     4  select ceiling(1.1e5);
     5  
     6  
     7  #INSERT, EXTREME VALUES
     8  #0.5MO不支持CREATE SELECT
     9  #CREATE TABLE t AS SELECT CEILING(18446744073709551615) AS c, FLOOR(18446744073709551615) AS f;
    10  #SELECT * FROM t;
    11  #DROP TABLE t;
    12  
    13  
    14  create table t(a double);
    15  insert into t select (CEILING(18446744073709551615));
    16  insert into t select (FLOOR(18446744073709551615));
    17  select * from t;
    18  drop table t;
    19  
    20  
    21  
    22  #SELECT, 嵌套，DATATYPE， EXTREME VALUE
    23  create table t(a decimal(19,0));
    24  insert into t select (CEILING(CAST(1844674407370955161 AS DECIMAL(19, 0))));
    25  insert into t select (FLOOR(CAST(1844674407370955161 AS DECIMAL(19, 0))));
    26  SELECT * FROM t;
    27  DROP TABLE t;
    28  create table t(a decimal(19,0));
    29  insert into t select (CEILING(CAST(-9223372036854775808 AS DECIMAL(19, 0))));
    30  insert into t select (FLOOR(CAST(-9223372036854775808 AS DECIMAL(19, 0))));
    31  SELECT * FROM t;
    32  DROP TABLE t;
    33  create table t(a decimal(18,0));
    34  insert into t select (CEILING(CAST(184467440737095516 AS DECIMAL(18, 0))));
    35  insert into t select (FLOOR(CAST(184467440737095516 AS DECIMAL(18, 0))));
    36  SELECT * FROM t;
    37  DROP TABLE t;
    38  create table t(a decimal(18,0));
    39  insert into t select (CEILING(CAST(-922337203685477580 AS DECIMAL(18, 0))));
    40  insert into t select (FLOOR(CAST(-922337203685477580 AS DECIMAL(18, 0))));
    41  SELECT * FROM t;
    42  DROP TABLE t;
    43  #0.5MO不支持CREATE SELECT
    44  #CREATE TABLE t AS
    45  #SELECT CEILING(CAST(99999999999999999.9 AS DECIMAL(18, 1))) AS c,
    46  #FLOOR(CAST(-99999999999999999.9 AS DECIMAL(18, 1))) AS f;
    47  #SELECT * FROM t;
    48  #DROP TABLE t;
    49  #CREATE TABLE t AS
    50  #SELECT CEILING(CAST(999999999999999999.9 AS DECIMAL(19, 1))) AS c,
    51  #FLOOR(CAST(-999999999999999999.9 AS DECIMAL(19, 1))) AS f;
    52  #SELECT * FROM t;
    53  #DROP TABLE t;
    54  
    55  #INSERT INTO, distinct
    56  CREATE table t1(a int, b float);
    57  insert into t1 select ceiling(12124.413), ceiling(-4213.413);
    58  insert into t1 select ceiling(12124.123), ceiling(-42413.409);
    59  select distinct ceiling(a) from t1;
    60  drop table t1;
    61  
    62  #null
    63  select ceiling(null);
    64  
    65  #算术操作
    66  select ceil(123.34)-ceil(123.03);
    67  
    68  #WHERE
    69  drop table if exists t1;
    70  create table t1(a INT,  b float);
    71  insert into t1 select ceiling(12124.413), ceiling(-4213.413);
    72  insert into t1 select ceiling(12124.123), ceiling(-42413.409);
    73  select * from t1 where ceiling(a)=12125;
    74  drop table t1;
    75  
    76  
    77  #ON CONDITION
    78  drop table if exists t1;
    79  drop table if exists t2;
    80  create table t1(a INT,  b float);
    81  create table t2(a INT,  b float);
    82  insert into t1 select ceiling(12124.413), ceiling(-4213.413);
    83  insert into t1 select ceiling(12124.123), ceiling(-42413.409);
    84  insert into t2 select ceiling(14124.413), ceiling(-4213.413);
    85  insert into t2 select ceiling(984798.123), ceiling(-980.409);
    86  SELECT t1.a, t2.a FROM t1 JOIN t2 ON (ceiling(t1.b) = ceiling(t2.b));
    87  drop table t1;
    88  drop table t2;
    89  
    90  
    91  #HAVING，比较操作
    92  drop table if exists t1;
    93  create table t1(a INT,  b float);
    94  insert into t1 select ceiling(14124.413), ceiling(-4213.413);
    95  insert into t1 select ceiling(984798.123), ceiling(-980.409);
    96  select b from t1 group by b having ceil(b)<-1000;
    97  drop table t1;
    98  
    99  #MO支持的ceil是clickhouse语义的，所以可以有两个参数, 以下在MO中运行测试,mysql跑不过
   100  select ceil(123.342, 1);
   101  select ceil(123.45, -1);
   102  select ceil(123.342124124214, 100);
   103  select ceil(-123.342124124214, -100);
   104  select ceil(123.342, null);
   105  select ceil(null, 1);
   106  select ceil(null, null);