Xi Liang created SPARK-62042:
--------------------------------
Summary: 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: 5.0.0
Reporter: Xi Liang
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]