verifyPPLToSparkSQL pins invalid Spark SQL in CalcitePPLScalarSubqueryTest.testCorrelatedScalarSubqueryInWhere

[penghuo]

Summary

The expectedSparkSql pinned by CalcitePPLScalarSubqueryTest.testCorrelatedScalarSubqueryInWhere is not valid Spark SQL — Spark, DuckDB, and SQLite all refuse to analyze it. The unit test passes only because verifyPPLToSparkSQL checks the printed SQL string and never executes it. This makes the assertion misleading: the Spark in the helper name implies an executable contract that doesn't exist.

Where

• File: ppl/src/test/java/org/opensearch/sql/ppl/calcite/CalcitePPLScalarSubqueryTest.java
• Test: testCorrelatedScalarSubqueryInWhere (around line 119)
• Helper: CalcitePPLAbstractTest.verifyPPLToSparkSQL (line 185) — only string-equals on sqlNode.toSqlString(OpenSearchSparkSqlDialect.DEFAULT); never analyzes/runs against Spark.

The PPL under test

source=EMP
| where SAL > [
    source=SALGRADE | where SAL = HISAL | stats AVG(SAL)
  ]

The expected Spark SQL pinned by the test

SELECT *
FROM `scott`.`EMP`
WHERE `SAL` > (((SELECT AVG(`EMP`.`SAL`) AS `AVG(SAL)`
                 FROM `scott`.`SALGRADE`
                 WHERE `EMP`.`SAL` = `HISAL`)))

Why it is invalid

The inner SELECT has FROM scott.SALGRADE, but SALGRADE does not have a SAL column. Both SAL references inside the subquery silently bind to the outer EMP.SAL (a correlated reference). The aggregate AVG(EMP.SAL) is therefore an aggregate over an outer column, which the SQL standard treats as an aggregate of the outer query block, not the inner one. That cannot be placed inside the outer WHERE clause.

The Calcite logical plan agrees that both SAL references are outer references:

LogicalFilter(condition=[>($7, $SCALAR_QUERY({
  LogicalAggregate(group=[{}], AVG(SAL)=[AVG($0)])
    LogicalProject($f0=[$cor0.SAL])              <-- AVG over outer column
      LogicalFilter(condition=[=($cor0.SAL, $0)]) <-- outer = SALGRADE.HISAL
        ...SALGRADE...
})], variablesSet=[[$cor0]])
  ...EMP...

Reproduction with real engines

Spark 4.1 (PySpark, the dialect the test claims to emit)

[UNSUPPORTED_SUBQUERY_EXPRESSION_CATEGORY.CORRELATED_REFERENCE]
Unsupported subquery expression: Expressions referencing the outer query
are not supported outside of WHERE/HAVING clauses:
"avg(SAL) AS `AVG(SAL)`". SQLSTATE: 0A000

Spark plan fragment before analysis fails:

Filter (SAL > scalar-subquery [avg(SAL) && SAL])
  Aggregate [avg(outer(SAL)) AS AVG(SAL)]
    Filter (outer(SAL) = HISAL)
      Relation scott.salgrade[GRADE,LOSAL,HISAL]

A valid SQL query equivalent to the physical plan

The optimized physical plan from opensearch _explain already de-correlates the query into a flat join (nested-loop because of the > predicate). The following SQL is accepted by Spark and matches that physical plan:

SELECT e.*
FROM   scott.EMP AS e
JOIN (
  SELECT s.SAL              AS sal_key,
         AVG(s.SAL)         AS avg_sal
  FROM   (SELECT DISTINCT SAL FROM scott.EMP) AS s
  CROSS JOIN scott.SALGRADE AS g
  WHERE  g.HISAL IS NOT NULL
  GROUP  BY s.SAL
) AS sub
  ON e.SAL <=> sub.sal_key   -- IS NOT DISTINCT FROM (null-safe equi-key, matches plan)
 AND e.SAL  > sub.avg_sal    -- nested-loop range predicate, matches plan

[dai-chen]

Here is my test with Apache Calcite SQL using sqlline to verify its behavior.

Test Data

EMPS table (DEPTS has no GENDER column):

EMPNO	NAME	DEPTNO	GENDER	CITY	EMPID	AGE	SLACKER	MANAGER	JOINEDAT
100	Fred	10			30	25	true	false	1996-08-03
110	Eric	20	M	San Francisco	3	80		false	2001-01-01
110	John	40	M	Vancouver	2	null	false	true	2002-05-03
120	Wilma	20	F		1	5		true	2005-09-07
130	Alice	40	F	Vancouver	2	null	false	true	2007-01-01

DEPTS table:

DEPTNO	NAME
10	Sales
20	Marketing
30	Accounts

Query 1: Ambiguous column names (deptno, name exist in both tables)

SELECT name FROM emps WHERE deptno IN (SELECT deptno FROM depts WHERE name = 'Sales');

Result:

+------+
| NAME |
+------+
| Fred |
+------+
1 row selected

✅ Calcite correctly resolves each column reference to its respective table's scope. Only Fred (deptno=10) matches Sales.

Query 2: Outer table column referenced in subquery (GENDER does NOT exist in DEPTS)

SELECT name FROM emps WHERE gender IN (SELECT gender FROM depts WHERE name = 'Sales');

Result:

+-------+
| NAME  |
+-------+
| Fred  |
| Eric  |
| John  |
| Wilma |
| Alice |
+-------+
5 rows selected

⚠️ No validation error is raised. Calcite resolves gender in the subquery from the outer emps table per SQL standard correlated subquery scoping (SQL-92 §7.6
(https://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt)). The subquery effectively becomes:

SELECT emps.gender FROM depts WHERE name = 'Sales'

This means each row checks whether its own gender value is IN a set containing... its own gender value — which is always true (when the WHERE clause matches at least one row). Hence all 5 rows are returned.