[I] Generate well-indented SQL from LogicalPlan [datafusion]

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

edmondop opened a new issue, #11308:
URL: https://github.com/apache/datafusion/issues/11308

   ### Is your feature request related to a problem or challenge?
   
   DataFusion provides the capability of "unparse" a logical plan into SQL via the `unparser` module in the `sql` crate (see https://github.com/apache/datafusion/blob/main/datafusion/sql/src/unparser/plan.rs). [Examples](https://github.com/apache/datafusion/blob/main/datafusion-examples/examples/plan_to_sql.rs) are provided to document its usage. 
   
   For example, as a part of the work on SpiceAI and datafusion-federation, we have some rewrites on the tpch_q13 and we expect the final rewritten SQL to be the following:
   
   ```sql
   SELECT c_orders.c_count,
          Count(1) AS custdist
   FROM   (SELECT c_custkey                       AS c_custkey,
                  "count(tpch.orders.o_orderkey)" AS c_count
           FROM   (SELECT TPCH.customer.c_custkey,
                          Count(TPCH.orders.o_orderkey) AS
                          "COUNT(tpch.orders.o_orderkey)"
                   FROM   TPCH.customer
                          LEFT JOIN TPCH.orders
                                 ON ( ( TPCH.customer.c_custkey =
                                        TPCH.orders.o_custkey )
                                      AND TPCH.orders.o_comment NOT LIKE
                                          '%special%requests%' )
                   GROUP  BY TPCH.customer.c_custkey)) AS c_orders
   GROUP  BY c_orders.c_count
   ORDER  BY custdist DESC NULLS FIRST,
             c_orders.c_count DESC NULLS FIRST 
   ```
   
   however the `plan_to_sql` generates a one-line sql
   
   ```sql
   SELECT c_orders.c_count, COUNT(1) AS custdist FROM (SELECT c_custkey AS c_custkey, "COUNT(tpch.orders.o_orderkey)" AS c_count FROM (SELECT tpch.customer.c_custkey, COUNT(tpch.orders.o_orderkey) AS "COUNT(tpch.orders.o_orderkey)" FROM tpch.customer LEFT JOIN tpch.orders ON ((tpch.customer.c_custkey = tpch.orders.o_custkey) AND tpch.orders.o_comment NOT LIKE '%special%requests%') GROUP BY tpch.customer.c_custkey)) AS c_orders GROUP BY c_orders.c_count ORDER BY custdist DESC NULLS FIRST, c_orders.c_count DESC NULLS FIRST"
   ``` 
   
   ### Describe the solution you'd like
   
   I would like to be able to provide an extra parameter to the Unparser constructor, such as `pretty_print` or `indent`
   
   ```rust
       pub struct Unparser<'a> {
       dialect: &'a dyn Dialect,
   }
   
   impl<'a> Unparser<'a> {
       pub fn new(dialect: &'a dyn Dialect) -> Self {
           Self { dialect }
       }
   }
   
   impl<'a> Default for Unparser<'a> {
       fn default() -> Self {
           Self {
               dialect: &DefaultDialect {},
           }
       }
   }
   ```
   
   ### Describe alternatives you've considered
   
   _No response_
   
   ### Additional context
   
   I used this https://www.dpriver.com/pp/sqlformat.htm to generate the formatted SQL from the SQL generated from dataufusion


-- 
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] Generate well-indented SQL from LogicalPlan [datafusion]

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

edmondop commented on issue #11308:
URL: https://github.com/apache/datafusion/issues/11308#issuecomment-2228795638

   Waiting https://github.com/sqlparser-rs/sqlparser-rs/issues/1294 to happen as it is likely that  sql-parser will be migrated to datafusion and the change will be easier


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