[SPARK-28299] Evaluation of multiple CTE uses

• Type: Sub-task
• Status: Resolved
• Priority: Major
• Resolution: Duplicate
• Affects Version/s: 3.1.0
• Component/s: SQL
• Assignee: Unassigned
• Reporter: Peter Toth
• Created: 08/Jul/19 10:32
• Updated: 24/Nov/21 09:55
• Resolved: 24/Nov/21 09:55

Description

This query returns 2 in Spark SQL (ie. the CTE is evaluated twice), but it returns 1 in PostgreSQL.

WITH t(x) AS (SELECT random())
SELECT count(*) FROM (
  SELECT * FROM t
  UNION
  SELECT * FROM t
) x

I tested MSSQL too and it returns 2 as Spark SQL does. Further tests are needed on different DBs...

Issue Links

• duplicates: SPARK-36447 Avoid inlining non-deterministic With-CTEs