github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/function/func_math_round.test

#SELECT, extreme value


select -(0-3),round(-(0-3)), round(9999999999999999999);


select round(5.5),round(-5.5);
select round(5.64,1),round(5.64,2),round(5.64,-1),round(5.64,-2);

#INSERT
#0.5 MO暂不支持Create select
#create table t1 select round(1, 6);
#select * from t1;
#drop table t1;

#extreme value
select round(111,-10);
select round(-5000111000111000155,-1);


select round(15000111000111000155,-1);
select round(cast(-2 as unsigned), 1), round(18446744073709551614, 1), round(-2, 1);
select round(4, cast(-2 as unsigned)), round(4, 18446744073709551614), round(4, -2);

select round(1.5, -4294967296), round(1.5, 4294967296);
select round(1.5, -9223372036854775808), round(1.5, 9223372036854775808);


#data type
CREATE TABLE t1 (a DOUBLE);
INSERT INTO t1 VALUES (-1.1), (1.1),
(-1.5), (1.5),
(-1.9), (1.9),
(-2.1), (2.1),
(-2.5), (2.5),
(-2.9), (2.9),
(-1e16 - 0.5), (1e16 + 0.5),
(-1e16 - 1.5), (1e16 + 1.5);
SELECT a, ROUND(a) FROM t1;
DROP TABLE t1;

#data type
-- @bvt:issue#2302
CREATE TABLE t1(f1 DECIMAL(22,1));
INSERT INTO t1 VALUES (0),(1);
SELECT ROUND(f1, f1) FROM t1;
SELECT ROUND(f1, f1) FROM t1 GROUP BY 1;
DROP TABLE t1;
-- @bvt:issue

#嵌套 
#LEAST函数0.5MO暂时不支持
#SELECT ROUND(LEAST(15, -4939092, 0.2704), STDDEV('a'));


SELECT ROUND(sin(0.45), cos(0.8));


#extreme value

select round(999999999, -9);
select round(999999999.0, -9);
select round(999999999999999999, -18);
select round(999999999999999999.0, -18);
-- @bvt:issue

#嵌套，join 
CREATE TABLE t1 (
id           VARCHAR(80) NOT NULL PRIMARY KEY,
sujet        VARCHAR(80),
motsclefs    varchar(255),
texte        varchar(255)
);
INSERT INTO t1 VALUES('123','toto','essai','test');
INSERT INTO t1 VALUES('456','droit','penal','lawyer');
INSERT INTO t1 VALUES('789','aaaaa','bbbbb','cccccc');
CREATE TABLE t2 (
id         VARCHAR(255) NOT NULL,
author     VARCHAR(255) NOT NULL
);
INSERT INTO t2 VALUES('123', 'moi');
INSERT INTO t2 VALUES('123', 'lui');
INSERT INTO t2 VALUES('456', 'lui');
#match against 0.5 MO暂不支持
#select round(match(t1.texte,t1.sujet,t1.motsclefs) against('droit'),5)
#from t1 left join t2 on t2.id=t1.id;
#select match(t1.texte,t1.sujet,t1.motsclefs) against('droit' IN BOOLEAN MODE)
#from t1 left join t2 on t2.id=t1.id;
select round((length(t1.texte)+length(t1.sujet))/length(t2.author))
from t1 left join t2 on t2.id=t1.id;
drop table t1;
drop table t2;

#嵌套，extreme value, 科学计算

select round(cast(-2 as unsigned), 1), round(18446744073709551614, 1), round(-2, 1);
select round(4, cast(-2 as unsigned)), round(4, 18446744073709551614), round(4, -2);
select round(10000000000000000000, -19);
select round(1e0, -309);
select round(1e1,308);
select round(1e1, 2147483648);
select round(1.1e1, 4294967295);
select round(1.12e1, 4294967296);
select round(1.5, 2147483640);
select round(1.5, -2147483649), round(1.5, 2147483648);
select round(1.5, -4294967296), round(1.5, 4294967296);
select round(1.5, -9223372036854775808), round(1.5, 9223372036854775808);
select round(1.5, 18446744073709551615);
select round(18446744073709551614, -1);
select round(4, -4294967200);

