github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/function/func_math_floor.result (about) 1 select floor(5.5),floor(-5.5); 2 floor(5.5) floor(-5.5) 3 5 -6 4 select floor(1.1e5); 5 floor(1.1e5) 6 110000.0 7 select floor(cast(-2 as unsigned)), floor(18446744073709551614), floor(-2); 8 Data truncation: data out of range: data type uint64, value '-2' 9 SELECT floor(ceil(12345678901234567890)); 10 floor(ceil(12345678901234567890)) 11 12345678901234567890 12 SELECT floor(18446744073709551616); 13 floor(18446744073709551616) 14 18446744073709551616 15 SELECT floor(floor(18446744073709551616)); 16 floor(floor(18446744073709551616)) 17 18446744073709551616 18 SELECT floor(floor(floor(18446744073709551616))); 19 floor(floor(floor(18446744073709551616))) 20 18446744073709551616 21 CREATE TABLE t1(a BIGINT UNSIGNED); 22 INSERT INTO t1 VALUES(18446744073709551615); 23 SELECT CEILING(a) AS c, FLOOR(a) AS f FROM t1; 24 c f 25 18446744073709551615 18446744073709551615 26 DROP TABLE t1; 27 select floor(999999999999999999); 28 floor(999999999999999999) 29 999999999999999999 30 select floor(9999999999999999999999); 31 floor(9999999999999999999999) 32 9999999999999999999999 33 select floor(9.999999999999999999999); 34 floor(9.999999999999999999999) 35 9 36 select floor(-9.999999999999999999999); 37 floor(-9.999999999999999999999) 38 -10 39 select floor(-999999999999999999999.999); 40 floor(-999999999999999999999.999) 41 -1000000000000000000000 42 CREATE TABLE t1 (col1 INTEGER); 43 CREATE TABLE t2 (col1 INTEGER); 44 INSERT INTO t1 VALUES (1), (2); 45 INSERT INTO t2 VALUES (1), (2); 46 SELECT 47 t1.col1, 48 t2.col1 49 FROM 50 t1, 51 t2 52 WHERE 53 t1.col1 = t2.col1 54 AND t1.col1 = FLOOR(2); 55 col1 col1 56 2 2 57 drop table t1; 58 drop table t2; 59 CREATE TABLE t1 (a VARCHAR(250), b INT, PRIMARY KEY (b)); 60 insert into t1 (a) select (FLOOR(0.13 * 100)); 61 invalid input: invalid default value for column 'b' 62 insert into t1 (a) select FLOOR(1.32 * 100) from t1; 63 invalid input: invalid default value for column 'b' 64 insert into t1 (a) select FLOOR(0.0044 * 100) from t1; 65 invalid input: invalid default value for column 'b' 66 insert into t1 (a) select FLOOR(124.314 * 100) from t1; 67 invalid input: invalid default value for column 'b' 68 insert into t1 (a) select FLOOR(0.3414 * 100) from t1; 69 invalid input: invalid default value for column 'b' 70 insert into t1 (a) select FLOOR(12.4412 * 100) from t1; 71 invalid input: invalid default value for column 'b' 72 insert into t1 (a) select FLOOR(124131.4123 * 100) from t1; 73 invalid input: invalid default value for column 'b' 74 insert into t1 (a) select FLOOR(0.2314114 * 100) from t1; 75 invalid input: invalid default value for column 'b' 76 insert into t1 (a) select FLOOR(13.45123 * 100) from t1; 77 invalid input: invalid default value for column 'b' 78 insert into t1 (a) select FLOOR(0.44521 * 100) from t1; 79 invalid input: invalid default value for column 'b' 80 insert into t1 (a) select FLOOR(0.577134 * 100) from t1; 81 invalid input: invalid default value for column 'b' 82 insert into t1 (a) select FLOOR(1244.53 * 100) from t1; 83 invalid input: invalid default value for column 'b' 84 insert into t1 (a) select FLOOR(6604.642222 * 100) from t1; 85 invalid input: invalid default value for column 'b' 86 insert into t1 (a) select FLOOR(1415.313 * 100) from t1; 87 invalid input: invalid default value for column 'b' 88 SELECT a, 89 (SELECT space(250) FROM t1 i1 90 WHERE i1.b=t1.a ORDER BY 1 LIMIT 1) AS a 91 FROM t1 ORDER BY a LIMIT 5; 92 a a 93 DROP TABLE t1; 94 SELECT DATE(FLOOR(20110512154559.616)); 95 date(floor(20110512154559.616)) 96 2011-05-12 97 CREATE table t1(a int, b float); 98 insert into t1 select floor(12124.413), floor(-4213.413); 99 insert into t1 select floor(12124.123), floor(-42413.409); 100 select distinct floor(a) from t1; 101 floor(a) 102 12124 103 drop table t1; 104 select floor(null); 105 floor(null) 106 null 107 select floor(123.34)-floor(123.03); 108 floor(123.34)-floor(123.03) 109 0 110 drop table if exists t1; 111 create table t1(a INT, b float); 112 insert into t1 select floor(12124.413), floor(-4213.413); 113 insert into t1 select floor(12124.123), floor(-42413.409); 114 select * from t1 where floor(a)=12124; 115 a b 116 12124 -4214.0 117 12124 -42414.0 118 drop table t1; 119 create table t1(a INT, b float); 120 create table t2(a INT, b float); 121 insert into t1 select floor(12124.413), floor(-4213.413); 122 insert into t1 select floor(12124.123), floor(-42413.409); 123 insert into t2 select floor(14124.413), floor(-4213.413); 124 insert into t2 select floor(984798.123), floor(-980.409); 125 SELECT t1.a, t2.a FROM t1 JOIN t2 ON (floor(t1.b) = floor(t2.b)); 126 a a 127 12124 14124 128 drop table t1; 129 drop table t2; 130 drop table if exists t1; 131 create table t1(a INT, b float); 132 insert into t1 select floor(14124.413), floor(-4213.413); 133 insert into t1 select floor(984798.123), floor(-980.409); 134 select b from t1 group by b having floor(b)<-1000; 135 b 136 -4214.0 137 drop table t1; 138 select floor(123.342, 1); 139 floor(123.342, 1) 140 123.3000 141 select floor(123.45, -1); 142 floor(123.45, -1) 143 120.0000 144 select floor(123.342124124214, 100); 145 floor(123.342124124214, 100) 146 123.342124124214 147 select floor(-123.342124124214, -100); 148 Data truncation: data out of range: data type decimal64, floor(decimal64(18,12),-100) 149 select floor(123.342, null); 150 invalid argument the second argument of the floor, bad value not const 151 select floor(null, 1); 152 floor(null, 1) 153 null 154 select floor(null, null); 155 invalid argument the second argument of the floor, bad value not const