Skip to content

armgit / dbx

Public Imported from GitHub
0

25 MB lightweight cross-platform database client for 100+ databases, including MySQL, PostgreSQL, SQLite, Redis, MongoDB, DuckDB, SQL Server, and Dameng. Built-in AI, MCP Server, CLI, desktop and Docker. | 轻量级跨平台数据库管理工具,支持 MySQL、PostgreSQL、SQLite、Redis、MongoDB、达梦等 100+ 数据库,提供桌面端、Docker、CLI、内置 AI 助手和 MCP。

75.6 KB
Raw Blame
use serde::{Deserialize, Serialize};
use std::collections::{HashMap, HashSet};
use std::sync::LazyLock;

use regex::Regex;
use sqlparser::ast::{
    Expr, FunctionArg, FunctionArgExpr, FunctionArguments, GroupByExpr, Ident, JoinConstraint, JoinOperator,
    ObjectName, ObjectNamePart, OrderByKind, Query, Select, SelectItem, SetExpr, Statement, TableFactor,
    TableWithJoins,
};
use sqlparser::dialect::{
    ClickHouseDialect, DuckDbDialect, GenericDialect, MsSqlDialect, MySqlDialect, PostgreSqlDialect, SQLiteDialect,
    SparkSqlDialect,
};
use sqlparser::keywords::Keyword;
use sqlparser::parser::{Parser, ParserError};
use sqlparser::tokenizer::{Span, Token, TokenWithSpan, Tokenizer};

use crate::models::connection::DatabaseType;
use crate::sql::{
    starts_with_duckdb_result_sql_keyword, starts_with_executable_sql_keyword, statement_ranges_for_database,
};

static CLICKHOUSE_STRICTNESS_FIRST_JOIN_RE: LazyLock<Regex> = LazyLock::new(|| {
    Regex::new(r"(?i)\b(ANY|ALL|SEMI|ANTI|ASOF)\s+(LEFT|RIGHT|FULL|INNER|CROSS)(\s+OUTER)?\s+JOIN\b")
        .expect("valid ClickHouse join strictness regex")
});
static POSTGRES_DEFAULT_PRIVILEGES_RE: LazyLock<Regex> = LazyLock::new(|| {
    Regex::new(r"(?i)^\s*ALTER\s+DEFAULT\s+PRIVILEGES\b").expect("valid PostgreSQL default privileges regex")
});

/// Oracle administrative DDL that sqlparser has no grammar for: Oracle and the
/// Oracle-compatible engines (OceanBase Oracle mode, Dameng) accept these statements, but
/// the analyzer routes them through sqlparser's generic dialect, which reports a bogus
/// `sql parser error` on statements the server happily runs (issue #9104).
static ORACLE_ADMIN_DDL_RE: LazyLock<Regex> = LazyLock::new(|| {
    Regex::new(
        r"(?is)^\s*(?:(?:create|alter|drop)\s+(?:or\s+replace\s+)?(?:public\s+)?(?:user|tablespace|profile|directory|synonym|role|session|system)\b|(?:grant|revoke)\b)",
    )
    .expect("valid Oracle administrative DDL regex")
});

#[derive(Debug, Clone, PartialEq, Eq, Serialize, Deserialize)]
pub struct SqlReferenceAnalysis {
    pub tables: Vec<SqlTableReference>,
    pub columns: Vec<SqlColumnReference>,
    pub scopes: Vec<SqlReferenceScope>,
    /// SELECT projection columns that are neither aggregated nor listed in GROUP BY.
    #[serde(default)]
    pub group_by_violations: Vec<SqlGroupByViolation>,
}

#[derive(Debug, Clone, PartialEq, Eq, Serialize, Deserialize)]
pub struct SqlTableReference {
    pub name: String,
    pub schema: Option<String>,
    pub alias: Option<String>,
    pub span: SqlTextSpan,
    pub scope_id: usize,
}

#[derive(Debug, Clone, PartialEq, Eq, Serialize, Deserialize)]
pub struct SqlColumnReference {
    pub name: String,
    pub qualifier: Option<String>,
    pub span: SqlTextSpan,
    pub scope_id: usize,
}

#[derive(Debug, Clone, PartialEq, Eq, Serialize, Deserialize)]
pub struct SqlGroupByViolation {
    pub span: SqlTextSpan,
    pub column: String,
    pub qualifier: Option<String>,
}

#[derive(Debug, Clone, PartialEq, Eq, Serialize, Deserialize)]
pub struct SqlReferenceScope {
    pub id: usize,
    pub parent_id: Option<usize>,
}

/// 1-based line and column range in SQL text.
/// Both `start_column` and `end_column` are 1-based and inclusive.
/// For example, a 1-character token at line 1, column 5 has
/// `start_line: 1, start_column: 5, end_line: 1, end_column: 5`.
#[derive(Debug, Clone, Copy, PartialEq, Eq, Serialize, Deserialize)]
pub struct SqlTextSpan {
    pub start_line: usize,
    pub start_column: usize,
    pub end_line: usize,
    pub end_column: usize,
}

impl From<Span> for SqlTextSpan {
    fn from(span: Span) -> Self {
        let start_line = span.start.line as usize;
        let start_column = span.start.column as usize;
        let end_line = span.end.line as usize;
        // sqlparser's `Span.end.column` is 1-based exclusive. Convert to 1-based
        // inclusive so that downstream consumers (both in Rust and TypeScript)
        // receive precise token bounds that do not overshoot into trailing
        // delimiters (e.g. commas or semicolons).
        let end_column = if span.end.column > 0 {
            if end_line == start_line && start_column > 0 {
                (span.end.column as usize).saturating_sub(1).max(start_column)
            } else {
                (span.end.column as usize).saturating_sub(1)
            }
        } else {
            0
        };
        Self { start_line, start_column, end_line, end_column }
    }
}

/// One SELECT projection item: the columns it references plus its output alias.
/// Columns are flagged `aggregated` when they sit inside an aggregate call or a
/// window function, which exempts them from the GROUP BY membership check.
#[derive(Default)]
struct ProjectionItemAcc {
    alias: Option<String>,
    columns: Vec<ProjectionColumnRef>,
}

#[derive(Clone)]
struct ProjectionColumnRef {
    name: String,
    qualifier: Option<String>,
    span: SqlTextSpan,
    aggregated: bool,
}

/// GROUP BY context of the SELECT currently being analyzed. Plain identifier
/// groups are recorded for membership matching; any other shape (ordinals,
/// computed expressions, `GROUP BY ALL`, rollups) marks the whole statement
/// unresolved so the conservative check skips it entirely.
#[derive(Default)]
struct GroupByContext {
    bare: HashSet<String>,
    qualified: HashSet<String>,
    unresolved: bool,
}

#[derive(Default)]
struct Analyzer {
    tables: Vec<SqlTableReference>,
    columns: Vec<SqlColumnReference>,
    scopes: Vec<SqlReferenceScope>,
    scope_stack: Vec<usize>,
    cte_scope_stack: Vec<HashSet<String>>,
    next_scope_id: usize,
    is_sqlserver: bool,
    is_spark: bool,
    group_by_violations: Vec<SqlGroupByViolation>,
    projection_items: Vec<ProjectionItemAcc>,
    current_projection_item: Option<ProjectionItemAcc>,
    aggregate_depth: usize,
    window_depth: usize,
    group_by: Option<GroupByContext>,
}

pub fn analyze_sql_references(sql: &str, dialect: Option<&str>) -> Result<SqlReferenceAnalysis, String> {
    let normalized_dialect = normalize_dialect(dialect);
    if normalized_dialect == "duckdb" && starts_with_duckdb_parser_gap_sql(sql) {
        return Ok(SqlReferenceAnalysis {
            tables: vec![],
            columns: vec![],
            scopes: vec![],
            group_by_violations: vec![],
        });
    }
    if normalized_dialect == "postgres" && starts_with_postgres_parser_gap_sql(sql) {
        return Ok(SqlReferenceAnalysis {
            tables: vec![],
            columns: vec![],
            scopes: vec![],
            group_by_violations: vec![],
        });
    }
    let parser_sql = if normalized_dialect == "clickhouse" {
        normalize_clickhouse_join_order_for_parser(sql)
    } else {
        sql.to_string()
    };

    let parsed_statements = match normalized_dialect.as_str() {
        "postgres" => Parser::parse_sql(&PostgreSqlDialect {}, &parser_sql),
        "mysql" => Parser::parse_sql(&MySqlDialect {}, &parser_sql),
        "sqlite" => Parser::parse_sql(&SQLiteDialect {}, &parser_sql),
        "sqlserver" => parse_sqlserver(&parser_sql),
        "clickhouse" => Parser::parse_sql(&ClickHouseDialect {}, &parser_sql),
        "duckdb" => Parser::parse_sql(&DuckDbDialect {}, &parser_sql),
        "spark" => parse_spark_sql(&parser_sql),
        _ => Parser::parse_sql(&GenericDialect {}, &parser_sql),
    };
    let statements = match parsed_statements {
        Ok(statements) => {
            if normalized_dialect == "postgres" && postgres_create_procedure_is_missing_body(&parser_sql) {
                return Err("Expected: procedure body after AS, found: EOF".to_string());
            }
            statements
        }
        Err(error)
            if normalized_dialect == "postgres" && is_postgres_create_procedure_parser_gap(&parser_sql, &error) =>
        {
            return Ok(SqlReferenceAnalysis {
                tables: vec![],
                columns: vec![],
                scopes: vec![],
                group_by_violations: vec![],
            });
        }
        Err(error) => match oracle_admin_ddl_gap_statements(dialect, &parser_sql) {
            Some(statements) => statements,
            None => return Err(error.to_string()),
        },
    };

    let mut analyzer = Analyzer {
        is_sqlserver: normalized_dialect == "sqlserver",
        is_spark: normalized_dialect == "spark",
        ..Analyzer::default()
    };
    for statement in statements {
        analyzer.visit_statement(&statement);
    }

    let mut analysis = SqlReferenceAnalysis {
        tables: analyzer.tables,
        columns: analyzer.columns,
        scopes: analyzer.scopes,
        group_by_violations: analyzer.group_by_violations,
    };
    // Engines that do not enforce strict GROUP BY semantics (MySQL, SQLite,
    // DuckDB, ClickHouse, Spark) accept ungrouped projection columns — MySQL
    // enforces ONLY_FULL_GROUP_BY by default since 5.7 but the frontend has no
    // access to the connection's sql_mode, so skipping is the conservative
    // choice — flagging them there would be noise.
    if matches!(normalized_dialect.as_str(), "mysql" | "sqlite" | "duckdb" | "clickhouse" | "spark") {
        analysis.group_by_violations.clear();
    }
    Ok(analysis)
}