#比较操作
CREATE TABLE t1 (a INTEGER, b INTEGER, c INTEGER);
INSERT INTO t1 (a,b) VALUES (1,2),(1,3),(2,5);
SELECT a, 0.1*0+1 r2, SUM(1) r1 FROM t1 WHERE a = 1 GROUP BY a HAVING r1>1 AND r2=1;
SELECT a, ROUND(sin(100)*10) r2, SUM(1) r1 FROM t1 GROUP BY a;
SELECT a, ROUND(sin(100)*10) r2, SUM(1) r1 FROM t1 GROUP BY a
HAVING SUM(1) >0 AND ROUND(sin(100)*10)<0 AND ROUND(sin(100)*10) > -10;
SELECT a, ROUND(sin(100)*10) r2, SUM(1) r1 FROM t1 GROUP BY a
HAVING SUM(1)>=1 AND (SELECT ROUND(sin(100)*10)>-100 AND ROUND(sin(100)*10) < 0 FROM t1 AS t2 LIMIT 1);
SELECT a, ROUND(sin(100)*10) r2, SUM(1) r1 FROM t1 WHERE a = 1
GROUP BY a HAVING SUM(1)>1 AND ROUND(sin(100)*10)<=2;
SELECT a, ROUND(sin(100)*10) r2, SUM(1) r1 FROM t1 WHERE a = 1
GROUP BY a HAVING SUM(1)>1 AND ROUND(sin(100)*10)<=2 ORDER BY a+r2+r1;
SELECT a,SUM(b) FROM t1 WHERE a=1 GROUP BY c;
SELECT a*SUM(b) FROM t1 WHERE a=1 GROUP BY c;
SELECT SUM(a)*SUM(b) FROM t1 WHERE a=1 GROUP BY c;
SELECT a,SUM(b) FROM t1 WHERE a=1 GROUP BY c HAVING a=1;
SELECT a AS d,SUM(b) FROM t1 WHERE a=1 GROUP BY c HAVING d=1;
SELECT SUM(a)*SUM(b) AS d FROM t1 WHERE a=1 GROUP BY c HAVING d > 0;
SELECT a, ROUND(sin(100)*10) r2 FROM t1;
SELECT ROUND(sin(100)*10) FROM t1 GROUP BY ROUND(sin(100)*10);
DROP TABLE t1;

#HAVING，比较操作

CREATE TABLE t1 (i INT, d1 DECIMAL(9,2), d2 DECIMAL(9,2));
INSERT INTO t1 VALUES (1, 101.40, 21.40), (1, -80.00, 0.00),
(2, 0.00, 0.00), (2, -13.20, 0.00), (2, 59.60, 46.40),
(2, 30.40, 30.40), (3, 37.00, 7.40), (3, -29.60, 0.00),
(4, 60.00, 15.40), (4, -10.60, 0.00), (4, -34.00, 0.00),
(5, 33.00, 0.00), (5, -25.80, 0.00), (5, 0.00, 7.20),
(6, 0.00, 0.00), (6, -51.40, 0.00);
SELECT i, SUM(d1), SUM(d2) FROM t1 GROUP BY i HAVING SUM(d1) <> SUM(d2);
-- @bvt:issue#2302
SELECT i, ROUND(SUM(d1), 2), ROUND(SUM(d2), 2) FROM t1 GROUP BY i
HAVING ROUND(SUM(d1), 2) <> ROUND(SUM(d2), 2);
-- @bvt:issue
drop table t1;


#DATA TYPE
#0.5 MO的TRUNCATE函数暂不支持
-- @bvt:issue#2302
CREATE TABLE t1 (qty decimal(16,6) default NULL, dps tinyint(3) unsigned default NULL);
INSERT INTO t1 VALUES (1.1325,3);
SELECT ROUND(qty,3), dps, ROUND(qty,dps) FROM t1;
DROP TABLE t1;
-- @bvt:issue

