[I] Support the unparsing of empty projections as `SELECT FROM` [datafusion]

["nuno-faria (via GitHub)" <gi...@apache.org>]

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: github-unsubscribe@datafusion.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org

Re: [I] Support the unparsing of empty projections as `SELECT FROM` [datafusion]

["kosiew (via GitHub)" <gi...@apache.org>]

kosiew closed issue #18991: Support the unparsing of empty projections as `SELECT FROM`
URL: https://github.com/apache/datafusion/issues/18991


-- 
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: github-unsubscribe@datafusion.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org