sqruff_lib/rules/structure/
st09.rs1use hashbrown::HashMap;
2use itertools::Itertools;
3use smol_str::{SmolStr, StrExt};
4use sqruff_lib_core::dialects::syntax::{SyntaxKind, SyntaxSet};
5use sqruff_lib_core::lint_fix::LintFix;
6use sqruff_lib_core::parser::segments::from::FromExpressionElementSegment;
7use sqruff_lib_core::parser::segments::join::JoinClauseSegment;
8use sqruff_lib_core::parser::segments::{ErasedSegment, SegmentBuilder};
9use sqruff_lib_core::utils::functional::segments::Segments;
10
11use crate::core::config::Value;
12use crate::core::rules::context::RuleContext;
13use crate::core::rules::crawlers::{Crawler, SegmentSeekerCrawler};
14use crate::core::rules::{Erased, ErasedRule, LintResult, Rule, RuleGroups};
15use crate::utils::functional::context::FunctionalContext;
16
17const REORDERABLE_OPERATORS: &[&str] = &["=", "!=", "<>", "<=>", "<", ">", "<=", ">="];
18
19fn normalize_identifier(raw: &str) -> SmolStr {
20 let is_bracket_quoted = raw.starts_with('[') && raw.ends_with(']') && raw.len() >= 2;
21 let is_matching_quote_quoted = matches!(raw.chars().next(), Some('"') | Some('\'') | Some('`'))
22 && raw.len() >= 2
23 && raw.chars().next() == raw.chars().last();
24
25 if is_bracket_quoted || is_matching_quote_quoted {
26 raw[1..raw.len() - 1].into()
27 } else {
28 raw.into()
29 }
30}
31
32#[derive(Default, Debug, Clone)]
33pub struct RuleST09 {
34 preferred_first_table_in_join_clause: String,
35}
36
37impl Rule for RuleST09 {
38 fn load_from_config(&self, config: &HashMap<String, Value>) -> Result<ErasedRule, String> {
39 match config["preferred_first_table_in_join_clause"].as_string() {
40 Some("earlier" | "later") => Ok(RuleST09 {
41 preferred_first_table_in_join_clause:
42 config["preferred_first_table_in_join_clause"]
43 .as_string()
44 .unwrap()
45 .to_owned(),
46 }
47 .erased()),
48 Some(value) => Err(format!(
49 "Invalid value for preferred_first_table_in_join_clause: {value}. Must be one of \
50 [earlier, later]"
51 )),
52 None => {
53 Err("Rule ST09 expects a string for `preferred_first_table_in_join_clause`".into())
54 }
55 }
56 }
57
58 fn name(&self) -> &'static str {
59 "structure.join_condition_order"
60 }
61
62 fn description(&self) -> &'static str {
63 "Joins should list the table referenced earlier/later first."
64 }
65
66 fn long_description(&self) -> &'static str {
67 r#"
68**Anti-pattern**
69
70In this example, the tables that were referenced later are listed first
71and the `preferred_first_table_in_join_clause` configuration
72is set to `earlier`.
73
74```sql
75select
76 foo.a,
77 foo.b,
78 bar.c
79from foo
80left join bar
81 -- This subcondition does not list
82 -- the table referenced earlier first:
83 on bar.a = foo.a
84 -- Neither does this subcondition:
85 and bar.b = foo.b
86```
87
88**Best practice**
89
90List the tables that were referenced earlier first.
91
92```sql
93select
94 foo.a,
95 foo.b,
96 bar.c
97from foo
98left join bar
99 on foo.a = bar.a
100 and foo.b = bar.b
101```
102"#
103 }
104
105 fn groups(&self) -> &'static [RuleGroups] {
106 &[RuleGroups::All, RuleGroups::Structure]
107 }
108
109 fn is_fix_compatible(&self) -> bool {
110 true
111 }
112
113 fn eval(&self, context: &RuleContext) -> Vec<LintResult> {
114 let mut table_aliases = Vec::new();
115 let children = FunctionalContext::new(context).segment().children_all();
116 let join_clauses =
117 children.recursive_crawl(const { &SyntaxSet::new(&[SyntaxKind::JoinClause]) }, true);
118 let join_on_conditions = join_clauses.children_all().recursive_crawl(
119 const { &SyntaxSet::new(&[SyntaxKind::JoinOnCondition]) },
120 true,
121 );
122
123 if join_on_conditions.is_empty() {
124 return Vec::new();
125 }
126
127 let from_expression_alias_info = FromExpressionElementSegment(
128 children.recursive_crawl(
129 const { &SyntaxSet::new(&[SyntaxKind::FromExpressionElement]) },
130 true,
131 )[0]
132 .clone(),
133 )
134 .eventual_alias();
135 let from_expression_alias = from_expression_alias_info
136 .segment
137 .as_ref()
138 .map(|segment| normalize_identifier(segment.raw()))
139 .unwrap_or_else(|| normalize_identifier(from_expression_alias_info.ref_str.as_str()));
140
141 table_aliases.push(from_expression_alias);
142
143 let mut join_clause_aliases = join_clauses
144 .into_iter()
145 .map(|join_clause| {
146 JoinClauseSegment(join_clause)
147 .eventual_aliases()
148 .first()
149 .unwrap()
150 .1
151 .clone()
152 })
153 .map(|alias_info| {
154 alias_info
155 .segment
156 .as_ref()
157 .map(|segment| normalize_identifier(segment.raw()))
158 .unwrap_or_else(|| normalize_identifier(alias_info.ref_str.as_str()))
159 })
160 .collect_vec();
161
162 table_aliases.append(&mut join_clause_aliases);
163
164 let table_aliases = table_aliases
165 .iter()
166 .map(|it| it.to_uppercase_smolstr())
167 .collect_vec();
168 let mut conditions = Vec::new();
169
170 let join_on_condition_expressions = join_on_conditions
171 .children_all()
172 .recursive_crawl(const { &SyntaxSet::new(&[SyntaxKind::Expression]) }, true);
173
174 for expression in join_on_condition_expressions {
175 let mut expression_group = Vec::new();
176 for element in Segments::new(expression, None).children_all() {
177 if !matches!(
178 element.get_type(),
179 SyntaxKind::Whitespace | SyntaxKind::Newline
180 ) {
181 expression_group.push(element);
182 }
183 }
184 conditions.push(expression_group);
185 }
186
187 let mut subconditions = Vec::new();
188
189 for expression_group in conditions {
190 subconditions.append(&mut split_list_by_segment_type(
191 expression_group,
192 SyntaxKind::BinaryOperator,
193 vec!["and".into(), "or".into()],
194 ));
195 }
196
197 let column_operator_column_subconditions = subconditions
198 .into_iter()
199 .filter(|it| is_qualified_column_operator_qualified_column_sequence(it))
200 .collect_vec();
201
202 let mut fixes = Vec::new();
203
204 for subcondition in column_operator_column_subconditions {
205 let comparison_operator = subcondition[1].clone();
206 let first_column_reference = subcondition[0].clone();
207 let second_column_reference = subcondition[2].clone();
208 let raw_comparison_operators: Vec<_> = comparison_operator
209 .children(const { &SyntaxSet::new(&[SyntaxKind::RawComparisonOperator]) })
210 .collect();
211 let operator_str = if raw_comparison_operators.is_empty() {
212 comparison_operator.raw().trim().to_owned()
213 } else {
214 raw_comparison_operators.iter().map(|it| it.raw()).join("")
215 };
216
217 if !REORDERABLE_OPERATORS.contains(&operator_str.as_str()) {
218 continue;
219 }
220
221 let first_table_seg = first_column_reference
222 .child(
223 const {
224 &SyntaxSet::new(&[
225 SyntaxKind::NakedIdentifier,
226 SyntaxKind::QuotedIdentifier,
227 ])
228 },
229 )
230 .unwrap();
231 let second_table_seg = second_column_reference
232 .child(
233 const {
234 &SyntaxSet::new(&[
235 SyntaxKind::NakedIdentifier,
236 SyntaxKind::QuotedIdentifier,
237 ])
238 },
239 )
240 .unwrap();
241
242 let first_table = normalize_identifier(first_table_seg.raw()).to_uppercase_smolstr();
243 let second_table = normalize_identifier(second_table_seg.raw()).to_uppercase_smolstr();
244
245 let raw_comparison_operator_opposites = |op| match op {
246 "<" => ">",
247 ">" => "<",
248 _ => unimplemented!(),
249 };
250
251 if !table_aliases.contains(&first_table) || !table_aliases.contains(&second_table) {
252 continue;
253 }
254
255 if (table_aliases
256 .iter()
257 .position(|x| x == &first_table)
258 .unwrap()
259 > table_aliases
260 .iter()
261 .position(|x| x == &second_table)
262 .unwrap()
263 && self.preferred_first_table_in_join_clause == "earlier")
264 || (table_aliases
265 .iter()
266 .position(|x| x == &first_table)
267 .unwrap()
268 < table_aliases
269 .iter()
270 .position(|x| x == &second_table)
271 .unwrap()
272 && self.preferred_first_table_in_join_clause == "later")
273 {
274 fixes.push(LintFix::replace(
275 first_column_reference.clone(),
276 vec![second_column_reference.clone()],
277 None,
278 ));
279 fixes.push(LintFix::replace(
280 second_column_reference.clone(),
281 vec![first_column_reference.clone()],
282 None,
283 ));
284
285 if raw_comparison_operators
286 .first()
287 .is_some_and(|op| matches!(op.raw().as_ref(), "<" | ">"))
288 && raw_comparison_operators
289 .iter()
290 .map(|it| it.raw())
291 .ne(["<", ">"])
292 {
293 fixes.push(LintFix::replace(
294 raw_comparison_operators[0].clone(),
295 vec![
296 SegmentBuilder::token(
297 context.tables.next_id(),
298 raw_comparison_operator_opposites(
299 raw_comparison_operators[0].raw().as_ref(),
300 ),
301 SyntaxKind::RawComparisonOperator,
302 )
303 .finish(),
304 ],
305 None,
306 ));
307 }
308 }
309 }
310
311 if fixes.is_empty() {
312 return Vec::new();
313 }
314
315 vec![LintResult::new(
316 context.segment.clone().into(),
317 fixes,
318 format!(
319 "Joins should list the table referenced {} first.",
320 self.preferred_first_table_in_join_clause
321 )
322 .into(),
323 None,
324 )]
325 }
326
327 fn crawl_behaviour(&self) -> Crawler {
328 SegmentSeekerCrawler::new(const { SyntaxSet::new(&[SyntaxKind::FromExpression]) }).into()
329 }
330}
331
332fn split_list_by_segment_type(
333 segment_list: Vec<ErasedSegment>,
334 delimiter_type: SyntaxKind,
335 delimiters: Vec<SmolStr>,
336) -> Vec<Vec<ErasedSegment>> {
337 let delimiters = delimiters
338 .into_iter()
339 .map(|it| it.to_uppercase_smolstr())
340 .collect_vec();
341 let mut new_list = Vec::new();
342 let mut sub_list = Vec::new();
343
344 for i in 0..segment_list.len() {
345 if i == segment_list.len() - 1 {
346 sub_list.push(segment_list[i].clone());
347 new_list.push(sub_list.clone());
348 } else if segment_list[i].get_type() == delimiter_type
349 && delimiters.contains(&segment_list[i].raw().to_uppercase_smolstr())
350 {
351 new_list.push(sub_list.clone());
352 sub_list.clear();
353 } else {
354 sub_list.push(segment_list[i].clone());
355 }
356 }
357
358 new_list
359}
360
361fn is_qualified_column_operator_qualified_column_sequence(segment_list: &[ErasedSegment]) -> bool {
362 if segment_list.len() != 3 {
363 return false;
364 }
365
366 if segment_list[0].get_type() == SyntaxKind::ColumnReference
367 && segment_list[0]
368 .direct_descendant_type_set()
369 .contains(SyntaxKind::Dot)
370 && segment_list[1].get_type() == SyntaxKind::ComparisonOperator
371 && segment_list[2].get_type() == SyntaxKind::ColumnReference
372 && segment_list[2]
373 .direct_descendant_type_set()
374 .contains(SyntaxKind::Dot)
375 {
376 return true;
377 }
378
379 false
380}
381
382#[cfg(test)]
383mod tests {
384 use super::*;
385 use hashbrown::HashMap;
386
387 use crate::core::config::Value;
388
389 #[test]
390 fn st09_is_fix_compatible() {
391 assert!(RuleST09::default().is_fix_compatible());
392 }
393
394 #[test]
395 fn st09_description_matches_python() {
396 let rule = RuleST09 {
397 preferred_first_table_in_join_clause: "earlier".into(),
398 };
399
400 let result = format!(
401 "Joins should list the table referenced {} first.",
402 rule.preferred_first_table_in_join_clause
403 );
404 assert_eq!(
405 result,
406 "Joins should list the table referenced earlier first."
407 );
408 }
409
410 #[test]
411 fn st09_load_from_config_rejects_invalid_value() {
412 let config = HashMap::from_iter([(
413 "preferred_first_table_in_join_clause".into(),
414 Value::String("middle".into()),
415 )]);
416
417 let err = RuleST09::default().load_from_config(&config).unwrap_err();
418 assert_eq!(
419 err,
420 "Invalid value for preferred_first_table_in_join_clause: middle. Must be one of \
421 [earlier, later]"
422 );
423 }
424
425 #[test]
426 fn st09_load_from_config_accepts_valid_value() {
427 let config = HashMap::from_iter([(
428 "preferred_first_table_in_join_clause".into(),
429 Value::String("later".into()),
430 )]);
431
432 assert!(RuleST09::default().load_from_config(&config).is_ok());
433 }
434}