fn parse_sqlserver(sql: &str) -> Result<Vec<Statement>, ParserError> {
    let dialect = MsSqlDialect {};
    let mut tokens = Tokenizer::new(&dialect, sql).tokenize_with_location()?;
    normalize_sqlserver_create_proc_tokens(&mut tokens);
    match Parser::new(&dialect).with_tokens_with_locations(tokens).parse_statements() {
        Ok(statements) => Ok(statements),
        Err(error) => {
            let mut fallback_tokens = Tokenizer::new(&dialect, sql).tokenize_with_location()?;
            normalize_sqlserver_create_proc_tokens(&mut fallback_tokens);
            let mut changed = normalize_sqlserver_cursor_tokens(&mut fallback_tokens);
            changed |= normalize_sqlserver_alter_table_add_tokens(&mut fallback_tokens);
            changed |= remove_sqlserver_query_hint_tokens(&mut fallback_tokens);
            if changed {
                if let Ok(statements) =
                    Parser::new(&dialect).with_tokens_with_locations(fallback_tokens).parse_statements()
                {
                    return Ok(statements);
                }
            }
            Err(error)
        }
    }
}

fn parse_spark_sql(sql: &str) -> Result<Vec<Statement>, ParserError> {
    let dialect = SparkSqlDialect {};
    let tokens = Tokenizer::new(&dialect, sql).tokenize_with_location()?;
    let mut fallback_tokens = tokens.clone();
    let fallback_changed = normalize_spark_datasource_create_table_tokens(&mut fallback_tokens)?;
    match Parser::new(&dialect).with_tokens_with_locations(tokens).parse_statements() {
        Ok(statements) => Ok(statements),
        Err(error) => {
            // sqlparser 0.62 accepts Spark's USING clause but expects later datasource clauses in Hive order.
            // The fallback removes only clauses parsed independently from the same located token stream.
            if fallback_changed {
                if let Ok(statements) =
                    Parser::new(&dialect).with_tokens_with_locations(fallback_tokens).parse_statements()
                {
                    return Ok(statements);
                }
            }
            Err(error)
        }
    }
}

fn normalize_spark_datasource_create_table_tokens(tokens: &mut Vec<TokenWithSpan>) -> Result<bool, ParserError> {
    let dialect = SparkSqlDialect {};
    let mut removals = Vec::new();
    let mut index = 0usize;

    while let Some(statement_start) = next_significant_token_index(tokens, index) {
        if matches!(tokens[statement_start].token, Token::EOF) {
            break;
        }
        if matches!(tokens[statement_start].token, Token::SemiColon) {
            index = statement_start + 1;
            continue;
        }

        let statement_end = sql_statement_end_index(tokens, statement_start);
        let significant_indexes = tokens
            .iter()
            .enumerate()
            .take(statement_end)
            .skip(statement_start)
            .filter_map(|(index, token)| (!matches!(token.token, Token::Whitespace(_))).then_some(index))
            .collect::<Vec<_>>();
        removals.extend(spark_datasource_clause_removals(tokens, &significant_indexes, &dialect)?);
        index = statement_end.saturating_add(1);
    }

    removals.sort_unstable();
    removals.dedup();
    let changed = !removals.is_empty();
    for index in removals.into_iter().rev() {
        tokens.remove(index);
    }
    Ok(changed)
}

fn spark_datasource_clause_removals(
    tokens: &[TokenWithSpan],
    indexes: &[usize],
    dialect: &SparkSqlDialect,
) -> Result<Vec<usize>, ParserError> {
    let Some(using_position) = spark_create_table_using_position(tokens, indexes) else {
        return Ok(vec![]);
    };
    let Some(provider_index) = indexes.get(using_position + 1).copied() else {
        return Ok(vec![]);
    };
    if !matches!(tokens[provider_index].token, Token::Word(_)) {
        return Parser::new(dialect).expected("data source identifier", tokens[provider_index].clone());
    }

    let mut removals = Vec::new();
    let mut seen_options = false;
    let mut seen_partitioning = false;
    let mut seen_comment = false;
    let mut seen_table_properties = false;
    let mut position = using_position + 2;
    while position < indexes.len() {
        let token = &tokens[indexes[position]];
        match unquoted_token_keyword(token) {
            Some(Keyword::OPTIONS) | Some(Keyword::TBLPROPERTIES) => {
                let keyword = unquoted_token_keyword(token).expect("matched option keyword");
                let (seen, clause_name) = match keyword {
                    Keyword::OPTIONS => (&mut seen_options, "OPTIONS"),
                    Keyword::TBLPROPERTIES => (&mut seen_table_properties, "TBLPROPERTIES"),
                    _ => unreachable!("matched option keyword"),
                };
                if *seen {
                    return Parser::new(dialect).expected(&format!("at most one {clause_name} clause"), token.clone());
                }
                *seen = true;
                let Some(open_index) = indexes.get(position + 1).copied() else {
                    return Ok(vec![]);
                };
                if !matches!(tokens[open_index].token, Token::LParen) {
                    return Ok(vec![]);
                }
                let Some(close_index) = matching_parenthesis_index(tokens, open_index) else {
                    return Ok(vec![]);
                };
                let Some(close_position) = indexes.iter().position(|index| *index == close_index) else {
                    return Ok(vec![]);
                };
                if !spark_tokens_parse_options(tokens, &indexes[position..=close_position], keyword, dialect) {
                    return Ok(vec![]);
                }
                removals.extend_from_slice(&indexes[position..=close_position]);
                position = close_position + 1;
            }
            Some(Keyword::PARTITIONED) => {
                if seen_partitioning {
                    return Parser::new(dialect).expected("at most one PARTITIONED BY clause", token.clone());
                }
                seen_partitioning = true;
                if indexes.get(position + 1).and_then(|index| unquoted_token_keyword(&tokens[*index]))
                    != Some(Keyword::BY)
                {
                    return Ok(vec![]);
                }
                let Some(open_index) = indexes.get(position + 2).copied() else {
                    return Ok(vec![]);
                };
                if !matches!(tokens[open_index].token, Token::LParen) {
                    return Ok(vec![]);
                }
                let Some(close_index) = matching_parenthesis_index(tokens, open_index) else {
                    return Ok(vec![]);
                };
                let Some(close_position) = indexes.iter().position(|index| *index == close_index) else {
                    return Ok(vec![]);
                };
                if !spark_tokens_parse_partitioning(tokens, &indexes[position..=close_position], dialect) {
                    return Ok(vec![]);
                }
                removals.extend_from_slice(&indexes[position..=close_position]);
                position = close_position + 1;
            }
            Some(Keyword::COMMENT) => {
                if seen_comment {
                    return Parser::new(dialect).expected("at most one COMMENT clause", token.clone());
                }
                seen_comment = true;
                let Some(comment_index) = indexes.get(position + 1).copied() else {
                    return Ok(vec![]);
                };
                if !matches!(tokens[comment_index].token, Token::SingleQuotedString(_)) {
                    return Ok(vec![]);
                }
                removals.extend_from_slice(&indexes[position..=position + 1]);
                position += 2;
            }
            Some(Keyword::LOCATION) => {
                let Some(location_index) = indexes.get(position + 1).copied() else {
                    return Ok(vec![]);
                };
                if !matches!(tokens[location_index].token, Token::SingleQuotedString(_)) {
                    return Ok(vec![]);
                }
                position += 2;
            }
            Some(Keyword::AS) => break,
            _ => return Ok(vec![]),
        }
    }
    Ok(removals)
}

