[functions] more functions working with number

[sundy-li]

Summary

From MySQL docs in https://dev.mysql.com/doc/refman/8.0/en/numeric-functions.html

Tasks list:

If you are interested in one or more tasks in this issue, you can comment like: /assignme abs functions to make this subtask assigned to you, then I'll mark that as wip.

• [x] add a development doc to guide developers.
• [ ] document of the below functions, refer: https://dev.mysql.com/doc/refman/8.0/en/mathematical-functions.html
• [x] %, MOD Modulo operator
• [x] * Multiplication operator
• [x] + Addition operator
• [x] - Minus operator
• [x] - Change the sign of the argument
• [x] / Division operator
• [x] ABS() Return the absolute value [#2596]
• [x] ACOS() Return the arc cosine [#2888]
• [x] ASIN() Return the arc sine [#2888]
• [x] ATAN() Return the arc tangent [#2888]
• [x] ATAN2(), ATAN() Return the arc tangent of the two arguments [#2888]
• [x] CEIL() Return the smallest integer value not less than the argument[#2759]
• [x] CEILING() Return the smallest integer value not less than the argument[#2759]
• [wip] CONV() Convert numbers between different number bases
• [x] COS() Return the cosine [#2610]
• [x] COT() Return the cotangent [#2610]
• [x] CRC32() Compute a cyclic redundancy check value [#2616]
• [x] DEGREES() Convert radians to degrees [#2643]
• [x] DIV Integer division
• [x] EXP() Raise to the power of e [#2847]
• [x] FLOOR() Return the largest integer value not greater than the argument[#2759]
• [x] LN() Return the natural logarithm of the argument [#2733]
• [x] LOG() Return the natural logarithm of the first argument [#2669 ]
• [x] LOG10() Return the base-10 logarithm of the argument [#2733 ]
• [x] LOG2() Return the base-2 logarithm of the argument [#2733 ]
• [x] MOD() Return the remainder [#2850 ]
• [x] PI() Return the value of pi [#2584]
• [x] POW() Return the argument raised to the specified power [#2949]
• [x] POWER() Return the argument raised to the specified power [#2949]
• [x] RADIANS() Return argument converted to radians [#2643]
• [WIP] RAND() Return a random floating-point value
• [x] ROUND() Round the argument [#2923]
• [x] SIGN() Return the sign of the argument [#2906]
• [x] SIN() Return the sine of the argument [#2610]
• [x] SQRT() Return the square root of the argument [#2905 ]
• [x] TAN() Return the tangent of the argument [#2610]
• [x] TRUNCATE() Truncate to specified number of decimal places [#2923]

[flaneur2020]

/assignme abs function

[flaneur2020]

/assignme ABS() Return the absolute value

[flaneur2020]

you can comment like: /assignme abs functions to make this subtask assigned to you.

hmm, i tried /assignme abs functions but it assigned this issue to me but not the subtask, is this behaviour as expected?

[BohuTANG]

you can comment like: /assignme abs functions to make this subtask assigned to you.

hmm, i tried /assignme abs functions but it assigned this issue to me but not the subtask, is this behaviour as expected?

I guess it's OK, @sundy-li will maintain this issue :)

[junnplus]

PI() Return the value of pi

take this

[PsiACE]

/assignme rand() function

[junnplus]

/assignme sin() cos() cot() tan() function

[flaneur2020]

/assignme LOG(), LOG10(), LOG2()

[junnplus]

/assignme crc32()

[junnplus]

/assignme DEGREES() RADIANS()

[junnplus]

/assignme ACOS() ASIN() ATAN() ATAN2()

[kyon0304]

/assignme CEIL() CEILING() FLOOR()

[mshauneu]

/assignme sign() function

[mshauneu]

/assignme ROUND() Round the argument

[junnplus]

/assignme POW() POWER()

[mshauneu]

/assignme TRUNCATE() Truncate to specified number of decimal places

[mshauneu]

/assignme DIV Integer division

[junnplus]

hi @sundy-li , Can you update the issue? CONV DIV RAND is wip, others have been completed.

[PsiACE]

/assignme rand() function

I need to unassign this task for now. Sorry I should manage my task list more sensibly.

also, @Junnplus would you be interested in taking it?

[mshauneu]

/assignme rand() function

[mshauneu]

@PsiACE @sundy-li I am working on DIV operator. To finish it I need a help:

• DIV operator reqs change of sqlparser-rs. So I did it and submit PR (https://github.com/datafuse-extras/sqlparser-rs/pull/9/files) pls take a look...
• impl of DIV operator would be simple if databend / operator comply with mysql, especially for case when delimiter is 0:

  
  mysql -uroot -e 'SELECT 1 / 0'
  +-------+
  | 1 / 0 |
  +-------+
  |  NULL |
  +-------+ 

mysql -h0.0.0.0 -P3307 -uroot -e 'SELECT 1 / 0' +---------+ | (1 / 0) | +---------+ | inf | +---------+

That complicate 'SELECT 1 DIV 0' case much.

I can share my code without support of SELECT 1 DIV 0 for now...with hope databend change `/ 0` in nearest feature.

[sundy-li]

@mshauneu we can have a new function named intDiv and map the div operator into this function.

For MySQL return NULL if divided by zero, it's related to config ERROR_FOR_DIVISION_BY_ZERO in MySQL. This config will not be introduced in databend, it's too complicated.

I think it is ok to output inf, because 3/0 = 3.0f64/0.0f64 = f64::INFINITY. Some design of MySQL is poor, no need to be compatible with that.

[Xuanwo]

@soyeric128 Please take a look at this issue.

[sundy-li]

@soyeric128 Please take a look at this issue.

We will use other issues to track the doc.