[jira] [Comment Edited] (CALCITE-1908) Mod function got exception in MOD(34.5,3), MOD(19,6.7) situation.

["sunjincheng (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-1908?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16112139#comment-16112139 ] 

sunjincheng edited comment on CALCITE-1908 at 8/3/17 3:57 AM:
--------------------------------------------------------------

Hi, [~julianhyde] Thanks for tell me more detail.
I think we can not consider decimal at the first. For the example above, we can look {{11.6}} as `DOUBLE`, {{2}} as {{INTGRE}}, then we can implicitly convert {{2}} as {{DOUBLE}}. i.e., we can consider the following implicitly conversions:

* tinyint --> smallint-> int --> bigint --> float --> double - string

Is this implicitly conversion logic make sense for you? 

Thanks, Jincheng


was (Author: sunjincheng121):
Hi, [~julianhyde] Thanks for tell me more detail.
I think we can not consider decimal at the first. For the example above, we can look {{11.6}} as `DOUBLE`, {{2}} as {{INTGRE"), then we can implicitly convert {2} as {DOUBLE}. i.e., we can consider the following implicitly conversions:

* tinyint --> smallint-> int --> bigint --> float --> double - string

Is this implicitly conversion logic make sense for you? 

Thanks, Jincheng

> 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:
> 		// 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:
> 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：
> https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-type-conversion-database-engine
> https://issues.apache.org/jira/browse/CALCITE-1296
> https://issues.apache.org/jira/browse/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)