dreverri opened a new issue, #27332:
URL: https://github.com/apache/datafusion/issues/27332
### Describe the bug
Unparsing the optimized plan of a simple `count(*)` query with a filter
produces `SELECT FROM ...` (an empty select list), even for dialects whose
`supports_empty_select_list()` is `false`. Engines that don't accept an empty
select list reject the generated SQL. For example, BigQuery fails with:
```
SELECT list must not be empty
```
The column used in the filter isn't needed above the `Filter`, so
`optimize_projections` prunes it and leaves a `Projection` with no expressions:
```
Projection: count(Int64(1)) AS count(*)
Aggregate: groupBy=[[]], aggr=[[count(Int64(1))]]
Projection:
Filter: t.id > Int32(10)
TableScan: t projection=[id]
```
#19221 (closing #18991) added `Dialect::supports_empty_select_list` and a
`SELECT 1` fallback, but only the `TableScan` path checks it (the case where
the scan has `projection=[]`, as in `select count(*) from t` with no filter).
An empty `Projection` node goes through `reconstruct_select_statement`, which
uses `p.expr` as is.
### To Reproduce
```rust
use datafusion::prelude::SessionContext;
use datafusion::sql::unparser::plan_to_sql;
let ctx = SessionContext::new();
ctx.sql("create table t (id int)").await?.collect().await?;
let plan = ctx
.sql("select count(*) from t where id > 10")
.await?
.into_optimized_plan()?;
println!("{}", plan_to_sql(&plan)?);
```
Output (default dialect):
```sql
SELECT count(1) AS "count(*)" FROM (SELECT FROM t WHERE (t.id > 10))
```
`BigQueryDialect` produces the same thing with backtick quoting.
### Expected behavior
An empty `Projection` should get the same dialect-dependent fallback as an
empty `TableScan` projection:
- Dialects with `supports_empty_select_list() == false` (default, BigQuery,
...):
```sql
SELECT count(1) AS "count(*)" FROM (SELECT 1 FROM t WHERE (t.id > 10))
```
- Dialects that support it (PostgreSQL): unchanged, `SELECT FROM ...`
### Additional context
The fix is small: in `reconstruct_select_statement`
(`datafusion/sql/src/unparser/plan.rs`), use `self.empty_projection_fallback()`
when `p.expr` is empty. I'll open a PR with the fix and tests.
--
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]
