[I] Parse `WITH ... AS [NOT] MATERIALIZED` in `GenericDialect`, `DuckDbDialect` and `SQLiteDialect` [datafusion-sqlparser-rs]

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

adriangb opened a new issue, #2622:
URL: https://github.com/apache/datafusion-sqlparser-rs/issues/2622

   `WITH x AS [NOT] MATERIALIZED (...)` is parsed only by `PostgreSqlDialect`. With `GenericDialect` (and all other dialects) the parser returns an error.
   
   ```rust
   use sqlparser::dialect::{GenericDialect, PostgreSqlDialect};
   use sqlparser::parser::Parser;
   
   let sql = "WITH c AS MATERIALIZED (SELECT 1) SELECT * FROM c";
   assert!(Parser::parse_sql(&PostgreSqlDialect {}, sql).is_ok());
   // sql parser error: Expected: (, found: MATERIALIZED
   assert!(Parser::parse_sql(&GenericDialect {}, sql).is_err());
   ```
   
   The cause is the dialect check in `Parser::parse_cte`:
   
   ```rust
   let mut is_materialized = None;
   if dialect_of!(self is PostgreSqlDialect) {
       if self.parse_keyword(Keyword::MATERIALIZED) {
           is_materialized = Some(CteAsMaterialized::Materialized);
       } else if self.parse_keywords(&[Keyword::NOT, Keyword::MATERIALIZED]) {
           is_materialized = Some(CteAsMaterialized::NotMaterialized);
       }
   }
   ```
   
   ### Why
   
   DataFusion is adding support for the directive in https://github.com/apache/datafusion/pull/25580 (issue https://github.com/apache/datafusion/issues/17737). The default dialect of DataFusion is `GenericDialect`, so users must change the dialect to PostgreSQL to use it.
   
   DuckDB and SQLite accept the same syntax in the same position, so `DuckDbDialect` and `SQLiteDialect` should also parse it.
   
   ### Proposal
   
   Replace the `dialect_of!` check with a dialect method, for example `Dialect::supports_cte_materialized()`, that returns `true` for `PostgreSqlDialect`, `GenericDialect`, `DuckDbDialect` and `SQLiteDialect`.
   
   `MATERIALIZED` directly after `AS` and before `(` is not ambiguous with other syntax, so the change is small.
   


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