fn spark_create_table_using_position(tokens: &[TokenWithSpan], indexes: &[usize]) -> Option<usize> {
    if indexes.first().and_then(|index| unquoted_token_keyword(&tokens[*index])) != Some(Keyword::CREATE) {
        return None;
    }
    let mut position = 1usize;
    if indexes.get(position).and_then(|index| unquoted_token_keyword(&tokens[*index])) == Some(Keyword::OR)
        && indexes.get(position + 1).and_then(|index| unquoted_token_keyword(&tokens[*index])) == Some(Keyword::REPLACE)
    {
        position += 2;
    }
    while indexes.get(position).is_some_and(|index| {
        ["GLOBAL", "LOCAL", "TEMP", "TEMPORARY", "EXTERNAL"]
            .iter()
            .any(|modifier| unquoted_token_word_eq(&tokens[*index], modifier))
    }) {
        position += 1;
    }
    if indexes.get(position).and_then(|index| unquoted_token_keyword(&tokens[*index])) != Some(Keyword::TABLE) {
        return None;
    }

    let mut depth = 0usize;
    for (position, index) in indexes.iter().copied().enumerate().skip(position + 1) {
        match tokens[index].token {
            Token::LParen => depth += 1,
            Token::RParen => depth = depth.saturating_sub(1),
            _ if depth == 0 && unquoted_token_keyword(&tokens[index]) == Some(Keyword::AS) => return None,
            _ if depth == 0 && unquoted_token_keyword(&tokens[index]) == Some(Keyword::USING) => {
                return Some(position);
            }
            _ => {}
        }
    }
    None
}

fn spark_tokens_parse_options(
    tokens: &[TokenWithSpan],
    indexes: &[usize],
    keyword: Keyword,
    dialect: &SparkSqlDialect,
) -> bool {
    let clause_tokens = indexes.iter().map(|index| tokens[*index].clone()).collect();
    let mut parser = Parser::new(dialect).with_tokens_with_locations(clause_tokens);
    parser.parse_options(keyword).is_ok() && matches!(parser.peek_token_ref().token, Token::EOF)
}

fn spark_tokens_parse_partitioning(tokens: &[TokenWithSpan], indexes: &[usize], dialect: &SparkSqlDialect) -> bool {
    let clause_tokens = indexes.iter().map(|index| tokens[*index].clone()).collect();
    let mut parser = Parser::new(dialect).with_tokens_with_locations(clause_tokens);
    if parser.expect_keywords(&[Keyword::PARTITIONED, Keyword::BY]).is_err()
        || parser.expect_token(&Token::LParen).is_err()
    {
        return false;
    }
    let Ok(expressions) = parser.parse_comma_separated(Parser::parse_expr) else {
        return false;
    };
    !expressions.is_empty()
        && parser.expect_token(&Token::RParen).is_ok()
        && matches!(parser.peek_token_ref().token, Token::EOF)
}

fn normalize_sqlserver_cursor_tokens(tokens: &mut Vec<TokenWithSpan>) -> bool {
    let mut removals = Vec::new();
    let mut index = 0usize;

    while let Some(statement_start) = next_significant_token_index(tokens, index) {
        if matches!(tokens[statement_start].token, Token::EOF) {
            break;
        }
        if matches!(tokens[statement_start].token, Token::SemiColon) {
            index = statement_start + 1;
            continue;
        }

        let statement_end = sql_statement_end_index(tokens, statement_start);
        let significant_indexes = tokens
            .iter()
            .enumerate()
            .take(statement_end)
            .skip(statement_start)
            .filter_map(|(index, token)| (!matches!(token.token, Token::Whitespace(_))).then_some(index))
            .collect::<Vec<_>>();

        if let Some(removal) = sqlserver_cursor_option_removal(tokens, &significant_indexes) {
            removals.push(removal);
        }

        index = statement_end.saturating_add(1);
    }

    let changed = !removals.is_empty();
    for removal in removals.into_iter().rev() {
        tokens.drain(removal);
    }
    changed
}

fn sqlserver_cursor_option_removal(tokens: &[TokenWithSpan], indexes: &[usize]) -> Option<std::ops::Range<usize>> {
    if indexes.len() < 4
        || !unquoted_token_word_eq(&tokens[indexes[0]], "DECLARE")
        || !is_sqlserver_named_cursor(&tokens[indexes[1]])
        || !unquoted_token_word_eq(&tokens[indexes[2]], "CURSOR")
    {
        return None;
    }

    let mut position = 3usize;
    if indexes.get(position).is_some_and(|index| {
        unquoted_token_word_eq(&tokens[*index], "LOCAL") || unquoted_token_word_eq(&tokens[*index], "GLOBAL")
    }) {
        position += 1;
    }
    if indexes.get(position).is_some_and(|index| unquoted_token_word_eq(&tokens[*index], "FAST_FORWARD")) {
        position += 1;
    }
    if position == 3 || !indexes.get(position).is_some_and(|index| unquoted_token_word_eq(&tokens[*index], "FOR")) {
        return None;
    }

    let query_position = position + 1;
    let query_start = *indexes.get(query_position)?;
    if (!unquoted_token_word_eq(&tokens[query_start], "SELECT")
        && !unquoted_token_word_eq(&tokens[query_start], "WITH"))
        || sqlserver_cursor_query_has_forbidden_clause(tokens, &indexes[query_position..])
    {
        return None;
    }

    Some(indexes[3]..indexes[position])
}

fn is_sqlserver_named_cursor(token: &TokenWithSpan) -> bool {
    let Token::Word(word) = &token.token else {
        return false;
    };
    !word.value.starts_with('@') && (word.quote_style.is_some() || word.keyword == Keyword::NoKeyword)
}

fn sqlserver_cursor_query_has_forbidden_clause(tokens: &[TokenWithSpan], indexes: &[usize]) -> bool {
    let mut depth = 0usize;
    for (position, index) in indexes.iter().copied().enumerate() {
        match tokens[index].token {
            Token::LParen => depth += 1,
            Token::RParen => depth = depth.saturating_sub(1),
            _ if depth == 0
                && (unquoted_token_word_eq(&tokens[index], "INTO")
                    || unquoted_token_word_eq(&tokens[index], "COMPUTE")
                    || unquoted_token_word_eq(&tokens[index], "FOR")
                        && indexes
                            .get(position + 1)
                            .is_some_and(|index| unquoted_token_word_eq(&tokens[*index], "BROWSE"))) =>
            {
                return true;
            }
            _ => {}
        }
    }
    false
}

fn unquoted_token_word_eq(token: &TokenWithSpan, expected: &str) -> bool {
    matches!(&token.token, Token::Word(word) if word.quote_style.is_none() && word.value.eq_ignore_ascii_case(expected))
}

fn normalize_sqlserver_alter_table_add_tokens(tokens: &mut Vec<TokenWithSpan>) -> bool {
    let mut insertions = Vec::new();
    let mut index = 0usize;

    while index < tokens.len() {
        if unquoted_token_keyword(&tokens[index]) != Some(Keyword::ALTER) {
            index += 1;
            continue;
        }
        let Some(table_index) = next_significant_token_index(tokens, index + 1) else {
            break;
        };
        if unquoted_token_keyword(&tokens[table_index]) != Some(Keyword::TABLE) {
            index += 1;
            continue;
        }

        let statement_end = sql_statement_end_index(tokens, table_index + 1);
        let Some(add_index) = sqlserver_alter_table_add_index(tokens, table_index + 1, statement_end) else {
            index = statement_end.saturating_add(1);
            continue;
        };
        let add_token = tokens[add_index].clone();
        let mut depth = 0usize;
        for token_index in add_index + 1..statement_end {
            match tokens[token_index].token {
                Token::LParen => depth += 1,
                Token::RParen => depth = depth.saturating_sub(1),
                Token::Comma if depth == 0 => {
                    let Some(next_index) = next_significant_token_index(tokens, token_index + 1) else {
                        continue;
                    };
                    if next_index >= statement_end {
                        continue;
                    }
                    if is_sqlserver_alter_table_operation_starter(&tokens[next_index]) {
                        break;
                    }
                    insertions.push((next_index, add_token.clone()));
                }
                _ => {}
            }
        }
        index = statement_end.saturating_add(1);
    }

    let changed = !insertions.is_empty();
    for (index, token) in insertions.into_iter().rev() {
        tokens.insert(index, token);
    }
    changed
}

fn sql_statement_end_index(tokens: &[TokenWithSpan], start: usize) -> usize {
    let mut depth = 0usize;
    for (index, token) in tokens.iter().enumerate().skip(start) {
        match token.token {
            Token::LParen => depth += 1,
            Token::RParen => depth = depth.saturating_sub(1),
            Token::SemiColon | Token::EOF if depth == 0 => return index,
            _ => {}
        }
    }
    tokens.len()
}

fn sqlserver_alter_table_add_index(tokens: &[TokenWithSpan], start: usize, end: usize) -> Option<usize> {
    let mut depth = 0usize;
    for (index, token) in tokens.iter().enumerate().take(end).skip(start) {
        match token.token {
            Token::LParen => depth += 1,
            Token::RParen => depth = depth.saturating_sub(1),
            _ if depth == 0 && unquoted_token_keyword(token) == Some(Keyword::ADD) => return Some(index),
            _ => {}
        }
    }
    None
}

fn is_sqlserver_alter_table_operation_starter(token: &TokenWithSpan) -> bool {
    let Token::Word(word) = &token.token else {
        return false;
    };
    if word.quote_style.is_some() {
        return false;
    }

    matches!(
        word.value.to_ascii_uppercase().as_str(),
        "ADD"
            | "ALTER"
            | "DISABLE"
            | "DROP"
            | "ENABLE"
            | "NOCHECK"
            | "PARTITION"
            | "REBUILD"
            | "RENAME"
            | "REPLICA"
            | "SET"
            | "SWAP"
            | "SWITCH"
    )
}

