datafusion_sql/unparser/
dialect.rs

1// Licensed to the Apache Software Foundation (ASF) under one
2// or more contributor license agreements.  See the NOTICE file
3// distributed with this work for additional information
4// regarding copyright ownership.  The ASF licenses this file
5// to you under the Apache License, Version 2.0 (the
6// "License"); you may not use this file except in compliance
7// with the License.  You may obtain a copy of the License at
8//
9//   http://www.apache.org/licenses/LICENSE-2.0
10//
11// Unless required by applicable law or agreed to in writing,
12// software distributed under the License is distributed on an
13// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
14// KIND, either express or implied.  See the License for the
15// specific language governing permissions and limitations
16// under the License.
17
18use std::{collections::HashMap, sync::Arc};
19
20use super::{
21    Unparser, utils::character_length_to_sql, utils::date_part_to_sql,
22    utils::sqlite_date_trunc_to_sql, utils::sqlite_from_unixtime_to_sql,
23};
24use arrow::array::timezone::Tz;
25use arrow::datatypes::TimeUnit;
26use chrono::DateTime;
27use datafusion_common::{Result, internal_err};
28use datafusion_expr::Expr;
29use regex::Regex;
30use sqlparser::tokenizer::Span;
31use sqlparser::{
32    ast::{
33        self, BinaryOperator, Function, Ident, ObjectName, TimezoneInfo, WindowFrameBound,
34    },
35    keywords::ALL_KEYWORDS,
36};
37
38pub type ScalarFnToSqlHandler =
39    Box<dyn Fn(&Unparser, &[Expr]) -> Result<Option<ast::Expr>> + Send + Sync>;
40
41/// `Dialect` to use for Unparsing
42///
43/// The default dialect tries to avoid quoting identifiers unless necessary (e.g. `a` instead of `"a"`)
44/// but this behavior can be overridden as needed
45///
46/// **Note**: This trait will eventually be replaced by the Dialect in the SQLparser package
47///
48/// See <https://github.com/sqlparser-rs/sqlparser-rs/pull/1170>
49/// See also the discussion in <https://github.com/apache/datafusion/pull/10625>
50pub trait Dialect: Send + Sync {
51    /// Return the character used to quote identifiers.
52    fn identifier_quote_style(&self, _identifier: &str) -> Option<char>;
53
54    /// Whether array literals should be rendered with the `ARRAY[...]` keyword.
55    fn use_array_keyword_for_array_literals(&self) -> bool {
56        false
57    }
58
59    /// Does the dialect support specifying `NULLS FIRST/LAST` in `ORDER BY` clauses?
60    fn supports_nulls_first_in_sort(&self) -> bool {
61        true
62    }
63
64    /// Does the dialect use TIMESTAMP to represent Date64 rather than DATETIME?
65    /// E.g. Trino, Athena and Dremio does not have DATETIME data type
66    fn use_timestamp_for_date64(&self) -> bool {
67        false
68    }
69
70    fn interval_style(&self) -> IntervalStyle {
71        IntervalStyle::PostgresVerbose
72    }
73
74    /// Does the dialect use DOUBLE PRECISION to represent Float64 rather than DOUBLE?
75    /// E.g. Postgres uses DOUBLE PRECISION instead of DOUBLE
76    fn float64_ast_dtype(&self) -> ast::DataType {
77        ast::DataType::Double(ast::ExactNumberInfo::None)
78    }
79
80    /// The SQL type to use for Arrow Utf8 unparsing
81    /// Most dialects use VARCHAR, but some, like MySQL, require CHAR
82    fn utf8_cast_dtype(&self) -> ast::DataType {
83        ast::DataType::Varchar(None)
84    }
85
86    /// The SQL type to use for Arrow LargeUtf8 unparsing
87    /// Most dialects use TEXT, but some, like MySQL, require CHAR
88    fn large_utf8_cast_dtype(&self) -> ast::DataType {
89        ast::DataType::Text
90    }
91
92    /// The date field extract style to use: `DateFieldExtractStyle`
93    fn date_field_extract_style(&self) -> DateFieldExtractStyle {
94        DateFieldExtractStyle::DatePart
95    }
96
97    /// The style to use when unparsing DISTINCT FROM style expressions
98    fn distinct_from_style(&self) -> DistinctFromStyle {
99        DistinctFromStyle::FullText
100    }
101
102    /// The character length extraction style to use: `CharacterLengthStyle`
103    fn character_length_style(&self) -> CharacterLengthStyle {
104        CharacterLengthStyle::CharacterLength
105    }
106
107    /// The SQL type to use for Arrow Int64 unparsing
108    /// Most dialects use BigInt, but some, like MySQL, require SIGNED
109    fn int64_cast_dtype(&self) -> ast::DataType {
110        ast::DataType::BigInt(None)
111    }
112
113    /// The SQL type to use for Arrow Int8 unparsing
114    /// Most dialects use TinyInt, but PostgreSQL prefers SmallInt
115    fn int8_cast_dtype(&self) -> ast::DataType {
116        ast::DataType::TinyInt(None)
117    }
118
119    /// The SQL type to use for Arrow Int32 unparsing
120    /// Most dialects use Integer, but some, like MySQL, require SIGNED
121    fn int32_cast_dtype(&self) -> ast::DataType {
122        ast::DataType::Integer(None)
123    }
124
125    /// The SQL type to use for Timestamp unparsing
126    /// Most dialects use Timestamp, but some, like MySQL, require Datetime
127    /// Some dialects like Dremio does not support WithTimeZone and requires always Timestamp
128    fn timestamp_cast_dtype(
129        &self,
130        _time_unit: &TimeUnit,
131        tz: &Option<Arc<str>>,
132    ) -> ast::DataType {
133        let tz_info = match tz {
134            Some(_) => TimezoneInfo::WithTimeZone,
135            None => TimezoneInfo::None,
136        };
137
138        ast::DataType::Timestamp(None, tz_info)
139    }
140
141    /// The SQL type to use for Arrow Date32 unparsing
142    /// Most dialects use Date, but some, like SQLite require TEXT
143    fn date32_cast_dtype(&self) -> ast::DataType {
144        ast::DataType::Date
145    }
146
147    /// Does the dialect support specifying column aliases as part of alias table definition?
148    /// (SELECT col1, col2 from my_table) AS my_table_alias(col1_alias, col2_alias)
149    fn supports_column_alias_in_table_alias(&self) -> bool {
150        true
151    }
152
153    /// Whether the dialect requires a table alias for any subquery in the FROM clause
154    /// This affects behavior when deriving logical plans for Sort, Limit, etc.
155    fn requires_derived_table_alias(&self) -> bool {
156        false
157    }
158
159    /// The division operator for the dialect
160    /// Most dialect uses ` BinaryOperator::Divide` (/)
161    /// But DuckDB dialect uses `BinaryOperator::DuckIntegerDivide` (//)
162    fn division_operator(&self) -> BinaryOperator {
163        BinaryOperator::Divide
164    }
165
166    /// Allows the dialect to override scalar function unparsing if the dialect has specific rules.
167    /// Returns None if the default unparsing should be used, or Some(ast::Expr) if there is
168    /// a custom implementation for the function.
169    fn scalar_function_to_sql_overrides(
170        &self,
171        _unparser: &Unparser,
172        _func_name: &str,
173        _args: &[Expr],
174    ) -> Result<Option<ast::Expr>> {
175        Ok(None)
176    }
177
178    /// Allows the dialect to override higher order function unparsing if the dialect has specific rules.
179    /// Returns None if the default unparsing should be used, or Some(ast::Expr) if there is
180    /// a custom implementation for the function.
181    fn higher_order_function_to_sql_overrides(
182        &self,
183        _unparser: &Unparser,
184        _func_name: &str,
185        _args: &[Expr],
186    ) -> Result<Option<ast::Expr>> {
187        Ok(None)
188    }
189
190    /// Allows the dialect to choose to omit window frame in unparsing
191    /// based on function name and window frame bound
192    /// Returns false if specific function name / window frame bound indicates no window frame is needed in unparsing
193    fn window_func_support_window_frame(
194        &self,
195        _func_name: &str,
196        _start_bound: &WindowFrameBound,
197        _end_bound: &WindowFrameBound,
198    ) -> bool {
199        true
200    }
201
202    /// Extends the dialect's default rules for unparsing scalar functions.
203    /// This is useful for supporting application-specific UDFs or custom engine extensions.
204    fn with_custom_scalar_overrides(
205        self,
206        _handlers: Vec<(&str, ScalarFnToSqlHandler)>,
207    ) -> Self
208    where
209        Self: Sized,
210    {
211        unimplemented!("Custom scalar overrides are not supported by this dialect yet");
212    }
213
214    /// Allow to unparse a qualified column with a full qualified name
215    /// (e.g. catalog_name.schema_name.table_name.column_name)
216    /// Otherwise, the column will be unparsed with only the table name and column name
217    /// (e.g. table_name.column_name)
218    fn full_qualified_col(&self) -> bool {
219        false
220    }
221
222    /// Allow to unparse the unnest plan as [ast::TableFactor::UNNEST].
223    ///
224    /// Some dialects like BigQuery require UNNEST to be used in the FROM clause but
225    /// the LogicalPlan planner always puts UNNEST in the SELECT clause. This flag allows
226    /// to unparse the UNNEST plan as [ast::TableFactor::UNNEST] instead of a subquery.
227    fn unnest_as_table_factor(&self) -> bool {
228        false
229    }
230
231    /// Unparse the unnest plan as `LATERAL FLATTEN(INPUT => expr, ...)`.
232    ///
233    /// Snowflake uses FLATTEN as a table function instead of the SQL-standard UNNEST.
234    /// When this returns `true`, the unparser emits
235    /// `LATERAL FLATTEN(INPUT => <col>, OUTER => <bool>)` in the FROM clause.
236    fn unnest_as_lateral_flatten(&self) -> bool {
237        false
238    }
239
240    /// Allows the dialect to override column alias unparsing if the dialect has specific rules.
241    /// Returns None if the default unparsing should be used, or Some(String) if there is
242    /// a custom implementation for the alias.
243    fn col_alias_overrides(&self, _alias: &str) -> Result<Option<String>> {
244        Ok(None)
245    }
246
247    /// Allows the dialect to support the QUALIFY clause
248    ///
249    /// Some dialects, like Postgres, do not support the QUALIFY clause
250    fn supports_qualify(&self) -> bool {
251        true
252    }
253
254    /// Allows the dialect to override logic of formatting datetime with tz into string.
255    fn timestamp_with_tz_to_string(&self, dt: DateTime<Tz>, _unit: TimeUnit) -> String {
256        dt.to_rfc3339()
257    }
258
259    /// Whether the dialect supports an empty select list such as `SELECT FROM table`.
260    ///
261    /// An empty select list returns rows without any column data, which is useful for:
262    /// - Counting rows: `SELECT FROM users WHERE active = true` (combined with `COUNT(*)`)
263    /// - Testing row existence without retrieving column data
264    /// - Performance optimization when only row counts or existence checks are needed
265    ///
266    /// # Default
267    ///
268    /// Returns `false` for maximum compatibility across SQL dialects. When `false`,
269    /// the unparser falls back to `SELECT 1 FROM table`.
270    ///
271    /// # Implementation Note
272    ///
273    /// Specific dialects should override this method to return `true` if they support
274    /// the empty select list syntax (e.g., PostgreSQL).
275    ///
276    /// # Example SQL Output
277    ///
278    /// ```sql
279    /// -- When supported:
280    /// SELECT FROM users WHERE active = true;
281    ///
282    /// -- Fallback when unsupported:
283    /// SELECT 1 FROM users WHERE active = true;
284    /// ```
285    fn supports_empty_select_list(&self) -> bool {
286        false
287    }
288
289    /// Override the default string literal unparsing.
290    ///
291    /// Returns `Some(ast::Expr)` to replace the default single-quoted string,
292    /// or `None` to use the default behavior.
293    ///
294    /// For example, MSSQL requires non-ASCII strings to use national string
295    /// literal syntax (`N'datafusion資料融合'`).
296    fn string_literal_to_sql(&self, _s: &str) -> Option<ast::Expr> {
297        None
298    }
299}
300
301/// `IntervalStyle` to use for unparsing
302///
303/// <https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-INTERVAL-INPUT>
304/// different DBMS follows different standards, popular ones are:
305/// postgres_verbose: '2 years 15 months 100 weeks 99 hours 123456789 milliseconds' which is
306/// compatible with arrow display format, as well as duckdb
307/// sql standard format is '1-2' for year-month, or '1 10:10:10.123456' for day-time
308/// <https://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt>
309#[derive(Clone, Copy)]
310pub enum IntervalStyle {
311    PostgresVerbose,
312    SQLStandard,
313    MySQL,
314}
315
316/// Datetime subfield extraction style for unparsing
317///
318/// `<https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT>`
319/// Different DBMSs follow different standards; popular ones are:
320/// date_part('YEAR', date '2001-02-16')
321/// EXTRACT(YEAR from date '2001-02-16')
322/// Some DBMSs, like Postgres, support both, whereas others like MySQL require EXTRACT.
323#[derive(Clone, Copy, PartialEq)]
324pub enum DateFieldExtractStyle {
325    DatePart,
326    Extract,
327    Strftime,
328}
329
330/// `CharacterLengthStyle` to use for unparsing
331///
332/// Different DBMSs uses different names for function calculating the number of characters in the string
333/// `Length` style uses length(x)
334/// `SQLStandard` style uses character_length(x)
335#[derive(Clone, Copy, PartialEq)]
336pub enum CharacterLengthStyle {
337    Length,
338    CharacterLength,
339}
340
341/// `DistinctFromStyle` to use for unparsing `IsDistinctFrom` and `IsNotDistinctFrom` operators
342#[derive(Clone, Copy, PartialEq)]
343pub enum DistinctFromStyle {
344    /// DBMS supports `IS (NOT) DISTINCT FROM`
345    FullText,
346    /// DBMS supports equivalent operations via `<=>` and `NOT <=>`
347    Spaceship,
348}
349
350pub struct DefaultDialect {}
351
352impl Dialect for DefaultDialect {
353    fn identifier_quote_style(&self, identifier: &str) -> Option<char> {
354        let identifier_regex = Regex::new(r"^[a-zA-Z_][a-zA-Z0-9_]*$").unwrap();
355        let id_upper = identifier.to_uppercase();
356        // Special case ignore "ID", see https://github.com/sqlparser-rs/sqlparser-rs/issues/1382
357        // ID is a keyword in ClickHouse, but we don't want to quote it when unparsing SQL here
358        // Also quote identifiers with uppercase letters since unquoted identifiers are
359        // normalized to lowercase by the SQL parser, which would break case-sensitive schemas
360        let needs_quote = (id_upper != "ID" && ALL_KEYWORDS.contains(&id_upper.as_str()))
361            || !identifier_regex.is_match(identifier)
362            || identifier.chars().any(|c| c.is_ascii_uppercase());
363        if needs_quote { Some('"') } else { None }
364    }
365}
366
367pub struct PostgreSqlDialect {}
368
369impl Dialect for PostgreSqlDialect {
370    fn use_array_keyword_for_array_literals(&self) -> bool {
371        true
372    }
373
374    fn supports_qualify(&self) -> bool {
375        false
376    }
377
378    fn requires_derived_table_alias(&self) -> bool {
379        true
380    }
381
382    fn supports_empty_select_list(&self) -> bool {
383        true
384    }
385
386    fn identifier_quote_style(&self, _: &str) -> Option<char> {
387        Some('"')
388    }
389
390    fn interval_style(&self) -> IntervalStyle {
391        IntervalStyle::PostgresVerbose
392    }
393
394    fn float64_ast_dtype(&self) -> ast::DataType {
395        ast::DataType::DoublePrecision
396    }
397
398    fn int8_cast_dtype(&self) -> ast::DataType {
399        ast::DataType::SmallInt(None)
400    }
401
402    fn distinct_from_style(&self) -> DistinctFromStyle {
403        DistinctFromStyle::FullText
404    }
405
406    fn scalar_function_to_sql_overrides(
407        &self,
408        unparser: &Unparser,
409        func_name: &str,
410        args: &[Expr],
411    ) -> Result<Option<ast::Expr>> {
412        if func_name == "array_has" {
413            return self.array_has_to_sql_any(unparser, args);
414        }
415
416        if func_name == "round" {
417            return Ok(Some(
418                self.round_to_sql_enforce_numeric(unparser, func_name, args)?,
419            ));
420        }
421
422        Ok(None)
423    }
424}
425
426impl PostgreSqlDialect {
427    fn array_has_to_sql_any(
428        &self,
429        unparser: &Unparser,
430        args: &[Expr],
431    ) -> Result<Option<ast::Expr>> {
432        let [haystack, needle] = args else {
433            return internal_err!("array_has expected 2 arguments, got {}", args.len());
434        };
435
436        Ok(Some(ast::Expr::AnyOp {
437            // Recurse through the annotated entry point so the stack-growth
438            // protection engages on nested arguments; see issue #23056.
439            left: Box::new(unparser.expr_to_sql_with_nesting(needle)?),
440            compare_op: BinaryOperator::Eq,
441            right: Box::new(unparser.expr_to_sql_with_nesting(haystack)?),
442            is_some: false,
443        }))
444    }
445
446    fn round_to_sql_enforce_numeric(
447        &self,
448        unparser: &Unparser,
449        func_name: &str,
450        args: &[Expr],
451    ) -> Result<ast::Expr> {
452        let mut args = unparser.function_args_to_sql(args)?;
453
454        // Enforce the first argument to be Numeric
455        if let Some(ast::FunctionArg::Unnamed(ast::FunctionArgExpr::Expr(expr))) =
456            args.first_mut()
457        {
458            if let ast::Expr::Cast { data_type, .. } = expr {
459                // Don't create an additional cast wrapper if we can update the existing one
460                *data_type = ast::DataType::Numeric(ast::ExactNumberInfo::None);
461            } else {
462                // Wrap the expression in a new cast
463                *expr = ast::Expr::Cast {
464                    kind: ast::CastKind::Cast,
465                    expr: Box::new(expr.clone()),
466                    data_type: ast::DataType::Numeric(ast::ExactNumberInfo::None),
467                    array: false,
468                    format: None,
469                };
470            }
471        }
472
473        Ok(ast::Expr::Function(Function {
474            name: ObjectName::from(vec![Ident {
475                value: func_name.to_string(),
476                quote_style: None,
477                span: Span::empty(),
478            }]),
479            args: ast::FunctionArguments::List(ast::FunctionArgumentList {
480                duplicate_treatment: None,
481                args,
482                clauses: vec![],
483            }),
484            filter: None,
485            null_treatment: None,
486            over: None,
487            within_group: vec![],
488            parameters: ast::FunctionArguments::None,
489            uses_odbc_syntax: false,
490        }))
491    }
492}
493
494#[derive(Default)]
495pub struct DuckDBDialect {
496    custom_scalar_fn_overrides: HashMap<String, ScalarFnToSqlHandler>,
497}
498
499impl DuckDBDialect {
500    #[must_use]
501    pub fn new() -> Self {
502        Self {
503            custom_scalar_fn_overrides: HashMap::new(),
504        }
505    }
506}
507
508impl Dialect for DuckDBDialect {
509    fn identifier_quote_style(&self, _: &str) -> Option<char> {
510        Some('"')
511    }
512
513    fn character_length_style(&self) -> CharacterLengthStyle {
514        CharacterLengthStyle::Length
515    }
516
517    fn division_operator(&self) -> BinaryOperator {
518        BinaryOperator::DuckIntegerDivide
519    }
520
521    fn with_custom_scalar_overrides(
522        mut self,
523        handlers: Vec<(&str, ScalarFnToSqlHandler)>,
524    ) -> Self {
525        for (func_name, handler) in handlers {
526            self.custom_scalar_fn_overrides
527                .insert(func_name.to_string(), handler);
528        }
529        self
530    }
531
532    fn scalar_function_to_sql_overrides(
533        &self,
534        unparser: &Unparser,
535        func_name: &str,
536        args: &[Expr],
537    ) -> Result<Option<ast::Expr>> {
538        if let Some(handler) = self.custom_scalar_fn_overrides.get(func_name) {
539            return handler(unparser, args);
540        }
541
542        if func_name == "character_length" {
543            return character_length_to_sql(
544                unparser,
545                self.character_length_style(),
546                args,
547            );
548        }
549
550        Ok(None)
551    }
552
553    fn distinct_from_style(&self) -> DistinctFromStyle {
554        DistinctFromStyle::FullText
555    }
556}
557
558pub struct MySqlDialect {}
559
560impl Dialect for MySqlDialect {
561    fn supports_qualify(&self) -> bool {
562        false
563    }
564
565    fn identifier_quote_style(&self, _: &str) -> Option<char> {
566        Some('`')
567    }
568
569    fn supports_nulls_first_in_sort(&self) -> bool {
570        false
571    }
572
573    fn interval_style(&self) -> IntervalStyle {
574        IntervalStyle::MySQL
575    }
576
577    fn utf8_cast_dtype(&self) -> ast::DataType {
578        ast::DataType::Char(None)
579    }
580
581    fn large_utf8_cast_dtype(&self) -> ast::DataType {
582        ast::DataType::Char(None)
583    }
584
585    fn date_field_extract_style(&self) -> DateFieldExtractStyle {
586        DateFieldExtractStyle::Extract
587    }
588
589    fn distinct_from_style(&self) -> DistinctFromStyle {
590        DistinctFromStyle::Spaceship
591    }
592
593    fn int64_cast_dtype(&self) -> ast::DataType {
594        ast::DataType::Custom(ObjectName::from(vec![Ident::new("SIGNED")]), vec![])
595    }
596
597    fn int32_cast_dtype(&self) -> ast::DataType {
598        ast::DataType::Custom(ObjectName::from(vec![Ident::new("SIGNED")]), vec![])
599    }
600
601    fn timestamp_cast_dtype(
602        &self,
603        _time_unit: &TimeUnit,
604        _tz: &Option<Arc<str>>,
605    ) -> ast::DataType {
606        ast::DataType::Datetime(None)
607    }
608
609    fn requires_derived_table_alias(&self) -> bool {
610        true
611    }
612
613    fn scalar_function_to_sql_overrides(
614        &self,
615        unparser: &Unparser,
616        func_name: &str,
617        args: &[Expr],
618    ) -> Result<Option<ast::Expr>> {
619        if func_name == "date_part" {
620            return date_part_to_sql(unparser, self.date_field_extract_style(), args);
621        }
622
623        Ok(None)
624    }
625}
626
627pub struct SqliteDialect {}
628
629impl Dialect for SqliteDialect {
630    fn supports_qualify(&self) -> bool {
631        false
632    }
633
634    fn identifier_quote_style(&self, _: &str) -> Option<char> {
635        Some('`')
636    }
637
638    fn date_field_extract_style(&self) -> DateFieldExtractStyle {
639        DateFieldExtractStyle::Strftime
640    }
641
642    fn date32_cast_dtype(&self) -> ast::DataType {
643        ast::DataType::Text
644    }
645
646    fn character_length_style(&self) -> CharacterLengthStyle {
647        CharacterLengthStyle::Length
648    }
649
650    fn distinct_from_style(&self) -> DistinctFromStyle {
651        DistinctFromStyle::FullText
652    }
653
654    fn supports_column_alias_in_table_alias(&self) -> bool {
655        false
656    }
657
658    fn timestamp_cast_dtype(
659        &self,
660        _time_unit: &TimeUnit,
661        _tz: &Option<Arc<str>>,
662    ) -> ast::DataType {
663        ast::DataType::Text
664    }
665
666    fn scalar_function_to_sql_overrides(
667        &self,
668        unparser: &Unparser,
669        func_name: &str,
670        args: &[Expr],
671    ) -> Result<Option<ast::Expr>> {
672        match func_name {
673            "date_part" => {
674                date_part_to_sql(unparser, self.date_field_extract_style(), args)
675            }
676            "character_length" => {
677                character_length_to_sql(unparser, self.character_length_style(), args)
678            }
679            "from_unixtime" => sqlite_from_unixtime_to_sql(unparser, args),
680            "date_trunc" => sqlite_date_trunc_to_sql(unparser, args),
681            _ => Ok(None),
682        }
683    }
684}
685
686#[derive(Default)]
687pub struct BigQueryDialect {}
688
689impl Dialect for BigQueryDialect {
690    fn identifier_quote_style(&self, _: &str) -> Option<char> {
691        Some('`')
692    }
693
694    fn col_alias_overrides(&self, alias: &str) -> Result<Option<String>> {
695        // Check if alias contains any special characters not supported by BigQuery col names
696        // https://cloud.google.com/bigquery/docs/schemas#flexible-column-names
697        let special_chars: [char; 20] = [
698            '!', '"', '$', '(', ')', '*', ',', '.', '/', ';', '?', '@', '[', '\\', ']',
699            '^', '`', '{', '}', '~',
700        ];
701
702        if alias.chars().any(|c| special_chars.contains(&c)) {
703            let mut encoded_name = String::new();
704            for c in alias.chars() {
705                if special_chars.contains(&c) {
706                    encoded_name.push_str(&format!("_{}", c as u32));
707                } else {
708                    encoded_name.push(c);
709                }
710            }
711            Ok(Some(encoded_name))
712        } else {
713            Ok(Some(alias.to_string()))
714        }
715    }
716
717    fn unnest_as_table_factor(&self) -> bool {
718        true
719    }
720
721    fn supports_column_alias_in_table_alias(&self) -> bool {
722        false
723    }
724
725    fn float64_ast_dtype(&self) -> ast::DataType {
726        ast::DataType::Float64
727    }
728
729    fn utf8_cast_dtype(&self) -> ast::DataType {
730        ast::DataType::String(None)
731    }
732
733    fn large_utf8_cast_dtype(&self) -> ast::DataType {
734        ast::DataType::String(None)
735    }
736
737    fn timestamp_cast_dtype(
738        &self,
739        _time_unit: &TimeUnit,
740        _tz: &Option<Arc<str>>,
741    ) -> ast::DataType {
742        ast::DataType::Timestamp(None, TimezoneInfo::None)
743    }
744
745    fn date_field_extract_style(&self) -> DateFieldExtractStyle {
746        DateFieldExtractStyle::Extract
747    }
748
749    fn interval_style(&self) -> IntervalStyle {
750        IntervalStyle::SQLStandard
751    }
752
753    fn scalar_function_to_sql_overrides(
754        &self,
755        unparser: &Unparser,
756        func_name: &str,
757        args: &[Expr],
758    ) -> Result<Option<ast::Expr>> {
759        if func_name == "date_part" {
760            return date_part_to_sql(unparser, self.date_field_extract_style(), args);
761        }
762
763        Ok(None)
764    }
765}
766
767impl BigQueryDialect {
768    #[must_use]
769    pub fn new() -> Self {
770        Self {}
771    }
772}
773
774/// Dialect for Snowflake SQL.
775///
776/// Key differences from the default dialect:
777/// - Uses double-quote identifier quoting
778/// - Supports `NULLS FIRST`/`NULLS LAST` in `ORDER BY`
779/// - Does not support empty select lists (`SELECT FROM t`)
780/// - Does not support column aliases in table alias definitions
781///   (Snowflake accepts the syntax but silently ignores the renames in join contexts)
782/// - Unparses `UNNEST` plans as `LATERAL FLATTEN(INPUT => expr, ...)`
783pub struct SnowflakeDialect {}
784
785#[expect(clippy::new_without_default)]
786impl SnowflakeDialect {
787    #[must_use]
788    pub fn new() -> Self {
789        Self {}
790    }
791}
792
793impl Dialect for SnowflakeDialect {
794    fn identifier_quote_style(&self, _: &str) -> Option<char> {
795        Some('"')
796    }
797
798    fn supports_nulls_first_in_sort(&self) -> bool {
799        true
800    }
801
802    fn supports_empty_select_list(&self) -> bool {
803        false
804    }
805
806    fn supports_column_alias_in_table_alias(&self) -> bool {
807        false
808    }
809
810    fn timestamp_cast_dtype(
811        &self,
812        _time_unit: &TimeUnit,
813        tz: &Option<Arc<str>>,
814    ) -> ast::DataType {
815        if tz.is_some() {
816            ast::DataType::Timestamp(None, TimezoneInfo::WithTimeZone)
817        } else {
818            ast::DataType::Timestamp(None, TimezoneInfo::None)
819        }
820    }
821
822    fn unnest_as_lateral_flatten(&self) -> bool {
823        true
824    }
825}
826
827pub struct CustomDialect {
828    identifier_quote_style: Option<char>,
829    supports_nulls_first_in_sort: bool,
830    use_timestamp_for_date64: bool,
831    interval_style: IntervalStyle,
832    float64_ast_dtype: ast::DataType,
833    utf8_cast_dtype: ast::DataType,
834    large_utf8_cast_dtype: ast::DataType,
835    date_field_extract_style: DateFieldExtractStyle,
836    character_length_style: CharacterLengthStyle,
837    int8_cast_dtype: ast::DataType,
838    int64_cast_dtype: ast::DataType,
839    int32_cast_dtype: ast::DataType,
840    timestamp_cast_dtype: ast::DataType,
841    timestamp_tz_cast_dtype: ast::DataType,
842    date32_cast_dtype: ast::DataType,
843    supports_column_alias_in_table_alias: bool,
844    requires_derived_table_alias: bool,
845    division_operator: BinaryOperator,
846    window_func_support_window_frame: bool,
847    full_qualified_col: bool,
848    unnest_as_table_factor: bool,
849    unnest_as_lateral_flatten: bool,
850}
851
852impl Default for CustomDialect {
853    fn default() -> Self {
854        Self {
855            identifier_quote_style: None,
856            supports_nulls_first_in_sort: true,
857            use_timestamp_for_date64: false,
858            interval_style: IntervalStyle::SQLStandard,
859            float64_ast_dtype: ast::DataType::Double(ast::ExactNumberInfo::None),
860            utf8_cast_dtype: ast::DataType::Varchar(None),
861            large_utf8_cast_dtype: ast::DataType::Text,
862            date_field_extract_style: DateFieldExtractStyle::DatePart,
863            character_length_style: CharacterLengthStyle::CharacterLength,
864            int8_cast_dtype: ast::DataType::TinyInt(None),
865            int64_cast_dtype: ast::DataType::BigInt(None),
866            int32_cast_dtype: ast::DataType::Integer(None),
867            timestamp_cast_dtype: ast::DataType::Timestamp(None, TimezoneInfo::None),
868            timestamp_tz_cast_dtype: ast::DataType::Timestamp(
869                None,
870                TimezoneInfo::WithTimeZone,
871            ),
872            date32_cast_dtype: ast::DataType::Date,
873            supports_column_alias_in_table_alias: true,
874            requires_derived_table_alias: false,
875            division_operator: BinaryOperator::Divide,
876            window_func_support_window_frame: true,
877            full_qualified_col: false,
878            unnest_as_table_factor: false,
879            unnest_as_lateral_flatten: false,
880        }
881    }
882}
883
884impl Dialect for CustomDialect {
885    fn identifier_quote_style(&self, _: &str) -> Option<char> {
886        self.identifier_quote_style
887    }
888
889    fn supports_nulls_first_in_sort(&self) -> bool {
890        self.supports_nulls_first_in_sort
891    }
892
893    fn use_timestamp_for_date64(&self) -> bool {
894        self.use_timestamp_for_date64
895    }
896
897    fn interval_style(&self) -> IntervalStyle {
898        self.interval_style
899    }
900
901    fn float64_ast_dtype(&self) -> ast::DataType {
902        self.float64_ast_dtype.clone()
903    }
904
905    fn utf8_cast_dtype(&self) -> ast::DataType {
906        self.utf8_cast_dtype.clone()
907    }
908
909    fn large_utf8_cast_dtype(&self) -> ast::DataType {
910        self.large_utf8_cast_dtype.clone()
911    }
912
913    fn date_field_extract_style(&self) -> DateFieldExtractStyle {
914        self.date_field_extract_style
915    }
916
917    fn character_length_style(&self) -> CharacterLengthStyle {
918        self.character_length_style
919    }
920
921    fn int64_cast_dtype(&self) -> ast::DataType {
922        self.int64_cast_dtype.clone()
923    }
924
925    fn int8_cast_dtype(&self) -> ast::DataType {
926        self.int8_cast_dtype.clone()
927    }
928
929    fn int32_cast_dtype(&self) -> ast::DataType {
930        self.int32_cast_dtype.clone()
931    }
932
933    fn timestamp_cast_dtype(
934        &self,
935        _time_unit: &TimeUnit,
936        tz: &Option<Arc<str>>,
937    ) -> ast::DataType {
938        if tz.is_some() {
939            self.timestamp_tz_cast_dtype.clone()
940        } else {
941            self.timestamp_cast_dtype.clone()
942        }
943    }
944
945    fn date32_cast_dtype(&self) -> ast::DataType {
946        self.date32_cast_dtype.clone()
947    }
948
949    fn supports_column_alias_in_table_alias(&self) -> bool {
950        self.supports_column_alias_in_table_alias
951    }
952
953    fn scalar_function_to_sql_overrides(
954        &self,
955        unparser: &Unparser,
956        func_name: &str,
957        args: &[Expr],
958    ) -> Result<Option<ast::Expr>> {
959        match func_name {
960            "date_part" => {
961                date_part_to_sql(unparser, self.date_field_extract_style(), args)
962            }
963            "character_length" => {
964                character_length_to_sql(unparser, self.character_length_style(), args)
965            }
966            _ => Ok(None),
967        }
968    }
969
970    fn requires_derived_table_alias(&self) -> bool {
971        self.requires_derived_table_alias
972    }
973
974    fn division_operator(&self) -> BinaryOperator {
975        self.division_operator.clone()
976    }
977
978    fn window_func_support_window_frame(
979        &self,
980        _func_name: &str,
981        _start_bound: &WindowFrameBound,
982        _end_bound: &WindowFrameBound,
983    ) -> bool {
984        self.window_func_support_window_frame
985    }
986
987    fn full_qualified_col(&self) -> bool {
988        self.full_qualified_col
989    }
990
991    fn unnest_as_table_factor(&self) -> bool {
992        self.unnest_as_table_factor
993    }
994
995    fn unnest_as_lateral_flatten(&self) -> bool {
996        self.unnest_as_lateral_flatten
997    }
998}
999
1000/// `CustomDialectBuilder` to build `CustomDialect` using builder pattern
1001///
1002///
1003/// # Examples
1004///
1005/// Building a custom dialect with all default options set in CustomDialectBuilder::new()
1006/// but with `use_timestamp_for_date64` overridden to `true`
1007///
1008/// ```
1009/// use datafusion_sql::unparser::dialect::CustomDialectBuilder;
1010/// let dialect = CustomDialectBuilder::new()
1011///     .with_use_timestamp_for_date64(true)
1012///     .build();
1013/// ```
1014pub struct CustomDialectBuilder {
1015    identifier_quote_style: Option<char>,
1016    supports_nulls_first_in_sort: bool,
1017    use_timestamp_for_date64: bool,
1018    interval_style: IntervalStyle,
1019    float64_ast_dtype: ast::DataType,
1020    utf8_cast_dtype: ast::DataType,
1021    large_utf8_cast_dtype: ast::DataType,
1022    date_field_extract_style: DateFieldExtractStyle,
1023    character_length_style: CharacterLengthStyle,
1024    int8_cast_dtype: ast::DataType,
1025    int64_cast_dtype: ast::DataType,
1026    int32_cast_dtype: ast::DataType,
1027    timestamp_cast_dtype: ast::DataType,
1028    timestamp_tz_cast_dtype: ast::DataType,
1029    date32_cast_dtype: ast::DataType,
1030    supports_column_alias_in_table_alias: bool,
1031    requires_derived_table_alias: bool,
1032    division_operator: BinaryOperator,
1033    window_func_support_window_frame: bool,
1034    full_qualified_col: bool,
1035    unnest_as_table_factor: bool,
1036    unnest_as_lateral_flatten: bool,
1037}
1038
1039impl Default for CustomDialectBuilder {
1040    fn default() -> Self {
1041        Self::new()
1042    }
1043}
1044
1045impl CustomDialectBuilder {
1046    pub fn new() -> Self {
1047        Self {
1048            identifier_quote_style: None,
1049            supports_nulls_first_in_sort: true,
1050            use_timestamp_for_date64: false,
1051            interval_style: IntervalStyle::PostgresVerbose,
1052            float64_ast_dtype: ast::DataType::Double(ast::ExactNumberInfo::None),
1053            utf8_cast_dtype: ast::DataType::Varchar(None),
1054            large_utf8_cast_dtype: ast::DataType::Text,
1055            date_field_extract_style: DateFieldExtractStyle::DatePart,
1056            character_length_style: CharacterLengthStyle::CharacterLength,
1057            int8_cast_dtype: ast::DataType::TinyInt(None),
1058            int64_cast_dtype: ast::DataType::BigInt(None),
1059            int32_cast_dtype: ast::DataType::Integer(None),
1060            timestamp_cast_dtype: ast::DataType::Timestamp(None, TimezoneInfo::None),
1061            timestamp_tz_cast_dtype: ast::DataType::Timestamp(
1062                None,
1063                TimezoneInfo::WithTimeZone,
1064            ),
1065            date32_cast_dtype: ast::DataType::Date,
1066            supports_column_alias_in_table_alias: true,
1067            requires_derived_table_alias: false,
1068            division_operator: BinaryOperator::Divide,
1069            window_func_support_window_frame: true,
1070            full_qualified_col: false,
1071            unnest_as_table_factor: false,
1072            unnest_as_lateral_flatten: false,
1073        }
1074    }
1075
1076    pub fn build(self) -> CustomDialect {
1077        CustomDialect {
1078            identifier_quote_style: self.identifier_quote_style,
1079            supports_nulls_first_in_sort: self.supports_nulls_first_in_sort,
1080            use_timestamp_for_date64: self.use_timestamp_for_date64,
1081            interval_style: self.interval_style,
1082            float64_ast_dtype: self.float64_ast_dtype,
1083            utf8_cast_dtype: self.utf8_cast_dtype,
1084            large_utf8_cast_dtype: self.large_utf8_cast_dtype,
1085            date_field_extract_style: self.date_field_extract_style,
1086            character_length_style: self.character_length_style,
1087            int8_cast_dtype: self.int8_cast_dtype,
1088            int64_cast_dtype: self.int64_cast_dtype,
1089            int32_cast_dtype: self.int32_cast_dtype,
1090            timestamp_cast_dtype: self.timestamp_cast_dtype,
1091            timestamp_tz_cast_dtype: self.timestamp_tz_cast_dtype,
1092            date32_cast_dtype: self.date32_cast_dtype,
1093            supports_column_alias_in_table_alias: self
1094                .supports_column_alias_in_table_alias,
1095            requires_derived_table_alias: self.requires_derived_table_alias,
1096            division_operator: self.division_operator,
1097            window_func_support_window_frame: self.window_func_support_window_frame,
1098            full_qualified_col: self.full_qualified_col,
1099            unnest_as_table_factor: self.unnest_as_table_factor,
1100            unnest_as_lateral_flatten: self.unnest_as_lateral_flatten,
1101        }
1102    }
1103
1104    /// Customize the dialect with a specific identifier quote style, e.g. '`', '"'
1105    pub fn with_identifier_quote_style(mut self, identifier_quote_style: char) -> Self {
1106        self.identifier_quote_style = Some(identifier_quote_style);
1107        self
1108    }
1109
1110    /// Customize the dialect to support `NULLS FIRST` in `ORDER BY` clauses
1111    pub fn with_supports_nulls_first_in_sort(
1112        mut self,
1113        supports_nulls_first_in_sort: bool,
1114    ) -> Self {
1115        self.supports_nulls_first_in_sort = supports_nulls_first_in_sort;
1116        self
1117    }
1118
1119    /// Customize the dialect to uses TIMESTAMP when casting Date64 rather than DATETIME
1120    pub fn with_use_timestamp_for_date64(
1121        mut self,
1122        use_timestamp_for_date64: bool,
1123    ) -> Self {
1124        self.use_timestamp_for_date64 = use_timestamp_for_date64;
1125        self
1126    }
1127
1128    /// Customize the dialect with a specific interval style listed in `IntervalStyle`
1129    pub fn with_interval_style(mut self, interval_style: IntervalStyle) -> Self {
1130        self.interval_style = interval_style;
1131        self
1132    }
1133
1134    /// Customize the dialect with a specific character_length_style listed in `CharacterLengthStyle`
1135    pub fn with_character_length_style(
1136        mut self,
1137        character_length_style: CharacterLengthStyle,
1138    ) -> Self {
1139        self.character_length_style = character_length_style;
1140        self
1141    }
1142
1143    /// Customize the dialect with a specific SQL type for Int8 casting: TinyInt, SmallInt, etc.
1144    pub fn with_int8_cast_dtype(mut self, int8_cast_dtype: ast::DataType) -> Self {
1145        self.int8_cast_dtype = int8_cast_dtype;
1146        self
1147    }
1148
1149    /// Customize the dialect with a specific SQL type for Float64 casting: DOUBLE, DOUBLE PRECISION, etc.
1150    pub fn with_float64_ast_dtype(mut self, float64_ast_dtype: ast::DataType) -> Self {
1151        self.float64_ast_dtype = float64_ast_dtype;
1152        self
1153    }
1154
1155    /// Customize the dialect with a specific SQL type for Utf8 casting: VARCHAR, CHAR, etc.
1156    pub fn with_utf8_cast_dtype(mut self, utf8_cast_dtype: ast::DataType) -> Self {
1157        self.utf8_cast_dtype = utf8_cast_dtype;
1158        self
1159    }
1160
1161    /// Customize the dialect with a specific SQL type for LargeUtf8 casting: TEXT, CHAR, etc.
1162    pub fn with_large_utf8_cast_dtype(
1163        mut self,
1164        large_utf8_cast_dtype: ast::DataType,
1165    ) -> Self {
1166        self.large_utf8_cast_dtype = large_utf8_cast_dtype;
1167        self
1168    }
1169
1170    /// Customize the dialect with a specific date field extract style listed in `DateFieldExtractStyle`
1171    pub fn with_date_field_extract_style(
1172        mut self,
1173        date_field_extract_style: DateFieldExtractStyle,
1174    ) -> Self {
1175        self.date_field_extract_style = date_field_extract_style;
1176        self
1177    }
1178
1179    /// Customize the dialect with a specific SQL type for Int64 casting: BigInt, SIGNED, etc.
1180    pub fn with_int64_cast_dtype(mut self, int64_cast_dtype: ast::DataType) -> Self {
1181        self.int64_cast_dtype = int64_cast_dtype;
1182        self
1183    }
1184
1185    /// Customize the dialect with a specific SQL type for Int32 casting: Integer, SIGNED, etc.
1186    pub fn with_int32_cast_dtype(mut self, int32_cast_dtype: ast::DataType) -> Self {
1187        self.int32_cast_dtype = int32_cast_dtype;
1188        self
1189    }
1190
1191    /// Customize the dialect with a specific SQL type for Timestamp casting: Timestamp, Datetime, etc.
1192    pub fn with_timestamp_cast_dtype(
1193        mut self,
1194        timestamp_cast_dtype: ast::DataType,
1195        timestamp_tz_cast_dtype: ast::DataType,
1196    ) -> Self {
1197        self.timestamp_cast_dtype = timestamp_cast_dtype;
1198        self.timestamp_tz_cast_dtype = timestamp_tz_cast_dtype;
1199        self
1200    }
1201
1202    pub fn with_date32_cast_dtype(mut self, date32_cast_dtype: ast::DataType) -> Self {
1203        self.date32_cast_dtype = date32_cast_dtype;
1204        self
1205    }
1206
1207    /// Customize the dialect to support column aliases as part of alias table definition
1208    pub fn with_supports_column_alias_in_table_alias(
1209        mut self,
1210        supports_column_alias_in_table_alias: bool,
1211    ) -> Self {
1212        self.supports_column_alias_in_table_alias = supports_column_alias_in_table_alias;
1213        self
1214    }
1215
1216    pub fn with_requires_derived_table_alias(
1217        mut self,
1218        requires_derived_table_alias: bool,
1219    ) -> Self {
1220        self.requires_derived_table_alias = requires_derived_table_alias;
1221        self
1222    }
1223
1224    pub fn with_division_operator(mut self, division_operator: BinaryOperator) -> Self {
1225        self.division_operator = division_operator;
1226        self
1227    }
1228
1229    pub fn with_window_func_support_window_frame(
1230        mut self,
1231        window_func_support_window_frame: bool,
1232    ) -> Self {
1233        self.window_func_support_window_frame = window_func_support_window_frame;
1234        self
1235    }
1236
1237    /// Customize the dialect to allow full qualified column names
1238    pub fn with_full_qualified_col(mut self, full_qualified_col: bool) -> Self {
1239        self.full_qualified_col = full_qualified_col;
1240        self
1241    }
1242
1243    pub fn with_unnest_as_table_factor(mut self, unnest_as_table_factor: bool) -> Self {
1244        self.unnest_as_table_factor = unnest_as_table_factor;
1245        self
1246    }
1247
1248    pub fn with_unnest_as_lateral_flatten(
1249        mut self,
1250        unnest_as_lateral_flatten: bool,
1251    ) -> Self {
1252        self.unnest_as_lateral_flatten = unnest_as_lateral_flatten;
1253        self
1254    }
1255}
datafusion_sql/unparser/dialect.rs

datafusion_sql/unparser/
dialect.rs
