sqlparser/ast/
query.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
18#[cfg(not(feature = "std"))]
19use alloc::{boxed::Box, vec::Vec};
20
21use helpers::attached_token::AttachedToken;
22#[cfg(feature = "serde")]
23use serde::{Deserialize, Serialize};
24
25#[cfg(feature = "visitor")]
26use sqlparser_derive::{Visit, VisitMut};
27
28use crate::{
29    ast::*,
30    display_utils::{indented_list, SpaceOrNewline},
31    tokenizer::{Token, TokenWithSpan},
32};
33
34/// The most complete variant of a `SELECT` query expression, optionally
35/// including `WITH`, `UNION` / other set operations, and `ORDER BY`.
36#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
37#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
38#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
39#[cfg_attr(feature = "visitor", visit(with = "visit_query"))]
40pub struct Query {
41    /// WITH (common table expressions, or CTEs)
42    pub with: Option<With>,
43    /// SELECT or UNION / EXCEPT / INTERSECT
44    pub body: Box<SetExpr>,
45    /// ORDER BY
46    pub order_by: Option<OrderBy>,
47    /// `LIMIT ... OFFSET ... | LIMIT <offset>, <limit>`
48    pub limit_clause: Option<LimitClause>,
49    /// `FETCH { FIRST | NEXT } <N> [ PERCENT ] { ROW | ROWS } | { ONLY | WITH TIES }`
50    pub fetch: Option<Fetch>,
51    /// `FOR { UPDATE | SHARE } [ OF table_name ] [ SKIP LOCKED | NOWAIT ]`
52    pub locks: Vec<LockClause>,
53    /// `FOR XML { RAW | AUTO | EXPLICIT | PATH } [ , ELEMENTS ]`
54    /// `FOR JSON { AUTO | PATH } [ , INCLUDE_NULL_VALUES ]`
55    /// (MSSQL-specific)
56    pub for_clause: Option<ForClause>,
57    /// ClickHouse syntax: `SELECT * FROM t SETTINGS key1 = value1, key2 = value2`
58    ///
59    /// [ClickHouse](https://clickhouse.com/docs/en/sql-reference/statements/select#settings-in-select-query)
60    pub settings: Option<Vec<Setting>>,
61    /// `SELECT * FROM t FORMAT JSONCompact`
62    ///
63    /// [ClickHouse](https://clickhouse.com/docs/en/sql-reference/statements/select/format)
64    /// (ClickHouse-specific)
65    pub format_clause: Option<FormatClause>,
66
67    /// Pipe operator
68    pub pipe_operators: Vec<PipeOperator>,
69}
70
71impl fmt::Display for Query {
72    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
73        if let Some(ref with) = self.with {
74            with.fmt(f)?;
75            SpaceOrNewline.fmt(f)?;
76        }
77        self.body.fmt(f)?;
78        if let Some(ref order_by) = self.order_by {
79            f.write_str(" ")?;
80            order_by.fmt(f)?;
81        }
82
83        if let Some(ref limit_clause) = self.limit_clause {
84            limit_clause.fmt(f)?;
85        }
86        if let Some(ref settings) = self.settings {
87            f.write_str(" SETTINGS ")?;
88            display_comma_separated(settings).fmt(f)?;
89        }
90        if let Some(ref fetch) = self.fetch {
91            f.write_str(" ")?;
92            fetch.fmt(f)?;
93        }
94        if !self.locks.is_empty() {
95            f.write_str(" ")?;
96            display_separated(&self.locks, " ").fmt(f)?;
97        }
98        if let Some(ref for_clause) = self.for_clause {
99            f.write_str(" ")?;
100            for_clause.fmt(f)?;
101        }
102        if let Some(ref format) = self.format_clause {
103            f.write_str(" ")?;
104            format.fmt(f)?;
105        }
106        for pipe_operator in &self.pipe_operators {
107            f.write_str(" |> ")?;
108            pipe_operator.fmt(f)?;
109        }
110        Ok(())
111    }
112}
113
114/// Query syntax for ClickHouse ADD PROJECTION statement.
115/// Its syntax is similar to SELECT statement, but it is used to add a new projection to a table.
116/// Syntax is `SELECT <COLUMN LIST EXPR> [GROUP BY] [ORDER BY]`
117///
118/// [ClickHouse](https://clickhouse.com/docs/en/sql-reference/statements/alter/projection#add-projection)
119#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
120#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
121#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
122pub struct ProjectionSelect {
123    pub projection: Vec<SelectItem>,
124    pub order_by: Option<OrderBy>,
125    pub group_by: Option<GroupByExpr>,
126}
127
128impl fmt::Display for ProjectionSelect {
129    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
130        write!(f, "SELECT {}", display_comma_separated(&self.projection))?;
131        if let Some(ref group_by) = self.group_by {
132            write!(f, " {group_by}")?;
133        }
134        if let Some(ref order_by) = self.order_by {
135            write!(f, " {order_by}")?;
136        }
137        Ok(())
138    }
139}
140
141/// A node in a tree, representing a "query body" expression, roughly:
142/// `SELECT ... [ {UNION|EXCEPT|INTERSECT} SELECT ...]`
143#[allow(clippy::large_enum_variant)]
144#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
145#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
146#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
147pub enum SetExpr {
148    /// Restricted SELECT .. FROM .. HAVING (no ORDER BY or set operations)
149    Select(Box<Select>),
150    /// Parenthesized SELECT subquery, which may include more set operations
151    /// in its body and an optional ORDER BY / LIMIT.
152    Query(Box<Query>),
153    /// UNION/EXCEPT/INTERSECT of two queries
154    SetOperation {
155        op: SetOperator,
156        set_quantifier: SetQuantifier,
157        left: Box<SetExpr>,
158        right: Box<SetExpr>,
159    },
160    Values(Values),
161    Insert(Statement),
162    Update(Statement),
163    Delete(Statement),
164    Table(Box<Table>),
165}
166
167impl SetExpr {
168    /// If this `SetExpr` is a `SELECT`, returns the [`Select`].
169    pub fn as_select(&self) -> Option<&Select> {
170        if let Self::Select(select) = self {
171            Some(&**select)
172        } else {
173            None
174        }
175    }
176}
177
178impl fmt::Display for SetExpr {
179    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
180        match self {
181            SetExpr::Select(s) => s.fmt(f),
182            SetExpr::Query(q) => {
183                f.write_str("(")?;
184                q.fmt(f)?;
185                f.write_str(")")
186            }
187            SetExpr::Values(v) => v.fmt(f),
188            SetExpr::Insert(v) => v.fmt(f),
189            SetExpr::Update(v) => v.fmt(f),
190            SetExpr::Delete(v) => v.fmt(f),
191            SetExpr::Table(t) => t.fmt(f),
192            SetExpr::SetOperation {
193                left,
194                right,
195                op,
196                set_quantifier,
197            } => {
198                left.fmt(f)?;
199                SpaceOrNewline.fmt(f)?;
200                op.fmt(f)?;
201                match set_quantifier {
202                    SetQuantifier::All
203                    | SetQuantifier::Distinct
204                    | SetQuantifier::ByName
205                    | SetQuantifier::AllByName
206                    | SetQuantifier::DistinctByName => {
207                        f.write_str(" ")?;
208                        set_quantifier.fmt(f)?;
209                    }
210                    SetQuantifier::None => {}
211                }
212                SpaceOrNewline.fmt(f)?;
213                right.fmt(f)?;
214                Ok(())
215            }
216        }
217    }
218}
219
220#[derive(Debug, Copy, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
221#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
222#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
223pub enum SetOperator {
224    Union,
225    Except,
226    Intersect,
227    Minus,
228}
229
230impl fmt::Display for SetOperator {
231    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
232        f.write_str(match self {
233            SetOperator::Union => "UNION",
234            SetOperator::Except => "EXCEPT",
235            SetOperator::Intersect => "INTERSECT",
236            SetOperator::Minus => "MINUS",
237        })
238    }
239}
240
241/// A quantifier for [SetOperator].
242// TODO: Restrict parsing specific SetQuantifier in some specific dialects.
243// For example, BigQuery does not support `DISTINCT` for `EXCEPT` and `INTERSECT`
244#[derive(Debug, Copy, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
245#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
246#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
247pub enum SetQuantifier {
248    All,
249    Distinct,
250    ByName,
251    AllByName,
252    DistinctByName,
253    None,
254}
255
256impl fmt::Display for SetQuantifier {
257    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
258        match self {
259            SetQuantifier::All => write!(f, "ALL"),
260            SetQuantifier::Distinct => write!(f, "DISTINCT"),
261            SetQuantifier::ByName => write!(f, "BY NAME"),
262            SetQuantifier::AllByName => write!(f, "ALL BY NAME"),
263            SetQuantifier::DistinctByName => write!(f, "DISTINCT BY NAME"),
264            SetQuantifier::None => Ok(()),
265        }
266    }
267}
268
269#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
270#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
271/// A [`TABLE` command]( https://www.postgresql.org/docs/current/sql-select.html#SQL-TABLE)
272#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
273pub struct Table {
274    pub table_name: Option<String>,
275    pub schema_name: Option<String>,
276}
277
278impl fmt::Display for Table {
279    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
280        if let Some(ref schema_name) = self.schema_name {
281            write!(
282                f,
283                "TABLE {}.{}",
284                schema_name,
285                self.table_name.as_ref().unwrap(),
286            )?;
287        } else {
288            write!(f, "TABLE {}", self.table_name.as_ref().unwrap(),)?;
289        }
290        Ok(())
291    }
292}
293
294/// What did this select look like?
295#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
296#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
297#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
298pub enum SelectFlavor {
299    /// `SELECT *`
300    Standard,
301    /// `FROM ... SELECT *`
302    FromFirst,
303    /// `FROM *`
304    FromFirstNoSelect,
305}
306
307/// A restricted variant of `SELECT` (without CTEs/`ORDER BY`), which may
308/// appear either as the only body item of a `Query`, or as an operand
309/// to a set operation like `UNION`.
310#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
311#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
312#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
313pub struct Select {
314    /// Token for the `SELECT` keyword
315    pub select_token: AttachedToken,
316    /// `SELECT [DISTINCT] ...`
317    pub distinct: Option<Distinct>,
318    /// MSSQL syntax: `TOP (<N>) [ PERCENT ] [ WITH TIES ]`
319    pub top: Option<Top>,
320    /// Whether the top was located before `ALL`/`DISTINCT`
321    pub top_before_distinct: bool,
322    /// projection expressions
323    pub projection: Vec<SelectItem>,
324    /// INTO
325    pub into: Option<SelectInto>,
326    /// FROM
327    pub from: Vec<TableWithJoins>,
328    /// LATERAL VIEWs
329    pub lateral_views: Vec<LateralView>,
330    /// ClickHouse syntax: `PREWHERE a = 1 WHERE b = 2`,
331    /// and it can be used together with WHERE selection.
332    ///
333    /// [ClickHouse](https://clickhouse.com/docs/en/sql-reference/statements/select/prewhere)
334    pub prewhere: Option<Expr>,
335    /// WHERE
336    pub selection: Option<Expr>,
337    /// GROUP BY
338    pub group_by: GroupByExpr,
339    /// CLUSTER BY (Hive)
340    pub cluster_by: Vec<Expr>,
341    /// DISTRIBUTE BY (Hive)
342    pub distribute_by: Vec<Expr>,
343    /// SORT BY (Hive)
344    pub sort_by: Vec<OrderByExpr>,
345    /// HAVING
346    pub having: Option<Expr>,
347    /// WINDOW AS
348    pub named_window: Vec<NamedWindowDefinition>,
349    /// QUALIFY (Snowflake)
350    pub qualify: Option<Expr>,
351    /// The positioning of QUALIFY and WINDOW clauses differ between dialects.
352    /// e.g. BigQuery requires that WINDOW comes after QUALIFY, while DUCKDB accepts
353    /// WINDOW before QUALIFY.
354    /// We accept either positioning and flag the accepted variant.
355    pub window_before_qualify: bool,
356    /// BigQuery syntax: `SELECT AS VALUE | SELECT AS STRUCT`
357    pub value_table_mode: Option<ValueTableMode>,
358    /// STARTING WITH .. CONNECT BY
359    pub connect_by: Option<ConnectBy>,
360    /// Was this a FROM-first query?
361    pub flavor: SelectFlavor,
362}
363
364impl fmt::Display for Select {
365    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
366        match self.flavor {
367            SelectFlavor::Standard => {
368                write!(f, "SELECT")?;
369            }
370            SelectFlavor::FromFirst => {
371                write!(f, "FROM {} SELECT", display_comma_separated(&self.from))?;
372            }
373            SelectFlavor::FromFirstNoSelect => {
374                write!(f, "FROM {}", display_comma_separated(&self.from))?;
375            }
376        }
377
378        if let Some(value_table_mode) = self.value_table_mode {
379            f.write_str(" ")?;
380            value_table_mode.fmt(f)?;
381        }
382
383        if let Some(ref top) = self.top {
384            if self.top_before_distinct {
385                f.write_str(" ")?;
386                top.fmt(f)?;
387            }
388        }
389        if let Some(ref distinct) = self.distinct {
390            f.write_str(" ")?;
391            distinct.fmt(f)?;
392        }
393        if let Some(ref top) = self.top {
394            if !self.top_before_distinct {
395                f.write_str(" ")?;
396                top.fmt(f)?;
397            }
398        }
399
400        if !self.projection.is_empty() {
401            indented_list(f, &self.projection)?;
402        }
403
404        if let Some(ref into) = self.into {
405            f.write_str(" ")?;
406            into.fmt(f)?;
407        }
408
409        if self.flavor == SelectFlavor::Standard && !self.from.is_empty() {
410            SpaceOrNewline.fmt(f)?;
411            f.write_str("FROM")?;
412            indented_list(f, &self.from)?;
413        }
414        if !self.lateral_views.is_empty() {
415            for lv in &self.lateral_views {
416                lv.fmt(f)?;
417            }
418        }
419        if let Some(ref prewhere) = self.prewhere {
420            f.write_str(" PREWHERE ")?;
421            prewhere.fmt(f)?;
422        }
423        if let Some(ref selection) = self.selection {
424            SpaceOrNewline.fmt(f)?;
425            f.write_str("WHERE")?;
426            SpaceOrNewline.fmt(f)?;
427            Indent(selection).fmt(f)?;
428        }
429        match &self.group_by {
430            GroupByExpr::All(_) => {
431                SpaceOrNewline.fmt(f)?;
432                self.group_by.fmt(f)?;
433            }
434            GroupByExpr::Expressions(exprs, _) => {
435                if !exprs.is_empty() {
436                    SpaceOrNewline.fmt(f)?;
437                    self.group_by.fmt(f)?;
438                }
439            }
440        }
441        if !self.cluster_by.is_empty() {
442            SpaceOrNewline.fmt(f)?;
443            f.write_str("CLUSTER BY")?;
444            SpaceOrNewline.fmt(f)?;
445            Indent(display_comma_separated(&self.cluster_by)).fmt(f)?;
446        }
447        if !self.distribute_by.is_empty() {
448            SpaceOrNewline.fmt(f)?;
449            f.write_str("DISTRIBUTE BY")?;
450            SpaceOrNewline.fmt(f)?;
451            display_comma_separated(&self.distribute_by).fmt(f)?;
452        }
453        if !self.sort_by.is_empty() {
454            SpaceOrNewline.fmt(f)?;
455            f.write_str("SORT BY")?;
456            SpaceOrNewline.fmt(f)?;
457            Indent(display_comma_separated(&self.sort_by)).fmt(f)?;
458        }
459        if let Some(ref having) = self.having {
460            SpaceOrNewline.fmt(f)?;
461            f.write_str("HAVING")?;
462            SpaceOrNewline.fmt(f)?;
463            Indent(having).fmt(f)?;
464        }
465        if self.window_before_qualify {
466            if !self.named_window.is_empty() {
467                SpaceOrNewline.fmt(f)?;
468                f.write_str("WINDOW")?;
469                SpaceOrNewline.fmt(f)?;
470                display_comma_separated(&self.named_window).fmt(f)?;
471            }
472            if let Some(ref qualify) = self.qualify {
473                SpaceOrNewline.fmt(f)?;
474                f.write_str("QUALIFY")?;
475                SpaceOrNewline.fmt(f)?;
476                qualify.fmt(f)?;
477            }
478        } else {
479            if let Some(ref qualify) = self.qualify {
480                SpaceOrNewline.fmt(f)?;
481                f.write_str("QUALIFY")?;
482                SpaceOrNewline.fmt(f)?;
483                qualify.fmt(f)?;
484            }
485            if !self.named_window.is_empty() {
486                SpaceOrNewline.fmt(f)?;
487                f.write_str("WINDOW")?;
488                SpaceOrNewline.fmt(f)?;
489                display_comma_separated(&self.named_window).fmt(f)?;
490            }
491        }
492        if let Some(ref connect_by) = self.connect_by {
493            SpaceOrNewline.fmt(f)?;
494            connect_by.fmt(f)?;
495        }
496        Ok(())
497    }
498}
499
500/// A hive LATERAL VIEW with potential column aliases
501#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
502#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
503#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
504pub struct LateralView {
505    /// LATERAL VIEW
506    pub lateral_view: Expr,
507    /// LATERAL VIEW table name
508    pub lateral_view_name: ObjectName,
509    /// LATERAL VIEW optional column aliases
510    pub lateral_col_alias: Vec<Ident>,
511    /// LATERAL VIEW OUTER
512    pub outer: bool,
513}
514
515impl fmt::Display for LateralView {
516    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
517        write!(
518            f,
519            " LATERAL VIEW{outer} {} {}",
520            self.lateral_view,
521            self.lateral_view_name,
522            outer = if self.outer { " OUTER" } else { "" }
523        )?;
524        if !self.lateral_col_alias.is_empty() {
525            write!(
526                f,
527                " AS {}",
528                display_comma_separated(&self.lateral_col_alias)
529            )?;
530        }
531        Ok(())
532    }
533}
534
535/// An expression used in a named window declaration.
536///
537/// ```sql
538/// WINDOW mywindow AS [named_window_expr]
539/// ```
540#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
541#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
542#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
543pub enum NamedWindowExpr {
544    /// A direct reference to another named window definition.
545    /// [BigQuery]
546    ///
547    /// Example:
548    /// ```sql
549    /// WINDOW mywindow AS prev_window
550    /// ```
551    ///
552    /// [BigQuery]: https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls#ref_named_window
553    NamedWindow(Ident),
554    /// A window expression.
555    ///
556    /// Example:
557    /// ```sql
558    /// WINDOW mywindow AS (ORDER BY 1)
559    /// ```
560    WindowSpec(WindowSpec),
561}
562
563impl fmt::Display for NamedWindowExpr {
564    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
565        match self {
566            NamedWindowExpr::NamedWindow(named_window) => {
567                write!(f, "{named_window}")?;
568            }
569            NamedWindowExpr::WindowSpec(window_spec) => {
570                write!(f, "({window_spec})")?;
571            }
572        };
573        Ok(())
574    }
575}
576
577#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
578#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
579#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
580pub struct NamedWindowDefinition(pub Ident, pub NamedWindowExpr);
581
582impl fmt::Display for NamedWindowDefinition {
583    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
584        write!(f, "{} AS {}", self.0, self.1)
585    }
586}
587
588#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
589#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
590#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
591pub struct With {
592    /// Token for the "WITH" keyword
593    pub with_token: AttachedToken,
594    pub recursive: bool,
595    pub cte_tables: Vec<Cte>,
596}
597
598impl fmt::Display for With {
599    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
600        f.write_str("WITH ")?;
601        if self.recursive {
602            f.write_str("RECURSIVE ")?;
603        }
604        display_comma_separated(&self.cte_tables).fmt(f)?;
605        Ok(())
606    }
607}
608
609#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
610#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
611#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
612pub enum CteAsMaterialized {
613    /// The `WITH` statement specifies `AS MATERIALIZED` behavior
614    Materialized,
615    /// The `WITH` statement specifies `AS NOT MATERIALIZED` behavior
616    NotMaterialized,
617}
618
619impl fmt::Display for CteAsMaterialized {
620    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
621        match *self {
622            CteAsMaterialized::Materialized => {
623                write!(f, "MATERIALIZED")?;
624            }
625            CteAsMaterialized::NotMaterialized => {
626                write!(f, "NOT MATERIALIZED")?;
627            }
628        };
629        Ok(())
630    }
631}
632
633/// A single CTE (used after `WITH`): `<alias> [(col1, col2, ...)] AS <materialized> ( <query> )`
634/// The names in the column list before `AS`, when specified, replace the names
635/// of the columns returned by the query. The parser does not validate that the
636/// number of columns in the query matches the number of columns in the query.
637#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
638#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
639#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
640pub struct Cte {
641    pub alias: TableAlias,
642    pub query: Box<Query>,
643    pub from: Option<Ident>,
644    pub materialized: Option<CteAsMaterialized>,
645    /// Token for the closing parenthesis
646    pub closing_paren_token: AttachedToken,
647}
648
649impl fmt::Display for Cte {
650    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
651        match self.materialized.as_ref() {
652            None => {
653                self.alias.fmt(f)?;
654                f.write_str(" AS (")?;
655                NewLine.fmt(f)?;
656                Indent(&self.query).fmt(f)?;
657                NewLine.fmt(f)?;
658                f.write_str(")")?;
659            }
660            Some(materialized) => {
661                self.alias.fmt(f)?;
662                f.write_str(" AS ")?;
663                materialized.fmt(f)?;
664                f.write_str(" (")?;
665                NewLine.fmt(f)?;
666                Indent(&self.query).fmt(f)?;
667                NewLine.fmt(f)?;
668                f.write_str(")")?;
669            }
670        };
671        if let Some(ref fr) = self.from {
672            write!(f, " FROM {fr}")?;
673        }
674        Ok(())
675    }
676}
677
678/// Represents an expression behind a wildcard expansion in a projection.
679/// `SELECT T.* FROM T;
680#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
681#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
682#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
683pub enum SelectItemQualifiedWildcardKind {
684    /// Expression is an object name.
685    /// e.g. `alias.*` or even `schema.table.*`
686    ObjectName(ObjectName),
687    /// Select star on an arbitrary expression.
688    /// e.g. `STRUCT<STRING>('foo').*`
689    Expr(Expr),
690}
691
692/// One item of the comma-separated list following `SELECT`
693#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
694#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
695#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
696pub enum SelectItem {
697    /// Any expression, not followed by `[ AS ] alias`
698    UnnamedExpr(Expr),
699    /// An expression, followed by `[ AS ] alias`
700    ExprWithAlias { expr: Expr, alias: Ident },
701    /// An expression, followed by a wildcard expansion.
702    /// e.g. `alias.*`, `STRUCT<STRING>('foo').*`
703    QualifiedWildcard(SelectItemQualifiedWildcardKind, WildcardAdditionalOptions),
704    /// An unqualified `*`
705    Wildcard(WildcardAdditionalOptions),
706}
707
708impl fmt::Display for SelectItemQualifiedWildcardKind {
709    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
710        match &self {
711            SelectItemQualifiedWildcardKind::ObjectName(object_name) => {
712                write!(f, "{object_name}.*")
713            }
714            SelectItemQualifiedWildcardKind::Expr(expr) => write!(f, "{expr}.*"),
715        }
716    }
717}
718
719/// Single aliased identifier
720///
721/// # Syntax
722/// ```plaintext
723/// <ident> AS <alias>
724/// ```
725#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
726#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
727#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
728pub struct IdentWithAlias {
729    pub ident: Ident,
730    pub alias: Ident,
731}
732
733impl fmt::Display for IdentWithAlias {
734    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
735        write!(f, "{} AS {}", self.ident, self.alias)
736    }
737}
738
739/// Additional options for wildcards, e.g. Snowflake `EXCLUDE`/`RENAME` and Bigquery `EXCEPT`.
740#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
741#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
742#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
743pub struct WildcardAdditionalOptions {
744    /// The wildcard token `*`
745    pub wildcard_token: AttachedToken,
746    /// `[ILIKE...]`.
747    ///  Snowflake syntax: <https://docs.snowflake.com/en/sql-reference/sql/select#parameters>
748    pub opt_ilike: Option<IlikeSelectItem>,
749    /// `[EXCLUDE...]`.
750    pub opt_exclude: Option<ExcludeSelectItem>,
751    /// `[EXCEPT...]`.
752    ///  Clickhouse syntax: <https://clickhouse.com/docs/en/sql-reference/statements/select#except>
753    pub opt_except: Option<ExceptSelectItem>,
754    /// `[REPLACE]`
755    ///  BigQuery syntax: <https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#select_replace>
756    ///  Clickhouse syntax: <https://clickhouse.com/docs/en/sql-reference/statements/select#replace>
757    ///  Snowflake syntax: <https://docs.snowflake.com/en/sql-reference/sql/select#parameters>
758    pub opt_replace: Option<ReplaceSelectItem>,
759    /// `[RENAME ...]`.
760    pub opt_rename: Option<RenameSelectItem>,
761}
762
763impl Default for WildcardAdditionalOptions {
764    fn default() -> Self {
765        Self {
766            wildcard_token: TokenWithSpan::wrap(Token::Mul).into(),
767            opt_ilike: None,
768            opt_exclude: None,
769            opt_except: None,
770            opt_replace: None,
771            opt_rename: None,
772        }
773    }
774}
775
776impl fmt::Display for WildcardAdditionalOptions {
777    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
778        if let Some(ilike) = &self.opt_ilike {
779            write!(f, " {ilike}")?;
780        }
781        if let Some(exclude) = &self.opt_exclude {
782            write!(f, " {exclude}")?;
783        }
784        if let Some(except) = &self.opt_except {
785            write!(f, " {except}")?;
786        }
787        if let Some(replace) = &self.opt_replace {
788            write!(f, " {replace}")?;
789        }
790        if let Some(rename) = &self.opt_rename {
791            write!(f, " {rename}")?;
792        }
793        Ok(())
794    }
795}
796
797/// Snowflake `ILIKE` information.
798///
799/// # Syntax
800/// ```plaintext
801/// ILIKE <value>
802/// ```
803#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
804#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
805#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
806pub struct IlikeSelectItem {
807    pub pattern: String,
808}
809
810impl fmt::Display for IlikeSelectItem {
811    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
812        write!(
813            f,
814            "ILIKE '{}'",
815            value::escape_single_quote_string(&self.pattern)
816        )?;
817        Ok(())
818    }
819}
820/// Snowflake `EXCLUDE` information.
821///
822/// # Syntax
823/// ```plaintext
824/// <col_name>
825/// | (<col_name>, <col_name>, ...)
826/// ```
827#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
828#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
829#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
830pub enum ExcludeSelectItem {
831    /// Single column name without parenthesis.
832    ///
833    /// # Syntax
834    /// ```plaintext
835    /// <col_name>
836    /// ```
837    Single(Ident),
838    /// Multiple column names inside parenthesis.
839    /// # Syntax
840    /// ```plaintext
841    /// (<col_name>, <col_name>, ...)
842    /// ```
843    Multiple(Vec<Ident>),
844}
845
846impl fmt::Display for ExcludeSelectItem {
847    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
848        write!(f, "EXCLUDE")?;
849        match self {
850            Self::Single(column) => {
851                write!(f, " {column}")?;
852            }
853            Self::Multiple(columns) => {
854                write!(f, " ({})", display_comma_separated(columns))?;
855            }
856        }
857        Ok(())
858    }
859}
860
861/// Snowflake `RENAME` information.
862///
863/// # Syntax
864/// ```plaintext
865/// <col_name> AS <col_alias>
866/// | (<col_name> AS <col_alias>, <col_name> AS <col_alias>, ...)
867/// ```
868#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
869#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
870#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
871pub enum RenameSelectItem {
872    /// Single column name with alias without parenthesis.
873    ///
874    /// # Syntax
875    /// ```plaintext
876    /// <col_name> AS <col_alias>
877    /// ```
878    Single(IdentWithAlias),
879    /// Multiple column names with aliases inside parenthesis.
880    /// # Syntax
881    /// ```plaintext
882    /// (<col_name> AS <col_alias>, <col_name> AS <col_alias>, ...)
883    /// ```
884    Multiple(Vec<IdentWithAlias>),
885}
886
887impl fmt::Display for RenameSelectItem {
888    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
889        write!(f, "RENAME")?;
890        match self {
891            Self::Single(column) => {
892                write!(f, " {column}")?;
893            }
894            Self::Multiple(columns) => {
895                write!(f, " ({})", display_comma_separated(columns))?;
896            }
897        }
898        Ok(())
899    }
900}
901
902/// Bigquery `EXCEPT` information, with at least one column.
903///
904/// # Syntax
905/// ```plaintext
906/// EXCEPT (<col_name> [, ...])
907/// ```
908#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
909#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
910#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
911pub struct ExceptSelectItem {
912    /// First guaranteed column.
913    pub first_element: Ident,
914    /// Additional columns. This list can be empty.
915    pub additional_elements: Vec<Ident>,
916}
917
918impl fmt::Display for ExceptSelectItem {
919    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
920        write!(f, "EXCEPT ")?;
921        if self.additional_elements.is_empty() {
922            write!(f, "({})", self.first_element)?;
923        } else {
924            write!(
925                f,
926                "({}, {})",
927                self.first_element,
928                display_comma_separated(&self.additional_elements)
929            )?;
930        }
931        Ok(())
932    }
933}
934
935/// Bigquery `REPLACE` information.
936///
937/// # Syntax
938/// ```plaintext
939/// REPLACE (<new_expr> [AS] <col_name>)
940/// REPLACE (<col_name> [AS] <col_alias>, <col_name> [AS] <col_alias>, ...)
941/// ```
942#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
943#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
944#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
945pub struct ReplaceSelectItem {
946    pub items: Vec<Box<ReplaceSelectElement>>,
947}
948
949impl fmt::Display for ReplaceSelectItem {
950    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
951        write!(f, "REPLACE")?;
952        write!(f, " ({})", display_comma_separated(&self.items))?;
953        Ok(())
954    }
955}
956
957/// # Syntax
958/// ```plaintext
959/// <expr> [AS] <column_name>
960/// ```
961#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
962#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
963#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
964pub struct ReplaceSelectElement {
965    pub expr: Expr,
966    pub column_name: Ident,
967    pub as_keyword: bool,
968}
969
970impl fmt::Display for ReplaceSelectElement {
971    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
972        if self.as_keyword {
973            write!(f, "{} AS {}", self.expr, self.column_name)
974        } else {
975            write!(f, "{} {}", self.expr, self.column_name)
976        }
977    }
978}
979
980impl fmt::Display for SelectItem {
981    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
982        use core::fmt::Write;
983        match &self {
984            SelectItem::UnnamedExpr(expr) => expr.fmt(f),
985            SelectItem::ExprWithAlias { expr, alias } => {
986                expr.fmt(f)?;
987                f.write_str(" AS ")?;
988                alias.fmt(f)
989            }
990            SelectItem::QualifiedWildcard(kind, additional_options) => {
991                kind.fmt(f)?;
992                additional_options.fmt(f)
993            }
994            SelectItem::Wildcard(additional_options) => {
995                f.write_char('*')?;
996                additional_options.fmt(f)
997            }
998        }
999    }
1000}
1001
1002#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
1003#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
1004#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
1005pub struct TableWithJoins {
1006    pub relation: TableFactor,
1007    pub joins: Vec<Join>,
1008}
1009
1010impl fmt::Display for TableWithJoins {
1011    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
1012        self.relation.fmt(f)?;
1013        for join in &self.joins {
1014            SpaceOrNewline.fmt(f)?;
1015            join.fmt(f)?;
1016        }
1017        Ok(())
1018    }
1019}
1020
1021/// Joins a table to itself to process hierarchical data in the table.
1022///
1023/// See <https://docs.snowflake.com/en/sql-reference/constructs/connect-by>.
1024#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
1025#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
1026#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
1027pub struct ConnectBy {
1028    /// START WITH
1029    pub condition: Expr,
1030    /// CONNECT BY
1031    pub relationships: Vec<Expr>,
1032}
1033
1034impl fmt::Display for ConnectBy {
1035    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
1036        write!(
1037            f,
1038            "START WITH {condition} CONNECT BY {relationships}",
1039            condition = self.condition,
1040            relationships = display_comma_separated(&self.relationships)
1041        )
1042    }
1043}
1044
1045#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
1046#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
1047#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
1048pub struct Setting {
1049    pub key: Ident,
1050    pub value: Value,
1051}
1052
1053impl fmt::Display for Setting {
1054    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
1055        write!(f, "{} = {}", self.key, self.value)
1056    }
1057}
1058
1059/// An expression optionally followed by an alias.
1060///
1061/// Example:
1062/// ```sql
1063/// 42 AS myint
1064/// ```
1065#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
1066#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
1067#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
1068pub struct ExprWithAlias {
1069    pub expr: Expr,
1070    pub alias: Option<Ident>,
1071}
1072
1073impl fmt::Display for ExprWithAlias {
1074    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
1075        let ExprWithAlias { expr, alias } = self;
1076        write!(f, "{expr}")?;
1077        if let Some(alias) = alias {
1078            write!(f, " AS {alias}")?;
1079        }
1080        Ok(())
1081    }
1082}
1083
1084/// An expression optionally followed by an alias and order by options.
1085///
1086/// Example:
1087/// ```sql
1088/// 42 AS myint ASC
1089/// ```
1090#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
1091#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
1092#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
1093pub struct ExprWithAliasAndOrderBy {
1094    pub expr: ExprWithAlias,
1095    pub order_by: OrderByOptions,
1096}
1097
1098impl fmt::Display for ExprWithAliasAndOrderBy {
1099    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
1100        write!(f, "{}{}", self.expr, self.order_by)
1101    }
1102}
1103
1104/// Arguments to a table-valued function
1105#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
1106#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
1107#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
1108pub struct TableFunctionArgs {
1109    pub args: Vec<FunctionArg>,
1110    /// ClickHouse-specific SETTINGS clause.
1111    /// For example,
1112    /// `SELECT * FROM executable('generate_random.py', TabSeparated, 'id UInt32, random String', SETTINGS send_chunk_header = false, pool_size = 16)`
1113    /// [`executable` table function](https://clickhouse.com/docs/en/engines/table-functions/executable)
1114    pub settings: Option<Vec<Setting>>,
1115}
1116
1117#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
1118#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
1119#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
1120pub enum TableIndexHintType {
1121    Use,
1122    Ignore,
1123    Force,
1124}
1125
1126impl fmt::Display for TableIndexHintType {
1127    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
1128        f.write_str(match self {
1129            TableIndexHintType::Use => "USE",
1130            TableIndexHintType::Ignore => "IGNORE",
1131            TableIndexHintType::Force => "FORCE",
1132        })
1133    }
1134}
1135
1136#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
1137#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
1138#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
1139pub enum TableIndexType {
1140    Index,
1141    Key,
1142}
1143
1144impl fmt::Display for TableIndexType {
1145    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
1146        f.write_str(match self {
1147            TableIndexType::Index => "INDEX",
1148            TableIndexType::Key => "KEY",
1149        })
1150    }
1151}
1152
1153#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
1154#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
1155#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
1156pub enum TableIndexHintForClause {
1157    Join,
1158    OrderBy,
1159    GroupBy,
1160}
1161
1162impl fmt::Display for TableIndexHintForClause {
1163    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
1164        f.write_str(match self {
1165            TableIndexHintForClause::Join => "JOIN",
1166            TableIndexHintForClause::OrderBy => "ORDER BY",
1167            TableIndexHintForClause::GroupBy => "GROUP BY",
1168        })
1169    }
1170}
1171
1172#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
1173#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
1174#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
1175pub struct TableIndexHints {
1176    pub hint_type: TableIndexHintType,
1177    pub index_type: TableIndexType,
1178    pub for_clause: Option<TableIndexHintForClause>,
1179    pub index_names: Vec<Ident>,
1180}
1181
1182impl fmt::Display for TableIndexHints {
1183    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
1184        write!(f, "{} {} ", self.hint_type, self.index_type)?;
1185        if let Some(for_clause) = &self.for_clause {
1186            write!(f, "FOR {} ", for_clause)?;
1187        }
1188        write!(f, "({})", display_comma_separated(&self.index_names))
1189    }
1190}
1191
1192/// A table name or a parenthesized subquery with an optional alias
1193#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
1194#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
1195#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
1196#[cfg_attr(feature = "visitor", visit(with = "visit_table_factor"))]
1197pub enum TableFactor {
1198    Table {
1199        #[cfg_attr(feature = "visitor", visit(with = "visit_relation"))]
1200        name: ObjectName,
1201        alias: Option<TableAlias>,
1202        /// Arguments of a table-valued function, as supported by Postgres
1203        /// and MSSQL. Note that deprecated MSSQL `FROM foo (NOLOCK)` syntax
1204        /// will also be parsed as `args`.
1205        ///
1206        /// This field's value is `Some(v)`, where `v` is a (possibly empty)
1207        /// vector of arguments, in the case of a table-valued function call,
1208        /// whereas it's `None` in the case of a regular table name.
1209        args: Option<TableFunctionArgs>,
1210        /// MSSQL-specific `WITH (...)` hints such as NOLOCK.
1211        with_hints: Vec<Expr>,
1212        /// Optional version qualifier to facilitate table time-travel, as
1213        /// supported by BigQuery and MSSQL.
1214        version: Option<TableVersion>,
1215        //  Optional table function modifier to generate the ordinality for column.
1216        /// For example, `SELECT * FROM generate_series(1, 10) WITH ORDINALITY AS t(a, b);`
1217        /// [WITH ORDINALITY](https://www.postgresql.org/docs/current/functions-srf.html), supported by Postgres.
1218        with_ordinality: bool,
1219        /// [Partition selection](https://dev.mysql.com/doc/refman/8.0/en/partitioning-selection.html), supported by MySQL.
1220        partitions: Vec<Ident>,
1221        /// Optional PartiQL JsonPath: <https://partiql.org/dql/from.html>
1222        json_path: Option<JsonPath>,
1223        /// Optional table sample modifier
1224        /// See: <https://jakewheat.github.io/sql-overview/sql-2016-foundation-grammar.html#sample-clause>
1225        sample: Option<TableSampleKind>,
1226        /// Optional index hints(mysql)
1227        /// See: <https://dev.mysql.com/doc/refman/8.4/en/index-hints.html>
1228        index_hints: Vec<TableIndexHints>,
1229    },
1230    Derived {
1231        lateral: bool,
1232        subquery: Box<Query>,
1233        alias: Option<TableAlias>,
1234    },
1235    /// `TABLE(<expr>)[ AS <alias> ]`
1236    TableFunction {
1237        expr: Expr,
1238        alias: Option<TableAlias>,
1239    },
1240    /// `e.g. LATERAL FLATTEN(<args>)[ AS <alias> ]`
1241    Function {
1242        lateral: bool,
1243        name: ObjectName,
1244        args: Vec<FunctionArg>,
1245        alias: Option<TableAlias>,
1246    },
1247    /// ```sql
1248    /// SELECT * FROM UNNEST ([10,20,30]) as numbers WITH OFFSET;
1249    /// +---------+--------+
1250    /// | numbers | offset |
1251    /// +---------+--------+
1252    /// | 10      | 0      |
1253    /// | 20      | 1      |
1254    /// | 30      | 2      |
1255    /// +---------+--------+
1256    /// ```
1257    UNNEST {
1258        alias: Option<TableAlias>,
1259        array_exprs: Vec<Expr>,
1260        with_offset: bool,
1261        with_offset_alias: Option<Ident>,
1262        with_ordinality: bool,
1263    },
1264    /// The `JSON_TABLE` table-valued function.
1265    /// Part of the SQL standard, but implemented only by MySQL, Oracle, and DB2.
1266    ///
1267    /// <https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016#json_table>
1268    /// <https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html#function_json-table>
1269    ///
1270    /// ```sql
1271    /// SELECT * FROM JSON_TABLE(
1272    ///    '[{"a": 1, "b": 2}, {"a": 3, "b": 4}]',
1273    ///    '$[*]' COLUMNS(
1274    ///        a INT PATH '$.a' DEFAULT '0' ON EMPTY,
1275    ///        b INT PATH '$.b' NULL ON ERROR
1276    ///     )
1277    /// ) AS jt;
1278    /// ````
1279    JsonTable {
1280        /// The JSON expression to be evaluated. It must evaluate to a json string
1281        json_expr: Expr,
1282        /// The path to the array or object to be iterated over.
1283        /// It must evaluate to a json array or object.
1284        json_path: Value,
1285        /// The columns to be extracted from each element of the array or object.
1286        /// Each column must have a name and a type.
1287        columns: Vec<JsonTableColumn>,
1288        /// The alias for the table.
1289        alias: Option<TableAlias>,
1290    },
1291    /// The MSSQL's `OPENJSON` table-valued function.
1292    ///
1293    /// ```sql
1294    /// OPENJSON( jsonExpression [ , path ] )  [ <with_clause> ]
1295    ///
1296    /// <with_clause> ::= WITH ( { colName type [ column_path ] [ AS JSON ] } [ ,...n ] )
1297    /// ````
1298    ///
1299    /// Reference: <https://learn.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql?view=sql-server-ver16#syntax>
1300    OpenJsonTable {
1301        /// The JSON expression to be evaluated. It must evaluate to a json string
1302        json_expr: Expr,
1303        /// The path to the array or object to be iterated over.
1304        /// It must evaluate to a json array or object.
1305        json_path: Option<Value>,
1306        /// The columns to be extracted from each element of the array or object.
1307        /// Each column must have a name and a type.
1308        columns: Vec<OpenJsonTableColumn>,
1309        /// The alias for the table.
1310        alias: Option<TableAlias>,
1311    },
1312    /// Represents a parenthesized table factor. The SQL spec only allows a
1313    /// join expression (`(foo <JOIN> bar [ <JOIN> baz ... ])`) to be nested,
1314    /// possibly several times.
1315    ///
1316    /// The parser may also accept non-standard nesting of bare tables for some
1317    /// dialects, but the information about such nesting is stripped from AST.
1318    NestedJoin {
1319        table_with_joins: Box<TableWithJoins>,
1320        alias: Option<TableAlias>,
1321    },
1322    /// Represents PIVOT operation on a table.
1323    /// For example `FROM monthly_sales PIVOT(sum(amount) FOR MONTH IN ('JAN', 'FEB'))`
1324    ///
1325    /// [BigQuery](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#pivot_operator)
1326    /// [Snowflake](https://docs.snowflake.com/en/sql-reference/constructs/pivot)
1327    Pivot {
1328        table: Box<TableFactor>,
1329        aggregate_functions: Vec<ExprWithAlias>, // Function expression
1330        value_column: Vec<Ident>,
1331        value_source: PivotValueSource,
1332        default_on_null: Option<Expr>,
1333        alias: Option<TableAlias>,
1334    },
1335    /// An UNPIVOT operation on a table.
1336    ///
1337    /// Syntax:
1338    /// ```sql
1339    /// table UNPIVOT [ { INCLUDE | EXCLUDE } NULLS ] (value FOR name IN (column1, [ column2, ... ])) [ alias ]
1340    /// ```
1341    ///
1342    /// See <https://docs.snowflake.com/en/sql-reference/constructs/unpivot>.
1343    Unpivot {
1344        table: Box<TableFactor>,
1345        value: Ident,
1346        name: Ident,
1347        columns: Vec<Ident>,
1348        null_inclusion: Option<NullInclusion>,
1349        alias: Option<TableAlias>,
1350    },
1351    /// A `MATCH_RECOGNIZE` operation on a table.
1352    ///
1353    /// See <https://docs.snowflake.com/en/sql-reference/constructs/match_recognize>.
1354    MatchRecognize {
1355        table: Box<TableFactor>,
1356        /// `PARTITION BY <expr> [, ... ]`
1357        partition_by: Vec<Expr>,
1358        /// `ORDER BY <expr> [, ... ]`
1359        order_by: Vec<OrderByExpr>,
1360        /// `MEASURES <expr> [AS] <alias> [, ... ]`
1361        measures: Vec<Measure>,
1362        /// `ONE ROW PER MATCH | ALL ROWS PER MATCH [ <option> ]`
1363        rows_per_match: Option<RowsPerMatch>,
1364        /// `AFTER MATCH SKIP <option>`
1365        after_match_skip: Option<AfterMatchSkip>,
1366        /// `PATTERN ( <pattern> )`
1367        pattern: MatchRecognizePattern,
1368        /// `DEFINE <symbol> AS <expr> [, ... ]`
1369        symbols: Vec<SymbolDefinition>,
1370        alias: Option<TableAlias>,
1371    },
1372    /// The `XMLTABLE` table-valued function.
1373    /// Part of the SQL standard, supported by PostgreSQL, Oracle, and DB2.
1374    ///
1375    /// <https://www.postgresql.org/docs/15/functions-xml.html#FUNCTIONS-XML-PROCESSING>
1376    ///
1377    /// ```sql
1378    /// SELECT xmltable.*
1379    /// FROM xmldata,
1380    /// XMLTABLE('//ROWS/ROW'
1381    ///     PASSING data
1382    ///     COLUMNS id int PATH '@id',
1383    ///     ordinality FOR ORDINALITY,
1384    ///     "COUNTRY_NAME" text,
1385    ///     country_id text PATH 'COUNTRY_ID',
1386    ///     size_sq_km float PATH 'SIZE[@unit = "sq_km"]',
1387    ///     size_other text PATH 'concat(SIZE[@unit!="sq_km"], " ", SIZE[@unit!="sq_km"]/@unit)',
1388    ///     premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified'
1389    /// );
1390    /// ````
1391    XmlTable {
1392        /// Optional XMLNAMESPACES clause (empty if not present)
1393        namespaces: Vec<XmlNamespaceDefinition>,
1394        /// The row-generating XPath expression.
1395        row_expression: Expr,
1396        /// The PASSING clause specifying the document expression.
1397        passing: XmlPassingClause,
1398        /// The columns to be extracted from each generated row.
1399        columns: Vec<XmlTableColumn>,
1400        /// The alias for the table.
1401        alias: Option<TableAlias>,
1402    },
1403}
1404
1405/// The table sample modifier options
1406#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
1407#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
1408#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
1409pub enum TableSampleKind {
1410    /// Table sample located before the table alias option
1411    BeforeTableAlias(Box<TableSample>),
1412    /// Table sample located after the table alias option
1413    AfterTableAlias(Box<TableSample>),
1414}
1415
1416#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
1417#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
1418#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
1419pub struct TableSample {
1420    pub modifier: TableSampleModifier,
1421    pub name: Option<TableSampleMethod>,
1422    pub quantity: Option<TableSampleQuantity>,
1423    pub seed: Option<TableSampleSeed>,
1424    pub bucket: Option<TableSampleBucket>,
1425    pub offset: Option<Expr>,
1426}
1427
1428#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
1429#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
1430#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
1431pub enum TableSampleModifier {
1432    Sample,
1433    TableSample,
1434}
1435
1436impl fmt::Display for TableSampleModifier {
1437    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
1438        match self {
1439            TableSampleModifier::Sample => write!(f, "SAMPLE")?,
1440            TableSampleModifier::TableSample => write!(f, "TABLESAMPLE")?,
1441        }
1442        Ok(())
1443    }
1444}
1445
1446#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
1447#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
1448#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
1449pub struct TableSampleQuantity {
1450    pub parenthesized: bool,
1451    pub value: Expr,
1452    pub unit: Option<TableSampleUnit>,
1453}
1454
1455impl fmt::Display for TableSampleQuantity {
1456    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
1457        if self.parenthesized {
1458            write!(f, "(")?;
1459        }
1460        write!(f, "{}", self.value)?;
1461        if let Some(unit) = &self.unit {
1462            write!(f, " {}", unit)?;
1463        }
1464        if self.parenthesized {
1465            write!(f, ")")?;
1466        }
1467        Ok(())
1468    }
1469}
1470
1471/// The table sample method names
1472#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
1473#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
1474#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
1475pub enum TableSampleMethod {
1476    Row,
1477    Bernoulli,
1478    System,
1479    Block,
1480}
1481
1482impl fmt::Display for TableSampleMethod {
1483    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
1484        match self {
1485            TableSampleMethod::Bernoulli => write!(f, "BERNOULLI"),
1486            TableSampleMethod::Row => write!(f, "ROW"),
1487            TableSampleMethod::System => write!(f, "SYSTEM"),
1488            TableSampleMethod::Block => write!(f, "BLOCK"),
1489        }
1490    }
1491}
1492
1493#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
1494#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
1495#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
1496pub struct TableSampleSeed {
1497    pub modifier: TableSampleSeedModifier,
1498    pub value: Value,
1499}
1500
1501impl fmt::Display for TableSampleSeed {
1502    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
1503        write!(f, "{} ({})", self.modifier, self.value)?;
1504        Ok(())
1505    }
1506}
1507
1508#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
1509#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
1510#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
1511pub enum TableSampleSeedModifier {
1512    Repeatable,
1513    Seed,
1514}
1515
1516impl fmt::Display for TableSampleSeedModifier {
1517    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
1518        match self {
1519            TableSampleSeedModifier::Repeatable => write!(f, "REPEATABLE"),
1520            TableSampleSeedModifier::Seed => write!(f, "SEED"),
1521        }
1522    }
1523}
1524
1525#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
1526#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
1527#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
1528pub enum TableSampleUnit {
1529    Rows,
1530    Percent,
1531}
1532
1533impl fmt::Display for TableSampleUnit {
1534    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
1535        match self {
1536            TableSampleUnit::Percent => write!(f, "PERCENT"),
1537            TableSampleUnit::Rows => write!(f, "ROWS"),
1538        }
1539    }
1540}
1541
1542#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
1543#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
1544#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
1545pub struct TableSampleBucket {
1546    pub bucket: Value,
1547    pub total: Value,
1548    pub on: Option<Expr>,
1549}
1550
1551impl fmt::Display for TableSampleBucket {
1552    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
1553        write!(f, "BUCKET {} OUT OF {}", self.bucket, self.total)?;
1554        if let Some(on) = &self.on {
1555            write!(f, " ON {}", on)?;
1556        }
1557        Ok(())
1558    }
1559}
1560impl fmt::Display for TableSample {
1561    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
1562        write!(f, "{}", self.modifier)?;
1563        if let Some(name) = &self.name {
1564            write!(f, " {}", name)?;
1565        }
1566        if let Some(quantity) = &self.quantity {
1567            write!(f, " {}", quantity)?;
1568        }
1569        if let Some(seed) = &self.seed {
1570            write!(f, " {}", seed)?;
1571        }
1572        if let Some(bucket) = &self.bucket {
1573            write!(f, " ({})", bucket)?;
1574        }
1575        if let Some(offset) = &self.offset {
1576            write!(f, " OFFSET {}", offset)?;
1577        }
1578        Ok(())
1579    }
1580}
1581
1582/// The source of values in a `PIVOT` operation.
1583#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
1584#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
1585#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
1586pub enum PivotValueSource {
1587    /// Pivot on a static list of values.
1588    ///
1589    /// See <https://docs.snowflake.com/en/sql-reference/constructs/pivot#pivot-on-a-specified-list-of-column-values-for-the-pivot-column>.
1590    List(Vec<ExprWithAlias>),
1591    /// Pivot on all distinct values of the pivot column.
1592    ///
1593    /// See <https://docs.snowflake.com/en/sql-reference/constructs/pivot#pivot-on-all-distinct-column-values-automatically-with-dynamic-pivot>.
1594    Any(Vec<OrderByExpr>),
1595    /// Pivot on all values returned by a subquery.
1596    ///
1597    /// See <https://docs.snowflake.com/en/sql-reference/constructs/pivot#pivot-on-column-values-using-a-subquery-with-dynamic-pivot>.
1598    Subquery(Box<Query>),
1599}
1600
1601impl fmt::Display for PivotValueSource {
1602    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
1603        match self {
1604            PivotValueSource::List(values) => write!(f, "{}", display_comma_separated(values)),
1605            PivotValueSource::Any(order_by) => {
1606                write!(f, "ANY")?;
1607                if !order_by.is_empty() {
1608                    write!(f, " ORDER BY {}", display_comma_separated(order_by))?;
1609                }
1610                Ok(())
1611            }
1612            PivotValueSource::Subquery(query) => write!(f, "{query}"),
1613        }
1614    }
1615}
1616
1617/// An item in the `MEASURES` subclause of a `MATCH_RECOGNIZE` operation.
1618///
1619/// See <https://docs.snowflake.com/en/sql-reference/constructs/match_recognize#measures-specifying-additional-output-columns>.
1620#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
1621#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
1622#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
1623pub struct Measure {
1624    pub expr: Expr,
1625    pub alias: Ident,
1626}
1627
1628impl fmt::Display for Measure {
1629    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
1630        write!(f, "{} AS {}", self.expr, self.alias)
1631    }
1632}
1633
1634/// The rows per match option in a `MATCH_RECOGNIZE` operation.
1635///
1636/// See <https://docs.snowflake.com/en/sql-reference/constructs/match_recognize#row-s-per-match-specifying-the-rows-to-return>.
1637#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
1638#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
1639#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
1640pub enum RowsPerMatch {
1641    /// `ONE ROW PER MATCH`
1642    OneRow,
1643    /// `ALL ROWS PER MATCH <mode>`
1644    AllRows(Option<EmptyMatchesMode>),
1645}
1646
1647impl fmt::Display for RowsPerMatch {
1648    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
1649        match self {
1650            RowsPerMatch::OneRow => write!(f, "ONE ROW PER MATCH"),
1651            RowsPerMatch::AllRows(mode) => {
1652                write!(f, "ALL ROWS PER MATCH")?;
1653                if let Some(mode) = mode {
1654                    write!(f, " {}", mode)?;
1655                }
1656                Ok(())
1657            }
1658        }
1659    }
1660}
1661
1662/// The after match skip option in a `MATCH_RECOGNIZE` operation.
1663///
1664/// See <https://docs.snowflake.com/en/sql-reference/constructs/match_recognize#after-match-skip-specifying-where-to-continue-after-a-match>.
1665#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
1666#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
1667#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
1668pub enum AfterMatchSkip {
1669    /// `PAST LAST ROW`
1670    PastLastRow,
1671    /// `TO NEXT ROW`
1672    ToNextRow,
1673    /// `TO FIRST <symbol>`
1674    ToFirst(Ident),
1675    /// `TO LAST <symbol>`
1676    ToLast(Ident),
1677}
1678
1679impl fmt::Display for AfterMatchSkip {
1680    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
1681        write!(f, "AFTER MATCH SKIP ")?;
1682        match self {
1683            AfterMatchSkip::PastLastRow => write!(f, "PAST LAST ROW"),
1684            AfterMatchSkip::ToNextRow => write!(f, " TO NEXT ROW"),
1685            AfterMatchSkip::ToFirst(symbol) => write!(f, "TO FIRST {symbol}"),
1686            AfterMatchSkip::ToLast(symbol) => write!(f, "TO LAST {symbol}"),
1687        }
1688    }
1689}
1690
1691#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
1692#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
1693#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
1694pub enum EmptyMatchesMode {
1695    /// `SHOW EMPTY MATCHES`
1696    Show,
1697    /// `OMIT EMPTY MATCHES`
1698    Omit,
1699    /// `WITH UNMATCHED ROWS`
1700    WithUnmatched,
1701}
1702
1703impl fmt::Display for EmptyMatchesMode {
1704    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
1705        match self {
1706            EmptyMatchesMode::Show => write!(f, "SHOW EMPTY MATCHES"),
1707            EmptyMatchesMode::Omit => write!(f, "OMIT EMPTY MATCHES"),
1708            EmptyMatchesMode::WithUnmatched => write!(f, "WITH UNMATCHED ROWS"),
1709        }
1710    }
1711}
1712
1713/// A symbol defined in a `MATCH_RECOGNIZE` operation.
1714///
1715/// See <https://docs.snowflake.com/en/sql-reference/constructs/match_recognize#define-defining-symbols>.
1716#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
1717#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
1718#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
1719pub struct SymbolDefinition {
1720    pub symbol: Ident,
1721    pub definition: Expr,
1722}
1723
1724impl fmt::Display for SymbolDefinition {
1725    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
1726        write!(f, "{} AS {}", self.symbol, self.definition)
1727    }
1728}
1729
1730/// A symbol in a `MATCH_RECOGNIZE` pattern.
1731#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
1732#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
1733#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
1734pub enum MatchRecognizeSymbol {
1735    /// A named symbol, e.g. `S1`.
1736    Named(Ident),
1737    /// A virtual symbol representing the start of the of partition (`^`).
1738    Start,
1739    /// A virtual symbol representing the end of the partition (`$`).
1740    End,
1741}
1742
1743impl fmt::Display for MatchRecognizeSymbol {
1744    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
1745        match self {
1746            MatchRecognizeSymbol::Named(symbol) => write!(f, "{symbol}"),
1747            MatchRecognizeSymbol::Start => write!(f, "^"),
1748            MatchRecognizeSymbol::End => write!(f, "$"),
1749        }
1750    }
1751}
1752
1753/// The pattern in a `MATCH_RECOGNIZE` operation.
1754///
1755/// See <https://docs.snowflake.com/en/sql-reference/constructs/match_recognize#pattern-specifying-the-pattern-to-match>.
1756#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
1757#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
1758#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
1759pub enum MatchRecognizePattern {
1760    /// A named symbol such as `S1` or a virtual symbol such as `^`.
1761    Symbol(MatchRecognizeSymbol),
1762    /// {- symbol -}
1763    Exclude(MatchRecognizeSymbol),
1764    /// PERMUTE(symbol_1, ..., symbol_n)
1765    Permute(Vec<MatchRecognizeSymbol>),
1766    /// pattern_1 pattern_2 ... pattern_n
1767    Concat(Vec<MatchRecognizePattern>),
1768    /// ( pattern )
1769    Group(Box<MatchRecognizePattern>),
1770    /// pattern_1 | pattern_2 | ... | pattern_n
1771    Alternation(Vec<MatchRecognizePattern>),
1772    /// e.g. pattern*
1773    Repetition(Box<MatchRecognizePattern>, RepetitionQuantifier),
1774}
1775
1776impl fmt::Display for MatchRecognizePattern {
1777    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
1778        use MatchRecognizePattern::*;
1779        match self {
1780            Symbol(symbol) => write!(f, "{}", symbol),
1781            Exclude(symbol) => write!(f, "{{- {symbol} -}}"),
1782            Permute(symbols) => write!(f, "PERMUTE({})", display_comma_separated(symbols)),
1783            Concat(patterns) => write!(f, "{}", display_separated(patterns, " ")),
1784            Group(pattern) => write!(f, "( {pattern} )"),
1785            Alternation(patterns) => write!(f, "{}", display_separated(patterns, " | ")),
1786            Repetition(pattern, op) => write!(f, "{pattern}{op}"),
1787        }
1788    }
1789}
1790
1791/// Determines the minimum and maximum allowed occurrences of a pattern in a
1792/// `MATCH_RECOGNIZE` operation.
1793#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
1794#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
1795#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
1796pub enum RepetitionQuantifier {
1797    /// `*`
1798    ZeroOrMore,
1799    /// `+`
1800    OneOrMore,
1801    /// `?`
1802    AtMostOne,
1803    /// `{n}`
1804    Exactly(u32),
1805    /// `{n,}`
1806    AtLeast(u32),
1807    /// `{,n}`
1808    AtMost(u32),
1809    /// `{n,m}
1810    Range(u32, u32),
1811}
1812
1813impl fmt::Display for RepetitionQuantifier {
1814    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
1815        use RepetitionQuantifier::*;
1816        match self {
1817            ZeroOrMore => write!(f, "*"),
1818            OneOrMore => write!(f, "+"),
1819            AtMostOne => write!(f, "?"),
1820            Exactly(n) => write!(f, "{{{n}}}"),
1821            AtLeast(n) => write!(f, "{{{n},}}"),
1822            AtMost(n) => write!(f, "{{,{n}}}"),
1823            Range(n, m) => write!(f, "{{{n},{m}}}"),
1824        }
1825    }
1826}
1827
1828impl fmt::Display for TableFactor {
1829    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
1830        match self {
1831            TableFactor::Table {
1832                name,
1833                alias,
1834                args,
1835                with_hints,
1836                version,
1837                partitions,
1838                with_ordinality,
1839                json_path,
1840                sample,
1841                index_hints,
1842            } => {
1843                name.fmt(f)?;
1844                if let Some(json_path) = json_path {
1845                    json_path.fmt(f)?;
1846                }
1847                if !partitions.is_empty() {
1848                    write!(f, "PARTITION ({})", display_comma_separated(partitions))?;
1849                }
1850                if let Some(args) = args {
1851                    write!(f, "(")?;
1852                    write!(f, "{}", display_comma_separated(&args.args))?;
1853                    if let Some(ref settings) = args.settings {
1854                        if !args.args.is_empty() {
1855                            write!(f, ", ")?;
1856                        }
1857                        write!(f, "SETTINGS {}", display_comma_separated(settings))?;
1858                    }
1859                    write!(f, ")")?;
1860                }
1861                if *with_ordinality {
1862                    write!(f, " WITH ORDINALITY")?;
1863                }
1864                if let Some(TableSampleKind::BeforeTableAlias(sample)) = sample {
1865                    write!(f, " {sample}")?;
1866                }
1867                if let Some(alias) = alias {
1868                    write!(f, " AS {alias}")?;
1869                }
1870                if !index_hints.is_empty() {
1871                    write!(f, " {}", display_separated(index_hints, " "))?;
1872                }
1873                if !with_hints.is_empty() {
1874                    write!(f, " WITH ({})", display_comma_separated(with_hints))?;
1875                }
1876                if let Some(version) = version {
1877                    write!(f, "{version}")?;
1878                }
1879                if let Some(TableSampleKind::AfterTableAlias(sample)) = sample {
1880                    write!(f, " {sample}")?;
1881                }
1882                Ok(())
1883            }
1884            TableFactor::Derived {
1885                lateral,
1886                subquery,
1887                alias,
1888            } => {
1889                if *lateral {
1890                    write!(f, "LATERAL ")?;
1891                }
1892                f.write_str("(")?;
1893                NewLine.fmt(f)?;
1894                Indent(subquery).fmt(f)?;
1895                NewLine.fmt(f)?;
1896                f.write_str(")")?;
1897                if let Some(alias) = alias {
1898                    write!(f, " AS {alias}")?;
1899                }
1900                Ok(())
1901            }
1902            TableFactor::Function {
1903                lateral,
1904                name,
1905                args,
1906                alias,
1907            } => {
1908                if *lateral {
1909                    write!(f, "LATERAL ")?;
1910                }
1911                write!(f, "{name}")?;
1912                write!(f, "({})", display_comma_separated(args))?;
1913                if let Some(alias) = alias {
1914                    write!(f, " AS {alias}")?;
1915                }
1916                Ok(())
1917            }
1918            TableFactor::TableFunction { expr, alias } => {
1919                write!(f, "TABLE({expr})")?;
1920                if let Some(alias) = alias {
1921                    write!(f, " AS {alias}")?;
1922                }
1923                Ok(())
1924            }
1925            TableFactor::UNNEST {
1926                alias,
1927                array_exprs,
1928                with_offset,
1929                with_offset_alias,
1930                with_ordinality,
1931            } => {
1932                write!(f, "UNNEST({})", display_comma_separated(array_exprs))?;
1933
1934                if *with_ordinality {
1935                    write!(f, " WITH ORDINALITY")?;
1936                }
1937
1938                if let Some(alias) = alias {
1939                    write!(f, " AS {alias}")?;
1940                }
1941                if *with_offset {
1942                    write!(f, " WITH OFFSET")?;
1943                }
1944                if let Some(alias) = with_offset_alias {
1945                    write!(f, " AS {alias}")?;
1946                }
1947                Ok(())
1948            }
1949            TableFactor::JsonTable {
1950                json_expr,
1951                json_path,
1952                columns,
1953                alias,
1954            } => {
1955                write!(
1956                    f,
1957                    "JSON_TABLE({json_expr}, {json_path} COLUMNS({columns}))",
1958                    columns = display_comma_separated(columns)
1959                )?;
1960                if let Some(alias) = alias {
1961                    write!(f, " AS {alias}")?;
1962                }
1963                Ok(())
1964            }
1965            TableFactor::OpenJsonTable {
1966                json_expr,
1967                json_path,
1968                columns,
1969                alias,
1970            } => {
1971                write!(f, "OPENJSON({json_expr}")?;
1972                if let Some(json_path) = json_path {
1973                    write!(f, ", {json_path}")?;
1974                }
1975                write!(f, ")")?;
1976                if !columns.is_empty() {
1977                    write!(f, " WITH ({})", display_comma_separated(columns))?;
1978                }
1979                if let Some(alias) = alias {
1980                    write!(f, " AS {alias}")?;
1981                }
1982                Ok(())
1983            }
1984            TableFactor::NestedJoin {
1985                table_with_joins,
1986                alias,
1987            } => {
1988                write!(f, "({table_with_joins})")?;
1989                if let Some(alias) = alias {
1990                    write!(f, " AS {alias}")?;
1991                }
1992                Ok(())
1993            }
1994            TableFactor::Pivot {
1995                table,
1996                aggregate_functions,
1997                value_column,
1998                value_source,
1999                default_on_null,
2000                alias,
2001            } => {
2002                write!(
2003                    f,
2004                    "{table} PIVOT({} FOR {} IN ({value_source})",
2005                    display_comma_separated(aggregate_functions),
2006                    Expr::CompoundIdentifier(value_column.to_vec()),
2007                )?;
2008                if let Some(expr) = default_on_null {
2009                    write!(f, " DEFAULT ON NULL ({expr})")?;
2010                }
2011                write!(f, ")")?;
2012                if alias.is_some() {
2013                    write!(f, " AS {}", alias.as_ref().unwrap())?;
2014                }
2015                Ok(())
2016            }
2017            TableFactor::Unpivot {
2018                table,
2019                null_inclusion,
2020                value,
2021                name,
2022                columns,
2023                alias,
2024            } => {
2025                write!(f, "{table} UNPIVOT")?;
2026                if let Some(null_inclusion) = null_inclusion {
2027                    write!(f, " {null_inclusion} ")?;
2028                }
2029                write!(
2030                    f,
2031                    "({} FOR {} IN ({}))",
2032                    value,
2033                    name,
2034                    display_comma_separated(columns)
2035                )?;
2036                if alias.is_some() {
2037                    write!(f, " AS {}", alias.as_ref().unwrap())?;
2038                }
2039                Ok(())
2040            }
2041            TableFactor::MatchRecognize {
2042                table,
2043                partition_by,
2044                order_by,
2045                measures,
2046                rows_per_match,
2047                after_match_skip,
2048                pattern,
2049                symbols,
2050                alias,
2051            } => {
2052                write!(f, "{table} MATCH_RECOGNIZE(")?;
2053                if !partition_by.is_empty() {
2054                    write!(f, "PARTITION BY {} ", display_comma_separated(partition_by))?;
2055                }
2056                if !order_by.is_empty() {
2057                    write!(f, "ORDER BY {} ", display_comma_separated(order_by))?;
2058                }
2059                if !measures.is_empty() {
2060                    write!(f, "MEASURES {} ", display_comma_separated(measures))?;
2061                }
2062                if let Some(rows_per_match) = rows_per_match {
2063                    write!(f, "{rows_per_match} ")?;
2064                }
2065                if let Some(after_match_skip) = after_match_skip {
2066                    write!(f, "{after_match_skip} ")?;
2067                }
2068                write!(f, "PATTERN ({pattern}) ")?;
2069                write!(f, "DEFINE {})", display_comma_separated(symbols))?;
2070                if alias.is_some() {
2071                    write!(f, " AS {}", alias.as_ref().unwrap())?;
2072                }
2073                Ok(())
2074            }
2075            TableFactor::XmlTable {
2076                row_expression,
2077                passing,
2078                columns,
2079                alias,
2080                namespaces,
2081            } => {
2082                write!(f, "XMLTABLE(")?;
2083                if !namespaces.is_empty() {
2084                    write!(
2085                        f,
2086                        "XMLNAMESPACES({}), ",
2087                        display_comma_separated(namespaces)
2088                    )?;
2089                }
2090                write!(
2091                    f,
2092                    "{row_expression}{passing} COLUMNS {columns})",
2093                    columns = display_comma_separated(columns)
2094                )?;
2095                if let Some(alias) = alias {
2096                    write!(f, " AS {alias}")?;
2097                }
2098                Ok(())
2099            }
2100        }
2101    }
2102}
2103
2104#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
2105#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
2106#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
2107pub struct TableAlias {
2108    pub name: Ident,
2109    pub columns: Vec<TableAliasColumnDef>,
2110}
2111
2112impl fmt::Display for TableAlias {
2113    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
2114        write!(f, "{}", self.name)?;
2115        if !self.columns.is_empty() {
2116            write!(f, " ({})", display_comma_separated(&self.columns))?;
2117        }
2118        Ok(())
2119    }
2120}
2121
2122/// SQL column definition in a table expression alias.
2123/// Most of the time, the data type is not specified.
2124/// But some table-valued functions do require specifying the data type.
2125///
2126/// See <https://www.postgresql.org/docs/17/queries-table-expressions.html#QUERIES-TABLEFUNCTIONS>
2127#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
2128#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
2129#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
2130pub struct TableAliasColumnDef {
2131    /// Column name alias
2132    pub name: Ident,
2133    /// Some table-valued functions require specifying the data type in the alias.
2134    pub data_type: Option<DataType>,
2135}
2136
2137impl TableAliasColumnDef {
2138    /// Create a new table alias column definition with only a name and no type
2139    pub fn from_name<S: Into<String>>(name: S) -> Self {
2140        TableAliasColumnDef {
2141            name: Ident::new(name),
2142            data_type: None,
2143        }
2144    }
2145}
2146
2147impl fmt::Display for TableAliasColumnDef {
2148    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
2149        write!(f, "{}", self.name)?;
2150        if let Some(ref data_type) = self.data_type {
2151            write!(f, " {}", data_type)?;
2152        }
2153        Ok(())
2154    }
2155}
2156
2157#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
2158#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
2159#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
2160pub enum TableVersion {
2161    /// When the table version is defined using `FOR SYSTEM_TIME AS OF`.
2162    /// For example: `SELECT * FROM tbl FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)`
2163    ForSystemTimeAsOf(Expr),
2164    /// When the table version is defined using a function.
2165    /// For example: `SELECT * FROM tbl AT(TIMESTAMP => '2020-08-14 09:30:00')`
2166    Function(Expr),
2167}
2168
2169impl Display for TableVersion {
2170    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
2171        match self {
2172            TableVersion::ForSystemTimeAsOf(e) => write!(f, " FOR SYSTEM_TIME AS OF {e}")?,
2173            TableVersion::Function(func) => write!(f, " {func}")?,
2174        }
2175        Ok(())
2176    }
2177}
2178
2179#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
2180#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
2181#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
2182pub struct Join {
2183    pub relation: TableFactor,
2184    /// ClickHouse supports the optional `GLOBAL` keyword before the join operator.
2185    /// See [ClickHouse](https://clickhouse.com/docs/en/sql-reference/statements/select/join)
2186    pub global: bool,
2187    pub join_operator: JoinOperator,
2188}
2189
2190impl fmt::Display for Join {
2191    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
2192        fn prefix(constraint: &JoinConstraint) -> &'static str {
2193            match constraint {
2194                JoinConstraint::Natural => "NATURAL ",
2195                _ => "",
2196            }
2197        }
2198        fn suffix(constraint: &'_ JoinConstraint) -> impl fmt::Display + '_ {
2199            struct Suffix<'a>(&'a JoinConstraint);
2200            impl fmt::Display for Suffix<'_> {
2201                fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
2202                    match self.0 {
2203                        JoinConstraint::On(expr) => write!(f, " ON {expr}"),
2204                        JoinConstraint::Using(attrs) => {
2205                            write!(f, " USING({})", display_comma_separated(attrs))
2206                        }
2207                        _ => Ok(()),
2208                    }
2209                }
2210            }
2211            Suffix(constraint)
2212        }
2213        if self.global {
2214            write!(f, "GLOBAL ")?;
2215        }
2216
2217        match &self.join_operator {
2218            JoinOperator::Join(constraint) => f.write_fmt(format_args!(
2219                "{}JOIN {}{}",
2220                prefix(constraint),
2221                self.relation,
2222                suffix(constraint)
2223            )),
2224            JoinOperator::Inner(constraint) => f.write_fmt(format_args!(
2225                "{}INNER JOIN {}{}",
2226                prefix(constraint),
2227                self.relation,
2228                suffix(constraint)
2229            )),
2230            JoinOperator::Left(constraint) => f.write_fmt(format_args!(
2231                "{}LEFT JOIN {}{}",
2232                prefix(constraint),
2233                self.relation,
2234                suffix(constraint)
2235            )),
2236            JoinOperator::LeftOuter(constraint) => f.write_fmt(format_args!(
2237                "{}LEFT OUTER JOIN {}{}",
2238                prefix(constraint),
2239                self.relation,
2240                suffix(constraint)
2241            )),
2242            JoinOperator::Right(constraint) => f.write_fmt(format_args!(
2243                "{}RIGHT JOIN {}{}",
2244                prefix(constraint),
2245                self.relation,
2246                suffix(constraint)
2247            )),
2248            JoinOperator::RightOuter(constraint) => f.write_fmt(format_args!(
2249                "{}RIGHT OUTER JOIN {}{}",
2250                prefix(constraint),
2251                self.relation,
2252                suffix(constraint)
2253            )),
2254            JoinOperator::FullOuter(constraint) => f.write_fmt(format_args!(
2255                "{}FULL JOIN {}{}",
2256                prefix(constraint),
2257                self.relation,
2258                suffix(constraint)
2259            )),
2260            JoinOperator::CrossJoin => f.write_fmt(format_args!("CROSS JOIN {}", self.relation)),
2261            JoinOperator::Semi(constraint) => f.write_fmt(format_args!(
2262                "{}SEMI JOIN {}{}",
2263                prefix(constraint),
2264                self.relation,
2265                suffix(constraint)
2266            )),
2267            JoinOperator::LeftSemi(constraint) => f.write_fmt(format_args!(
2268                "{}LEFT SEMI JOIN {}{}",
2269                prefix(constraint),
2270                self.relation,
2271                suffix(constraint)
2272            )),
2273            JoinOperator::RightSemi(constraint) => f.write_fmt(format_args!(
2274                "{}RIGHT SEMI JOIN {}{}",
2275                prefix(constraint),
2276                self.relation,
2277                suffix(constraint)
2278            )),
2279            JoinOperator::Anti(constraint) => f.write_fmt(format_args!(
2280                "{}ANTI JOIN {}{}",
2281                prefix(constraint),
2282                self.relation,
2283                suffix(constraint)
2284            )),
2285            JoinOperator::LeftAnti(constraint) => f.write_fmt(format_args!(
2286                "{}LEFT ANTI JOIN {}{}",
2287                prefix(constraint),
2288                self.relation,
2289                suffix(constraint)
2290            )),
2291            JoinOperator::RightAnti(constraint) => f.write_fmt(format_args!(
2292                "{}RIGHT ANTI JOIN {}{}",
2293                prefix(constraint),
2294                self.relation,
2295                suffix(constraint)
2296            )),
2297            JoinOperator::CrossApply => f.write_fmt(format_args!("CROSS APPLY {}", self.relation)),
2298            JoinOperator::OuterApply => f.write_fmt(format_args!("OUTER APPLY {}", self.relation)),
2299            JoinOperator::AsOf {
2300                match_condition,
2301                constraint,
2302            } => f.write_fmt(format_args!(
2303                "ASOF JOIN {} MATCH_CONDITION ({match_condition}){}",
2304                self.relation,
2305                suffix(constraint)
2306            )),
2307            JoinOperator::StraightJoin(constraint) => f.write_fmt(format_args!(
2308                "STRAIGHT_JOIN {}{}",
2309                self.relation,
2310                suffix(constraint)
2311            )),
2312        }
2313    }
2314}
2315
2316#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
2317#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
2318#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
2319pub enum JoinOperator {
2320    Join(JoinConstraint),
2321    Inner(JoinConstraint),
2322    Left(JoinConstraint),
2323    LeftOuter(JoinConstraint),
2324    Right(JoinConstraint),
2325    RightOuter(JoinConstraint),
2326    FullOuter(JoinConstraint),
2327    CrossJoin,
2328    /// SEMI (non-standard)
2329    Semi(JoinConstraint),
2330    /// LEFT SEMI (non-standard)
2331    LeftSemi(JoinConstraint),
2332    /// RIGHT SEMI (non-standard)
2333    RightSemi(JoinConstraint),
2334    /// ANTI (non-standard)
2335    Anti(JoinConstraint),
2336    /// LEFT ANTI (non-standard)
2337    LeftAnti(JoinConstraint),
2338    /// RIGHT ANTI (non-standard)
2339    RightAnti(JoinConstraint),
2340    /// CROSS APPLY (non-standard)
2341    CrossApply,
2342    /// OUTER APPLY (non-standard)
2343    OuterApply,
2344    /// `ASOF` joins are used for joining tables containing time-series data
2345    /// whose timestamp columns do not match exactly.
2346    ///
2347    /// See <https://docs.snowflake.com/en/sql-reference/constructs/asof-join>.
2348    AsOf {
2349        match_condition: Expr,
2350        constraint: JoinConstraint,
2351    },
2352    /// STRAIGHT_JOIN (non-standard)
2353    ///
2354    /// See <https://dev.mysql.com/doc/refman/8.4/en/join.html>.
2355    StraightJoin(JoinConstraint),
2356}
2357
2358#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
2359#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
2360#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
2361pub enum JoinConstraint {
2362    On(Expr),
2363    Using(Vec<ObjectName>),
2364    Natural,
2365    None,
2366}
2367
2368#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
2369#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
2370#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
2371pub enum OrderByKind {
2372    /// ALL syntax of [DuckDB] and [ClickHouse].
2373    ///
2374    /// [DuckDB]:  <https://duckdb.org/docs/sql/query_syntax/orderby>
2375    /// [ClickHouse]: <https://clickhouse.com/docs/en/sql-reference/statements/select/order-by>
2376    All(OrderByOptions),
2377
2378    /// Expressions
2379    Expressions(Vec<OrderByExpr>),
2380}
2381
2382#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
2383#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
2384#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
2385pub struct OrderBy {
2386    pub kind: OrderByKind,
2387
2388    /// Optional: `INTERPOLATE`
2389    /// Supported by [ClickHouse syntax]
2390    pub interpolate: Option<Interpolate>,
2391}
2392
2393impl fmt::Display for OrderBy {
2394    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
2395        write!(f, "ORDER BY")?;
2396        match &self.kind {
2397            OrderByKind::Expressions(exprs) => {
2398                write!(f, " {}", display_comma_separated(exprs))?;
2399            }
2400            OrderByKind::All(all) => {
2401                write!(f, " ALL{}", all)?;
2402            }
2403        }
2404
2405        if let Some(ref interpolate) = self.interpolate {
2406            match &interpolate.exprs {
2407                Some(exprs) => write!(f, " INTERPOLATE ({})", display_comma_separated(exprs))?,
2408                None => write!(f, " INTERPOLATE")?,
2409            }
2410        }
2411
2412        Ok(())
2413    }
2414}
2415
2416/// An `ORDER BY` expression
2417#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
2418#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
2419#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
2420pub struct OrderByExpr {
2421    pub expr: Expr,
2422    pub options: OrderByOptions,
2423    /// Optional: `WITH FILL`
2424    /// Supported by [ClickHouse syntax]: <https://clickhouse.com/docs/en/sql-reference/statements/select/order-by#order-by-expr-with-fill-modifier>
2425    pub with_fill: Option<WithFill>,
2426}
2427
2428impl fmt::Display for OrderByExpr {
2429    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
2430        write!(f, "{}{}", self.expr, self.options)?;
2431        if let Some(ref with_fill) = self.with_fill {
2432            write!(f, " {}", with_fill)?
2433        }
2434        Ok(())
2435    }
2436}
2437
2438/// ClickHouse `WITH FILL` modifier for `ORDER BY` clause.
2439/// Supported by [ClickHouse syntax]
2440///
2441/// [ClickHouse syntax]: <https://clickhouse.com/docs/en/sql-reference/statements/select/order-by#order-by-expr-with-fill-modifier>
2442#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
2443#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
2444#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
2445pub struct WithFill {
2446    pub from: Option<Expr>,
2447    pub to: Option<Expr>,
2448    pub step: Option<Expr>,
2449}
2450
2451impl fmt::Display for WithFill {
2452    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
2453        write!(f, "WITH FILL")?;
2454        if let Some(ref from) = self.from {
2455            write!(f, " FROM {}", from)?;
2456        }
2457        if let Some(ref to) = self.to {
2458            write!(f, " TO {}", to)?;
2459        }
2460        if let Some(ref step) = self.step {
2461            write!(f, " STEP {}", step)?;
2462        }
2463        Ok(())
2464    }
2465}
2466
2467/// ClickHouse `INTERPOLATE` clause for use in `ORDER BY` clause when using `WITH FILL` modifier.
2468/// Supported by [ClickHouse syntax]
2469///
2470/// [ClickHouse syntax]: <https://clickhouse.com/docs/en/sql-reference/statements/select/order-by#order-by-expr-with-fill-modifier>
2471#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
2472#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
2473#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
2474pub struct InterpolateExpr {
2475    pub column: Ident,
2476    pub expr: Option<Expr>,
2477}
2478
2479#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
2480#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
2481#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
2482pub struct Interpolate {
2483    pub exprs: Option<Vec<InterpolateExpr>>,
2484}
2485
2486impl fmt::Display for InterpolateExpr {
2487    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
2488        write!(f, "{}", self.column)?;
2489        if let Some(ref expr) = self.expr {
2490            write!(f, " AS {}", expr)?;
2491        }
2492        Ok(())
2493    }
2494}
2495
2496#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
2497#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
2498#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
2499pub struct OrderByOptions {
2500    /// Optional `ASC` or `DESC`
2501    pub asc: Option<bool>,
2502    /// Optional `NULLS FIRST` or `NULLS LAST`
2503    pub nulls_first: Option<bool>,
2504}
2505
2506impl fmt::Display for OrderByOptions {
2507    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
2508        match self.asc {
2509            Some(true) => write!(f, " ASC")?,
2510            Some(false) => write!(f, " DESC")?,
2511            None => (),
2512        }
2513        match self.nulls_first {
2514            Some(true) => write!(f, " NULLS FIRST")?,
2515            Some(false) => write!(f, " NULLS LAST")?,
2516            None => (),
2517        }
2518        Ok(())
2519    }
2520}
2521
2522#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
2523#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
2524#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
2525pub enum LimitClause {
2526    /// Standard SQL syntax
2527    ///
2528    /// `LIMIT <limit> [BY <expr>,<expr>,...] [OFFSET <offset>]`
2529    LimitOffset {
2530        /// `LIMIT { <N> | ALL }`
2531        limit: Option<Expr>,
2532        /// `OFFSET <N> [ { ROW | ROWS } ]`
2533        offset: Option<Offset>,
2534        /// `BY { <expr>,<expr>,... } }`
2535        ///
2536        /// [ClickHouse](https://clickhouse.com/docs/sql-reference/statements/select/limit-by)
2537        limit_by: Vec<Expr>,
2538    },
2539    /// [MySQL]-specific syntax; the order of expressions is reversed.
2540    ///
2541    /// `LIMIT <offset>, <limit>`
2542    ///
2543    /// [MySQL]: https://dev.mysql.com/doc/refman/8.4/en/select.html
2544    OffsetCommaLimit { offset: Expr, limit: Expr },
2545}
2546
2547impl fmt::Display for LimitClause {
2548    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
2549        match self {
2550            LimitClause::LimitOffset {
2551                limit,
2552                limit_by,
2553                offset,
2554            } => {
2555                if let Some(ref limit) = limit {
2556                    write!(f, " LIMIT {limit}")?;
2557                }
2558                if let Some(ref offset) = offset {
2559                    write!(f, " {offset}")?;
2560                }
2561                if !limit_by.is_empty() {
2562                    debug_assert!(limit.is_some());
2563                    write!(f, " BY {}", display_separated(limit_by, ", "))?;
2564                }
2565                Ok(())
2566            }
2567            LimitClause::OffsetCommaLimit { offset, limit } => {
2568                write!(f, " LIMIT {}, {}", offset, limit)
2569            }
2570        }
2571    }
2572}
2573
2574#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
2575#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
2576#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
2577pub struct Offset {
2578    pub value: Expr,
2579    pub rows: OffsetRows,
2580}
2581
2582impl fmt::Display for Offset {
2583    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
2584        write!(f, "OFFSET {}{}", self.value, self.rows)
2585    }
2586}
2587
2588/// Stores the keyword after `OFFSET <number>`
2589#[derive(Debug, Copy, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
2590#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
2591#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
2592pub enum OffsetRows {
2593    /// Omitting ROW/ROWS is non-standard MySQL quirk.
2594    None,
2595    Row,
2596    Rows,
2597}
2598
2599impl fmt::Display for OffsetRows {
2600    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
2601        match self {
2602            OffsetRows::None => Ok(()),
2603            OffsetRows::Row => write!(f, " ROW"),
2604            OffsetRows::Rows => write!(f, " ROWS"),
2605        }
2606    }
2607}
2608
2609/// Pipe syntax, first introduced in Google BigQuery.
2610/// Example:
2611///
2612/// ```sql
2613/// FROM Produce
2614/// |> WHERE sales > 0
2615/// |> AGGREGATE SUM(sales) AS total_sales, COUNT(*) AS num_sales
2616///    GROUP BY item;
2617/// ```
2618///
2619/// See <https://cloud.google.com/bigquery/docs/reference/standard-sql/pipe-syntax#pipe_syntax>
2620#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
2621#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
2622#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
2623pub enum PipeOperator {
2624    /// Limits the number of rows to return in a query, with an optional OFFSET clause to skip over rows.
2625    ///
2626    /// Syntax: `|> LIMIT <n> [OFFSET <m>]`
2627    ///
2628    /// See more at <https://cloud.google.com/bigquery/docs/reference/standard-sql/pipe-syntax#limit_pipe_operator>
2629    Limit { expr: Expr, offset: Option<Expr> },
2630    /// Filters the results of the input table.
2631    ///
2632    /// Syntax: `|> WHERE <condition>`
2633    ///
2634    /// See more at <https://cloud.google.com/bigquery/docs/reference/standard-sql/pipe-syntax#where_pipe_operator>
2635    Where { expr: Expr },
2636    /// `ORDER BY <expr> [ASC|DESC], ...`
2637    OrderBy { exprs: Vec<OrderByExpr> },
2638    /// Produces a new table with the listed columns, similar to the outermost SELECT clause in a table subquery in standard syntax.
2639    ///
2640    /// Syntax `|> SELECT <expr> [[AS] alias], ...`
2641    ///
2642    /// See more at <https://cloud.google.com/bigquery/docs/reference/standard-sql/pipe-syntax#select_pipe_operator>
2643    Select { exprs: Vec<SelectItem> },
2644    /// Propagates the existing table and adds computed columns, similar to SELECT *, new_column in standard syntax.
2645    ///
2646    /// Syntax: `|> EXTEND <expr> [[AS] alias], ...`
2647    ///
2648    /// See more at <https://cloud.google.com/bigquery/docs/reference/standard-sql/pipe-syntax#extend_pipe_operator>
2649    Extend { exprs: Vec<SelectItem> },
2650    /// Replaces the value of a column in the current table, similar to SELECT * REPLACE (expression AS column) in standard syntax.
2651    ///
2652    /// Syntax: `|> SET <column> = <expression>, ...`
2653    ///
2654    /// See more at <https://cloud.google.com/bigquery/docs/reference/standard-sql/pipe-syntax#set_pipe_operator>
2655    Set { assignments: Vec<Assignment> },
2656    /// Removes listed columns from the current table, similar to SELECT * EXCEPT (column) in standard syntax.
2657    ///
2658    /// Syntax: `|> DROP <column>, ...`
2659    ///
2660    /// See more at <https://cloud.google.com/bigquery/docs/reference/standard-sql/pipe-syntax#drop_pipe_operator>
2661    Drop { columns: Vec<Ident> },
2662    /// Introduces a table alias for the input table, similar to applying the AS alias clause on a table subquery in standard syntax.
2663    ///
2664    /// Syntax: `|> AS <alias>`
2665    ///
2666    /// See more at <https://cloud.google.com/bigquery/docs/reference/standard-sql/pipe-syntax#as_pipe_operator>
2667    As { alias: Ident },
2668    /// Performs aggregation on data across grouped rows or an entire table.
2669    ///
2670    /// Syntax: `|> AGGREGATE <agg_expr> [[AS] alias], ...`
2671    ///
2672    /// Syntax:
2673    /// ```norust
2674    /// |> AGGREGATE [<agg_expr> [[AS] alias], ...]
2675    /// GROUP BY <grouping_expr> [AS alias], ...
2676    /// ```
2677    ///
2678    /// See more at <https://cloud.google.com/bigquery/docs/reference/standard-sql/pipe-syntax#aggregate_pipe_operator>
2679    Aggregate {
2680        full_table_exprs: Vec<ExprWithAliasAndOrderBy>,
2681        group_by_expr: Vec<ExprWithAliasAndOrderBy>,
2682    },
2683    /// Selects a random sample of rows from the input table.
2684    /// Syntax: `|> TABLESAMPLE SYSTEM (10 PERCENT)
2685    /// See more at <https://cloud.google.com/bigquery/docs/reference/standard-sql/pipe-syntax#tablesample_pipe_operator>
2686    TableSample { sample: Box<TableSample> },
2687}
2688
2689impl fmt::Display for PipeOperator {
2690    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
2691        match self {
2692            PipeOperator::Select { exprs } => {
2693                write!(f, "SELECT {}", display_comma_separated(exprs.as_slice()))
2694            }
2695            PipeOperator::Extend { exprs } => {
2696                write!(f, "EXTEND {}", display_comma_separated(exprs.as_slice()))
2697            }
2698            PipeOperator::Set { assignments } => {
2699                write!(f, "SET {}", display_comma_separated(assignments.as_slice()))
2700            }
2701            PipeOperator::Drop { columns } => {
2702                write!(f, "DROP {}", display_comma_separated(columns.as_slice()))
2703            }
2704            PipeOperator::As { alias } => {
2705                write!(f, "AS {}", alias)
2706            }
2707            PipeOperator::Limit { expr, offset } => {
2708                write!(f, "LIMIT {}", expr)?;
2709                if let Some(offset) = offset {
2710                    write!(f, " OFFSET {}", offset)?;
2711                }
2712                Ok(())
2713            }
2714            PipeOperator::Aggregate {
2715                full_table_exprs,
2716                group_by_expr,
2717            } => {
2718                write!(f, "AGGREGATE")?;
2719                if !full_table_exprs.is_empty() {
2720                    write!(
2721                        f,
2722                        " {}",
2723                        display_comma_separated(full_table_exprs.as_slice())
2724                    )?;
2725                }
2726                if !group_by_expr.is_empty() {
2727                    write!(f, " GROUP BY {}", display_comma_separated(group_by_expr))?;
2728                }
2729                Ok(())
2730            }
2731
2732            PipeOperator::Where { expr } => {
2733                write!(f, "WHERE {}", expr)
2734            }
2735            PipeOperator::OrderBy { exprs } => {
2736                write!(f, "ORDER BY {}", display_comma_separated(exprs.as_slice()))
2737            }
2738
2739            PipeOperator::TableSample { sample } => {
2740                write!(f, "{}", sample)
2741            }
2742        }
2743    }
2744}
2745
2746#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
2747#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
2748#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
2749pub struct Fetch {
2750    pub with_ties: bool,
2751    pub percent: bool,
2752    pub quantity: Option<Expr>,
2753}
2754
2755impl fmt::Display for Fetch {
2756    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
2757        let extension = if self.with_ties { "WITH TIES" } else { "ONLY" };
2758        if let Some(ref quantity) = self.quantity {
2759            let percent = if self.percent { " PERCENT" } else { "" };
2760            write!(f, "FETCH FIRST {quantity}{percent} ROWS {extension}")
2761        } else {
2762            write!(f, "FETCH FIRST ROWS {extension}")
2763        }
2764    }
2765}
2766
2767#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
2768#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
2769#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
2770pub struct LockClause {
2771    pub lock_type: LockType,
2772    pub of: Option<ObjectName>,
2773    pub nonblock: Option<NonBlock>,
2774}
2775
2776impl fmt::Display for LockClause {
2777    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
2778        write!(f, "FOR {}", &self.lock_type)?;
2779        if let Some(ref of) = self.of {
2780            write!(f, " OF {of}")?;
2781        }
2782        if let Some(ref nb) = self.nonblock {
2783            write!(f, " {nb}")?;
2784        }
2785        Ok(())
2786    }
2787}
2788
2789#[derive(Debug, Copy, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
2790#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
2791#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
2792pub enum LockType {
2793    Share,
2794    Update,
2795}
2796
2797impl fmt::Display for LockType {
2798    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
2799        let select_lock = match self {
2800            LockType::Share => "SHARE",
2801            LockType::Update => "UPDATE",
2802        };
2803        write!(f, "{select_lock}")
2804    }
2805}
2806
2807#[derive(Debug, Copy, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
2808#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
2809#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
2810pub enum NonBlock {
2811    Nowait,
2812    SkipLocked,
2813}
2814
2815impl fmt::Display for NonBlock {
2816    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
2817        let nonblock = match self {
2818            NonBlock::Nowait => "NOWAIT",
2819            NonBlock::SkipLocked => "SKIP LOCKED",
2820        };
2821        write!(f, "{nonblock}")
2822    }
2823}
2824
2825#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
2826#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
2827#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
2828pub enum Distinct {
2829    /// DISTINCT
2830    Distinct,
2831
2832    /// DISTINCT ON({column names})
2833    On(Vec<Expr>),
2834}
2835
2836impl fmt::Display for Distinct {
2837    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
2838        match self {
2839            Distinct::Distinct => write!(f, "DISTINCT"),
2840            Distinct::On(col_names) => {
2841                let col_names = display_comma_separated(col_names);
2842                write!(f, "DISTINCT ON ({col_names})")
2843            }
2844        }
2845    }
2846}
2847
2848#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
2849#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
2850#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
2851pub struct Top {
2852    /// SQL semantic equivalent of LIMIT but with same structure as FETCH.
2853    /// MSSQL only.
2854    pub with_ties: bool,
2855    /// MSSQL only.
2856    pub percent: bool,
2857    pub quantity: Option<TopQuantity>,
2858}
2859
2860#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
2861#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
2862#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
2863pub enum TopQuantity {
2864    // A parenthesized expression. MSSQL only.
2865    Expr(Expr),
2866    // An unparenthesized integer constant.
2867    Constant(u64),
2868}
2869
2870impl fmt::Display for Top {
2871    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
2872        let extension = if self.with_ties { " WITH TIES" } else { "" };
2873        if let Some(ref quantity) = self.quantity {
2874            let percent = if self.percent { " PERCENT" } else { "" };
2875            match quantity {
2876                TopQuantity::Expr(quantity) => write!(f, "TOP ({quantity}){percent}{extension}"),
2877                TopQuantity::Constant(quantity) => {
2878                    write!(f, "TOP {quantity}{percent}{extension}")
2879                }
2880            }
2881        } else {
2882            write!(f, "TOP{extension}")
2883        }
2884    }
2885}
2886
2887#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
2888#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
2889#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
2890pub struct Values {
2891    /// Was there an explicit ROWs keyword (MySQL)?
2892    /// <https://dev.mysql.com/doc/refman/8.0/en/values.html>
2893    pub explicit_row: bool,
2894    pub rows: Vec<Vec<Expr>>,
2895}
2896
2897impl fmt::Display for Values {
2898    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
2899        f.write_str("VALUES")?;
2900        let prefix = if self.explicit_row { "ROW" } else { "" };
2901        let mut delim = "";
2902        for row in &self.rows {
2903            f.write_str(delim)?;
2904            delim = ",";
2905            SpaceOrNewline.fmt(f)?;
2906            Indent(format_args!("{prefix}({})", display_comma_separated(row))).fmt(f)?;
2907        }
2908        Ok(())
2909    }
2910}
2911
2912#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
2913#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
2914#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
2915pub struct SelectInto {
2916    pub temporary: bool,
2917    pub unlogged: bool,
2918    pub table: bool,
2919    pub name: ObjectName,
2920}
2921
2922impl fmt::Display for SelectInto {
2923    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
2924        let temporary = if self.temporary { " TEMPORARY" } else { "" };
2925        let unlogged = if self.unlogged { " UNLOGGED" } else { "" };
2926        let table = if self.table { " TABLE" } else { "" };
2927
2928        write!(f, "INTO{}{}{} {}", temporary, unlogged, table, self.name)
2929    }
2930}
2931
2932/// ClickHouse supports GROUP BY WITH modifiers(includes ROLLUP|CUBE|TOTALS).
2933/// e.g. GROUP BY year WITH ROLLUP WITH TOTALS
2934///
2935/// [ClickHouse]: <https://clickhouse.com/docs/en/sql-reference/statements/select/group-by#rollup-modifier>
2936#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
2937#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
2938#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
2939pub enum GroupByWithModifier {
2940    Rollup,
2941    Cube,
2942    Totals,
2943    /// Hive supports GROUP BY GROUPING SETS syntax.
2944    /// e.g. GROUP BY year , month GROUPING SETS((year,month),(year),(month))
2945    ///
2946    /// [Hive]: <https://cwiki.apache.org/confluence/pages/viewpage.action?pageId=30151323#EnhancedAggregation,Cube,GroupingandRollup-GROUPINGSETSclause>
2947    GroupingSets(Expr),
2948}
2949
2950impl fmt::Display for GroupByWithModifier {
2951    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
2952        match self {
2953            GroupByWithModifier::Rollup => write!(f, "WITH ROLLUP"),
2954            GroupByWithModifier::Cube => write!(f, "WITH CUBE"),
2955            GroupByWithModifier::Totals => write!(f, "WITH TOTALS"),
2956            GroupByWithModifier::GroupingSets(expr) => {
2957                write!(f, "{expr}")
2958            }
2959        }
2960    }
2961}
2962
2963#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
2964#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
2965#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
2966pub enum GroupByExpr {
2967    /// ALL syntax of [Snowflake], [DuckDB] and [ClickHouse].
2968    ///
2969    /// [Snowflake]: <https://docs.snowflake.com/en/sql-reference/constructs/group-by#label-group-by-all-columns>
2970    /// [DuckDB]:  <https://duckdb.org/docs/sql/query_syntax/groupby.html>
2971    /// [ClickHouse]: <https://clickhouse.com/docs/en/sql-reference/statements/select/group-by#group-by-all>
2972    ///
2973    /// ClickHouse also supports WITH modifiers after GROUP BY ALL and expressions.
2974    ///
2975    /// [ClickHouse]: <https://clickhouse.com/docs/en/sql-reference/statements/select/group-by#rollup-modifier>
2976    All(Vec<GroupByWithModifier>),
2977
2978    /// Expressions
2979    Expressions(Vec<Expr>, Vec<GroupByWithModifier>),
2980}
2981
2982impl fmt::Display for GroupByExpr {
2983    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
2984        match self {
2985            GroupByExpr::All(modifiers) => {
2986                write!(f, "GROUP BY ALL")?;
2987                if !modifiers.is_empty() {
2988                    write!(f, " {}", display_separated(modifiers, " "))?;
2989                }
2990                Ok(())
2991            }
2992            GroupByExpr::Expressions(col_names, modifiers) => {
2993                f.write_str("GROUP BY")?;
2994                SpaceOrNewline.fmt(f)?;
2995                Indent(display_comma_separated(col_names)).fmt(f)?;
2996                if !modifiers.is_empty() {
2997                    write!(f, " {}", display_separated(modifiers, " "))?;
2998                }
2999                Ok(())
3000            }
3001        }
3002    }
3003}
3004
3005/// FORMAT identifier or FORMAT NULL clause, specific to ClickHouse.
3006///
3007/// [ClickHouse]: <https://clickhouse.com/docs/en/sql-reference/statements/select/format>
3008#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
3009#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
3010#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
3011pub enum FormatClause {
3012    Identifier(Ident),
3013    Null,
3014}
3015
3016impl fmt::Display for FormatClause {
3017    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
3018        match self {
3019            FormatClause::Identifier(ident) => write!(f, "FORMAT {}", ident),
3020            FormatClause::Null => write!(f, "FORMAT NULL"),
3021        }
3022    }
3023}
3024
3025/// FORMAT identifier in input context, specific to ClickHouse.
3026///
3027/// [ClickHouse]: <https://clickhouse.com/docs/en/interfaces/formats>
3028#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
3029#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
3030#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
3031pub struct InputFormatClause {
3032    pub ident: Ident,
3033    pub values: Vec<Expr>,
3034}
3035
3036impl fmt::Display for InputFormatClause {
3037    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
3038        write!(f, "FORMAT {}", self.ident)?;
3039
3040        if !self.values.is_empty() {
3041            write!(f, " {}", display_comma_separated(self.values.as_slice()))?;
3042        }
3043
3044        Ok(())
3045    }
3046}
3047
3048/// FOR XML or FOR JSON clause, specific to MSSQL
3049/// (formats the output of a query as XML or JSON)
3050#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
3051#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
3052#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
3053pub enum ForClause {
3054    Browse,
3055    Json {
3056        for_json: ForJson,
3057        root: Option<String>,
3058        include_null_values: bool,
3059        without_array_wrapper: bool,
3060    },
3061    Xml {
3062        for_xml: ForXml,
3063        elements: bool,
3064        binary_base64: bool,
3065        root: Option<String>,
3066        r#type: bool,
3067    },
3068}
3069
3070impl fmt::Display for ForClause {
3071    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
3072        match self {
3073            ForClause::Browse => write!(f, "FOR BROWSE"),
3074            ForClause::Json {
3075                for_json,
3076                root,
3077                include_null_values,
3078                without_array_wrapper,
3079            } => {
3080                write!(f, "FOR JSON ")?;
3081                write!(f, "{}", for_json)?;
3082                if let Some(root) = root {
3083                    write!(f, ", ROOT('{}')", root)?;
3084                }
3085                if *include_null_values {
3086                    write!(f, ", INCLUDE_NULL_VALUES")?;
3087                }
3088                if *without_array_wrapper {
3089                    write!(f, ", WITHOUT_ARRAY_WRAPPER")?;
3090                }
3091                Ok(())
3092            }
3093            ForClause::Xml {
3094                for_xml,
3095                elements,
3096                binary_base64,
3097                root,
3098                r#type,
3099            } => {
3100                write!(f, "FOR XML ")?;
3101                write!(f, "{}", for_xml)?;
3102                if *binary_base64 {
3103                    write!(f, ", BINARY BASE64")?;
3104                }
3105                if *r#type {
3106                    write!(f, ", TYPE")?;
3107                }
3108                if let Some(root) = root {
3109                    write!(f, ", ROOT('{}')", root)?;
3110                }
3111                if *elements {
3112                    write!(f, ", ELEMENTS")?;
3113                }
3114                Ok(())
3115            }
3116        }
3117    }
3118}
3119
3120#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
3121#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
3122#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
3123pub enum ForXml {
3124    Raw(Option<String>),
3125    Auto,
3126    Explicit,
3127    Path(Option<String>),
3128}
3129
3130impl fmt::Display for ForXml {
3131    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
3132        match self {
3133            ForXml::Raw(root) => {
3134                write!(f, "RAW")?;
3135                if let Some(root) = root {
3136                    write!(f, "('{}')", root)?;
3137                }
3138                Ok(())
3139            }
3140            ForXml::Auto => write!(f, "AUTO"),
3141            ForXml::Explicit => write!(f, "EXPLICIT"),
3142            ForXml::Path(root) => {
3143                write!(f, "PATH")?;
3144                if let Some(root) = root {
3145                    write!(f, "('{}')", root)?;
3146                }
3147                Ok(())
3148            }
3149        }
3150    }
3151}
3152
3153#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
3154#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
3155#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
3156pub enum ForJson {
3157    Auto,
3158    Path,
3159}
3160
3161impl fmt::Display for ForJson {
3162    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
3163        match self {
3164            ForJson::Auto => write!(f, "AUTO"),
3165            ForJson::Path => write!(f, "PATH"),
3166        }
3167    }
3168}
3169
3170/// A single column definition in MySQL's `JSON_TABLE` table valued function.
3171///
3172/// See
3173/// - [MySQL's JSON_TABLE documentation](https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html#function_json-table)
3174/// - [Oracle's JSON_TABLE documentation](https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/JSON_TABLE.html)
3175/// - [MariaDB's JSON_TABLE documentation](https://mariadb.com/kb/en/json_table/)
3176///
3177/// ```sql
3178/// SELECT *
3179/// FROM JSON_TABLE(
3180///     '["a", "b"]',
3181///     '$[*]' COLUMNS (
3182///         name FOR ORDINALITY,
3183///         value VARCHAR(20) PATH '$',
3184///         NESTED PATH '$[*]' COLUMNS (
3185///             value VARCHAR(20) PATH '$'
3186///         )
3187///     )
3188/// ) AS jt;
3189/// ```
3190#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
3191#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
3192#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
3193pub enum JsonTableColumn {
3194    /// A named column with a JSON path
3195    Named(JsonTableNamedColumn),
3196    /// The FOR ORDINALITY column, which is a special column that returns the index of the current row in a JSON array.
3197    ForOrdinality(Ident),
3198    /// A set of nested columns, which extracts data from a nested JSON array.
3199    Nested(JsonTableNestedColumn),
3200}
3201
3202impl fmt::Display for JsonTableColumn {
3203    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
3204        match self {
3205            JsonTableColumn::Named(json_table_named_column) => {
3206                write!(f, "{json_table_named_column}")
3207            }
3208            JsonTableColumn::ForOrdinality(ident) => write!(f, "{} FOR ORDINALITY", ident),
3209            JsonTableColumn::Nested(json_table_nested_column) => {
3210                write!(f, "{json_table_nested_column}")
3211            }
3212        }
3213    }
3214}
3215
3216/// A nested column in a JSON_TABLE column list
3217///
3218/// See <https://mariadb.com/kb/en/json_table/#nested-paths>
3219#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
3220#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
3221#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
3222pub struct JsonTableNestedColumn {
3223    pub path: Value,
3224    pub columns: Vec<JsonTableColumn>,
3225}
3226
3227impl fmt::Display for JsonTableNestedColumn {
3228    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
3229        write!(
3230            f,
3231            "NESTED PATH {} COLUMNS ({})",
3232            self.path,
3233            display_comma_separated(&self.columns)
3234        )
3235    }
3236}
3237
3238/// A single column definition in MySQL's `JSON_TABLE` table valued function.
3239///
3240/// See <https://mariadb.com/kb/en/json_table/#path-columns>
3241///
3242/// ```sql
3243///         value VARCHAR(20) PATH '$'
3244/// ```
3245#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
3246#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
3247#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
3248pub struct JsonTableNamedColumn {
3249    /// The name of the column to be extracted.
3250    pub name: Ident,
3251    /// The type of the column to be extracted.
3252    pub r#type: DataType,
3253    /// The path to the column to be extracted. Must be a literal string.
3254    pub path: Value,
3255    /// true if the column is a boolean set to true if the given path exists
3256    pub exists: bool,
3257    /// The empty handling clause of the column
3258    pub on_empty: Option<JsonTableColumnErrorHandling>,
3259    /// The error handling clause of the column
3260    pub on_error: Option<JsonTableColumnErrorHandling>,
3261}
3262
3263impl fmt::Display for JsonTableNamedColumn {
3264    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
3265        write!(
3266            f,
3267            "{} {}{} PATH {}",
3268            self.name,
3269            self.r#type,
3270            if self.exists { " EXISTS" } else { "" },
3271            self.path
3272        )?;
3273        if let Some(on_empty) = &self.on_empty {
3274            write!(f, " {} ON EMPTY", on_empty)?;
3275        }
3276        if let Some(on_error) = &self.on_error {
3277            write!(f, " {} ON ERROR", on_error)?;
3278        }
3279        Ok(())
3280    }
3281}
3282
3283/// Stores the error handling clause of a `JSON_TABLE` table valued function:
3284/// {NULL | DEFAULT json_string | ERROR} ON {ERROR | EMPTY }
3285#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
3286#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
3287#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
3288pub enum JsonTableColumnErrorHandling {
3289    Null,
3290    Default(Value),
3291    Error,
3292}
3293
3294impl fmt::Display for JsonTableColumnErrorHandling {
3295    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
3296        match self {
3297            JsonTableColumnErrorHandling::Null => write!(f, "NULL"),
3298            JsonTableColumnErrorHandling::Default(json_string) => {
3299                write!(f, "DEFAULT {}", json_string)
3300            }
3301            JsonTableColumnErrorHandling::Error => write!(f, "ERROR"),
3302        }
3303    }
3304}
3305
3306/// A single column definition in MSSQL's `OPENJSON WITH` clause.
3307///
3308/// ```sql
3309/// colName type [ column_path ] [ AS JSON ]
3310/// ```
3311///
3312/// Reference: <https://learn.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql?view=sql-server-ver16#syntax>
3313#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
3314#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
3315#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
3316pub struct OpenJsonTableColumn {
3317    /// The name of the column to be extracted.
3318    pub name: Ident,
3319    /// The type of the column to be extracted.
3320    pub r#type: DataType,
3321    /// The path to the column to be extracted. Must be a literal string.
3322    pub path: Option<String>,
3323    /// The `AS JSON` option.
3324    pub as_json: bool,
3325}
3326
3327impl fmt::Display for OpenJsonTableColumn {
3328    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
3329        write!(f, "{} {}", self.name, self.r#type)?;
3330        if let Some(path) = &self.path {
3331            write!(f, " '{}'", value::escape_single_quote_string(path))?;
3332        }
3333        if self.as_json {
3334            write!(f, " AS JSON")?;
3335        }
3336        Ok(())
3337    }
3338}
3339
3340/// BigQuery supports ValueTables which have 2 modes:
3341/// `SELECT [ALL | DISTINCT] AS STRUCT`
3342/// `SELECT [ALL | DISTINCT] AS VALUE`
3343///
3344/// <https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#value_tables>
3345/// <https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#select_list>
3346#[derive(Debug, Copy, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
3347#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
3348#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
3349pub enum ValueTableMode {
3350    AsStruct,
3351    AsValue,
3352    DistinctAsStruct,
3353    DistinctAsValue,
3354}
3355
3356impl fmt::Display for ValueTableMode {
3357    fn fmt(&self, f: &mut fmt::Formatter) -> fmt::Result {
3358        match self {
3359            ValueTableMode::AsStruct => write!(f, "AS STRUCT"),
3360            ValueTableMode::AsValue => write!(f, "AS VALUE"),
3361            ValueTableMode::DistinctAsStruct => write!(f, "DISTINCT AS STRUCT"),
3362            ValueTableMode::DistinctAsValue => write!(f, "DISTINCT AS VALUE"),
3363        }
3364    }
3365}
3366
3367/// The `FROM` clause of an `UPDATE TABLE` statement
3368#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
3369#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
3370#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
3371pub enum UpdateTableFromKind {
3372    /// Update Statement where the 'FROM' clause is before the 'SET' keyword (Supported by Snowflake)
3373    /// For Example: `UPDATE FROM t1 SET t1.name='aaa'`
3374    BeforeSet(Vec<TableWithJoins>),
3375    /// Update Statement where the 'FROM' clause is after the 'SET' keyword (Which is the standard way)
3376    /// For Example: `UPDATE SET t1.name='aaa' FROM t1`
3377    AfterSet(Vec<TableWithJoins>),
3378}
3379
3380/// Defines the options for an XmlTable column: Named or ForOrdinality
3381#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
3382#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
3383#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
3384pub enum XmlTableColumnOption {
3385    /// A named column with a type, optional path, and default value.
3386    NamedInfo {
3387        /// The type of the column to be extracted.
3388        r#type: DataType,
3389        /// The path to the column to be extracted. If None, defaults to the column name.
3390        path: Option<Expr>,
3391        /// Default value if path does not match
3392        default: Option<Expr>,
3393        /// Whether the column is nullable (NULL=true, NOT NULL=false)
3394        nullable: bool,
3395    },
3396    /// The FOR ORDINALITY marker
3397    ForOrdinality,
3398}
3399
3400/// A single column definition in XMLTABLE
3401///
3402/// ```sql
3403/// COLUMNS
3404///     id int PATH '@id',
3405///     ordinality FOR ORDINALITY,
3406///     "COUNTRY_NAME" text,
3407///     country_id text PATH 'COUNTRY_ID',
3408///     size_sq_km float PATH 'SIZE[@unit = "sq_km"]',
3409///     size_other text PATH 'concat(SIZE[@unit!="sq_km"], " ", SIZE[@unit!="sq_km"]/@unit)',
3410///     premier_name text PATH 'PREMIER_NAME' DEFAULT 'not specified'
3411/// ```
3412#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
3413#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
3414#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
3415pub struct XmlTableColumn {
3416    /// The name of the column.
3417    pub name: Ident,
3418    /// Column options: type/path/default or FOR ORDINALITY
3419    pub option: XmlTableColumnOption,
3420}
3421
3422impl fmt::Display for XmlTableColumn {
3423    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
3424        write!(f, "{}", self.name)?;
3425        match &self.option {
3426            XmlTableColumnOption::NamedInfo {
3427                r#type,
3428                path,
3429                default,
3430                nullable,
3431            } => {
3432                write!(f, " {}", r#type)?;
3433                if let Some(p) = path {
3434                    write!(f, " PATH {}", p)?;
3435                }
3436                if let Some(d) = default {
3437                    write!(f, " DEFAULT {}", d)?;
3438                }
3439                if !*nullable {
3440                    write!(f, " NOT NULL")?;
3441                }
3442                Ok(())
3443            }
3444            XmlTableColumnOption::ForOrdinality => {
3445                write!(f, " FOR ORDINALITY")
3446            }
3447        }
3448    }
3449}
3450
3451/// Argument passed in the XMLTABLE PASSING clause
3452#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
3453#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
3454#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
3455pub struct XmlPassingArgument {
3456    pub expr: Expr,
3457    pub alias: Option<Ident>,
3458    pub by_value: bool, // True if BY VALUE is specified
3459}
3460
3461impl fmt::Display for XmlPassingArgument {
3462    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
3463        if self.by_value {
3464            write!(f, "BY VALUE ")?;
3465        }
3466        write!(f, "{}", self.expr)?;
3467        if let Some(alias) = &self.alias {
3468            write!(f, " AS {}", alias)?;
3469        }
3470        Ok(())
3471    }
3472}
3473
3474/// The PASSING clause for XMLTABLE
3475#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
3476#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
3477#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
3478pub struct XmlPassingClause {
3479    pub arguments: Vec<XmlPassingArgument>,
3480}
3481
3482impl fmt::Display for XmlPassingClause {
3483    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
3484        if !self.arguments.is_empty() {
3485            write!(f, " PASSING {}", display_comma_separated(&self.arguments))?;
3486        }
3487        Ok(())
3488    }
3489}
3490
3491/// Represents a single XML namespace definition in the XMLNAMESPACES clause.
3492///
3493/// `namespace_uri AS namespace_name`
3494#[derive(Debug, Clone, PartialEq, PartialOrd, Eq, Ord, Hash)]
3495#[cfg_attr(feature = "visitor", derive(Visit, VisitMut))]
3496#[cfg_attr(feature = "serde", derive(Serialize, Deserialize))]
3497pub struct XmlNamespaceDefinition {
3498    /// The namespace URI (a text expression).
3499    pub uri: Expr,
3500    /// The alias for the namespace (a simple identifier).
3501    pub name: Ident,
3502}
3503
3504impl fmt::Display for XmlNamespaceDefinition {
3505    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
3506        write!(f, "{} AS {}", self.uri, self.name)
3507    }
3508}