[
https://issues.apache.org/jira/browse/SPARK-62042?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Xi Liang updated SPARK-62042:
-----------------------------
Labels: correctness (was: )
> Exclude non-deterministic nested field accesses from nested column aliasing
> ---------------------------------------------------------------------------
>
> Key: SPARK-62042
> URL: https://issues.apache.org/jira/browse/SPARK-62042
> Project: Spark
> Issue Type: Bug
> Components: SQL
> Affects Versions: 4.1.0, 4.0.0, 4.2.0, 4.3.0, 5.0.0
> Reporter: Xi Liang
> Priority: Major
> Labels: correctness
>
> Nested column aliasing ({{NestedColumnAliasing}} and
> {{GeneratorNestedColumnAliasing}}, applied by {{ColumnPruning}}) replaces a
> nested field access with an alias that it evaluates in a new {{Project}}
> below an operator. It does this for a non-deterministic access too. A query's
> select list is evaluated once per row of its input, so a non-deterministic
> expression in it should give each row its own value, but the alias is
> evaluated once per row of the operator's input. When the operator repeats
> input rows, as a join, an explode or a sample with replacement does, every
> output row built from one input row then gets the same value.
> On master, and on 4.0.0 and 4.3.0 (pyspark from PyPI), with AQE on and off:
> {code:sql}
> CREATE TABLE t1 USING parquet AS
> SELECT 1 AS id,
> named_struct('arr', transform(sequence(0, 999), i -> named_struct('x', i,
> 'y', i)), 'e', 0) AS s,
> sequence(1, 1000) AS seq;
> CREATE TABLE t2 USING parquet AS SELECT 1 AS id FROM range(1000);
> -- 1000 joined rows, each picking a random element of s.arr
> SELECT count(*), count(DISTINCT v) FROM (SELECT s.arr[CAST(rand(0) * 1000 AS
> INT)].x AS v FROM t1 JOIN t2 ON t1.id = t2.id);
> -- expected: 1000, about 632
> -- actual: 1000, 1
> -- 1000 exploded rows, each picking a random element of s.arr (the query also
> reads n; see below)
> SELECT count(*), count(DISTINCT v), sum(n) FROM (SELECT s.arr[CAST(rand(0) *
> 1000 AS INT)].x AS v, n FROM t1 LATERAL VIEW explode(seq) g AS n);
> -- expected: 1000, about 632, 500500
> -- actual: 1000, 1, 500500
> -- control: the same random pick from seq, an array of integers; seq[...] has
> no struct
> -- field access, so nested column aliasing leaves it alone
> SELECT count(*), count(DISTINCT v) FROM (SELECT seq[CAST(rand(0) * 1000 AS
> INT)] AS v FROM t1 JOIN t2 ON t1.id = t2.id);
> -- actual: 1000, 630
> {code}
> 1000 random picks from 1000 values give about 632 distinct values on average;
> with seed 0, the control gives 630. The exact counts in this ticket come from
> local runs and can differ with the partitioning; the signal is one value
> versus about 632.
> The optimized plan of the first query's subquery evaluates the access below
> the join, once for the one row of {{t1}}:
> {code}
> Project [_extract_x#240 AS v#237]
> +- Join Inner, (id#135 = id#138)
> :- Filter isnotnull(id#135)
> : +- Project [id#135, s#136.arr[cast((rand(0) * 1000.0) as int)].x AS
> _extract_x#240]
> : +- Relation spark_catalog.default.t1[id#135,s#136,seq#137] parquet
> +- Filter isnotnull(id#138)
> +- Relation spark_catalog.default.t2[id#138] parquet
> {code}
> Other accesses and operators behave the same. On master, over {{t1}}, {{t2}}
> and a new table {{t4}}, each of these gives one distinct value:
> {code:sql}
> -- 1000 rows
> SELECT s.arr[CAST(monotonically_increasing_id() % 1000 AS INT)].x AS v FROM
> t1 JOIN t2 ON t1.id = t2.id;
> -- 1000 rows
> SELECT shuffle(s.arr, 0)[0].x AS v FROM t1 JOIN t2 ON t1.id = t2.id;
> CREATE TABLE t4 USING parquet AS SELECT 1 AS id, transform(sequence(0, 999),
> i -> named_struct('b', i, 'c', i)) AS a;
> -- a random index into the top-level array column a: 1000 rows
> SELECT a[CAST(rand(0) * 1000 AS INT)].b AS v FROM t4 JOIN t2 ON t4.id = t2.id;
> {code}
> {code:scala}
> // a sample with replacement: 1016 rows
> spark.table("t1").sample(withReplacement = true, fraction = 1000.0, seed = 0)
> .selectExpr("s.arr[CAST(rand(0) * 1000 AS INT)].x AS v")
> {code}
> The join case needs {{spark.sql.optimizer.nestedSchemaPruning.enabled}}
> (default true): with it off, the join query gives 630 distinct values. The
> explode case needs {{spark.sql.optimizer.nestedSchemaPruning.enabled}} or
> {{spark.sql.optimizer.expression.nestedPruning.enabled}} (also default true):
> with only {{spark.sql.optimizer.nestedSchemaPruning.enabled}} off, the
> explode query still gives 1, and with both off, 630. The explode case also
> needs the query to read the generator output {{n}}: without {{n}} in the
> subquery's select list, the explode query gives 630. By reading the code,
> {{ColumnPruning}} has an earlier case for a {{Project}} above a {{Generate}}
> that does not read all of the {{Generate}}'s output; it matches such a
> {{Project}} on every iteration, so {{GeneratorNestedColumnAliasing}} never
> sees it.
> 4.1.0 and 4.2.0 have the same {{NestedColumnAliasing.canAlias}} and the same
> lists of operators that nested column aliasing aliases below, by reading the
> code; they were not run. The join case dates from SPARK-31736 (3.1.0), which
> made nested column aliasing work through a join. The changes that introduced
> the explode and sample cases were not looked up, and 3.x was not run.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]