use std::collections::HashSet;
use crate::linter::rule::{LintContext, LintRule};
use crate::types::{issue_codes, Issue};
use sqlparser::ast::{
ConnectByKind, CreateView, Expr, FunctionArg, FunctionArgExpr, JoinOperator, NamedWindowExpr,
OrderByKind, Query, Select, SelectItem, SelectItemQualifiedWildcardKind, SetExpr, Statement,
TableFactor,
};
use super::semantic_helpers::{
collect_qualifier_prefixes_in_expr, count_reference_qualification_in_expr_excluding_aliases,
join_on_expr, select_projection_alias_set, table_factor_reference_name,
};
pub struct StructureUnusedJoin;
impl LintRule for StructureUnusedJoin {
fn code(&self) -> &'static str {
issue_codes::LINT_ST_011
}
fn name(&self) -> &'static str {
"Structure unused join"
}
fn description(&self) -> &'static str {
"Joined table not referenced in query."
}
fn check(&self, statement: &Statement, ctx: &LintContext) -> Vec<Issue> {
let violations = unused_join_count_for_statement(statement);
(0..violations)
.map(|_| {
Issue::warning(issue_codes::LINT_ST_011, "Joined source appears unused.")
.with_statement(ctx.statement_index)
})
.collect()
}
}
fn unused_join_count_for_statement(statement: &Statement) -> usize {
match statement {
Statement::Query(query) => unused_join_count_for_query(query),
Statement::Insert(insert) => insert
.source
.as_ref()
.map_or(0, |query| unused_join_count_for_query(query)),
Statement::CreateView(CreateView { query, .. }) => unused_join_count_for_query(query),
Statement::CreateTable(create) => create
.query
.as_ref()
.map_or(0, |query| unused_join_count_for_query(query)),
_ => 0,
}
}
fn unused_join_count_for_query(query: &Query) -> usize {
let mut total = 0usize;
if let Some(with) = &query.with {
for cte in &with.cte_tables {
total += unused_join_count_for_query(&cte.query);
}
}
let query_order_by_exprs = query_order_by_exprs(query);
total + unused_join_count_for_set_expr(&query.body, &query_order_by_exprs)
}
fn unused_join_count_for_set_expr(set_expr: &SetExpr, query_order_by_exprs: &[&Expr]) -> usize {
match set_expr {
SetExpr::Select(select) => {
let mut total = unused_join_count_for_select(select, query_order_by_exprs);
for table in &select.from {
total += unused_join_count_for_table_factor(&table.relation);
for join in &table.joins {
total += unused_join_count_for_table_factor(&join.relation);
}
}
visit_non_join_select_expressions(select, &mut |expr| {
total += unused_join_count_for_expr_subqueries(expr);
});
visit_distinct_on_expressions(select, &mut |expr| {
total += unused_join_count_for_expr_subqueries(expr);
});
visit_named_window_expressions(select, &mut |expr| {
total += unused_join_count_for_expr_subqueries(expr);
});
total
}
SetExpr::Query(query) => unused_join_count_for_query(query),
SetExpr::SetOperation { left, right, .. } => {
unused_join_count_for_set_expr(left, &[]) + unused_join_count_for_set_expr(right, &[])
}
SetExpr::Insert(statement)
| SetExpr::Update(statement)
| SetExpr::Delete(statement)
| SetExpr::Merge(statement) => unused_join_count_for_statement(statement),
_ => 0,
}
}
fn query_order_by_exprs(query: &Query) -> Vec<&Expr> {
let Some(order_by) = &query.order_by else {
return Vec::new();
};
match &order_by.kind {
OrderByKind::Expressions(order_exprs) => {
order_exprs.iter().map(|item| &item.expr).collect()
}
_ => Vec::new(),
}
}
fn unused_join_count_for_select(select: &Select, query_order_by_exprs: &[&Expr]) -> usize {
if select.from.is_empty() {
return 0;
}
let mut joined_sources = joined_sources(select);
if joined_sources.is_empty() {
return 0;
}
let referenced_in_join_clauses = referenced_tables_in_join_clauses(select);
joined_sources.retain(|source| !referenced_in_join_clauses.contains(source));
if joined_sources.is_empty() {
return 0;
}
if select_has_unqualified_wildcard(select) {
return 0;
}
let aliases = select_projection_alias_set(select);
let mut used_prefixes = HashSet::new();
let mut unqualified_references = 0usize;
visit_non_join_select_expressions(select, &mut |expr| {
collect_qualifier_prefixes_in_expr(expr, &mut used_prefixes);
let (_, unqualified) =
count_reference_qualification_in_expr_excluding_aliases(expr, &aliases);
unqualified_references += unqualified;
});
visit_distinct_on_expressions(select, &mut |expr| {
collect_qualifier_prefixes_in_expr(expr, &mut used_prefixes);
let (_, unqualified) =
count_reference_qualification_in_expr_excluding_aliases(expr, &aliases);
unqualified_references += unqualified;
});
visit_named_window_expressions(select, &mut |expr| {
collect_qualifier_prefixes_in_expr(expr, &mut used_prefixes);
let (_, unqualified) =
count_reference_qualification_in_expr_excluding_aliases(expr, &aliases);
unqualified_references += unqualified;
});
for expr in query_order_by_exprs {
collect_qualifier_prefixes_in_expr(expr, &mut used_prefixes);
let (_, unqualified) =
count_reference_qualification_in_expr_excluding_aliases(expr, &aliases);
unqualified_references += unqualified;
}
if unqualified_references > 0 {
return 0;
}
collect_projection_wildcard_prefixes(select, &mut used_prefixes);
collect_join_relation_reference_prefixes(select, &mut used_prefixes);
joined_sources
.iter()
.filter(|source| !used_prefixes.contains(*source))
.count()
}
fn unused_join_count_for_table_factor(table_factor: &TableFactor) -> usize {
match table_factor {
TableFactor::Derived { subquery, .. } => unused_join_count_for_query(subquery),
TableFactor::NestedJoin {
table_with_joins, ..
} => {
let mut total = unused_join_count_for_table_factor(&table_with_joins.relation);
for join in &table_with_joins.joins {
total += unused_join_count_for_table_factor(&join.relation);
if let Some(on_expr) = join_on_expr(&join.join_operator) {
total += unused_join_count_for_expr_subqueries(on_expr);
}
}
total
}
TableFactor::Pivot {
table,
aggregate_functions,
value_column,
default_on_null,
..
} => {
let mut total = unused_join_count_for_table_factor(table);
for expr_with_alias in aggregate_functions {
total += unused_join_count_for_expr_subqueries(&expr_with_alias.expr);
}
for expr in value_column {
total += unused_join_count_for_expr_subqueries(expr);
}
if let Some(expr) = default_on_null {
total += unused_join_count_for_expr_subqueries(expr);
}
total
}
TableFactor::Unpivot {
table,
value,
columns,
..
} => {
let mut total = unused_join_count_for_table_factor(table);
total += unused_join_count_for_expr_subqueries(value);
for expr_with_alias in columns {
total += unused_join_count_for_expr_subqueries(&expr_with_alias.expr);
}
total
}
TableFactor::MatchRecognize {
table,
partition_by,
order_by,
measures,
..
} => {
let mut total = unused_join_count_for_table_factor(table);
for expr in partition_by {
total += unused_join_count_for_expr_subqueries(expr);
}
for order in order_by {
total += unused_join_count_for_expr_subqueries(&order.expr);
}
for measure in measures {
total += unused_join_count_for_expr_subqueries(&measure.expr);
}
total
}
TableFactor::TableFunction { expr, .. } => unused_join_count_for_expr_subqueries(expr),
TableFactor::Function { args, .. } => args
.iter()
.map(unused_join_count_for_function_arg)
.sum::<usize>(),
TableFactor::UNNEST { array_exprs, .. } => array_exprs
.iter()
.map(unused_join_count_for_expr_subqueries)
.sum::<usize>(),
TableFactor::JsonTable { json_expr, .. } | TableFactor::OpenJsonTable { json_expr, .. } => {
unused_join_count_for_expr_subqueries(json_expr)
}
TableFactor::XmlTable { row_expression, .. } => {
unused_join_count_for_expr_subqueries(row_expression)
}
TableFactor::Table { .. } | TableFactor::SemanticView { .. } => 0,
}
}
fn unused_join_count_for_function_arg(arg: &FunctionArg) -> usize {
match arg {
FunctionArg::Unnamed(FunctionArgExpr::Expr(expr))
| FunctionArg::Named {
arg: FunctionArgExpr::Expr(expr),
..
} => unused_join_count_for_expr_subqueries(expr),
_ => 0,
}
}
fn unused_join_count_for_expr_subqueries(expr: &Expr) -> usize {
match expr {
Expr::Subquery(query)
| Expr::Exists {
subquery: query, ..
} => unused_join_count_for_query(query),
Expr::InSubquery {
expr: inner,
subquery,
..
} => unused_join_count_for_expr_subqueries(inner) + unused_join_count_for_query(subquery),
Expr::BinaryOp { left, right, .. }
| Expr::AnyOp { left, right, .. }
| Expr::AllOp { left, right, .. } => {
unused_join_count_for_expr_subqueries(left)
+ unused_join_count_for_expr_subqueries(right)
}
Expr::UnaryOp { expr: inner, .. }
| Expr::Nested(inner)
| Expr::IsNull(inner)
| Expr::IsNotNull(inner)
| Expr::Cast { expr: inner, .. } => unused_join_count_for_expr_subqueries(inner),
Expr::InList { expr, list, .. } => {
unused_join_count_for_expr_subqueries(expr)
+ list
.iter()
.map(unused_join_count_for_expr_subqueries)
.sum::<usize>()
}
Expr::Between {
expr, low, high, ..
} => {
unused_join_count_for_expr_subqueries(expr)
+ unused_join_count_for_expr_subqueries(low)
+ unused_join_count_for_expr_subqueries(high)
}
Expr::Case {
operand,
conditions,
else_result,
..
} => {
let operand_count = operand
.as_ref()
.map_or(0, |expr| unused_join_count_for_expr_subqueries(expr));
let condition_count = conditions
.iter()
.map(|when| {
unused_join_count_for_expr_subqueries(&when.condition)
+ unused_join_count_for_expr_subqueries(&when.result)
})
.sum::<usize>();
let else_count = else_result
.as_ref()
.map_or(0, |expr| unused_join_count_for_expr_subqueries(expr));
operand_count + condition_count + else_count
}
Expr::Function(function) => {
let args_count =
if let sqlparser::ast::FunctionArguments::List(arguments) = &function.args {
arguments
.args
.iter()
.map(unused_join_count_for_function_arg)
.sum::<usize>()
} else {
0
};
let filter_count = function
.filter
.as_ref()
.map_or(0, |expr| unused_join_count_for_expr_subqueries(expr));
let within_group_count = function
.within_group
.iter()
.map(|order| unused_join_count_for_expr_subqueries(&order.expr))
.sum::<usize>();
let window_count = match &function.over {
Some(sqlparser::ast::WindowType::WindowSpec(spec)) => {
spec.partition_by
.iter()
.map(unused_join_count_for_expr_subqueries)
.sum::<usize>()
+ spec
.order_by
.iter()
.map(|order| unused_join_count_for_expr_subqueries(&order.expr))
.sum::<usize>()
}
_ => 0,
};
args_count + filter_count + within_group_count + window_count
}
_ => 0,
}
}
fn select_has_unqualified_wildcard(select: &Select) -> bool {
select
.projection
.iter()
.any(|item| matches!(item, SelectItem::Wildcard(_)))
}
fn joined_sources(select: &Select) -> HashSet<String> {
let mut joined_sources = HashSet::new();
for table in &select.from {
for join in &table.joins {
if !is_tracked_join(&join.join_operator) {
continue;
}
if let Some(name) = table_factor_reference_name(&join.relation) {
joined_sources.insert(name);
}
}
}
joined_sources
}
fn referenced_tables_in_join_clauses(select: &Select) -> HashSet<String> {
let mut referenced = HashSet::new();
for table in &select.from {
for join in &table.joins {
let self_ref = table_factor_reference_name(&join.relation);
if let Some(on_expr) = join_on_expr(&join.join_operator) {
let mut refs = HashSet::new();
collect_qualifier_prefixes_in_expr(on_expr, &mut refs);
for table_ref in refs {
if self_ref.as_deref() != Some(table_ref.as_str()) {
referenced.insert(table_ref);
}
}
}
}
}
referenced
}
fn is_tracked_join(operator: &JoinOperator) -> bool {
matches!(
operator,
JoinOperator::Left(_)
| JoinOperator::LeftOuter(_)
| JoinOperator::Right(_)
| JoinOperator::RightOuter(_)
| JoinOperator::FullOuter(_)
)
}
fn visit_non_join_select_expressions<F: FnMut(&sqlparser::ast::Expr)>(
select: &Select,
visitor: &mut F,
) {
for item in &select.projection {
if let sqlparser::ast::SelectItem::UnnamedExpr(expr)
| sqlparser::ast::SelectItem::ExprWithAlias { expr, .. } = item
{
visitor(expr);
}
}
if let Some(prewhere) = &select.prewhere {
visitor(prewhere);
}
if let Some(selection) = &select.selection {
visitor(selection);
}
if let sqlparser::ast::GroupByExpr::Expressions(exprs, _) = &select.group_by {
for expr in exprs {
visitor(expr);
}
}
for expr in &select.cluster_by {
visitor(expr);
}
for expr in &select.distribute_by {
visitor(expr);
}
for lateral_view in &select.lateral_views {
visitor(&lateral_view.lateral_view);
}
if let Some(having) = &select.having {
visitor(having);
}
if let Some(qualify) = &select.qualify {
visitor(qualify);
}
for connect_by_kind in &select.connect_by {
match connect_by_kind {
ConnectByKind::ConnectBy { relationships, .. } => {
for relationship in relationships {
visitor(relationship);
}
}
ConnectByKind::StartWith { condition, .. } => {
visitor(condition);
}
}
}
for sort in &select.sort_by {
visitor(&sort.expr);
}
}
fn visit_distinct_on_expressions<F: FnMut(&sqlparser::ast::Expr)>(
select: &Select,
visitor: &mut F,
) {
if let Some(sqlparser::ast::Distinct::On(expressions)) = &select.distinct {
for expr in expressions {
visitor(expr);
}
}
}
fn visit_named_window_expressions<F: FnMut(&sqlparser::ast::Expr)>(
select: &Select,
visitor: &mut F,
) {
for named_window in &select.named_window {
if let NamedWindowExpr::WindowSpec(spec) = &named_window.1 {
for expr in &spec.partition_by {
visitor(expr);
}
for order in &spec.order_by {
visitor(&order.expr);
}
}
}
}
fn collect_projection_wildcard_prefixes(select: &Select, prefixes: &mut HashSet<String>) {
for item in &select.projection {
if let SelectItem::QualifiedWildcard(kind, _) = item {
match kind {
SelectItemQualifiedWildcardKind::ObjectName(name) => {
if let Some(last) = name.0.last().and_then(|part| part.as_ident()) {
prefixes.insert(last.value.to_ascii_uppercase());
}
}
SelectItemQualifiedWildcardKind::Expr(expr) => match expr {
Expr::Identifier(identifier) => {
prefixes.insert(identifier.value.to_ascii_uppercase());
}
Expr::CompoundIdentifier(parts) if parts.len() > 1 => {
if let Some(first) = parts.first() {
prefixes.insert(first.value.to_ascii_uppercase());
}
}
_ => collect_qualifier_prefixes_in_expr(expr, prefixes),
},
}
}
}
}
fn collect_join_relation_reference_prefixes(select: &Select, prefixes: &mut HashSet<String>) {
for table in &select.from {
for join in &table.joins {
collect_table_factor_reference_prefixes(&join.relation, prefixes);
}
}
}
fn collect_table_factor_reference_prefixes(
table_factor: &TableFactor,
prefixes: &mut HashSet<String>,
) {
match table_factor {
TableFactor::Table { .. } => {}
TableFactor::Derived { .. } => {}
TableFactor::TableFunction { expr, .. } => {
collect_qualifier_prefixes_in_expr(expr, prefixes);
}
TableFactor::Function { args, .. } => {
for arg in args {
collect_function_arg_prefixes(arg, prefixes);
}
}
TableFactor::UNNEST { array_exprs, .. } => {
for expr in array_exprs {
collect_qualifier_prefixes_in_expr(expr, prefixes);
}
}
TableFactor::JsonTable { json_expr, .. } | TableFactor::OpenJsonTable { json_expr, .. } => {
collect_qualifier_prefixes_in_expr(json_expr, prefixes);
}
TableFactor::NestedJoin {
table_with_joins, ..
} => {
collect_table_factor_reference_prefixes(&table_with_joins.relation, prefixes);
for join in &table_with_joins.joins {
collect_table_factor_reference_prefixes(&join.relation, prefixes);
}
}
TableFactor::Pivot {
table,
aggregate_functions,
value_column,
default_on_null,
..
} => {
collect_table_factor_reference_prefixes(table, prefixes);
for expr_with_alias in aggregate_functions {
collect_qualifier_prefixes_in_expr(&expr_with_alias.expr, prefixes);
}
for expr in value_column {
collect_qualifier_prefixes_in_expr(expr, prefixes);
}
if let Some(expr) = default_on_null {
collect_qualifier_prefixes_in_expr(expr, prefixes);
}
}
TableFactor::Unpivot {
table,
value,
columns,
..
} => {
collect_table_factor_reference_prefixes(table, prefixes);
collect_qualifier_prefixes_in_expr(value, prefixes);
for expr_with_alias in columns {
collect_qualifier_prefixes_in_expr(&expr_with_alias.expr, prefixes);
}
}
TableFactor::MatchRecognize {
table,
partition_by,
order_by,
measures,
..
} => {
collect_table_factor_reference_prefixes(table, prefixes);
for expr in partition_by {
collect_qualifier_prefixes_in_expr(expr, prefixes);
}
for order in order_by {
collect_qualifier_prefixes_in_expr(&order.expr, prefixes);
}
for measure in measures {
collect_qualifier_prefixes_in_expr(&measure.expr, prefixes);
}
}
TableFactor::XmlTable { row_expression, .. } => {
collect_qualifier_prefixes_in_expr(row_expression, prefixes);
}
TableFactor::SemanticView { .. } => {}
}
}
fn collect_function_arg_prefixes(arg: &FunctionArg, prefixes: &mut HashSet<String>) {
match arg {
FunctionArg::Unnamed(FunctionArgExpr::Expr(expr))
| FunctionArg::Named {
arg: FunctionArgExpr::Expr(expr),
..
} => collect_qualifier_prefixes_in_expr(expr, prefixes),
_ => {}
}
}
#[cfg(test)]
mod tests {
use super::*;
use crate::{parser::parse_sql_with_dialect, types::Dialect};
fn run(sql: &str) -> Vec<Issue> {
run_in_dialect(sql, Dialect::Generic)
}
fn run_in_dialect(sql: &str, dialect: Dialect) -> Vec<Issue> {
let statements = parse_sql_with_dialect(sql, dialect).expect("parse");
let rule = StructureUnusedJoin;
statements
.iter()
.enumerate()
.flat_map(|(index, statement)| {
rule.check(
statement,
&LintContext {
sql,
statement_range: 0..sql.len(),
statement_index: index,
},
)
})
.collect()
}
#[test]
fn flags_unused_outer_joined_source() {
let issues = run("select 1 from b left join c on b.x = c.x");
assert_eq!(issues.len(), 1);
assert_eq!(issues[0].code, issue_codes::LINT_ST_011);
}
#[test]
fn allows_single_table_statement() {
let issues = run("select 1 from foo");
assert!(issues.is_empty());
}
#[test]
fn allows_single_values_table_statement() {
let issues = run("select 1 from (values (1, 'one'), (2, 'two'))");
assert!(issues.is_empty());
}
#[test]
fn allows_inner_join_when_joined_source_unreferenced() {
let issues = run("select a.* from a inner join b using(x)");
assert!(issues.is_empty());
}
#[test]
fn allows_implicit_inner_join_when_joined_source_unreferenced() {
let issues = run("select a.* from a join b using(x)");
assert!(issues.is_empty());
}
#[test]
fn flags_unreferenced_right_joined_source() {
let issues = run("select a.* from a right join d using(x)");
assert_eq!(issues.len(), 1);
assert_eq!(issues[0].code, issue_codes::LINT_ST_011);
}
#[test]
fn defers_when_unqualified_references_exist() {
let issues = run("select a from b left join c using(d)");
assert!(issues.is_empty());
}
#[test]
fn allows_outer_join_when_joined_source_is_referenced() {
let issues = run("select widget.id, inventor.id from widget left join inventor on widget.inventor_id = inventor.id");
assert!(issues.is_empty());
}
#[test]
fn allows_outer_join_when_joined_source_only_referenced_in_query_order_by() {
let issues = run("select a.id from a left join b on a.id = b.id order by b.id");
assert!(issues.is_empty());
}
#[test]
fn does_not_flag_base_from_source_as_unused_with_using_join() {
let issues = run("select c.id from b left join c using(id)");
assert!(issues.is_empty());
}
#[test]
fn allows_unqualified_wildcard_projection() {
let issues = run("select * from a left join b on a.id = b.id");
assert!(issues.is_empty());
}
#[test]
fn detects_unused_join_in_subquery_scope() {
let issues = run(
"SELECT a.col1 FROM a LEFT JOIN b ON a.id = b.a_id WHERE a.some_column IN (SELECT c.some_column FROM c WHERE c.other_column = a.col)",
);
assert_eq!(issues.len(), 1);
}
#[test]
fn allows_join_reference_inside_subquery() {
let issues = run(
"SELECT a.col1 FROM a LEFT JOIN b ON a.id = b.a_id WHERE a.some_column IN (SELECT c.some_column FROM c WHERE c.other_column = b.col)",
);
assert!(issues.is_empty());
}
#[test]
fn flags_unused_outer_join_inside_derived_subquery() {
let issues =
run("SELECT * FROM (SELECT t1.col1 FROM db1.t1 LEFT JOIN t2 ON t1.id = t2.id)");
assert_eq!(issues.len(), 1);
assert_eq!(issues[0].code, issue_codes::LINT_ST_011);
}
#[test]
fn allows_outer_join_table_referenced_by_another_join_condition() {
let issues =
run("SELECT a.id FROM a LEFT JOIN b ON a.id = b.a_id LEFT JOIN c ON b.c_id = c.id");
assert_eq!(issues.len(), 1);
assert_eq!(issues[0].code, issue_codes::LINT_ST_011);
}
#[test]
fn flags_unused_outer_join_in_multi_root_from_clause() {
let issues = run("SELECT a.id FROM a, b LEFT JOIN c ON b.id = c.id");
assert_eq!(issues.len(), 1);
assert_eq!(issues[0].code, issue_codes::LINT_ST_011);
}
#[test]
fn allows_used_outer_join_in_multi_root_from_clause() {
let issues = run("SELECT c.id FROM a, b LEFT JOIN c ON b.id = c.id");
assert!(issues.is_empty());
}
#[test]
fn allows_outer_join_source_referenced_by_later_unnest_join_relation() {
let issues = run(
"SELECT ft.id, n.generic_field FROM fact_table AS ft LEFT JOIN UNNEST(ft.generic_array) AS g LEFT JOIN UNNEST(g.nested_array) AS n",
);
assert!(issues.is_empty());
}
#[test]
fn allows_outer_join_source_referenced_in_named_window_clause() {
let issues = run(
"SELECT sum(a.value) OVER w FROM a LEFT JOIN b ON a.id = b.id WINDOW w AS (PARTITION BY b.group_key)",
);
assert!(issues.is_empty());
}
#[test]
fn defers_when_named_window_clause_has_unqualified_reference() {
let issues = run(
"SELECT sum(a.value) OVER w FROM a LEFT JOIN b ON a.id = b.id WINDOW w AS (PARTITION BY group_key)",
);
assert!(issues.is_empty());
}
#[test]
fn allows_outer_join_source_referenced_in_distinct_on_clause() {
let issues = run("SELECT DISTINCT ON (b.id) a.id FROM a LEFT JOIN b ON a.id = b.id");
assert!(issues.is_empty());
}
#[test]
fn defers_when_distinct_on_clause_has_unqualified_reference() {
let issues = run("SELECT DISTINCT ON (id) a.id FROM a LEFT JOIN b ON a.id = b.id");
assert!(issues.is_empty());
}
#[test]
fn allows_outer_join_source_referenced_in_cluster_by_clause() {
let issues = run("SELECT a.id FROM a LEFT JOIN b ON a.id = b.id CLUSTER BY b.id");
assert!(issues.is_empty());
}
#[test]
fn allows_outer_join_source_referenced_in_distribute_by_clause() {
let issues = run("SELECT a.id FROM a LEFT JOIN b ON a.id = b.id DISTRIBUTE BY b.id");
assert!(issues.is_empty());
}
#[test]
fn allows_outer_join_source_referenced_with_mysql_backtick_quoted_table_name() {
let issues = run_in_dialect(
"SELECT `test`.one, `test-2`.two FROM `test` LEFT JOIN `test-2` ON `test`.id = `test-2`.id",
Dialect::Mysql,
);
assert!(issues.is_empty());
}
#[test]
fn allows_outer_join_source_referenced_with_mssql_bracket_quoted_table_name() {
let issues = run_in_dialect(
"SELECT [test].one, [test-2].two FROM [test] LEFT JOIN [test-2] ON [test].id = [test-2].id",
Dialect::Mssql,
);
assert!(issues.is_empty());
}
#[test]
fn allows_snowflake_qualified_wildcard_exclude_for_joined_source() {
let issues = run_in_dialect(
"select \
simulation_source_data_reference.*, \
sourcings.* exclude sourcing_job_id \
from simulation_source_data_reference \
left join sourcings \
on simulation_source_data_reference.sourcing_job_id = sourcings.sourcing_job_id",
Dialect::Snowflake,
);
assert!(issues.is_empty());
}
#[test]
fn allows_outer_join_source_referenced_in_hive_lateral_view_clause() {
let issues = run_in_dialect(
"SELECT a.id FROM a LEFT JOIN b ON a.id = b.id LATERAL VIEW explode(b.items) lv AS item",
Dialect::Hive,
);
assert!(issues.is_empty());
}
#[test]
fn defers_when_hive_lateral_view_clause_has_unqualified_reference() {
let issues = run_in_dialect(
"SELECT a.id FROM a LEFT JOIN b ON a.id = b.id LATERAL VIEW explode(items) lv AS item",
Dialect::Hive,
);
assert!(issues.is_empty());
}
#[test]
fn allows_outer_join_source_referenced_in_connect_by_clause() {
let issues = run_in_dialect(
"SELECT a.id FROM a LEFT JOIN b ON a.id = b.id START WITH b.id IS NOT NULL CONNECT BY PRIOR a.id = b.id",
Dialect::Snowflake,
);
assert!(issues.is_empty());
}
#[test]
fn defers_when_connect_by_clause_has_unqualified_reference() {
let issues = run_in_dialect(
"SELECT a.id FROM a LEFT JOIN b ON a.id = b.id START WITH id IS NOT NULL CONNECT BY PRIOR a.id = b.id",
Dialect::Snowflake,
);
assert!(issues.is_empty());
}
}