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。
TypeScript aicliclickhousedatabasedatabase-clientdatabase-managementdockerguimcpmongodbmysqlpostgresqlredisrustsql-serversqlitetaurivue
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: ®ex::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);
}
}