[Bug]: floor result wrong

[dengn]

Is there an existing issue for the same bug?

• [X] I have checked the existing issues.

Environment

- Version or commit-id (e.g. v0.1.0 or 8b23a93): 6dedae20180c9e7ad4159ef0c7ad0f8e8c358dee
- Hardware parameters:Mac M1 ARM 16GB Memory
- OS type:OSX
- Others:

Actual Behavior

[SQL STATEMENT]: SELECT floor(ceil(12345678901234567890)); [EXPECT RESULT]: floor(ceil(12345678901234567890)) 12345678901234567890 [ACTUAL RESULT]: floor(ceil(12345678901234567890)) -6101065172474983726

[ERROR] [SCRIPT FILE]: cases/function/func_math_floor.test [ROW NUMBER]: 7 [SQL STATEMENT]: select floor(cast(-2 as unsigned)), floor(18446744073709551614), floor(-2); [EXPECT RESULT]: floor(cast(-2 as unsigned)) floor(18446744073709551614) floor(-2) 18446744073709551614 18446744073709551614 -2 [ACTUAL RESULT]: floor(cast(-2 as unsigned unsigned)) floor(18446744073709551614) floor(-2) 18446744073709551614 -2 -2

[ERROR] [SCRIPT FILE]: cases/function/func_math_floor.test [ROW NUMBER]: 22 [SQL STATEMENT]: select floor(9.999999999999999999999); [EXPECT RESULT]: floor(9.999999999999999999999) 9 [ACTUAL RESULT]: floor(9.999999999999999999999) 10.0

Expected Behavior

Steps to Reproduce

Additional information

No response

[broccoliSpicy]

this behavior is conformant with the floating point number definition

[fengttt]

Clear your tag before assign to test. Thank you.

[dongdongyang33]

[SQL STATEMENT]: SELECT floor(ceil(12345678901234567890)); [EXPECT RESULT]: floor(ceil(12345678901234567890)) 12345678901234567890 [ACTUAL RESULT]: floor(ceil(12345678901234567890)) -6101065172474983726

CURRENT [ACTUAL RESULT]: floor(ceil(12345678901234567890)) 12345678901234567890

This issue has been fixed.

[dongdongyang33]

[SQL STATEMENT]: select floor(cast(-2 as unsigned)), floor(18446744073709551614), floor(-2); [EXPECT RESULT]: floor(cast(-2 as unsigned)) floor(18446744073709551614) floor(-2) 18446744073709551614 18446744073709551614 -2 [ACTUAL RESULT]: floor(cast(-2 as unsigned unsigned)) floor(18446744073709551614) floor(-2) 18446744073709551614 -2 -2

CURRENT [ACTUAL RESULT]: overflow from bigint to bigint unsigned

this is a new issue caused by PR https://github.com/matrixorigin/matrixone/pull/3434 Create a new issue https://github.com/matrixorigin/matrixone/issues/3550 for it

[dongdongyang33]

[SQL STATEMENT]: select floor(9.999999999999999999999); [EXPECT RESULT]: floor(9.999999999999999999999) 9 [ACTUAL RESULT]: floor(9.999999999999999999999) 10.0

will be fixed in 0.6 #3579

[iamlinjunhong]

fix in pr #4483. It doesn't relate to the issue #3579

[aressu1985]

mysql> select cast(-19999999999999999999 as signed); ERROR 20201 (HY000): data out of range: data type int64, value '-19999999999999999999' mysql> mysql> mysql> mysql> mysql> SELECT floor(ceil(12345678901234567890)); +-----------------------------------+ | floor(ceil(12345678901234567890)) | +-----------------------------------+ | 12345678901234567890 | +-----------------------------------+ 1 row in set (0.00 sec)

mysql> select floor(cast(-2 as unsigned)), floor(18446744073709551614), floor(-2); ERROR 20201 (HY000): data out of range: data type uint64, mysql> select floor(9.999999999999999999999); +--------------------------------+ | floor(9.999999999999999999999) | +--------------------------------+ | 9 | +--------------------------------+ 1 row in set (0.00 sec)

mysql>