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]

Reply via email to