sqruff_lib/rules/convention/
cv02.rs

1use ahash::AHashMap;
2use smol_str::StrExt;
3use sqruff_lib_core::dialects::syntax::{SyntaxKind, SyntaxSet};
4use sqruff_lib_core::lint_fix::LintFix;
5use sqruff_lib_core::parser::segments::base::SegmentBuilder;
6
7use crate::core::config::Value;
8use crate::core::rules::base::{Erased, ErasedRule, LintResult, Rule, RuleGroups};
9use crate::core::rules::context::RuleContext;
10use crate::core::rules::crawlers::{Crawler, SegmentSeekerCrawler};
11
12/// Prefer using `COALESCE` over `IFNULL` or `NVL`.
13///
14/// # Anti-pattern
15///
16/// `IFNULL` or `NVL` are commonly used to handle `NULL` values in SQL queries.
17/// However, they have compatibility issues across different database systems.
18///
19/// ```sql
20/// SELECT ifnull(foo, 0) AS bar,
21/// FROM baz;
22///
23/// SELECT nvl(foo, 0) AS bar,
24/// FROM baz;
25/// ```
26///
27/// # Best Practice
28///
29/// It is recommended to use `COALESCE` instead. `COALESCE` is universally
30/// supported, while `IFNULL` is not supported in Redshift, and `NVL` is not
31/// supported in BigQuery. Moreover, `COALESCE` offers greater flexibility, as
32/// it can accept an arbitrary number of arguments, enhancing the query's
33/// robustness.
34///
35/// ```sql
36/// SELECT coalesce(foo, 0) AS bar,
37/// FROM baz;
38/// ```
39#[derive(Debug, Default, Clone)]
40pub struct RuleCV02;
41
42impl Rule for RuleCV02 {
43    fn load_from_config(&self, _config: &AHashMap<String, Value>) -> Result<ErasedRule, String> {
44        Ok(RuleCV02.erased())
45    }
46
47    fn name(&self) -> &'static str {
48        "convention.coalesce"
49    }
50
51    fn description(&self) -> &'static str {
52        "Use 'COALESCE' instead of 'IFNULL' or 'NVL'."
53    }
54
55    fn long_description(&self) -> &'static str {
56        r#"
57**Anti-pattern**
58
59`IFNULL` or `NVL` are used to fill `NULL` values.
60
61```sql
62SELECT ifnull(foo, 0) AS bar,
63FROM baz;
64
65SELECT nvl(foo, 0) AS bar,
66FROM baz;
67```
68
69**Best practice**
70
71Use COALESCE instead. COALESCE is universally supported, whereas Redshift doesn’t support IFNULL and BigQuery doesn’t support NVL. Additionally, COALESCE is more flexible and accepts an arbitrary number of arguments.
72
73```sql
74SELECT coalesce(foo, 0) AS bar,
75FROM baz;
76```
77"#
78    }
79
80    fn groups(&self) -> &'static [RuleGroups] {
81        &[RuleGroups::All, RuleGroups::Convention]
82    }
83
84    fn eval(&self, context: &RuleContext) -> Vec<LintResult> {
85        // Use "COALESCE" instead of "IFNULL" or "NVL".
86        // We only care about function names, and they should be the
87        // only things we get.
88        // assert!(context.segment.is_type(SyntaxKind::FunctionNameIdentifier));
89
90        // Only care if the function is "IFNULL" or "NVL".
91
92        if !(context.segment.raw().eq_ignore_ascii_case("IFNULL")
93            || context.segment.raw().eq_ignore_ascii_case("NVL"))
94        {
95            return Vec::new();
96        }
97
98        // Create fix to replace "IFNULL" or "NVL" with "COALESCE".
99        let fix = LintFix::replace(
100            context.segment.clone(),
101            vec![
102                SegmentBuilder::token(
103                    context.tables.next_id(),
104                    "COALESCE",
105                    SyntaxKind::FunctionNameIdentifier,
106                )
107                .finish(),
108            ],
109            None,
110        );
111
112        vec![LintResult::new(
113            context.segment.clone().into(),
114            vec![fix],
115            Some(format!(
116                "Use 'COALESCE' instead of '{}'.",
117                context.segment.raw().to_uppercase_smolstr()
118            )),
119            None,
120        )]
121    }
122
123    fn crawl_behaviour(&self) -> Crawler {
124        SegmentSeekerCrawler::new(const { SyntaxSet::new(&[SyntaxKind::FunctionNameIdentifier]) })
125            .into()
126    }
127}
sqruff_lib/rules/convention/cv02.rs

sqruff_lib/rules/convention/
cv02.rs