fn remove_sqlserver_query_hint_tokens(tokens: &mut Vec<TokenWithSpan>) -> bool {
    let mut depth = 0usize;
    let mut ranges = Vec::new();

    for index in 0..tokens.len() {
        match &tokens[index].token {
            Token::LParen => depth += 1,
            Token::RParen => depth = depth.saturating_sub(1),
            Token::Word(word)
                if depth == 0 && word.quote_style.is_none() && word.value.eq_ignore_ascii_case("OPTION") =>
            {
                if let Some(range) = sqlserver_query_hint_range(tokens, index) {
                    if sqlserver_query_hint_removal_parses_statement(tokens, range) {
                        ranges.push(range);
                    }
                }
            }
            _ => {}
        }
    }

    let removed = !ranges.is_empty();
    for (start, end) in ranges.into_iter().rev() {
        tokens.drain(start..=end);
    }
    removed
}

fn sqlserver_query_hint_removal_parses_statement(tokens: &[TokenWithSpan], range: (usize, usize)) -> bool {
    let statement_start = tokens[..range.0]
        .iter()
        .rposition(|token| matches!(token.token, Token::SemiColon))
        .map_or(0, |index| index + 1);
    let statement_end = next_significant_token_index(tokens, range.1 + 1).unwrap_or(tokens.len() - 1);
    let mut statement_tokens = tokens[statement_start..=statement_end].to_vec();

    if parse_sqlserver_tokens(statement_tokens.clone()).is_ok() {
        return false;
    }

    statement_tokens.drain(range.0 - statement_start..=range.1 - statement_start);
    parse_sqlserver_tokens(statement_tokens).is_ok()
}

fn parse_sqlserver_tokens(tokens: Vec<TokenWithSpan>) -> Result<Vec<Statement>, ParserError> {
    Parser::new(&MsSqlDialect {}).with_tokens_with_locations(tokens).parse_statements()
}

fn sqlserver_query_hint_range(tokens: &[TokenWithSpan], option_index: usize) -> Option<(usize, usize)> {
    let open_index = next_significant_token_index(tokens, option_index + 1)?;
    if !matches!(tokens[open_index].token, Token::LParen) {
        return None;
    }

    let close_index = matching_parenthesis_index(tokens, open_index)?;
    let hint_index = next_significant_token_index(tokens, open_index + 1)?;
    if hint_index >= close_index || !is_sqlserver_query_hint_starter(&tokens[hint_index]) {
        return None;
    }

    if let Some(next_index) = next_significant_token_index(tokens, close_index + 1) {
        if !matches!(tokens[next_index].token, Token::SemiColon | Token::EOF) {
            return None;
        }
    }

    Some((option_index, close_index))
}

fn next_significant_token_index(tokens: &[TokenWithSpan], mut index: usize) -> Option<usize> {
    while index < tokens.len() {
        if !matches!(tokens[index].token, Token::Whitespace(_)) {
            return Some(index);
        }
        index += 1;
    }
    None
}

fn matching_parenthesis_index(tokens: &[TokenWithSpan], open_index: usize) -> Option<usize> {
    let mut depth = 0usize;
    for (index, token) in tokens.iter().enumerate().skip(open_index) {
        match token.token {
            Token::LParen => depth += 1,
            Token::RParen => {
                depth = depth.checked_sub(1)?;
                if depth == 0 {
                    return Some(index);
                }
            }
            _ => {}
        }
    }
    None
}

fn is_sqlserver_query_hint_starter(token: &TokenWithSpan) -> bool {
    let Token::Word(word) = &token.token else {
        return false;
    };
    if word.quote_style.is_some() {
        return false;
    }

    matches!(
        word.value.to_ascii_uppercase().as_str(),
        "CONCAT"
            | "DISABLE"
            | "DISABLE_OPTIMIZED_PLAN_FORCING"
            | "EXPAND"
            | "FAST"
            | "FOR"
            | "FORCE"
            | "HASH"
            | "IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX"
            | "KEEP"
            | "KEEPFIXED"
            | "LABEL"
            | "LOOP"
            | "MAX_GRANT_PERCENT"
            | "MAXDOP"
            | "MAXRECURSION"
            | "MERGE"
            | "MIN_GRANT_PERCENT"
            | "NO_PERFORMANCE_SPOOL"
            | "OPTIMIZE"
            | "ORDER"
            | "PARAMETERIZATION"
            | "QUERYTRACEON"
            | "RECOMPILE"
            | "ROBUST"
            | "TABLE"
            | "USE"
    )
}

fn normalize_sqlserver_create_proc_tokens(tokens: &mut [TokenWithSpan]) {
    let significant_indexes: Vec<usize> = tokens
        .iter()
        .enumerate()
        .filter_map(|(index, token)| (!matches!(token.token, Token::Whitespace(_))).then_some(index))
        .collect();

    for (position, index) in significant_indexes.iter().copied().enumerate() {
        if token_keyword(&tokens[index]) != Some(Keyword::CREATE) {
            continue;
        }

        let mut proc_position = position + 1;
        if significant_indexes.get(proc_position).and_then(|index| token_keyword(&tokens[*index])) == Some(Keyword::OR)
        {
            proc_position += 1;
            if significant_indexes.get(proc_position).and_then(|index| token_keyword(&tokens[*index]))
                != Some(Keyword::ALTER)
            {
                continue;
            }
            proc_position += 1;
        }

        let Some(proc_index) = significant_indexes.get(proc_position).copied() else {
            continue;
        };
        let Token::Word(word) = &mut tokens[proc_index].token else {
            continue;
        };
        // SQL Server documents PROC as a contextual synonym for PROCEDURE after CREATE.
        if word.quote_style.is_none() && word.value.eq_ignore_ascii_case("proc") {
            word.keyword = Keyword::PROCEDURE;
        }
    }
}

fn token_keyword(token: &TokenWithSpan) -> Option<Keyword> {
    match &token.token {
        Token::Word(word) => Some(word.keyword),
        _ => None,
    }
}

fn unquoted_token_keyword(token: &TokenWithSpan) -> Option<Keyword> {
    match &token.token {
        Token::Word(word) if word.quote_style.is_none() => Some(word.keyword),
        _ => None,
    }
}

fn starts_with_duckdb_parser_gap_sql(sql: &str) -> bool {
    starts_with_duckdb_result_sql_keyword(sql)
        && starts_with_executable_sql_keyword(sql, &["FROM", "SUMMARIZE", "SUMMARISE", "PIVOT", "UNPIVOT"])
}

/// Oracle-compatible dialects whose statements the analyzer parses with sqlparser's
/// generic dialect. Oracle connections report the PL/SQL formatter id (`oracle`) as
/// their analysis dialect, and Dameng follows the same Oracle-compatible grammar.
fn is_oracle_compatible_analysis_dialect(dialect: Option<&str>) -> bool {
    let Some(name) = dialect else {
        return false;
    };
    let name = name.trim().to_ascii_lowercase();
    matches!(name.as_str(), "oracle" | "dameng")
}

/// Re-parses `sql` with every Oracle administrative DDL statement blanked out, so the
/// statements around it keep their diagnostics instead of the whole script failing on a
/// false syntax error. Returns `None` when nothing matched or when the rest of the script
/// still does not parse, in which case the caller keeps the original parser error.
fn oracle_admin_ddl_gap_statements(dialect: Option<&str>, sql: &str) -> Option<Vec<Statement>> {
    if !is_oracle_compatible_analysis_dialect(dialect) {
        return None;
    }
    let mut masked: Option<String> = None;
    for range in statement_ranges_for_database(sql, DatabaseType::Oracle) {
        if !ORACLE_ADMIN_DDL_RE.is_match(&range.text) {
            continue;
        }
        let target = masked.get_or_insert_with(|| sql.to_string());
        let blanked = blank_out_statement_text(target.get(range.start..range.end)?);
        target.replace_range(range.start..range.end, &blanked);
    }
    Parser::parse_sql(&GenericDialect {}, masked.as_deref()?).ok()
}

/// Replaces every character of a statement with a space while keeping line breaks and the
/// original byte length, so token positions of the statements that follow stay exact.
fn blank_out_statement_text(statement: &str) -> String {
    let mut blanked = String::with_capacity(statement.len());
    for character in statement.chars() {
        if character == '\n' || character == '\r' {
            blanked.push(character);
        } else {
            for _ in 0..character.len_utf8() {
                blanked.push(' ');
            }
        }
    }
    blanked
}

fn starts_with_postgres_parser_gap_sql(sql: &str) -> bool {
    POSTGRES_DEFAULT_PRIVILEGES_RE.is_match(sql)
}

struct PostgresCreateProcedureLayout {
    tokens: Vec<TokenWithSpan>,
    significant_indexes: Vec<usize>,
    or_replace_indexes: Option<[usize; 2]>,
    parameters_open_position: usize,
    parameters_close_position: usize,
}

