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). #25258

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);

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(())
}

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)