polyglot_sql/optimizer/
qualify_columns.rs

1//! Column Qualification Module
2//!
3//! This module provides functionality for qualifying column references in SQL queries,
4//! adding table qualifiers to column names and expanding star expressions.
5//!
6//! Ported from sqlglot's optimizer/qualify_columns.py
7
8use crate::dialects::transform_recursive;
9use crate::dialects::DialectType;
10use crate::expressions::{
11    Alias, BinaryOp, Column, DotAccess, Expression, Identifier, Join, JoinKind, LateralView,
12    Literal, Over, Paren, Select, TableRef, VarArgFunc, With,
13};
14use crate::resolver::{Resolver, ResolverError};
15use crate::schema::{normalize_name, Schema};
16use crate::scope::{build_scope, traverse_scope, Scope};
17use std::cell::RefCell;
18use std::collections::{HashMap, HashSet};
19use thiserror::Error;
20
21/// Errors that can occur during column qualification
22#[derive(Debug, Error, Clone)]
23pub enum QualifyColumnsError {
24    #[error("Unknown table: {0}")]
25    UnknownTable(String),
26
27    #[error("Unknown column: {0}")]
28    UnknownColumn(String),
29
30    #[error("Ambiguous column: {0}")]
31    AmbiguousColumn(String),
32
33    #[error("Cannot automatically join: {0}")]
34    CannotAutoJoin(String),
35
36    #[error("Unknown output column: {0}")]
37    UnknownOutputColumn(String),
38
39    #[error("Column could not be resolved: {column}{for_table}")]
40    ColumnNotResolved { column: String, for_table: String },
41
42    #[error("Resolver error: {0}")]
43    ResolverError(#[from] ResolverError),
44}
45
46/// Result type for column qualification operations
47pub type QualifyColumnsResult<T> = Result<T, QualifyColumnsError>;
48
49/// Options for column qualification
50#[derive(Debug, Clone, Default)]
51pub struct QualifyColumnsOptions {
52    /// Whether to expand references to aliases
53    pub expand_alias_refs: bool,
54    /// Whether to expand star expressions to explicit columns
55    pub expand_stars: bool,
56    /// Whether to infer schema if not provided
57    pub infer_schema: Option<bool>,
58    /// Whether to allow partial qualification
59    pub allow_partial_qualification: bool,
60    /// The dialect for dialect-specific behavior
61    pub dialect: Option<DialectType>,
62}
63
64impl QualifyColumnsOptions {
65    /// Create new options with defaults
66    pub fn new() -> Self {
67        Self {
68            expand_alias_refs: true,
69            expand_stars: true,
70            infer_schema: None,
71            allow_partial_qualification: false,
72            dialect: None,
73        }
74    }
75
76    /// Set whether to expand alias refs
77    pub fn with_expand_alias_refs(mut self, expand: bool) -> Self {
78        self.expand_alias_refs = expand;
79        self
80    }
81
82    /// Set whether to expand stars
83    pub fn with_expand_stars(mut self, expand: bool) -> Self {
84        self.expand_stars = expand;
85        self
86    }
87
88    /// Set the dialect
89    pub fn with_dialect(mut self, dialect: DialectType) -> Self {
90        self.dialect = Some(dialect);
91        self
92    }
93
94    /// Set whether to allow partial qualification
95    pub fn with_allow_partial(mut self, allow: bool) -> Self {
96        self.allow_partial_qualification = allow;
97        self
98    }
99}
100
101/// Rewrite SQL AST to have fully qualified columns.
102///
103/// # Example
104/// ```ignore
105/// // SELECT col FROM tbl => SELECT tbl.col AS col FROM tbl
106/// ```
107///
108/// # Arguments
109/// * `expression` - Expression to qualify
110/// * `schema` - Database schema for column lookup
111/// * `options` - Qualification options
112///
113/// # Returns
114/// The qualified expression
115pub fn qualify_columns(
116    expression: Expression,
117    schema: &dyn Schema,
118    options: &QualifyColumnsOptions,
119) -> QualifyColumnsResult<Expression> {
120    let infer_schema = options.infer_schema.unwrap_or(schema.is_empty());
121    let dialect = options.dialect.or_else(|| schema.dialect());
122    let first_error: RefCell<Option<QualifyColumnsError>> = RefCell::new(None);
123
124    let transformed = transform_recursive(expression, &|node| {
125        if first_error.borrow().is_some() {
126            return Ok(node);
127        }
128
129        match node {
130            Expression::Select(mut select) => {
131                if let Some(with) = &mut select.with {
132                    pushdown_cte_alias_columns_with(with);
133                }
134
135                let scope_expr = Expression::Select(select.clone());
136                let scope = build_scope(&scope_expr);
137                let mut resolver = Resolver::new(&scope, schema, infer_schema);
138
139                // 1. Expand USING → ON before column qualification
140                let column_tables = if first_error.borrow().is_none() {
141                    match expand_using(&mut select, &scope, &mut resolver) {
142                        Ok(ct) => ct,
143                        Err(err) => {
144                            *first_error.borrow_mut() = Some(err);
145                            HashMap::new()
146                        }
147                    }
148                } else {
149                    HashMap::new()
150                };
151
152                // 2. Expand alias references before qualification so same-select
153                // aliases do not get treated as unresolved physical columns.
154                if first_error.borrow().is_none() && options.expand_alias_refs {
155                    if let Err(err) = expand_alias_refs(&mut select, &mut resolver, dialect) {
156                        *first_error.borrow_mut() = Some(err);
157                    }
158                }
159
160                // 3. A parsed `column.field` is indistinguishable from `table.column`.
161                // Normalize an apparent qualifier that resolves as a column before
162                // regular qualification rejects it as an unknown table.
163                if first_error.borrow().is_none() {
164                    if let Err(err) =
165                        normalize_dotted_columns_in_scope(&mut select, &scope, &mut resolver)
166                    {
167                        *first_error.borrow_mut() = Some(err);
168                    }
169                }
170
171                // 4. Qualify columns (add table qualifiers)
172                if first_error.borrow().is_none() {
173                    if let Err(err) = qualify_columns_in_scope(
174                        &mut select,
175                        &scope,
176                        &mut resolver,
177                        options.allow_partial_qualification,
178                    ) {
179                        *first_error.borrow_mut() = Some(err);
180                    }
181                }
182
183                // 5. Expand star expressions (with USING deduplication)
184                if first_error.borrow().is_none() && options.expand_stars {
185                    if let Err(err) =
186                        expand_stars(&mut select, &scope, &mut resolver, &column_tables)
187                    {
188                        *first_error.borrow_mut() = Some(err);
189                    }
190                }
191
192                // 6. Qualify outputs
193                if first_error.borrow().is_none() {
194                    if let Err(err) = qualify_outputs_select(&mut select) {
195                        *first_error.borrow_mut() = Some(err);
196                    }
197                }
198
199                // 7. Expand GROUP BY positional refs
200                if first_error.borrow().is_none() {
201                    if let Err(err) = expand_group_by(&mut select, dialect) {
202                        *first_error.borrow_mut() = Some(err);
203                    }
204                }
205
206                Ok(Expression::Select(select))
207            }
208            _ => Ok(node),
209        }
210    })
211    .map_err(|err| QualifyColumnsError::CannotAutoJoin(err.to_string()))?;
212
213    if let Some(err) = first_error.into_inner() {
214        return Err(err);
215    }
216
217    Ok(transformed)
218}
219
220/// Validate that all columns in an expression are qualified.
221///
222/// # Returns
223/// The expression if valid, or an error if unqualified columns exist.
224pub fn validate_qualify_columns(expression: &Expression) -> QualifyColumnsResult<()> {
225    let mut all_unqualified = Vec::new();
226
227    for scope in traverse_scope(expression) {
228        if let Expression::Select(_) = &scope.expression {
229            // Get unqualified columns from this scope
230            let unqualified = get_unqualified_columns(&scope);
231
232            // Check for external columns that couldn't be resolved
233            let external = get_external_columns(&scope);
234            if !external.is_empty() && !is_correlated_subquery(&scope) {
235                let first = &external[0];
236                let for_table = if first.table.is_some() {
237                    format!(" for table: '{}'", first.table.as_ref().unwrap())
238                } else {
239                    String::new()
240                };
241                return Err(QualifyColumnsError::ColumnNotResolved {
242                    column: first.name.clone(),
243                    for_table,
244                });
245            }
246
247            all_unqualified.extend(unqualified);
248        }
249    }
250
251    if !all_unqualified.is_empty() {
252        let first = &all_unqualified[0];
253        return Err(QualifyColumnsError::AmbiguousColumn(first.name.clone()));
254    }
255
256    Ok(())
257}
258
259/// Get the alias or table name from a table expression in FROM/JOIN context.
260fn get_source_name(expr: &Expression) -> Option<String> {
261    match expr {
262        Expression::Table(t) => Some(
263            t.alias
264                .as_ref()
265                .map(|a| a.name.clone())
266                .unwrap_or_else(|| t.name.name.clone()),
267        ),
268        Expression::Subquery(sq) => sq.alias.as_ref().map(|a| a.name.clone()),
269        Expression::Pivot(pivot) => Some(pivot_source_name(
270            &pivot.this,
271            pivot.alias.as_ref().map(|alias| alias.name.as_str()),
272        )),
273        Expression::Unpivot(unpivot) => Some(pivot_source_name(
274            &unpivot.this,
275            unpivot.alias.as_ref().map(|alias| alias.name.as_str()),
276        )),
277        _ => None,
278    }
279}
280
281fn pivot_source_name(source: &Expression, explicit_alias: Option<&str>) -> String {
282    if let Some(alias) = explicit_alias {
283        return alias.to_string();
284    }
285
286    match source {
287        Expression::Table(table) => table
288            .alias
289            .as_ref()
290            .map(|alias| alias.name.clone())
291            .unwrap_or_else(|| table.name.name.clone()),
292        Expression::Subquery(subquery) => subquery
293            .alias
294            .as_ref()
295            .map(|alias| alias.name.clone())
296            .unwrap_or_else(|| "_0".to_string()),
297        Expression::Paren(paren) => pivot_source_name(&paren.this, explicit_alias),
298        _ => "_0".to_string(),
299    }
300}
301
302/// Get ordered source names from a SELECT's FROM + JOIN clauses.
303/// FROM tables come first, then JOIN tables in declaration order.
304fn get_ordered_source_names(select: &Select) -> Vec<String> {
305    let mut ordered = Vec::new();
306    if let Some(from) = &select.from {
307        for expr in &from.expressions {
308            if let Some(name) = get_source_name(expr) {
309                ordered.push(name);
310            }
311        }
312    }
313    for join in &select.joins {
314        if is_semi_or_anti_join_kind(join.kind) {
315            continue;
316        }
317        if let Some(name) = get_source_name(&join.this) {
318            ordered.push(name);
319        }
320    }
321    ordered
322}
323
324fn is_semi_or_anti_join_kind(kind: JoinKind) -> bool {
325    matches!(
326        kind,
327        JoinKind::Semi
328            | JoinKind::Anti
329            | JoinKind::LeftSemi
330            | JoinKind::LeftAnti
331            | JoinKind::RightSemi
332            | JoinKind::RightAnti
333    )
334}
335
336/// Create a COALESCE expression over qualified columns from the given tables.
337fn make_coalesce(column_name: &str, tables: &[String]) -> Expression {
338    let args: Vec<Expression> = tables
339        .iter()
340        .map(|t| Expression::qualified_column(t.as_str(), column_name))
341        .collect();
342    Expression::Coalesce(Box::new(VarArgFunc {
343        expressions: args,
344        original_name: None,
345        inferred_type: None,
346    }))
347}
348
349/// Expand JOIN USING clauses into ON conditions and track which columns
350/// participate in USING joins for later COALESCE rewriting.
351///
352/// Returns a mapping from column name → ordered list of table names that
353/// participate in USING for that column.
354fn expand_using(
355    select: &mut Select,
356    _scope: &Scope,
357    resolver: &mut Resolver,
358) -> QualifyColumnsResult<HashMap<String, Vec<String>>> {
359    // columns: normalized column name → first source that owns it
360    // (first-seen-wins)
361    let mut columns: HashMap<String, String> = HashMap::new();
362    // Preserve the first-seen column order for deterministic NATURAL JOIN
363    // expansion. HashMap iteration order must not affect the generated USING list.
364    let mut column_order: Vec<String> = Vec::new();
365
366    // column_tables: normalized column name → ordered list of tables that
367    // participate in USING
368    let mut column_tables: HashMap<String, Vec<String>> = HashMap::new();
369
370    // Get non-join source names from FROM clause
371    let join_names: HashSet<String> = select
372        .joins
373        .iter()
374        .filter_map(|j| get_source_name(&j.this))
375        .collect();
376
377    let all_ordered = get_ordered_source_names(select);
378    let mut ordered: Vec<String> = all_ordered
379        .iter()
380        .filter(|name| !join_names.contains(name.as_str()))
381        .cloned()
382        .collect();
383    let mut accumulated_schema_known = true;
384
385    if join_names.is_empty() {
386        return Ok(column_tables);
387    }
388
389    // Helper closure to update columns map from a source
390    fn update_source_columns(
391        source_name: &str,
392        columns: &mut HashMap<String, String>,
393        column_order: &mut Vec<String>,
394        resolver: &mut Resolver,
395    ) -> bool {
396        let Ok(source_cols) = resolver.get_source_columns(source_name) else {
397            return false;
398        };
399        let schema_known = !source_cols.is_empty()
400            && !source_cols
401                .iter()
402                .any(|column| column == "*" || column.is_empty());
403        for col_name in source_cols {
404            let normalized = normalize_column_name(&col_name, resolver.dialect);
405            if let std::collections::hash_map::Entry::Vacant(entry) = columns.entry(normalized) {
406                entry.insert(source_name.to_string());
407                column_order.push(col_name);
408            }
409        }
410        schema_known
411    }
412
413    // Pre-populate columns from FROM (base) sources
414    for source_name in &ordered {
415        accumulated_schema_known &=
416            update_source_columns(source_name, &mut columns, &mut column_order, resolver);
417    }
418
419    for i in 0..select.joins.len() {
420        // Get source_table (most recently seen non-join table)
421        let source_table = ordered.last().cloned().unwrap_or_default();
422        if !source_table.is_empty() {
423            accumulated_schema_known &=
424                update_source_columns(&source_table, &mut columns, &mut column_order, resolver);
425        }
426
427        // Get join_table name and append to ordered
428        let join_table = get_source_name(&select.joins[i].this).unwrap_or_default();
429        ordered.push(join_table.clone());
430
431        let join_columns: Vec<String> =
432            resolver.get_source_columns(&join_table).unwrap_or_default();
433        let star = normalize_column_name("*", resolver.dialect);
434        let right_schema_known = !join_columns.is_empty()
435            && !join_columns
436                .iter()
437                .any(|column| normalize_column_name(column, resolver.dialect) == star);
438
439        // NATURAL JOIN is an implicit USING join over every column common to
440        // the accumulated left side and the current right side. Only expand
441        // when both schemas are known; otherwise preserve NATURAL rather than
442        // guessing join keys.
443        let join_kind = select.joins[i].kind;
444        if select.joins[i].using.is_empty()
445            && matches!(
446                join_kind,
447                JoinKind::Natural
448                    | JoinKind::NaturalLeft
449                    | JoinKind::NaturalRight
450                    | JoinKind::NaturalFull
451            )
452        {
453            let left_schema_known =
454                accumulated_schema_known && !columns.is_empty() && !columns.contains_key(&star);
455
456            if left_schema_known && right_schema_known {
457                let right_columns: HashSet<String> = join_columns
458                    .iter()
459                    .map(|column| normalize_column_name(column, resolver.dialect))
460                    .collect();
461                let implicit_using: Vec<Identifier> = column_order
462                    .iter()
463                    .filter(|column| {
464                        right_columns.contains(&normalize_column_name(column, resolver.dialect))
465                    })
466                    .map(|column| Identifier::new(column))
467                    .collect();
468
469                if !implicit_using.is_empty() {
470                    select.joins[i].using = implicit_using;
471                    select.joins[i].kind = match join_kind {
472                        JoinKind::Natural => JoinKind::Inner,
473                        JoinKind::NaturalLeft => JoinKind::Left,
474                        JoinKind::NaturalRight => JoinKind::Right,
475                        JoinKind::NaturalFull => JoinKind::Full,
476                        _ => unreachable!("checked NATURAL join kind above"),
477                    };
478                }
479            }
480        }
481
482        // Preserve NATURAL joins with unknown schemas or no common columns,
483        // and skip ordinary joins without a USING clause.
484        if select.joins[i].using.is_empty() {
485            accumulated_schema_known &= right_schema_known;
486            continue;
487        }
488
489        let using_identifiers: Vec<String> = select.joins[i]
490            .using
491            .iter()
492            .map(|id| id.name.clone())
493            .collect();
494
495        let using_count = using_identifiers.len();
496        let is_semi_or_anti = matches!(
497            select.joins[i].kind,
498            crate::expressions::JoinKind::Semi
499                | crate::expressions::JoinKind::Anti
500                | crate::expressions::JoinKind::LeftSemi
501                | crate::expressions::JoinKind::LeftAnti
502                | crate::expressions::JoinKind::RightSemi
503                | crate::expressions::JoinKind::RightAnti
504        );
505
506        let mut conditions: Vec<Expression> = Vec::new();
507
508        for identifier in &using_identifiers {
509            let normalized_identifier = normalize_column_name(identifier, resolver.dialect);
510            let table = columns
511                .get(&normalized_identifier)
512                .cloned()
513                .unwrap_or_else(|| source_table.clone());
514
515            // Build LHS of the equality
516            let lhs = if i == 0 || using_count == 1 {
517                // Simple qualified column for first join or single USING column
518                Expression::qualified_column(table.as_str(), identifier.as_str())
519            } else {
520                // For subsequent joins with multiple USING columns,
521                // COALESCE over all previous sources that have this column
522                let coalesce_cols: Vec<String> = ordered[..ordered.len() - 1]
523                    .iter()
524                    .filter(|t| {
525                        resolver
526                            .get_source_columns(t)
527                            .unwrap_or_default()
528                            .iter()
529                            .any(|column| {
530                                normalize_column_name(column, resolver.dialect)
531                                    == normalized_identifier
532                            })
533                    })
534                    .cloned()
535                    .collect();
536
537                if coalesce_cols.len() > 1 {
538                    make_coalesce(identifier, &coalesce_cols)
539                } else {
540                    Expression::qualified_column(table.as_str(), identifier.as_str())
541                }
542            };
543
544            // Build RHS: qualified column from join table
545            let rhs = Expression::qualified_column(join_table.as_str(), identifier.as_str());
546
547            conditions.push(Expression::Eq(Box::new(BinaryOp::new(lhs, rhs))));
548
549            // Track tables for COALESCE rewriting (skip for semi/anti joins)
550            if !is_semi_or_anti {
551                let tables = column_tables
552                    .entry(normalized_identifier)
553                    .or_insert_with(Vec::new);
554                if !tables.contains(&table) {
555                    tables.push(table.clone());
556                }
557                if !tables.contains(&join_table) {
558                    tables.push(join_table.clone());
559                }
560            }
561        }
562
563        // Combine conditions with AND (left fold)
564        let on_condition = conditions
565            .into_iter()
566            .reduce(|acc, cond| Expression::And(Box::new(BinaryOp::new(acc, cond))))
567            .expect("at least one USING column");
568
569        // Set ON condition and clear USING
570        select.joins[i].on = Some(on_condition);
571        select.joins[i].using = vec![];
572        accumulated_schema_known &= right_schema_known;
573    }
574
575    // Phase 2: Rewrite unqualified USING column references to COALESCE
576    if !column_tables.is_empty() {
577        // Rewrite select.expressions (projections)
578        let mut new_expressions = Vec::with_capacity(select.expressions.len());
579        for expr in &select.expressions {
580            match expr {
581                Expression::Column(col) if col.table.is_none() => {
582                    let normalized = normalize_column_name(&col.name.name, resolver.dialect);
583                    let Some(tables) = column_tables.get(&normalized) else {
584                        new_expressions.push(expr.clone());
585                        continue;
586                    };
587                    let coalesce = make_coalesce(&col.name.name, tables);
588                    // Wrap in alias to preserve column name in projections
589                    new_expressions.push(Expression::Alias(Box::new(Alias {
590                        this: coalesce,
591                        alias: col.name.clone(),
592                        column_aliases: vec![],
593                        alias_explicit_as: false,
594                        alias_keyword: None,
595                        pre_alias_comments: vec![],
596                        trailing_comments: vec![],
597                        inferred_type: None,
598                    })));
599                }
600                _ => {
601                    let mut rewritten = expr.clone();
602                    rewrite_using_columns_in_expression(
603                        &mut rewritten,
604                        &column_tables,
605                        resolver.dialect,
606                    );
607                    new_expressions.push(rewritten);
608                }
609            }
610        }
611        select.expressions = new_expressions;
612
613        // Rewrite WHERE
614        if let Some(where_clause) = &mut select.where_clause {
615            rewrite_using_columns_in_expression(
616                &mut where_clause.this,
617                &column_tables,
618                resolver.dialect,
619            );
620        }
621
622        // Rewrite GROUP BY
623        if let Some(group_by) = &mut select.group_by {
624            for expr in &mut group_by.expressions {
625                rewrite_using_columns_in_expression(expr, &column_tables, resolver.dialect);
626            }
627        }
628
629        // Rewrite HAVING
630        if let Some(having) = &mut select.having {
631            rewrite_using_columns_in_expression(&mut having.this, &column_tables, resolver.dialect);
632        }
633
634        // Rewrite QUALIFY
635        if let Some(qualify) = &mut select.qualify {
636            rewrite_using_columns_in_expression(
637                &mut qualify.this,
638                &column_tables,
639                resolver.dialect,
640            );
641        }
642
643        // Rewrite ORDER BY
644        if let Some(order_by) = &mut select.order_by {
645            for ordered in &mut order_by.expressions {
646                rewrite_using_columns_in_expression(
647                    &mut ordered.this,
648                    &column_tables,
649                    resolver.dialect,
650                );
651            }
652        }
653    }
654
655    Ok(column_tables)
656}
657
658/// Recursively replace unqualified USING column references with COALESCE.
659fn rewrite_using_columns_in_expression(
660    expr: &mut Expression,
661    column_tables: &HashMap<String, Vec<String>>,
662    dialect: Option<DialectType>,
663) {
664    let transformed = transform_recursive(expr.clone(), &|node| match node {
665        Expression::Column(col) if col.table.is_none() => {
666            let normalized = normalize_column_name(&col.name.name, dialect);
667            if let Some(tables) = column_tables.get(&normalized) {
668                Ok(make_coalesce(&col.name.name, tables))
669            } else {
670                Ok(Expression::Column(col))
671            }
672        }
673        other => Ok(other),
674    });
675
676    if let Ok(next) = transformed {
677        *expr = next;
678    }
679}
680
681/// Normalize ambiguous two-part column references into struct/JSON field access.
682///
683/// SQL parsers cannot distinguish `table.column` from `column.field` without a
684/// scope and schema. If the apparent table is not a source but resolves as an
685/// unambiguous column in the current scope, rewrite it to a [`DotAccess`] rooted
686/// at the qualified source column. Validation calls this same helper so it uses
687/// exactly the same interpretation as schema-aware analysis and lineage.
688pub(crate) fn normalize_dotted_columns_in_scope(
689    select: &mut Select,
690    scope: &Scope,
691    resolver: &mut Resolver,
692) -> QualifyColumnsResult<()> {
693    for expression in &mut select.expressions {
694        normalize_dotted_columns_in_expression(expression, scope, resolver)?;
695    }
696    if let Some(where_clause) = &mut select.where_clause {
697        normalize_dotted_columns_in_expression(&mut where_clause.this, scope, resolver)?;
698    }
699    if let Some(group_by) = &mut select.group_by {
700        for expression in &mut group_by.expressions {
701            normalize_dotted_columns_in_expression(expression, scope, resolver)?;
702        }
703    }
704    if let Some(having) = &mut select.having {
705        normalize_dotted_columns_in_expression(&mut having.this, scope, resolver)?;
706    }
707    if let Some(qualify) = &mut select.qualify {
708        normalize_dotted_columns_in_expression(&mut qualify.this, scope, resolver)?;
709    }
710    if let Some(order_by) = &mut select.order_by {
711        for ordered in &mut order_by.expressions {
712            normalize_dotted_columns_in_expression(&mut ordered.this, scope, resolver)?;
713        }
714    }
715    for join in &mut select.joins {
716        normalize_dotted_columns_in_expression(&mut join.this, scope, resolver)?;
717        if let Some(on) = &mut join.on {
718            normalize_dotted_columns_in_expression(on, scope, resolver)?;
719        }
720    }
721    Ok(())
722}
723
724pub(crate) fn normalize_dotted_columns_in_expression(
725    expression: &mut Expression,
726    scope: &Scope,
727    resolver: &mut Resolver,
728) -> QualifyColumnsResult<()> {
729    let resolver = RefCell::new(resolver);
730    let transformed = transform_recursive(expression.clone(), &|node| {
731        let Expression::Column(column) = node else {
732            return Ok(node);
733        };
734        let Some(root) = column.table.as_ref() else {
735            return Ok(Expression::Column(column));
736        };
737
738        let root_is_source = scope
739            .sources
740            .keys()
741            .any(|source| source.eq_ignore_ascii_case(&root.name));
742        if root_is_source {
743            return Ok(Expression::Column(column));
744        }
745
746        let Some(source_name) = resolver.borrow_mut().get_table(&root.name) else {
747            return Ok(Expression::Column(column));
748        };
749
750        let root_column = Expression::boxed_column(Column {
751            name: root.clone(),
752            table: Some(Identifier::new(source_name)),
753            join_mark: column.join_mark,
754            trailing_comments: column.trailing_comments.clone(),
755            span: column.span,
756            inferred_type: None,
757        });
758
759        Ok(Expression::Dot(Box::new(DotAccess {
760            this: root_column,
761            field: column.name.clone(),
762            inferred_type: None,
763        })))
764    })
765    .map_err(|error| QualifyColumnsError::CannotAutoJoin(error.to_string()))?;
766
767    *expression = transformed;
768    Ok(())
769}
770
771/// Qualify columns in a scope by adding table qualifiers
772fn qualify_columns_in_scope(
773    select: &mut Select,
774    scope: &Scope,
775    resolver: &mut Resolver,
776    allow_partial: bool,
777) -> QualifyColumnsResult<()> {
778    for expr in &mut select.expressions {
779        qualify_columns_in_expression(expr, scope, resolver, allow_partial)?;
780    }
781    if let Some(where_clause) = &mut select.where_clause {
782        qualify_columns_in_expression(&mut where_clause.this, scope, resolver, allow_partial)?;
783    }
784    if let Some(group_by) = &mut select.group_by {
785        for expr in &mut group_by.expressions {
786            qualify_columns_in_expression(expr, scope, resolver, allow_partial)?;
787        }
788    }
789    if let Some(having) = &mut select.having {
790        qualify_columns_in_expression(&mut having.this, scope, resolver, allow_partial)?;
791    }
792    if let Some(qualify) = &mut select.qualify {
793        qualify_columns_in_expression(&mut qualify.this, scope, resolver, allow_partial)?;
794    }
795    if let Some(order_by) = &mut select.order_by {
796        for ordered in &mut order_by.expressions {
797            qualify_columns_in_expression(&mut ordered.this, scope, resolver, allow_partial)?;
798        }
799    }
800    for join in &mut select.joins {
801        qualify_columns_in_expression(&mut join.this, scope, resolver, allow_partial)?;
802        if let Some(on) = &mut join.on {
803            qualify_columns_in_expression(on, scope, resolver, allow_partial)?;
804        }
805    }
806    Ok(())
807}
808
809/// Expand alias references in a scope.
810///
811/// For example:
812/// `SELECT y.foo AS bar, bar * 2 AS baz FROM y`
813/// becomes:
814/// `SELECT y.foo AS bar, y.foo * 2 AS baz FROM y`
815fn expand_alias_refs(
816    select: &mut Select,
817    _resolver: &mut Resolver,
818    _dialect: Option<DialectType>,
819) -> QualifyColumnsResult<()> {
820    let mut alias_to_expression: HashMap<String, (Expression, usize)> = HashMap::new();
821
822    for (i, expr) in select.expressions.iter_mut().enumerate() {
823        replace_alias_refs_in_expression(expr, &alias_to_expression, false);
824        if let Expression::Alias(alias) = expr {
825            alias_to_expression.insert(alias.alias.name.clone(), (alias.this.clone(), i + 1));
826        }
827    }
828
829    if let Some(where_clause) = &mut select.where_clause {
830        replace_alias_refs_in_expression(&mut where_clause.this, &alias_to_expression, false);
831    }
832    if let Some(group_by) = &mut select.group_by {
833        for expr in &mut group_by.expressions {
834            replace_alias_refs_in_expression(expr, &alias_to_expression, true);
835        }
836    }
837    if let Some(having) = &mut select.having {
838        replace_alias_refs_in_expression(&mut having.this, &alias_to_expression, false);
839    }
840    if let Some(qualify) = &mut select.qualify {
841        replace_alias_refs_in_expression(&mut qualify.this, &alias_to_expression, false);
842    }
843    if let Some(order_by) = &mut select.order_by {
844        for ordered in &mut order_by.expressions {
845            replace_alias_refs_in_expression(&mut ordered.this, &alias_to_expression, false);
846        }
847    }
848
849    Ok(())
850}
851
852/// Expand GROUP BY positional references.
853///
854/// For example:
855/// `SELECT a, b FROM t GROUP BY 1, 2`
856/// becomes:
857/// `SELECT a, b FROM t GROUP BY a, b`
858fn expand_group_by(select: &mut Select, _dialect: Option<DialectType>) -> QualifyColumnsResult<()> {
859    let projections = select.expressions.clone();
860
861    if let Some(group_by) = &mut select.group_by {
862        for group_expr in &mut group_by.expressions {
863            if let Some(index) = positional_reference(group_expr) {
864                let replacement = select_expression_at_position(&projections, index)?;
865                *group_expr = replacement;
866            }
867        }
868    }
869    Ok(())
870}
871
872/// Expand star expressions to explicit column lists, with USING deduplication.
873///
874/// For example:
875/// `SELECT * FROM users`
876/// becomes:
877/// `SELECT users.id, users.name, users.email FROM users`
878///
879/// With USING joins, USING columns appear once as COALESCE and are
880/// deduplicated across sources.
881fn expand_stars(
882    select: &mut Select,
883    _scope: &Scope,
884    resolver: &mut Resolver,
885    column_tables: &HashMap<String, Vec<String>>,
886) -> QualifyColumnsResult<()> {
887    let mut new_selections: Vec<Expression> = Vec::new();
888    let mut has_star = false;
889    let mut coalesced_columns: HashSet<String> = HashSet::new();
890
891    // Use ordered source names (not unordered HashMap keys)
892    let ordered_sources = get_ordered_source_names(select);
893
894    for expr in &select.expressions {
895        match expr {
896            Expression::Star(star) => {
897                has_star = true;
898                if let Some(table) = &star.table {
899                    let table_name = &table.name;
900                    if !ordered_sources.contains(table_name) {
901                        return Err(QualifyColumnsError::UnknownTable(table_name.clone()));
902                    }
903                    if let Ok(columns) = resolver.get_source_columns(table_name) {
904                        if columns.contains(&"*".to_string()) || columns.is_empty() {
905                            return Ok(());
906                        }
907                        for col_name in &columns {
908                            let normalized = normalize_column_name(col_name, resolver.dialect);
909                            if coalesced_columns.contains(&normalized) {
910                                continue;
911                            }
912                            if let Some(tables) = column_tables.get(&normalized) {
913                                if tables.contains(table_name) {
914                                    coalesced_columns.insert(normalized);
915                                    let coalesce = make_coalesce(col_name, tables);
916                                    new_selections.push(Expression::Alias(Box::new(Alias {
917                                        this: coalesce,
918                                        alias: Identifier::new(col_name),
919                                        column_aliases: vec![],
920                                        alias_explicit_as: false,
921                                        alias_keyword: None,
922                                        pre_alias_comments: vec![],
923                                        trailing_comments: vec![],
924                                        inferred_type: None,
925                                    })));
926                                    continue;
927                                }
928                            }
929                            new_selections
930                                .push(create_qualified_column(col_name, Some(table_name)));
931                        }
932                    }
933                } else {
934                    for source_name in &ordered_sources {
935                        if let Ok(columns) = resolver.get_source_columns(source_name) {
936                            if columns.contains(&"*".to_string()) || columns.is_empty() {
937                                return Ok(());
938                            }
939                            for col_name in &columns {
940                                let normalized = normalize_column_name(col_name, resolver.dialect);
941                                if coalesced_columns.contains(&normalized) {
942                                    // Already emitted as COALESCE, skip
943                                    continue;
944                                }
945                                if let Some(tables) = column_tables.get(&normalized) {
946                                    if tables.contains(source_name) {
947                                        // Emit COALESCE and mark as coalesced
948                                        coalesced_columns.insert(normalized);
949                                        let coalesce = make_coalesce(col_name, tables);
950                                        new_selections.push(Expression::Alias(Box::new(Alias {
951                                            this: coalesce,
952                                            alias: Identifier::new(col_name),
953                                            column_aliases: vec![],
954                                            alias_explicit_as: false,
955                                            alias_keyword: None,
956                                            pre_alias_comments: vec![],
957                                            trailing_comments: vec![],
958                                            inferred_type: None,
959                                        })));
960                                        continue;
961                                    }
962                                }
963                                new_selections
964                                    .push(create_qualified_column(col_name, Some(source_name)));
965                            }
966                        }
967                    }
968                }
969            }
970            Expression::Column(col) if is_star_column(col) => {
971                has_star = true;
972                if let Some(table) = &col.table {
973                    let table_name = &table.name;
974                    if !ordered_sources.contains(table_name) {
975                        return Err(QualifyColumnsError::UnknownTable(table_name.clone()));
976                    }
977                    if let Ok(columns) = resolver.get_source_columns(table_name) {
978                        if columns.contains(&"*".to_string()) || columns.is_empty() {
979                            return Ok(());
980                        }
981                        for col_name in &columns {
982                            let normalized = normalize_column_name(col_name, resolver.dialect);
983                            if coalesced_columns.contains(&normalized) {
984                                continue;
985                            }
986                            if let Some(tables) = column_tables.get(&normalized) {
987                                if tables.contains(table_name) {
988                                    coalesced_columns.insert(normalized);
989                                    let coalesce = make_coalesce(col_name, tables);
990                                    new_selections.push(Expression::Alias(Box::new(Alias {
991                                        this: coalesce,
992                                        alias: Identifier::new(col_name),
993                                        column_aliases: vec![],
994                                        alias_explicit_as: false,
995                                        alias_keyword: None,
996                                        pre_alias_comments: vec![],
997                                        trailing_comments: vec![],
998                                        inferred_type: None,
999                                    })));
1000                                    continue;
1001                                }
1002                            }
1003                            new_selections
1004                                .push(create_qualified_column(col_name, Some(table_name)));
1005                        }
1006                    }
1007                }
1008            }
1009            _ => new_selections.push(expr.clone()),
1010        }
1011    }
1012
1013    if has_star {
1014        select.expressions = new_selections;
1015    }
1016
1017    Ok(())
1018}
1019
1020/// Ensure all output columns in a SELECT are aliased.
1021///
1022/// For example:
1023/// `SELECT a + b FROM t`
1024/// becomes:
1025/// `SELECT a + b AS _col_0 FROM t`
1026pub fn qualify_outputs(scope: &Scope) -> QualifyColumnsResult<()> {
1027    if let Expression::Select(mut select) = scope.expression.clone() {
1028        qualify_outputs_select(&mut select)?;
1029    }
1030    Ok(())
1031}
1032
1033fn qualify_outputs_select(select: &mut Select) -> QualifyColumnsResult<()> {
1034    let mut new_selections: Vec<Expression> = Vec::new();
1035
1036    for (i, expr) in select.expressions.iter().enumerate() {
1037        match expr {
1038            Expression::Alias(_) => new_selections.push(expr.clone()),
1039            Expression::Column(col) => {
1040                new_selections.push(create_alias(expr.clone(), &col.name.name));
1041            }
1042            Expression::Star(_) => new_selections.push(expr.clone()),
1043            _ => {
1044                let alias_name = get_output_name(expr).unwrap_or_else(|| format!("_col_{}", i));
1045                new_selections.push(create_alias(expr.clone(), &alias_name));
1046            }
1047        }
1048    }
1049
1050    select.expressions = new_selections;
1051    Ok(())
1052}
1053
1054fn qualify_columns_in_expression(
1055    expr: &mut Expression,
1056    scope: &Scope,
1057    resolver: &mut Resolver,
1058    allow_partial: bool,
1059) -> QualifyColumnsResult<()> {
1060    let first_error: RefCell<Option<QualifyColumnsError>> = RefCell::new(None);
1061    let resolver_cell: RefCell<&mut Resolver> = RefCell::new(resolver);
1062
1063    let transformed = transform_recursive(expr.clone(), &|node| {
1064        if first_error.borrow().is_some() {
1065            return Ok(node);
1066        }
1067
1068        match node {
1069            Expression::Column(mut col) => {
1070                if let Err(err) = qualify_single_column(
1071                    &mut col,
1072                    scope,
1073                    &mut resolver_cell.borrow_mut(),
1074                    allow_partial,
1075                ) {
1076                    *first_error.borrow_mut() = Some(err);
1077                }
1078                Ok(Expression::Column(col))
1079            }
1080            _ => Ok(node),
1081        }
1082    })
1083    .map_err(|err| QualifyColumnsError::CannotAutoJoin(err.to_string()))?;
1084
1085    if let Some(err) = first_error.into_inner() {
1086        return Err(err);
1087    }
1088
1089    *expr = transformed;
1090    Ok(())
1091}
1092
1093fn qualify_single_column(
1094    col: &mut Column,
1095    scope: &Scope,
1096    resolver: &mut Resolver,
1097    allow_partial: bool,
1098) -> QualifyColumnsResult<()> {
1099    if is_star_column(col) {
1100        return Ok(());
1101    }
1102
1103    if let Some(table) = &col.table {
1104        let table_name = &table.name;
1105        if !scope.sources.contains_key(table_name) {
1106            // Allow correlated references: if the table exists in the schema
1107            // but not in the current scope, it may be referencing an outer scope
1108            // (e.g., in a correlated scalar subquery).
1109            if resolver.table_exists_in_schema(table_name) {
1110                return Ok(());
1111            }
1112            return Err(QualifyColumnsError::UnknownTable(table_name.clone()));
1113        }
1114
1115        if let Ok(source_columns) = resolver.get_source_columns(table_name) {
1116            let normalized_column_name = normalize_column_name(&col.name.name, resolver.dialect);
1117            if !allow_partial
1118                && !source_columns.is_empty()
1119                && !source_columns.iter().any(|column| {
1120                    normalize_column_name(column, resolver.dialect) == normalized_column_name
1121                })
1122                && !source_columns.contains(&"*".to_string())
1123            {
1124                return Err(QualifyColumnsError::UnknownColumn(col.name.name.clone()));
1125            }
1126        }
1127        return Ok(());
1128    }
1129
1130    if let Some(table_name) = resolver.get_table(&col.name.name) {
1131        col.table = Some(Identifier::new(table_name));
1132        return Ok(());
1133    }
1134
1135    // Check for correlated reference: column might belong to an outer scope table.
1136    // Search all schema tables not in the current scope for this column.
1137    if let Some(outer_table) = resolver.find_column_in_outer_schema_tables(&col.name.name) {
1138        col.table = Some(Identifier::new(outer_table));
1139        return Ok(());
1140    }
1141
1142    if !allow_partial {
1143        return Err(QualifyColumnsError::UnknownColumn(col.name.name.clone()));
1144    }
1145
1146    Ok(())
1147}
1148
1149fn normalize_column_name(name: &str, dialect: Option<DialectType>) -> String {
1150    normalize_name(name, dialect, false, true)
1151}
1152
1153fn replace_alias_refs_in_expression(
1154    expr: &mut Expression,
1155    alias_to_expression: &HashMap<String, (Expression, usize)>,
1156    literal_index: bool,
1157) {
1158    let transformed = transform_recursive(expr.clone(), &|node| match node {
1159        Expression::Column(col) if col.table.is_none() => {
1160            if let Some((alias_expr, index)) = alias_to_expression.get(&col.name.name) {
1161                if literal_index && matches!(alias_expr, Expression::Literal(_)) {
1162                    return Ok(Expression::number(*index as i64));
1163                }
1164                return Ok(Expression::Paren(Box::new(Paren {
1165                    this: alias_expr.clone(),
1166                    trailing_comments: vec![],
1167                })));
1168            }
1169            Ok(Expression::Column(col))
1170        }
1171        other => Ok(other),
1172    });
1173
1174    if let Ok(next) = transformed {
1175        *expr = next;
1176    }
1177}
1178
1179fn positional_reference(expr: &Expression) -> Option<usize> {
1180    match expr {
1181        Expression::Literal(lit) if matches!(lit.as_ref(), Literal::Number(_)) => {
1182            let Literal::Number(value) = lit.as_ref() else {
1183                unreachable!()
1184            };
1185            value.parse::<usize>().ok()
1186        }
1187        _ => None,
1188    }
1189}
1190
1191fn select_expression_at_position(
1192    projections: &[Expression],
1193    index: usize,
1194) -> QualifyColumnsResult<Expression> {
1195    if index == 0 || index > projections.len() {
1196        return Err(QualifyColumnsError::UnknownOutputColumn(index.to_string()));
1197    }
1198
1199    let projection = projections[index - 1].clone();
1200    Ok(match projection {
1201        Expression::Alias(alias) => alias.this.clone(),
1202        other => other,
1203    })
1204}
1205
1206/// Returns the set of SQL reserved words for a given dialect.
1207/// If no dialect is provided, returns a comprehensive default set.
1208fn get_reserved_words(dialect: Option<DialectType>) -> HashSet<&'static str> {
1209    // Core SQL reserved words that are common across all dialects
1210    let mut words: HashSet<&'static str> = [
1211        // SQL standard reserved words
1212        "ADD",
1213        "ALL",
1214        "ALTER",
1215        "AND",
1216        "ANY",
1217        "AS",
1218        "ASC",
1219        "BETWEEN",
1220        "BY",
1221        "CASE",
1222        "CAST",
1223        "CHECK",
1224        "COLUMN",
1225        "CONSTRAINT",
1226        "CREATE",
1227        "CROSS",
1228        "CURRENT",
1229        "CURRENT_DATE",
1230        "CURRENT_TIME",
1231        "CURRENT_TIMESTAMP",
1232        "CURRENT_USER",
1233        "DATABASE",
1234        "DEFAULT",
1235        "DELETE",
1236        "DESC",
1237        "DISTINCT",
1238        "DROP",
1239        "ELSE",
1240        "END",
1241        "ESCAPE",
1242        "EXCEPT",
1243        "EXISTS",
1244        "FALSE",
1245        "FETCH",
1246        "FOR",
1247        "FOREIGN",
1248        "FROM",
1249        "FULL",
1250        "GRANT",
1251        "GROUP",
1252        "HAVING",
1253        "IF",
1254        "IN",
1255        "INDEX",
1256        "INNER",
1257        "INSERT",
1258        "INTERSECT",
1259        "INTO",
1260        "IS",
1261        "JOIN",
1262        "KEY",
1263        "LEFT",
1264        "LIKE",
1265        "LIMIT",
1266        "NATURAL",
1267        "NOT",
1268        "NULL",
1269        "OFFSET",
1270        "ON",
1271        "OR",
1272        "ORDER",
1273        "OUTER",
1274        "PRIMARY",
1275        "REFERENCES",
1276        "REPLACE",
1277        "RETURNING",
1278        "RIGHT",
1279        "ROLLBACK",
1280        "ROW",
1281        "ROWS",
1282        "SELECT",
1283        "SESSION_USER",
1284        "SET",
1285        "SOME",
1286        "TABLE",
1287        "THEN",
1288        "TO",
1289        "TRUE",
1290        "TRUNCATE",
1291        "UNION",
1292        "UNIQUE",
1293        "UPDATE",
1294        "USING",
1295        "VALUES",
1296        "VIEW",
1297        "WHEN",
1298        "WHERE",
1299        "WINDOW",
1300        "WITH",
1301    ]
1302    .iter()
1303    .copied()
1304    .collect();
1305
1306    // Add dialect-specific reserved words
1307    match dialect {
1308        Some(DialectType::MySQL) => {
1309            words.extend(
1310                [
1311                    "ANALYZE",
1312                    "BOTH",
1313                    "CHANGE",
1314                    "CONDITION",
1315                    "DATABASES",
1316                    "DAY_HOUR",
1317                    "DAY_MICROSECOND",
1318                    "DAY_MINUTE",
1319                    "DAY_SECOND",
1320                    "DELAYED",
1321                    "DETERMINISTIC",
1322                    "DIV",
1323                    "DUAL",
1324                    "EACH",
1325                    "ELSEIF",
1326                    "ENCLOSED",
1327                    "EXPLAIN",
1328                    "FLOAT4",
1329                    "FLOAT8",
1330                    "FORCE",
1331                    "HOUR_MICROSECOND",
1332                    "HOUR_MINUTE",
1333                    "HOUR_SECOND",
1334                    "IGNORE",
1335                    "INFILE",
1336                    "INT1",
1337                    "INT2",
1338                    "INT3",
1339                    "INT4",
1340                    "INT8",
1341                    "ITERATE",
1342                    "KEYS",
1343                    "KILL",
1344                    "LEADING",
1345                    "LEAVE",
1346                    "LINES",
1347                    "LOAD",
1348                    "LOCK",
1349                    "LONG",
1350                    "LONGBLOB",
1351                    "LONGTEXT",
1352                    "LOOP",
1353                    "LOW_PRIORITY",
1354                    "MATCH",
1355                    "MEDIUMBLOB",
1356                    "MEDIUMINT",
1357                    "MEDIUMTEXT",
1358                    "MINUTE_MICROSECOND",
1359                    "MINUTE_SECOND",
1360                    "MOD",
1361                    "MODIFIES",
1362                    "NO_WRITE_TO_BINLOG",
1363                    "OPTIMIZE",
1364                    "OPTIONALLY",
1365                    "OUT",
1366                    "OUTFILE",
1367                    "PURGE",
1368                    "READS",
1369                    "REGEXP",
1370                    "RELEASE",
1371                    "RENAME",
1372                    "REPEAT",
1373                    "REQUIRE",
1374                    "RESIGNAL",
1375                    "RETURN",
1376                    "REVOKE",
1377                    "RLIKE",
1378                    "SCHEMA",
1379                    "SCHEMAS",
1380                    "SECOND_MICROSECOND",
1381                    "SENSITIVE",
1382                    "SEPARATOR",
1383                    "SHOW",
1384                    "SIGNAL",
1385                    "SPATIAL",
1386                    "SQL",
1387                    "SQLEXCEPTION",
1388                    "SQLSTATE",
1389                    "SQLWARNING",
1390                    "SQL_BIG_RESULT",
1391                    "SQL_CALC_FOUND_ROWS",
1392                    "SQL_SMALL_RESULT",
1393                    "SSL",
1394                    "STARTING",
1395                    "STRAIGHT_JOIN",
1396                    "TERMINATED",
1397                    "TINYBLOB",
1398                    "TINYINT",
1399                    "TINYTEXT",
1400                    "TRAILING",
1401                    "TRIGGER",
1402                    "UNDO",
1403                    "UNLOCK",
1404                    "UNSIGNED",
1405                    "USAGE",
1406                    "UTC_DATE",
1407                    "UTC_TIME",
1408                    "UTC_TIMESTAMP",
1409                    "VARBINARY",
1410                    "VARCHARACTER",
1411                    "WHILE",
1412                    "WRITE",
1413                    "XOR",
1414                    "YEAR_MONTH",
1415                    "ZEROFILL",
1416                ]
1417                .iter()
1418                .copied(),
1419            );
1420        }
1421        Some(DialectType::PostgreSQL) | Some(DialectType::CockroachDB) => {
1422            words.extend(
1423                [
1424                    "ANALYSE",
1425                    "ANALYZE",
1426                    "ARRAY",
1427                    "AUTHORIZATION",
1428                    "BINARY",
1429                    "BOTH",
1430                    "COLLATE",
1431                    "CONCURRENTLY",
1432                    "DO",
1433                    "FREEZE",
1434                    "ILIKE",
1435                    "INITIALLY",
1436                    "ISNULL",
1437                    "LATERAL",
1438                    "LEADING",
1439                    "LOCALTIME",
1440                    "LOCALTIMESTAMP",
1441                    "NOTNULL",
1442                    "ONLY",
1443                    "OVERLAPS",
1444                    "PLACING",
1445                    "SIMILAR",
1446                    "SYMMETRIC",
1447                    "TABLESAMPLE",
1448                    "TRAILING",
1449                    "VARIADIC",
1450                    "VERBOSE",
1451                ]
1452                .iter()
1453                .copied(),
1454            );
1455        }
1456        Some(DialectType::BigQuery) => {
1457            words.extend(
1458                [
1459                    "ASSERT_ROWS_MODIFIED",
1460                    "COLLATE",
1461                    "CONTAINS",
1462                    "CUBE",
1463                    "DEFINE",
1464                    "ENUM",
1465                    "EXTRACT",
1466                    "FOLLOWING",
1467                    "GROUPING",
1468                    "GROUPS",
1469                    "HASH",
1470                    "IGNORE",
1471                    "LATERAL",
1472                    "LOOKUP",
1473                    "MERGE",
1474                    "NEW",
1475                    "NO",
1476                    "NULLS",
1477                    "OF",
1478                    "OVER",
1479                    "PARTITION",
1480                    "PRECEDING",
1481                    "PROTO",
1482                    "RANGE",
1483                    "RECURSIVE",
1484                    "RESPECT",
1485                    "ROLLUP",
1486                    "STRUCT",
1487                    "TABLESAMPLE",
1488                    "TREAT",
1489                    "UNBOUNDED",
1490                    "WITHIN",
1491                ]
1492                .iter()
1493                .copied(),
1494            );
1495        }
1496        Some(DialectType::Snowflake) => {
1497            words.extend(
1498                [
1499                    "ACCOUNT",
1500                    "BOTH",
1501                    "CONNECT",
1502                    "FOLLOWING",
1503                    "ILIKE",
1504                    "INCREMENT",
1505                    "ISSUE",
1506                    "LATERAL",
1507                    "LEADING",
1508                    "LOCALTIME",
1509                    "LOCALTIMESTAMP",
1510                    "MINUS",
1511                    "QUALIFY",
1512                    "REGEXP",
1513                    "RLIKE",
1514                    "SOME",
1515                    "START",
1516                    "TABLESAMPLE",
1517                    "TOP",
1518                    "TRAILING",
1519                    "TRY_CAST",
1520                ]
1521                .iter()
1522                .copied(),
1523            );
1524        }
1525        Some(DialectType::TSQL) | Some(DialectType::Fabric) => {
1526            words.extend(
1527                [
1528                    "BACKUP",
1529                    "BREAK",
1530                    "BROWSE",
1531                    "BULK",
1532                    "CASCADE",
1533                    "CHECKPOINT",
1534                    "CLOSE",
1535                    "CLUSTERED",
1536                    "COALESCE",
1537                    "COMPUTE",
1538                    "CONTAINS",
1539                    "CONTAINSTABLE",
1540                    "CONTINUE",
1541                    "CONVERT",
1542                    "DBCC",
1543                    "DEALLOCATE",
1544                    "DENY",
1545                    "DISK",
1546                    "DISTRIBUTED",
1547                    "DUMP",
1548                    "ERRLVL",
1549                    "EXEC",
1550                    "EXECUTE",
1551                    "EXIT",
1552                    "EXTERNAL",
1553                    "FILE",
1554                    "FILLFACTOR",
1555                    "FREETEXT",
1556                    "FREETEXTTABLE",
1557                    "FUNCTION",
1558                    "GOTO",
1559                    "HOLDLOCK",
1560                    "IDENTITY",
1561                    "IDENTITYCOL",
1562                    "IDENTITY_INSERT",
1563                    "KILL",
1564                    "LINENO",
1565                    "MERGE",
1566                    "NONCLUSTERED",
1567                    "NULLIF",
1568                    "OF",
1569                    "OFF",
1570                    "OFFSETS",
1571                    "OPEN",
1572                    "OPENDATASOURCE",
1573                    "OPENQUERY",
1574                    "OPENROWSET",
1575                    "OPENXML",
1576                    "OVER",
1577                    "PERCENT",
1578                    "PIVOT",
1579                    "PLAN",
1580                    "PRINT",
1581                    "PROC",
1582                    "PROCEDURE",
1583                    "PUBLIC",
1584                    "RAISERROR",
1585                    "READ",
1586                    "READTEXT",
1587                    "RECONFIGURE",
1588                    "REPLICATION",
1589                    "RESTORE",
1590                    "RESTRICT",
1591                    "REVERT",
1592                    "ROWCOUNT",
1593                    "ROWGUIDCOL",
1594                    "RULE",
1595                    "SAVE",
1596                    "SECURITYAUDIT",
1597                    "SEMANTICKEYPHRASETABLE",
1598                    "SEMANTICSIMILARITYDETAILSTABLE",
1599                    "SEMANTICSIMILARITYTABLE",
1600                    "SETUSER",
1601                    "SHUTDOWN",
1602                    "STATISTICS",
1603                    "SYSTEM_USER",
1604                    "TEXTSIZE",
1605                    "TOP",
1606                    "TRAN",
1607                    "TRANSACTION",
1608                    "TRIGGER",
1609                    "TSEQUAL",
1610                    "UNPIVOT",
1611                    "UPDATETEXT",
1612                    "WAITFOR",
1613                    "WRITETEXT",
1614                ]
1615                .iter()
1616                .copied(),
1617            );
1618        }
1619        Some(DialectType::ClickHouse) => {
1620            words.extend(
1621                [
1622                    "ANTI",
1623                    "ARRAY",
1624                    "ASOF",
1625                    "FINAL",
1626                    "FORMAT",
1627                    "GLOBAL",
1628                    "INF",
1629                    "KILL",
1630                    "MATERIALIZED",
1631                    "NAN",
1632                    "PREWHERE",
1633                    "SAMPLE",
1634                    "SEMI",
1635                    "SETTINGS",
1636                    "TOP",
1637                ]
1638                .iter()
1639                .copied(),
1640            );
1641        }
1642        Some(DialectType::DuckDB) => {
1643            words.extend(
1644                [
1645                    "ANALYSE",
1646                    "ANALYZE",
1647                    "ARRAY",
1648                    "BOTH",
1649                    "LATERAL",
1650                    "LEADING",
1651                    "LOCALTIME",
1652                    "LOCALTIMESTAMP",
1653                    "PLACING",
1654                    "QUALIFY",
1655                    "SIMILAR",
1656                    "TABLESAMPLE",
1657                    "TRAILING",
1658                ]
1659                .iter()
1660                .copied(),
1661            );
1662        }
1663        Some(DialectType::Hive) | Some(DialectType::Spark) | Some(DialectType::Databricks) => {
1664            words.extend(
1665                [
1666                    "BOTH",
1667                    "CLUSTER",
1668                    "DISTRIBUTE",
1669                    "EXCHANGE",
1670                    "EXTENDED",
1671                    "FUNCTION",
1672                    "LATERAL",
1673                    "LEADING",
1674                    "MACRO",
1675                    "OVER",
1676                    "PARTITION",
1677                    "PERCENT",
1678                    "RANGE",
1679                    "READS",
1680                    "REDUCE",
1681                    "REGEXP",
1682                    "REVOKE",
1683                    "RLIKE",
1684                    "ROLLUP",
1685                    "SEMI",
1686                    "SORT",
1687                    "TABLESAMPLE",
1688                    "TRAILING",
1689                    "TRANSFORM",
1690                    "UNBOUNDED",
1691                    "UNIQUEJOIN",
1692                ]
1693                .iter()
1694                .copied(),
1695            );
1696        }
1697        Some(DialectType::Trino) | Some(DialectType::Presto) | Some(DialectType::Athena) => {
1698            words.extend(
1699                [
1700                    "CUBE",
1701                    "DEALLOCATE",
1702                    "DESCRIBE",
1703                    "EXECUTE",
1704                    "EXTRACT",
1705                    "GROUPING",
1706                    "LATERAL",
1707                    "LOCALTIME",
1708                    "LOCALTIMESTAMP",
1709                    "NORMALIZE",
1710                    "PREPARE",
1711                    "ROLLUP",
1712                    "SOME",
1713                    "TABLESAMPLE",
1714                    "UESCAPE",
1715                    "UNNEST",
1716                ]
1717                .iter()
1718                .copied(),
1719            );
1720        }
1721        Some(DialectType::Oracle) => {
1722            words.extend(
1723                [
1724                    "ACCESS",
1725                    "AUDIT",
1726                    "CLUSTER",
1727                    "COMMENT",
1728                    "COMPRESS",
1729                    "CONNECT",
1730                    "EXCLUSIVE",
1731                    "FILE",
1732                    "IDENTIFIED",
1733                    "IMMEDIATE",
1734                    "INCREMENT",
1735                    "INITIAL",
1736                    "LEVEL",
1737                    "LOCK",
1738                    "LONG",
1739                    "MAXEXTENTS",
1740                    "MINUS",
1741                    "MODE",
1742                    "NOAUDIT",
1743                    "NOCOMPRESS",
1744                    "NOWAIT",
1745                    "NUMBER",
1746                    "OF",
1747                    "OFFLINE",
1748                    "ONLINE",
1749                    "PCTFREE",
1750                    "PRIOR",
1751                    "RAW",
1752                    "RENAME",
1753                    "RESOURCE",
1754                    "REVOKE",
1755                    "SHARE",
1756                    "SIZE",
1757                    "START",
1758                    "SUCCESSFUL",
1759                    "SYNONYM",
1760                    "SYSDATE",
1761                    "TRIGGER",
1762                    "UID",
1763                    "VALIDATE",
1764                    "VARCHAR2",
1765                    "WHENEVER",
1766                ]
1767                .iter()
1768                .copied(),
1769            );
1770        }
1771        Some(DialectType::Redshift) => {
1772            words.extend(
1773                [
1774                    "AZ64",
1775                    "BZIP2",
1776                    "DELTA",
1777                    "DELTA32K",
1778                    "DISTSTYLE",
1779                    "ENCODE",
1780                    "GZIP",
1781                    "ILIKE",
1782                    "LIMIT",
1783                    "LUNS",
1784                    "LZO",
1785                    "LZOP",
1786                    "MOSTLY13",
1787                    "MOSTLY32",
1788                    "MOSTLY8",
1789                    "RAW",
1790                    "SIMILAR",
1791                    "SNAPSHOT",
1792                    "SORTKEY",
1793                    "SYSDATE",
1794                    "TOP",
1795                    "ZSTD",
1796                ]
1797                .iter()
1798                .copied(),
1799            );
1800        }
1801        _ => {
1802            // For Generic or unknown dialects, add a broad set of commonly reserved words
1803            words.extend(
1804                [
1805                    "ANALYZE",
1806                    "ARRAY",
1807                    "BOTH",
1808                    "CUBE",
1809                    "GROUPING",
1810                    "LATERAL",
1811                    "LEADING",
1812                    "LOCALTIME",
1813                    "LOCALTIMESTAMP",
1814                    "OVER",
1815                    "PARTITION",
1816                    "QUALIFY",
1817                    "RANGE",
1818                    "ROLLUP",
1819                    "SIMILAR",
1820                    "SOME",
1821                    "TABLESAMPLE",
1822                    "TRAILING",
1823                ]
1824                .iter()
1825                .copied(),
1826            );
1827        }
1828    }
1829
1830    words
1831}
1832
1833/// Check whether an identifier name needs quoting.
1834///
1835/// An identifier needs quoting if:
1836/// - It is empty
1837/// - It starts with a digit
1838/// - It contains characters other than `[a-zA-Z0-9_]`
1839/// - It is a SQL reserved word (case-insensitive)
1840fn needs_quoting(name: &str, reserved_words: &HashSet<&str>) -> bool {
1841    if name.is_empty() {
1842        return false;
1843    }
1844
1845    // Starts with a digit
1846    if name.as_bytes()[0].is_ascii_digit() {
1847        return true;
1848    }
1849
1850    // Contains non-identifier characters
1851    if !name.bytes().all(|b| b.is_ascii_alphanumeric() || b == b'_') {
1852        return true;
1853    }
1854
1855    // Is a reserved word (case-insensitive check)
1856    let upper = name.to_uppercase();
1857    reserved_words.contains(upper.as_str())
1858}
1859
1860/// Conditionally set `quoted = true` on an identifier if it needs quoting.
1861fn maybe_quote(id: &mut Identifier, reserved_words: &HashSet<&str>) {
1862    // Don't re-quote something already quoted, and don't quote empty identifiers
1863    // or wildcard identifiers
1864    if id.quoted || id.name.is_empty() || id.name == "*" {
1865        return;
1866    }
1867    if needs_quoting(&id.name, reserved_words) {
1868        id.quoted = true;
1869    }
1870}
1871
1872/// Recursively walk an expression and quote identifiers that need quoting.
1873fn quote_identifiers_recursive(expr: &mut Expression, reserved_words: &HashSet<&str>) {
1874    match expr {
1875        // ── Leaf nodes with Identifier ────────────────────────────
1876        Expression::Identifier(id) => {
1877            maybe_quote(id, reserved_words);
1878        }
1879
1880        Expression::Column(col) => {
1881            maybe_quote(&mut col.name, reserved_words);
1882            if let Some(ref mut table) = col.table {
1883                maybe_quote(table, reserved_words);
1884            }
1885        }
1886
1887        Expression::Table(table_ref) => {
1888            maybe_quote(&mut table_ref.name, reserved_words);
1889            if let Some(ref mut schema) = table_ref.schema {
1890                maybe_quote(schema, reserved_words);
1891            }
1892            if let Some(ref mut catalog) = table_ref.catalog {
1893                maybe_quote(catalog, reserved_words);
1894            }
1895            if let Some(ref mut alias) = table_ref.alias {
1896                maybe_quote(alias, reserved_words);
1897            }
1898            for ca in &mut table_ref.column_aliases {
1899                maybe_quote(ca, reserved_words);
1900            }
1901            for p in &mut table_ref.partitions {
1902                maybe_quote(p, reserved_words);
1903            }
1904            // Recurse into hints and other child expressions
1905            for h in &mut table_ref.hints {
1906                quote_identifiers_recursive(h, reserved_words);
1907            }
1908            if let Some(ref mut ver) = table_ref.version {
1909                quote_identifiers_recursive(&mut ver.this, reserved_words);
1910                if let Some(ref mut e) = ver.expression {
1911                    quote_identifiers_recursive(e, reserved_words);
1912                }
1913            }
1914        }
1915
1916        Expression::Star(star) => {
1917            if let Some(ref mut table) = star.table {
1918                maybe_quote(table, reserved_words);
1919            }
1920            if let Some(ref mut except_ids) = star.except {
1921                for id in except_ids {
1922                    maybe_quote(id, reserved_words);
1923                }
1924            }
1925            if let Some(ref mut replace_aliases) = star.replace {
1926                for alias in replace_aliases {
1927                    maybe_quote(&mut alias.alias, reserved_words);
1928                    quote_identifiers_recursive(&mut alias.this, reserved_words);
1929                }
1930            }
1931            if let Some(ref mut rename_pairs) = star.rename {
1932                for (from, to) in rename_pairs {
1933                    maybe_quote(from, reserved_words);
1934                    maybe_quote(to, reserved_words);
1935                }
1936            }
1937        }
1938
1939        // ── Alias ─────────────────────────────────────────────────
1940        Expression::Alias(alias) => {
1941            maybe_quote(&mut alias.alias, reserved_words);
1942            for ca in &mut alias.column_aliases {
1943                maybe_quote(ca, reserved_words);
1944            }
1945            quote_identifiers_recursive(&mut alias.this, reserved_words);
1946        }
1947
1948        // ── SELECT ────────────────────────────────────────────────
1949        Expression::Select(select) => {
1950            for e in &mut select.expressions {
1951                quote_identifiers_recursive(e, reserved_words);
1952            }
1953            if let Some(ref mut from) = select.from {
1954                for e in &mut from.expressions {
1955                    quote_identifiers_recursive(e, reserved_words);
1956                }
1957            }
1958            for join in &mut select.joins {
1959                quote_join(join, reserved_words);
1960            }
1961            for lv in &mut select.lateral_views {
1962                quote_lateral_view(lv, reserved_words);
1963            }
1964            if let Some(ref mut prewhere) = select.prewhere {
1965                quote_identifiers_recursive(prewhere, reserved_words);
1966            }
1967            if let Some(ref mut wh) = select.where_clause {
1968                quote_identifiers_recursive(&mut wh.this, reserved_words);
1969            }
1970            if let Some(ref mut gb) = select.group_by {
1971                for e in &mut gb.expressions {
1972                    quote_identifiers_recursive(e, reserved_words);
1973                }
1974            }
1975            if let Some(ref mut hv) = select.having {
1976                quote_identifiers_recursive(&mut hv.this, reserved_words);
1977            }
1978            if let Some(ref mut q) = select.qualify {
1979                quote_identifiers_recursive(&mut q.this, reserved_words);
1980            }
1981            if let Some(ref mut ob) = select.order_by {
1982                for o in &mut ob.expressions {
1983                    quote_identifiers_recursive(&mut o.this, reserved_words);
1984                }
1985            }
1986            if let Some(ref mut lim) = select.limit {
1987                quote_identifiers_recursive(&mut lim.this, reserved_words);
1988            }
1989            if let Some(ref mut off) = select.offset {
1990                quote_identifiers_recursive(&mut off.this, reserved_words);
1991            }
1992            if let Some(ref mut with) = select.with {
1993                quote_with(with, reserved_words);
1994            }
1995            if let Some(ref mut windows) = select.windows {
1996                for nw in windows {
1997                    maybe_quote(&mut nw.name, reserved_words);
1998                    quote_over(&mut nw.spec, reserved_words);
1999                }
2000            }
2001            if let Some(ref mut distinct_on) = select.distinct_on {
2002                for e in distinct_on {
2003                    quote_identifiers_recursive(e, reserved_words);
2004                }
2005            }
2006            if let Some(ref mut limit_by) = select.limit_by {
2007                for e in limit_by {
2008                    quote_identifiers_recursive(e, reserved_words);
2009                }
2010            }
2011            if let Some(ref mut settings) = select.settings {
2012                for e in settings {
2013                    quote_identifiers_recursive(e, reserved_words);
2014                }
2015            }
2016            if let Some(ref mut format) = select.format {
2017                quote_identifiers_recursive(format, reserved_words);
2018            }
2019        }
2020
2021        // ── Set operations ────────────────────────────────────────
2022        Expression::Union(u) => {
2023            quote_identifiers_recursive(&mut u.left, reserved_words);
2024            quote_identifiers_recursive(&mut u.right, reserved_words);
2025            if let Some(ref mut with) = u.with {
2026                quote_with(with, reserved_words);
2027            }
2028            if let Some(ref mut ob) = u.order_by {
2029                for o in &mut ob.expressions {
2030                    quote_identifiers_recursive(&mut o.this, reserved_words);
2031                }
2032            }
2033            if let Some(ref mut lim) = u.limit {
2034                quote_identifiers_recursive(lim, reserved_words);
2035            }
2036            if let Some(ref mut off) = u.offset {
2037                quote_identifiers_recursive(off, reserved_words);
2038            }
2039        }
2040        Expression::Intersect(i) => {
2041            quote_identifiers_recursive(&mut i.left, reserved_words);
2042            quote_identifiers_recursive(&mut i.right, reserved_words);
2043            if let Some(ref mut with) = i.with {
2044                quote_with(with, reserved_words);
2045            }
2046            if let Some(ref mut ob) = i.order_by {
2047                for o in &mut ob.expressions {
2048                    quote_identifiers_recursive(&mut o.this, reserved_words);
2049                }
2050            }
2051        }
2052        Expression::Except(e) => {
2053            quote_identifiers_recursive(&mut e.left, reserved_words);
2054            quote_identifiers_recursive(&mut e.right, reserved_words);
2055            if let Some(ref mut with) = e.with {
2056                quote_with(with, reserved_words);
2057            }
2058            if let Some(ref mut ob) = e.order_by {
2059                for o in &mut ob.expressions {
2060                    quote_identifiers_recursive(&mut o.this, reserved_words);
2061                }
2062            }
2063        }
2064
2065        // ── Subquery ──────────────────────────────────────────────
2066        Expression::Subquery(sq) => {
2067            quote_identifiers_recursive(&mut sq.this, reserved_words);
2068            if let Some(ref mut alias) = sq.alias {
2069                maybe_quote(alias, reserved_words);
2070            }
2071            for ca in &mut sq.column_aliases {
2072                maybe_quote(ca, reserved_words);
2073            }
2074            if let Some(ref mut ob) = sq.order_by {
2075                for o in &mut ob.expressions {
2076                    quote_identifiers_recursive(&mut o.this, reserved_words);
2077                }
2078            }
2079        }
2080
2081        // ── DML ───────────────────────────────────────────────────
2082        Expression::Insert(ins) => {
2083            quote_table_ref(&mut ins.table, reserved_words);
2084            for c in &mut ins.columns {
2085                maybe_quote(c, reserved_words);
2086            }
2087            for row in &mut ins.values {
2088                for e in row {
2089                    quote_identifiers_recursive(e, reserved_words);
2090                }
2091            }
2092            if let Some(ref mut q) = ins.query {
2093                quote_identifiers_recursive(q, reserved_words);
2094            }
2095            for (id, val) in &mut ins.partition {
2096                maybe_quote(id, reserved_words);
2097                if let Some(ref mut v) = val {
2098                    quote_identifiers_recursive(v, reserved_words);
2099                }
2100            }
2101            for e in &mut ins.returning {
2102                quote_identifiers_recursive(e, reserved_words);
2103            }
2104            if let Some(ref mut on_conflict) = ins.on_conflict {
2105                quote_identifiers_recursive(on_conflict, reserved_words);
2106            }
2107            if let Some(ref mut with) = ins.with {
2108                quote_with(with, reserved_words);
2109            }
2110            if let Some(ref mut alias) = ins.alias {
2111                maybe_quote(alias, reserved_words);
2112            }
2113            if let Some(ref mut src_alias) = ins.source_alias {
2114                maybe_quote(src_alias, reserved_words);
2115            }
2116        }
2117
2118        Expression::Update(upd) => {
2119            quote_table_ref(&mut upd.table, reserved_words);
2120            for tr in &mut upd.extra_tables {
2121                quote_table_ref(tr, reserved_words);
2122            }
2123            for join in &mut upd.table_joins {
2124                quote_join(join, reserved_words);
2125            }
2126            for (id, val) in &mut upd.set {
2127                maybe_quote(id, reserved_words);
2128                quote_identifiers_recursive(val, reserved_words);
2129            }
2130            if let Some(ref mut from) = upd.from_clause {
2131                for e in &mut from.expressions {
2132                    quote_identifiers_recursive(e, reserved_words);
2133                }
2134            }
2135            for join in &mut upd.from_joins {
2136                quote_join(join, reserved_words);
2137            }
2138            if let Some(ref mut wh) = upd.where_clause {
2139                quote_identifiers_recursive(&mut wh.this, reserved_words);
2140            }
2141            for e in &mut upd.returning {
2142                quote_identifiers_recursive(e, reserved_words);
2143            }
2144            if let Some(ref mut with) = upd.with {
2145                quote_with(with, reserved_words);
2146            }
2147        }
2148
2149        Expression::Delete(del) => {
2150            quote_table_ref(&mut del.table, reserved_words);
2151            if let Some(ref mut alias) = del.alias {
2152                maybe_quote(alias, reserved_words);
2153            }
2154            for tr in &mut del.using {
2155                quote_table_ref(tr, reserved_words);
2156            }
2157            if let Some(ref mut wh) = del.where_clause {
2158                quote_identifiers_recursive(&mut wh.this, reserved_words);
2159            }
2160            if let Some(ref mut with) = del.with {
2161                quote_with(with, reserved_words);
2162            }
2163        }
2164
2165        // ── Binary operations ─────────────────────────────────────
2166        Expression::And(bin)
2167        | Expression::Or(bin)
2168        | Expression::Eq(bin)
2169        | Expression::Neq(bin)
2170        | Expression::Lt(bin)
2171        | Expression::Lte(bin)
2172        | Expression::Gt(bin)
2173        | Expression::Gte(bin)
2174        | Expression::Add(bin)
2175        | Expression::Sub(bin)
2176        | Expression::Mul(bin)
2177        | Expression::Div(bin)
2178        | Expression::Mod(bin)
2179        | Expression::BitwiseAnd(bin)
2180        | Expression::BitwiseOr(bin)
2181        | Expression::BitwiseXor(bin)
2182        | Expression::Concat(bin)
2183        | Expression::Adjacent(bin)
2184        | Expression::TsMatch(bin)
2185        | Expression::PropertyEQ(bin)
2186        | Expression::ArrayContainsAll(bin)
2187        | Expression::ArrayContainedBy(bin)
2188        | Expression::ArrayOverlaps(bin)
2189        | Expression::JSONBContainsAllTopKeys(bin)
2190        | Expression::JSONBContainsAnyTopKeys(bin)
2191        | Expression::JSONBDeleteAtPath(bin)
2192        | Expression::ExtendsLeft(bin)
2193        | Expression::ExtendsRight(bin)
2194        | Expression::Is(bin)
2195        | Expression::NullSafeEq(bin)
2196        | Expression::NullSafeNeq(bin)
2197        | Expression::Glob(bin)
2198        | Expression::Match(bin)
2199        | Expression::MemberOf(bin)
2200        | Expression::BitwiseLeftShift(bin)
2201        | Expression::BitwiseRightShift(bin) => {
2202            quote_identifiers_recursive(&mut bin.left, reserved_words);
2203            quote_identifiers_recursive(&mut bin.right, reserved_words);
2204        }
2205
2206        // ── Like operations ───────────────────────────────────────
2207        Expression::Like(like) | Expression::ILike(like) => {
2208            quote_identifiers_recursive(&mut like.left, reserved_words);
2209            quote_identifiers_recursive(&mut like.right, reserved_words);
2210            if let Some(ref mut esc) = like.escape {
2211                quote_identifiers_recursive(esc, reserved_words);
2212            }
2213        }
2214
2215        // ── Unary operations ──────────────────────────────────────
2216        Expression::Not(un) | Expression::Neg(un) | Expression::BitwiseNot(un) => {
2217            quote_identifiers_recursive(&mut un.this, reserved_words);
2218        }
2219
2220        // ── Predicates ────────────────────────────────────────────
2221        Expression::In(in_expr) => {
2222            quote_identifiers_recursive(&mut in_expr.this, reserved_words);
2223            for e in &mut in_expr.expressions {
2224                quote_identifiers_recursive(e, reserved_words);
2225            }
2226            if let Some(ref mut q) = in_expr.query {
2227                quote_identifiers_recursive(q, reserved_words);
2228            }
2229            if let Some(ref mut un) = in_expr.unnest {
2230                quote_identifiers_recursive(un, reserved_words);
2231            }
2232        }
2233
2234        Expression::Between(bw) => {
2235            quote_identifiers_recursive(&mut bw.this, reserved_words);
2236            quote_identifiers_recursive(&mut bw.low, reserved_words);
2237            quote_identifiers_recursive(&mut bw.high, reserved_words);
2238        }
2239
2240        Expression::IsNull(is_null) => {
2241            quote_identifiers_recursive(&mut is_null.this, reserved_words);
2242        }
2243
2244        Expression::IsTrue(is_tf) | Expression::IsFalse(is_tf) => {
2245            quote_identifiers_recursive(&mut is_tf.this, reserved_words);
2246        }
2247
2248        Expression::Exists(ex) => {
2249            quote_identifiers_recursive(&mut ex.this, reserved_words);
2250        }
2251
2252        // ── Functions ─────────────────────────────────────────────
2253        Expression::Function(func) => {
2254            for arg in &mut func.args {
2255                quote_identifiers_recursive(arg, reserved_words);
2256            }
2257        }
2258
2259        Expression::AggregateFunction(agg) => {
2260            for arg in &mut agg.args {
2261                quote_identifiers_recursive(arg, reserved_words);
2262            }
2263            if let Some(ref mut filter) = agg.filter {
2264                quote_identifiers_recursive(filter, reserved_words);
2265            }
2266            for o in &mut agg.order_by {
2267                quote_identifiers_recursive(&mut o.this, reserved_words);
2268            }
2269        }
2270
2271        Expression::WindowFunction(wf) => {
2272            quote_identifiers_recursive(&mut wf.this, reserved_words);
2273            quote_over(&mut wf.over, reserved_words);
2274        }
2275
2276        // ── CASE ──────────────────────────────────────────────────
2277        Expression::Case(case) => {
2278            if let Some(ref mut operand) = case.operand {
2279                quote_identifiers_recursive(operand, reserved_words);
2280            }
2281            for (when, then) in &mut case.whens {
2282                quote_identifiers_recursive(when, reserved_words);
2283                quote_identifiers_recursive(then, reserved_words);
2284            }
2285            if let Some(ref mut else_) = case.else_ {
2286                quote_identifiers_recursive(else_, reserved_words);
2287            }
2288        }
2289
2290        // ── CAST / TryCast / SafeCast ─────────────────────────────
2291        Expression::Cast(cast) | Expression::TryCast(cast) | Expression::SafeCast(cast) => {
2292            quote_identifiers_recursive(&mut cast.this, reserved_words);
2293            if let Some(ref mut fmt) = cast.format {
2294                quote_identifiers_recursive(fmt, reserved_words);
2295            }
2296        }
2297
2298        // ── Paren / Annotated ─────────────────────────────────────
2299        Expression::Paren(paren) => {
2300            quote_identifiers_recursive(&mut paren.this, reserved_words);
2301        }
2302
2303        Expression::Annotated(ann) => {
2304            quote_identifiers_recursive(&mut ann.this, reserved_words);
2305        }
2306
2307        // ── WITH clause (standalone) ──────────────────────────────
2308        Expression::With(with) => {
2309            quote_with(with, reserved_words);
2310        }
2311
2312        Expression::Cte(cte) => {
2313            maybe_quote(&mut cte.alias, reserved_words);
2314            for c in &mut cte.columns {
2315                maybe_quote(c, reserved_words);
2316            }
2317            quote_identifiers_recursive(&mut cte.this, reserved_words);
2318        }
2319
2320        // ── Clauses (standalone) ──────────────────────────────────
2321        Expression::From(from) => {
2322            for e in &mut from.expressions {
2323                quote_identifiers_recursive(e, reserved_words);
2324            }
2325        }
2326
2327        Expression::Join(join) => {
2328            quote_join(join, reserved_words);
2329        }
2330
2331        Expression::JoinedTable(jt) => {
2332            quote_identifiers_recursive(&mut jt.left, reserved_words);
2333            for join in &mut jt.joins {
2334                quote_join(join, reserved_words);
2335            }
2336            if let Some(ref mut alias) = jt.alias {
2337                maybe_quote(alias, reserved_words);
2338            }
2339        }
2340
2341        Expression::Where(wh) => {
2342            quote_identifiers_recursive(&mut wh.this, reserved_words);
2343        }
2344
2345        Expression::GroupBy(gb) => {
2346            for e in &mut gb.expressions {
2347                quote_identifiers_recursive(e, reserved_words);
2348            }
2349        }
2350
2351        Expression::Having(hv) => {
2352            quote_identifiers_recursive(&mut hv.this, reserved_words);
2353        }
2354
2355        Expression::OrderBy(ob) => {
2356            for o in &mut ob.expressions {
2357                quote_identifiers_recursive(&mut o.this, reserved_words);
2358            }
2359        }
2360
2361        Expression::Ordered(ord) => {
2362            quote_identifiers_recursive(&mut ord.this, reserved_words);
2363        }
2364
2365        Expression::Limit(lim) => {
2366            quote_identifiers_recursive(&mut lim.this, reserved_words);
2367        }
2368
2369        Expression::Offset(off) => {
2370            quote_identifiers_recursive(&mut off.this, reserved_words);
2371        }
2372
2373        Expression::Qualify(q) => {
2374            quote_identifiers_recursive(&mut q.this, reserved_words);
2375        }
2376
2377        Expression::Window(ws) => {
2378            for e in &mut ws.partition_by {
2379                quote_identifiers_recursive(e, reserved_words);
2380            }
2381            for o in &mut ws.order_by {
2382                quote_identifiers_recursive(&mut o.this, reserved_words);
2383            }
2384        }
2385
2386        Expression::Over(over) => {
2387            quote_over(over, reserved_words);
2388        }
2389
2390        Expression::WithinGroup(wg) => {
2391            quote_identifiers_recursive(&mut wg.this, reserved_words);
2392            for o in &mut wg.order_by {
2393                quote_identifiers_recursive(&mut o.this, reserved_words);
2394            }
2395        }
2396
2397        // ── Pivot / Unpivot ───────────────────────────────────────
2398        Expression::Pivot(piv) => {
2399            quote_identifiers_recursive(&mut piv.this, reserved_words);
2400            for e in &mut piv.expressions {
2401                quote_identifiers_recursive(e, reserved_words);
2402            }
2403            for f in &mut piv.fields {
2404                quote_identifiers_recursive(f, reserved_words);
2405            }
2406            if let Some(ref mut alias) = piv.alias {
2407                maybe_quote(alias, reserved_words);
2408            }
2409        }
2410
2411        Expression::Unpivot(unpiv) => {
2412            quote_identifiers_recursive(&mut unpiv.this, reserved_words);
2413            maybe_quote(&mut unpiv.value_column, reserved_words);
2414            maybe_quote(&mut unpiv.name_column, reserved_words);
2415            for e in &mut unpiv.columns {
2416                quote_identifiers_recursive(e, reserved_words);
2417            }
2418            if let Some(ref mut alias) = unpiv.alias {
2419                maybe_quote(alias, reserved_words);
2420            }
2421        }
2422
2423        // ── Values ────────────────────────────────────────────────
2424        Expression::Values(vals) => {
2425            for tuple in &mut vals.expressions {
2426                for e in &mut tuple.expressions {
2427                    quote_identifiers_recursive(e, reserved_words);
2428                }
2429            }
2430            if let Some(ref mut alias) = vals.alias {
2431                maybe_quote(alias, reserved_words);
2432            }
2433            for ca in &mut vals.column_aliases {
2434                maybe_quote(ca, reserved_words);
2435            }
2436        }
2437
2438        // ── Array / Struct / Tuple ────────────────────────────────
2439        Expression::Array(arr) => {
2440            for e in &mut arr.expressions {
2441                quote_identifiers_recursive(e, reserved_words);
2442            }
2443        }
2444
2445        Expression::Struct(st) => {
2446            for (_name, e) in &mut st.fields {
2447                quote_identifiers_recursive(e, reserved_words);
2448            }
2449        }
2450
2451        Expression::Tuple(tup) => {
2452            for e in &mut tup.expressions {
2453                quote_identifiers_recursive(e, reserved_words);
2454            }
2455        }
2456
2457        // ── Subscript / Dot / Method ──────────────────────────────
2458        Expression::Subscript(sub) => {
2459            quote_identifiers_recursive(&mut sub.this, reserved_words);
2460            quote_identifiers_recursive(&mut sub.index, reserved_words);
2461        }
2462
2463        Expression::Dot(dot) => {
2464            quote_identifiers_recursive(&mut dot.this, reserved_words);
2465            maybe_quote(&mut dot.field, reserved_words);
2466        }
2467
2468        Expression::ScopeResolution(sr) => {
2469            if let Some(ref mut this) = sr.this {
2470                quote_identifiers_recursive(this, reserved_words);
2471            }
2472            quote_identifiers_recursive(&mut sr.expression, reserved_words);
2473        }
2474
2475        // ── Lateral ───────────────────────────────────────────────
2476        Expression::Lateral(lat) => {
2477            quote_identifiers_recursive(&mut lat.this, reserved_words);
2478            // lat.alias is Option<String>, not Identifier, so we skip it
2479        }
2480
2481        // ── DPipe (|| concatenation) ──────────────────────────────
2482        Expression::DPipe(dpipe) => {
2483            quote_identifiers_recursive(&mut dpipe.this, reserved_words);
2484            quote_identifiers_recursive(&mut dpipe.expression, reserved_words);
2485        }
2486
2487        // ── Merge ─────────────────────────────────────────────────
2488        Expression::Merge(merge) => {
2489            quote_identifiers_recursive(&mut merge.this, reserved_words);
2490            quote_identifiers_recursive(&mut merge.using, reserved_words);
2491            if let Some(ref mut on) = merge.on {
2492                quote_identifiers_recursive(on, reserved_words);
2493            }
2494            if let Some(ref mut whens) = merge.whens {
2495                quote_identifiers_recursive(whens, reserved_words);
2496            }
2497            if let Some(ref mut with) = merge.with_ {
2498                quote_identifiers_recursive(with, reserved_words);
2499            }
2500            if let Some(ref mut ret) = merge.returning {
2501                quote_identifiers_recursive(ret, reserved_words);
2502            }
2503        }
2504
2505        // ── LateralView (standalone) ──────────────────────────────
2506        Expression::LateralView(lv) => {
2507            quote_lateral_view(lv, reserved_words);
2508        }
2509
2510        // ── Anonymous (generic function) ──────────────────────────
2511        Expression::Anonymous(anon) => {
2512            quote_identifiers_recursive(&mut anon.this, reserved_words);
2513            for e in &mut anon.expressions {
2514                quote_identifiers_recursive(e, reserved_words);
2515            }
2516        }
2517
2518        // ── Filter (e.g., FILTER(WHERE ...)) ──────────────────────
2519        Expression::Filter(filter) => {
2520            quote_identifiers_recursive(&mut filter.this, reserved_words);
2521            quote_identifiers_recursive(&mut filter.expression, reserved_words);
2522        }
2523
2524        // ── Returning ─────────────────────────────────────────────
2525        Expression::Returning(ret) => {
2526            for e in &mut ret.expressions {
2527                quote_identifiers_recursive(e, reserved_words);
2528            }
2529        }
2530
2531        // ── BracedWildcard ────────────────────────────────────────
2532        Expression::BracedWildcard(inner) => {
2533            quote_identifiers_recursive(inner, reserved_words);
2534        }
2535
2536        // ── ReturnStmt ────────────────────────────────────────────
2537        Expression::ReturnStmt(inner) => {
2538            quote_identifiers_recursive(inner, reserved_words);
2539        }
2540
2541        // ── Leaf nodes that never contain identifiers ─────────────
2542        Expression::Literal(_)
2543        | Expression::Boolean(_)
2544        | Expression::Null(_)
2545        | Expression::DataType(_)
2546        | Expression::Raw(_)
2547        | Expression::Placeholder(_)
2548        | Expression::CurrentDate(_)
2549        | Expression::CurrentTime(_)
2550        | Expression::CurrentTimestamp(_)
2551        | Expression::CurrentTimestampLTZ(_)
2552        | Expression::SessionUser(_)
2553        | Expression::RowNumber(_)
2554        | Expression::Rank(_)
2555        | Expression::DenseRank(_)
2556        | Expression::PercentRank(_)
2557        | Expression::CumeDist(_)
2558        | Expression::Random(_)
2559        | Expression::Pi(_)
2560        | Expression::JSONPathRoot(_) => {
2561            // Nothing to do – these are leaves or do not contain identifiers
2562        }
2563
2564        // ── Catch-all: many expression variants follow common patterns.
2565        // Rather than listing every single variant, we leave them unchanged.
2566        // The key identifier-bearing variants are covered above.
2567        _ => {}
2568    }
2569}
2570
2571/// Helper: quote identifiers in a Join.
2572fn quote_join(join: &mut Join, reserved_words: &HashSet<&str>) {
2573    quote_identifiers_recursive(&mut join.this, reserved_words);
2574    if let Some(ref mut on) = join.on {
2575        quote_identifiers_recursive(on, reserved_words);
2576    }
2577    for id in &mut join.using {
2578        maybe_quote(id, reserved_words);
2579    }
2580    if let Some(ref mut mc) = join.match_condition {
2581        quote_identifiers_recursive(mc, reserved_words);
2582    }
2583    for piv in &mut join.pivots {
2584        quote_identifiers_recursive(piv, reserved_words);
2585    }
2586}
2587
2588/// Helper: quote identifiers in a WITH clause.
2589fn quote_with(with: &mut With, reserved_words: &HashSet<&str>) {
2590    for cte in &mut with.ctes {
2591        maybe_quote(&mut cte.alias, reserved_words);
2592        for c in &mut cte.columns {
2593            maybe_quote(c, reserved_words);
2594        }
2595        for k in &mut cte.key_expressions {
2596            maybe_quote(k, reserved_words);
2597        }
2598        quote_identifiers_recursive(&mut cte.this, reserved_words);
2599    }
2600}
2601
2602/// Helper: quote identifiers in an Over clause.
2603fn quote_over(over: &mut Over, reserved_words: &HashSet<&str>) {
2604    if let Some(ref mut wn) = over.window_name {
2605        maybe_quote(wn, reserved_words);
2606    }
2607    for e in &mut over.partition_by {
2608        quote_identifiers_recursive(e, reserved_words);
2609    }
2610    for o in &mut over.order_by {
2611        quote_identifiers_recursive(&mut o.this, reserved_words);
2612    }
2613    if let Some(ref mut alias) = over.alias {
2614        maybe_quote(alias, reserved_words);
2615    }
2616}
2617
2618/// Helper: quote identifiers in a TableRef (used by DML statements).
2619fn quote_table_ref(table_ref: &mut TableRef, reserved_words: &HashSet<&str>) {
2620    maybe_quote(&mut table_ref.name, reserved_words);
2621    if let Some(ref mut schema) = table_ref.schema {
2622        maybe_quote(schema, reserved_words);
2623    }
2624    if let Some(ref mut catalog) = table_ref.catalog {
2625        maybe_quote(catalog, reserved_words);
2626    }
2627    if let Some(ref mut alias) = table_ref.alias {
2628        maybe_quote(alias, reserved_words);
2629    }
2630    for ca in &mut table_ref.column_aliases {
2631        maybe_quote(ca, reserved_words);
2632    }
2633    for p in &mut table_ref.partitions {
2634        maybe_quote(p, reserved_words);
2635    }
2636    for h in &mut table_ref.hints {
2637        quote_identifiers_recursive(h, reserved_words);
2638    }
2639}
2640
2641/// Helper: quote identifiers in a LateralView.
2642fn quote_lateral_view(lv: &mut LateralView, reserved_words: &HashSet<&str>) {
2643    quote_identifiers_recursive(&mut lv.this, reserved_words);
2644    if let Some(ref mut ta) = lv.table_alias {
2645        maybe_quote(ta, reserved_words);
2646    }
2647    for ca in &mut lv.column_aliases {
2648        maybe_quote(ca, reserved_words);
2649    }
2650}
2651
2652/// Quote identifiers that need quoting based on dialect rules.
2653///
2654/// Walks the entire AST recursively and sets `quoted = true` on any
2655/// `Identifier` that:
2656/// - contains special characters (anything not `[a-zA-Z0-9_]`)
2657/// - starts with a digit
2658/// - is a SQL reserved word for the given dialect
2659///
2660/// The function takes ownership of the expression, mutates a clone,
2661/// and returns the modified version.
2662pub fn quote_identifiers(expression: Expression, dialect: Option<DialectType>) -> Expression {
2663    let reserved_words = get_reserved_words(dialect);
2664    let mut result = expression;
2665    quote_identifiers_recursive(&mut result, &reserved_words);
2666    result
2667}
2668
2669/// Pushdown CTE alias columns into the projection.
2670///
2671/// This is useful for dialects like Snowflake where CTE alias columns
2672/// can be referenced in HAVING.
2673pub fn pushdown_cte_alias_columns(_scope: &Scope) {
2674    // Kept for API compatibility. The mutating implementation is applied within
2675    // `qualify_columns` where AST ownership is available.
2676}
2677
2678fn pushdown_cte_alias_columns_with(with: &mut With) {
2679    for cte in &mut with.ctes {
2680        if cte.columns.is_empty() {
2681            continue;
2682        }
2683
2684        if let Expression::Select(select) = &mut cte.this {
2685            let mut next_expressions = Vec::with_capacity(select.expressions.len());
2686
2687            for (i, projection) in select.expressions.iter().enumerate() {
2688                let Some(alias_name) = cte.columns.get(i) else {
2689                    next_expressions.push(projection.clone());
2690                    continue;
2691                };
2692
2693                match projection {
2694                    Expression::Alias(existing) => {
2695                        let mut aliased = existing.clone();
2696                        aliased.alias = alias_name.clone();
2697                        next_expressions.push(Expression::Alias(aliased));
2698                    }
2699                    _ => {
2700                        next_expressions.push(create_alias(projection.clone(), &alias_name.name));
2701                    }
2702                }
2703            }
2704
2705            select.expressions = next_expressions;
2706        }
2707    }
2708}
2709
2710// ============================================================================
2711// Helper functions
2712// ============================================================================
2713
2714/// Get all column references in a scope
2715fn get_scope_columns(scope: &Scope) -> Vec<ColumnRef> {
2716    let mut columns = Vec::new();
2717    collect_columns(&scope.expression, &mut columns);
2718    columns
2719}
2720
2721/// Column reference for tracking
2722#[derive(Debug, Clone)]
2723struct ColumnRef {
2724    table: Option<String>,
2725    name: String,
2726}
2727
2728/// Recursively collect column references from an expression
2729fn collect_columns(expr: &Expression, columns: &mut Vec<ColumnRef>) {
2730    match expr {
2731        Expression::Column(col) => {
2732            columns.push(ColumnRef {
2733                table: col.table.as_ref().map(|t| t.name.clone()),
2734                name: col.name.name.clone(),
2735            });
2736        }
2737        Expression::Select(select) => {
2738            for e in &select.expressions {
2739                collect_columns(e, columns);
2740            }
2741            if let Some(from) = &select.from {
2742                for e in &from.expressions {
2743                    collect_columns(e, columns);
2744                }
2745            }
2746            if let Some(where_clause) = &select.where_clause {
2747                collect_columns(&where_clause.this, columns);
2748            }
2749            if let Some(group_by) = &select.group_by {
2750                for e in &group_by.expressions {
2751                    collect_columns(e, columns);
2752                }
2753            }
2754            if let Some(having) = &select.having {
2755                collect_columns(&having.this, columns);
2756            }
2757            if let Some(order_by) = &select.order_by {
2758                for o in &order_by.expressions {
2759                    collect_columns(&o.this, columns);
2760                }
2761            }
2762            for join in &select.joins {
2763                collect_columns(&join.this, columns);
2764                if let Some(on) = &join.on {
2765                    collect_columns(on, columns);
2766                }
2767            }
2768        }
2769        Expression::Alias(alias) => {
2770            collect_columns(&alias.this, columns);
2771        }
2772        Expression::Function(func) => {
2773            for arg in &func.args {
2774                collect_columns(arg, columns);
2775            }
2776        }
2777        Expression::AggregateFunction(agg) => {
2778            for arg in &agg.args {
2779                collect_columns(arg, columns);
2780            }
2781        }
2782        Expression::And(bin)
2783        | Expression::Or(bin)
2784        | Expression::Eq(bin)
2785        | Expression::Neq(bin)
2786        | Expression::Lt(bin)
2787        | Expression::Lte(bin)
2788        | Expression::Gt(bin)
2789        | Expression::Gte(bin)
2790        | Expression::Add(bin)
2791        | Expression::Sub(bin)
2792        | Expression::Mul(bin)
2793        | Expression::Div(bin) => {
2794            collect_columns(&bin.left, columns);
2795            collect_columns(&bin.right, columns);
2796        }
2797        Expression::Not(unary) | Expression::Neg(unary) => {
2798            collect_columns(&unary.this, columns);
2799        }
2800        Expression::Paren(paren) => {
2801            collect_columns(&paren.this, columns);
2802        }
2803        Expression::Case(case) => {
2804            if let Some(operand) = &case.operand {
2805                collect_columns(operand, columns);
2806            }
2807            for (when, then) in &case.whens {
2808                collect_columns(when, columns);
2809                collect_columns(then, columns);
2810            }
2811            if let Some(else_) = &case.else_ {
2812                collect_columns(else_, columns);
2813            }
2814        }
2815        Expression::Cast(cast) => {
2816            collect_columns(&cast.this, columns);
2817        }
2818        Expression::In(in_expr) => {
2819            collect_columns(&in_expr.this, columns);
2820            for e in &in_expr.expressions {
2821                collect_columns(e, columns);
2822            }
2823            if let Some(query) = &in_expr.query {
2824                collect_columns(query, columns);
2825            }
2826        }
2827        Expression::Between(between) => {
2828            collect_columns(&between.this, columns);
2829            collect_columns(&between.low, columns);
2830            collect_columns(&between.high, columns);
2831        }
2832        Expression::Subquery(subquery) => {
2833            collect_columns(&subquery.this, columns);
2834        }
2835        _ => {}
2836    }
2837}
2838
2839/// Get unqualified columns in a scope
2840fn get_unqualified_columns(scope: &Scope) -> Vec<ColumnRef> {
2841    get_scope_columns(scope)
2842        .into_iter()
2843        .filter(|c| c.table.is_none())
2844        .collect()
2845}
2846
2847/// Get external columns (columns not resolvable in current scope)
2848fn get_external_columns(scope: &Scope) -> Vec<ColumnRef> {
2849    let source_names: HashSet<_> = scope.sources.keys().cloned().collect();
2850
2851    get_scope_columns(scope)
2852        .into_iter()
2853        .filter(|c| {
2854            if let Some(table) = &c.table {
2855                !source_names.contains(table)
2856            } else {
2857                false
2858            }
2859        })
2860        .collect()
2861}
2862
2863/// Check if a scope represents a correlated subquery
2864fn is_correlated_subquery(scope: &Scope) -> bool {
2865    scope.can_be_correlated && !get_external_columns(scope).is_empty()
2866}
2867
2868/// Check if a column represents a star (e.g., table.*)
2869fn is_star_column(col: &Column) -> bool {
2870    col.name.name == "*"
2871}
2872
2873/// Create a qualified column expression
2874fn create_qualified_column(name: &str, table: Option<&str>) -> Expression {
2875    Expression::boxed_column(Column {
2876        name: Identifier::new(name),
2877        table: table.map(Identifier::new),
2878        join_mark: false,
2879        trailing_comments: vec![],
2880        span: None,
2881        inferred_type: None,
2882    })
2883}
2884
2885/// Create an alias expression
2886fn create_alias(expr: Expression, alias_name: &str) -> Expression {
2887    Expression::Alias(Box::new(Alias {
2888        this: expr,
2889        alias: Identifier::new(alias_name),
2890        column_aliases: vec![],
2891        alias_explicit_as: false,
2892        alias_keyword: None,
2893        pre_alias_comments: vec![],
2894        trailing_comments: vec![],
2895        inferred_type: None,
2896    }))
2897}
2898
2899/// Get the output name for an expression
2900fn get_output_name(expr: &Expression) -> Option<String> {
2901    match expr {
2902        Expression::Column(col) => Some(col.name.name.clone()),
2903        Expression::Alias(alias) => Some(alias.alias.name.clone()),
2904        Expression::Identifier(id) => Some(id.name.clone()),
2905        _ => None,
2906    }
2907}
2908
2909#[cfg(test)]
2910mod tests {
2911    use super::*;
2912    use crate::expressions::DataType;
2913    use crate::generator::Generator;
2914    use crate::parser::Parser;
2915    use crate::scope::build_scope;
2916    use crate::{MappingSchema, Schema};
2917
2918    fn gen(expr: &Expression) -> String {
2919        Generator::new().generate(expr).unwrap()
2920    }
2921
2922    fn parse(sql: &str) -> Expression {
2923        Parser::parse_sql(sql).expect("Failed to parse")[0].clone()
2924    }
2925
2926    #[test]
2927    fn test_qualify_columns_options() {
2928        let options = QualifyColumnsOptions::new()
2929            .with_expand_alias_refs(true)
2930            .with_expand_stars(false)
2931            .with_dialect(DialectType::PostgreSQL)
2932            .with_allow_partial(true);
2933
2934        assert!(options.expand_alias_refs);
2935        assert!(!options.expand_stars);
2936        assert_eq!(options.dialect, Some(DialectType::PostgreSQL));
2937        assert!(options.allow_partial_qualification);
2938    }
2939
2940    #[test]
2941    fn test_get_scope_columns() {
2942        let expr = parse("SELECT a, b FROM t WHERE c = 1");
2943        let scope = build_scope(&expr);
2944        let columns = get_scope_columns(&scope);
2945
2946        assert!(columns.iter().any(|c| c.name == "a"));
2947        assert!(columns.iter().any(|c| c.name == "b"));
2948        assert!(columns.iter().any(|c| c.name == "c"));
2949    }
2950
2951    #[test]
2952    fn test_get_unqualified_columns() {
2953        let expr = parse("SELECT t.a, b FROM t");
2954        let scope = build_scope(&expr);
2955        let unqualified = get_unqualified_columns(&scope);
2956
2957        // Only 'b' should be unqualified
2958        assert!(unqualified.iter().any(|c| c.name == "b"));
2959        assert!(!unqualified.iter().any(|c| c.name == "a"));
2960    }
2961
2962    #[test]
2963    fn test_is_star_column() {
2964        let col = Column {
2965            name: Identifier::new("*"),
2966            table: Some(Identifier::new("t")),
2967            join_mark: false,
2968            trailing_comments: vec![],
2969            span: None,
2970            inferred_type: None,
2971        };
2972        assert!(is_star_column(&col));
2973
2974        let col2 = Column {
2975            name: Identifier::new("id"),
2976            table: None,
2977            join_mark: false,
2978            trailing_comments: vec![],
2979            span: None,
2980            inferred_type: None,
2981        };
2982        assert!(!is_star_column(&col2));
2983    }
2984
2985    #[test]
2986    fn test_create_qualified_column() {
2987        let expr = create_qualified_column("id", Some("users"));
2988        let sql = gen(&expr);
2989        assert!(sql.contains("users"));
2990        assert!(sql.contains("id"));
2991    }
2992
2993    #[test]
2994    fn test_create_alias() {
2995        let col = Expression::boxed_column(Column {
2996            name: Identifier::new("value"),
2997            table: None,
2998            join_mark: false,
2999            trailing_comments: vec![],
3000            span: None,
3001            inferred_type: None,
3002        });
3003        let aliased = create_alias(col, "total");
3004        let sql = gen(&aliased);
3005        assert!(sql.contains("AS") || sql.contains("total"));
3006    }
3007
3008    #[test]
3009    fn test_qualify_columns_normalizes_struct_field_access_issue_408() {
3010        let struct_type = DataType::Struct {
3011            fields: vec![crate::expressions::StructField::new(
3012                "field_value".into(),
3013                DataType::Text,
3014            )],
3015            nested: true,
3016        };
3017        let mut schema = MappingSchema::with_dialect(DialectType::DuckDB);
3018        schema
3019            .add_table(
3020                "source_table",
3021                &[
3022                    ("composite_value".into(), struct_type.clone()),
3023                    (
3024                        "nested_items".into(),
3025                        DataType::Array {
3026                            element_type: Box::new(struct_type),
3027                            dimension: None,
3028                        },
3029                    ),
3030                ],
3031                None,
3032            )
3033            .expect("schema setup");
3034
3035        let cases = [
3036            (
3037                "SELECT composite_value.field_value AS output_value FROM source_table",
3038                "source_table",
3039                "composite_value",
3040            ),
3041            (
3042                "SELECT item.field_value AS output_value FROM source_table s \
3043                 CROSS JOIN UNNEST(s.nested_items) AS expanded(item)",
3044                "expanded",
3045                "item",
3046            ),
3047        ];
3048
3049        for (sql, expected_table, expected_column) in cases {
3050            let qualified = qualify_columns(
3051                parse(sql),
3052                &schema,
3053                &QualifyColumnsOptions::new().with_dialect(DialectType::DuckDB),
3054            )
3055            .unwrap_or_else(|error| panic!("qualification failed for {sql:?}: {error}"));
3056            let Expression::Select(select) = qualified else {
3057                panic!("expected SELECT");
3058            };
3059            let Expression::Alias(alias) = &select.expressions[0] else {
3060                panic!("expected aliased projection");
3061            };
3062            let Expression::Dot(dot) = &alias.this else {
3063                panic!("expected normalized Dot, got {:?}", alias.this);
3064            };
3065            let Expression::Column(base) = &dot.this else {
3066                panic!("expected column-backed Dot");
3067            };
3068
3069            assert_eq!(
3070                base.table.as_ref().map(|table| table.name.as_str()),
3071                Some(expected_table)
3072            );
3073            assert_eq!(base.name.name, expected_column);
3074            assert_eq!(dot.field.name, "field_value");
3075        }
3076    }
3077
3078    #[test]
3079    fn test_validate_qualify_columns_success() {
3080        // All columns qualified
3081        let expr = parse("SELECT t.a, t.b FROM t");
3082        let result = validate_qualify_columns(&expr);
3083        // This may or may not error depending on scope analysis
3084        // The test verifies the function runs without panic
3085        let _ = result;
3086    }
3087
3088    #[test]
3089    fn test_collect_columns_nested() {
3090        let expr = parse("SELECT a + b, c FROM t WHERE d > 0 GROUP BY e HAVING f = 1");
3091        let mut columns = Vec::new();
3092        collect_columns(&expr, &mut columns);
3093
3094        let names: Vec<_> = columns.iter().map(|c| c.name.as_str()).collect();
3095        assert!(names.contains(&"a"));
3096        assert!(names.contains(&"b"));
3097        assert!(names.contains(&"c"));
3098        assert!(names.contains(&"d"));
3099        assert!(names.contains(&"e"));
3100        assert!(names.contains(&"f"));
3101    }
3102
3103    #[test]
3104    fn test_collect_columns_in_case() {
3105        let expr = parse("SELECT CASE WHEN a = 1 THEN b ELSE c END FROM t");
3106        let mut columns = Vec::new();
3107        collect_columns(&expr, &mut columns);
3108
3109        let names: Vec<_> = columns.iter().map(|c| c.name.as_str()).collect();
3110        assert!(names.contains(&"a"));
3111        assert!(names.contains(&"b"));
3112        assert!(names.contains(&"c"));
3113    }
3114
3115    #[test]
3116    fn test_collect_columns_in_subquery() {
3117        let expr = parse("SELECT a FROM t WHERE b IN (SELECT c FROM s)");
3118        let mut columns = Vec::new();
3119        collect_columns(&expr, &mut columns);
3120
3121        let names: Vec<_> = columns.iter().map(|c| c.name.as_str()).collect();
3122        assert!(names.contains(&"a"));
3123        assert!(names.contains(&"b"));
3124        assert!(names.contains(&"c"));
3125    }
3126
3127    #[test]
3128    fn test_qualify_outputs_basic() {
3129        let expr = parse("SELECT a, b + c FROM t");
3130        let scope = build_scope(&expr);
3131        let result = qualify_outputs(&scope);
3132        assert!(result.is_ok());
3133    }
3134
3135    #[test]
3136    fn test_qualify_columns_expands_star_with_schema() {
3137        let expr = parse("SELECT * FROM users");
3138
3139        let mut schema = MappingSchema::new();
3140        schema
3141            .add_table(
3142                "users",
3143                &[
3144                    (
3145                        "id".to_string(),
3146                        DataType::Int {
3147                            length: None,
3148                            integer_spelling: false,
3149                        },
3150                    ),
3151                    ("name".to_string(), DataType::Text),
3152                    ("email".to_string(), DataType::Text),
3153                ],
3154                None,
3155            )
3156            .expect("schema setup");
3157
3158        let result =
3159            qualify_columns(expr, &schema, &QualifyColumnsOptions::new()).expect("qualify");
3160        let sql = gen(&result);
3161
3162        assert!(!sql.contains("SELECT *"));
3163        assert!(sql.contains("users.id"));
3164        assert!(sql.contains("users.name"));
3165        assert!(sql.contains("users.email"));
3166    }
3167
3168    #[test]
3169    fn test_qualify_columns_expands_group_by_positions() {
3170        let expr = parse("SELECT a, b FROM t GROUP BY 1, 2");
3171
3172        let mut schema = MappingSchema::new();
3173        schema
3174            .add_table(
3175                "t",
3176                &[
3177                    (
3178                        "a".to_string(),
3179                        DataType::Int {
3180                            length: None,
3181                            integer_spelling: false,
3182                        },
3183                    ),
3184                    (
3185                        "b".to_string(),
3186                        DataType::Int {
3187                            length: None,
3188                            integer_spelling: false,
3189                        },
3190                    ),
3191                ],
3192                None,
3193            )
3194            .expect("schema setup");
3195
3196        let result =
3197            qualify_columns(expr, &schema, &QualifyColumnsOptions::new()).expect("qualify");
3198        let sql = gen(&result);
3199
3200        assert!(!sql.contains("GROUP BY 1"));
3201        assert!(!sql.contains("GROUP BY 2"));
3202        assert!(sql.contains("GROUP BY"));
3203        assert!(sql.contains("t.a"));
3204        assert!(sql.contains("t.b"));
3205    }
3206
3207    // ======================================================================
3208    // USING expansion tests
3209    // ======================================================================
3210
3211    #[test]
3212    fn test_expand_using_simple() {
3213        // Already-qualified column: USING→ON rewrite but no COALESCE needed
3214        let expr = parse("SELECT x.b FROM x JOIN y USING (b)");
3215
3216        let mut schema = MappingSchema::new();
3217        schema
3218            .add_table(
3219                "x",
3220                &[
3221                    ("a".to_string(), DataType::BigInt { length: None }),
3222                    ("b".to_string(), DataType::BigInt { length: None }),
3223                ],
3224                None,
3225            )
3226            .expect("schema setup");
3227        schema
3228            .add_table(
3229                "y",
3230                &[
3231                    ("b".to_string(), DataType::BigInt { length: None }),
3232                    ("c".to_string(), DataType::BigInt { length: None }),
3233                ],
3234                None,
3235            )
3236            .expect("schema setup");
3237
3238        let result =
3239            qualify_columns(expr, &schema, &QualifyColumnsOptions::new()).expect("qualify");
3240        let sql = gen(&result);
3241
3242        // USING should be replaced with ON
3243        assert!(
3244            !sql.contains("USING"),
3245            "USING should be replaced with ON: {sql}"
3246        );
3247        assert!(
3248            sql.contains("ON x.b = y.b"),
3249            "ON condition should be x.b = y.b: {sql}"
3250        );
3251        // x.b in SELECT should remain as-is (already qualified)
3252        assert!(sql.contains("SELECT x.b"), "SELECT should keep x.b: {sql}");
3253    }
3254
3255    #[test]
3256    fn test_expand_using_unqualified_coalesce() {
3257        // Unqualified USING column in SELECT should become COALESCE
3258        let expr = parse("SELECT b FROM x JOIN y USING(b)");
3259
3260        let mut schema = MappingSchema::new();
3261        schema
3262            .add_table(
3263                "x",
3264                &[
3265                    ("a".to_string(), DataType::BigInt { length: None }),
3266                    ("b".to_string(), DataType::BigInt { length: None }),
3267                ],
3268                None,
3269            )
3270            .expect("schema setup");
3271        schema
3272            .add_table(
3273                "y",
3274                &[
3275                    ("b".to_string(), DataType::BigInt { length: None }),
3276                    ("c".to_string(), DataType::BigInt { length: None }),
3277                ],
3278                None,
3279            )
3280            .expect("schema setup");
3281
3282        let result =
3283            qualify_columns(expr, &schema, &QualifyColumnsOptions::new()).expect("qualify");
3284        let sql = gen(&result);
3285
3286        assert!(
3287            sql.contains("COALESCE(x.b, y.b)"),
3288            "Unqualified USING column should become COALESCE: {sql}"
3289        );
3290        assert!(
3291            sql.contains("AS b"),
3292            "COALESCE should be aliased as 'b': {sql}"
3293        );
3294        assert!(
3295            sql.contains("ON x.b = y.b"),
3296            "ON condition should be generated: {sql}"
3297        );
3298    }
3299
3300    #[test]
3301    fn test_expand_using_with_where() {
3302        // USING column in WHERE should become COALESCE
3303        let expr = parse("SELECT b FROM x JOIN y USING(b) WHERE b = 1");
3304
3305        let mut schema = MappingSchema::new();
3306        schema
3307            .add_table(
3308                "x",
3309                &[("b".to_string(), DataType::BigInt { length: None })],
3310                None,
3311            )
3312            .expect("schema setup");
3313        schema
3314            .add_table(
3315                "y",
3316                &[("b".to_string(), DataType::BigInt { length: None })],
3317                None,
3318            )
3319            .expect("schema setup");
3320
3321        let result =
3322            qualify_columns(expr, &schema, &QualifyColumnsOptions::new()).expect("qualify");
3323        let sql = gen(&result);
3324
3325        assert!(
3326            sql.contains("WHERE COALESCE(x.b, y.b)"),
3327            "WHERE should use COALESCE for USING column: {sql}"
3328        );
3329    }
3330
3331    #[test]
3332    fn test_expand_using_multi_join() {
3333        // Three-way join with same USING column
3334        let expr = parse("SELECT b FROM x JOIN y USING(b) JOIN z USING(b)");
3335
3336        let mut schema = MappingSchema::new();
3337        for table in &["x", "y", "z"] {
3338            schema
3339                .add_table(
3340                    table,
3341                    &[("b".to_string(), DataType::BigInt { length: None })],
3342                    None,
3343                )
3344                .expect("schema setup");
3345        }
3346
3347        let result =
3348            qualify_columns(expr, &schema, &QualifyColumnsOptions::new()).expect("qualify");
3349        let sql = gen(&result);
3350
3351        // SELECT should have 3-table COALESCE
3352        assert!(
3353            sql.contains("COALESCE(x.b, y.b, z.b)"),
3354            "Should have 3-table COALESCE: {sql}"
3355        );
3356        // First join: simple ON
3357        assert!(
3358            sql.contains("ON x.b = y.b"),
3359            "First join ON condition: {sql}"
3360        );
3361    }
3362
3363    #[test]
3364    fn test_expand_using_multi_column() {
3365        // Two USING columns
3366        let expr = parse("SELECT b, c FROM y JOIN z USING(b, c)");
3367
3368        let mut schema = MappingSchema::new();
3369        schema
3370            .add_table(
3371                "y",
3372                &[
3373                    ("b".to_string(), DataType::BigInt { length: None }),
3374                    ("c".to_string(), DataType::BigInt { length: None }),
3375                ],
3376                None,
3377            )
3378            .expect("schema setup");
3379        schema
3380            .add_table(
3381                "z",
3382                &[
3383                    ("b".to_string(), DataType::BigInt { length: None }),
3384                    ("c".to_string(), DataType::BigInt { length: None }),
3385                ],
3386                None,
3387            )
3388            .expect("schema setup");
3389
3390        let result =
3391            qualify_columns(expr, &schema, &QualifyColumnsOptions::new()).expect("qualify");
3392        let sql = gen(&result);
3393
3394        assert!(
3395            sql.contains("COALESCE(y.b, z.b)"),
3396            "column 'b' should get COALESCE: {sql}"
3397        );
3398        assert!(
3399            sql.contains("COALESCE(y.c, z.c)"),
3400            "column 'c' should get COALESCE: {sql}"
3401        );
3402        // ON should have both conditions ANDed
3403        assert!(
3404            sql.contains("y.b = z.b") && sql.contains("y.c = z.c"),
3405            "ON should have both equality conditions: {sql}"
3406        );
3407    }
3408
3409    #[test]
3410    fn test_expand_using_star() {
3411        // SELECT * should deduplicate USING columns
3412        let expr = parse("SELECT * FROM x JOIN y USING(b)");
3413
3414        let mut schema = MappingSchema::new();
3415        schema
3416            .add_table(
3417                "x",
3418                &[
3419                    ("a".to_string(), DataType::BigInt { length: None }),
3420                    ("b".to_string(), DataType::BigInt { length: None }),
3421                ],
3422                None,
3423            )
3424            .expect("schema setup");
3425        schema
3426            .add_table(
3427                "y",
3428                &[
3429                    ("b".to_string(), DataType::BigInt { length: None }),
3430                    ("c".to_string(), DataType::BigInt { length: None }),
3431                ],
3432                None,
3433            )
3434            .expect("schema setup");
3435
3436        let result =
3437            qualify_columns(expr, &schema, &QualifyColumnsOptions::new()).expect("qualify");
3438        let sql = gen(&result);
3439
3440        // b should appear once as COALESCE
3441        assert!(
3442            sql.contains("COALESCE(x.b, y.b) AS b"),
3443            "USING column should be COALESCE in star expansion: {sql}"
3444        );
3445        // a and c should be normal qualified columns
3446        assert!(sql.contains("x.a"), "non-USING column a from x: {sql}");
3447        assert!(sql.contains("y.c"), "non-USING column c from y: {sql}");
3448        // b should only appear once (not duplicated from both tables)
3449        let coalesce_count = sql.matches("COALESCE").count();
3450        assert_eq!(
3451            coalesce_count, 1,
3452            "b should appear only once as COALESCE: {sql}"
3453        );
3454    }
3455
3456    #[test]
3457    fn test_expand_using_table_star() {
3458        // table.* with USING column
3459        let expr = parse("SELECT x.* FROM x JOIN y USING(b)");
3460
3461        let mut schema = MappingSchema::new();
3462        schema
3463            .add_table(
3464                "x",
3465                &[
3466                    ("a".to_string(), DataType::BigInt { length: None }),
3467                    ("b".to_string(), DataType::BigInt { length: None }),
3468                ],
3469                None,
3470            )
3471            .expect("schema setup");
3472        schema
3473            .add_table(
3474                "y",
3475                &[
3476                    ("b".to_string(), DataType::BigInt { length: None }),
3477                    ("c".to_string(), DataType::BigInt { length: None }),
3478                ],
3479                None,
3480            )
3481            .expect("schema setup");
3482
3483        let result =
3484            qualify_columns(expr, &schema, &QualifyColumnsOptions::new()).expect("qualify");
3485        let sql = gen(&result);
3486
3487        // b should become COALESCE (since x participates in USING for b)
3488        assert!(
3489            sql.contains("COALESCE(x.b, y.b)"),
3490            "USING column from x.* should become COALESCE: {sql}"
3491        );
3492        assert!(sql.contains("x.a"), "non-USING column a: {sql}");
3493    }
3494
3495    #[test]
3496    fn test_expand_natural_join_with_derived_table() {
3497        let expr = parse(
3498            "SELECT shared_key AS output_key FROM source_table \
3499             NATURAL JOIN (SELECT shared_key FROM source_table) AS derived",
3500        );
3501
3502        let mut schema = MappingSchema::new();
3503        schema
3504            .add_table(
3505                "source_table",
3506                &[(
3507                    "shared_key".to_string(),
3508                    DataType::VarChar {
3509                        length: None,
3510                        parenthesized_length: false,
3511                    },
3512                )],
3513                None,
3514            )
3515            .expect("schema setup");
3516
3517        let result = qualify_columns(
3518            expr,
3519            &schema,
3520            &QualifyColumnsOptions::new().with_dialect(DialectType::DuckDB),
3521        )
3522        .expect("qualify");
3523        let sql = gen(&result);
3524
3525        assert!(!sql.contains("NATURAL"), "NATURAL should expand: {sql}");
3526        assert!(
3527            sql.contains("ON source_table.shared_key = derived.shared_key"),
3528            "common column should become an equality condition: {sql}"
3529        );
3530        assert!(
3531            sql.contains("COALESCE(source_table.shared_key, derived.shared_key) AS output_key"),
3532            "merged projection should preserve both sources: {sql}"
3533        );
3534    }
3535
3536    #[test]
3537    fn test_expand_natural_join_star_and_chained_sources() {
3538        let expr = parse("SELECT * FROM x NATURAL JOIN y NATURAL JOIN z");
3539
3540        let mut schema = MappingSchema::new();
3541        schema
3542            .add_table(
3543                "x",
3544                &[
3545                    ("a".to_string(), DataType::BigInt { length: None }),
3546                    ("shared".to_string(), DataType::BigInt { length: None }),
3547                ],
3548                None,
3549            )
3550            .expect("schema setup");
3551        schema
3552            .add_table(
3553                "y",
3554                &[
3555                    ("shared".to_string(), DataType::BigInt { length: None }),
3556                    ("b".to_string(), DataType::BigInt { length: None }),
3557                ],
3558                None,
3559            )
3560            .expect("schema setup");
3561        schema
3562            .add_table(
3563                "z",
3564                &[
3565                    ("shared".to_string(), DataType::BigInt { length: None }),
3566                    ("c".to_string(), DataType::BigInt { length: None }),
3567                ],
3568                None,
3569            )
3570            .expect("schema setup");
3571
3572        let result =
3573            qualify_columns(expr, &schema, &QualifyColumnsOptions::new()).expect("qualify");
3574        let sql = gen(&result);
3575
3576        assert!(!sql.contains("NATURAL"), "both joins should expand: {sql}");
3577        assert!(sql.contains("ON x.shared = y.shared"), "first join: {sql}");
3578        assert!(sql.contains("ON x.shared = z.shared"), "second join: {sql}");
3579        assert_eq!(
3580            sql.matches("COALESCE(x.shared, y.shared, z.shared) AS shared")
3581                .count(),
3582            1,
3583            "merged star column should be emitted once: {sql}"
3584        );
3585        assert!(sql.contains("x.a"), "left-only column should remain: {sql}");
3586        assert!(
3587            sql.contains("y.b"),
3588            "middle-only column should remain: {sql}"
3589        );
3590        assert!(
3591            sql.contains("z.c"),
3592            "right-only column should remain: {sql}"
3593        );
3594    }
3595
3596    #[test]
3597    fn test_expand_natural_outer_join_kinds() {
3598        let mut schema = MappingSchema::new();
3599        for table in ["x", "y"] {
3600            schema
3601                .add_table(
3602                    table,
3603                    &[("shared".to_string(), DataType::BigInt { length: None })],
3604                    None,
3605                )
3606                .expect("schema setup");
3607        }
3608
3609        for (input_kind, output_kind) in [
3610            ("NATURAL LEFT JOIN", "LEFT JOIN"),
3611            ("NATURAL RIGHT JOIN", "RIGHT JOIN"),
3612            ("NATURAL FULL JOIN", "FULL JOIN"),
3613        ] {
3614            let expr = parse(&format!("SELECT shared FROM x {input_kind} y"));
3615            let result =
3616                qualify_columns(expr, &schema, &QualifyColumnsOptions::new()).expect("qualify");
3617            let sql = gen(&result);
3618
3619            assert!(
3620                !sql.contains("NATURAL"),
3621                "{input_kind} should expand: {sql}"
3622            );
3623            assert!(sql.contains(output_kind), "join kind should remain: {sql}");
3624            assert!(
3625                sql.contains("ON x.shared = y.shared"),
3626                "join condition should be derived: {sql}"
3627            );
3628        }
3629    }
3630
3631    #[test]
3632    fn test_preserve_natural_join_without_known_common_columns() {
3633        let mut no_common_schema = MappingSchema::new();
3634        no_common_schema
3635            .add_table(
3636                "x",
3637                &[("a".to_string(), DataType::BigInt { length: None })],
3638                None,
3639            )
3640            .expect("schema setup");
3641        no_common_schema
3642            .add_table(
3643                "y",
3644                &[("b".to_string(), DataType::BigInt { length: None })],
3645                None,
3646            )
3647            .expect("schema setup");
3648
3649        let no_common = qualify_columns(
3650            parse("SELECT * FROM x NATURAL JOIN y"),
3651            &no_common_schema,
3652            &QualifyColumnsOptions::new(),
3653        )
3654        .expect("qualify");
3655        assert!(
3656            gen(&no_common).contains("NATURAL JOIN"),
3657            "join without common columns should remain NATURAL"
3658        );
3659
3660        let mut partial_schema = MappingSchema::new();
3661        partial_schema
3662            .add_table(
3663                "x",
3664                &[("a".to_string(), DataType::BigInt { length: None })],
3665                None,
3666            )
3667            .expect("schema setup");
3668        let unknown_right = qualify_columns(
3669            parse("SELECT * FROM x NATURAL JOIN unknown_table"),
3670            &partial_schema,
3671            &QualifyColumnsOptions::new().with_allow_partial(true),
3672        )
3673        .expect("partial qualification");
3674        assert!(
3675            gen(&unknown_right).contains("NATURAL JOIN"),
3676            "join with an unknown schema should remain NATURAL"
3677        );
3678
3679        let unknown_left = qualify_columns(
3680            parse("SELECT * FROM unknown_table NATURAL JOIN x"),
3681            &partial_schema,
3682            &QualifyColumnsOptions::new().with_allow_partial(true),
3683        )
3684        .expect("partial qualification");
3685        assert!(
3686            gen(&unknown_left).contains("NATURAL JOIN"),
3687            "join with an unknown left schema should remain NATURAL"
3688        );
3689
3690        let partially_known_chain = qualify_columns(
3691            parse("SELECT * FROM x NATURAL JOIN unknown_table NATURAL JOIN x AS x2"),
3692            &partial_schema,
3693            &QualifyColumnsOptions::new().with_allow_partial(true),
3694        )
3695        .expect("partial qualification");
3696        assert_eq!(
3697            gen(&partially_known_chain).matches("NATURAL JOIN").count(),
3698            2,
3699            "an unknown earlier source should prevent later inferred join keys"
3700        );
3701    }
3702
3703    #[test]
3704    fn test_qualify_columns_qualified_table_name() {
3705        let expr = parse("SELECT a FROM raw.t1");
3706
3707        let mut schema = MappingSchema::new();
3708        schema
3709            .add_table(
3710                "raw.t1",
3711                &[("a".to_string(), DataType::BigInt { length: None })],
3712                None,
3713            )
3714            .expect("schema setup");
3715
3716        let result =
3717            qualify_columns(expr, &schema, &QualifyColumnsOptions::new()).expect("qualify");
3718        let sql = gen(&result);
3719
3720        assert!(
3721            sql.contains("t1.a"),
3722            "column should be qualified with table name: {sql}"
3723        );
3724
3725        // test that columns in agg functions also get qualified
3726        let expr = parse("SELECT MAX(a) FROM raw.t1");
3727        let result =
3728            qualify_columns(expr, &schema, &QualifyColumnsOptions::new()).expect("qualify");
3729        let sql = gen(&result);
3730        assert!(
3731            sql.contains("t1.a"),
3732            "column in function should be qualified with table name: {sql}"
3733        );
3734
3735        // test that columns in scalar functions also get qualified
3736        let expr = parse("SELECT ABS(a) FROM raw.t1");
3737        let result =
3738            qualify_columns(expr, &schema, &QualifyColumnsOptions::new()).expect("qualify");
3739        let sql = gen(&result);
3740        assert!(
3741            sql.contains("t1.a"),
3742            "column in function should be qualified with table name: {sql}"
3743        );
3744    }
3745
3746    #[test]
3747    fn test_qualify_columns_count_star() {
3748        // COUNT(*) uses Count { this: None } — verify qualify_columns handles it without panic
3749        let expr = parse("SELECT COUNT(*) FROM t1");
3750
3751        let mut schema = MappingSchema::new();
3752        schema
3753            .add_table(
3754                "t1",
3755                &[("id".to_string(), DataType::BigInt { length: None })],
3756                None,
3757            )
3758            .expect("schema setup");
3759
3760        let result =
3761            qualify_columns(expr, &schema, &QualifyColumnsOptions::new()).expect("qualify");
3762        let sql = gen(&result);
3763
3764        assert!(
3765            sql.contains("COUNT(*)"),
3766            "COUNT(*) should be preserved: {sql}"
3767        );
3768    }
3769
3770    #[test]
3771    fn test_qualify_columns_correlated_scalar_subquery() {
3772        let expr =
3773            parse("SELECT id, (SELECT AVG(val) FROM t2 WHERE t2.id = t1.id) AS avg_val FROM t1");
3774
3775        let mut schema = MappingSchema::new();
3776        schema
3777            .add_table(
3778                "t1",
3779                &[("id".to_string(), DataType::BigInt { length: None })],
3780                None,
3781            )
3782            .expect("schema setup");
3783        schema
3784            .add_table(
3785                "t2",
3786                &[
3787                    ("id".to_string(), DataType::BigInt { length: None }),
3788                    ("val".to_string(), DataType::BigInt { length: None }),
3789                ],
3790                None,
3791            )
3792            .expect("schema setup");
3793
3794        let result =
3795            qualify_columns(expr, &schema, &QualifyColumnsOptions::new()).expect("qualify");
3796        let sql = gen(&result);
3797
3798        assert!(
3799            sql.contains("t1.id"),
3800            "outer column should be qualified: {sql}"
3801        );
3802        assert!(
3803            sql.contains("t2.id"),
3804            "inner column should be qualified: {sql}"
3805        );
3806    }
3807
3808    #[test]
3809    fn test_qualify_columns_correlated_scalar_subquery_unqualified() {
3810        let expr =
3811            parse("SELECT t1_id, (SELECT AVG(val) FROM t2 WHERE t2_id = t1_id) AS avg_val FROM t1");
3812
3813        let mut schema = MappingSchema::new();
3814        schema
3815            .add_table(
3816                "t1",
3817                &[("t1_id".to_string(), DataType::BigInt { length: None })],
3818                None,
3819            )
3820            .expect("schema setup");
3821        schema
3822            .add_table(
3823                "t2",
3824                &[
3825                    ("t2_id".to_string(), DataType::BigInt { length: None }),
3826                    ("val".to_string(), DataType::BigInt { length: None }),
3827                ],
3828                None,
3829            )
3830            .expect("schema setup");
3831
3832        let result =
3833            qualify_columns(expr, &schema, &QualifyColumnsOptions::new()).expect("qualify");
3834        let sql = gen(&result);
3835
3836        assert!(
3837            sql.contains("t1.t1_id"),
3838            "outer column should be qualified: {sql}"
3839        );
3840        assert!(
3841            sql.contains("t2.t2_id"),
3842            "inner column should be qualified: {sql}"
3843        );
3844        // Correlated reference t1_id in inner scope should be qualified as t1.t1_id
3845        assert!(
3846            sql.contains("= t1.t1_id"),
3847            "correlated column should be qualified: {sql}"
3848        );
3849    }
3850
3851    #[test]
3852    fn test_qualify_columns_correlated_exists_subquery() {
3853        let expr = parse(
3854            "SELECT o_orderpriority FROM orders \
3855             WHERE EXISTS (SELECT * FROM lineitem WHERE l_orderkey = o_orderkey)",
3856        );
3857
3858        let mut schema = MappingSchema::new();
3859        schema
3860            .add_table(
3861                "orders",
3862                &[
3863                    ("o_orderpriority".to_string(), DataType::Text),
3864                    ("o_orderkey".to_string(), DataType::BigInt { length: None }),
3865                ],
3866                None,
3867            )
3868            .expect("schema setup");
3869        schema
3870            .add_table(
3871                "lineitem",
3872                &[("l_orderkey".to_string(), DataType::BigInt { length: None })],
3873                None,
3874            )
3875            .expect("schema setup");
3876
3877        let result =
3878            qualify_columns(expr, &schema, &QualifyColumnsOptions::new()).expect("qualify");
3879        let sql = gen(&result);
3880
3881        assert!(
3882            sql.contains("orders.o_orderpriority"),
3883            "outer column should be qualified: {sql}"
3884        );
3885        assert!(
3886            sql.contains("lineitem.l_orderkey"),
3887            "inner column should be qualified: {sql}"
3888        );
3889        assert!(
3890            sql.contains("orders.o_orderkey"),
3891            "correlated outer column should be qualified: {sql}"
3892        );
3893    }
3894
3895    #[test]
3896    fn test_qualify_columns_rejects_unknown_table() {
3897        let expr = parse("SELECT id FROM t1 WHERE nonexistent.col = 1");
3898
3899        let mut schema = MappingSchema::new();
3900        schema
3901            .add_table(
3902                "t1",
3903                &[("id".to_string(), DataType::BigInt { length: None })],
3904                None,
3905            )
3906            .expect("schema setup");
3907
3908        let result = qualify_columns(expr, &schema, &QualifyColumnsOptions::new());
3909        assert!(
3910            result.is_err(),
3911            "should reject reference to table not in scope or schema"
3912        );
3913    }
3914
3915    // ======================================================================
3916    // quote_identifiers tests
3917    // ======================================================================
3918
3919    #[test]
3920    fn test_needs_quoting_reserved_word() {
3921        let reserved = get_reserved_words(None);
3922        assert!(needs_quoting("select", &reserved));
3923        assert!(needs_quoting("SELECT", &reserved));
3924        assert!(needs_quoting("from", &reserved));
3925        assert!(needs_quoting("WHERE", &reserved));
3926        assert!(needs_quoting("join", &reserved));
3927        assert!(needs_quoting("table", &reserved));
3928    }
3929
3930    #[test]
3931    fn test_needs_quoting_normal_identifiers() {
3932        let reserved = get_reserved_words(None);
3933        assert!(!needs_quoting("foo", &reserved));
3934        assert!(!needs_quoting("my_column", &reserved));
3935        assert!(!needs_quoting("col1", &reserved));
3936        assert!(!needs_quoting("A", &reserved));
3937        assert!(!needs_quoting("_hidden", &reserved));
3938    }
3939
3940    #[test]
3941    fn test_needs_quoting_special_characters() {
3942        let reserved = get_reserved_words(None);
3943        assert!(needs_quoting("my column", &reserved)); // space
3944        assert!(needs_quoting("my-column", &reserved)); // hyphen
3945        assert!(needs_quoting("my.column", &reserved)); // dot
3946        assert!(needs_quoting("col@name", &reserved)); // at sign
3947        assert!(needs_quoting("col#name", &reserved)); // hash
3948    }
3949
3950    #[test]
3951    fn test_needs_quoting_starts_with_digit() {
3952        let reserved = get_reserved_words(None);
3953        assert!(needs_quoting("1col", &reserved));
3954        assert!(needs_quoting("123", &reserved));
3955        assert!(needs_quoting("0_start", &reserved));
3956    }
3957
3958    #[test]
3959    fn test_needs_quoting_empty() {
3960        let reserved = get_reserved_words(None);
3961        assert!(!needs_quoting("", &reserved));
3962    }
3963
3964    #[test]
3965    fn test_maybe_quote_sets_quoted_flag() {
3966        let reserved = get_reserved_words(None);
3967        let mut id = Identifier::new("select");
3968        assert!(!id.quoted);
3969        maybe_quote(&mut id, &reserved);
3970        assert!(id.quoted);
3971    }
3972
3973    #[test]
3974    fn test_maybe_quote_skips_already_quoted() {
3975        let reserved = get_reserved_words(None);
3976        let mut id = Identifier::quoted("myname");
3977        assert!(id.quoted);
3978        maybe_quote(&mut id, &reserved);
3979        assert!(id.quoted); // still quoted
3980        assert_eq!(id.name, "myname"); // name unchanged
3981    }
3982
3983    #[test]
3984    fn test_maybe_quote_skips_star() {
3985        let reserved = get_reserved_words(None);
3986        let mut id = Identifier::new("*");
3987        maybe_quote(&mut id, &reserved);
3988        assert!(!id.quoted); // star should not be quoted
3989    }
3990
3991    #[test]
3992    fn test_maybe_quote_skips_normal() {
3993        let reserved = get_reserved_words(None);
3994        let mut id = Identifier::new("normal_col");
3995        maybe_quote(&mut id, &reserved);
3996        assert!(!id.quoted);
3997    }
3998
3999    #[test]
4000    fn test_quote_identifiers_column_with_reserved_name() {
4001        // A column named "select" should be quoted
4002        let expr = Expression::boxed_column(Column {
4003            name: Identifier::new("select"),
4004            table: None,
4005            join_mark: false,
4006            trailing_comments: vec![],
4007            span: None,
4008            inferred_type: None,
4009        });
4010        let result = quote_identifiers(expr, None);
4011        if let Expression::Column(col) = &result {
4012            assert!(col.name.quoted, "Column named 'select' should be quoted");
4013        } else {
4014            panic!("Expected Column expression");
4015        }
4016    }
4017
4018    #[test]
4019    fn test_quote_identifiers_column_with_special_chars() {
4020        let expr = Expression::boxed_column(Column {
4021            name: Identifier::new("my column"),
4022            table: None,
4023            join_mark: false,
4024            trailing_comments: vec![],
4025            span: None,
4026            inferred_type: None,
4027        });
4028        let result = quote_identifiers(expr, None);
4029        if let Expression::Column(col) = &result {
4030            assert!(col.name.quoted, "Column with space should be quoted");
4031        } else {
4032            panic!("Expected Column expression");
4033        }
4034    }
4035
4036    #[test]
4037    fn test_quote_identifiers_preserves_normal_column() {
4038        let expr = Expression::boxed_column(Column {
4039            name: Identifier::new("normal_col"),
4040            table: Some(Identifier::new("my_table")),
4041            join_mark: false,
4042            trailing_comments: vec![],
4043            span: None,
4044            inferred_type: None,
4045        });
4046        let result = quote_identifiers(expr, None);
4047        if let Expression::Column(col) = &result {
4048            assert!(!col.name.quoted, "Normal column should not be quoted");
4049            assert!(
4050                !col.table.as_ref().unwrap().quoted,
4051                "Normal table should not be quoted"
4052            );
4053        } else {
4054            panic!("Expected Column expression");
4055        }
4056    }
4057
4058    #[test]
4059    fn test_quote_identifiers_table_ref_reserved() {
4060        let expr = Expression::Table(Box::new(TableRef::new("select")));
4061        let result = quote_identifiers(expr, None);
4062        if let Expression::Table(tr) = &result {
4063            assert!(tr.name.quoted, "Table named 'select' should be quoted");
4064        } else {
4065            panic!("Expected Table expression");
4066        }
4067    }
4068
4069    #[test]
4070    fn test_quote_identifiers_table_ref_schema_and_alias() {
4071        let mut tr = TableRef::new("my_table");
4072        tr.schema = Some(Identifier::new("from"));
4073        tr.alias = Some(Identifier::new("t"));
4074        let expr = Expression::Table(Box::new(tr));
4075        let result = quote_identifiers(expr, None);
4076        if let Expression::Table(tr) = &result {
4077            assert!(!tr.name.quoted, "Normal table name should not be quoted");
4078            assert!(
4079                tr.schema.as_ref().unwrap().quoted,
4080                "Schema named 'from' should be quoted"
4081            );
4082            assert!(
4083                !tr.alias.as_ref().unwrap().quoted,
4084                "Normal alias should not be quoted"
4085            );
4086        } else {
4087            panic!("Expected Table expression");
4088        }
4089    }
4090
4091    #[test]
4092    fn test_quote_identifiers_identifier_node() {
4093        let expr = Expression::Identifier(Identifier::new("order"));
4094        let result = quote_identifiers(expr, None);
4095        if let Expression::Identifier(id) = &result {
4096            assert!(id.quoted, "Identifier named 'order' should be quoted");
4097        } else {
4098            panic!("Expected Identifier expression");
4099        }
4100    }
4101
4102    #[test]
4103    fn test_quote_identifiers_alias() {
4104        let inner = Expression::boxed_column(Column {
4105            name: Identifier::new("val"),
4106            table: None,
4107            join_mark: false,
4108            trailing_comments: vec![],
4109            span: None,
4110            inferred_type: None,
4111        });
4112        let expr = Expression::Alias(Box::new(Alias {
4113            this: inner,
4114            alias: Identifier::new("select"),
4115            column_aliases: vec![Identifier::new("from")],
4116            alias_explicit_as: false,
4117            alias_keyword: None,
4118            pre_alias_comments: vec![],
4119            trailing_comments: vec![],
4120            inferred_type: None,
4121        }));
4122        let result = quote_identifiers(expr, None);
4123        if let Expression::Alias(alias) = &result {
4124            assert!(alias.alias.quoted, "Alias named 'select' should be quoted");
4125            assert!(
4126                alias.column_aliases[0].quoted,
4127                "Column alias named 'from' should be quoted"
4128            );
4129            // Inner column "val" should not be quoted
4130            if let Expression::Column(col) = &alias.this {
4131                assert!(!col.name.quoted);
4132            }
4133        } else {
4134            panic!("Expected Alias expression");
4135        }
4136    }
4137
4138    #[test]
4139    fn test_quote_identifiers_select_recursive() {
4140        // Parse a query and verify quote_identifiers walks through it
4141        let expr = parse("SELECT a, b FROM t WHERE c = 1");
4142        let result = quote_identifiers(expr, None);
4143        // "a", "b", "c", "t" are all normal identifiers, none should be quoted
4144        let sql = gen(&result);
4145        // The SQL should be unchanged since no reserved words are used
4146        assert!(sql.contains("a"));
4147        assert!(sql.contains("b"));
4148        assert!(sql.contains("t"));
4149    }
4150
4151    #[test]
4152    fn test_quote_identifiers_digit_start() {
4153        let expr = Expression::boxed_column(Column {
4154            name: Identifier::new("1col"),
4155            table: None,
4156            join_mark: false,
4157            trailing_comments: vec![],
4158            span: None,
4159            inferred_type: None,
4160        });
4161        let result = quote_identifiers(expr, None);
4162        if let Expression::Column(col) = &result {
4163            assert!(
4164                col.name.quoted,
4165                "Column starting with digit should be quoted"
4166            );
4167        } else {
4168            panic!("Expected Column expression");
4169        }
4170    }
4171
4172    #[test]
4173    fn test_quote_identifiers_with_mysql_dialect() {
4174        let reserved = get_reserved_words(Some(DialectType::MySQL));
4175        // "KILL" is reserved in MySQL
4176        assert!(needs_quoting("KILL", &reserved));
4177        // "FORCE" is reserved in MySQL
4178        assert!(needs_quoting("FORCE", &reserved));
4179    }
4180
4181    #[test]
4182    fn test_quote_identifiers_with_postgresql_dialect() {
4183        let reserved = get_reserved_words(Some(DialectType::PostgreSQL));
4184        // "ILIKE" is reserved in PostgreSQL
4185        assert!(needs_quoting("ILIKE", &reserved));
4186        // "VERBOSE" is reserved in PostgreSQL
4187        assert!(needs_quoting("VERBOSE", &reserved));
4188    }
4189
4190    #[test]
4191    fn test_quote_identifiers_with_bigquery_dialect() {
4192        let reserved = get_reserved_words(Some(DialectType::BigQuery));
4193        // "STRUCT" is reserved in BigQuery
4194        assert!(needs_quoting("STRUCT", &reserved));
4195        // "PROTO" is reserved in BigQuery
4196        assert!(needs_quoting("PROTO", &reserved));
4197    }
4198
4199    #[test]
4200    fn test_quote_identifiers_case_insensitive_reserved() {
4201        let reserved = get_reserved_words(None);
4202        assert!(needs_quoting("Select", &reserved));
4203        assert!(needs_quoting("sElEcT", &reserved));
4204        assert!(needs_quoting("FROM", &reserved));
4205        assert!(needs_quoting("from", &reserved));
4206    }
4207
4208    #[test]
4209    fn test_quote_identifiers_join_using() {
4210        // Build a join with USING identifiers that include reserved words
4211        let mut join = crate::expressions::Join {
4212            this: Expression::Table(Box::new(TableRef::new("other"))),
4213            on: None,
4214            using: vec![Identifier::new("key"), Identifier::new("value")],
4215            kind: crate::expressions::JoinKind::Inner,
4216            use_inner_keyword: false,
4217            use_outer_keyword: false,
4218            deferred_condition: false,
4219            join_hint: None,
4220            match_condition: None,
4221            pivots: vec![],
4222            comments: vec![],
4223            nesting_group: 0,
4224            directed: false,
4225        };
4226        let reserved = get_reserved_words(None);
4227        quote_join(&mut join, &reserved);
4228        // "key" is reserved, "value" is not
4229        assert!(
4230            join.using[0].quoted,
4231            "USING identifier 'key' should be quoted"
4232        );
4233        assert!(
4234            !join.using[1].quoted,
4235            "USING identifier 'value' should not be quoted"
4236        );
4237    }
4238
4239    #[test]
4240    fn test_quote_identifiers_cte() {
4241        // Build a CTE where alias is a reserved word
4242        let mut cte = crate::expressions::Cte {
4243            alias: Identifier::new("select"),
4244            this: Expression::boxed_column(Column {
4245                name: Identifier::new("x"),
4246                table: None,
4247                join_mark: false,
4248                trailing_comments: vec![],
4249                span: None,
4250                inferred_type: None,
4251            }),
4252            columns: vec![Identifier::new("from"), Identifier::new("normal")],
4253            materialized: None,
4254            key_expressions: vec![],
4255            alias_first: false,
4256            comments: Vec::new(),
4257        };
4258        let reserved = get_reserved_words(None);
4259        maybe_quote(&mut cte.alias, &reserved);
4260        for c in &mut cte.columns {
4261            maybe_quote(c, &reserved);
4262        }
4263        assert!(cte.alias.quoted, "CTE alias 'select' should be quoted");
4264        assert!(cte.columns[0].quoted, "CTE column 'from' should be quoted");
4265        assert!(
4266            !cte.columns[1].quoted,
4267            "CTE column 'normal' should not be quoted"
4268        );
4269    }
4270
4271    #[test]
4272    fn test_quote_identifiers_binary_ops_recurse() {
4273        // a_col + select_col should quote "select_col" but that's actually
4274        // just a regular name. Use actual reserved word as column name.
4275        let expr = Expression::Add(Box::new(crate::expressions::BinaryOp::new(
4276            Expression::boxed_column(Column {
4277                name: Identifier::new("select"),
4278                table: None,
4279                join_mark: false,
4280                trailing_comments: vec![],
4281                span: None,
4282                inferred_type: None,
4283            }),
4284            Expression::boxed_column(Column {
4285                name: Identifier::new("normal"),
4286                table: None,
4287                join_mark: false,
4288                trailing_comments: vec![],
4289                span: None,
4290                inferred_type: None,
4291            }),
4292        )));
4293        let result = quote_identifiers(expr, None);
4294        if let Expression::Add(bin) = &result {
4295            if let Expression::Column(left) = &bin.left {
4296                assert!(
4297                    left.name.quoted,
4298                    "'select' column should be quoted in binary op"
4299                );
4300            }
4301            if let Expression::Column(right) = &bin.right {
4302                assert!(!right.name.quoted, "'normal' column should not be quoted");
4303            }
4304        } else {
4305            panic!("Expected Add expression");
4306        }
4307    }
4308
4309    #[test]
4310    fn test_quote_identifiers_already_quoted_preserved() {
4311        // Already-quoted identifier should stay quoted even if it doesn't need it
4312        let expr = Expression::boxed_column(Column {
4313            name: Identifier::quoted("normal_name"),
4314            table: None,
4315            join_mark: false,
4316            trailing_comments: vec![],
4317            span: None,
4318            inferred_type: None,
4319        });
4320        let result = quote_identifiers(expr, None);
4321        if let Expression::Column(col) = &result {
4322            assert!(
4323                col.name.quoted,
4324                "Already-quoted identifier should remain quoted"
4325            );
4326        } else {
4327            panic!("Expected Column expression");
4328        }
4329    }
4330
4331    #[test]
4332    fn test_quote_identifiers_full_parsed_query() {
4333        // Test with a parsed query that uses reserved words as identifiers
4334        // We build the AST manually since the parser would fail on unquoted reserved words
4335        let mut select = crate::expressions::Select::new();
4336        select.expressions.push(Expression::boxed_column(Column {
4337            name: Identifier::new("order"),
4338            table: Some(Identifier::new("t")),
4339            join_mark: false,
4340            trailing_comments: vec![],
4341            span: None,
4342            inferred_type: None,
4343        }));
4344        select.from = Some(crate::expressions::From {
4345            expressions: vec![Expression::Table(Box::new(TableRef::new("t")))],
4346        });
4347        let expr = Expression::Select(Box::new(select));
4348
4349        let result = quote_identifiers(expr, None);
4350        if let Expression::Select(sel) = &result {
4351            if let Expression::Column(col) = &sel.expressions[0] {
4352                assert!(col.name.quoted, "Column named 'order' should be quoted");
4353                assert!(
4354                    !col.table.as_ref().unwrap().quoted,
4355                    "Table 't' should not be quoted"
4356                );
4357            } else {
4358                panic!("Expected Column in SELECT list");
4359            }
4360        } else {
4361            panic!("Expected Select expression");
4362        }
4363    }
4364
4365    #[test]
4366    fn test_get_reserved_words_all_dialects() {
4367        // Ensure get_reserved_words doesn't panic for any dialect
4368        let dialects = [
4369            None,
4370            Some(DialectType::Generic),
4371            Some(DialectType::MySQL),
4372            Some(DialectType::PostgreSQL),
4373            Some(DialectType::BigQuery),
4374            Some(DialectType::Snowflake),
4375            Some(DialectType::TSQL),
4376            Some(DialectType::ClickHouse),
4377            Some(DialectType::DuckDB),
4378            Some(DialectType::Hive),
4379            Some(DialectType::Spark),
4380            Some(DialectType::Trino),
4381            Some(DialectType::Oracle),
4382            Some(DialectType::Redshift),
4383        ];
4384        for dialect in &dialects {
4385            let words = get_reserved_words(*dialect);
4386            // All dialects should have basic SQL reserved words
4387            assert!(
4388                words.contains("SELECT"),
4389                "All dialects should have SELECT as reserved"
4390            );
4391            assert!(
4392                words.contains("FROM"),
4393                "All dialects should have FROM as reserved"
4394            );
4395        }
4396    }
4397}
polyglot_sql/optimizer/qualify_columns.rs

polyglot_sql/optimizer/
qualify_columns.rs
