yiyi99 opened a new issue, #25258:
URL: https://github.com/apache/datafusion/issues/25258
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)
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
