Parse WITH ... AS [NOT] MATERIALIZED in GenericDialect, DuckDbDialect and SQLiteDialect #2622

WITH x AS [NOT] MATERIALIZED (...) is parsed only by PostgreSqlDialect. With GenericDialect (and all other dialects) the parser returns an error.

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:

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 apache/datafusion#25580 (issue apache/datafusion#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.