github.com/matrixorigin/matrixone@v0.7.0/test/distributed/cases/function/func_math_round.result

select -(0-3),round(-(0-3)), round(9999999999999999999);
-(0-3)	round(-(0-3))	round(9999999999999999999)
3	3	9999999999999999999
select round(5.5),round(-5.5);
round(5.5)	round(-5.5)
6	-6
select round(5.64,1),round(5.64,2),round(5.64,-1),round(5.64,-2);
round(5.64,1)	round(5.64,2)	round(5.64,-1)	round(5.64,-2)
5.6	5.64	10	0
select round(111,-10);
round(111,-10)
0
select round(-5000111000111000155,-1);
round(-5000111000111000155, -1)
-5000111000111000160
select round(15000111000111000155,-1);
round(15000111000111000155, -1)
15000111000111000160
select round(cast(-2 as unsigned), 1), round(18446744073709551614, 1), round(-2, 1);
Data truncation: data out of range: data type uint64, 
select round(4, cast(-2 as unsigned)), round(4, 18446744073709551614), round(4, -2);
Data truncation: data out of range: data type uint64, 
select round(1.5, -4294967296), round(1.5, 4294967296);
round(1.5, -4294967296)	round(1.5, 4294967296)
0	1.5
select round(1.5, -9223372036854775808), round(1.5, 9223372036854775808);
Data truncation: data out of range: data type int64, 
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;
a	ROUND(a)
-1.1	-1.0
1.1	1.0
-1.5	-2.0
1.5	2.0
-1.9	-2.0
1.9	2.0
-2.1	-2.0
2.1	2.0
-2.5	-2.0
2.5	2.0
-2.9	-3.0
2.9	3.0
-1.0E16	-1.0E16
1.0E16	1.0E16
-1.0000000000000002E16	-1.0000000000000002E16
1.0000000000000002E16	1.0000000000000002E16
DROP TABLE t1;
CREATE TABLE t1(f1 DECIMAL(22,1));
INSERT INTO t1 VALUES (0),(1);
SELECT ROUND(f1, f1) FROM t1;
ROUND(f1, f1)
0.0
1.0
SELECT ROUND(f1, f1) FROM t1 GROUP BY 1;
ROUND(f1, f1)
0.0
1.0
DROP TABLE t1;
SELECT ROUND(sin(0.45), cos(0.8));
ROUND(sin(0.45), cos(0.8))
0.4
select round(999999999, -9);
round(999999999, -9)
1000000000
select round(999999999.0, -9);
round(999999999.0, -9)
1000000000
select round(999999999999999999, -18);
round(999999999999999999, -18)
1000000000000000000
select round(999999999999999999.0, -18);
round(999999999999999999.0, -18)
1000000000000000000
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');
select round((length(t1.texte)+length(t1.sujet))/length(t2.author))
from t1 left join t2 on t2.id=t1.id;
round((length(t1.texte)+length(t1.sujet))/length(t2.author))
3
3
4
null
drop table t1;
drop table t2;
select round(cast(-2 as unsigned), 1), round(18446744073709551614, 1), round(-2, 1);
Data truncation: data out of range: data type uint64, 
select round(4, cast(-2 as unsigned)), round(4, 18446744073709551614), round(4, -2);
Data truncation: data out of range: data type uint64, 
select round(10000000000000000000, -19);
round(10000000000000000000, -19)
10000000000000000000
select round(1e0, -309);
round(1e0, -309)
0.0
select round(1e1,308);
round(1e1,308)
10.0
select round(1e1, 2147483648);
round(1e1, 2147483648)
10.0
select round(1.1e1, 4294967295);
round(1.1e1, 4294967295)
11.0
select round(1.12e1, 4294967296);
round(1.12e1, 4294967296)
11.2
select round(1.5, 2147483640);
round(1.5, 2147483640)
1.5
select round(1.5, -2147483649), round(1.5, 2147483648);
round(1.5, -2147483649)	round(1.5, 2147483648)
0	1.5
select round(1.5, -4294967296), round(1.5, 4294967296);
round(1.5, -4294967296)	round(1.5, 4294967296)
0	1.5
select round(1.5, -9223372036854775808), round(1.5, 9223372036854775808);
Data truncation: data out of range: data type int64, 
select round(1.5, 18446744073709551615);
Data truncation: data out of range: data type int64, 
select round(18446744073709551614, -1);
round(18446744073709551614, -1)
18446744073709551610
select round(4, -4294967200);
round(4, -4294967200)
0
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;
SQL syntax error: column "r1" must appear in the GROUP BY clause or be used in an aggregate function
SELECT a, ROUND(sin(100)*10) r2, SUM(1) r1 FROM t1 GROUP BY a;
a	r2	r1
1	-5.0	2
2	-5.0	1
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;
a	r2	r1
1	-5.0	2
2	-5.0	1
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);
a	r2	r1
1	-5.0	2
2	-5.0	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;
a	r2	r1
1	-5.0	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;
a	r2	r1
1	-5.0	2
SELECT a,SUM(b) FROM t1 WHERE a=1 GROUP BY c;
SQL syntax error: column "t1.a" must appear in the GROUP BY clause or be used in an aggregate function
SELECT a*SUM(b) FROM t1 WHERE a=1 GROUP BY c;
SQL syntax error: column "t1.a" must appear in the GROUP BY clause or be used in an aggregate function
SELECT SUM(a)*SUM(b) FROM t1 WHERE a=1 GROUP BY c;
sum(a) * sum(b)
10
SELECT a,SUM(b) FROM t1 WHERE a=1 GROUP BY c HAVING a=1;
SQL syntax error: column "t1.a" must appear in the GROUP BY clause or be used in an aggregate function
SELECT a AS d,SUM(b) FROM t1 WHERE a=1 GROUP BY c HAVING d=1;
SQL syntax error: column "d" must appear in the GROUP BY clause or be used in an aggregate function
SELECT SUM(a)*SUM(b) AS d FROM t1 WHERE a=1 GROUP BY c HAVING d > 0;
SQL syntax error: column "d" must appear in the GROUP BY clause or be used in an aggregate function
SELECT a, ROUND(sin(100)*10) r2 FROM t1;
a	r2
1	-5.0
1	-5.0
2	-5.0
SELECT ROUND(sin(100)*10) FROM t1 GROUP BY ROUND(sin(100)*10);
round(sin(100) * 10)
-5.0
DROP TABLE t1;
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);
i	sum(d1)	sum(d2)
6	-51.40	0
SELECT i, ROUND(SUM(d1), 2), ROUND(SUM(d2), 2) FROM t1 GROUP BY i
HAVING ROUND(SUM(d1), 2) <> ROUND(SUM(d2), 2);
i	sum(d1)	sum(d2)
6	-51.40	0
drop table t1;
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;
ROUND(qty,3)	dps	ROUND(qty,dps)
1.133	3	1.133000
DROP TABLE t1;
CREATE TABLE t1( a DECIMAL, b INT );
INSERT INTO t1 VALUES (123456, 40), (123456, 40);
SELECT ROUND( a, b ) AS c FROM t1 ORDER BY c;
c
123456
123456
SELECT ROUND( a, 100 ) AS c FROM t1 ORDER BY c;
c
123456
123456
CREATE TABLE t2( a DECIMAL, b INT );
INSERT INTO t2 VALUES (123456, 100);
SELECT ROUND( a, b ) AS c FROM t2 ORDER BY c;
c
123456
CREATE TABLE t3( a DECIMAL, b INT );
INSERT INTO t3 VALUES (123456, 40), (123456, 40);
SELECT ROUND( a, b ) AS c FROM t3 ORDER BY c;
c
123456
123456
SELECT ROUND( a, 100 ) AS c FROM t3 ORDER BY c;
c
123456
123456
CREATE TABLE t4( a DECIMAL, b INT );
INSERT INTO t4 VALUES (123456, 40), (123456, 40);
SELECT ROUND( a, b ) AS c FROM t4 ORDER BY c;
c
123456
123456
SELECT ROUND( a, 100 ) AS c FROM t4 ORDER BY c;
c
123456
123456
delete from t1;
INSERT INTO t1 VALUES (1234567890, 20), (999.99, 5);
select round(a,b) as c from t1 order by c;
c
1000
1234567890
drop table t1;
drop table t2;
drop table t3;
drop table t4;
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;
a	b	c
1.000	5	1.000
2.000	4	2.000
3.000	3	3.000
4.000	2	4.000
5.000	1	5.000
SELECT a, b, ROUND( a, b ) AS c FROM t1 ORDER BY c DESC;
a	b	c
5.000	1	5.000
4.000	2	4.000
3.000	3	3.000
2.000	4	2.000
1.000	5	1.000
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;
a	b	MAX(ROUND(c, a))
0	1	1.0000
1	2	1.2000
3	3	1.2350
2	4	1.2300
SELECT a, b, ROUND(c, a) FROM t2;
a	b	ROUND(c, a)
0	1	1.0000
1	2	1.2000
3	3	1.2350
2	4	1.2300
CREATE TABLE t3( a INT, b DECIMAL(6, 3) );
INSERT INTO t3 VALUES( 0, 1.5 );
SELECT ROUND( b, a ) FROM t3;
ROUND( b, a )
2.000
CREATE TABLE t4( a INT, b DECIMAL( 12, 0) );
INSERT INTO t4 VALUES( -9, 1.5e9 );
SELECT ROUND( b, a ) FROM t4;
ROUND( b, a )
2000000000
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;
ROUND( b, a )
2.000000000000
2.000E-9
CREATE TABLE t6( a INT );
INSERT INTO t6 VALUES( 6 / 8 );
SELECT * FROM t6;
a
1
SELECT ROUND(20061108085411.000002);
ROUND(20061108085411.000002)
20061108085411
drop table t1;
drop table t2;
drop table t3;
drop table t4;
drop table t5;
drop table t6;
CREATE TABLE t5(c1 DECIMAL(16,6), c2 tinyint);
INSERT INTO t5 VALUES(1.1325,3);
SELECT ROUND(c1,c2) from t5;
ROUND(c1,c2)
1.133000
DROP TABLE t5;
SELECT ROUND(NULL);
ROUND(NULL)
null
select round(123.54)-round(123.03);
round(123.54)-round(123.03)
1
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;
a	b
12124	-4213.41
12124	-42413.4
drop table t1;
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));
a	a
12124	14124
drop table t1;
drop table t2;