[jira] [Comment Edited] (CALCITE-3435) floor(mod(33.5,7)) wrongly returns 5.5
[
https://issues.apache.org/jira/browse/CALCITE-3435?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16968971#comment-16968971
]
Feng Zhu edited comment on CALCITE-3435 at 11/7/19 6:45 AM:
After investigate some materials, it seems that many products follow the rule
in MS-SQL[1][2][3][4].
{code:java}
e1[s1, p1] % e2[s2, p2]
s = max(s1, s2)
p = min(p1-s1, p2-s2) + max(s1, s2){code}
Therefore, I open a PR to implement the logic. Could you take a look when you
have time?
[1] MS-SQL: [https://msdn.microsoft.com/en-us/library/ms190476.aspx]
[2] Apache Hive:
[https://cwiki.apache.org/confluence/download/attachments/27362075/Hive_Decimal_Precision_Scale_Support.pdf]
[3] Apache Spark:
[https://github.com/apache/spark/blob/master/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/DecimalPrecision.scala]
[4] Impala:
[https://impala.apache.org/docs/build/html/topics/impala_decimal.html]
was (Author: donnyzone):
After investigate some materials, it seems that many products follow the rule
in MS-SQL^[1][2][3][4]^.
{code:java}
e1[s1, p1] % e2[s2, p2]
s = max(s1, s2)
p = min(p1-s1, p2-s2) + max(s1, s2){code}
Therefore, I open a PR to implement the logic. Could you take a look when you
have time?
[1] MS-SQL: https://msdn.microsoft.com/en-us/library/ms190476.aspx
[2] Apache Hive:
https://cwiki.apache.org/confluence/download/attachments/27362075/Hive_Decimal_Precision_Scale_Support.pdf
[3] Apache Spark:
https://github.com/apache/spark/blob/master/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/DecimalPrecision.scala
[4] Impala: https://impala.apache.org/docs/build/html/topics/impala_decimal.html
> floor(mod(33.5,7)) wrongly returns 5.5
> --
>
> Key: CALCITE-3435
> URL: https://issues.apache.org/jira/browse/CALCITE-3435
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.16.0, 1.21.0
>Reporter: jiezouSH
>Assignee: Feng Zhu
>Priority: Minor
> Labels: pull-request-available
> Time Spent: 10m
> Remaining Estimate: 0h
>
> mod's return type is
> chain(DECIMAL_MOD_NULLABLE, ARG1_NULLABLE),
> but mod(33.5,7)'s result is 5.5, not in line with ARG1_NULLABLE.
> This causes floor(mod(33.5,7)) wrongly returns 5.5
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Comment Edited] (CALCITE-3435) floor(mod(33.5,7)) wrongly returns 5.5
[
https://issues.apache.org/jira/browse/CALCITE-3435?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16962925#comment-16962925
]
Danny Chen edited comment on CALCITE-3435 at 10/30/19 10:58 AM:
I have tested MySQL 5.6, PostgreSQL 9.6 and MS-SQL 2017, for query:
{code:sql}
select mod(12.34, 3) from t; -- select 12.34 % 3 from t; for MS-SQL
{code}
They all returns 0.34, i'm inclined to keep sync with them.
was (Author: danny0405):
I have tested MySQL 5.6, PostgreSQL 9.6 and MS-SQL 2017, for query:
{code:sql}
select mod(12.34, 3) from t; -- select 12.34 % 3 from t; for MY-SQL
{code}
They all returns 0.34, i'm inclined to keep sync with them.
> floor(mod(33.5,7)) wrongly returns 5.5
> --
>
> Key: CALCITE-3435
> URL: https://issues.apache.org/jira/browse/CALCITE-3435
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.16.0, 1.21.0
>Reporter: jiezouSH
>Assignee: Feng Zhu
>Priority: Minor
>
> mod's return type is
> chain(DECIMAL_MOD_NULLABLE, ARG1_NULLABLE),
> but mod(33.5,7)'s result is 5.5, not in line with ARG1_NULLABLE.
> This causes floor(mod(33.5,7)) wrongly returns 5.5
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Comment Edited] (CALCITE-3435) floor(mod(33.5,7)) wrongly returns 5.5
[
https://issues.apache.org/jira/browse/CALCITE-3435?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16962789#comment-16962789
]
Danny Chen edited comment on CALCITE-3435 at 10/30/19 7:51 AM:
---
Here is what i found in the standard SQL 2011 6.28
The "mod" syntax:
{code:sql}
::=
MOD
{code}
Here is the type inference rules:
bq. If is specified, then the declared type of each
shall be exact numeric with scale 0 (zero). The
declared type of the result is the declared type of the immediately contained
.
That means, the standard does not allow a Numetic with non-zero scale.
was (Author: danny0405):
Here is what i found in the standard SQL 2011 6.28
The "mod" syntax:
{code:sql}
::=
MOD
{code}
Here is the type inference rules:
bq. If is specified, then the declared type of each
shall be exact numeric with scale 0 (zero). The
declared type of the result is the declared type of the immediately contained
.
> floor(mod(33.5,7)) wrongly returns 5.5
> --
>
> Key: CALCITE-3435
> URL: https://issues.apache.org/jira/browse/CALCITE-3435
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.16.0, 1.21.0
>Reporter: jiezouSH
>Assignee: Feng Zhu
>Priority: Minor
>
> mod's return type is
> chain(DECIMAL_MOD_NULLABLE, ARG1_NULLABLE),
> but mod(33.5,7)'s result is 5.5, not in line with ARG1_NULLABLE.
> This causes floor(mod(33.5,7)) wrongly returns 5.5
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Comment Edited] (CALCITE-3435) floor(mod(33.5,7)) wrongly returns 5.5
[
https://issues.apache.org/jira/browse/CALCITE-3435?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16962789#comment-16962789
]
Danny Chen edited comment on CALCITE-3435 at 10/30/19 7:51 AM:
---
Here is what i found in the standard SQL 2011 6.28
The "mod" syntax:
{code:sql}
::=
MOD
{code}
Here is the type inference rules:
bq. If is specified, then the declared type of each
shall be exact numeric with scale 0 (zero). The
declared type of the result is the declared type of the immediately contained
.
That means, the standard does not allow a Numeric with non-zero scale.
was (Author: danny0405):
Here is what i found in the standard SQL 2011 6.28
The "mod" syntax:
{code:sql}
::=
MOD
{code}
Here is the type inference rules:
bq. If is specified, then the declared type of each
shall be exact numeric with scale 0 (zero). The
declared type of the result is the declared type of the immediately contained
.
That means, the standard does not allow a Numetic with non-zero scale.
> floor(mod(33.5,7)) wrongly returns 5.5
> --
>
> Key: CALCITE-3435
> URL: https://issues.apache.org/jira/browse/CALCITE-3435
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.16.0, 1.21.0
>Reporter: jiezouSH
>Assignee: Feng Zhu
>Priority: Minor
>
> mod's return type is
> chain(DECIMAL_MOD_NULLABLE, ARG1_NULLABLE),
> but mod(33.5,7)'s result is 5.5, not in line with ARG1_NULLABLE.
> This causes floor(mod(33.5,7)) wrongly returns 5.5
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Comment Edited] (CALCITE-3435) floor(mod(33.5,7)) wrongly returns 5.5
[
https://issues.apache.org/jira/browse/CALCITE-3435?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16962789#comment-16962789
]
Danny Chen edited comment on CALCITE-3435 at 10/30/19 7:50 AM:
---
Here is what i found in the standard SQL 2011 6.28
The "mod" syntax:
{code:sql}
::=
MOD
{code}
Here is the type inference rules:
bq. If is specified, then the declared type of each
shall be exact numeric with scale 0 (zero). The
declared type of the result is the declared type of the immediately contained
.
was (Author: danny0405):
Here is what i found in the standard SQL 2011 6.28
The "mod" syntax:
{code:sql}
::=
MOD
{code}
Here is the type inference rules:
bq. If is specified, then the declared type of each
shall be
bq. exact numeric with scale 0 (zero). The declared type of the result is the
declared type of the immediately
bq. contained .
> floor(mod(33.5,7)) wrongly returns 5.5
> --
>
> Key: CALCITE-3435
> URL: https://issues.apache.org/jira/browse/CALCITE-3435
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.16.0, 1.21.0
>Reporter: jiezouSH
>Assignee: Feng Zhu
>Priority: Minor
>
> mod's return type is
> chain(DECIMAL_MOD_NULLABLE, ARG1_NULLABLE),
> but mod(33.5,7)'s result is 5.5, not in line with ARG1_NULLABLE.
> This causes floor(mod(33.5,7)) wrongly returns 5.5
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Comment Edited] (CALCITE-3435) floor(mod(33.5,7)) wrongly returns 5.5
[
https://issues.apache.org/jira/browse/CALCITE-3435?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16962789#comment-16962789
]
Danny Chen edited comment on CALCITE-3435 at 10/30/19 7:50 AM:
---
Here is what i found in the standard SQL 2011 6.28
The "mod" syntax:
{code:sql}
::=
MOD
{code}
Here is the type inference rules:
bq. If is specified, then the declared type of each
shall be
bq. exact numeric with scale 0 (zero). The declared type of the result is the
declared type of the immediately
bq. contained .
was (Author: danny0405):
Here is what i found in the standard SQL 2011 6.28
The "mod" syntax:
{code:sql}
::=
MOD
{code}
Here is the type inference rules:
bq. If is specified, then the declared type of each
shall be
exact numeric with scale 0 (zero). The declared type of the result is the
declared type of the immediately
contained .
> floor(mod(33.5,7)) wrongly returns 5.5
> --
>
> Key: CALCITE-3435
> URL: https://issues.apache.org/jira/browse/CALCITE-3435
> Project: Calcite
> Issue Type: Bug
>Affects Versions: 1.16.0, 1.21.0
>Reporter: jiezouSH
>Assignee: Feng Zhu
>Priority: Minor
>
> mod's return type is
> chain(DECIMAL_MOD_NULLABLE, ARG1_NULLABLE),
> but mod(33.5,7)'s result is 5.5, not in line with ARG1_NULLABLE.
> This causes floor(mod(33.5,7)) wrongly returns 5.5
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