fn postgres_create_procedure_layout(sql: &str) -> Option<PostgresCreateProcedureLayout> {
    let dialect = PostgreSqlDialect {};
    let tokens = Tokenizer::new(&dialect, sql).tokenize_with_location().ok()?;
    let significant_indexes: Vec<usize> = tokens
        .iter()
        .enumerate()
        .filter_map(|(index, token)| (!matches!(token.token, Token::Whitespace(_))).then_some(index))
        .collect();
    let mut position = 0;

    if significant_indexes.get(position).and_then(|index| token_keyword(&tokens[*index])) != Some(Keyword::CREATE) {
        return None;
    }
    position += 1;

    let or_replace_indexes = if significant_indexes.get(position).and_then(|index| token_keyword(&tokens[*index]))
        == Some(Keyword::OR)
        && significant_indexes.get(position + 1).and_then(|index| token_keyword(&tokens[*index]))
            == Some(Keyword::REPLACE)
    {
        let indexes = [significant_indexes[position], significant_indexes[position + 1]];
        position += 2;
        Some(indexes)
    } else {
        None
    };

    if significant_indexes.get(position).and_then(|index| token_keyword(&tokens[*index])) != Some(Keyword::PROCEDURE) {
        return None;
    }
    position += 1;

    if !matches!(tokens[significant_indexes.get(position).copied()?].token, Token::Word(_)) {
        return None;
    }
    position += 1;
    while matches!(significant_indexes.get(position).map(|index| &tokens[*index].token), Some(Token::Period)) {
        position += 1;
        if !matches!(tokens[significant_indexes.get(position).copied()?].token, Token::Word(_)) {
            return None;
        }
        position += 1;
    }

    let parameters_open_index = significant_indexes.get(position).copied()?;
    if !matches!(tokens[parameters_open_index].token, Token::LParen) {
        return None;
    }
    let parameters_close_index = matching_parenthesis_index(&tokens, parameters_open_index)?;
    let parameters_close_position = significant_indexes.iter().position(|index| *index == parameters_close_index)?;
    if !postgres_procedure_parameters_are_nonempty(&tokens, &significant_indexes, position, parameters_close_position) {
        return None;
    }

    Some(PostgresCreateProcedureLayout {
        tokens,
        significant_indexes,
        or_replace_indexes,
        parameters_open_position: position,
        parameters_close_position,
    })
}

fn postgres_procedure_parameters_are_nonempty(
    tokens: &[TokenWithSpan],
    significant_indexes: &[usize],
    open_position: usize,
    close_position: usize,
) -> bool {
    if close_position == open_position + 1 {
        return true;
    }

    let mut depth = 1;
    let mut parameter_has_tokens = false;
    for index in significant_indexes.iter().take(close_position).skip(open_position + 1).copied() {
        match tokens[index].token {
            Token::LParen => {
                depth += 1;
                parameter_has_tokens = true;
            }
            Token::RParen => depth -= 1,
            Token::Comma if depth == 1 => {
                if !parameter_has_tokens {
                    return false;
                }
                parameter_has_tokens = false;
            }
            _ if depth == 1 => parameter_has_tokens = true,
            _ => {}
        }
    }
    parameter_has_tokens
}

fn postgres_create_procedure_is_missing_body(sql: &str) -> bool {
    let Some(layout) = postgres_create_procedure_layout(sql) else {
        return false;
    };
    let mut tail = &layout.significant_indexes[layout.parameters_close_position + 1..];
    while let Some(index) = tail.last() {
        if matches!(layout.tokens[*index].token, Token::SemiColon) {
            tail = &tail[..tail.len() - 1];
        } else {
            break;
        }
    }
    tail.last().and_then(|index| token_keyword(&layout.tokens[*index])) == Some(Keyword::AS)
}

fn is_postgres_create_procedure_parser_gap(sql: &str, error: &ParserError) -> bool {
    let Some(mut layout) = postgres_create_procedure_layout(sql) else {
        return false;
    };
    let Some(body_position) =
        (layout.parameters_close_position + 1..layout.significant_indexes.len()).find(|position| {
            matches!(layout.tokens[layout.significant_indexes[*position]].token, Token::DollarQuotedString(_))
        })
    else {
        return false;
    };
    let as_index = layout.significant_indexes[body_position - 1];
    if token_keyword(&layout.tokens[as_index]) != Some(Keyword::AS) {
        return false;
    }
    let trailing = &layout.significant_indexes[body_position + 1..];
    if !(trailing.is_empty() || trailing.len() == 1 && matches!(layout.tokens[trailing[0]].token, Token::SemiColon)) {
        return false;
    }

    let ParserError::ParserError(message) = error else {
        return false;
    };
    let error_matches_gap = if layout.or_replace_indexes.is_some() {
        message.starts_with(
            "Expected: [EXTERNAL] TABLE or [MATERIALIZED] VIEW or FUNCTION after CREATE OR REPLACE, found: PROCEDURE at ",
        )
    } else {
        message.starts_with(&format!(
            "Expected: an SQL statement, found: {} at ",
            layout.tokens[layout.significant_indexes[body_position]]
        ))
    };
    if !error_matches_gap {
        return false;
    }

    let mut depth = 1;
    for position in layout.parameters_open_position + 1..layout.parameters_close_position {
        let index = layout.significant_indexes[position];
        match layout.tokens[index].token {
            Token::LParen => depth += 1,
            Token::RParen => depth -= 1,
            _ if depth == 1 && token_keyword(&layout.tokens[index]) == Some(Keyword::DEFAULT) => {
                layout.tokens[index].token = Token::Eq;
            }
            _ => {}
        }
    }

    let dialect = PostgreSqlDialect {};
    let body_index = layout.significant_indexes[body_position];
    let replacement = match Tokenizer::new(&dialect, "BEGIN SELECT 1; END").tokenize_with_location() {
        Ok(tokens) => tokens,
        Err(_) => return false,
    };
    layout.tokens.splice(body_index..=body_index, replacement);
    if let Some([or_index, replace_index]) = layout.or_replace_indexes {
        layout.tokens.remove(replace_index);
        layout.tokens.remove(or_index);
    }

    Parser::new(&dialect).with_tokens_with_locations(layout.tokens).parse_statements().is_ok()
}

fn normalize_clickhouse_join_order_for_parser(sql: &str) -> String {
    CLICKHOUSE_STRICTNESS_FIRST_JOIN_RE
        .replace_all(sql, |captures: &regex::Captures<'_>| {
            let matched_len = captures.get(0).map(|value| value.as_str().len()).unwrap_or(0);
            let strictness = captures.get(1).map(|value| value.as_str()).unwrap_or("");
            let join_type = captures.get(2).map(|value| value.as_str()).unwrap_or("");
            let outer = captures.get(3).map(|value| value.as_str()).unwrap_or("");
            let mut normalized = match strictness.to_ascii_uppercase().as_str() {
                "SEMI" | "ANTI" => format!("{join_type}{outer} {strictness} JOIN"),
                _ => format!("{join_type}{outer} JOIN"),
            };
            if normalized.len() < matched_len {
                normalized.push_str(&" ".repeat(matched_len - normalized.len()));
            }
            normalized
        })
        .into_owned()
}

fn normalize_dialect(dialect: Option<&str>) -> String {
    match dialect.unwrap_or("generic").to_ascii_lowercase().as_str() {
        "postgres" | "postgresql" | "redshift" | "opengauss" | "gaussdb" | "highgo" | "uxdb" | "questdb" => {
            "postgres".to_string()
        }
        "mysql" | "mariadb" | "doris" | "starrocks" | "manticoresearch" | "oceanbase" => "mysql".to_string(),
        "sqlite" => "sqlite".to_string(),
        "sqlserver" | "mssql" => "sqlserver".to_string(),
        "clickhouse" => "clickhouse".to_string(),
        "duckdb" => "duckdb".to_string(),
        "spark" | "sparksql" => "spark".to_string(),
        _ => "generic".to_string(),
    }
}

impl Analyzer {
    fn visit_statement(&mut self, statement: &Statement) {
        match statement {
            Statement::Query(query) => self.visit_query_in_new_scope(query, None),
            Statement::CreateTable(create_table) if self.is_spark => {
                if let Some(query) = &create_table.query {
                    self.visit_query_in_new_scope(query, None);
                }
            }
            Statement::Declare { stmts } if self.is_sqlserver => {
                for declaration in stmts {
                    if let Some(query) = &declaration.for_query {
                        self.visit_query_in_new_scope(query, None);
                    }
                }
            }
            _ => {}
        }
    }

    fn visit_query_in_new_scope(&mut self, query: &Query, parent_id: Option<usize>) {
        let scope_id = self.next_scope_id;
        self.next_scope_id += 1;
        self.scopes.push(SqlReferenceScope { id: scope_id, parent_id });
        self.scope_stack.push(scope_id);
        self.cte_scope_stack.push(HashSet::new());
        self.visit_query(query);
        self.cte_scope_stack.pop();
        self.scope_stack.pop();
    }

    fn visit_child_query(&mut self, query: &Query) {
        self.visit_query_in_new_scope(query, self.current_scope_id());
    }

    fn current_scope_id(&self) -> Option<usize> {
        self.scope_stack.last().copied()
    }

    fn add_visible_cte(&mut self, ident: &Ident) {
        let key = self.cte_name_key(ident);
        if let Some(visible_ctes) = self.cte_scope_stack.last_mut() {
            visible_ctes.insert(key);
        }
    }

    fn is_visible_cte(&self, name: &ObjectName) -> bool {
        if name.0.len() != 1 {
            return false;
        }
        let Some(ident) = name.0.first().and_then(ObjectNamePart::as_ident) else {
            return false;
        };
        let key = self.cte_name_key(ident);
        self.cte_scope_stack.iter().rev().any(|visible_ctes| visible_ctes.contains(&key))
    }

    fn cte_name_key(&self, ident: &Ident) -> String {
        if self.is_sqlserver || ident.quote_style.is_none() {
            ident.value.to_ascii_lowercase()
        } else {
            ident.value.clone()
        }
    }

