Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-1908

Mod function got exception in MOD(34.5,3), MOD(19,6.7) situation.

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None

      Description

      Calcite MOD definition:

      		// 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);
      

      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

       
      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)
      

      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:

      Hi, Julian Hyde What do you think? Maybe this is related to implicit conversions. I am not sure. I appreciated if you can tell me your thoughts.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                sunjincheng121 sunjincheng
                Reporter:
                sunjincheng121 sunjincheng
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated: