[ 
https://issues.apache.org/jira/browse/SPARK-61870?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Yang Jie updated SPARK-61870:
-----------------------------
    Description: 
{{InjectRuntimeFilter}} can take a DSv2 scan with a fully pushed {{LIMIT}}, 
{{OFFSET}}, top-N or {{TABLESAMPLE}} as the creation side of a runtime bloom 
filter when a selective {{Filter}} sits above it ({{case _: LeafNode if 
hasHitSelectiveFilter}} in {{extractSelectiveFilterOverScan}}). The bloom 
filter's subquery reads the scan again, apart from the join. A {{LIMIT}} 
without {{ORDER BY}}, or a top-N with ties, may return other rows on that read, 
so the bloom filter can miss keys that the join reads, and the join silently 
drops their rows.

For example, with the test source {{RotatingLimitDataSourceV2}} (each read of a 
pushed {{LIMIT}} returns other rows), 
{{spark.sql.autoBroadcastJoinThreshold=-1}}, DPP off and a small 
{{spark.sql.optimizer.runtime.bloomFilter.applicationSideScanSizeThreshold}}, 
master returns {{[1, 1]}} for the following query instead of {{[0, 91]}}, with 
AQE on and off. {{fact}} has 1000 rows with {{k = id % 11}}.

{code:sql}
SELECT count_if(f.id IS NULL), count(*)
FROM (SELECT * FROM dim LIMIT 1) d LEFT JOIN fact f ON d.i = f.k
WHERE d.i > 0
{code}

The same leaf case is on branch-3.5 and branch-4.0 through branch-4.4. 
apache/spark#60672 fixes it by taking a DSv2 scan as the creation side only if 
reading it again gives the same rows.


  was:
{{InjectRuntimeFilter}} can take a DSv2 scan with a fully pushed {{LIMIT}}, 
{{OFFSET}}, top-N or {{TABLESAMPLE}} as the creation side of a runtime bloom 
filter when a selective {{Filter}} sits above it ({{case _: LeafNode if 
hasHitSelectiveFilter}} in {{extractSelectiveFilterOverScan}}). The bloom 
filter's subquery reads the scan again, apart from the join. A {{LIMIT}} 
without {{ORDER BY}}, or a top-N with ties, may return other rows on that read, 
so the bloom filter can miss keys that the join reads, and the join silently 
drops their rows.

For example, with the test source {{RotatingLimitDataSourceV2}} (each read of a 
pushed {{LIMIT}} returns other rows), 
{{spark.sql.autoBroadcastJoinThreshold=-1}}, DPP off and a small 
{{spark.sql.optimizer.runtime.bloomFilter.applicationSideScanSizeThreshold}}, 
master returns {{[1, 1]}} for the following query instead of {{[0, 91]}}, with 
AQE on and off. {{fact}} has 1000 rows with {{k = id % 11}}.

{code:sql}
SELECT count_if(f.id IS NULL), count(*)
FROM (SELECT * FROM dim LIMIT 1) d LEFT JOIN fact f ON d.i = f.k
WHERE d.i > 0
{code}

The same leaf case is on branch-3.5 and branch-4.0 through branch-4.4. 
apache/spark#60387 (SPARK-61173) fixes it on master by taking a DSv2 scan as 
the creation side only if reading it again gives the same rows. This PR is an 
improvement and goes to master and branch-4.x only, so branch-4.0 through 
branch-4.4 need their own change.



> Fix wrong results when a runtime bloom filter reads a DSv2 scan with a pushed 
> LIMIT again
> -----------------------------------------------------------------------------------------
>
>                 Key: SPARK-61870
>                 URL: https://issues.apache.org/jira/browse/SPARK-61870
>             Project: Spark
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 3.5.9, 4.3.0, 5.0.0, 4.2.1, 4.0.5, 4.1.4
>            Reporter: Yang Jie
>            Priority: Major
>              Labels: correctness, pull-request-available
>
> {{InjectRuntimeFilter}} can take a DSv2 scan with a fully pushed {{LIMIT}}, 
> {{OFFSET}}, top-N or {{TABLESAMPLE}} as the creation side of a runtime bloom 
> filter when a selective {{Filter}} sits above it ({{case _: LeafNode if 
> hasHitSelectiveFilter}} in {{extractSelectiveFilterOverScan}}). The bloom 
> filter's subquery reads the scan again, apart from the join. A {{LIMIT}} 
> without {{ORDER BY}}, or a top-N with ties, may return other rows on that 
> read, so the bloom filter can miss keys that the join reads, and the join 
> silently drops their rows.
> For example, with the test source {{RotatingLimitDataSourceV2}} (each read of 
> a pushed {{LIMIT}} returns other rows), 
> {{spark.sql.autoBroadcastJoinThreshold=-1}}, DPP off and a small 
> {{spark.sql.optimizer.runtime.bloomFilter.applicationSideScanSizeThreshold}}, 
> master returns {{[1, 1]}} for the following query instead of {{[0, 91]}}, 
> with AQE on and off. {{fact}} has 1000 rows with {{k = id % 11}}.
> {code:sql}
> SELECT count_if(f.id IS NULL), count(*)
> FROM (SELECT * FROM dim LIMIT 1) d LEFT JOIN fact f ON d.i = f.k
> WHERE d.i > 0
> {code}
> The same leaf case is on branch-3.5 and branch-4.0 through branch-4.4. 
> apache/spark#60672 fixes it by taking a DSv2 scan as the creation side only 
> if reading it again gives the same rows.



--
This message was sent by Atlassian Jira
(v10.3.26#10030026)

---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]

Reply via email to