[jira] [Commented] (SPARK-27768) Infinity, -Infinity, NaN should be recognized in a case insensitive manner
[
https://issues.apache.org/jira/browse/SPARK-27768?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16878185#comment-16878185
]
Dilip Biswal commented on SPARK-27768:
--
[~dongjoon] Thank you very much for your response.
> Infinity, -Infinity, NaN should be recognized in a case insensitive manner
> --
>
> Key: SPARK-27768
> URL: https://issues.apache.org/jira/browse/SPARK-27768
> Project: Spark
> Issue Type: Sub-task
> Components: SQL
>Affects Versions: 3.0.0
>Reporter: Xiao Li
>Priority: Major
>
> When the inputs contain the constant 'infinity', Spark SQL does not generate
> the expected results.
> {code:java}
> SELECT avg(CAST(x AS DOUBLE)), var_pop(CAST(x AS DOUBLE))
> FROM (VALUES ('1'), (CAST('infinity' AS DOUBLE))) v(x);
> SELECT avg(CAST(x AS DOUBLE)), var_pop(CAST(x AS DOUBLE))
> FROM (VALUES ('infinity'), ('1')) v(x);
> SELECT avg(CAST(x AS DOUBLE)), var_pop(CAST(x AS DOUBLE))
> FROM (VALUES ('infinity'), ('infinity')) v(x);
> SELECT avg(CAST(x AS DOUBLE)), var_pop(CAST(x AS DOUBLE))
> FROM (VALUES ('-infinity'), ('infinity')) v(x);{code}
> The root cause: Spark SQL does not recognize the special constants in a case
> insensitive way. In PostgreSQL, they are recognized in a case insensitive
> way.
> Link: https://www.postgresql.org/docs/9.3/datatype-numeric.html
>
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-27768) Infinity, -Infinity, NaN should be recognized in a case insensitive manner
[
https://issues.apache.org/jira/browse/SPARK-27768?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16861711#comment-16861711
]
Dongjoon Hyun commented on SPARK-27768:
---
For this issue, I changed my mind to support this approach because I agreed the
big picture of [~smilegator]. Sorry for the delay on this issue.
> Infinity, -Infinity, NaN should be recognized in a case insensitive manner
> --
>
> Key: SPARK-27768
> URL: https://issues.apache.org/jira/browse/SPARK-27768
> Project: Spark
> Issue Type: Sub-task
> Components: SQL
>Affects Versions: 3.0.0
>Reporter: Xiao Li
>Priority: Major
>
> When the inputs contain the constant 'infinity', Spark SQL does not generate
> the expected results.
> {code:java}
> SELECT avg(CAST(x AS DOUBLE)), var_pop(CAST(x AS DOUBLE))
> FROM (VALUES ('1'), (CAST('infinity' AS DOUBLE))) v(x);
> SELECT avg(CAST(x AS DOUBLE)), var_pop(CAST(x AS DOUBLE))
> FROM (VALUES ('infinity'), ('1')) v(x);
> SELECT avg(CAST(x AS DOUBLE)), var_pop(CAST(x AS DOUBLE))
> FROM (VALUES ('infinity'), ('infinity')) v(x);
> SELECT avg(CAST(x AS DOUBLE)), var_pop(CAST(x AS DOUBLE))
> FROM (VALUES ('-infinity'), ('infinity')) v(x);{code}
> The root cause: Spark SQL does not recognize the special constants in a case
> insensitive way. In PostgreSQL, they are recognized in a case insensitive
> way.
> Link: https://www.postgresql.org/docs/9.3/datatype-numeric.html
>
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-27768) Infinity, -Infinity, NaN should be recognized in a case insensitive manner
[
https://issues.apache.org/jira/browse/SPARK-27768?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16849078#comment-16849078
]
Dilip Biswal commented on SPARK-27768:
--
[~dongjoon]
Thanks for trying out Presto.
Just want to share my 2 cents before we take a final call on it. I am okay with
whatever you guys decide :).
There seems to be a subtle difference between Presto and Spark ? Spark returns
"NULL" in this case where as presto returns an error ? Because of this i think
we should be more accommodative of data that is accepted in other systems. I am
afraid, because of "authoring null" semantics, sometimes during the etl process
we will treat some valid input from other systems as nulls and its probably
hard for users to locate the bad record and fix..
Lets say for a second that we decide to accept this case. So technically, we
will not be portable with Hive and Presto. But we are allowing something more
that these two systems, right ? Do we think that some users would actually want
the strings such as "infinity" to be treated as null and would be negatively
surprised to see the new behaviour ?
Let me know what you think..
> Infinity, -Infinity, NaN should be recognized in a case insensitive manner
> --
>
> Key: SPARK-27768
> URL: https://issues.apache.org/jira/browse/SPARK-27768
> Project: Spark
> Issue Type: Sub-task
> Components: SQL
>Affects Versions: 3.0.0
>Reporter: Xiao Li
>Priority: Major
>
> When the inputs contain the constant 'infinity', Spark SQL does not generate
> the expected results.
> {code:java}
> SELECT avg(CAST(x AS DOUBLE)), var_pop(CAST(x AS DOUBLE))
> FROM (VALUES ('1'), (CAST('infinity' AS DOUBLE))) v(x);
> SELECT avg(CAST(x AS DOUBLE)), var_pop(CAST(x AS DOUBLE))
> FROM (VALUES ('infinity'), ('1')) v(x);
> SELECT avg(CAST(x AS DOUBLE)), var_pop(CAST(x AS DOUBLE))
> FROM (VALUES ('infinity'), ('infinity')) v(x);
> SELECT avg(CAST(x AS DOUBLE)), var_pop(CAST(x AS DOUBLE))
> FROM (VALUES ('-infinity'), ('infinity')) v(x);{code}
> The root cause: Spark SQL does not recognize the special constants in a case
> insensitive way. In PostgreSQL, they are recognized in a case insensitive
> way.
> Link: https://www.postgresql.org/docs/9.3/datatype-numeric.html
>
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-27768) Infinity, -Infinity, NaN should be recognized in a case insensitive manner
[
https://issues.apache.org/jira/browse/SPARK-27768?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16848510#comment-16848510
]
Dongjoon Hyun commented on SPARK-27768:
---
In addition to [~dkbiswal]'s investigation, I want to add `Presto` result to be
complete. Hive/Spark/Presto is in the same behavior. Personally, I don't think
DB2 and PostgreSQL is currently used in Big Data area.
{code}
presto> select CAST('Infinity' AS DOUBLE);
_col0
--
Infinity
(1 row)
presto> select CAST('infinity' AS DOUBLE);
Query 20190526_192453_2_t4z79 failed: Cannot cast 'infinity' to DOUBLE
{code}
> Infinity, -Infinity, NaN should be recognized in a case insensitive manner
> --
>
> Key: SPARK-27768
> URL: https://issues.apache.org/jira/browse/SPARK-27768
> Project: Spark
> Issue Type: Sub-task
> Components: SQL
>Affects Versions: 3.0.0
>Reporter: Xiao Li
>Priority: Major
>
> When the inputs contain the constant 'infinity', Spark SQL does not generate
> the expected results.
> {code:java}
> SELECT avg(CAST(x AS DOUBLE)), var_pop(CAST(x AS DOUBLE))
> FROM (VALUES ('1'), (CAST('infinity' AS DOUBLE))) v(x);
> SELECT avg(CAST(x AS DOUBLE)), var_pop(CAST(x AS DOUBLE))
> FROM (VALUES ('infinity'), ('1')) v(x);
> SELECT avg(CAST(x AS DOUBLE)), var_pop(CAST(x AS DOUBLE))
> FROM (VALUES ('infinity'), ('infinity')) v(x);
> SELECT avg(CAST(x AS DOUBLE)), var_pop(CAST(x AS DOUBLE))
> FROM (VALUES ('-infinity'), ('infinity')) v(x);{code}
> The root cause: Spark SQL does not recognize the special constants in a case
> insensitive way. In PostgreSQL, they are recognized in a case insensitive
> way.
> Link: https://www.postgresql.org/docs/9.3/datatype-numeric.html
>
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-27768) Infinity, -Infinity, NaN should be recognized in a case insensitive manner
[
https://issues.apache.org/jira/browse/SPARK-27768?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16845277#comment-16845277
]
Dilip Biswal commented on SPARK-27768:
--
[~smilegator] I will wait for the test PR to get in first and then i will open
the pr the infinity issue.
> Infinity, -Infinity, NaN should be recognized in a case insensitive manner
> --
>
> Key: SPARK-27768
> URL: https://issues.apache.org/jira/browse/SPARK-27768
> Project: Spark
> Issue Type: Sub-task
> Components: SQL
>Affects Versions: 3.0.0
>Reporter: Xiao Li
>Priority: Major
>
> When the inputs contain the constant 'infinity', Spark SQL does not generate
> the expected results.
> {code:java}
> SELECT avg(CAST(x AS DOUBLE)), var_pop(CAST(x AS DOUBLE))
> FROM (VALUES ('1'), (CAST('infinity' AS DOUBLE))) v(x);
> SELECT avg(CAST(x AS DOUBLE)), var_pop(CAST(x AS DOUBLE))
> FROM (VALUES ('infinity'), ('1')) v(x);
> SELECT avg(CAST(x AS DOUBLE)), var_pop(CAST(x AS DOUBLE))
> FROM (VALUES ('infinity'), ('infinity')) v(x);
> SELECT avg(CAST(x AS DOUBLE)), var_pop(CAST(x AS DOUBLE))
> FROM (VALUES ('-infinity'), ('infinity')) v(x);{code}
> The root cause: Spark SQL does not recognize the special constants in a case
> insensitive way. In PostgreSQL, they are recognized in a case insensitive
> way.
> Link: https://www.postgresql.org/docs/9.3/datatype-numeric.html
>
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-27768) Infinity, -Infinity, NaN should be recognized in a case insensitive manner
[
https://issues.apache.org/jira/browse/SPARK-27768?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16844473#comment-16844473
]
Dilip Biswal commented on SPARK-27768:
--
OK [~smilegator]
> Infinity, -Infinity, NaN should be recognized in a case insensitive manner
> --
>
> Key: SPARK-27768
> URL: https://issues.apache.org/jira/browse/SPARK-27768
> Project: Spark
> Issue Type: Sub-task
> Components: SQL
>Affects Versions: 3.0.0
>Reporter: Xiao Li
>Priority: Major
>
> When the inputs contain the constant 'infinity', Spark SQL does not generate
> the expected results.
> {code:java}
> SELECT avg(CAST(x AS DOUBLE)), var_pop(CAST(x AS DOUBLE))
> FROM (VALUES ('1'), (CAST('infinity' AS DOUBLE))) v(x);
> SELECT avg(CAST(x AS DOUBLE)), var_pop(CAST(x AS DOUBLE))
> FROM (VALUES ('infinity'), ('1')) v(x);
> SELECT avg(CAST(x AS DOUBLE)), var_pop(CAST(x AS DOUBLE))
> FROM (VALUES ('infinity'), ('infinity')) v(x);
> SELECT avg(CAST(x AS DOUBLE)), var_pop(CAST(x AS DOUBLE))
> FROM (VALUES ('-infinity'), ('infinity')) v(x);{code}
> The root cause: Spark SQL does not recognize the special constants in a case
> insensitive way. In PostgreSQL, they are recognized in a case insensitive
> way.
> Link: https://www.postgresql.org/docs/9.3/datatype-numeric.html
>
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-27768) Infinity, -Infinity, NaN should be recognized in a case insensitive manner
[
https://issues.apache.org/jira/browse/SPARK-27768?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16844374#comment-16844374
]
Xiao Li commented on SPARK-27768:
-
Both DB2 and PostgreSQL treat 'infinity' like special values case insensitive.
In the past, Hive compatibility is important to us. Now, when more and more
traditional database users start migrating the workloads to Spark, we should
also improve the compatibility and simplify the migration.
This is especially true for the type coercion rules. The type coercion rules in
Hive have various issues based on the JIRAs reported by our Spark users. I
think we need to consider and introduce a new set of type coercion rules by
following the true ANSI-compliant SQL systems like postgreSQL.
> Infinity, -Infinity, NaN should be recognized in a case insensitive manner
> --
>
> Key: SPARK-27768
> URL: https://issues.apache.org/jira/browse/SPARK-27768
> Project: Spark
> Issue Type: Sub-task
> Components: SQL
>Affects Versions: 3.0.0
>Reporter: Xiao Li
>Priority: Major
>
> When the inputs contain the constant 'infinity', Spark SQL does not generate
> the expected results.
> {code:java}
> SELECT avg(CAST(x AS DOUBLE)), var_pop(CAST(x AS DOUBLE))
> FROM (VALUES ('1'), (CAST('infinity' AS DOUBLE))) v(x);
> SELECT avg(CAST(x AS DOUBLE)), var_pop(CAST(x AS DOUBLE))
> FROM (VALUES ('infinity'), ('1')) v(x);
> SELECT avg(CAST(x AS DOUBLE)), var_pop(CAST(x AS DOUBLE))
> FROM (VALUES ('infinity'), ('infinity')) v(x);
> SELECT avg(CAST(x AS DOUBLE)), var_pop(CAST(x AS DOUBLE))
> FROM (VALUES ('-infinity'), ('infinity')) v(x);{code}
> The root cause: Spark SQL does not recognize the special constants in a case
> insensitive way. In PostgreSQL, they are recognized in a case insensitive
> way.
> Link: https://www.postgresql.org/docs/9.3/datatype-numeric.html
>
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-27768) Infinity, -Infinity, NaN should be recognized in a case insensitive manner
[
https://issues.apache.org/jira/browse/SPARK-27768?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16844375#comment-16844375
]
Xiao Li commented on SPARK-27768:
-
We need to document the difference in the migration guide
> Infinity, -Infinity, NaN should be recognized in a case insensitive manner
> --
>
> Key: SPARK-27768
> URL: https://issues.apache.org/jira/browse/SPARK-27768
> Project: Spark
> Issue Type: Sub-task
> Components: SQL
>Affects Versions: 3.0.0
>Reporter: Xiao Li
>Priority: Major
>
> When the inputs contain the constant 'infinity', Spark SQL does not generate
> the expected results.
> {code:java}
> SELECT avg(CAST(x AS DOUBLE)), var_pop(CAST(x AS DOUBLE))
> FROM (VALUES ('1'), (CAST('infinity' AS DOUBLE))) v(x);
> SELECT avg(CAST(x AS DOUBLE)), var_pop(CAST(x AS DOUBLE))
> FROM (VALUES ('infinity'), ('1')) v(x);
> SELECT avg(CAST(x AS DOUBLE)), var_pop(CAST(x AS DOUBLE))
> FROM (VALUES ('infinity'), ('infinity')) v(x);
> SELECT avg(CAST(x AS DOUBLE)), var_pop(CAST(x AS DOUBLE))
> FROM (VALUES ('-infinity'), ('infinity')) v(x);{code}
> The root cause: Spark SQL does not recognize the special constants in a case
> insensitive way. In PostgreSQL, they are recognized in a case insensitive
> way.
> Link: https://www.postgresql.org/docs/9.3/datatype-numeric.html
>
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-27768) Infinity, -Infinity, NaN should be recognized in a case insensitive manner
[
https://issues.apache.org/jira/browse/SPARK-27768?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16844372#comment-16844372
]
Dilip Biswal commented on SPARK-27768:
--
Hi [~smilegator] and [~dongjoon],
Just checked the DB2 behaviour. FYI.
db2 => select cast('infinity' as decfloat) from foo;
1
--
Infinity
1 record(s) selected.
db2 => select cast('Infinity' as decfloat) from foo;
1
--
Infinity
1 record(s) selected.
db2 => select cast('iNfinity' as decfloat) from foo;
1
--
Infinity
1 record(s) selected.
Regards,
-- Dilip
> Infinity, -Infinity, NaN should be recognized in a case insensitive manner
> --
>
> Key: SPARK-27768
> URL: https://issues.apache.org/jira/browse/SPARK-27768
> Project: Spark
> Issue Type: Sub-task
> Components: SQL
>Affects Versions: 3.0.0
>Reporter: Xiao Li
>Priority: Major
>
> When the inputs contain the constant 'infinity', Spark SQL does not generate
> the expected results.
> {code:java}
> SELECT avg(CAST(x AS DOUBLE)), var_pop(CAST(x AS DOUBLE))
> FROM (VALUES ('1'), (CAST('infinity' AS DOUBLE))) v(x);
> SELECT avg(CAST(x AS DOUBLE)), var_pop(CAST(x AS DOUBLE))
> FROM (VALUES ('infinity'), ('1')) v(x);
> SELECT avg(CAST(x AS DOUBLE)), var_pop(CAST(x AS DOUBLE))
> FROM (VALUES ('infinity'), ('infinity')) v(x);
> SELECT avg(CAST(x AS DOUBLE)), var_pop(CAST(x AS DOUBLE))
> FROM (VALUES ('-infinity'), ('infinity')) v(x);{code}
> The root cause: Spark SQL does not recognize the special constants in a case
> insensitive way. In PostgreSQL, they are recognized in a case insensitive
> way.
> Link: https://www.postgresql.org/docs/9.3/datatype-numeric.html
>
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-27768) Infinity, -Infinity, NaN should be recognized in a case insensitive manner
[
https://issues.apache.org/jira/browse/SPARK-27768?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16844351#comment-16844351
]
Dilip Biswal commented on SPARK-27768:
--
Hi [~smilegator] [~dongjoon],
Had started to look at this. A few observations :
1) Apart from handling of cast string to double/float, there seem to be another
discrepancy with type promotion.
In the first test case, while handling the inline table, we can't find a
common type between string and double.
Not sure if we want to do anything about it ?
2) Seems like, we need to modify the casting code to handle this i.e handle
this at the runtime.
Made a trial at
[here|https://github.com/dilipbiswal/spark/pull/new/double_infinity]
(Note: I have tweaked the first test to use int literal as opposed to
string literal to bypass (1)
Not sure what the direction we would like to take.. Just wanted to share my
findings.
Regards,
Dilip
> Infinity, -Infinity, NaN should be recognized in a case insensitive manner
> --
>
> Key: SPARK-27768
> URL: https://issues.apache.org/jira/browse/SPARK-27768
> Project: Spark
> Issue Type: Sub-task
> Components: SQL
>Affects Versions: 3.0.0
>Reporter: Xiao Li
>Priority: Major
>
> When the inputs contain the constant 'infinity', Spark SQL does not generate
> the expected results.
> {code:java}
> SELECT avg(CAST(x AS DOUBLE)), var_pop(CAST(x AS DOUBLE))
> FROM (VALUES ('1'), (CAST('infinity' AS DOUBLE))) v(x);
> SELECT avg(CAST(x AS DOUBLE)), var_pop(CAST(x AS DOUBLE))
> FROM (VALUES ('infinity'), ('1')) v(x);
> SELECT avg(CAST(x AS DOUBLE)), var_pop(CAST(x AS DOUBLE))
> FROM (VALUES ('infinity'), ('infinity')) v(x);
> SELECT avg(CAST(x AS DOUBLE)), var_pop(CAST(x AS DOUBLE))
> FROM (VALUES ('-infinity'), ('infinity')) v(x);{code}
> The root cause: Spark SQL does not recognize the special constants in a case
> insensitive way. In PostgreSQL, they are recognized in a case insensitive
> way.
> Link: https://www.postgresql.org/docs/9.3/datatype-numeric.html
>
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-27768) Infinity, -Infinity, NaN should be recognized in a case insensitive manner
[
https://issues.apache.org/jira/browse/SPARK-27768?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16843597#comment-16843597
]
Dongjoon Hyun commented on SPARK-27768:
---
Spark 3.0.0 had better be consistent with the exiting Spark behavior for the
compatibility with the old Spark releases and Hive releases.
> Infinity, -Infinity, NaN should be recognized in a case insensitive manner
> --
>
> Key: SPARK-27768
> URL: https://issues.apache.org/jira/browse/SPARK-27768
> Project: Spark
> Issue Type: Sub-task
> Components: SQL
>Affects Versions: 3.0.0
>Reporter: Xiao Li
>Priority: Major
>
> When the inputs contain the constant 'infinity', Spark SQL does not generate
> the expected results.
> {code:java}
> SELECT avg(CAST(x AS DOUBLE)), var_pop(CAST(x AS DOUBLE))
> FROM (VALUES ('1'), (CAST('infinity' AS DOUBLE))) v(x);
> SELECT avg(CAST(x AS DOUBLE)), var_pop(CAST(x AS DOUBLE))
> FROM (VALUES ('infinity'), ('1')) v(x);
> SELECT avg(CAST(x AS DOUBLE)), var_pop(CAST(x AS DOUBLE))
> FROM (VALUES ('infinity'), ('infinity')) v(x);
> SELECT avg(CAST(x AS DOUBLE)), var_pop(CAST(x AS DOUBLE))
> FROM (VALUES ('-infinity'), ('infinity')) v(x);{code}
> The root cause: Spark SQL does not recognize the special constants in a case
> insensitive way. In PostgreSQL, they are recognized in a case insensitive
> way.
> Link: https://www.postgresql.org/docs/9.3/datatype-numeric.html
>
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-27768) Infinity, -Infinity, NaN should be recognized in a case insensitive manner
[
https://issues.apache.org/jira/browse/SPARK-27768?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16843595#comment-16843595
]
Dongjoon Hyun commented on SPARK-27768:
---
FYI, Spark behavior is compatible with Hive.
{code}
hive> select CAST('infinity' AS DOUBLE), CAST('Infinity' AS DOUBLE);
OK
NULLInfinity
{code}
> Infinity, -Infinity, NaN should be recognized in a case insensitive manner
> --
>
> Key: SPARK-27768
> URL: https://issues.apache.org/jira/browse/SPARK-27768
> Project: Spark
> Issue Type: Sub-task
> Components: SQL
>Affects Versions: 3.0.0
>Reporter: Xiao Li
>Priority: Major
>
> When the inputs contain the constant 'infinity', Spark SQL does not generate
> the expected results.
> {code:java}
> SELECT avg(CAST(x AS DOUBLE)), var_pop(CAST(x AS DOUBLE))
> FROM (VALUES ('1'), (CAST('infinity' AS DOUBLE))) v(x);
> SELECT avg(CAST(x AS DOUBLE)), var_pop(CAST(x AS DOUBLE))
> FROM (VALUES ('infinity'), ('1')) v(x);
> SELECT avg(CAST(x AS DOUBLE)), var_pop(CAST(x AS DOUBLE))
> FROM (VALUES ('infinity'), ('infinity')) v(x);
> SELECT avg(CAST(x AS DOUBLE)), var_pop(CAST(x AS DOUBLE))
> FROM (VALUES ('-infinity'), ('infinity')) v(x);{code}
> The root cause: Spark SQL does not recognize the special constants in a case
> insensitive way. In PostgreSQL, they are recognized in a case insensitive
> way.
> Link: https://www.postgresql.org/docs/9.3/datatype-numeric.html
>
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
