[BUG] Some math functions return types are different between V2 and V3

[xinyual]

What is the bug?
Some math functions returns wrong schema in doc test.
I get error log.

2025-03-25T06:17:52.6812429Z Failed example:
2025-03-25T06:17:52.6813884Z ppl_cmd.process('source=people | eval CEILING(0) = CEILING(0), CEILING(50.00005) = CEILING(50.00005), CEILING(-50.00005) = CEILING(-50.00005) | fields CEILING(0), CEILING(50.00005), CEILING(-50.00005)')Expected:
2025-03-25T06:17:52.6815163Z fetched rows / total rows = 1/1
2025-03-25T06:17:52.6815594Z +------------+-------------------+--------------------+
2025-03-25T06:17:52.6816087Z | CEILING(0) | CEILING(50.00005) | CEILING(-50.00005) |
2025-03-25T06:17:52.6816602Z |------------+-------------------+--------------------|
2025-03-25T06:17:52.6817041Z | 0 | 51 | -50 |
2025-03-25T06:17:52.6817477Z +------------+-------------------+--------------------+
2025-03-25T06:17:52.6817877Z Got:
2025-03-25T06:17:52.6818522Z fetched rows / total rows = 1/1
2025-03-25T06:17:52.6818953Z +------------+-------------------+--------------------+
2025-03-25T06:17:52.6819425Z | CEILING(0) | CEILING(50.00005) | CEILING(-50.00005) |
2025-03-25T06:17:52.6819898Z |------------+-------------------+--------------------|
2025-03-25T06:17:52.6820335Z | 0 | 51.0 | -50.0 |
2025-03-25T06:17:52.6820762Z +------------+-------------------+--------------------+
2025-03-25T06:17:52.6821247Z ----------------------------------------------------------------------
2025-03-25T06:17:52.6821951Z File "/__w/sql/sql/doctest/../docs/user/ppl/functions/math.rst", line 159, in math.rst
2025-03-25T06:17:52.6822569Z Failed example:
2025-03-25T06:17:52.6824485Z ppl_cmd.process('source=people | eval CEILING(3147483647.12345) = CEILING(3147483647.12345), CEILING(113147483647.12345) = CEILING(113147483647.12345), CEILING(3147483647.00001) = CEILING(3147483647.00001) | fields CEILING(3147483647.12345), CEILING(113147483647.12345), CEILING(3147483647.00001)')Expected:
2025-03-25T06:17:52.6826199Z fetched rows / total rows = 1/1
2025-03-25T06:17:52.6826697Z +---------------------------+-----------------------------+---------------------------+
2025-03-25T06:17:52.6827402Z | CEILING(3147483647.12345) | CEILING(113147483647.12345) | CEILING(3147483647.00001) |
2025-03-25T06:17:52.6828327Z |---------------------------+-----------------------------+---------------------------|
2025-03-25T06:17:52.6828940Z | 3147483648 | 113147483648 | 3147483648 |
2025-03-25T06:17:52.6829548Z +---------------------------+-----------------------------+---------------------------+
2025-03-25T06:17:52.6830046Z Got:
2025-03-25T06:17:52.6830328Z fetched rows / total rows = 1/1
2025-03-25T06:17:52.6830822Z +---------------------------+-----------------------------+---------------------------+
2025-03-25T06:17:52.6831534Z | CEILING(3147483647.12345) | CEILING(113147483647.12345) | CEILING(3147483647.00001) |
2025-03-25T06:17:52.6832229Z |---------------------------+-----------------------------+---------------------------|
2025-03-25T06:17:52.6833505Z | 3147483648.0 | 113147483648.0 | 3147483648.0 |
2025-03-25T06:17:52.6834156Z +---------------------------+-----------------------------+---------------------------+
2025-03-25T06:17:52.6834735Z ----------------------------------------------------------------------
2025-03-25T06:17:52.6835439Z File "/__w/sql/sql/doctest/../docs/user/ppl/functions/math.rst", line 343, in math.rst
2025-03-25T06:17:52.6859708Z Failed example:
2025-03-25T06:17:52.6860688Z ppl_cmd.process('source=people | eval FLOOR(0) = FLOOR(0), FLOOR(50.00005) = FLOOR(50.00005), FLOOR(-50.00005) = FLOOR(-50.00005) | fields FLOOR(0), FLOOR(50.00005), FLOOR(-50.00005)')Expected:
2025-03-25T06:17:52.6861746Z fetched rows / total rows = 1/1
2025-03-25T06:17:52.6862172Z +----------+-----------------+------------------+
2025-03-25T06:17:52.6862634Z | FLOOR(0) | FLOOR(50.00005) | FLOOR(-50.00005) |
2025-03-25T06:17:52.6893451Z |----------+-----------------+------------------|
2025-03-25T06:17:52.6893926Z | 0 | 50 | -51 |
2025-03-25T06:17:52.6894380Z +----------+-----------------+------------------+
2025-03-25T06:17:52.6894769Z Got:
2025-03-25T06:17:52.6895043Z fetched rows / total rows = 1/1
2025-03-25T06:17:52.6895450Z +----------+-----------------+------------------+
2025-03-25T06:17:52.6895886Z | FLOOR(0) | FLOOR(50.00005) | FLOOR(-50.00005) |
2025-03-25T06:17:52.6896313Z |----------+-----------------+------------------|
2025-03-25T06:17:52.6896720Z | 0 | 50.0 | -51.0 |
2025-03-25T06:17:52.6897124Z +----------+-----------------+------------------+
2025-03-25T06:17:52.6897604Z ----------------------------------------------------------------------
2025-03-25T06:17:52.6898583Z File "/__w/sql/sql/doctest/../docs/user/ppl/functions/math.rst", line 351, in math.rst
2025-03-25T06:17:52.6899214Z Failed example:
2025-03-25T06:17:52.6900671Z ppl_cmd.process('source=people | eval FLOOR(3147483647.12345) = FLOOR(3147483647.12345), FLOOR(113147483647.12345) = FLOOR(113147483647.12345), FLOOR(3147483647.00001) = FLOOR(3147483647.00001) | fields FLOOR(3147483647.12345), FLOOR(113147483647.12345), FLOOR(3147483647.00001)')Expected:
2025-03-25T06:17:52.6902237Z fetched rows / total rows = 1/1
2025-03-25T06:17:52.6902722Z +-------------------------+---------------------------+-------------------------+
2025-03-25T06:17:52.6903542Z | FLOOR(3147483647.12345) | FLOOR(113147483647.12345) | FLOOR(3147483647.00001) |
2025-03-25T06:17:52.6904177Z |-------------------------+---------------------------+-------------------------|
2025-03-25T06:17:52.6904766Z | 3147483647 | 113147483647 | 3147483647 |
2025-03-25T06:17:52.6905347Z +-------------------------+---------------------------+-------------------------+
2025-03-25T06:17:52.6905808Z Got:
2025-03-25T06:17:52.6906084Z fetched rows / total rows = 1/1
2025-03-25T06:17:52.6906550Z +-------------------------+---------------------------+-------------------------+
2025-03-25T06:17:52.6907396Z | FLOOR(3147483647.12345) | FLOOR(113147483647.12345) | FLOOR(3147483647.00001) |
2025-03-25T06:17:52.6908035Z |-------------------------+---------------------------+-------------------------|
2025-03-25T06:17:52.6908628Z | 3147483647.0 | 113147483647.0 | 3147483647.0 |
2025-03-25T06:17:52.6909212Z +-------------------------+---------------------------+-------------------------+
2025-03-25T06:17:52.6909764Z ----------------------------------------------------------------------
2025-03-25T06:17:52.6910469Z File "/__w/sql/sql/doctest/../docs/user/ppl/functions/math.rst", line 359, in math.rst
2025-03-25T06:17:52.6911092Z Failed example:
2025-03-25T06:17:52.6913052Z ppl_cmd.process('source=people | eval FLOOR(282474973688888.022) = FLOOR(282474973688888.022), FLOOR(9223372036854775807.022) = FLOOR(9223372036854775807.022), FLOOR(9223372036854775807.0000001) = FLOOR(9223372036854775807.0000001) | fields FLOOR(282474973688888.022), FLOOR(9223372036854775807.022), FLOOR(9223372036854775807.0000001)')Expected:
2025-03-25T06:17:52.6914927Z fetched rows / total rows = 1/1
2025-03-25T06:17:52.6915452Z +----------------------------+--------------------------------+------------------------------------+
2025-03-25T06:17:52.6916251Z | FLOOR(282474973688888.022) | FLOOR(9223372036854775807.022) | FLOOR(9223372036854775807.0000001) |
2025-03-25T06:17:52.6917025Z |----------------------------+--------------------------------+------------------------------------|
2025-03-25T06:17:52.6917754Z | 282474973688888 | 9223372036854775807 | 9223372036854775807 |
2025-03-25T06:17:52.6918481Z +----------------------------+--------------------------------+------------------------------------+
2025-03-25T06:17:52.6918997Z Got:
2025-03-25T06:17:52.6919276Z fetched rows / total rows = 1/1
2025-03-25T06:17:52.6919776Z +----------------------------+--------------------------------+------------------------------------+
2025-03-25T06:17:52.6920552Z | FLOOR(282474973688888.022) | FLOOR(9223372036854775807.022) | FLOOR(9223372036854775807.0000001) |
2025-03-25T06:17:52.6921330Z |----------------------------+--------------------------------+------------------------------------|
2025-03-25T06:17:52.6922063Z | 282474973688888.0 | 9.223372036854776e+18 | 9.223372036854776e+18 |
2025-03-25T06:17:52.6922938Z +----------------------------+--------------------------------+------------------------------------+
2025-03-25T06:17:52.6923555Z ----------------------------------------------------------------------
2025-03-25T06:17:52.6924422Z File "/__w/sql/sql/doctest/../docs/user/ppl/functions/math.rst", line 597, in math.rst
2025-03-25T06:17:52.6925054Z Failed example:
2025-03-25T06:17:52.6925570Z ppl_cmd.process('source=people | eval RAND(3) = RAND(3) | fields RAND(3)')Expected:
2025-03-25T06:17:52.6926202Z fetched rows / total rows = 1/1
2025-03-25T06:17:52.6926578Z +------------+
2025-03-25T06:17:52.6926863Z | RAND(3) |
2025-03-25T06:17:52.6927146Z |------------|
2025-03-25T06:17:52.6927400Z | 0.73105735 |
2025-03-25T06:17:52.6927668Z +------------+
2025-03-25T06:17:52.6927938Z Got:
2025-03-25T06:17:52.6928212Z fetched rows / total rows = 1/1
2025-03-25T06:17:52.6928584Z +---------------------+
2025-03-25T06:17:52.6928913Z | RAND(3) |
2025-03-25T06:17:52.6929232Z |---------------------|
2025-03-25T06:17:52.6929547Z | 0.34346429521113886 |
2025-03-25T06:17:52.6929862Z +---------------------+
2025-03-25T06:17:52.6930261Z ----------------------------------------------------------------------
2025-03-25T06:17:52.6930963Z File "/__w/sql/sql/doctest/../docs/user/ppl/functions/math.rst", line 646, in math.rst
2025-03-25T06:17:52.6931589Z Failed example:
2025-03-25T06:17:52.6932323Z ppl_cmd.process('source=people | eval SIGN(1) = SIGN(1), SIGN(0) = SIGN(0), SIGN(-1.1) = SIGN(-1.1) | fields SIGN(1), SIGN(0), SIGN(-1.1)')Expected:
2025-03-25T06:17:52.6933473Z fetched rows / total rows = 1/1
2025-03-25T06:17:52.6933860Z +---------+---------+------------+
2025-03-25T06:17:52.6934240Z | SIGN(1) | SIGN(0) | SIGN(-1.1) |
2025-03-25T06:17:52.6934616Z |---------+---------+------------|
2025-03-25T06:17:52.6934980Z | 1 | 0 | -1 |
2025-03-25T06:17:52.6935347Z +---------+---------+------------+
2025-03-25T06:17:52.6935693Z Got:
2025-03-25T06:17:52.6935959Z fetched rows / total rows = 1/1
2025-03-25T06:17:52.6936335Z +---------+---------+------------+
2025-03-25T06:17:52.6936713Z | SIGN(1) | SIGN(0) | SIGN(-1.1) |
2025-03-25T06:17:52.6937087Z |---------+---------+------------|
2025-03-25T06:17:52.6937443Z | 1 | 0 | -1.0 |
2025-03-25T06:17:52.6937800Z +---------+---------+------------+
2025-03-25T06:17:52.6938080Z

How can one reproduce the bug?
Steps to reproduce the behavior:

1. Go to '...'
2. Click on '....'
3. Scroll down to '....'
4. See error

What is the expected behavior?
For function like SIGN, ceiling and floor, we should keep the return type in schema same as input data. And for random function like RAND, we should not include it in doc test.

What is your host/environment?

• OS: [e.g. iOS]
• Version [e.g. 22]
• Plugins

Do you have any screenshots?
If applicable, add screenshots to help explain your problem.

Do you have any additional context?
Add any other context about the problem.

[xinyual]

cc. @LantaoJin

[LantaoJin]

CEILING, FLOOR, SIGN in Calcite (V3) will return the same type with input argument.

[LantaoJin]

Resolved by #3390