polyglot_sql/dialects/
presto.rs

1//! Presto Dialect
2//!
3//! Presto-specific transformations based on sqlglot patterns.
4//! Presto is the base for Trino dialect.
5
6use super::{DialectImpl, DialectType};
7use crate::error::Result;
8use crate::expressions::{
9    AggFunc, AggregateFunction, BinaryOp, Case, Cast, Column, DataType, Expression, Function,
10    JsonExtractFunc, LikeOp, Literal, UnaryFunc, VarArgFunc,
11};
12#[cfg(feature = "generate")]
13use crate::generator::GeneratorConfig;
14use crate::tokens::TokenizerConfig;
15
16/// Presto dialect
17pub struct PrestoDialect;
18
19impl DialectImpl for PrestoDialect {
20    fn dialect_type(&self) -> DialectType {
21        DialectType::Presto
22    }
23
24    fn tokenizer_config(&self) -> TokenizerConfig {
25        let mut config = TokenizerConfig::default();
26        // Presto uses double quotes for identifiers
27        config.identifiers.insert('"', '"');
28        // Presto does NOT support nested comments
29        config.nested_comments = false;
30        // Presto does NOT support QUALIFY - it's a valid identifier
31        // (unlike Snowflake, BigQuery, DuckDB which have QUALIFY clause)
32        config.keywords.remove("QUALIFY");
33        config
34    }
35
36    #[cfg(feature = "generate")]
37
38    fn generator_config(&self) -> GeneratorConfig {
39        use crate::generator::IdentifierQuoteStyle;
40        GeneratorConfig {
41            identifier_quote: '"',
42            identifier_quote_style: IdentifierQuoteStyle::DOUBLE_QUOTE,
43            dialect: Some(DialectType::Presto),
44            limit_only_literals: true,
45            tz_to_with_time_zone: true,
46            ..Default::default()
47        }
48    }
49
50    #[cfg(feature = "transpile")]
51
52    fn transform_expr(&self, expr: Expression) -> Result<Expression> {
53        match expr {
54            // IFNULL -> COALESCE in Presto
55            Expression::IfNull(f) => Ok(Expression::Coalesce(Box::new(VarArgFunc {
56                original_name: None,
57                expressions: vec![f.this, f.expression],
58                inferred_type: None,
59            }))),
60
61            // NVL -> COALESCE in Presto
62            Expression::Nvl(f) => Ok(Expression::Coalesce(Box::new(VarArgFunc {
63                original_name: None,
64                expressions: vec![f.this, f.expression],
65                inferred_type: None,
66            }))),
67
68            // TryCast stays as TryCast (Presto supports TRY_CAST)
69            Expression::TryCast(c) => Ok(Expression::TryCast(c)),
70
71            // SafeCast -> TRY_CAST in Presto
72            Expression::SafeCast(c) => Ok(Expression::TryCast(c)),
73
74            // ILike -> LOWER() LIKE LOWER() (Presto doesn't support ILIKE)
75            Expression::ILike(op) => {
76                let lower_left = Expression::Lower(Box::new(UnaryFunc::new(op.left.clone())));
77                let lower_right = Expression::Lower(Box::new(UnaryFunc::new(op.right.clone())));
78                Ok(Expression::Like(Box::new(LikeOp {
79                    left: lower_left,
80                    right: lower_right,
81                    escape: op.escape,
82                    quantifier: op.quantifier.clone(),
83                    inferred_type: None,
84                })))
85            }
86
87            // CountIf is native in Presto (keep as-is)
88            Expression::CountIf(f) => Ok(Expression::CountIf(f)),
89
90            // EXPLODE -> UNNEST in Presto
91            Expression::Explode(f) => Ok(Expression::Unnest(Box::new(
92                crate::expressions::UnnestFunc {
93                    this: f.this,
94                    expressions: Vec::new(),
95                    with_ordinality: false,
96                    alias: None,
97                    offset_alias: None,
98                    inferred_type: None,
99                },
100            ))),
101
102            // ExplodeOuter -> UNNEST in Presto
103            Expression::ExplodeOuter(f) => Ok(Expression::Unnest(Box::new(
104                crate::expressions::UnnestFunc {
105                    this: f.this,
106                    expressions: Vec::new(),
107                    with_ordinality: false,
108                    alias: None,
109                    offset_alias: None,
110                    inferred_type: None,
111                },
112            ))),
113
114            // StringAgg -> ARRAY_JOIN(ARRAY_AGG()) in Presto
115            Expression::StringAgg(f) => {
116                let array_agg = Expression::Function(Box::new(Function::new(
117                    "ARRAY_AGG".to_string(),
118                    vec![f.this.clone()],
119                )));
120                let mut join_args = vec![array_agg];
121                if let Some(sep) = f.separator {
122                    join_args.push(sep);
123                }
124                Ok(Expression::Function(Box::new(Function::new(
125                    "ARRAY_JOIN".to_string(),
126                    join_args,
127                ))))
128            }
129
130            // GroupConcat -> ARRAY_JOIN(ARRAY_AGG()) in Presto
131            Expression::GroupConcat(f) => {
132                let array_agg = Expression::Function(Box::new(Function::new(
133                    "ARRAY_AGG".to_string(),
134                    vec![f.this.clone()],
135                )));
136                let mut join_args = vec![array_agg];
137                if let Some(sep) = f.separator {
138                    join_args.push(sep);
139                }
140                Ok(Expression::Function(Box::new(Function::new(
141                    "ARRAY_JOIN".to_string(),
142                    join_args,
143                ))))
144            }
145
146            // ListAgg -> ARRAY_JOIN(ARRAY_AGG()) in Presto
147            Expression::ListAgg(f) => {
148                let array_agg = Expression::Function(Box::new(Function::new(
149                    "ARRAY_AGG".to_string(),
150                    vec![f.this.clone()],
151                )));
152                let mut join_args = vec![array_agg];
153                if let Some(sep) = f.separator {
154                    join_args.push(sep);
155                }
156                Ok(Expression::Function(Box::new(Function::new(
157                    "ARRAY_JOIN".to_string(),
158                    join_args,
159                ))))
160            }
161
162            // ParseJson: handled by generator (outputs JSON_PARSE for Presto)
163
164            // JSONExtract (variant_extract/colon accessor) -> JSON_EXTRACT in Presto
165            Expression::JSONExtract(e) if e.variant_extract.is_some() => {
166                let path = match *e.expression {
167                    Expression::Literal(lit) if matches!(lit.as_ref(), Literal::String(_)) => {
168                        let Literal::String(s) = lit.as_ref() else {
169                            unreachable!()
170                        };
171                        let normalized = if s.starts_with('$') {
172                            s.clone()
173                        } else if s.starts_with('[') {
174                            format!("${}", s)
175                        } else {
176                            format!("$.{}", s)
177                        };
178                        Expression::Literal(Box::new(Literal::String(normalized)))
179                    }
180                    other => other,
181                };
182                Ok(Expression::JsonExtract(Box::new(JsonExtractFunc {
183                    this: *e.this,
184                    path,
185                    returning: None,
186                    arrow_syntax: false,
187                    hash_arrow_syntax: false,
188                    wrapper_option: None,
189                    quotes_option: None,
190                    on_scalar_string: false,
191                    on_error: None,
192                })))
193            }
194
195            // Generic function transformations
196            Expression::Function(f) => self.transform_function(*f),
197
198            // Generic aggregate function transformations
199            Expression::AggregateFunction(f) => self.transform_aggregate_function(f),
200
201            // Cast transformations
202            Expression::Cast(c) => self.transform_cast(*c),
203
204            // Div: Presto has TYPED_DIVISION - wrap left operand in CAST(AS DOUBLE)
205            // to ensure float division (only when left isn't already a float cast)
206            Expression::Div(mut op) => {
207                if !Self::is_float_cast(&op.left) {
208                    op.left = Expression::Cast(Box::new(crate::expressions::Cast {
209                        this: op.left,
210                        to: DataType::Double {
211                            precision: None,
212                            scale: None,
213                        },
214                        trailing_comments: Vec::new(),
215                        double_colon_syntax: false,
216                        format: None,
217                        default: None,
218                        inferred_type: None,
219                    }));
220                }
221                Ok(Expression::Div(op))
222            }
223
224            // IntDiv -> CAST(CAST(x AS DOUBLE) / y AS INTEGER) in Presto
225            Expression::IntDiv(f) => {
226                let cast_x = Expression::Cast(Box::new(Cast {
227                    this: f.this,
228                    to: crate::expressions::DataType::Double {
229                        precision: None,
230                        scale: None,
231                    },
232                    trailing_comments: Vec::new(),
233                    double_colon_syntax: false,
234                    format: None,
235                    default: None,
236                    inferred_type: None,
237                }));
238                let div_expr = Expression::Div(Box::new(BinaryOp::new(cast_x, f.expression)));
239                Ok(Expression::Cast(Box::new(Cast {
240                    this: div_expr,
241                    to: crate::expressions::DataType::Int {
242                        length: None,
243                        integer_spelling: true,
244                    },
245                    trailing_comments: Vec::new(),
246                    double_colon_syntax: false,
247                    format: None,
248                    default: None,
249                    inferred_type: None,
250                })))
251            }
252
253            // DELETE: Strip table alias and unqualify columns (Presto doesn't support DELETE aliases)
254            Expression::Delete(mut d) => {
255                if d.alias.is_some() {
256                    d.alias = None;
257                    d.alias_explicit_as = false;
258                    // Unqualify all columns in the WHERE clause
259                    if let Some(ref mut where_clause) = d.where_clause {
260                        where_clause.this = Self::unqualify_columns(where_clause.this.clone());
261                    }
262                }
263                Ok(Expression::Delete(d))
264            }
265
266            // Pass through everything else
267            _ => Ok(expr),
268        }
269    }
270}
271
272#[cfg(feature = "transpile")]
273impl PrestoDialect {
274    /// Recursively unqualify columns - remove table qualifiers from Column references
275    fn unqualify_columns(expr: Expression) -> Expression {
276        match expr {
277            Expression::Column(c) => {
278                if c.table.is_some() {
279                    Expression::boxed_column(Column {
280                        name: c.name,
281                        table: None,
282                        join_mark: c.join_mark,
283                        trailing_comments: c.trailing_comments,
284                        span: None,
285                        inferred_type: None,
286                    })
287                } else {
288                    Expression::Column(c)
289                }
290            }
291            // DotAccess: db.t2.c -> c (strip all qualifiers, keep only the final field name)
292            Expression::Dot(d) => Expression::boxed_column(Column {
293                name: d.field,
294                table: None,
295                join_mark: false,
296                trailing_comments: Vec::new(),
297                span: None,
298                inferred_type: None,
299            }),
300            // Recursively walk common binary expression types
301            Expression::And(mut op) => {
302                op.left = Self::unqualify_columns(op.left);
303                op.right = Self::unqualify_columns(op.right);
304                Expression::And(op)
305            }
306            Expression::Or(mut op) => {
307                op.left = Self::unqualify_columns(op.left);
308                op.right = Self::unqualify_columns(op.right);
309                Expression::Or(op)
310            }
311            Expression::Eq(mut op) => {
312                op.left = Self::unqualify_columns(op.left);
313                op.right = Self::unqualify_columns(op.right);
314                Expression::Eq(op)
315            }
316            Expression::Neq(mut op) => {
317                op.left = Self::unqualify_columns(op.left);
318                op.right = Self::unqualify_columns(op.right);
319                Expression::Neq(op)
320            }
321            Expression::Gt(mut op) => {
322                op.left = Self::unqualify_columns(op.left);
323                op.right = Self::unqualify_columns(op.right);
324                Expression::Gt(op)
325            }
326            Expression::Lt(mut op) => {
327                op.left = Self::unqualify_columns(op.left);
328                op.right = Self::unqualify_columns(op.right);
329                Expression::Lt(op)
330            }
331            Expression::Gte(mut op) => {
332                op.left = Self::unqualify_columns(op.left);
333                op.right = Self::unqualify_columns(op.right);
334                Expression::Gte(op)
335            }
336            Expression::Lte(mut op) => {
337                op.left = Self::unqualify_columns(op.left);
338                op.right = Self::unqualify_columns(op.right);
339                Expression::Lte(op)
340            }
341            // Unary operators
342            Expression::Not(mut e) => {
343                e.this = Self::unqualify_columns(e.this);
344                Expression::Not(e)
345            }
346            // Predicates
347            Expression::In(mut i) => {
348                i.this = Self::unqualify_columns(i.this);
349                i.expressions = i
350                    .expressions
351                    .into_iter()
352                    .map(Self::unqualify_columns)
353                    .collect();
354                // Also recurse into subquery if present
355                if let Some(q) = i.query {
356                    i.query = Some(Self::unqualify_columns(q));
357                }
358                Expression::In(i)
359            }
360            Expression::IsNull(mut f) => {
361                f.this = Self::unqualify_columns(f.this);
362                Expression::IsNull(f)
363            }
364            Expression::Paren(mut p) => {
365                p.this = Self::unqualify_columns(p.this);
366                Expression::Paren(p)
367            }
368            Expression::Function(mut f) => {
369                f.args = f.args.into_iter().map(Self::unqualify_columns).collect();
370                Expression::Function(f)
371            }
372            // For subqueries (SELECT statements inside IN, etc), also unqualify
373            Expression::Select(mut s) => {
374                s.expressions = s
375                    .expressions
376                    .into_iter()
377                    .map(Self::unqualify_columns)
378                    .collect();
379                if let Some(ref mut w) = s.where_clause {
380                    w.this = Self::unqualify_columns(w.this.clone());
381                }
382                Expression::Select(s)
383            }
384            Expression::Subquery(mut sq) => {
385                sq.this = Self::unqualify_columns(sq.this);
386                Expression::Subquery(sq)
387            }
388            Expression::Alias(mut a) => {
389                a.this = Self::unqualify_columns(a.this);
390                Expression::Alias(a)
391            }
392            // Pass through other expressions unchanged
393            other => other,
394        }
395    }
396
397    /// Check if an expression is already a CAST to a float type
398    fn is_float_cast(expr: &Expression) -> bool {
399        if let Expression::Cast(cast) = expr {
400            matches!(&cast.to, DataType::Double { .. } | DataType::Float { .. })
401        } else {
402            false
403        }
404    }
405
406    /// Convert Oracle/PostgreSQL-style date format to Presto's C-style format
407    /// Oracle: dd, hh, hh24, mi, mm, ss, yyyy, yy
408    /// Presto: %d, %H, %H, %i, %m, %s, %Y, %y
409    pub fn oracle_to_presto_format(fmt: &str) -> String {
410        // Process character by character to avoid double-replacement issues
411        let chars: Vec<char> = fmt.chars().collect();
412        let mut result = String::new();
413        let mut i = 0;
414        while i < chars.len() {
415            let remaining = &fmt[i..];
416            if remaining.starts_with("yyyy") {
417                result.push_str("%Y");
418                i += 4;
419            } else if remaining.starts_with("yy") {
420                result.push_str("%y");
421                i += 2;
422            } else if remaining.starts_with("hh24") {
423                result.push_str("%H");
424                i += 4;
425            } else if remaining.starts_with("hh") {
426                result.push_str("%H");
427                i += 2;
428            } else if remaining.starts_with("mi") {
429                result.push_str("%i");
430                i += 2;
431            } else if remaining.starts_with("mm") {
432                result.push_str("%m");
433                i += 2;
434            } else if remaining.starts_with("dd") {
435                result.push_str("%d");
436                i += 2;
437            } else if remaining.starts_with("ss") {
438                result.push_str("%s");
439                i += 2;
440            } else {
441                result.push(chars[i]);
442                i += 1;
443            }
444        }
445        result
446    }
447
448    /// Convert Presto's C-style date format to Java-style format (for Hive/Spark)
449    /// Presto: %Y, %m, %d, %H, %i, %S, %s, %y, %T, %F
450    /// Java:   yyyy, MM, dd, HH, mm, ss, ss, yy, HH:mm:ss, yyyy-MM-dd
451    pub fn presto_to_java_format(fmt: &str) -> String {
452        fmt.replace("%Y", "yyyy")
453            .replace("%m", "MM")
454            .replace("%d", "dd")
455            .replace("%H", "HH")
456            .replace("%i", "mm")
457            .replace("%S", "ss")
458            .replace("%s", "ss")
459            .replace("%y", "yy")
460            .replace("%T", "HH:mm:ss")
461            .replace("%F", "yyyy-MM-dd")
462            .replace("%M", "MMMM")
463    }
464
465    /// Normalize Presto format strings (e.g., %H:%i:%S -> %T, %Y-%m-%d -> %F)
466    pub fn normalize_presto_format(fmt: &str) -> String {
467        fmt.replace("%H:%i:%S", "%T").replace("%H:%i:%s", "%T")
468    }
469
470    /// Convert Presto's C-style format to DuckDB C-style (only difference: %i -> %M for minutes)
471    pub fn presto_to_duckdb_format(fmt: &str) -> String {
472        fmt.replace("%i", "%M")
473            .replace("%s", "%S")
474            .replace("%T", "%H:%M:%S")
475    }
476
477    /// Convert Presto's C-style format to BigQuery format
478    pub fn presto_to_bigquery_format(fmt: &str) -> String {
479        // BigQuery uses %F for %Y-%m-%d, %T for %H:%M:%S
480        // BigQuery uses %M for minutes (like DuckDB), not %i
481        let result = fmt
482            .replace("%Y-%m-%d", "%F")
483            .replace("%H:%i:%S", "%T")
484            .replace("%H:%i:%s", "%T")
485            .replace("%i", "%M")
486            .replace("%s", "%S");
487        result
488    }
489
490    /// Check if a Presto format string matches the default timestamp format
491    pub fn is_default_timestamp_format(fmt: &str) -> bool {
492        let normalized = Self::normalize_presto_format(fmt);
493        normalized == "%Y-%m-%d %T"
494            || normalized == "%Y-%m-%d %H:%i:%S"
495            || fmt == "%Y-%m-%d %H:%i:%S"
496            || fmt == "%Y-%m-%d %T"
497    }
498
499    /// Check if a Presto format string matches the default date format
500    pub fn is_default_date_format(fmt: &str) -> bool {
501        fmt == "%Y-%m-%d" || fmt == "%F"
502    }
503
504    fn transform_function(&self, f: Function) -> Result<Expression> {
505        let name_upper = f.name.to_uppercase();
506        match name_upper.as_str() {
507            // IFNULL -> COALESCE
508            "IFNULL" if f.args.len() == 2 => Ok(Expression::Coalesce(Box::new(VarArgFunc {
509                original_name: None,
510                expressions: f.args,
511                inferred_type: None,
512            }))),
513
514            // NVL -> COALESCE
515            "NVL" if f.args.len() == 2 => Ok(Expression::Coalesce(Box::new(VarArgFunc {
516                original_name: None,
517                expressions: f.args,
518                inferred_type: None,
519            }))),
520
521            // ISNULL -> COALESCE
522            "ISNULL" if f.args.len() == 2 => Ok(Expression::Coalesce(Box::new(VarArgFunc {
523                original_name: None,
524                expressions: f.args,
525                inferred_type: None,
526            }))),
527
528            // GETDATE -> CURRENT_TIMESTAMP
529            "GETDATE" => Ok(Expression::CurrentTimestamp(
530                crate::expressions::CurrentTimestamp {
531                    precision: None,
532                    sysdate: false,
533                },
534            )),
535
536            // NOW -> CURRENT_TIMESTAMP
537            "NOW" => Ok(Expression::CurrentTimestamp(
538                crate::expressions::CurrentTimestamp {
539                    precision: None,
540                    sysdate: false,
541                },
542            )),
543
544            // RAND -> RANDOM in Presto (but it's actually RANDOM())
545            "RAND" => Ok(Expression::Function(Box::new(Function::new(
546                "RANDOM".to_string(),
547                vec![],
548            )))),
549
550            // GROUP_CONCAT -> ARRAY_JOIN(ARRAY_AGG())
551            "GROUP_CONCAT" if !f.args.is_empty() => {
552                let mut args = f.args;
553                let first = args.remove(0);
554                let separator = args.pop();
555                let array_agg = Expression::Function(Box::new(Function::new(
556                    "ARRAY_AGG".to_string(),
557                    vec![first],
558                )));
559                let mut join_args = vec![array_agg];
560                if let Some(sep) = separator {
561                    join_args.push(sep);
562                }
563                Ok(Expression::Function(Box::new(Function::new(
564                    "ARRAY_JOIN".to_string(),
565                    join_args,
566                ))))
567            }
568
569            // STRING_AGG -> ARRAY_JOIN(ARRAY_AGG())
570            "STRING_AGG" if !f.args.is_empty() => {
571                let mut args = f.args;
572                let first = args.remove(0);
573                let separator = args.pop();
574                let array_agg = Expression::Function(Box::new(Function::new(
575                    "ARRAY_AGG".to_string(),
576                    vec![first],
577                )));
578                let mut join_args = vec![array_agg];
579                if let Some(sep) = separator {
580                    join_args.push(sep);
581                }
582                Ok(Expression::Function(Box::new(Function::new(
583                    "ARRAY_JOIN".to_string(),
584                    join_args,
585                ))))
586            }
587
588            // LISTAGG -> ARRAY_JOIN(ARRAY_AGG())
589            "LISTAGG" if !f.args.is_empty() => {
590                let mut args = f.args;
591                let first = args.remove(0);
592                let separator = args.pop();
593                let array_agg = Expression::Function(Box::new(Function::new(
594                    "ARRAY_AGG".to_string(),
595                    vec![first],
596                )));
597                let mut join_args = vec![array_agg];
598                if let Some(sep) = separator {
599                    join_args.push(sep);
600                }
601                Ok(Expression::Function(Box::new(Function::new(
602                    "ARRAY_JOIN".to_string(),
603                    join_args,
604                ))))
605            }
606
607            // SUBSTR is native in Presto (keep as-is, don't convert to SUBSTRING)
608            "SUBSTR" => Ok(Expression::Function(Box::new(f))),
609
610            // LEN -> LENGTH
611            "LEN" if f.args.len() == 1 => Ok(Expression::Length(Box::new(UnaryFunc::new(
612                f.args.into_iter().next().unwrap(),
613            )))),
614
615            // CHARINDEX -> STRPOS in Presto (with swapped args)
616            "CHARINDEX" if f.args.len() >= 2 => {
617                let mut args = f.args;
618                let substring = args.remove(0);
619                let string = args.remove(0);
620                // STRPOS(string, substring) - note: argument order is reversed
621                Ok(Expression::Function(Box::new(Function::new(
622                    "STRPOS".to_string(),
623                    vec![string, substring],
624                ))))
625            }
626
627            // INSTR -> STRPOS (with same argument order)
628            "INSTR" if f.args.len() >= 2 => {
629                let args = f.args;
630                // INSTR(string, substring) -> STRPOS(string, substring)
631                Ok(Expression::Function(Box::new(Function::new(
632                    "STRPOS".to_string(),
633                    args,
634                ))))
635            }
636
637            // LOCATE -> STRPOS in Presto (with swapped args)
638            "LOCATE" if f.args.len() >= 2 => {
639                let mut args = f.args;
640                let substring = args.remove(0);
641                let string = args.remove(0);
642                // LOCATE(substring, string) -> STRPOS(string, substring)
643                Ok(Expression::Function(Box::new(Function::new(
644                    "STRPOS".to_string(),
645                    vec![string, substring],
646                ))))
647            }
648
649            // ARRAY_LENGTH -> CARDINALITY in Presto
650            "ARRAY_LENGTH" if f.args.len() == 1 => Ok(Expression::Function(Box::new(
651                Function::new("CARDINALITY".to_string(), f.args),
652            ))),
653
654            // SIZE -> CARDINALITY in Presto
655            "SIZE" if f.args.len() == 1 => Ok(Expression::Function(Box::new(Function::new(
656                "CARDINALITY".to_string(),
657                f.args,
658            )))),
659
660            // ARRAY_CONTAINS -> CONTAINS in Presto
661            "ARRAY_CONTAINS" if f.args.len() == 2 => Ok(Expression::Function(Box::new(
662                Function::new("CONTAINS".to_string(), f.args),
663            ))),
664
665            // TO_DATE -> DATE_PARSE in Presto (or CAST to DATE)
666            "TO_DATE" if !f.args.is_empty() => {
667                if f.args.len() == 1 {
668                    // Simple case: just cast to DATE
669                    Ok(Expression::Cast(Box::new(Cast {
670                        this: f.args.into_iter().next().unwrap(),
671                        to: DataType::Date,
672                        trailing_comments: Vec::new(),
673                        double_colon_syntax: false,
674                        format: None,
675                        default: None,
676                        inferred_type: None,
677                    })))
678                } else {
679                    // With format: use DATE_PARSE
680                    Ok(Expression::Function(Box::new(Function::new(
681                        "DATE_PARSE".to_string(),
682                        f.args,
683                    ))))
684                }
685            }
686
687            // TO_TIMESTAMP -> DATE_PARSE / CAST
688            "TO_TIMESTAMP" if !f.args.is_empty() => {
689                if f.args.len() == 1 {
690                    Ok(Expression::Cast(Box::new(Cast {
691                        this: f.args.into_iter().next().unwrap(),
692                        to: DataType::Timestamp {
693                            precision: None,
694                            timezone: false,
695                        },
696                        trailing_comments: Vec::new(),
697                        double_colon_syntax: false,
698                        format: None,
699                        default: None,
700                        inferred_type: None,
701                    })))
702                } else {
703                    Ok(Expression::Function(Box::new(Function::new(
704                        "DATE_PARSE".to_string(),
705                        f.args,
706                    ))))
707                }
708            }
709
710            // DATE_FORMAT -> DATE_FORMAT (native in Presto)
711            "DATE_FORMAT" => Ok(Expression::Function(Box::new(f))),
712
713            // strftime -> DATE_FORMAT in Presto
714            "STRFTIME" if f.args.len() >= 2 => {
715                let mut args = f.args;
716                // strftime(format, date) -> DATE_FORMAT(date, format)
717                let format = args.remove(0);
718                let date = args.remove(0);
719                Ok(Expression::Function(Box::new(Function::new(
720                    "DATE_FORMAT".to_string(),
721                    vec![date, format],
722                ))))
723            }
724
725            // TO_CHAR -> DATE_FORMAT in Presto (convert Oracle-style format to Presto C-style)
726            "TO_CHAR" if f.args.len() >= 2 => {
727                let mut args = f.args;
728                // Convert Oracle-style format string to Presto C-style
729                if let Expression::Literal(ref lit) = args[1] {
730                    if let Literal::String(ref s) = lit.as_ref() {
731                        let converted = Self::oracle_to_presto_format(s);
732                        args[1] = Expression::Literal(Box::new(Literal::String(converted)));
733                    }
734                }
735                Ok(Expression::Function(Box::new(Function::new(
736                    "DATE_FORMAT".to_string(),
737                    args,
738                ))))
739            }
740
741            // LEVENSHTEIN -> LEVENSHTEIN_DISTANCE in Presto
742            "LEVENSHTEIN" if !f.args.is_empty() => Ok(Expression::Function(Box::new(
743                Function::new("LEVENSHTEIN_DISTANCE".to_string(), f.args),
744            ))),
745
746            // FLATTEN -> FLATTEN is supported in Presto for nested arrays
747            "FLATTEN" => Ok(Expression::Function(Box::new(f))),
748
749            // JSON_EXTRACT -> JSON_EXTRACT (native in Presto)
750            "JSON_EXTRACT" => Ok(Expression::Function(Box::new(f))),
751
752            // JSON_EXTRACT_SCALAR -> JSON_EXTRACT_SCALAR (native in Presto)
753            "JSON_EXTRACT_SCALAR" => Ok(Expression::Function(Box::new(f))),
754
755            // GET_JSON_OBJECT -> JSON_EXTRACT_SCALAR in Presto
756            "GET_JSON_OBJECT" if f.args.len() == 2 => Ok(Expression::Function(Box::new(
757                Function::new("JSON_EXTRACT_SCALAR".to_string(), f.args),
758            ))),
759
760            // COLLECT_LIST -> ARRAY_AGG
761            "COLLECT_LIST" if !f.args.is_empty() => Ok(Expression::Function(Box::new(
762                Function::new("ARRAY_AGG".to_string(), f.args),
763            ))),
764
765            // COLLECT_SET -> ARRAY_DISTINCT(ARRAY_AGG())
766            "COLLECT_SET" if !f.args.is_empty() => {
767                let array_agg =
768                    Expression::Function(Box::new(Function::new("ARRAY_AGG".to_string(), f.args)));
769                Ok(Expression::Function(Box::new(Function::new(
770                    "ARRAY_DISTINCT".to_string(),
771                    vec![array_agg],
772                ))))
773            }
774
775            // RLIKE -> REGEXP_LIKE in Presto
776            "RLIKE" if f.args.len() == 2 => Ok(Expression::Function(Box::new(Function::new(
777                "REGEXP_LIKE".to_string(),
778                f.args,
779            )))),
780
781            // REGEXP -> REGEXP_LIKE in Presto
782            "REGEXP" if f.args.len() == 2 => Ok(Expression::Function(Box::new(Function::new(
783                "REGEXP_LIKE".to_string(),
784                f.args,
785            )))),
786
787            // PARSE_JSON -> JSON_PARSE in Presto
788            "PARSE_JSON" => Ok(Expression::Function(Box::new(Function::new(
789                "JSON_PARSE".to_string(),
790                f.args,
791            )))),
792
793            // GET_PATH(obj, path) -> JSON_EXTRACT(obj, json_path) in Presto
794            "GET_PATH" if f.args.len() == 2 => {
795                let mut args = f.args;
796                let this = args.remove(0);
797                let path = args.remove(0);
798                let json_path = match &path {
799                    Expression::Literal(lit) if matches!(lit.as_ref(), Literal::String(_)) => {
800                        let Literal::String(s) = lit.as_ref() else {
801                            unreachable!()
802                        };
803                        let normalized = if s.starts_with('$') {
804                            s.clone()
805                        } else if s.starts_with('[') {
806                            format!("${}", s)
807                        } else {
808                            format!("$.{}", s)
809                        };
810                        Expression::Literal(Box::new(Literal::String(normalized)))
811                    }
812                    _ => path,
813                };
814                Ok(Expression::JsonExtract(Box::new(JsonExtractFunc {
815                    this,
816                    path: json_path,
817                    returning: None,
818                    arrow_syntax: false,
819                    hash_arrow_syntax: false,
820                    wrapper_option: None,
821                    quotes_option: None,
822                    on_scalar_string: false,
823                    on_error: None,
824                })))
825            }
826
827            // REGEXP_SUBSTR(subject, pattern, ...) -> REGEXP_EXTRACT(subject, pattern[, group])
828            "REGEXP_SUBSTR" if f.args.len() >= 2 => {
829                let mut args = f.args;
830                let subject = args.remove(0);
831                let pattern = args.remove(0);
832                // If 6-arg form: (subject, pattern, pos, occ, params, group) -> keep group
833                if args.len() >= 4 {
834                    let _pos = args.remove(0);
835                    let _occ = args.remove(0);
836                    let _params = args.remove(0);
837                    let group = args.remove(0);
838                    Ok(Expression::Function(Box::new(Function::new(
839                        "REGEXP_EXTRACT".to_string(),
840                        vec![subject, pattern, group],
841                    ))))
842                } else {
843                    Ok(Expression::Function(Box::new(Function::new(
844                        "REGEXP_EXTRACT".to_string(),
845                        vec![subject, pattern],
846                    ))))
847                }
848            }
849
850            // DATE_PART(epoch_second, x) -> TO_UNIXTIME(CAST(x AS TIMESTAMP))
851            // DATE_PART(epoch_millisecond[s], x) -> TO_UNIXTIME(CAST(x AS TIMESTAMP)) * 1000
852            "DATE_PART" if f.args.len() == 2 => {
853                let part_name = match &f.args[0] {
854                    Expression::Identifier(id) => Some(id.name.to_uppercase()),
855                    Expression::Var(v) => Some(v.this.to_uppercase()),
856                    Expression::Column(c) => Some(c.name.name.to_uppercase()),
857                    _ => None,
858                };
859                match part_name.as_deref() {
860                    Some("EPOCH_SECOND" | "EPOCH_SECONDS") => {
861                        let mut args = f.args;
862                        let value = args.remove(1);
863                        let cast_expr = Expression::Cast(Box::new(Cast {
864                            this: value,
865                            to: DataType::Timestamp {
866                                precision: None,
867                                timezone: false,
868                            },
869                            trailing_comments: Vec::new(),
870                            double_colon_syntax: false,
871                            format: None,
872                            default: None,
873                            inferred_type: None,
874                        }));
875                        Ok(Expression::Function(Box::new(Function::new(
876                            "TO_UNIXTIME".to_string(),
877                            vec![cast_expr],
878                        ))))
879                    }
880                    Some("EPOCH_MILLISECOND" | "EPOCH_MILLISECONDS") => {
881                        let mut args = f.args;
882                        let value = args.remove(1);
883                        let cast_expr = Expression::Cast(Box::new(Cast {
884                            this: value,
885                            to: DataType::Timestamp {
886                                precision: None,
887                                timezone: false,
888                            },
889                            trailing_comments: Vec::new(),
890                            double_colon_syntax: false,
891                            format: None,
892                            default: None,
893                            inferred_type: None,
894                        }));
895                        let unixtime = Expression::Function(Box::new(Function::new(
896                            "TO_UNIXTIME".to_string(),
897                            vec![cast_expr],
898                        )));
899                        Ok(Expression::Mul(Box::new(BinaryOp {
900                            left: unixtime,
901                            right: Expression::Literal(Box::new(Literal::Number(
902                                "1000".to_string(),
903                            ))),
904                            left_comments: Vec::new(),
905                            operator_comments: Vec::new(),
906                            trailing_comments: Vec::new(),
907                            inferred_type: None,
908                        })))
909                    }
910                    _ => Ok(Expression::Function(Box::new(f))),
911                }
912            }
913
914            // REPLACE(x, y) with 2 args -> REPLACE(x, y, '') - Presto requires explicit empty string
915            "REPLACE" if f.args.len() == 2 => {
916                let mut args = f.args;
917                args.push(Expression::string(""));
918                Ok(Expression::Function(Box::new(Function::new(
919                    "REPLACE".to_string(),
920                    args,
921                ))))
922            }
923
924            // REGEXP_REPLACE(x, y) with 2 args -> REGEXP_REPLACE(x, y, '')
925            "REGEXP_REPLACE" if f.args.len() == 2 => {
926                let mut args = f.args;
927                args.push(Expression::string(""));
928                Ok(Expression::Function(Box::new(Function::new(
929                    "REGEXP_REPLACE".to_string(),
930                    args,
931                ))))
932            }
933
934            // Pass through everything else
935            _ => Ok(Expression::Function(Box::new(f))),
936        }
937    }
938
939    fn transform_aggregate_function(
940        &self,
941        f: Box<crate::expressions::AggregateFunction>,
942    ) -> Result<Expression> {
943        let name_upper = f.name.to_uppercase();
944        match name_upper.as_str() {
945            // COUNT_IF -> SUM(CASE WHEN...)
946            "COUNT_IF" if !f.args.is_empty() => {
947                let condition = f.args.into_iter().next().unwrap();
948                let case_expr = Expression::Case(Box::new(Case {
949                    operand: None,
950                    whens: vec![(condition, Expression::number(1))],
951                    else_: Some(Expression::number(0)),
952                    comments: Vec::new(),
953                    inferred_type: None,
954                }));
955                Ok(Expression::Sum(Box::new(AggFunc {
956                    ignore_nulls: None,
957                    having_max: None,
958                    this: case_expr,
959                    distinct: f.distinct,
960                    filter: f.filter,
961                    order_by: Vec::new(),
962                    name: None,
963                    limit: None,
964                    inferred_type: None,
965                })))
966            }
967
968            // ANY_VALUE -> ARBITRARY in Presto
969            "ANY_VALUE" if !f.args.is_empty() => Ok(Expression::Function(Box::new(Function::new(
970                "ARBITRARY".to_string(),
971                f.args,
972            )))),
973
974            // GROUP_CONCAT -> ARRAY_JOIN(ARRAY_AGG())
975            "GROUP_CONCAT" if !f.args.is_empty() => {
976                let mut args = f.args;
977                let first = args.remove(0);
978                let separator = args.pop();
979                let array_agg = Expression::Function(Box::new(Function::new(
980                    "ARRAY_AGG".to_string(),
981                    vec![first],
982                )));
983                let mut join_args = vec![array_agg];
984                if let Some(sep) = separator {
985                    join_args.push(sep);
986                }
987                Ok(Expression::Function(Box::new(Function::new(
988                    "ARRAY_JOIN".to_string(),
989                    join_args,
990                ))))
991            }
992
993            // STRING_AGG -> ARRAY_JOIN(ARRAY_AGG())
994            "STRING_AGG" if !f.args.is_empty() => {
995                let mut args = f.args;
996                let first = args.remove(0);
997                let separator = args.pop();
998                let array_agg = Expression::Function(Box::new(Function::new(
999                    "ARRAY_AGG".to_string(),
1000                    vec![first],
1001                )));
1002                let mut join_args = vec![array_agg];
1003                if let Some(sep) = separator {
1004                    join_args.push(sep);
1005                }
1006                Ok(Expression::Function(Box::new(Function::new(
1007                    "ARRAY_JOIN".to_string(),
1008                    join_args,
1009                ))))
1010            }
1011
1012            // LISTAGG -> ARRAY_JOIN(ARRAY_AGG())
1013            "LISTAGG" if !f.args.is_empty() => {
1014                let mut args = f.args;
1015                let first = args.remove(0);
1016                let separator = args.pop();
1017                let array_agg = Expression::Function(Box::new(Function::new(
1018                    "ARRAY_AGG".to_string(),
1019                    vec![first],
1020                )));
1021                let mut join_args = vec![array_agg];
1022                if let Some(sep) = separator {
1023                    join_args.push(sep);
1024                }
1025                Ok(Expression::Function(Box::new(Function::new(
1026                    "ARRAY_JOIN".to_string(),
1027                    join_args,
1028                ))))
1029            }
1030
1031            // VAR -> VAR_POP in Presto
1032            "VAR" if !f.args.is_empty() => {
1033                Ok(Expression::AggregateFunction(Box::new(AggregateFunction {
1034                    name: "VAR_POP".to_string(),
1035                    args: f.args,
1036                    distinct: f.distinct,
1037                    filter: f.filter,
1038                    order_by: Vec::new(),
1039                    limit: None,
1040                    ignore_nulls: None,
1041                    inferred_type: None,
1042                })))
1043            }
1044
1045            // VARIANCE -> VAR_SAMP in Presto (for sample variance)
1046            "VARIANCE" if !f.args.is_empty() => {
1047                Ok(Expression::AggregateFunction(Box::new(AggregateFunction {
1048                    name: "VAR_SAMP".to_string(),
1049                    args: f.args,
1050                    distinct: f.distinct,
1051                    filter: f.filter,
1052                    order_by: Vec::new(),
1053                    limit: None,
1054                    ignore_nulls: None,
1055                    inferred_type: None,
1056                })))
1057            }
1058
1059            // Pass through everything else
1060            _ => Ok(Expression::AggregateFunction(f)),
1061        }
1062    }
1063
1064    fn transform_cast(&self, c: Cast) -> Result<Expression> {
1065        // Presto type mappings are handled in the generator
1066        Ok(Expression::Cast(Box::new(c)))
1067    }
1068}
polyglot_sql/dialects/presto.rs

polyglot_sql/dialects/
presto.rs
