When performing set comparison subqueries (such as > ANY / > ALL / = ANY, etc.) on the same table (without using aliases), silently returns wrong results (0 rows or all rows).

[yiyi99]

When the outer query and a set comparison subquery (ANY / ALL) reference the same table without an alias, DataFusion silently returns wrong results — no error is raised:

• ordered comparisons (>, >=, <, <=) with ANY/ALL → 0 rows (expected non-empty)
• = ANY → all rows (expected only matching rows)

Aliasing either side (or using two different tables) produces correct results.
Verified on 54.1.0 and on the latest stable 55.1.0 (datafusion = "55.1").

To Reproduce

CREATE TABLE emp (id INT, name TEXT, salary INT);
INSERT INTO emp VALUES
  (1,'Alice',5000),(2,'Bob',6000),(3,'Carol',7000),
  (4,'Dave',8000),(5,'Eve',9000),(6,'Frank',10000);

-- returns 0 rows, expected ids 2..6
SELECT id FROM emp WHERE salary > ANY (SELECT salary FROM emp WHERE id <= 3);

-- returns 0 rows, expected ids 3..6
SELECT id FROM emp WHERE salary > ALL (SELECT salary FROM emp WHERE id <= 2);

-- returns ALL 6 rows, expected ids 1,2
SELECT id FROM emp WHERE salary = ANY (SELECT salary FROM emp WHERE id <= 2);

-- workaround: aliasing either side returns correct results
SELECT e.id FROM emp e WHERE e.salary > ANY (SELECT s.salary FROM emp s WHERE s.id <= 3);

Standalone Rust reproducer (MemTable, no custom TableProvider):

use datafusion::arrow::array::{Int32Array, RecordBatch, StringArray};
use datafusion::arrow::datatypes::{DataType, Field, Schema};
use datafusion::datasource::MemTable;
use datafusion::prelude::SessionContext;
use std::sync::Arc;

#[tokio::main]
async fn main() -> datafusion::error::Result<()> {
    let schema = Arc::new(Schema::new(vec![
        Field::new("id", DataType::Int32, false),
        Field::new("name", DataType::Utf8, true),
        Field::new("salary", DataType::Int32, true),
    ]));
    let batch = RecordBatch::try_new(
        schema.clone(),
        vec![
            Arc::new(Int32Array::from(vec![1, 2, 3, 4, 5, 6])),
            Arc::new(StringArray::from(vec!["Alice","Bob","Carol","Dave","Eve","Frank"])),
            Arc::new(Int32Array::from(vec![5000, 6000, 7000, 8000, 9000, 10000])),
        ],
    )?;
    let ctx = SessionContext::new();
    ctx.register_table("emp", Arc::new(MemTable::try_new(schema, vec![vec![batch]])?))?;

    for sql in [
        "SELECT id FROM emp WHERE salary > ANY (SELECT salary FROM emp WHERE id <= 3)",
        "SELECT id FROM emp WHERE salary = ANY (SELECT salary FROM emp WHERE id <= 2)",
        "SELECT e.id FROM emp e WHERE e.salary > ANY (SELECT s.salary FROM emp s WHERE s.id <= 3)",
    ] {
        let rows = ctx.sql(sql).await?.collect().await?;
        let n: usize = rows.iter().map(|b| b.num_rows()).sum();
        println!("{sql} -> {n} rows");
    }
    Ok(())
}

Output on 55.1.0:

SELECT id FROM emp WHERE salary > ANY (SELECT salary FROM emp WHERE id <= 3) -> 0 rows    -- expected 5
SELECT id FROM emp WHERE salary = ANY (SELECT salary FROM emp WHERE id <= 2) -> 6 rows    -- expected 2
SELECT e.id FROM emp e WHERE e.salary > ANY (SELECT s.salary FROM emp s WHERE s.id <= 3) -> 5 rows  -- correct (aliased)

[AdamGS]

@yiyi99 if you don't plan on working on this, I can take it

[yiyi99]

Thanks! I don't have bandwidth to work on a fix right now — please go ahead.

FWIW, in case it helps: we observed the optimized plan ends up with the correlation predicate pushed inside the subquery scan as a self-comparison (emp.salary > emp.salary IS TRUE) when the outer query and subquery reference the same unaliased table — looks like the outer reference becomes indistinguishable from the inner column after the SubqueryAlias wrapper is stripped during pushdown. Verified on 54.1.0 and 55.1.0. Happy to review/test the fix.

@yiyi99 if you don't plan on working on this, I can take it

[AdamGS]

Thanks! I'll tag you once I have a PR

[AdamGS]

@yiyi99 if you want to take a look - #25273

[yiyi99]

@yiyi99 if you want to take a look - #25273

Thanks!I tested against this branch (eba49b2, via a git dependency), with expected results derived from PostgreSQL semantics (using the same emp data as in the issue): 14/14 passed. All previously broken unaliased forms now match PostgreSQL behavior, and the previously working aliased forms remain unaffected.

form	result
> >= < <= × ANY (subquery id <= 3)	5 / 6 / 2 / 3 rows
> >= < <= × ALL (subquery id <= 2)	4 / 5 / 0 / 1 rows
= ANY / <> ALL	2 / 4 rows
nested > ANY (… WHERE id IN (SELECT id FROM emp …))	PASS
IN / scalar subquery, same unaliased table	PASS
aliased-outer regression check	PASS

Two review suggestions:

1. Test surface vs. reported surface: the SLT covers > ANY, > ALL, = ANY, but the issue covers all four ordered operators. Since they all route through the fixed shared path, I'd suggest adding the remaining variants (>= ANY, < ANY, <= ANY, >= ALL, < ALL, <= ALL, <> ALL) and a nested form to the SLT — my run above confirms they pass on this branch, so it's just locking them in.
2. API change note: find_join_exprs is public and its contract changed (it no longer strips outer references). Engines embedding DataFusion that call it directly would silently get different filters back — might be worth a doc comment / changelog entry.

Also glad to see #25276 filed for the mark-join plan shape. Once this lands in a release we'll drop our alias-injection workaround downstream. Thanks again!