[
https://issues.apache.org/jira/browse/FLINK-21125?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17272283#comment-17272283
]
Sebastian Liu commented on FLINK-21125:
---------------------------------------
Hi [~jark],
#1. What value will return if {{strict_sql_mode_enabled=false}} and result is
overflowing?
I suggest we return the MAX_VALUE of the current type, which is a meaningful
value relative to overflowed value. And this behavior is also similar to
MySQL's no restrictive mode.
#2. For the table config, we do need to be regulated. I propose to be a bool
type of "table.exec.strict-mode.enabled".
#3. Database systems have more standard sql semantic than OLAP-type systems in
comparison.
* Presto: No option related for the overflow control. All numeric types are
automatically cast to long type in SumAggFunction quietly, and if the long is
out of range, an exception is thrown to prompt user.
* Spark SQL: Its behavior is more like what flink is doing at present. FYI:
[https://spark.apache.org/docs/3.0.0-preview/sql-ref-arithmetic-ops.html]
* Hive 3.0: Also like what flink is doing at present. Hive silently overflows
integers. This comes from java, which does the same. But the workaround is to
use big decimal. As the [doc
says|https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Types#LanguageManualTypes-DecimalLiteralsDecimalLiterals]:
Integral literals larger than BIGINT must be handled with Decimal(38,0). The
Postfix BD is required.
* Snowflake: Its behavior is more like Presto. Numeric values are summed into
an equivalent or larger data type, and seems no related configuration.
[https://docs.snowflake.com/en/sql-reference/functions/sum.html#usage-notes]
For the SQL standard semantic, it should like the strict SQL mode in mysql.
> Sum or Sum0 overflow quietly
> ----------------------------
>
> Key: FLINK-21125
> URL: https://issues.apache.org/jira/browse/FLINK-21125
> Project: Flink
> Issue Type: Bug
> Components: Table SQL / Planner
> Reporter: Sebastian Liu
> Priority: Major
>
> Overflow is not calculated correctly in the build-in sum function of Blink
> planner.
> For a aggregate calculation such as:
> {code:java}
> CREATE TABLE TestTable (
> amount INT
> );
> insert into TestTable (2147483647);
> insert into TestTable (1);
> SELECT sum(amount) FROM TestTable;
> The result will be: -2147483648, which is an overflowed value and no
> exception was thrown. {code}
> The overflow occurs quietly and is difficult to detect.
> Compare the processing semantics of other systems：
> * *mysql*: provide two SQL mode for handling overflow. If strict SQL mode is
> enabled, MySQL rejects the out-of-range value with an error, and the insert
> fails, in accordance with the SQL standard. If no restrictive modes are
> enabled, MySQL clips the value to the appropriate endpoint of the column data
> type range and stores the resulting value instead. FYI:
> [https://dev.mysql.com/doc/refman/8.0/en/out-of-range-and-overflow.html]
> * *presto*: all numeric types are automatically cast to Long type, and If
> the long is out of range, an exception is thrown to prompt user.
> IMO, exception hint is necessary, instead of quietly overflow.
>
>
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