    fn visit_query(&mut self, query: &Query) {
        if let Some(with) = &query.with {
            for cte in &with.cte_tables {
                // Add each name before its body: recursive/self and earlier CTEs are visible, later CTEs are not.
                self.add_visible_cte(&cte.alias.name);
                self.visit_child_query(&cte.query);
            }
        }
        self.visit_set_expr(&query.body);
        if let Some(order_by) = &query.order_by {
            if let OrderByKind::Expressions(exprs) = &order_by.kind {
                for expr in exprs {
                    self.visit_expr(&expr.expr);
                }
            }
        }
    }

    fn visit_set_expr(&mut self, set_expr: &SetExpr) {
        match set_expr {
            SetExpr::Select(select) => self.visit_select(select),
            SetExpr::Query(query) => self.visit_child_query(query),
            SetExpr::SetOperation { left, right, .. } => {
                self.visit_set_expr_in_child_scope(left);
                self.visit_set_expr_in_child_scope(right);
            }
            _ => {}
        }
    }

    fn visit_set_expr_in_child_scope(&mut self, set_expr: &SetExpr) {
        let scope_id = self.next_scope_id;
        self.next_scope_id += 1;
        self.scopes.push(SqlReferenceScope { id: scope_id, parent_id: self.current_scope_id() });
        self.scope_stack.push(scope_id);
        self.visit_set_expr(set_expr);
        self.scope_stack.pop();
    }

    fn visit_select(&mut self, select: &Select) {
        // Nested selects (subqueries, derived tables, CTE bodies) run their own
        // projection/group-by collection; save the outer context so their columns
        // and violations never mix with this statement's.
        let outer_projection_items = std::mem::take(&mut self.projection_items);
        let outer_projection_item = self.current_projection_item.take();
        let outer_group_by = self.group_by.take();

        for table in &select.from {
            self.visit_table_with_joins(table);
        }

        for item in &select.projection {
            match item {
                SelectItem::UnnamedExpr(expr) => self.visit_projection_item(expr, None),
                SelectItem::ExprWithAlias { expr, alias } => {
                    self.visit_projection_item(expr, Some(alias.value.clone()))
                }
                SelectItem::ExprWithAliases { expr, .. } => self.visit_projection_item(expr, None),
                _ => {}
            }
        }

        if let Some(expr) = &select.prewhere {
            self.visit_expr(expr);
        }
        if let Some(expr) = &select.selection {
            self.visit_expr(expr);
        }
        let mut group_by_context = GroupByContext::default();
        if let GroupByExpr::Expressions(exprs, _) = &select.group_by {
            for expr in exprs {
                match plain_identifier_parts(expr) {
                    Some((qualifier, name)) => {
                        if let Some(qualifier) = qualifier {
                            group_by_context.qualified.insert(format!(
                                "{}.{}",
                                qualifier.to_ascii_lowercase(),
                                name.to_ascii_lowercase()
                            ));
                        } else {
                            group_by_context.bare.insert(name.to_ascii_lowercase());
                        }
                    }
                    None => group_by_context.unresolved = true,
                }
                self.visit_expr(expr);
            }
        } else if matches!(select.group_by, GroupByExpr::All(_)) {
            // GROUP BY ALL / rollup-style grouping has no explicit membership to match.
            group_by_context.unresolved = true;
        }
        self.group_by = Some(group_by_context);

        for expr in &select.cluster_by {
            self.visit_expr(expr);
        }
        for expr in &select.distribute_by {
            self.visit_expr(expr);
        }
        for expr in &select.sort_by {
            self.visit_expr(&expr.expr);
        }
        if let Some(expr) = &select.having {
            self.visit_expr(expr);
        }
        if let Some(expr) = &select.qualify {
            self.visit_expr(expr);
        }

        self.finish_group_by_check();
        self.projection_items = outer_projection_items;
        self.current_projection_item = outer_projection_item;
        self.group_by = outer_group_by;
    }

    fn visit_projection_item(&mut self, expr: &Expr, alias: Option<String>) {
        self.current_projection_item = Some(ProjectionItemAcc { alias, ..ProjectionItemAcc::default() });
        self.visit_expr(expr);
        if let Some(item) = self.current_projection_item.take() {
            self.projection_items.push(item);
        }
    }

    /// Flag SELECT projection columns that are neither aggregated nor part of the
    /// statement's GROUP BY. Conservative by design: unresolved GROUP BY shapes
    /// skip the check entirely, alias-based coverage covers `GROUP BY <alias>`.
    fn finish_group_by_check(&mut self) {
        let Some(context) = self.group_by.as_ref() else {
            return;
        };
        if context.unresolved || (context.bare.is_empty() && context.qualified.is_empty()) {
            return;
        }
        // Columns of projection items whose alias is what GROUP BY references
        // (`SELECT a AS x ... GROUP BY x`) count as grouped for identical column
        // names elsewhere in the projection.
        let mut alias_grouped_columns: HashSet<String> = HashSet::new();
        for item in &self.projection_items {
            let Some(alias) = item.alias.as_ref() else { continue };
            if !context.bare.contains(&alias.to_ascii_lowercase()) {
                continue;
            }
            for column in &item.columns {
                alias_grouped_columns.insert(column.name.to_ascii_lowercase());
            }
        }
        // Qualifier asymmetry: `u.name` in the projection and `users.name` in
        // GROUP BY refer to the same table when one side is the other's alias,
        // so normalize every qualifier to the underlying table name before
        // comparing.
        let qualifier_table_names: HashMap<String, String> = self
            .tables
            .iter()
            .filter_map(|table| {
                table.alias.as_ref().map(|alias| (alias.to_ascii_lowercase(), table.name.to_ascii_lowercase()))
            })
            .collect();
        let normalize_qualifier = |qualifier: &str| -> String {
            qualifier_table_names
                .get(&qualifier.to_ascii_lowercase())
                .cloned()
                .unwrap_or_else(|| qualifier.to_ascii_lowercase())
        };
        let grouped_qualified: HashSet<String> = context
            .qualified
            .iter()
            .map(|entry| match entry.split_once('.') {
                Some((qualifier, name)) => format!("{}.{}", normalize_qualifier(qualifier), name),
                None => entry.clone(),
            })
            .collect();
        let mut violations: Vec<SqlGroupByViolation> = Vec::new();
        for item in &self.projection_items {
            let item_alias_grouped =
                item.alias.as_ref().map(|alias| context.bare.contains(&alias.to_ascii_lowercase())).unwrap_or(false);
            for column in &item.columns {
                if column.aggregated || item_alias_grouped {
                    continue;
                }
                let name = column.name.to_ascii_lowercase();
                if context.bare.contains(&name) {
                    continue;
                }
                if let Some(qualifier) = &column.qualifier {
                    if grouped_qualified.contains(&format!("{}.{}", normalize_qualifier(qualifier), name)) {
                        continue;
                    }
                }
                if alias_grouped_columns.contains(&name) {
                    continue;
                }
                violations.push(SqlGroupByViolation {
                    span: column.span,
                    column: column.name.clone(),
                    qualifier: column.qualifier.clone(),
                });
            }
        }
        self.group_by_violations.extend(violations);
    }

    fn visit_table_with_joins(&mut self, table: &TableWithJoins) {
        self.visit_table_factor(&table.relation);
        for join in &table.joins {
            self.visit_table_factor(&join.relation);
            self.visit_join_operator(&join.join_operator);
        }
    }

    fn visit_join_operator(&mut self, operator: &JoinOperator) {
        match operator {
            JoinOperator::Join(constraint)
            | JoinOperator::Inner(constraint)
            | JoinOperator::Left(constraint)
            | JoinOperator::LeftOuter(constraint)
            | JoinOperator::Right(constraint)
            | JoinOperator::RightOuter(constraint)
            | JoinOperator::FullOuter(constraint)
            | JoinOperator::CrossJoin(constraint)
            | JoinOperator::Semi(constraint)
            | JoinOperator::LeftSemi(constraint)
            | JoinOperator::RightSemi(constraint)
            | JoinOperator::Anti(constraint)
            | JoinOperator::LeftAnti(constraint)
            | JoinOperator::RightAnti(constraint)
            | JoinOperator::StraightJoin(constraint) => self.visit_join_constraint(constraint),
            JoinOperator::AsOf { match_condition, constraint } => {
                self.visit_expr(match_condition);
                self.visit_join_constraint(constraint);
            }
            _ => {}
        }
    }

    fn visit_join_constraint(&mut self, constraint: &JoinConstraint) {
        match constraint {
            JoinConstraint::On(expr) => self.visit_expr(expr),
            JoinConstraint::Using(names) => {
                for name in names {
                    if let Some(ident) = object_name_last_ident(name) {
                        self.push_column(None, ident);
                    }
                }
            }
            _ => {}
        }
    }

    fn visit_table_factor(&mut self, factor: &TableFactor) {
        match factor {
            TableFactor::Table { name, alias, args, .. } => {
                // Qualified names remain physical objects even when their final component matches a visible CTE.
                if args.is_none() && !self.is_visible_cte(name) {
                    if let Some(table) = table_reference_from_name(
                        name,
                        alias.as_ref().map(|a| a.name.value.clone()),
                        self.current_scope_id(),
                    ) {
                        self.tables.push(table);
                    }
                }
            }
            TableFactor::Derived { subquery, .. } => self.visit_child_query(subquery),
            TableFactor::NestedJoin { table_with_joins, .. } => self.visit_table_with_joins(table_with_joins),
            TableFactor::TableFunction { expr, .. } => self.visit_expr(expr),
            TableFactor::Function { args, .. } => {
                for arg in args {
                    self.visit_function_arg(arg);
                }
            }
            TableFactor::UNNEST { array_exprs, .. } => {
                for expr in array_exprs {
                    self.visit_expr(expr);
                }
            }
            _ => {}
        }
    }

