[jira] [Commented] (SPARK-41793) Incorrect result for window frames defined by a range clause on large decimals
[
https://issues.apache.org/jira/browse/SPARK-41793?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17692480#comment-17692480
]
Apache Spark commented on SPARK-41793:
--
User 'ulysses-you' has created a pull request for this issue:
https://github.com/apache/spark/pull/40138
> Incorrect result for window frames defined by a range clause on large
> decimals
> ---
>
> Key: SPARK-41793
> URL: https://issues.apache.org/jira/browse/SPARK-41793
> Project: Spark
> Issue Type: Bug
> Components: SQL
>Affects Versions: 3.4.0
>Reporter: Gera Shegalov
>Priority: Blocker
> Labels: correctness
>
> Context
> https://github.com/NVIDIA/spark-rapids/issues/7429#issuecomment-1368040686
> The following windowing query on a simple two-row input should produce two
> non-empty windows as a result
> {code}
> from pprint import pprint
> data = [
> ('9223372036854775807', '11342371013783243717493546650944543.47'),
> ('9223372036854775807', '.99')
> ]
> df1 = spark.createDataFrame(data, 'a STRING, b STRING')
> df2 = df1.select(df1.a.cast('LONG'), df1.b.cast('DECIMAL(38,2)'))
> df2.createOrReplaceTempView('test_table')
> df = sql('''
> SELECT
> COUNT(1) OVER (
> PARTITION BY a
> ORDER BY b ASC
> RANGE BETWEEN 10.2345 PRECEDING AND 6.7890 FOLLOWING
> ) AS CNT_1
> FROM
> test_table
> ''')
> res = df.collect()
> df.explain(True)
> pprint(res)
> {code}
> Spark 3.4.0-SNAPSHOT output:
> {code}
> [Row(CNT_1=1), Row(CNT_1=0)]
> {code}
> Spark 3.3.1 output as expected:
> {code}
> Row(CNT_1=1), Row(CNT_1=1)]
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-41793) Incorrect result for window frames defined by a range clause on large decimals
[
https://issues.apache.org/jira/browse/SPARK-41793?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17692451#comment-17692451
]
Wenchen Fan commented on SPARK-41793:
-
When we are doing window operator computing per partition, it's local decimal
calculations and we can temporarily go beyond the decimal precision limitation,
because `Decimal` is backed by `java.math.BigDecimal`. We should only check
overflow before writing out decimal values. There is an expression
`DecimalAddNoOverflowCheck` and we should use it in the window operator.
[~ulysses] can you help to fix it? This is the same idea we use in Sum/Average.
> Incorrect result for window frames defined by a range clause on large
> decimals
> ---
>
> Key: SPARK-41793
> URL: https://issues.apache.org/jira/browse/SPARK-41793
> Project: Spark
> Issue Type: Bug
> Components: SQL
>Affects Versions: 3.4.0
>Reporter: Gera Shegalov
>Priority: Blocker
> Labels: correctness
>
> Context
> https://github.com/NVIDIA/spark-rapids/issues/7429#issuecomment-1368040686
> The following windowing query on a simple two-row input should produce two
> non-empty windows as a result
> {code}
> from pprint import pprint
> data = [
> ('9223372036854775807', '11342371013783243717493546650944543.47'),
> ('9223372036854775807', '.99')
> ]
> df1 = spark.createDataFrame(data, 'a STRING, b STRING')
> df2 = df1.select(df1.a.cast('LONG'), df1.b.cast('DECIMAL(38,2)'))
> df2.createOrReplaceTempView('test_table')
> df = sql('''
> SELECT
> COUNT(1) OVER (
> PARTITION BY a
> ORDER BY b ASC
> RANGE BETWEEN 10.2345 PRECEDING AND 6.7890 FOLLOWING
> ) AS CNT_1
> FROM
> test_table
> ''')
> res = df.collect()
> df.explain(True)
> pprint(res)
> {code}
> Spark 3.4.0-SNAPSHOT output:
> {code}
> [Row(CNT_1=1), Row(CNT_1=0)]
> {code}
> Spark 3.3.1 output as expected:
> {code}
> Row(CNT_1=1), Row(CNT_1=1)]
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-41793) Incorrect result for window frames defined by a range clause on large decimals
[
https://issues.apache.org/jira/browse/SPARK-41793?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17692408#comment-17692408
]
Thomas Graves commented on SPARK-41793:
---
[~ulysses] [~cloud_fan] [~xinrong]
We need to decide what we are doing with this for 3.4 before doing any release.
> Incorrect result for window frames defined by a range clause on large
> decimals
> ---
>
> Key: SPARK-41793
> URL: https://issues.apache.org/jira/browse/SPARK-41793
> Project: Spark
> Issue Type: Bug
> Components: SQL
>Affects Versions: 3.4.0
>Reporter: Gera Shegalov
>Priority: Blocker
> Labels: correctness
>
> Context
> https://github.com/NVIDIA/spark-rapids/issues/7429#issuecomment-1368040686
> The following windowing query on a simple two-row input should produce two
> non-empty windows as a result
> {code}
> from pprint import pprint
> data = [
> ('9223372036854775807', '11342371013783243717493546650944543.47'),
> ('9223372036854775807', '.99')
> ]
> df1 = spark.createDataFrame(data, 'a STRING, b STRING')
> df2 = df1.select(df1.a.cast('LONG'), df1.b.cast('DECIMAL(38,2)'))
> df2.createOrReplaceTempView('test_table')
> df = sql('''
> SELECT
> COUNT(1) OVER (
> PARTITION BY a
> ORDER BY b ASC
> RANGE BETWEEN 10.2345 PRECEDING AND 6.7890 FOLLOWING
> ) AS CNT_1
> FROM
> test_table
> ''')
> res = df.collect()
> df.explain(True)
> pprint(res)
> {code}
> Spark 3.4.0-SNAPSHOT output:
> {code}
> [Row(CNT_1=1), Row(CNT_1=0)]
> {code}
> Spark 3.3.1 output as expected:
> {code}
> Row(CNT_1=1), Row(CNT_1=1)]
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-41793) Incorrect result for window frames defined by a range clause on large decimals
[
https://issues.apache.org/jira/browse/SPARK-41793?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17685384#comment-17685384
]
Gera Shegalov commented on SPARK-41793:
---
Another interpretation of why the pre-3.4 count of 1 may be actually correct
could be that regardless of whether the window frame bound values overflow or
not the current row is always part of the window it defines. Whether or not it
should be the case can be clarified in the doc.
> Incorrect result for window frames defined by a range clause on large
> decimals
> ---
>
> Key: SPARK-41793
> URL: https://issues.apache.org/jira/browse/SPARK-41793
> Project: Spark
> Issue Type: Bug
> Components: SQL
>Affects Versions: 3.4.0
>Reporter: Gera Shegalov
>Priority: Blocker
> Labels: correctness
>
> Context
> https://github.com/NVIDIA/spark-rapids/issues/7429#issuecomment-1368040686
> The following windowing query on a simple two-row input should produce two
> non-empty windows as a result
> {code}
> from pprint import pprint
> data = [
> ('9223372036854775807', '11342371013783243717493546650944543.47'),
> ('9223372036854775807', '.99')
> ]
> df1 = spark.createDataFrame(data, 'a STRING, b STRING')
> df2 = df1.select(df1.a.cast('LONG'), df1.b.cast('DECIMAL(38,2)'))
> df2.createOrReplaceTempView('test_table')
> df = sql('''
> SELECT
> COUNT(1) OVER (
> PARTITION BY a
> ORDER BY b ASC
> RANGE BETWEEN 10.2345 PRECEDING AND 6.7890 FOLLOWING
> ) AS CNT_1
> FROM
> test_table
> ''')
> res = df.collect()
> df.explain(True)
> pprint(res)
> {code}
> Spark 3.4.0-SNAPSHOT output:
> {code}
> [Row(CNT_1=1), Row(CNT_1=0)]
> {code}
> Spark 3.3.1 output as expected:
> {code}
> Row(CNT_1=1), Row(CNT_1=1)]
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-41793) Incorrect result for window frames defined by a range clause on large decimals
[
https://issues.apache.org/jira/browse/SPARK-41793?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17684903#comment-17684903
]
Gera Shegalov commented on SPARK-41793:
---
if the consensus is that it's not a correctness bug in 3.4, then this fix
should probably be documented and probably backported to maintenance branches?
> Incorrect result for window frames defined by a range clause on large
> decimals
> ---
>
> Key: SPARK-41793
> URL: https://issues.apache.org/jira/browse/SPARK-41793
> Project: Spark
> Issue Type: Bug
> Components: SQL
>Affects Versions: 3.4.0
>Reporter: Gera Shegalov
>Priority: Blocker
> Labels: correctness
>
> Context
> https://github.com/NVIDIA/spark-rapids/issues/7429#issuecomment-1368040686
> The following windowing query on a simple two-row input should produce two
> non-empty windows as a result
> {code}
> from pprint import pprint
> data = [
> ('9223372036854775807', '11342371013783243717493546650944543.47'),
> ('9223372036854775807', '.99')
> ]
> df1 = spark.createDataFrame(data, 'a STRING, b STRING')
> df2 = df1.select(df1.a.cast('LONG'), df1.b.cast('DECIMAL(38,2)'))
> df2.createOrReplaceTempView('test_table')
> df = sql('''
> SELECT
> COUNT(1) OVER (
> PARTITION BY a
> ORDER BY b ASC
> RANGE BETWEEN 10.2345 PRECEDING AND 6.7890 FOLLOWING
> ) AS CNT_1
> FROM
> test_table
> ''')
> res = df.collect()
> df.explain(True)
> pprint(res)
> {code}
> Spark 3.4.0-SNAPSHOT output:
> {code}
> [Row(CNT_1=1), Row(CNT_1=0)]
> {code}
> Spark 3.3.1 output as expected:
> {code}
> Row(CNT_1=1), Row(CNT_1=1)]
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-41793) Incorrect result for window frames defined by a range clause on large decimals
[
https://issues.apache.org/jira/browse/SPARK-41793?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17683753#comment-17683753
]
XiDuo You commented on SPARK-41793:
---
I'm not sure this is a correctness bug but I think it's more like a right
behavior change.
An example, what happened if run this query ?
{code:java}
select 1 + .99 {code}
Without ANSI, it returns null, so the size of count should return 0.
> Incorrect result for window frames defined by a range clause on large
> decimals
> ---
>
> Key: SPARK-41793
> URL: https://issues.apache.org/jira/browse/SPARK-41793
> Project: Spark
> Issue Type: Bug
> Components: SQL
>Affects Versions: 3.4.0
>Reporter: Gera Shegalov
>Priority: Blocker
> Labels: correctness
>
> Context
> https://github.com/NVIDIA/spark-rapids/issues/7429#issuecomment-1368040686
> The following windowing query on a simple two-row input should produce two
> non-empty windows as a result
> {code}
> from pprint import pprint
> data = [
> ('9223372036854775807', '11342371013783243717493546650944543.47'),
> ('9223372036854775807', '.99')
> ]
> df1 = spark.createDataFrame(data, 'a STRING, b STRING')
> df2 = df1.select(df1.a.cast('LONG'), df1.b.cast('DECIMAL(38,2)'))
> df2.createOrReplaceTempView('test_table')
> df = sql('''
> SELECT
> COUNT(1) OVER (
> PARTITION BY a
> ORDER BY b ASC
> RANGE BETWEEN 10.2345 PRECEDING AND 6.7890 FOLLOWING
> ) AS CNT_1
> FROM
> test_table
> ''')
> res = df.collect()
> df.explain(True)
> pprint(res)
> {code}
> Spark 3.4.0-SNAPSHOT output:
> {code}
> [Row(CNT_1=1), Row(CNT_1=0)]
> {code}
> Spark 3.3.1 output as expected:
> {code}
> Row(CNT_1=1), Row(CNT_1=1)]
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-41793) Incorrect result for window frames defined by a range clause on large decimals
[
https://issues.apache.org/jira/browse/SPARK-41793?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17678744#comment-17678744
]
Thomas Graves commented on SPARK-41793:
---
this sounds like a correctness issue - [~cloud_fan] [~ulyssesyou] am I missing
something here?
> Incorrect result for window frames defined by a range clause on large
> decimals
> ---
>
> Key: SPARK-41793
> URL: https://issues.apache.org/jira/browse/SPARK-41793
> Project: Spark
> Issue Type: Bug
> Components: SQL
>Affects Versions: 3.4.0
>Reporter: Gera Shegalov
>Priority: Major
>
> Context
> https://github.com/NVIDIA/spark-rapids/issues/7429#issuecomment-1368040686
> The following windowing query on a simple two-row input should produce two
> non-empty windows as a result
> {code}
> from pprint import pprint
> data = [
> ('9223372036854775807', '11342371013783243717493546650944543.47'),
> ('9223372036854775807', '.99')
> ]
> df1 = spark.createDataFrame(data, 'a STRING, b STRING')
> df2 = df1.select(df1.a.cast('LONG'), df1.b.cast('DECIMAL(38,2)'))
> df2.createOrReplaceTempView('test_table')
> df = sql('''
> SELECT
> COUNT(1) OVER (
> PARTITION BY a
> ORDER BY b ASC
> RANGE BETWEEN 10.2345 PRECEDING AND 6.7890 FOLLOWING
> ) AS CNT_1
> FROM
> test_table
> ''')
> res = df.collect()
> df.explain(True)
> pprint(res)
> {code}
> Spark 3.4.0-SNAPSHOT output:
> {code}
> [Row(CNT_1=1), Row(CNT_1=0)]
> {code}
> Spark 3.3.1 output as expected:
> {code}
> Row(CNT_1=1), Row(CNT_1=1)]
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-41793) Incorrect result for window frames defined by a range clause on large decimals
[
https://issues.apache.org/jira/browse/SPARK-41793?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17653348#comment-17653348
]
Bruce Robbins commented on SPARK-41793:
---
[~cloud_fan] [~ulysses]
The change in behavior appears to be from commit
[301a139638|https://github.com/apache/spark/commit/301a139638] (SPARK-39316).
When I test with the commit immediately preceding, I get:
{noformat}
1
1
{noformat}
When I test on that commit, I get
{noformat}
1
0
{noformat}
I used this to test in spark-sql:
{noformat}
create or replace temp view test_table
as select * from values
(9223372036854775807l, cast('11342371013783243717493546650944543.47' as
decimal(38,2))),
(9223372036854775807l, cast('.99' as
decimal(38,2)))
as data(a, b);
SELECT
COUNT(1) OVER (
PARTITION BY a
ORDER BY b ASC
RANGE BETWEEN 10.2345 PRECEDING AND 6.7890 FOLLOWING
) AS CNT_1
FROM
test_table;
{noformat}
> Incorrect result for window frames defined by a range clause on large
> decimals
> ---
>
> Key: SPARK-41793
> URL: https://issues.apache.org/jira/browse/SPARK-41793
> Project: Spark
> Issue Type: Bug
> Components: SQL
>Affects Versions: 3.4.0
>Reporter: Gera Shegalov
>Priority: Major
>
> Context
> https://github.com/NVIDIA/spark-rapids/issues/7429#issuecomment-1368040686
> The following windowing query on a simple two-row input should produce two
> non-empty windows as a result
> {code}
> from pprint import pprint
> data = [
> ('9223372036854775807', '11342371013783243717493546650944543.47'),
> ('9223372036854775807', '.99')
> ]
> df1 = spark.createDataFrame(data, 'a STRING, b STRING')
> df2 = df1.select(df1.a.cast('LONG'), df1.b.cast('DECIMAL(38,2)'))
> df2.createOrReplaceTempView('test_table')
> df = sql('''
> SELECT
> COUNT(1) OVER (
> PARTITION BY a
> ORDER BY b ASC
> RANGE BETWEEN 10.2345 PRECEDING AND 6.7890 FOLLOWING
> ) AS CNT_1
> FROM
> test_table
> ''')
> res = df.collect()
> df.explain(True)
> pprint(res)
> {code}
> Spark 3.4.0-SNAPSHOT output:
> {code}
> [Row(CNT_1=1), Row(CNT_1=0)]
> {code}
> Spark 3.3.1 output as expected:
> {code}
> Row(CNT_1=1), Row(CNT_1=1)]
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
[jira] [Commented] (SPARK-41793) Incorrect result for window frames defined by a range clause on large decimals
[
https://issues.apache.org/jira/browse/SPARK-41793?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17653238#comment-17653238
]
Gera Shegalov commented on SPARK-41793:
---
Similarly in SQLite
{code}
.header on
create table test_table(a long, b decimal(38,2));
insert into test_table
values
('9223372036854775807', '11342371013783243717493546650944543.47'),
('9223372036854775807', '.99');
select * from test_table;
select
count(1) over(
partition by a
order by b asc
range between 10.2345 preceding and 6.7890 following) as cnt_1
from
test_table;
{code}
yields
{code}
a|b
9223372036854775807|1.13423710137832e+34
9223372036854775807|1.0e+36
cnt_1
1
1
{code}
> Incorrect result for window frames defined by a range clause on large
> decimals
> ---
>
> Key: SPARK-41793
> URL: https://issues.apache.org/jira/browse/SPARK-41793
> Project: Spark
> Issue Type: Bug
> Components: SQL
>Affects Versions: 3.4.0
>Reporter: Gera Shegalov
>Priority: Major
>
> Context
> https://github.com/NVIDIA/spark-rapids/issues/7429#issuecomment-1368040686
> The following windowing query on a simple two-row input should produce two
> non-empty windows as a result
> {code}
> from pprint import pprint
> data = [
> ('9223372036854775807', '11342371013783243717493546650944543.47'),
> ('9223372036854775807', '.99')
> ]
> df1 = spark.createDataFrame(data, 'a STRING, b STRING')
> df2 = df1.select(df1.a.cast('LONG'), df1.b.cast('DECIMAL(38,2)'))
> df2.createOrReplaceTempView('test_table')
> df = sql('''
> SELECT
> COUNT(1) OVER (
> PARTITION BY a
> ORDER BY b ASC
> RANGE BETWEEN 10.2345 PRECEDING AND 6.7890 FOLLOWING
> ) AS CNT_1
> FROM
> test_table
> ''')
> res = df.collect()
> df.explain(True)
> pprint(res)
> {code}
> Spark 3.4.0-SNAPSHOT output:
> {code}
> [Row(CNT_1=1), Row(CNT_1=0)]
> {code}
> Spark 3.3.1 output as expected:
> {code}
> Row(CNT_1=1), Row(CNT_1=1)]
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
-
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
