[ https://issues.apache.org/jira/browse/CALCITE-1908?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16112445#comment-16112445 ]
Julian Hyde commented on CALCITE-1908: -------------------------------------- Two issues relating to {{MOD}} should be aware of each other. If we add {{MOD(<DOUBLE>, <DOUBLE>)}} then we should also add {{<DOUBLE> % <DOUBLE>}}. Whichever PR gets accepted second should do the extra work. > Mod function got exception in MOD(34.5,3), MOD(19,6.7) situation. > ----------------------------------------------------------------- > > Key: CALCITE-1908 > URL: https://issues.apache.org/jira/browse/CALCITE-1908 > Project: Calcite > Issue Type: Bug > Reporter: sunjincheng > > Calcite MOD definition: > {code} > // Return type is same as divisor (2nd operand) > // SQL2003 Part2 Section 6.27, Syntax Rules 9 > new SqlFunction( > "MOD", > SqlKind.OTHER_FUNCTION, > ReturnTypes.ARG1_NULLABLE, > null, > OperandTypes.EXACT_NUMERIC_EXACT_NUMERIC, > SqlFunctionCategory.NUMERIC); > {code} > Calcite Document description: > .bq MOD(numeric, numeric) Returns the remainder (modulus) of numeric1 > divided by numeric2. The result is negative only if numeric1 is negative > {noformat} > MOD(34.5,3), mod(19,6.7) 异常: > org.apache.calcite.sql.validate.SqlValidatorException: Cannot apply 'MOD' to > arguments of type 'MOD(<DOUBLE>, <INTEGER>)'. Supported form(s): > 'MOD(<EXACT_NUMERIC>, <EXACT_NUMERIC>)' at > sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) > {noformat} > In MySQL: > https://dev.mysql.com/doc/refman/5.7/en/mathematical-functions.html#function_mod > Modulo operation. Returns the remainder of N divided by M. We can get the > correct value in MOD(34.5,3), MOD(19,6.7) situation. > MOD(34.5,3), mod(19,6.7) -> 1.5 , 5.6. > So, In this JIRA. we should do two thing: > 1. Decide whether we are consistent with Mysql. > 2. Improve the document, clearly inform the user for the MOD behavior. > Reference: > * > https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-type-conversion-database-engine > * CALCITE-1296 > * CALCITE-613 > Hi, [~julianhyde] What do you think? Maybe this is related to implicit > conversions. I am not sure. I appreciated if you can tell me your thoughts. > :) -- This message was sent by Atlassian JIRA (v6.4.14#64029)