    fn visit_expr(&mut self, expr: &Expr) {
        match expr {
            Expr::Identifier(ident) => self.push_column(None, ident),
            Expr::CompoundIdentifier(idents) => {
                if idents.len() >= 2 {
                    let column = idents.last().expect("checked length");
                    let qualifier = idents.get(idents.len() - 2).map(|ident| ident.value.clone());
                    self.push_column(qualifier, column);
                }
            }
            Expr::CompoundFieldAccess { root, .. } | Expr::JsonAccess { value: root, .. } => self.visit_expr(root),
            Expr::IsFalse(expr)
            | Expr::IsNotFalse(expr)
            | Expr::IsTrue(expr)
            | Expr::IsNotTrue(expr)
            | Expr::IsNull(expr)
            | Expr::IsNotNull(expr)
            | Expr::IsUnknown(expr)
            | Expr::IsNotUnknown(expr)
            | Expr::UnaryOp { expr, .. }
            | Expr::Nested(expr) => self.visit_expr(expr),
            Expr::IsDistinctFrom(left, right)
            | Expr::IsNotDistinctFrom(left, right)
            | Expr::BinaryOp { left, right, .. }
            | Expr::AnyOp { left, right, .. }
            | Expr::AllOp { left, right, .. } => {
                self.visit_expr(left);
                self.visit_expr(right);
            }
            Expr::InList { expr, list, .. } => {
                self.visit_expr(expr);
                for item in list {
                    self.visit_expr(item);
                }
            }
            Expr::InSubquery { expr, subquery, .. } => {
                self.visit_expr(expr);
                self.visit_child_query(subquery);
            }
            Expr::InUnnest { expr, array_expr, .. } => {
                self.visit_expr(expr);
                self.visit_expr(array_expr);
            }
            Expr::Between { expr, low, high, .. } => {
                self.visit_expr(expr);
                self.visit_expr(low);
                self.visit_expr(high);
            }
            Expr::Like { expr, pattern, .. }
            | Expr::ILike { expr, pattern, .. }
            | Expr::SimilarTo { expr, pattern, .. }
            | Expr::RLike { expr, pattern, .. } => {
                self.visit_expr(expr);
                self.visit_expr(pattern);
            }
            Expr::Cast { expr, .. }
            | Expr::Extract { expr, .. }
            | Expr::Ceil { expr, .. }
            | Expr::Floor { expr, .. } => self.visit_expr(expr),
            Expr::AtTimeZone { timestamp, time_zone } => {
                self.visit_expr(timestamp);
                self.visit_expr(time_zone);
            }
            Expr::Position { expr, r#in } => {
                self.visit_expr(expr);
                self.visit_expr(r#in);
            }
            Expr::Function(function) => {
                // Columns wrapped in an aggregate call or a window function are
                // exempt from the GROUP BY membership check.
                let function_name =
                    object_name_last_ident(&function.name).map(|ident| ident.value.to_ascii_lowercase());
                let is_aggregate = function_name.as_deref().map(is_aggregate_function_name).unwrap_or(false);
                let has_window = function.over.is_some();
                if is_aggregate {
                    self.aggregate_depth += 1;
                }
                if has_window {
                    self.window_depth += 1;
                }
                self.visit_function_args(&function.parameters);
                self.visit_function_call_args(&function.name, &function.args);
                if let Some(filter) = &function.filter {
                    self.visit_expr(filter);
                }
                for order in &function.within_group {
                    self.visit_expr(&order.expr);
                }
                if is_aggregate {
                    self.aggregate_depth -= 1;
                }
                if has_window {
                    self.window_depth -= 1;
                }
            }
            Expr::Case { operand, conditions, else_result, .. } => {
                if let Some(operand) = operand {
                    self.visit_expr(operand);
                }
                for condition in conditions {
                    self.visit_expr(&condition.condition);
                    self.visit_expr(&condition.result);
                }
                if let Some(else_result) = else_result {
                    self.visit_expr(else_result);
                }
            }
            Expr::Subquery(query) | Expr::Exists { subquery: query, .. } => self.visit_child_query(query),
            _ => {}
        }
    }

    fn visit_function_args(&mut self, args: &FunctionArguments) {
        self.visit_function_args_skipping(args, |_, _| false);
    }

    fn visit_function_call_args(&mut self, name: &ObjectName, args: &FunctionArguments) {
        let sqlserver_datepart_function = self.is_sqlserver.then(|| sqlserver_datepart_function_name(name)).flatten();
        self.visit_function_args_skipping(args, |index, arg| {
            index == 0
                && sqlserver_datepart_function
                    .is_some_and(|function_name| is_sqlserver_datepart_argument(function_name, arg))
        });
    }

    fn visit_function_args_skipping(
        &mut self,
        args: &FunctionArguments,
        mut should_skip: impl FnMut(usize, &FunctionArg) -> bool,
    ) {
        match args {
            FunctionArguments::Subquery(query) => self.visit_child_query(query),
            FunctionArguments::List(list) => {
                for (index, arg) in list.args.iter().enumerate() {
                    if !should_skip(index, arg) {
                        self.visit_function_arg(arg);
                    }
                }
                for clause in &list.clauses {
                    if let sqlparser::ast::FunctionArgumentClause::OrderBy(items) = clause {
                        for item in items {
                            self.visit_expr(&item.expr);
                        }
                    }
                }
            }
            FunctionArguments::None => {}
        }
    }

    fn visit_function_arg(&mut self, arg: &FunctionArg) {
        match arg {
            FunctionArg::Named { arg, .. } | FunctionArg::ExprNamed { arg, .. } | FunctionArg::Unnamed(arg) => {
                if let FunctionArgExpr::Expr(expr) = arg {
                    self.visit_expr(expr);
                }
            }
        }
    }

    fn push_column(&mut self, qualifier: Option<String>, ident: &Ident) {
        let span: SqlTextSpan = ident.span.into();
        if let Some(scope_id) = self.current_scope_id() {
            self.columns.push(SqlColumnReference {
                name: ident.value.clone(),
                qualifier: qualifier.clone(),
                span,
                scope_id,
            });
        }
        // Projection collection: the accumulator is Some exclusively while a
        // SELECT projection expression is being visited (nested selects swap it
        // in visit_select), so subquery columns never leak into outer items.
        if let Some(item) = self.current_projection_item.as_mut() {
            item.columns.push(ProjectionColumnRef {
                name: ident.value.clone(),
                qualifier,
                span,
                aggregated: self.aggregate_depth > 0 || self.window_depth > 0,
            });
        }
    }
}

/// Split a GROUP BY expression into its `(qualifier, name)` parts when it is a
/// plain (possibly qualified) identifier — the only shape the conservative
/// membership check can match against.
fn plain_identifier_parts(expr: &Expr) -> Option<(Option<String>, String)> {
    match expr {
        Expr::Identifier(ident) => Some((None, ident.value.clone())),
        Expr::CompoundIdentifier(idents) => {
            let column = idents.last()?;
            let qualifier =
                if idents.len() >= 2 { idents.get(idents.len() - 2).map(|ident| ident.value.clone()) } else { None };
            Some((qualifier, column.value.clone()))
        }
        _ => None,
    }
}

fn is_aggregate_function_name(name: &str) -> bool {
    const AGGREGATE_FUNCTION_NAMES: &[&str] = &[
        "sum",
        "count",
        "count_big",
        "avg",
        "min",
        "max",
        "total",
        "group_concat",
        "string_agg",
        "array_agg",
        "listagg",
        "bit_and",
        "bit_or",
        "bit_xor",
        "bool_and",
        "bool_or",
        "every",
        "median",
        "stddev",
        "stddev_pop",
        "stddev_samp",
        "stdev",
        "stdevp",
        "variance",
        "var_pop",
        "var_samp",
        "varp",
        "json_agg",
        "jsonb_agg",
        "json_group_array",
        "xmlagg",
        "wm_concat",
        "approx_count_distinct",
        "checksum_agg",
    ];
    AGGREGATE_FUNCTION_NAMES.contains(&name)
}

fn table_reference_from_name(
    name: &ObjectName,
    alias: Option<String>,
    scope_id: Option<usize>,
) -> Option<SqlTableReference> {
    let parts: Vec<&Ident> = name.0.iter().filter_map(ObjectNamePart::as_ident).collect();
    let table = parts.last()?;
    let schema = if parts.len() >= 2 { parts.get(parts.len() - 2).map(|ident| ident.value.clone()) } else { None };

    Some(SqlTableReference { name: table.value.clone(), schema, alias, span: table.span.into(), scope_id: scope_id? })
}

fn object_name_last_ident(name: &ObjectName) -> Option<&Ident> {
    name.0.iter().rev().find_map(ObjectNamePart::as_ident)
}

fn sqlserver_datepart_function_name(name: &ObjectName) -> Option<&str> {
    if name.0.len() != 1 {
        return None;
    }

    let ident = name.0.first()?.as_ident()?;
    ["DATEADD", "DATEDIFF", "DATEDIFF_BIG", "DATEPART", "DATENAME"]
        .iter()
        .copied()
        .find(|function_name| ident.value.eq_ignore_ascii_case(function_name))
}

fn is_sqlserver_datepart_argument(function_name: &str, arg: &FunctionArg) -> bool {
    let FunctionArg::Unnamed(FunctionArgExpr::Expr(Expr::Identifier(ident))) = arg else {
        return false;
    };
    if ident.quote_style.is_some() {
        return false;
    }

    // SQL Server parses datepart tokens as identifiers, but these built-ins treat the first token as grammar, not a column.
    let datepart = ident.value.as_str();
    let common_datepart = is_datepart(
        datepart,
        &[
            "year",
            "yy",
            "yyyy",
            "quarter",
            "qq",
            "q",
            "month",
            "mm",
            "m",
            "dayofyear",
            "dy",
            "y",
            "day",
            "dd",
            "d",
            "week",
            "wk",
            "ww",
            "hour",
            "hh",
            "minute",
            "mi",
            "n",
            "second",
            "ss",
            "s",
            "millisecond",
            "ms",
            "microsecond",
            "mcs",
            "nanosecond",
            "ns",
        ],
    );
    let weekday_datepart = is_datepart(datepart, &["weekday", "dw", "w"]);
    let extended_datepart = is_datepart(datepart, &["tzoffset", "tz", "iso_week", "isowk", "isoww"]);

    match function_name {
        "DATEADD" | "DATEDIFF" | "DATEDIFF_BIG" => common_datepart || weekday_datepart,
        "DATEPART" => common_datepart || weekday_datepart || extended_datepart,
        "DATENAME" => common_datepart || weekday_datepart || extended_datepart,
        _ => false,
    }
}

fn is_datepart(value: &str, valid_dateparts: &[&str]) -> bool {
    valid_dateparts.iter().any(|datepart| value.eq_ignore_ascii_case(datepart))
}

#[cfg(test)]
mod group_by_violation_tests {
    use super::*;

