flowscope_core/linter/rules/
st_002.rs

1//! LINT_ST_002: Unnecessary CASE statement.
2//!
3//! SQLFluff ST02 parity: detect CASE expressions that can be replaced by
4//! simpler forms such as `COALESCE(...)`, `NOT COALESCE(...)`, or a plain
5//! column reference.
6//!
7//! Detectable patterns:
8//!   1. `CASE WHEN cond THEN TRUE  ELSE FALSE END` → `COALESCE(cond, FALSE)`
9//!   2. `CASE WHEN cond THEN FALSE ELSE TRUE  END` → `NOT COALESCE(cond, FALSE)`
10//!   3. `CASE WHEN x IS NULL     THEN y ELSE x END` → `COALESCE(x, y)`
11//!   4. `CASE WHEN x IS NOT NULL THEN x ELSE y END` → `COALESCE(x, y)`
12//!   5. `CASE WHEN x IS NULL     THEN NULL ELSE x END` → `x`
13//!   6. `CASE WHEN x IS NOT NULL THEN x ELSE NULL END` → `x`
14//!   7. `CASE WHEN x IS NOT NULL THEN x END`          → `x`
15
16use crate::linter::rule::{LintContext, LintRule};
17use crate::linter::visit;
18use crate::types::{issue_codes, Issue, IssueAutofixApplicability, IssuePatchEdit, Span};
19use regex::Regex;
20use sqlparser::ast::{Expr, Spanned, Statement, Value};
21use sqlparser::tokenizer::{Token, TokenWithSpan, Tokenizer, Whitespace};
22use std::sync::OnceLock;
23
24pub struct StructureSimpleCase;
25
26impl LintRule for StructureSimpleCase {
27    fn code(&self) -> &'static str {
28        issue_codes::LINT_ST_002
29    }
30
31    fn name(&self) -> &'static str {
32        "Structure simple case"
33    }
34
35    fn description(&self) -> &'static str {
36        "Unnecessary 'CASE' statement."
37    }
38
39    fn check(&self, stmt: &Statement, ctx: &LintContext) -> Vec<Issue> {
40        let mut issues = Vec::new();
41
42        visit::visit_expressions(stmt, &mut |expr| {
43            let Some(rewrite) = classify_unnecessary_case(expr) else {
44                return;
45            };
46
47            let mut issue = Issue::info(
48                issue_codes::LINT_ST_002,
49                "Unnecessary CASE statement. Use COALESCE function or simple column reference.",
50            )
51            .with_statement(ctx.statement_index);
52
53            if let Some((span, applicability, edits)) = build_autofix(ctx, expr, &rewrite) {
54                issue = issue
55                    .with_span(span)
56                    .with_autofix_edits(applicability, edits);
57            }
58
59            issues.push(issue);
60        });
61
62        if issues.is_empty() && statementless_template_case_requires_st02(ctx.statement_sql()) {
63            issues.push(
64                Issue::info(
65                    issue_codes::LINT_ST_002,
66                    "Unnecessary CASE statement. Use COALESCE function or simple column reference.",
67                )
68                .with_statement(ctx.statement_index),
69            );
70        }
71
72        issues
73    }
74}
75
76// ---------------------------------------------------------------------------
77// Rewrite classification
78// ---------------------------------------------------------------------------
79
80/// The kind of simplification that can be applied.
81#[derive(Debug, Clone)]
82enum UnnecessaryCaseKind {
83    /// `CASE WHEN cond THEN TRUE ELSE FALSE END` → `COALESCE(cond, FALSE)`
84    BoolCoalesce,
85    /// `CASE WHEN cond THEN FALSE ELSE TRUE END` → `NOT COALESCE(cond, FALSE)`
86    BoolCoalesceNegated,
87    /// `CASE WHEN x IS [NOT] NULL THEN a ELSE b END` → `COALESCE(x, y)`
88    NullCoalesce,
89    /// `CASE WHEN x IS [NOT] NULL THEN x [ELSE NULL] END` → `x`
90    ColumnIdentity,
91}
92
93/// Classify the CASE expression if it is an unnecessary pattern.
94fn classify_unnecessary_case(expr: &Expr) -> Option<UnnecessaryCaseKind> {
95    let Expr::Case {
96        operand: None,
97        conditions,
98        else_result,
99        ..
100    } = expr
101    else {
102        return None;
103    };
104
105    // Only single-WHEN case expressions can be simplified.
106    if conditions.len() != 1 {
107        return None;
108    }
109
110    let when = &conditions[0];
111    let condition = &when.condition;
112    let result = &when.result;
113
114    // -----------------------------------------------------------------------
115    // Pattern group 1: boolean CASE WHEN cond THEN TRUE/FALSE ELSE FALSE/TRUE
116    // -----------------------------------------------------------------------
117    if let Some(result_bool) = expr_bool_value(result) {
118        if let Some(else_bool) = else_result.as_deref().and_then(expr_bool_value) {
119            // TRUE/FALSE or FALSE/TRUE — other combos don't simplify.
120            if result_bool && !else_bool {
121                // CASE WHEN cond THEN TRUE ELSE FALSE END → coalesce(cond, false)
122                return Some(UnnecessaryCaseKind::BoolCoalesce);
123            } else if !result_bool && else_bool {
124                // CASE WHEN cond THEN FALSE ELSE TRUE END → not coalesce(cond, false)
125                return Some(UnnecessaryCaseKind::BoolCoalesceNegated);
126            }
127        }
128    }
129
130    // -----------------------------------------------------------------------
131    // Pattern group 2: NULL-check simplifications
132    // -----------------------------------------------------------------------
133    // CASE WHEN x IS NULL THEN ... ELSE ... END
134    if let Expr::IsNull(checked_expr) = condition {
135        return classify_null_check_case(checked_expr, result, else_result.as_deref(), true);
136    }
137
138    // CASE WHEN x IS NOT NULL THEN ... ELSE ... END
139    if let Expr::IsNotNull(checked_expr) = condition {
140        return classify_null_check_case(checked_expr, result, else_result.as_deref(), false);
141    }
142
143    None
144}
145
146/// Classify a CASE with IS NULL / IS NOT NULL condition.
147fn classify_null_check_case(
148    checked_expr: &Expr,
149    then_result: &Expr,
150    else_result: Option<&Expr>,
151    is_null_check: bool,
152) -> Option<UnnecessaryCaseKind> {
153    // Use AST Display for structural comparison only (case-insensitive matching).
154    let checked_text = format!("{checked_expr}");
155    let then_text = format!("{then_result}");
156    let else_text = else_result.map(|e| format!("{e}"));
157
158    if is_null_check {
159        // CASE WHEN x IS NULL THEN ... ELSE x END
160        if let Some(ref else_t) = else_text {
161            if else_t == &checked_text {
162                if is_null_expr(then_result) {
163                    // CASE WHEN x IS NULL THEN NULL ELSE x END → x
164                    return Some(UnnecessaryCaseKind::ColumnIdentity);
165                }
166                // CASE WHEN x IS NULL THEN y ELSE x END → COALESCE(x, y)
167                return Some(UnnecessaryCaseKind::NullCoalesce);
168            }
169        }
170    } else {
171        // CASE WHEN x IS NOT NULL THEN ... ELSE ... END
172        if then_text == checked_text {
173            match &else_text {
174                Some(et) if is_null_text(et) => {
175                    // CASE WHEN x IS NOT NULL THEN x ELSE NULL END → x
176                    return Some(UnnecessaryCaseKind::ColumnIdentity);
177                }
178                None => {
179                    // CASE WHEN x IS NOT NULL THEN x END → x
180                    return Some(UnnecessaryCaseKind::ColumnIdentity);
181                }
182                Some(_) => {
183                    // CASE WHEN x IS NOT NULL THEN x ELSE y END → COALESCE(x, y)
184                    return Some(UnnecessaryCaseKind::NullCoalesce);
185                }
186            }
187        }
188    }
189
190    None
191}
192
193fn expr_bool_value(expr: &Expr) -> Option<bool> {
194    match expr {
195        Expr::Value(v) => match &v.value {
196            Value::Boolean(b) => Some(*b),
197            _ => None,
198        },
199        _ => None,
200    }
201}
202
203fn is_null_expr(expr: &Expr) -> bool {
204    matches!(expr, Expr::Value(v) if matches!(v.value, Value::Null))
205}
206
207fn is_null_text(s: &str) -> bool {
208    s.eq_ignore_ascii_case("NULL")
209}
210
211fn statementless_template_case_requires_st02(sql: &str) -> bool {
212    if !contains_template_tags(sql) {
213        return false;
214    }
215
216    static RE: OnceLock<Regex> = OnceLock::new();
217    let pattern = RE.get_or_init(|| {
218        Regex::new(
219            r"(?is)\bcase\b.*?\bwhen\b\s+([a-zA-Z_][\w\.]*)\s+is\s+null\s+then\s+(\{\{.*?\}\})\s+else\s+([a-zA-Z_][\w\.]*)\s+end\b",
220        )
221        .expect("valid ST02 template fallback regex")
222    });
223
224    pattern.captures(sql).is_some_and(|caps| {
225        let checked = caps.get(1).map_or("", |m| m.as_str());
226        let else_expr = caps.get(3).map_or("", |m| m.as_str());
227        !checked.is_empty() && checked.eq_ignore_ascii_case(else_expr)
228    })
229}
230
231fn contains_template_tags(sql: &str) -> bool {
232    sql.contains("{{") || sql.contains("{%") || sql.contains("{#")
233}
234
235// ---------------------------------------------------------------------------
236// Autofix
237// ---------------------------------------------------------------------------
238
239fn build_autofix(
240    ctx: &LintContext,
241    expr: &Expr,
242    rewrite: &UnnecessaryCaseKind,
243) -> Option<(Span, IssueAutofixApplicability, Vec<IssuePatchEdit>)> {
244    let (expr_start, expr_end) = expr_statement_offsets(ctx, expr)?;
245    let expr_span = ctx.span_from_statement_offset(expr_start, expr_end);
246
247    let applicability = if span_contains_comment(ctx, expr_span) {
248        IssueAutofixApplicability::Unsafe
249    } else {
250        IssueAutofixApplicability::Safe
251    };
252
253    let Expr::Case {
254        conditions,
255        else_result,
256        ..
257    } = expr
258    else {
259        return None;
260    };
261    let when = conditions.first()?;
262    let condition = &when.condition;
263
264    let replacement = match rewrite {
265        UnnecessaryCaseKind::BoolCoalesce => {
266            let cond_text = source_text_for_expr(ctx, condition)?;
267            format!("coalesce({cond_text}, false)")
268        }
269        UnnecessaryCaseKind::BoolCoalesceNegated => {
270            let cond_text = source_text_for_expr(ctx, condition)?;
271            format!("not coalesce({cond_text}, false)")
272        }
273        UnnecessaryCaseKind::NullCoalesce => {
274            let (checked_expr, fallback_expr) =
275                null_coalesce_operands(condition, &when.result, else_result.as_deref())?;
276            let checked_text = source_text_for_expr(ctx, checked_expr)?;
277            let fallback_text = source_text_for_expr(ctx, fallback_expr)?;
278            format!("coalesce({checked_text}, {fallback_text})")
279        }
280        UnnecessaryCaseKind::ColumnIdentity => {
281            let col_expr = column_identity_expr(condition, &when.result, else_result.as_deref())?;
282            source_text_for_expr(ctx, col_expr)?
283        }
284    };
285
286    Some((
287        expr_span,
288        applicability,
289        vec![IssuePatchEdit::new(expr_span, replacement)],
290    ))
291}
292
293/// Extract the original source text for an expression, preserving keyword case.
294///
295/// Falls back to AST Display with keyword-case normalization when the span
296/// does not capture the full expression text (e.g. sqlparser omits unary
297/// operator keywords like `NOT` from span calculations).
298fn source_text_for_expr(ctx: &LintContext, expr: &Expr) -> Option<String> {
299    let display_text = format!("{expr}");
300
301    let Some((start, end)) = expr_statement_offsets(ctx, expr) else {
302        return if display_text.is_empty() {
303            None
304        } else {
305            Some(normalize_keywords_to_match_source(
306                ctx.statement_sql(),
307                &display_text,
308            ))
309        };
310    };
311    let sql = ctx.statement_sql();
312    if end > sql.len() || start > end {
313        return if display_text.is_empty() {
314            None
315        } else {
316            Some(normalize_keywords_to_match_source(sql, &display_text))
317        };
318    }
319
320    let source = &sql[start..end];
321
322    // Validate that the source text is correct by checking the AST Display
323    // output length.  When sqlparser's Spanned impl omits a keyword prefix
324    // (e.g. NOT in UnaryOp), the source text will be too short.
325    if source.len() >= display_text.len() {
326        return Some(source.to_string());
327    }
328
329    // Fall back to AST Display but normalise keyword case to match the
330    // surrounding SQL.  Detect the dominant case from the CASE expression
331    // context by looking at the `when` keyword that precedes the condition.
332    Some(normalize_keywords_to_match_source(sql, &display_text))
333}
334
335/// Normalise SQL keywords in `text` to match the case used in `context_sql`.
336fn normalize_keywords_to_match_source(context_sql: &str, text: &str) -> String {
337    // Check if the source SQL actually uses lowercase keywords by comparing
338    // against the original (non-lowered) text.
339    let source_uses_lower = context_sql.contains(" and ")
340        || context_sql.contains(" or ")
341        || context_sql.contains(" not ")
342        || context_sql.contains("when not ")
343        || context_sql.contains("when ");
344
345    if source_uses_lower {
346        text.replace(" AND ", " and ")
347            .replace(" OR ", " or ")
348            .replace("NOT ", "not ")
349            .replace(" IS NOT NULL", " is not null")
350            .replace(" IS NULL", " is null")
351            .replace(" TRUE", " true")
352            .replace(" FALSE", " false")
353    } else {
354        text.to_string()
355    }
356}
357
358/// For a NullCoalesce rewrite, return (checked_expr, fallback_expr).
359fn null_coalesce_operands<'a>(
360    condition: &'a Expr,
361    then_result: &'a Expr,
362    else_result: Option<&'a Expr>,
363) -> Option<(&'a Expr, &'a Expr)> {
364    if let Expr::IsNull(checked) = condition {
365        // CASE WHEN x IS NULL THEN y ELSE x END → COALESCE(x, y)
366        Some((checked.as_ref(), then_result))
367    } else if let Expr::IsNotNull(checked) = condition {
368        // CASE WHEN x IS NOT NULL THEN x ELSE y END → COALESCE(x, y)
369        let fallback = else_result?;
370        Some((checked.as_ref(), fallback))
371    } else {
372        None
373    }
374}
375
376/// For a ColumnIdentity rewrite, return the column expression.
377fn column_identity_expr<'a>(
378    condition: &'a Expr,
379    _then_result: &'a Expr,
380    _else_result: Option<&'a Expr>,
381) -> Option<&'a Expr> {
382    if let Expr::IsNull(checked) = condition {
383        // CASE WHEN x IS NULL THEN NULL ELSE x END → x
384        Some(checked.as_ref())
385    } else if let Expr::IsNotNull(checked) = condition {
386        // CASE WHEN x IS NOT NULL THEN x [ELSE NULL] END → x
387        Some(checked.as_ref())
388    } else {
389        None
390    }
391}
392
393// ---------------------------------------------------------------------------
394// Span and offset utilities
395// ---------------------------------------------------------------------------
396
397fn expr_statement_offsets(ctx: &LintContext, expr: &Expr) -> Option<(usize, usize)> {
398    if ctx.statement_range.start > 0 {
399        if let Some((start, end)) = expr_span_offsets(ctx.sql, expr) {
400            if start >= ctx.statement_range.start && end <= ctx.statement_range.end {
401                return Some((
402                    start - ctx.statement_range.start,
403                    end - ctx.statement_range.start,
404                ));
405            }
406        }
407    }
408
409    if let Some((start, end)) = expr_span_offsets(ctx.statement_sql(), expr) {
410        return Some((start, end));
411    }
412
413    let (start, end) = expr_span_offsets(ctx.sql, expr)?;
414    if start < ctx.statement_range.start || end > ctx.statement_range.end {
415        return None;
416    }
417
418    Some((
419        start - ctx.statement_range.start,
420        end - ctx.statement_range.start,
421    ))
422}
423
424fn expr_span_offsets(sql: &str, expr: &Expr) -> Option<(usize, usize)> {
425    let span = expr.span();
426    if span.start.line == 0 || span.start.column == 0 || span.end.line == 0 || span.end.column == 0
427    {
428        return None;
429    }
430
431    let start = line_col_to_offset(sql, span.start.line as usize, span.start.column as usize)?;
432    let end = line_col_to_offset(sql, span.end.line as usize, span.end.column as usize)?;
433    (end >= start).then_some((start, end))
434}
435
436fn span_contains_comment(ctx: &LintContext, span: Span) -> bool {
437    let from_document_tokens = ctx.with_document_tokens(|tokens| {
438        if tokens.is_empty() {
439            return None;
440        }
441        Some(tokens.iter().any(|token| {
442            let Some((start, end)) = token_with_span_offsets(ctx.sql, token) else {
443                return false;
444            };
445            start >= span.start && end <= span.end && is_comment_token(&token.token)
446        }))
447    });
448
449    if let Some(has_comment) = from_document_tokens {
450        return has_comment;
451    }
452
453    let Some(tokens) = tokenize_statement_with_spans(ctx.statement_sql(), ctx.dialect()) else {
454        return false;
455    };
456    let statement_span = Span::new(
457        span.start.saturating_sub(ctx.statement_range.start),
458        span.end.saturating_sub(ctx.statement_range.start),
459    );
460    tokens.iter().any(|token| {
461        let Some((start, end)) = token_with_span_offsets(ctx.statement_sql(), token) else {
462            return false;
463        };
464        start >= statement_span.start && end <= statement_span.end && is_comment_token(&token.token)
465    })
466}
467
468fn tokenize_statement_with_spans(
469    sql: &str,
470    dialect: crate::types::Dialect,
471) -> Option<Vec<TokenWithSpan>> {
472    let dialect = dialect.to_sqlparser_dialect();
473    let mut tokenizer = Tokenizer::new(dialect.as_ref(), sql);
474    tokenizer.tokenize_with_location().ok()
475}
476
477fn token_with_span_offsets(sql: &str, token: &TokenWithSpan) -> Option<(usize, usize)> {
478    let start = line_col_to_offset(
479        sql,
480        token.span.start.line as usize,
481        token.span.start.column as usize,
482    )?;
483    let end = line_col_to_offset(
484        sql,
485        token.span.end.line as usize,
486        token.span.end.column as usize,
487    )?;
488    Some((start, end))
489}
490
491fn line_col_to_offset(sql: &str, line: usize, column: usize) -> Option<usize> {
492    if line == 0 || column == 0 {
493        return None;
494    }
495
496    let mut current_line = 1usize;
497    let mut line_start = 0usize;
498
499    for (idx, ch) in sql.char_indices() {
500        if current_line == line {
501            break;
502        }
503        if ch == '\n' {
504            current_line += 1;
505            line_start = idx + ch.len_utf8();
506        }
507    }
508    if current_line != line {
509        return None;
510    }
511
512    let mut current_column = 1usize;
513    for (rel_idx, ch) in sql[line_start..].char_indices() {
514        if current_column == column {
515            return Some(line_start + rel_idx);
516        }
517        if ch == '\n' {
518            return None;
519        }
520        current_column += 1;
521    }
522
523    if current_column == column {
524        return Some(sql.len());
525    }
526
527    None
528}
529
530fn is_comment_token(token: &Token) -> bool {
531    matches!(
532        token,
533        Token::Whitespace(Whitespace::SingleLineComment { .. } | Whitespace::MultiLineComment(_))
534    )
535}
536
537#[cfg(test)]
538mod tests {
539    use super::*;
540    use crate::parser::parse_sql;
541    use crate::types::{IssueAutofixApplicability, IssuePatchEdit};
542
543    fn run(sql: &str) -> Vec<Issue> {
544        let statements = parse_sql(sql).expect("parse");
545        let rule = StructureSimpleCase;
546        statements
547            .iter()
548            .enumerate()
549            .flat_map(|(index, statement)| {
550                rule.check(
551                    statement,
552                    &LintContext {
553                        sql,
554                        statement_range: 0..sql.len(),
555                        statement_index: index,
556                    },
557                )
558            })
559            .collect()
560    }
561
562    fn apply_edits(sql: &str, edits: &[IssuePatchEdit]) -> String {
563        let mut output = sql.to_string();
564        let mut ordered = edits.iter().collect::<Vec<_>>();
565        ordered.sort_by_key(|edit| edit.span.start);
566
567        for edit in ordered.into_iter().rev() {
568            output.replace_range(edit.span.start..edit.span.end, &edit.replacement);
569        }
570
571        output
572    }
573
574    // --- Pass cases from SQLFluff ST02 fixture ---
575
576    #[test]
577    fn pass_case_cannot_be_reduced_1() {
578        let sql = "select fab > 0 as is_fab from fancy_table";
579        assert!(run(sql).is_empty());
580    }
581
582    #[test]
583    fn pass_case_cannot_be_reduced_2() {
584        let sql = "select case when fab > 0 then true end as is_fab from fancy_table";
585        assert!(run(sql).is_empty());
586    }
587
588    #[test]
589    fn pass_case_cannot_be_reduced_3() {
590        let sql = "select case when fab is not null then false end as is_fab from fancy_table";
591        assert!(run(sql).is_empty());
592    }
593
594    #[test]
595    fn pass_case_cannot_be_reduced_4() {
596        let sql = "select case when fab > 0 then true else true end as is_fab from fancy_table";
597        assert!(run(sql).is_empty());
598    }
599
600    #[test]
601    fn pass_case_cannot_be_reduced_5() {
602        let sql =
603            "select case when fab <> 0 then 'just a string' end as fab_category from fancy_table";
604        assert!(run(sql).is_empty());
605    }
606
607    #[test]
608    fn pass_case_cannot_be_reduced_6() {
609        let sql = "select case when fab <> 0 then true when fab < 0 then 'not a bool' end as fab_category from fancy_table";
610        assert!(run(sql).is_empty());
611    }
612
613    #[test]
614    fn pass_single_when_is_null_then_bar() {
615        let sql = "select foo, case when bar is null then bar else '123' end as test from baz";
616        assert!(run(sql).is_empty());
617    }
618
619    #[test]
620    fn pass_is_not_null_then_literal() {
621        let sql = "select foo, case when bar is not null then '123' else bar end as test from baz";
622        assert!(run(sql).is_empty());
623    }
624
625    #[test]
626    fn pass_multiple_when_is_not_null() {
627        let sql = "select foo, case when bar is not null then '123' when foo is not null then '456' else bar end as test from baz";
628        assert!(run(sql).is_empty());
629    }
630
631    #[test]
632    fn pass_compound_condition() {
633        let sql = "select foo, case when bar is not null and abs(foo) > 0 then '123' else bar end as test from baz";
634        assert!(run(sql).is_empty());
635    }
636
637    #[test]
638    fn pass_window_lead_is_null() {
639        let sql = "SELECT dv_runid, CASE WHEN LEAD(dv_startdateutc) OVER (PARTITION BY rowid ORDER BY dv_startdateutc) IS NULL THEN 1 ELSE 0 END AS loadstate FROM d";
640        assert!(run(sql).is_empty());
641    }
642
643    #[test]
644    fn pass_coalesce_is_null() {
645        let sql = "select field_1, field_2, field_3, case when coalesce(field_2, field_3) is null then 1 else 0 end as field_4 from my_table";
646        assert!(run(sql).is_empty());
647    }
648
649    #[test]
650    fn pass_submitted_timestamp() {
651        let sql = "SELECT CASE WHEN item.submitted_timestamp IS NOT NULL THEN item.sitting_id END";
652        assert!(run(sql).is_empty());
653    }
654
655    #[test]
656    fn pass_array_accessor_snowflake() {
657        let sql = "SELECT CASE WHEN genres[0] IS NULL THEN 'x' ELSE genres END AS g FROM table_t";
658        assert!(run(sql).is_empty());
659    }
660
661    // --- Fail cases from SQLFluff ST02 fixture ---
662
663    #[test]
664    fn fail_unnecessary_case_bool_true_false() {
665        let sql = "select case when fab > 0 then true else false end as is_fab from fancy_table";
666        let issues = run(sql);
667        assert_eq!(issues.len(), 1);
668        assert_eq!(issues[0].code, issue_codes::LINT_ST_002);
669    }
670
671    #[test]
672    fn fail_unnecessary_case_bool_false_true() {
673        let sql = "select case when fab > 0 then false else true end as is_fab from fancy_table";
674        let issues = run(sql);
675        assert_eq!(issues.len(), 1);
676    }
677
678    #[test]
679    fn fail_unnecessary_case_bool_compound_condition() {
680        let sql = "select case when fab > 0 and tot > 0 then true else false end as is_fab from fancy_table";
681        let issues = run(sql);
682        assert_eq!(issues.len(), 1);
683    }
684
685    #[test]
686    fn fail_unnecessary_case_is_null_coalesce() {
687        let sql = "select foo, case when bar is null then '123' else bar end as test from baz";
688        let issues = run(sql);
689        assert_eq!(issues.len(), 1);
690    }
691
692    #[test]
693    fn fail_unnecessary_case_is_not_null_coalesce() {
694        let sql = "select foo, case when bar is not null then bar else '123' end as test from baz";
695        let issues = run(sql);
696        assert_eq!(issues.len(), 1);
697    }
698
699    #[test]
700    fn fail_unnecessary_case_is_null_identity_null_else() {
701        let sql = "select foo, case when bar is null then null else bar end as test from baz";
702        let issues = run(sql);
703        assert_eq!(issues.len(), 1);
704    }
705
706    #[test]
707    fn fail_unnecessary_case_is_not_null_identity_else_null() {
708        let sql = "select foo, case when bar is not null then bar else null end as test from baz";
709        let issues = run(sql);
710        assert_eq!(issues.len(), 1);
711    }
712
713    #[test]
714    fn fail_unnecessary_case_is_not_null_identity_no_else() {
715        let sql = "select foo, case when bar is not null then bar end as test from baz";
716        let issues = run(sql);
717        assert_eq!(issues.len(), 1);
718    }
719
720    #[test]
721    fn fail_is_null_then_false_else_true() {
722        let sql = "select case when perks.perk is null then false else true end as perk_redeemed from subscriptions_xf";
723        let issues = run(sql);
724        assert_eq!(issues.len(), 1);
725    }
726
727    // --- Autofix tests ---
728
729    #[test]
730    fn autofix_bool_true_false() {
731        let sql = "select case when fab > 0 then true else false end as is_fab from fancy_table";
732        let issues = run(sql);
733        assert_eq!(issues.len(), 1);
734        let autofix = issues[0].autofix.as_ref().expect("expected autofix");
735        assert_eq!(autofix.applicability, IssueAutofixApplicability::Safe);
736        let fixed = apply_edits(sql, &autofix.edits);
737        assert_eq!(
738            fixed,
739            "select coalesce(fab > 0, false) as is_fab from fancy_table"
740        );
741    }
742
743    #[test]
744    fn autofix_bool_false_true() {
745        let sql = "select case when fab > 0 then false else true end as is_fab from fancy_table";
746        let issues = run(sql);
747        assert_eq!(issues.len(), 1);
748        let autofix = issues[0].autofix.as_ref().expect("expected autofix");
749        let fixed = apply_edits(sql, &autofix.edits);
750        assert_eq!(
751            fixed,
752            "select not coalesce(fab > 0, false) as is_fab from fancy_table"
753        );
754    }
755
756    #[test]
757    fn autofix_is_null_coalesce() {
758        let sql = "select foo, case when bar is null then '123' else bar end as test from baz";
759        let issues = run(sql);
760        assert_eq!(issues.len(), 1);
761        let autofix = issues[0].autofix.as_ref().expect("expected autofix");
762        let fixed = apply_edits(sql, &autofix.edits);
763        assert_eq!(fixed, "select foo, coalesce(bar, '123') as test from baz");
764    }
765
766    #[test]
767    fn autofix_is_not_null_coalesce() {
768        let sql = "select foo, case when bar is not null then bar else '123' end as test from baz";
769        let issues = run(sql);
770        assert_eq!(issues.len(), 1);
771        let autofix = issues[0].autofix.as_ref().expect("expected autofix");
772        let fixed = apply_edits(sql, &autofix.edits);
773        assert_eq!(fixed, "select foo, coalesce(bar, '123') as test from baz");
774    }
775
776    #[test]
777    fn autofix_is_null_then_null_identity() {
778        let sql = "select foo, case when bar is null then null else bar end as test from baz";
779        let issues = run(sql);
780        assert_eq!(issues.len(), 1);
781        let autofix = issues[0].autofix.as_ref().expect("expected autofix");
782        let fixed = apply_edits(sql, &autofix.edits);
783        assert_eq!(fixed, "select foo, bar as test from baz");
784    }
785
786    #[test]
787    fn autofix_is_not_null_identity_no_else() {
788        let sql = "select foo, case when bar is not null then bar end as test from baz";
789        let issues = run(sql);
790        assert_eq!(issues.len(), 1);
791        let autofix = issues[0].autofix.as_ref().expect("expected autofix");
792        let fixed = apply_edits(sql, &autofix.edits);
793        assert_eq!(fixed, "select foo, bar as test from baz");
794    }
795
796    #[test]
797    fn autofix_bool_compound_preserves_keyword_case() {
798        let sql =
799            "select case when fab > 0 and tot > 0 then true else false end as is_fab from fancy_table";
800        let issues = run(sql);
801        assert_eq!(issues.len(), 1);
802        let autofix = issues[0].autofix.as_ref().expect("expected autofix");
803        let fixed = apply_edits(sql, &autofix.edits);
804        assert_eq!(
805            fixed,
806            "select coalesce(fab > 0 and tot > 0, false) as is_fab from fancy_table"
807        );
808    }
809
810    #[test]
811    fn autofix_bool_negated_compound_preserves_keyword_case() {
812        let sql =
813            "select case when fab > 0 and tot > 0 then false else true end as is_fab from fancy_table";
814        let issues = run(sql);
815        assert_eq!(issues.len(), 1);
816        let autofix = issues[0].autofix.as_ref().expect("expected autofix");
817        let fixed = apply_edits(sql, &autofix.edits);
818        assert_eq!(
819            fixed,
820            "select not coalesce(fab > 0 and tot > 0, false) as is_fab from fancy_table"
821        );
822    }
823
824    #[test]
825    fn autofix_multiline_compound_preserves_keyword_case() {
826        let sql = "select\n    case\n        when fab > 0 and tot > 0 then true else false end as is_fab\nfrom fancy_table";
827        let issues = run(sql);
828        assert_eq!(issues.len(), 1);
829        let autofix = issues[0].autofix.as_ref().expect("expected autofix");
830        let fixed = apply_edits(sql, &autofix.edits);
831        assert_eq!(
832            fixed,
833            "select\n    coalesce(fab > 0 and tot > 0, false) as is_fab\nfrom fancy_table"
834        );
835    }
836
837    #[test]
838    fn autofix_multiline_negated_or_preserves_keyword_case() {
839        let sql = "select\n    case\n        when not fab > 0 or tot > 0 then false else true end as is_fab\nfrom fancy_table";
840        let issues = run(sql);
841        assert_eq!(issues.len(), 1);
842        let autofix = issues[0].autofix.as_ref().expect("expected autofix");
843        let fixed = apply_edits(sql, &autofix.edits);
844        assert_eq!(
845            fixed,
846            "select\n    not coalesce(not fab > 0 or tot > 0, false) as is_fab\nfrom fancy_table"
847        );
848    }
849
850    #[test]
851    fn comment_in_case_downgrades_autofix_to_unsafe() {
852        let sql =
853            "select case when fab > 0 /*keep*/ then true else false end as is_fab from fancy_table";
854        let issues = run(sql);
855        assert_eq!(issues.len(), 1);
856        let autofix = issues[0]
857            .autofix
858            .as_ref()
859            .expect("expected autofix metadata");
860        assert_eq!(autofix.applicability, IssueAutofixApplicability::Unsafe);
861    }
862
863    #[test]
864    fn autofix_comment_after_case_keyword_uses_display_fallback() {
865        let sql = "select\n    subscriptions_xf.metadata_migrated,\n\n    case  -- BEFORE ST02 FIX\n        when perks.perk is null then false\n        else true\n    end as perk_redeemed,\n\n    perks.received_at as perk_received_at\n\nfrom subscriptions_xf\n";
866        let issues = run(sql);
867        assert_eq!(issues.len(), 1);
868        let autofix = issues[0]
869            .autofix
870            .as_ref()
871            .expect("expected autofix metadata");
872        assert_eq!(autofix.applicability, IssueAutofixApplicability::Unsafe);
873        let fixed = apply_edits(sql, &autofix.edits);
874        assert_eq!(
875            fixed,
876            "select\n    subscriptions_xf.metadata_migrated,\n\n    not coalesce(perks.perk is null, false) as perk_redeemed,\n\n    perks.received_at as perk_received_at\n\nfrom subscriptions_xf\n"
877        );
878    }
879
880    #[test]
881    fn statementless_template_case_is_still_reported_without_autofix() {
882        let sql = "select\n    foo,\n    case\n        when\n            bar is null then {{ result }}\n        else bar\n    end as test\nfrom baz;\n";
883        let synthetic = parse_sql("SELECT 1").expect("parse");
884        let rule = StructureSimpleCase;
885        let issues = rule.check(
886            &synthetic[0],
887            &LintContext {
888                sql,
889                statement_range: 0..sql.len(),
890                statement_index: 0,
891            },
892        );
893        assert_eq!(issues.len(), 1);
894        assert_eq!(issues[0].code, issue_codes::LINT_ST_002);
895        assert!(
896            issues[0].autofix.is_none(),
897            "template fallback should report detection-only without copying templated code"
898        );
899    }
900}
flowscope_core/linter/rules/st_002.rs

flowscope_core/linter/rules/
st_002.rs