-- @bvt:issue#2302
CREATE TABLE t1( a DECIMAL, b INT );
INSERT INTO t1 VALUES (123456, 40), (123456, 40);
#SELECT TRUNCATE( a, b ) AS c FROM t1 ORDER BY c;
SELECT ROUND( a, b ) AS c FROM t1 ORDER BY c;
SELECT ROUND( a, 100 ) AS c FROM t1 ORDER BY c;
CREATE TABLE t2( a DECIMAL, b INT );
INSERT INTO t2 VALUES (123456, 100);
#SELECT TRUNCATE( a, b ) AS c FROM t2 ORDER BY c;
SELECT ROUND( a, b ) AS c FROM t2 ORDER BY c;
CREATE TABLE t3( a DECIMAL, b INT );
INSERT INTO t3 VALUES (123456, 40), (123456, 40);
#SELECT TRUNCATE( a, b ) AS c FROM t3 ORDER BY c;
SELECT ROUND( a, b ) AS c FROM t3 ORDER BY c;
SELECT ROUND( a, 100 ) AS c FROM t3 ORDER BY c;
CREATE TABLE t4( a DECIMAL, b INT );
INSERT INTO t4 VALUES (123456, 40), (123456, 40);
#SELECT TRUNCATE( a, b ) AS c FROM t4 ORDER BY c;
SELECT ROUND( a, b ) AS c FROM t4 ORDER BY c;
SELECT ROUND( a, 100 ) AS c FROM t4 ORDER BY c;
-- @ignore{
delete from t1;
INSERT INTO t1 VALUES (1234567890, 20), (999.99, 5);
select round(a,b) as c from t1 order by c;
-- @ignore}
drop table t1;
drop table t2;
drop table t3;
drop table t4;

-- @bvt:issue

-- @bvt:issue#2302
CREATE TABLE t1( a DECIMAL(4, 3), b INT );
INSERT INTO t1 VALUES ( 1, 5 ), ( 2, 4 ), ( 3, 3 ), ( 4, 2 ), ( 5, 1 );
SELECT a, b, ROUND( a, b ) AS c FROM t1 ORDER BY c;
SELECT a, b, ROUND( a, b ) AS c FROM t1 ORDER BY c DESC;
CREATE TABLE t2 ( a INT, b INT, c DECIMAL(5, 4) );
INSERT INTO t2 VALUES ( 0, 1, 1.2345 ), ( 1, 2, 1.2345 ),( 3, 3, 1.2345 ), ( 2, 4, 1.2345 );
SELECT a, b, MAX(ROUND(c, a)) FROM t2 GROUP BY a, b ORDER BY b;
SELECT a, b, ROUND(c, a) FROM t2;
CREATE TABLE t3( a INT, b DECIMAL(6, 3) );
INSERT INTO t3 VALUES( 0, 1.5 );
SELECT ROUND( b, a ) FROM t3;
CREATE TABLE t4( a INT, b DECIMAL( 12, 0) );
INSERT INTO t4 VALUES( -9, 1.5e9 );
SELECT ROUND( b, a ) FROM t4;
CREATE TABLE t5( a INT, b DECIMAL( 13, 12 ) );
INSERT INTO t5 VALUES( 0, 1.5 );
INSERT INTO t5 VALUES( 9, 1.5e-9 );
SELECT ROUND( b, a ) FROM t5;
CREATE TABLE t6( a INT );
INSERT INTO t6 VALUES( 6 / 8 );
SELECT * FROM t6;
SELECT ROUND(20061108085411.000002);
drop table t1;
drop table t2;
drop table t3;
drop table t4;
drop table t5;
drop table t6;
-- @bvt:issue

-- @bvt:issue#2302
CREATE TABLE t5(c1 DECIMAL(16,6), c2 tinyint);
INSERT INTO t5 VALUES(1.1325,3);
SELECT ROUND(c1,c2) from t5;
#select TRUNCATE(c1,c2) FROM t5;
DROP TABLE t5;
-- @bvt:issue

#null 
SELECT ROUND(NULL);

#算术操作
select round(123.54)-round(123.03);

#WHERE
drop table if exists t1;
create table t1(a INT,  b float);
insert into t1 values(12124, -4213.413), (12124, -42413.409);
select * from t1 where round(a)=12124;
drop table t1;

#ON CONDITION
create table t1(a INT,  b float);
create table t2(a INT,  b float);
insert into t1 select round(12124.413), round(-4213.413);
insert into t1 select round(12124.123), round(-42413.409);
insert into t2 select round(14124.413), round(-4213.413);
insert into t2 select round(984798.123), round(-980.409);
SELECT t1.a, t2.a FROM t1 JOIN t2 ON (round(t1.b) = round(t2.b));
drop table t1;
drop table t2;