    fn violations(sql: &str, dialect: Option<&str>) -> Vec<SqlGroupByViolation> {
        analyze_sql_references(sql, dialect)
            .unwrap_or_else(|error| panic!("parse failed for {sql:?}: {error}"))
            .group_by_violations
    }

    #[test]
    fn flags_ungrouped_projection_column() {
        let result = violations("SELECT USER_ID, SUM(MONEY) FROM users GROUP BY USER_NAME", None);
        assert_eq!(result.len(), 1);
        assert_eq!(result[0].column, "USER_ID");
        assert_eq!(result[0].qualifier, None);
    }

    #[test]
    fn grouped_column_passes() {
        assert!(violations("SELECT USER_ID, SUM(MONEY) FROM users GROUP BY USER_ID", None).is_empty());
        assert!(violations("SELECT dept_name, COUNT(*) FROM emp GROUP BY dept_name", None).is_empty());
    }

    #[test]
    fn aggregate_wrapped_columns_pass() {
        assert!(violations("SELECT COUNT(a) FROM t GROUP BY b", None).is_empty());
        assert!(violations("SELECT a, SUM(b) FROM t GROUP BY a", None).is_empty());
    }

    #[test]
    fn alias_grouped_query_passes() {
        assert!(violations("SELECT a AS x FROM t GROUP BY x", None).is_empty());
        // `SELECT a, a AS b ... GROUP BY b` groups the same source column twice.
        assert!(violations("SELECT a, a AS b FROM t GROUP BY b", None).is_empty());
    }

    #[test]
    fn unresolved_group_by_shapes_skip_the_check() {
        assert!(violations("SELECT a FROM t GROUP BY 1", None).is_empty());
        assert!(violations("SELECT a + b FROM t GROUP BY a + b", None).is_empty());
    }

    #[test]
    fn computed_projection_flags_ungrouped_member() {
        let result = violations("SELECT a + b FROM t GROUP BY a", None);
        assert_eq!(result.len(), 1);
        assert_eq!(result[0].column, "b");
    }

    #[test]
    fn window_function_columns_pass() {
        assert!(violations("SELECT dept, SUM(sal) OVER (PARTITION BY dept) FROM emp GROUP BY dept", None).is_empty());
    }

    #[test]
    fn subquery_columns_do_not_leak() {
        assert!(violations("SELECT (SELECT max(v) FROM t2) AS m, uid FROM t1 GROUP BY uid", None).is_empty());
    }

    #[test]
    fn qualified_references_match_by_qualifier() {
        let result = violations("SELECT u.id, u.name FROM users u GROUP BY u.id", None);
        assert_eq!(result.len(), 1);
        assert_eq!(result[0].column, "name");
        assert_eq!(result[0].qualifier.as_deref(), Some("u"));
        assert!(violations("SELECT u.id, u.name FROM users u GROUP BY u.id, u.name", None).is_empty());
    }

    #[test]
    fn qualifier_asymmetry_resolves_alias_and_table_name() {
        // Projection qualifier (alias) vs GROUP BY qualifier (table name) refers
        // to the same table, so the grouped column itself must pass...
        assert!(violations("SELECT u.id FROM users u GROUP BY users.id", None).is_empty());
        // ...and the reverse direction.
        assert!(violations("SELECT users.id FROM users u GROUP BY u.id", None).is_empty());
        // Columns that are genuinely ungrouped stay flagged under either spelling.
        let result = violations("SELECT u.id, u.name FROM users u GROUP BY users.id", None);
        assert_eq!(result.len(), 1);
        assert_eq!(result[0].column, "name");
    }

    #[test]
    fn missing_group_by_passes() {
        assert!(violations("SELECT a, SUM(b) FROM t", None).is_empty());
    }

    #[test]
    fn lenient_engines_skip_the_check() {
        for dialect in ["mysql", "sqlite", "duckdb", "clickhouse", "spark"] {
            assert!(
                violations("SELECT USER_ID, SUM(MONEY) FROM users GROUP BY USER_NAME", Some(dialect)).is_empty(),
                "dialect {dialect}"
            );
        }
    }
}

#[cfg(test)]
mod sql_text_span_tests {
    use super::*;
    use sqlparser::tokenizer::Location;

    #[test]
    fn span_conversion_produces_inclusive_end_column() {
        // sqlparser's exclusive column 15 for 7-char token starting at 8 maps to inclusive 14.
        let span = Span { start: Location { line: 1, column: 8 }, end: Location { line: 1, column: 15 } };
        let text_span: SqlTextSpan = span.into();
        assert_eq!(text_span, SqlTextSpan { start_line: 1, start_column: 8, end_line: 1, end_column: 14 });

        // 1-character token at column 5: start 5, exclusive end 6 maps to inclusive 5.
        let single_char = Span { start: Location { line: 1, column: 5 }, end: Location { line: 1, column: 6 } };
        let single_span: SqlTextSpan = single_char.into();
        assert_eq!(single_span, SqlTextSpan { start_line: 1, start_column: 5, end_line: 1, end_column: 5 });

        // Multi-line span: end column 10 maps to inclusive 9 on line 2.
        let multiline = Span { start: Location { line: 1, column: 1 }, end: Location { line: 2, column: 10 } };
        let multiline_span: SqlTextSpan = multiline.into();
        assert_eq!(multiline_span, SqlTextSpan { start_line: 1, start_column: 1, end_line: 2, end_column: 9 });

        // Empty span stays zeroed.
        let empty_span: SqlTextSpan = Span::empty().into();
        assert_eq!(empty_span, SqlTextSpan { start_line: 0, start_column: 0, end_line: 0, end_column: 0 });
    }

    #[test]
    fn reference_analysis_emits_inclusive_spans_before_semicolon() {
        let sql = "SELECT id, name AS user_name FROM users GROUP BY user_name;";
        let analysis = analyze_sql_references(sql, Some("generic")).expect("query should analyze");

        // "users" starts at 1-based column 35, length 5 ("users" -> 35..=39).
        assert_eq!(analysis.tables[0].name, "users");
        assert_eq!(analysis.tables[0].span.start_line, 1);
        assert_eq!(analysis.tables[0].span.start_column, 35);
        assert_eq!(analysis.tables[0].span.end_line, 1);
        assert_eq!(analysis.tables[0].span.end_column, 39);

        // "id" at col 8..=9 (length 2).
        let id_col = analysis.columns.iter().find(|col| col.name == "id").unwrap();
        assert_eq!(id_col.span.start_column, 8);
        assert_eq!(id_col.span.end_column, 9);

        // "name" at col 12..=15 (length 4).
        let name_col = analysis.columns.iter().find(|col| col.name == "name").unwrap();
        assert_eq!(name_col.span.start_column, 12);
        assert_eq!(name_col.span.end_column, 15);

        // The GROUP BY column reference "user_name" directly before ';' at column 60:
        // "user_name" starts at column 50, length 9 (50..=58).
        // It must NOT overshoot to column 59 (which is ';').
        let group_col = analysis.columns.iter().rfind(|col| col.name == "user_name").unwrap();
        assert_eq!(group_col.span.start_column, 50);
        assert_eq!(group_col.span.end_column, 58);
    }

    #[test]
    fn group_by_violations_emit_inclusive_spans() {
        let sql = "SELECT USER_ID, SUM(MONEY) FROM users GROUP BY USER_NAME;";
        let analysis = analyze_sql_references(sql, Some("postgres")).expect("query should analyze");
        assert_eq!(analysis.group_by_violations.len(), 1);
        let violation = &analysis.group_by_violations[0];
        assert_eq!(violation.column, "USER_ID");
        // "USER_ID" starts at column 8, length 7 (8..=14).
        // Must NOT overshoot to column 15 (which is the comma ',').
        assert_eq!(violation.span.start_line, 1);
        assert_eq!(violation.span.start_column, 8);
        assert_eq!(violation.span.end_line, 1);
        assert_eq!(violation.span.end_column, 14);
    }
}