polyglot_sql/dialects/
vertica.rs

1//! Vertica Dialect
2//!
3//! Vertica (OpenText Analytics Database) is a columnar MPP analytic database whose
4//! SQL surface is largely PostgreSQL-compatible.
5//! Reference: https://docs.vertica.com/latest/en/sql-reference/
6//! Semantics cross-checked against https://github.com/luisdelatorre012/vertica-sqlglot-dialect
7//!
8//! Key characteristics:
9//! - Double-quote identifiers, case-insensitive (folded to lowercase)
10//! - `::` casts, ILIKE, `||` concatenation, LIMIT/OFFSET
11//! - MINUS is an alias for EXCEPT
12//! - All integer types are 64-bit (INT, SMALLINT, TINYINT are BIGINT)
13//! - REAL / FLOAT are DOUBLE PRECISION
14//! - LONG VARCHAR / LONG VARBINARY types
15//! - NVL, NVL2, DECODE, ZEROIFNULL, LISTAGG, DATEDIFF(unit, a, b), TIMESTAMPADD
16//! - GETDATE() / SYSDATE are statement-start timestamps
17//! - No QUALIFY, TRY_CAST or semi/anti join syntax
18//! - No nested comments
19
20use super::{DialectImpl, DialectType};
21#[cfg(feature = "transpile")]
22use crate::error::Result;
23#[cfg(feature = "transpile")]
24use crate::expressions::{
25    AggFunc, Case, Expression, Function, Identifier, IntervalUnit, ListAggFunc, Literal, VarArgFunc,
26};
27#[cfg(feature = "generate")]
28use crate::generator::GeneratorConfig;
29use crate::tokens::TokenizerConfig;
30
31/// Vertica dialect
32pub struct VerticaDialect;
33
34impl DialectImpl for VerticaDialect {
35    fn dialect_type(&self) -> DialectType {
36        DialectType::Vertica
37    }
38
39    fn tokenizer_config(&self) -> TokenizerConfig {
40        use crate::tokens::TokenType;
41        let mut config = TokenizerConfig::default();
42        // Vertica uses double quotes for identifiers (PostgreSQL-style)
43        config.identifiers.insert('"', '"');
44        // Vertica does NOT support nested comments
45        config.nested_comments = false;
46        // `//` is integer division
47        config.double_slash_int_div = true;
48        // MINUS is an alias for EXCEPT in Vertica
49        config
50            .keywords
51            .insert("MINUS".to_string(), TokenType::Except);
52        config
53    }
54
55    #[cfg(feature = "generate")]
56    fn generator_config(&self) -> GeneratorConfig {
57        use crate::generator::{IdentifierQuoteStyle, LimitFetchStyle};
58        GeneratorConfig {
59            identifier_quote: '"',
60            identifier_quote_style: IdentifierQuoteStyle::DOUBLE_QUOTE,
61            dialect: Some(DialectType::Vertica),
62            single_string_interval: true,
63            locking_reads_supported: true,
64            limit_fetch_style: LimitFetchStyle::Limit,
65            nvl2_supported: true,
66            supports_median: true,
67            aggregate_filter_supported: false,
68            ..Default::default()
69        }
70    }
71
72    #[cfg(feature = "transpile")]
73    fn transform_expr(&self, expr: Expression) -> Result<Expression> {
74        match expr {
75            // IFNULL -> COALESCE in Vertica
76            Expression::IfNull(f) => Ok(Expression::Coalesce(Box::new(VarArgFunc {
77                original_name: None,
78                expressions: vec![f.this, f.expression],
79                inferred_type: None,
80            }))),
81
82            // Coalesce with original_name (e.g., IFNULL parsed as Coalesce) -> clear original_name
83            Expression::Coalesce(mut f) => {
84                f.original_name = None;
85                Ok(Expression::Coalesce(f))
86            }
87
88            Expression::TryCast(_) | Expression::SafeCast(_) => {
89                Err(crate::error::Error::unsupported(
90                    "safe casts: Vertica ::! does not suppress constant cast failures",
91                    "vertica",
92                ))
93            }
94
95            // CountIf -> SUM(CASE WHEN condition THEN 1 ELSE 0 END)
96            Expression::CountIf(f) => {
97                let case_expr = Expression::Case(Box::new(Case {
98                    operand: None,
99                    whens: vec![(f.this.clone(), Expression::number(1))],
100                    else_: Some(Expression::number(0)),
101                    comments: Vec::new(),
102                    inferred_type: None,
103                }));
104                Ok(Expression::Sum(Box::new(AggFunc {
105                    ignore_nulls: None,
106                    having_max: None,
107                    this: case_expr,
108                    distinct: f.distinct,
109                    filter: f.filter,
110                    order_by: Vec::new(),
111                    name: None,
112                    limit: None,
113                    inferred_type: None,
114                })))
115            }
116
117            // RAND -> RANDOM in Vertica
118            Expression::Rand(r) => {
119                let _ = r.seed;
120                Ok(Expression::Random(crate::expressions::Random))
121            }
122
123            // DAYOFWEEK_ISO has no shared generator form
124            Expression::DayOfWeekIso(f) => Ok(Expression::Function(Box::new(Function::new(
125                "DAYOFWEEK_ISO".to_string(),
126                vec![f.this],
127            )))),
128
129            // DATE_ADD / DATEADD -> TIMESTAMPADD(unit, n, ts); Vertica has no DATEADD
130            Expression::DateAdd(f) => Ok(timestamp_add(
131                interval_unit_name(&f.unit),
132                f.interval,
133                f.this,
134            )),
135
136            // APPROX_COUNT_DISTINCT -> APPROXIMATE_COUNT_DISTINCT
137            Expression::ApproxDistinct(mut f) | Expression::ApproxCountDistinct(mut f) => {
138                if !f.order_by.is_empty()
139                    || f.limit.is_some()
140                    || f.having_max.is_some()
141                    || f.ignore_nulls.is_some()
142                {
143                    return Err(crate::error::Error::unsupported(
144                        "approximate distinct aggregate modifiers",
145                        "vertica",
146                    ));
147                }
148                if let Some(filter) = f.filter.take() {
149                    f.this = Expression::Case(Box::new(Case {
150                        operand: None,
151                        whens: vec![(filter, f.this)],
152                        else_: None,
153                        comments: Vec::new(),
154                        inferred_type: None,
155                    }));
156                }
157                // DISTINCT is redundant for an approximate distinct count.
158                f.distinct = false;
159                f.name = None;
160                Ok(Expression::ApproxDistinct(f))
161            }
162
163            // GROUP_CONCAT / STRING_AGG -> LISTAGG
164            Expression::GroupConcat(f) => Ok(Expression::ListAgg(Box::new(ListAggFunc {
165                source_dialect: None,
166                this: f.this,
167                separator: f.separator,
168                on_overflow: None,
169                max_length: None,
170                order_by: f.order_by,
171                distinct: f.distinct,
172                filter: f.filter,
173                inferred_type: None,
174            }))),
175            Expression::StringAgg(f) => Ok(Expression::ListAgg(Box::new(ListAggFunc {
176                source_dialect: None,
177                this: f.this,
178                separator: f.separator,
179                on_overflow: None,
180                max_length: None,
181                order_by: f.order_by,
182                distinct: f.distinct,
183                filter: f.filter,
184                inferred_type: None,
185            }))),
186
187            // Generic function transformations
188            Expression::Function(f) => self.transform_function(*f),
189
190            // Pass through everything else
191            _ => Ok(expr),
192        }
193    }
194}
195
196#[cfg(feature = "transpile")]
197impl VerticaDialect {
198    fn transform_function(&self, f: Function) -> Result<Expression> {
199        let name_upper = f.name.to_uppercase();
200        match name_upper.as_str() {
201            // IFNULL / ISNULL -> COALESCE
202            "IFNULL" | "ISNULL" if f.args.len() == 2 => {
203                Ok(Expression::Coalesce(Box::new(VarArgFunc {
204                    original_name: None,
205                    expressions: f.args,
206                    inferred_type: None,
207                })))
208            }
209
210            // SYSDATE is a synonym for GETDATE (statement-start timestamp)
211            "SYSDATE" if f.args.is_empty() => Ok(Expression::Function(Box::new(Function::new(
212                "GETDATE".to_string(),
213                vec![],
214            )))),
215
216            // TIMESTAMPDIFF(unit, a, b) is a synonym for DATEDIFF(unit, a, b)
217            "TIMESTAMPDIFF" if f.args.len() == 3 => {
218                let mut args = f.args;
219                upper_unit(&mut args[0]);
220                Ok(Expression::Function(Box::new(Function::new(
221                    "DATEDIFF".to_string(),
222                    args,
223                ))))
224            }
225
226            // APPROX_COUNT_DISTINCT -> APPROXIMATE_COUNT_DISTINCT
227            "APPROX_COUNT_DISTINCT" if !f.args.is_empty() => Ok(Expression::Function(Box::new(
228                Function::new("APPROXIMATE_COUNT_DISTINCT".to_string(), f.args),
229            ))),
230
231            // DATEADD(unit, n, ts) -> TIMESTAMPADD(unit, n, ts)
232            "DATEADD" | "DATE_ADD" if f.args.len() == 3 => {
233                let mut args = f.args;
234                let ts = args.pop().unwrap();
235                let n = args.pop().unwrap();
236                let unit = args.pop().unwrap();
237                let unit = match unit {
238                    Expression::Literal(lit) => match *lit {
239                        Literal::String(s) => {
240                            Expression::Identifier(Identifier::new(s.to_ascii_uppercase()))
241                        }
242                        other => Expression::Literal(Box::new(other)),
243                    },
244                    other => other,
245                };
246                Ok(Expression::Function(Box::new(Function::new(
247                    "TIMESTAMPADD".to_string(),
248                    vec![unit, n, ts],
249                ))))
250            }
251
252            // TIMESTAMPADD(unit, n, ts): normalize the unit keyword to upper case
253            "TIMESTAMPADD" if f.args.len() == 3 => {
254                let mut f = f;
255                upper_unit(&mut f.args[0]);
256                Ok(Expression::Function(Box::new(f)))
257            }
258
259            // CHARINDEX(substr, str[, start]) -> INSTR(str, substr[, start])
260            "CHARINDEX" if f.args.len() >= 2 => {
261                let mut args = f.args;
262                let substr = args.remove(0);
263                let string = args.remove(0);
264                let mut new_args = vec![string, substr];
265                new_args.extend(args);
266                Ok(Expression::Function(Box::new(Function::new(
267                    "INSTR".to_string(),
268                    new_args,
269                ))))
270            }
271
272            // LEN -> LENGTH
273            "LEN" if f.args.len() == 1 => Ok(Expression::Function(Box::new(Function::new(
274                "LENGTH".to_string(),
275                f.args,
276            )))),
277
278            // Pass through everything else
279            _ => Ok(Expression::Function(Box::new(f))),
280        }
281    }
282}
283
284#[cfg(feature = "transpile")]
285fn interval_unit_name(unit: &IntervalUnit) -> &'static str {
286    match unit {
287        IntervalUnit::Year => "YEAR",
288        IntervalUnit::Quarter => "QUARTER",
289        IntervalUnit::Month => "MONTH",
290        IntervalUnit::Week => "WEEK",
291        IntervalUnit::Day => "DAY",
292        IntervalUnit::Hour => "HOUR",
293        IntervalUnit::Minute => "MINUTE",
294        IntervalUnit::Second => "SECOND",
295        IntervalUnit::Millisecond => "MILLISECOND",
296        IntervalUnit::Microsecond => "MICROSECOND",
297        IntervalUnit::Nanosecond => "NANOSECOND",
298    }
299}
300
301#[cfg(feature = "transpile")]
302fn timestamp_add(unit: &str, amount: Expression, ts: Expression) -> Expression {
303    Expression::Function(Box::new(Function::new(
304        "TIMESTAMPADD".to_string(),
305        vec![Expression::Identifier(Identifier::new(unit)), amount, ts],
306    )))
307}
308
309/// Upper-case a bare datetime unit keyword (`day` -> `DAY`), leaving expressions alone.
310#[cfg(feature = "transpile")]
311fn upper_unit(unit: &mut Expression) {
312    match unit {
313        Expression::Identifier(id) if !id.quoted => id.name = id.name.to_ascii_uppercase(),
314        Expression::Column(col) if col.table.is_none() && !col.name.quoted => {
315            *unit = Expression::Identifier(Identifier::new(col.name.name.to_ascii_uppercase()));
316        }
317        _ => {}
318    }
319}
polyglot_sql/dialects/vertica.rs

polyglot_sql/dialects/
vertica.rs
