nuno-faria opened a new issue, #18991: URL: https://github.com/apache/datafusion/issues/18991
### Describe the bug When a logical plan contains a `None` projection, it is unparsed to `SELECT * FROM ...`, which helps avoiding invalid results (https://github.com/apache/datafusion/issues/11743). However, when a projection is deliberately empty, it is unparsed as `SELECT 1 FROM ...` (https://github.com/apache/datafusion/pull/14116). While this is the best approach for most SQL systems, PostgreSQL actually supports the [`SELECT FROM ...` syntax](https://www.postgresql.org/docs/current/sql-select.html#id-1.9.3.172.10.4): ```sql postgres=# select from t; -- (2 rows) ``` DataFusion itself also supports this syntax: ```sql > select from t; ++ ++ ++ 3 row(s) fetched. ``` My main use case for this are queries such as `SELECT count(*) FROM ...` executed over external data sources, where the logical plan generates empty projections: ```sql Projection: count(Int64(1)) AS count(*) Aggregate: groupBy=[[]], aggr=[[count(Int64(1))]] TableScan: t projection=[] ``` If only the table scan is pushed to the database (e.g., `count(*)` done over a union with different data sources), the pushed query will be `SELECT 1 ...`, which will be different from the expected `RecordBatch` schema when converting to arrow (i.e., no columns). (The even better alternative would be to automatically optimize the `count(*)` of unions into `count(*) + count(*)`, which would also solve this issue.) ### To Reproduce ```rust use datafusion::error::Result; use datafusion::prelude::SessionContext; use datafusion::sql::unparser::{self, Unparser}; #[tokio::main] async fn main() -> Result<()> { let ctx = SessionContext::new(); ctx.sql("create table t (k int, v int)") .await? .collect() .await?; let df = ctx.sql("select from t").await?; let plan = df.into_optimized_plan()?; println!("{}", plan.display_indent()); let sql = Unparser::new(&unparser::dialect::PostgreSqlDialect {}).plan_to_sql(&plan)?; println!("{sql}"); Ok(()) } ``` ``` TableScan: t projection=[] SELECT 1 FROM "t" ``` ### Expected behavior Generating `SELECT FROM ...` on empty projections for the dialects that support it. ### Additional context The introduction of the literal 1 is done here: https://github.com/apache/datafusion/blob/73562e8ea14efac5ed6827dddb37362d7019266e/datafusion/sql/src/unparser/plan.rs#L1105-L1109 -- 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]
