polyglot_sql/dialects/
redshift.rs

1//! Redshift Dialect
2//!
3//! Redshift-specific transformations based on sqlglot patterns.
4//! Redshift is based on PostgreSQL but has some differences.
5
6use super::{DialectImpl, DialectType};
7use crate::error::Result;
8use crate::expressions::{
9    AggFunc, Case, Cast, DataType, Expression, Function, Limit, RegexpFunc, VarArgFunc,
10};
11#[cfg(feature = "generate")]
12use crate::generator::GeneratorConfig;
13use crate::tokens::TokenizerConfig;
14
15/// Redshift dialect
16pub struct RedshiftDialect;
17
18impl DialectImpl for RedshiftDialect {
19    fn dialect_type(&self) -> DialectType {
20        DialectType::Redshift
21    }
22
23    fn tokenizer_config(&self) -> TokenizerConfig {
24        use crate::tokens::TokenType;
25        let mut config = TokenizerConfig::default();
26        // Redshift uses double quotes for identifiers (like PostgreSQL)
27        config.identifiers.insert('"', '"');
28        // Redshift does NOT support nested comments
29        config.nested_comments = false;
30        // MINUS is an alias for EXCEPT in Redshift
31        config
32            .keywords
33            .insert("MINUS".to_string(), TokenType::Except);
34        config
35    }
36
37    #[cfg(feature = "generate")]
38
39    fn generator_config(&self) -> GeneratorConfig {
40        use crate::generator::IdentifierQuoteStyle;
41        GeneratorConfig {
42            identifier_quote: '"',
43            identifier_quote_style: IdentifierQuoteStyle::DOUBLE_QUOTE,
44            dialect: Some(DialectType::Redshift),
45            supports_column_join_marks: true,
46            locking_reads_supported: false,
47            tz_to_with_time_zone: true,
48            single_string_interval: true,
49            ..Default::default()
50        }
51    }
52
53    #[cfg(feature = "transpile")]
54
55    fn transform_expr(&self, expr: Expression) -> Result<Expression> {
56        match expr {
57            // IFNULL -> COALESCE in Redshift
58            Expression::IfNull(f) => Ok(Expression::Coalesce(Box::new(VarArgFunc {
59                original_name: None,
60                expressions: vec![f.this, f.expression],
61                inferred_type: None,
62            }))),
63
64            // NVL is native in Redshift, but we standardize to COALESCE for consistency
65            Expression::Nvl(f) => Ok(Expression::Coalesce(Box::new(VarArgFunc {
66                original_name: None,
67                expressions: vec![f.this, f.expression],
68                inferred_type: None,
69            }))),
70
71            // TryCast -> TRY_CAST (Redshift supports TRY_CAST via CONVERT)
72            Expression::TryCast(c) => Ok(Expression::TryCast(c)),
73
74            // SafeCast -> TRY_CAST in Redshift
75            Expression::SafeCast(c) => Ok(Expression::TryCast(c)),
76
77            // ILIKE is native in Redshift (inherited from PostgreSQL)
78            Expression::ILike(op) => Ok(Expression::ILike(op)),
79
80            // CountIf -> SUM(CASE WHEN condition THEN 1 ELSE 0 END)
81            Expression::CountIf(f) => {
82                let case_expr = Expression::Case(Box::new(Case {
83                    operand: None,
84                    whens: vec![(f.this.clone(), Expression::number(1))],
85                    else_: Some(Expression::number(0)),
86                    comments: Vec::new(),
87                    inferred_type: None,
88                }));
89                Ok(Expression::Sum(Box::new(AggFunc {
90                    ignore_nulls: None,
91                    having_max: None,
92                    this: case_expr,
93                    distinct: f.distinct,
94                    filter: f.filter,
95                    order_by: Vec::new(),
96                    name: None,
97                    limit: None,
98                    inferred_type: None,
99                })))
100            }
101
102            // EXPLODE is not supported in Redshift
103            Expression::Explode(_) => Ok(expr),
104
105            // ExplodeOuter is not supported in Redshift
106            Expression::ExplodeOuter(_) => Ok(expr),
107
108            // UNNEST is supported in Redshift (but limited)
109            Expression::Unnest(_) => Ok(expr),
110
111            // RAND -> RANDOM in Redshift (like PostgreSQL)
112            Expression::Rand(r) => {
113                let _ = r.seed;
114                Ok(Expression::Random(crate::expressions::Random))
115            }
116
117            // DateAdd -> DATEADD(unit, count, date) in Redshift
118            Expression::DateAdd(f) => {
119                let unit_str = match f.unit {
120                    crate::expressions::IntervalUnit::Year => "YEAR",
121                    crate::expressions::IntervalUnit::Quarter => "QUARTER",
122                    crate::expressions::IntervalUnit::Month => "MONTH",
123                    crate::expressions::IntervalUnit::Week => "WEEK",
124                    crate::expressions::IntervalUnit::Day => "DAY",
125                    crate::expressions::IntervalUnit::Hour => "HOUR",
126                    crate::expressions::IntervalUnit::Minute => "MINUTE",
127                    crate::expressions::IntervalUnit::Second => "SECOND",
128                    crate::expressions::IntervalUnit::Millisecond => "MILLISECOND",
129                    crate::expressions::IntervalUnit::Microsecond => "MICROSECOND",
130                    crate::expressions::IntervalUnit::Nanosecond => "NANOSECOND",
131                };
132                let unit = Expression::Identifier(crate::expressions::Identifier {
133                    name: unit_str.to_string(),
134                    quoted: false,
135                    trailing_comments: Vec::new(),
136                    span: None,
137                });
138                Ok(Expression::Function(Box::new(Function::new(
139                    "DATEADD".to_string(),
140                    vec![unit, f.interval, f.this],
141                ))))
142            }
143
144            // Generic function transformations
145            Expression::Function(f) => self.transform_function(*f),
146
147            // Generic aggregate function transformations
148            Expression::AggregateFunction(f) => self.transform_aggregate_function(f),
149
150            // Cast transformations
151            Expression::Cast(c) => self.transform_cast(*c),
152
153            // CONVERT -> CAST in Redshift
154            Expression::Convert(c) => Ok(Expression::Cast(Box::new(Cast {
155                this: c.this,
156                to: c.to,
157                trailing_comments: Vec::new(),
158                double_colon_syntax: false,
159                format: None,
160                default: None,
161                inferred_type: None,
162            }))),
163
164            // SELECT TOP n -> SELECT ... LIMIT n in Redshift (PostgreSQL-style)
165            Expression::Select(mut select) => {
166                if let Some(top) = select.top.take() {
167                    // Only convert simple TOP (not TOP PERCENT or WITH TIES)
168                    if !top.percent && !top.with_ties {
169                        // Convert TOP to LIMIT
170                        select.limit = Some(Limit {
171                            this: top.this,
172                            percent: false,
173                            comments: Vec::new(),
174                        });
175                    } else {
176                        // Restore TOP if it has PERCENT or WITH TIES (not supported as LIMIT)
177                        select.top = Some(top);
178                    }
179                }
180                Ok(Expression::Select(select))
181            }
182
183            // Pass through everything else
184            _ => Ok(expr),
185        }
186    }
187}
188
189#[cfg(feature = "transpile")]
190impl RedshiftDialect {
191    fn transform_function(&self, f: Function) -> Result<Expression> {
192        let name_upper = f.name.to_uppercase();
193        match name_upper.as_str() {
194            // IFNULL -> COALESCE
195            "IFNULL" if f.args.len() == 2 => Ok(Expression::Coalesce(Box::new(VarArgFunc {
196                original_name: None,
197                expressions: f.args,
198                inferred_type: None,
199            }))),
200
201            // NVL -> COALESCE (supports 2+ args)
202            "NVL" if f.args.len() >= 2 => Ok(Expression::Coalesce(Box::new(VarArgFunc {
203                original_name: None,
204                expressions: f.args,
205                inferred_type: None,
206            }))),
207
208            // ISNULL -> COALESCE
209            "ISNULL" if f.args.len() == 2 => Ok(Expression::Coalesce(Box::new(VarArgFunc {
210                original_name: None,
211                expressions: f.args,
212                inferred_type: None,
213            }))),
214
215            // GETDATE is native to Redshift
216            "GETDATE" => Ok(Expression::Function(Box::new(Function::new(
217                "GETDATE".to_string(),
218                vec![],
219            )))),
220
221            // NOW -> GETDATE in Redshift
222            "NOW" => Ok(Expression::Function(Box::new(Function::new(
223                "GETDATE".to_string(),
224                vec![],
225            )))),
226
227            // RAND -> RANDOM in Redshift
228            "RAND" => Ok(Expression::Random(crate::expressions::Random)),
229
230            // GROUP_CONCAT -> LISTAGG in Redshift
231            "GROUP_CONCAT" if !f.args.is_empty() => Ok(Expression::Function(Box::new(
232                Function::new("LISTAGG".to_string(), f.args),
233            ))),
234
235            // STRING_AGG -> LISTAGG in Redshift
236            "STRING_AGG" if !f.args.is_empty() => Ok(Expression::Function(Box::new(
237                Function::new("LISTAGG".to_string(), f.args),
238            ))),
239
240            // LISTAGG is native in Redshift
241            "LISTAGG" => Ok(Expression::Function(Box::new(f))),
242
243            // SUBSTR -> SUBSTRING
244            "SUBSTR" => Ok(Expression::Function(Box::new(Function::new(
245                "SUBSTRING".to_string(),
246                f.args,
247            )))),
248
249            // LEN is native in Redshift
250            "LEN" => Ok(Expression::Function(Box::new(f))),
251
252            // LENGTH -> LEN in Redshift
253            "LENGTH" if f.args.len() == 1 => Ok(Expression::Function(Box::new(Function::new(
254                "LEN".to_string(),
255                f.args,
256            )))),
257
258            // CHARINDEX is native in Redshift
259            "CHARINDEX" => Ok(Expression::Function(Box::new(f))),
260
261            // POSITION -> CHARINDEX in Redshift (with swapped args)
262            "POSITION" if f.args.len() == 2 => {
263                let mut args = f.args;
264                let substring = args.remove(0);
265                let string = args.remove(0);
266                // CHARINDEX(substring, string)
267                Ok(Expression::Function(Box::new(Function::new(
268                    "CHARINDEX".to_string(),
269                    vec![substring, string],
270                ))))
271            }
272
273            // STRPOS -> CHARINDEX in Redshift
274            "STRPOS" if f.args.len() == 2 => {
275                let args = f.args;
276                // STRPOS(string, substring) -> CHARINDEX(substring, string)
277                let string = args[0].clone();
278                let substring = args[1].clone();
279                Ok(Expression::Function(Box::new(Function::new(
280                    "CHARINDEX".to_string(),
281                    vec![substring, string],
282                ))))
283            }
284
285            // INSTR -> CHARINDEX in Redshift
286            "INSTR" if f.args.len() >= 2 => {
287                let mut args = f.args;
288                let string = args.remove(0);
289                let substring = args.remove(0);
290                // INSTR(string, substring) -> CHARINDEX(substring, string)
291                Ok(Expression::Function(Box::new(Function::new(
292                    "CHARINDEX".to_string(),
293                    vec![substring, string],
294                ))))
295            }
296
297            // LOCATE -> CHARINDEX in Redshift
298            "LOCATE" if f.args.len() >= 2 => {
299                let mut args = f.args;
300                let substring = args.remove(0);
301                let string = args.remove(0);
302                // LOCATE(substring, string) -> CHARINDEX(substring, string)
303                Ok(Expression::Function(Box::new(Function::new(
304                    "CHARINDEX".to_string(),
305                    vec![substring, string],
306                ))))
307            }
308
309            // ARRAY_LENGTH -> ARRAY_UPPER / custom in Redshift
310            // Redshift doesn't have ARRAY_LENGTH, arrays are limited
311            "ARRAY_LENGTH" => Ok(Expression::Function(Box::new(f))),
312
313            // SIZE -> not directly supported
314            "SIZE" => Ok(Expression::Function(Box::new(f))),
315
316            // TO_DATE -> TO_DATE (native in Redshift)
317            "TO_DATE" => Ok(Expression::Function(Box::new(f))),
318
319            // TO_TIMESTAMP -> TO_TIMESTAMP (native in Redshift)
320            "TO_TIMESTAMP" => Ok(Expression::Function(Box::new(f))),
321
322            // DATE_FORMAT -> TO_CHAR in Redshift
323            "DATE_FORMAT" if f.args.len() >= 2 => Ok(Expression::Function(Box::new(
324                Function::new("TO_CHAR".to_string(), f.args),
325            ))),
326
327            // strftime -> TO_CHAR in Redshift
328            "STRFTIME" if f.args.len() >= 2 => {
329                let mut args = f.args;
330                let format = args.remove(0);
331                let date = args.remove(0);
332                Ok(Expression::Function(Box::new(Function::new(
333                    "TO_CHAR".to_string(),
334                    vec![date, format],
335                ))))
336            }
337
338            // TO_CHAR is native in Redshift
339            "TO_CHAR" => Ok(Expression::Function(Box::new(f))),
340
341            // LEVENSHTEIN -> not directly supported
342            "LEVENSHTEIN" => Ok(Expression::Function(Box::new(f))),
343
344            // JSON_EXTRACT -> JSON_EXTRACT_PATH_TEXT in Redshift
345            "JSON_EXTRACT" if f.args.len() >= 2 => Ok(Expression::Function(Box::new(
346                Function::new("JSON_EXTRACT_PATH_TEXT".to_string(), f.args),
347            ))),
348
349            // JSON_EXTRACT_SCALAR -> JSON_EXTRACT_PATH_TEXT in Redshift
350            "JSON_EXTRACT_SCALAR" if f.args.len() >= 2 => Ok(Expression::Function(Box::new(
351                Function::new("JSON_EXTRACT_PATH_TEXT".to_string(), f.args),
352            ))),
353
354            // GET_JSON_OBJECT -> JSON_EXTRACT_PATH_TEXT in Redshift
355            "GET_JSON_OBJECT" if f.args.len() == 2 => Ok(Expression::Function(Box::new(
356                Function::new("JSON_EXTRACT_PATH_TEXT".to_string(), f.args),
357            ))),
358
359            // COLLECT_LIST -> not directly supported (limited array support)
360            "COLLECT_LIST" => Ok(Expression::Function(Box::new(f))),
361
362            // COLLECT_SET -> not directly supported
363            "COLLECT_SET" => Ok(Expression::Function(Box::new(f))),
364
365            // RLIKE -> REGEXP_MATCHES in Redshift (or SIMILAR TO)
366            "RLIKE" if f.args.len() == 2 => {
367                // Redshift uses ~ for regex matching
368                let mut args = f.args;
369                let string = args.remove(0);
370                let pattern = args.remove(0);
371                Ok(Expression::RegexpLike(Box::new(RegexpFunc {
372                    options: None,
373
374                    this: string,
375                    pattern,
376                    flags: None,
377                })))
378            }
379
380            // REGEXP -> RegexpLike
381            "REGEXP" if f.args.len() == 2 => {
382                let mut args = f.args;
383                let string = args.remove(0);
384                let pattern = args.remove(0);
385                Ok(Expression::RegexpLike(Box::new(RegexpFunc {
386                    options: None,
387
388                    this: string,
389                    pattern,
390                    flags: None,
391                })))
392            }
393
394            // REGEXP_LIKE -> native in Redshift (PostgreSQL-compatible)
395            "REGEXP_LIKE" => Ok(Expression::Function(Box::new(f))),
396
397            // ADD_MONTHS -> DATEADD in Redshift
398            "ADD_MONTHS" if f.args.len() == 2 => {
399                let mut args = f.args;
400                let date = args.remove(0);
401                let months = args.remove(0);
402                // DATEADD(month, num_months, date)
403                Ok(Expression::Function(Box::new(Function::new(
404                    "DATEADD".to_string(),
405                    vec![Expression::identifier("month"), months, date],
406                ))))
407            }
408
409            // DATEDIFF is native in Redshift
410            "DATEDIFF" => Ok(Expression::Function(Box::new(f))),
411
412            // DATE_DIFF -> DATEDIFF in Redshift
413            "DATE_DIFF" => Ok(Expression::Function(Box::new(Function::new(
414                "DATEDIFF".to_string(),
415                f.args,
416            )))),
417
418            // DATEADD is native in Redshift
419            "DATEADD" => Ok(Expression::Function(Box::new(f))),
420
421            // DATE_ADD -> DATEADD in Redshift
422            "DATE_ADD" => Ok(Expression::Function(Box::new(Function::new(
423                "DATEADD".to_string(),
424                f.args,
425            )))),
426
427            // SPLIT_TO_ARRAY is native in Redshift
428            "SPLIT_TO_ARRAY" => Ok(Expression::Function(Box::new(f))),
429
430            // STRING_TO_ARRAY -> SPLIT_TO_ARRAY in Redshift
431            "STRING_TO_ARRAY" if f.args.len() >= 1 => Ok(Expression::Function(Box::new(
432                Function::new("SPLIT_TO_ARRAY".to_string(), f.args),
433            ))),
434
435            // SPLIT -> SPLIT_TO_ARRAY in Redshift
436            "SPLIT" if f.args.len() >= 1 => Ok(Expression::Function(Box::new(Function::new(
437                "SPLIT_TO_ARRAY".to_string(),
438                f.args,
439            )))),
440
441            // STRTOL is native in Redshift (string to long/base conversion)
442            "STRTOL" => Ok(Expression::Function(Box::new(f))),
443
444            // FROM_BASE -> STRTOL in Redshift
445            "FROM_BASE" if f.args.len() == 2 => Ok(Expression::Function(Box::new(Function::new(
446                "STRTOL".to_string(),
447                f.args,
448            )))),
449
450            // CONVERT_TIMEZONE(target_tz, timestamp) -> CONVERT_TIMEZONE('UTC', target_tz, timestamp)
451            "CONVERT_TIMEZONE" if f.args.len() == 2 => {
452                let mut new_args = vec![Expression::string("UTC")];
453                new_args.extend(f.args);
454                Ok(Expression::Function(Box::new(Function::new(
455                    "CONVERT_TIMEZONE".to_string(),
456                    new_args,
457                ))))
458            }
459            // 3-arg form stays as-is
460            "CONVERT_TIMEZONE" => Ok(Expression::Function(Box::new(f))),
461
462            // CONVERT(type, expr) -> CAST(expr AS type)
463            "CONVERT" if f.args.len() == 2 => {
464                let type_expr = &f.args[0];
465                let value_expr = f.args[1].clone();
466
467                // Extract type name from the first argument (it's likely a Column or Identifier)
468                let type_name = match type_expr {
469                    Expression::Column(c) => c.name.name.clone(),
470                    Expression::Identifier(i) => i.name.clone(),
471                    _ => return Ok(Expression::Function(Box::new(f))), // Can't handle, pass through
472                };
473
474                // Map type name to DataType
475                let data_type = match type_name.to_uppercase().as_str() {
476                    "INT" | "INTEGER" => DataType::Int {
477                        length: None,
478                        integer_spelling: false,
479                    },
480                    "BIGINT" => DataType::BigInt { length: None },
481                    "SMALLINT" => DataType::SmallInt { length: None },
482                    "TINYINT" => DataType::TinyInt { length: None },
483                    "VARCHAR" => DataType::VarChar {
484                        length: None,
485                        parenthesized_length: false,
486                    },
487                    "CHAR" => DataType::Char { length: None },
488                    "FLOAT" | "REAL" => DataType::Float {
489                        precision: None,
490                        scale: None,
491                        real_spelling: false,
492                    },
493                    "DOUBLE" => DataType::Double {
494                        precision: None,
495                        scale: None,
496                    },
497                    "BOOLEAN" | "BOOL" => DataType::Boolean,
498                    "DATE" => DataType::Date,
499                    "TIMESTAMP" => DataType::Timestamp {
500                        precision: None,
501                        timezone: false,
502                    },
503                    "TEXT" => DataType::Text,
504                    "DECIMAL" | "NUMERIC" => DataType::Decimal {
505                        precision: None,
506                        scale: None,
507                    },
508                    _ => return Ok(Expression::Function(Box::new(f))), // Unknown type, pass through
509                };
510
511                Ok(Expression::Cast(Box::new(Cast {
512                    this: value_expr,
513                    to: data_type,
514                    trailing_comments: Vec::new(),
515                    double_colon_syntax: false,
516                    format: None,
517                    default: None,
518                    inferred_type: None,
519                })))
520            }
521
522            // Pass through everything else
523            _ => Ok(Expression::Function(Box::new(f))),
524        }
525    }
526
527    fn transform_aggregate_function(
528        &self,
529        f: Box<crate::expressions::AggregateFunction>,
530    ) -> Result<Expression> {
531        let name_upper = f.name.to_uppercase();
532        match name_upper.as_str() {
533            // COUNT_IF -> SUM(CASE WHEN...)
534            "COUNT_IF" if !f.args.is_empty() => {
535                let condition = f.args.into_iter().next().unwrap();
536                let case_expr = Expression::Case(Box::new(Case {
537                    operand: None,
538                    whens: vec![(condition, Expression::number(1))],
539                    else_: Some(Expression::number(0)),
540                    comments: Vec::new(),
541                    inferred_type: None,
542                }));
543                Ok(Expression::Sum(Box::new(AggFunc {
544                    ignore_nulls: None,
545                    having_max: None,
546                    this: case_expr,
547                    distinct: f.distinct,
548                    filter: f.filter,
549                    order_by: Vec::new(),
550                    name: None,
551                    limit: None,
552                    inferred_type: None,
553                })))
554            }
555
556            // ANY_VALUE is native in Redshift
557            "ANY_VALUE" => Ok(Expression::AggregateFunction(f)),
558
559            // GROUP_CONCAT -> LISTAGG in Redshift
560            "GROUP_CONCAT" if !f.args.is_empty() => Ok(Expression::Function(Box::new(
561                Function::new("LISTAGG".to_string(), f.args),
562            ))),
563
564            // STRING_AGG -> LISTAGG in Redshift
565            "STRING_AGG" if !f.args.is_empty() => Ok(Expression::Function(Box::new(
566                Function::new("LISTAGG".to_string(), f.args),
567            ))),
568
569            // LISTAGG is native in Redshift
570            "LISTAGG" => Ok(Expression::AggregateFunction(f)),
571
572            // STDDEV is native in Redshift
573            "STDDEV" => Ok(Expression::AggregateFunction(f)),
574
575            // VARIANCE is native in Redshift
576            "VARIANCE" => Ok(Expression::AggregateFunction(f)),
577
578            // MEDIAN is native in Redshift
579            "MEDIAN" => Ok(Expression::AggregateFunction(f)),
580
581            // Pass through everything else
582            _ => Ok(Expression::AggregateFunction(f)),
583        }
584    }
585
586    fn transform_cast(&self, c: Cast) -> Result<Expression> {
587        // Redshift type mappings are handled in the generator
588        Ok(Expression::Cast(Box::new(c)))
589    }
590}
polyglot_sql/dialects/redshift.rs

polyglot_sql/dialects/
redshift.rs
