Skip to main content

polyglot_sql/dialects/
duckdb.rs

1//! DuckDB Dialect
2//!
3//! DuckDB-specific transformations based on sqlglot patterns.
4//! Key features:
5//! - Modern SQL analytics database with PostgreSQL-like syntax
6//! - LIST type for arrays
7//! - STRUCT support with dot access
8//! - EPOCH_MS / EPOCH for timestamps
9//! - EXCLUDE / REPLACE in SELECT
10//! - Rich array/list functions
11
12use super::{DialectImpl, DialectType};
13use crate::error::Result;
14use crate::expressions::{
15    AggFunc, Alias, BinaryOp, Case, Cast, CeilFunc, Column, DataType, Expression, Function,
16    FunctionParameter, Identifier, Interval, IntervalUnit, IntervalUnitSpec, IsNull, JSONPath,
17    JSONPathKey, JSONPathRoot, JSONPathSubscript, JsonExtractFunc, Literal, Null, Paren, Struct,
18    Subquery, SubstringFunc, UnaryFunc, UnaryOp, VarArgFunc, WindowFunction,
19};
20use crate::generator::GeneratorConfig;
21use crate::tokens::TokenizerConfig;
22
23/// Normalize a JSON path for DuckDB arrow syntax.
24/// Converts string keys like 'foo' to '$.foo' and numeric indexes like 0 to '$[0]'.
25/// This matches Python sqlglot's to_json_path() behavior.
26fn normalize_json_path(path: Expression) -> Expression {
27    match &path {
28        // String literal: 'foo' -> JSONPath with $.foo
29        Expression::Literal(lit) if matches!(lit.as_ref(), Literal::String(_)) => {
30            let Literal::String(s) = lit.as_ref() else {
31                unreachable!()
32            };
33            // Skip paths that are already normalized (start with $ or /)
34            // Also skip JSON pointer syntax and back-of-list syntax [#-i]
35            if s.starts_with('$') || s.starts_with('/') || s.contains("[#") {
36                return path;
37            }
38            // Create JSONPath expression: $.key
39            Expression::JSONPath(Box::new(JSONPath {
40                expressions: vec![
41                    Expression::JSONPathRoot(JSONPathRoot),
42                    Expression::JSONPathKey(Box::new(JSONPathKey {
43                        this: Box::new(Expression::Literal(Box::new(Literal::String(s.clone())))),
44                    })),
45                ],
46                escape: None,
47            }))
48        }
49        // Number literal: 0 -> JSONPath with $[0]
50        Expression::Literal(lit) if matches!(lit.as_ref(), Literal::Number(_)) => {
51            let Literal::Number(n) = lit.as_ref() else {
52                unreachable!()
53            };
54            // Create JSONPath expression: $[n]
55            Expression::JSONPath(Box::new(JSONPath {
56                expressions: vec![
57                    Expression::JSONPathRoot(JSONPathRoot),
58                    Expression::JSONPathSubscript(Box::new(JSONPathSubscript {
59                        this: Box::new(Expression::Literal(Box::new(Literal::Number(n.clone())))),
60                    })),
61                ],
62                escape: None,
63            }))
64        }
65        // Already a JSONPath or other expression - return as is
66        _ => path,
67    }
68}
69
70/// Helper to wrap JSON arrow expressions in parentheses when they appear
71/// in contexts that require it (Binary, In, Not expressions)
72/// This matches Python sqlglot's WRAPPED_JSON_EXTRACT_EXPRESSIONS behavior
73fn wrap_if_json_arrow(expr: Expression) -> Expression {
74    match &expr {
75        Expression::JsonExtract(f) if f.arrow_syntax => Expression::Paren(Box::new(Paren {
76            this: expr,
77            trailing_comments: Vec::new(),
78        })),
79        Expression::JsonExtractScalar(f) if f.arrow_syntax => Expression::Paren(Box::new(Paren {
80            this: expr,
81            trailing_comments: Vec::new(),
82        })),
83        _ => expr,
84    }
85}
86
87/// DuckDB dialect
88pub struct DuckDBDialect;
89
90impl DialectImpl for DuckDBDialect {
91    fn dialect_type(&self) -> DialectType {
92        DialectType::DuckDB
93    }
94
95    fn tokenizer_config(&self) -> TokenizerConfig {
96        let mut config = TokenizerConfig::default();
97        // DuckDB uses double quotes for identifiers
98        config.identifiers.insert('"', '"');
99        // DuckDB supports nested comments
100        config.nested_comments = true;
101        // DuckDB allows underscores as digit separators in numeric literals
102        config.numbers_can_be_underscore_separated = true;
103        config
104    }
105
106    fn generator_config(&self) -> GeneratorConfig {
107        use crate::generator::IdentifierQuoteStyle;
108        GeneratorConfig {
109            identifier_quote: '"',
110            identifier_quote_style: IdentifierQuoteStyle::DOUBLE_QUOTE,
111            dialect: Some(DialectType::DuckDB),
112            // DuckDB-specific settings from Python sqlglot
113            parameter_token: "$",
114            named_placeholder_token: "$",
115            join_hints: false,
116            table_hints: false,
117            query_hints: false,
118            limit_fetch_style: crate::generator::LimitFetchStyle::Limit,
119            struct_delimiter: ("(", ")"),
120            rename_table_with_db: false,
121            nvl2_supported: false,
122            semi_anti_join_with_side: false,
123            tablesample_keywords: "TABLESAMPLE",
124            tablesample_seed_keyword: "REPEATABLE",
125            last_day_supports_date_part: false,
126            json_key_value_pair_sep: ",",
127            ignore_nulls_in_func: true,
128            json_path_bracketed_key_supported: false,
129            supports_create_table_like: false,
130            multi_arg_distinct: false,
131            quantified_no_paren_space: false,
132            can_implement_array_any: true,
133            supports_to_number: false,
134            supports_window_exclude: true,
135            copy_has_into_keyword: false,
136            star_except: "EXCLUDE",
137            pad_fill_pattern_is_required: true,
138            array_concat_is_var_len: false,
139            array_size_dim_required: None,
140            normalize_extract_date_parts: true,
141            supports_like_quantifiers: false,
142            // DuckDB supports TRY_CAST
143            try_supported: true,
144            // DuckDB uses curly brace notation for struct literals: {'a': 1}
145            struct_curly_brace_notation: true,
146            // DuckDB uses bracket-only notation for arrays: [1, 2, 3]
147            array_bracket_only: true,
148            ..Default::default()
149        }
150    }
151
152    fn transform_expr(&self, expr: Expression) -> Result<Expression> {
153        match expr {
154            // ===== Data Type Mappings =====
155            Expression::DataType(dt) => self.transform_data_type(dt),
156
157            // ===== Operator transformations =====
158            // BitwiseXor -> XOR() function in DuckDB
159            Expression::BitwiseXor(op) => Ok(Expression::Function(Box::new(
160                crate::expressions::Function::new("XOR", vec![op.left, op.right]),
161            ))),
162
163            // ===== Array/List syntax =====
164            // ARRAY[1, 2, 3] -> [1, 2, 3] in DuckDB (bracket notation preferred)
165            Expression::ArrayFunc(mut f) => {
166                f.bracket_notation = true;
167                Ok(Expression::ArrayFunc(f))
168            }
169
170            // IFNULL -> COALESCE in DuckDB
171            Expression::IfNull(f) => Ok(Expression::Coalesce(Box::new(VarArgFunc {
172                original_name: None,
173                expressions: vec![f.this, f.expression],
174                inferred_type: None,
175            }))),
176
177            // NVL -> COALESCE in DuckDB
178            Expression::Nvl(f) => Ok(Expression::Coalesce(Box::new(VarArgFunc {
179                original_name: None,
180                expressions: vec![f.this, f.expression],
181                inferred_type: None,
182            }))),
183
184            // Coalesce with original_name (e.g., IFNULL parsed as Coalesce) -> clear original_name
185            Expression::Coalesce(mut f) => {
186                f.original_name = None;
187                Ok(Expression::Coalesce(f))
188            }
189
190            // GROUP_CONCAT -> LISTAGG in DuckDB
191            Expression::GroupConcat(f) => Ok(Expression::ListAgg(Box::new(
192                crate::expressions::ListAggFunc {
193                    this: f.this,
194                    separator: f.separator,
195                    on_overflow: None,
196                    order_by: f.order_by,
197                    distinct: f.distinct,
198                    filter: f.filter,
199                    inferred_type: None,
200                },
201            ))),
202
203            // LISTAGG is native in DuckDB - keep as-is
204            Expression::ListAgg(f) => Ok(Expression::ListAgg(f)),
205
206            // STRING_AGG -> LISTAGG in DuckDB (normalize to LISTAGG)
207            Expression::StringAgg(f) => Ok(Expression::ListAgg(Box::new(
208                crate::expressions::ListAggFunc {
209                    this: f.this,
210                    separator: f.separator,
211                    on_overflow: None,
212                    order_by: f.order_by,
213                    distinct: f.distinct,
214                    filter: f.filter,
215                    inferred_type: None,
216                },
217            ))),
218
219            // TryCast -> TRY_CAST (DuckDB supports TRY_CAST)
220            Expression::TryCast(c) => Ok(Expression::TryCast(c)),
221
222            // SafeCast -> TRY_CAST in DuckDB
223            Expression::SafeCast(c) => Ok(Expression::TryCast(c)),
224
225            // ILIKE is native to DuckDB (PostgreSQL-compatible)
226            Expression::ILike(op) => Ok(Expression::ILike(op)),
227
228            // EXPLODE -> UNNEST in DuckDB
229            Expression::Explode(f) => Ok(Expression::Unnest(Box::new(
230                crate::expressions::UnnestFunc {
231                    this: f.this,
232                    expressions: Vec::new(),
233                    with_ordinality: false,
234                    alias: None,
235                    offset_alias: None,
236                },
237            ))),
238
239            // UNNEST is native to DuckDB
240            Expression::Unnest(f) => Ok(Expression::Unnest(f)),
241
242            // ArrayContainedBy (<@) -> ArrayContainsAll (@>) with swapped operands
243            // a <@ b becomes b @> a
244            Expression::ArrayContainedBy(op) => {
245                Ok(Expression::ArrayContainsAll(Box::new(BinaryOp {
246                    left: op.right,
247                    right: op.left,
248                    left_comments: Vec::new(),
249                    operator_comments: Vec::new(),
250                    trailing_comments: Vec::new(),
251                    inferred_type: None,
252                })))
253            }
254
255            // DATE_ADD -> date + INTERVAL in DuckDB
256            Expression::DateAdd(f) => {
257                // Reconstruct INTERVAL expression from value and unit
258                let interval_expr = if matches!(&f.interval, Expression::Interval(_)) {
259                    f.interval
260                } else {
261                    Expression::Interval(Box::new(Interval {
262                        this: Some(f.interval),
263                        unit: Some(IntervalUnitSpec::Simple {
264                            unit: f.unit,
265                            use_plural: false,
266                        }),
267                    }))
268                };
269                Ok(Expression::Add(Box::new(BinaryOp {
270                    left: f.this,
271                    right: interval_expr,
272                    left_comments: Vec::new(),
273                    operator_comments: Vec::new(),
274                    trailing_comments: Vec::new(),
275                    inferred_type: None,
276                })))
277            }
278
279            // DATE_SUB -> date - INTERVAL in DuckDB
280            Expression::DateSub(f) => {
281                // Reconstruct INTERVAL expression from value and unit
282                let interval_expr = if matches!(&f.interval, Expression::Interval(_)) {
283                    f.interval
284                } else {
285                    Expression::Interval(Box::new(Interval {
286                        this: Some(f.interval),
287                        unit: Some(IntervalUnitSpec::Simple {
288                            unit: f.unit,
289                            use_plural: false,
290                        }),
291                    }))
292                };
293                Ok(Expression::Sub(Box::new(BinaryOp {
294                    left: f.this,
295                    right: interval_expr,
296                    left_comments: Vec::new(),
297                    operator_comments: Vec::new(),
298                    trailing_comments: Vec::new(),
299                    inferred_type: None,
300                })))
301            }
302
303            // GenerateSeries with 1 arg -> GENERATE_SERIES(0, n)
304            Expression::GenerateSeries(mut f) => {
305                // If only end is set (no start), add 0 as start
306                if f.start.is_none() && f.end.is_some() {
307                    f.start = Some(Box::new(Expression::number(0)));
308                }
309                Ok(Expression::GenerateSeries(f))
310            }
311
312            // ===== Array/List functions =====
313            // ArrayAppend -> LIST_APPEND
314            Expression::ArrayAppend(f) => Ok(Expression::Function(Box::new(Function::new(
315                "LIST_APPEND".to_string(),
316                vec![f.this, f.expression],
317            )))),
318
319            // ArrayPrepend -> LIST_PREPEND(element, array) - note arg swap
320            Expression::ArrayPrepend(f) => Ok(Expression::Function(Box::new(Function::new(
321                "LIST_PREPEND".to_string(),
322                vec![f.expression, f.this],
323            )))),
324
325            // ArrayUniqueAgg -> LIST(DISTINCT col) FILTER(WHERE NOT col IS NULL)
326            Expression::ArrayUniqueAgg(f) => {
327                let col = f.this;
328                // NOT col IS NULL
329                let filter_expr = Expression::Not(Box::new(UnaryOp {
330                    this: Expression::IsNull(Box::new(IsNull {
331                        this: col.clone(),
332                        not: false,
333                        postfix_form: false,
334                    })),
335                    inferred_type: None,
336                }));
337                Ok(Expression::ArrayAgg(Box::new(AggFunc {
338                    this: col,
339                    distinct: true,
340                    filter: Some(filter_expr),
341                    order_by: Vec::new(),
342                    name: Some("LIST".to_string()),
343                    ignore_nulls: None,
344                    having_max: None,
345                    limit: None,
346                    inferred_type: None,
347                })))
348            }
349
350            // Split -> STR_SPLIT
351            Expression::Split(f) => Ok(Expression::Function(Box::new(Function::new(
352                "STR_SPLIT".to_string(),
353                vec![f.this, f.delimiter],
354            )))),
355
356            // RANDOM is native to DuckDB
357            Expression::Random(_) => Ok(Expression::Random(crate::expressions::Random)),
358
359            // Rand with seed -> keep as Rand so NORMAL/UNIFORM handlers can extract the seed
360            // Rand without seed -> Random
361            Expression::Rand(r) => {
362                if r.seed.is_some() {
363                    Ok(Expression::Rand(r))
364                } else {
365                    Ok(Expression::Random(crate::expressions::Random))
366                }
367            }
368
369            // ===== Boolean aggregates =====
370            // LogicalAnd -> BOOL_AND with CAST to BOOLEAN
371            Expression::LogicalAnd(f) => Ok(Expression::Function(Box::new(Function::new(
372                "BOOL_AND".to_string(),
373                vec![Expression::Cast(Box::new(crate::expressions::Cast {
374                    this: f.this,
375                    to: crate::expressions::DataType::Boolean,
376                    trailing_comments: Vec::new(),
377                    double_colon_syntax: false,
378                    format: None,
379                    default: None,
380                    inferred_type: None,
381                }))],
382            )))),
383
384            // LogicalOr -> BOOL_OR with CAST to BOOLEAN
385            Expression::LogicalOr(f) => Ok(Expression::Function(Box::new(Function::new(
386                "BOOL_OR".to_string(),
387                vec![Expression::Cast(Box::new(crate::expressions::Cast {
388                    this: f.this,
389                    to: crate::expressions::DataType::Boolean,
390                    trailing_comments: Vec::new(),
391                    double_colon_syntax: false,
392                    format: None,
393                    default: None,
394                    inferred_type: None,
395                }))],
396            )))),
397
398            // ===== Approximate functions =====
399            // ApproxDistinct -> APPROX_COUNT_DISTINCT
400            Expression::ApproxDistinct(f) => Ok(Expression::Function(Box::new(Function::new(
401                "APPROX_COUNT_DISTINCT".to_string(),
402                vec![f.this],
403            )))),
404
405            // ===== Variance =====
406            // VarPop -> VAR_POP
407            Expression::VarPop(f) => Ok(Expression::Function(Box::new(Function::new(
408                "VAR_POP".to_string(),
409                vec![f.this],
410            )))),
411
412            // ===== Date/time functions =====
413            // DayOfMonth -> DAYOFMONTH
414            Expression::DayOfMonth(f) => Ok(Expression::Function(Box::new(Function::new(
415                "DAYOFMONTH".to_string(),
416                vec![f.this],
417            )))),
418
419            // DayOfWeek -> DAYOFWEEK
420            Expression::DayOfWeek(f) => Ok(Expression::Function(Box::new(Function::new(
421                "DAYOFWEEK".to_string(),
422                vec![f.this],
423            )))),
424
425            // DayOfWeekIso -> ISODOW
426            Expression::DayOfWeekIso(f) => Ok(Expression::Function(Box::new(Function::new(
427                "ISODOW".to_string(),
428                vec![f.this],
429            )))),
430
431            // DayOfYear -> DAYOFYEAR
432            Expression::DayOfYear(f) => Ok(Expression::Function(Box::new(Function::new(
433                "DAYOFYEAR".to_string(),
434                vec![f.this],
435            )))),
436
437            // WeekOfYear -> WEEKOFYEAR
438            Expression::WeekOfYear(f) => Ok(Expression::Function(Box::new(Function::new(
439                "WEEKOFYEAR".to_string(),
440                vec![f.this],
441            )))),
442
443            // ===== Time conversion functions =====
444            // TimeStrToUnix -> EPOCH
445            Expression::TimeStrToUnix(f) => Ok(Expression::Function(Box::new(Function::new(
446                "EPOCH".to_string(),
447                vec![f.this],
448            )))),
449
450            // TimeToUnix -> EPOCH
451            Expression::TimeToUnix(f) => Ok(Expression::Function(Box::new(Function::new(
452                "EPOCH".to_string(),
453                vec![f.this],
454            )))),
455
456            // UnixMicros -> EPOCH_US
457            Expression::UnixMicros(f) => Ok(Expression::Function(Box::new(Function::new(
458                "EPOCH_US".to_string(),
459                vec![f.this],
460            )))),
461
462            // UnixMillis -> EPOCH_MS
463            Expression::UnixMillis(f) => Ok(Expression::Function(Box::new(Function::new(
464                "EPOCH_MS".to_string(),
465                vec![f.this],
466            )))),
467
468            // TimestampDiff -> DATE_DIFF
469            Expression::TimestampDiff(f) => Ok(Expression::Function(Box::new(Function::new(
470                "DATE_DIFF".to_string(),
471                vec![*f.this, *f.expression],
472            )))),
473
474            // ===== Hash functions =====
475            // SHA -> SHA1
476            Expression::SHA(f) => Ok(Expression::Function(Box::new(Function::new(
477                "SHA1".to_string(),
478                vec![f.this],
479            )))),
480
481            // MD5Digest -> UNHEX(MD5(...))
482            Expression::MD5Digest(f) => Ok(Expression::Function(Box::new(Function::new(
483                "UNHEX".to_string(),
484                vec![*f.this],
485            )))),
486
487            // SHA1Digest -> UNHEX
488            Expression::SHA1Digest(f) => Ok(Expression::Function(Box::new(Function::new(
489                "UNHEX".to_string(),
490                vec![f.this],
491            )))),
492
493            // SHA2Digest -> UNHEX
494            Expression::SHA2Digest(f) => Ok(Expression::Function(Box::new(Function::new(
495                "UNHEX".to_string(),
496                vec![*f.this],
497            )))),
498
499            // ===== Vector/Distance functions =====
500            // CosineDistance -> LIST_COSINE_DISTANCE
501            Expression::CosineDistance(f) => Ok(Expression::Function(Box::new(Function::new(
502                "LIST_COSINE_DISTANCE".to_string(),
503                vec![*f.this, *f.expression],
504            )))),
505
506            // EuclideanDistance -> LIST_DISTANCE
507            Expression::EuclideanDistance(f) => Ok(Expression::Function(Box::new(Function::new(
508                "LIST_DISTANCE".to_string(),
509                vec![*f.this, *f.expression],
510            )))),
511
512            // ===== Numeric checks =====
513            // IsInf -> ISINF
514            Expression::IsInf(f) => Ok(Expression::Function(Box::new(Function::new(
515                "ISINF".to_string(),
516                vec![f.this],
517            )))),
518
519            // IsNan -> ISNAN
520            Expression::IsNan(f) => Ok(Expression::Function(Box::new(Function::new(
521                "ISNAN".to_string(),
522                vec![f.this],
523            )))),
524
525            // ===== Pattern matching =====
526            // RegexpLike (~) -> REGEXP_FULL_MATCH in DuckDB
527            Expression::RegexpLike(f) => Ok(Expression::Function(Box::new(Function::new(
528                "REGEXP_FULL_MATCH".to_string(),
529                vec![f.this, f.pattern],
530            )))),
531
532            // ===== Time functions =====
533            // CurrentTime -> CURRENT_TIME (no parens in DuckDB)
534            Expression::CurrentTime(_) => Ok(Expression::Function(Box::new(Function {
535                name: "CURRENT_TIME".to_string(),
536                args: vec![],
537                distinct: false,
538                trailing_comments: vec![],
539                use_bracket_syntax: false,
540                no_parens: true,
541                quoted: false,
542                span: None,
543                inferred_type: None,
544            }))),
545
546            // ===== Return statement =====
547            // ReturnStmt -> just output the inner expression
548            Expression::ReturnStmt(e) => Ok(*e),
549
550            // ===== DDL Column Constraints =====
551            // CommentColumnConstraint -> ignored (DuckDB doesn't support column comments this way)
552            Expression::CommentColumnConstraint(_) => Ok(Expression::Literal(Box::new(
553                crate::expressions::Literal::String(String::new()),
554            ))),
555
556            // JsonExtract -> use arrow syntax (->) in DuckDB with normalized JSON path
557            Expression::JsonExtract(mut f) => {
558                f.arrow_syntax = true;
559                f.path = normalize_json_path(f.path);
560                Ok(Expression::JsonExtract(f))
561            }
562
563            // JsonExtractScalar -> use arrow syntax (->>) in DuckDB with normalized JSON path
564            Expression::JsonExtractScalar(mut f) => {
565                f.arrow_syntax = true;
566                f.path = normalize_json_path(f.path);
567                Ok(Expression::JsonExtractScalar(f))
568            }
569
570            // CARDINALITY: keep as Expression::Cardinality - cross_dialect_normalize handles
571            // the conversion to target-specific form. For DuckDB->DuckDB, CARDINALITY is preserved
572            // (used for maps), for DuckDB->other it goes through ArrayLengthConvert.
573
574            // ADD_MONTHS(date, n) -> convert to Function and handle in transform_function
575            Expression::AddMonths(f) => {
576                let func = Function::new("ADD_MONTHS".to_string(), vec![f.this, f.expression]);
577                self.transform_function(func)
578            }
579
580            // NEXT_DAY(date, day) -> convert to Function and handle in transform_function
581            Expression::NextDay(f) => {
582                let func = Function::new("NEXT_DAY".to_string(), vec![f.this, f.expression]);
583                self.transform_function(func)
584            }
585
586            // LAST_DAY(date, unit) -> convert to Function and handle in transform_function
587            Expression::LastDay(f) => {
588                if let Some(unit) = f.unit {
589                    let unit_str = match unit {
590                        crate::expressions::DateTimeField::Year => "YEAR",
591                        crate::expressions::DateTimeField::Month => "MONTH",
592                        crate::expressions::DateTimeField::Quarter => "QUARTER",
593                        crate::expressions::DateTimeField::Week => "WEEK",
594                        crate::expressions::DateTimeField::Day => "DAY",
595                        _ => "MONTH",
596                    };
597                    let func = Function::new(
598                        "LAST_DAY".to_string(),
599                        vec![
600                            f.this,
601                            Expression::Identifier(Identifier {
602                                name: unit_str.to_string(),
603                                quoted: false,
604                                trailing_comments: Vec::new(),
605                                span: None,
606                            }),
607                        ],
608                    );
609                    self.transform_function(func)
610                } else {
611                    // Single arg LAST_DAY - pass through
612                    Ok(Expression::Function(Box::new(Function::new(
613                        "LAST_DAY".to_string(),
614                        vec![f.this],
615                    ))))
616                }
617            }
618
619            // DAYNAME(expr) -> STRFTIME(expr, '%a')
620            Expression::Dayname(d) => Ok(Expression::Function(Box::new(Function::new(
621                "STRFTIME".to_string(),
622                vec![
623                    *d.this,
624                    Expression::Literal(Box::new(Literal::String("%a".to_string()))),
625                ],
626            )))),
627
628            // MONTHNAME(expr) -> STRFTIME(expr, '%b')
629            Expression::Monthname(d) => Ok(Expression::Function(Box::new(Function::new(
630                "STRFTIME".to_string(),
631                vec![
632                    *d.this,
633                    Expression::Literal(Box::new(Literal::String("%b".to_string()))),
634                ],
635            )))),
636
637            // FLOOR(x, scale) -> ROUND(FLOOR(x * POWER(10, scale)) / POWER(10, scale), scale)
638            Expression::Floor(f) if f.scale.is_some() => {
639                let x = f.this;
640                let scale = f.scale.unwrap();
641                let needs_cast = match &scale {
642                    Expression::Literal(lit) if matches!(lit.as_ref(), Literal::Number(_)) => {
643                        let Literal::Number(n) = lit.as_ref() else {
644                            unreachable!()
645                        };
646                        n.contains('.')
647                    }
648                    _ => false,
649                };
650                let int_scale = if needs_cast {
651                    Expression::Cast(Box::new(Cast {
652                        this: scale.clone(),
653                        to: DataType::Int {
654                            length: None,
655                            integer_spelling: false,
656                        },
657                        trailing_comments: Vec::new(),
658                        double_colon_syntax: false,
659                        format: None,
660                        default: None,
661                        inferred_type: None,
662                    }))
663                } else {
664                    scale.clone()
665                };
666                let power_10 = Expression::Function(Box::new(Function::new(
667                    "POWER".to_string(),
668                    vec![Expression::number(10), int_scale.clone()],
669                )));
670                let x_paren = match &x {
671                    Expression::Add(_)
672                    | Expression::Sub(_)
673                    | Expression::Mul(_)
674                    | Expression::Div(_) => Expression::Paren(Box::new(Paren {
675                        this: x,
676                        trailing_comments: Vec::new(),
677                    })),
678                    _ => x,
679                };
680                let multiplied = Expression::Mul(Box::new(BinaryOp {
681                    left: x_paren,
682                    right: power_10.clone(),
683                    left_comments: Vec::new(),
684                    operator_comments: Vec::new(),
685                    trailing_comments: Vec::new(),
686                    inferred_type: None,
687                }));
688                let floored = Expression::Function(Box::new(Function::new(
689                    "FLOOR".to_string(),
690                    vec![multiplied],
691                )));
692                let divided = Expression::Div(Box::new(BinaryOp {
693                    left: floored,
694                    right: power_10,
695                    left_comments: Vec::new(),
696                    operator_comments: Vec::new(),
697                    trailing_comments: Vec::new(),
698                    inferred_type: None,
699                }));
700                Ok(Expression::Function(Box::new(Function::new(
701                    "ROUND".to_string(),
702                    vec![divided, int_scale],
703                ))))
704            }
705
706            // CEIL(x, scale) -> ROUND(CEIL(x * POWER(10, scale)) / POWER(10, scale), scale)
707            Expression::Ceil(f) if f.decimals.is_some() => {
708                let x = f.this;
709                let scale = f.decimals.unwrap();
710                let needs_cast = match &scale {
711                    Expression::Literal(lit) if matches!(lit.as_ref(), Literal::Number(_)) => {
712                        let Literal::Number(n) = lit.as_ref() else {
713                            unreachable!()
714                        };
715                        n.contains('.')
716                    }
717                    _ => false,
718                };
719                let int_scale = if needs_cast {
720                    Expression::Cast(Box::new(Cast {
721                        this: scale.clone(),
722                        to: DataType::Int {
723                            length: None,
724                            integer_spelling: false,
725                        },
726                        trailing_comments: Vec::new(),
727                        double_colon_syntax: false,
728                        format: None,
729                        default: None,
730                        inferred_type: None,
731                    }))
732                } else {
733                    scale.clone()
734                };
735                let power_10 = Expression::Function(Box::new(Function::new(
736                    "POWER".to_string(),
737                    vec![Expression::number(10), int_scale.clone()],
738                )));
739                let x_paren = match &x {
740                    Expression::Add(_)
741                    | Expression::Sub(_)
742                    | Expression::Mul(_)
743                    | Expression::Div(_) => Expression::Paren(Box::new(Paren {
744                        this: x,
745                        trailing_comments: Vec::new(),
746                    })),
747                    _ => x,
748                };
749                let multiplied = Expression::Mul(Box::new(BinaryOp {
750                    left: x_paren,
751                    right: power_10.clone(),
752                    left_comments: Vec::new(),
753                    operator_comments: Vec::new(),
754                    trailing_comments: Vec::new(),
755                    inferred_type: None,
756                }));
757                let ceiled = Expression::Function(Box::new(Function::new(
758                    "CEIL".to_string(),
759                    vec![multiplied],
760                )));
761                let divided = Expression::Div(Box::new(BinaryOp {
762                    left: ceiled,
763                    right: power_10,
764                    left_comments: Vec::new(),
765                    operator_comments: Vec::new(),
766                    trailing_comments: Vec::new(),
767                    inferred_type: None,
768                }));
769                Ok(Expression::Function(Box::new(Function::new(
770                    "ROUND".to_string(),
771                    vec![divided, int_scale],
772                ))))
773            }
774
775            // ParseJson: handled by generator (outputs JSON() for DuckDB)
776
777            // TABLE(GENERATOR(ROWCOUNT => n)) -> RANGE(n) in DuckDB
778            // The TABLE() wrapper around GENERATOR is parsed as TableArgument
779            Expression::TableArgument(ta) if ta.prefix.to_uppercase() == "TABLE" => {
780                // Check if inner is a GENERATOR or RANGE function
781                match ta.this {
782                    Expression::Function(ref f) if f.name.to_uppercase() == "RANGE" => {
783                        // Already converted to RANGE, unwrap TABLE()
784                        Ok(ta.this)
785                    }
786                    Expression::Function(ref f) if f.name.to_uppercase() == "GENERATOR" => {
787                        // GENERATOR(ROWCOUNT => n) -> RANGE(n)
788                        let mut rowcount = None;
789                        for arg in &f.args {
790                            if let Expression::NamedArgument(na) = arg {
791                                if na.name.name.to_uppercase() == "ROWCOUNT" {
792                                    rowcount = Some(na.value.clone());
793                                }
794                            }
795                        }
796                        if let Some(n) = rowcount {
797                            Ok(Expression::Function(Box::new(Function::new(
798                                "RANGE".to_string(),
799                                vec![n],
800                            ))))
801                        } else {
802                            Ok(Expression::TableArgument(ta))
803                        }
804                    }
805                    _ => Ok(Expression::TableArgument(ta)),
806                }
807            }
808
809            // JSONExtract (variant_extract/colon accessor) -> arrow syntax in DuckDB
810            Expression::JSONExtract(e) if e.variant_extract.is_some() => {
811                let path = match *e.expression {
812                    Expression::Literal(lit) if matches!(lit.as_ref(), Literal::String(_)) => {
813                        let Literal::String(s) = lit.as_ref() else {
814                            unreachable!()
815                        };
816                        // Convert bracket notation ["key"] to quoted dot notation ."key"
817                        let s = Self::convert_bracket_to_quoted_path(&s);
818                        let normalized = if s.starts_with('$') {
819                            s
820                        } else if s.starts_with('[') {
821                            format!("${}", s)
822                        } else {
823                            format!("$.{}", s)
824                        };
825                        Expression::Literal(Box::new(Literal::String(normalized)))
826                    }
827                    other => other,
828                };
829                Ok(Expression::JsonExtract(Box::new(JsonExtractFunc {
830                    this: *e.this,
831                    path,
832                    returning: None,
833                    arrow_syntax: true,
834                    hash_arrow_syntax: false,
835                    wrapper_option: None,
836                    quotes_option: None,
837                    on_scalar_string: false,
838                    on_error: None,
839                })))
840            }
841
842            // X'ABCD' -> UNHEX('ABCD') in DuckDB
843            Expression::Literal(lit) if matches!(lit.as_ref(), Literal::HexString(_)) => {
844                let Literal::HexString(s) = lit.as_ref() else {
845                    unreachable!()
846                };
847                Ok(Expression::Function(Box::new(Function::new(
848                    "UNHEX".to_string(),
849                    vec![Expression::Literal(Box::new(Literal::String(s.clone())))],
850                ))))
851            }
852
853            // b'a' -> CAST(e'a' AS BLOB) in DuckDB
854            Expression::Literal(lit) if matches!(lit.as_ref(), Literal::ByteString(_)) => {
855                let Literal::ByteString(s) = lit.as_ref() else {
856                    unreachable!()
857                };
858                Ok(Expression::Cast(Box::new(Cast {
859                    this: Expression::Literal(Box::new(Literal::EscapeString(s.clone()))),
860                    to: DataType::VarBinary { length: None },
861                    trailing_comments: Vec::new(),
862                    double_colon_syntax: false,
863                    format: None,
864                    default: None,
865                    inferred_type: None,
866                })))
867            }
868
869            // CAST(x AS DECIMAL) -> CAST(x AS DECIMAL(18, 3)) in DuckDB (default precision)
870            // Exception: CAST(a // b AS DECIMAL) from DIV conversion keeps bare DECIMAL
871            Expression::Cast(mut c) => {
872                if matches!(
873                    &c.to,
874                    DataType::Decimal {
875                        precision: None,
876                        ..
877                    }
878                ) && !matches!(&c.this, Expression::IntDiv(_))
879                {
880                    c.to = DataType::Decimal {
881                        precision: Some(18),
882                        scale: Some(3),
883                    };
884                }
885                let transformed_this = self.transform_expr(c.this)?;
886                c.this = transformed_this;
887                Ok(Expression::Cast(c))
888            }
889
890            // Generic function transformations
891            Expression::Function(f) => self.transform_function(*f),
892
893            // Generic aggregate function transformations
894            Expression::AggregateFunction(f) => self.transform_aggregate_function(f),
895
896            // WindowFunction with CASE-wrapped CORR: re-wrap so OVER is inside CASE
897            // Pattern: WindowFunction { this: CASE(ISNAN(CORR), NULL, CORR), over }
898            // Expected: CASE(ISNAN(WindowFunction(CORR, over)), NULL, WindowFunction(CORR, over))
899            Expression::WindowFunction(wf) => {
900                if let Expression::Case(case_box) = wf.this {
901                    let case = *case_box;
902                    // Detect the ISNAN(CORR) -> NULL pattern
903                    if case.whens.len() == 1
904                        && matches!(&case.else_, Some(Expression::AggregateFunction(ref af)) if af.name.to_uppercase() == "CORR")
905                    {
906                        // Re-wrap: put the OVER on each CORR inside the CASE
907                        let over = wf.over;
908                        let new_else = case.else_.map(|e| {
909                            Expression::WindowFunction(Box::new(WindowFunction {
910                                this: e,
911                                over: over.clone(),
912                                keep: None,
913                                inferred_type: None,
914                            }))
915                        });
916                        let new_whens = case
917                            .whens
918                            .into_iter()
919                            .map(|(when_cond, when_result)| {
920                                // wrap the ISNAN arg (which is CORR) with OVER
921                                let new_cond = if let Expression::Function(func) = when_cond {
922                                    if func.name.to_uppercase() == "ISNAN" && func.args.len() == 1 {
923                                        let inner = func.args.into_iter().next().unwrap();
924                                        let windowed =
925                                            Expression::WindowFunction(Box::new(WindowFunction {
926                                                this: inner,
927                                                over: over.clone(),
928                                                keep: None,
929                                                inferred_type: None,
930                                            }));
931                                        Expression::Function(Box::new(Function::new(
932                                            "ISNAN".to_string(),
933                                            vec![windowed],
934                                        )))
935                                    } else {
936                                        Expression::Function(func)
937                                    }
938                                } else {
939                                    when_cond
940                                };
941                                (new_cond, when_result)
942                            })
943                            .collect();
944                        Ok(Expression::Case(Box::new(Case {
945                            operand: None,
946                            whens: new_whens,
947                            else_: new_else,
948                            comments: Vec::new(),
949                            inferred_type: None,
950                        })))
951                    } else {
952                        Ok(Expression::WindowFunction(Box::new(WindowFunction {
953                            this: Expression::Case(Box::new(case)),
954                            over: wf.over,
955                            keep: wf.keep,
956                            inferred_type: None,
957                        })))
958                    }
959                } else {
960                    Ok(Expression::WindowFunction(wf))
961                }
962            }
963
964            // ===== Context-aware JSON arrow wrapping =====
965            // When JSON arrow expressions appear in Binary/In/Not contexts,
966            // they need to be wrapped in parentheses for correct precedence.
967            // This matches Python sqlglot's WRAPPED_JSON_EXTRACT_EXPRESSIONS behavior.
968
969            // Binary operators that need JSON wrapping
970            Expression::Eq(op) => Ok(Expression::Eq(Box::new(BinaryOp {
971                left: wrap_if_json_arrow(op.left),
972                right: wrap_if_json_arrow(op.right),
973                ..*op
974            }))),
975            Expression::Neq(op) => Ok(Expression::Neq(Box::new(BinaryOp {
976                left: wrap_if_json_arrow(op.left),
977                right: wrap_if_json_arrow(op.right),
978                ..*op
979            }))),
980            Expression::Lt(op) => Ok(Expression::Lt(Box::new(BinaryOp {
981                left: wrap_if_json_arrow(op.left),
982                right: wrap_if_json_arrow(op.right),
983                ..*op
984            }))),
985            Expression::Lte(op) => Ok(Expression::Lte(Box::new(BinaryOp {
986                left: wrap_if_json_arrow(op.left),
987                right: wrap_if_json_arrow(op.right),
988                ..*op
989            }))),
990            Expression::Gt(op) => Ok(Expression::Gt(Box::new(BinaryOp {
991                left: wrap_if_json_arrow(op.left),
992                right: wrap_if_json_arrow(op.right),
993                ..*op
994            }))),
995            Expression::Gte(op) => Ok(Expression::Gte(Box::new(BinaryOp {
996                left: wrap_if_json_arrow(op.left),
997                right: wrap_if_json_arrow(op.right),
998                ..*op
999            }))),
1000            Expression::And(op) => Ok(Expression::And(Box::new(BinaryOp {
1001                left: wrap_if_json_arrow(op.left),
1002                right: wrap_if_json_arrow(op.right),
1003                ..*op
1004            }))),
1005            Expression::Or(op) => Ok(Expression::Or(Box::new(BinaryOp {
1006                left: wrap_if_json_arrow(op.left),
1007                right: wrap_if_json_arrow(op.right),
1008                ..*op
1009            }))),
1010            Expression::Add(op) => Ok(Expression::Add(Box::new(BinaryOp {
1011                left: wrap_if_json_arrow(op.left),
1012                right: wrap_if_json_arrow(op.right),
1013                ..*op
1014            }))),
1015            Expression::Sub(op) => Ok(Expression::Sub(Box::new(BinaryOp {
1016                left: wrap_if_json_arrow(op.left),
1017                right: wrap_if_json_arrow(op.right),
1018                ..*op
1019            }))),
1020            Expression::Mul(op) => Ok(Expression::Mul(Box::new(BinaryOp {
1021                left: wrap_if_json_arrow(op.left),
1022                right: wrap_if_json_arrow(op.right),
1023                ..*op
1024            }))),
1025            Expression::Div(op) => Ok(Expression::Div(Box::new(BinaryOp {
1026                left: wrap_if_json_arrow(op.left),
1027                right: wrap_if_json_arrow(op.right),
1028                ..*op
1029            }))),
1030            Expression::Mod(op) => Ok(Expression::Mod(Box::new(BinaryOp {
1031                left: wrap_if_json_arrow(op.left),
1032                right: wrap_if_json_arrow(op.right),
1033                ..*op
1034            }))),
1035            Expression::Concat(op) => Ok(Expression::Concat(Box::new(BinaryOp {
1036                left: wrap_if_json_arrow(op.left),
1037                right: wrap_if_json_arrow(op.right),
1038                ..*op
1039            }))),
1040
1041            // In expression - wrap the this part if it's JSON arrow
1042            // Also transform `expr NOT IN (list)` to `NOT (expr) IN (list)` for DuckDB
1043            Expression::In(mut i) => {
1044                i.this = wrap_if_json_arrow(i.this);
1045                if i.not {
1046                    // Transform `expr NOT IN (list)` to `NOT (expr) IN (list)`
1047                    i.not = false;
1048                    Ok(Expression::Not(Box::new(crate::expressions::UnaryOp {
1049                        this: Expression::In(i),
1050                        inferred_type: None,
1051                    })))
1052                } else {
1053                    Ok(Expression::In(i))
1054                }
1055            }
1056
1057            // Not expression - wrap the this part if it's JSON arrow
1058            Expression::Not(mut n) => {
1059                n.this = wrap_if_json_arrow(n.this);
1060                Ok(Expression::Not(n))
1061            }
1062
1063            // WithinGroup: PERCENTILE_CONT/DISC WITHIN GROUP (ORDER BY ...) -> QUANTILE_CONT/DISC(col, quantile ORDER BY ...)
1064            Expression::WithinGroup(wg) => {
1065                match &wg.this {
1066                    Expression::PercentileCont(p) => {
1067                        let column = wg
1068                            .order_by
1069                            .first()
1070                            .map(|o| o.this.clone())
1071                            .unwrap_or_else(|| p.this.clone());
1072                        let percentile = p.percentile.clone();
1073                        let filter = p.filter.clone();
1074                        Ok(Expression::AggregateFunction(Box::new(
1075                            crate::expressions::AggregateFunction {
1076                                name: "QUANTILE_CONT".to_string(),
1077                                args: vec![column, percentile],
1078                                distinct: false,
1079                                filter,
1080                                order_by: wg.order_by,
1081                                limit: None,
1082                                ignore_nulls: None,
1083                                inferred_type: None,
1084                            },
1085                        )))
1086                    }
1087                    Expression::PercentileDisc(p) => {
1088                        let column = wg
1089                            .order_by
1090                            .first()
1091                            .map(|o| o.this.clone())
1092                            .unwrap_or_else(|| p.this.clone());
1093                        let percentile = p.percentile.clone();
1094                        let filter = p.filter.clone();
1095                        Ok(Expression::AggregateFunction(Box::new(
1096                            crate::expressions::AggregateFunction {
1097                                name: "QUANTILE_DISC".to_string(),
1098                                args: vec![column, percentile],
1099                                distinct: false,
1100                                filter,
1101                                order_by: wg.order_by,
1102                                limit: None,
1103                                ignore_nulls: None,
1104                                inferred_type: None,
1105                            },
1106                        )))
1107                    }
1108                    // Handle case where inner is AggregateFunction with PERCENTILE_CONT/DISC name
1109                    Expression::AggregateFunction(af)
1110                        if af.name == "PERCENTILE_CONT" || af.name == "PERCENTILE_DISC" =>
1111                    {
1112                        let new_name = if af.name == "PERCENTILE_CONT" {
1113                            "QUANTILE_CONT"
1114                        } else {
1115                            "QUANTILE_DISC"
1116                        };
1117                        let column = wg.order_by.first().map(|o| o.this.clone());
1118                        let quantile = af.args.first().cloned();
1119                        match (column, quantile) {
1120                            (Some(col), Some(q)) => Ok(Expression::AggregateFunction(Box::new(
1121                                crate::expressions::AggregateFunction {
1122                                    name: new_name.to_string(),
1123                                    args: vec![col, q],
1124                                    distinct: false,
1125                                    filter: af.filter.clone(),
1126                                    order_by: wg.order_by,
1127                                    limit: None,
1128                                    ignore_nulls: None,
1129                                    inferred_type: None,
1130                                },
1131                            ))),
1132                            _ => Ok(Expression::WithinGroup(wg)),
1133                        }
1134                    }
1135                    _ => Ok(Expression::WithinGroup(wg)),
1136                }
1137            }
1138
1139            // ===== DuckDB @ prefix operator → ABS() =====
1140            // In DuckDB, @expr means ABS(expr)
1141            // Parser creates Column with name "@col" — strip the @ and wrap in ABS()
1142            Expression::Column(ref c) if c.name.name.starts_with('@') && c.table.is_none() => {
1143                let col_name = &c.name.name[1..]; // strip leading @
1144                Ok(Expression::Abs(Box::new(UnaryFunc {
1145                    this: Expression::boxed_column(Column {
1146                        name: Identifier::new(col_name),
1147                        table: None,
1148                        join_mark: false,
1149                        trailing_comments: Vec::new(),
1150                        span: None,
1151                        inferred_type: None,
1152                    }),
1153                    original_name: None,
1154                    inferred_type: None,
1155                })))
1156            }
1157
1158            // ===== SELECT-level transforms =====
1159            // DuckDB colon alias syntax: `foo: bar` → `bar AS foo`
1160            // Parser creates JSONExtract(this=foo, expression='bar', variant_extract=true)
1161            // which needs to become Alias(this=Column(bar), alias=foo)
1162            Expression::Select(mut select) => {
1163                select.expressions = select
1164                    .expressions
1165                    .into_iter()
1166                    .map(|e| {
1167                        match e {
1168                            Expression::JSONExtract(ref je) if je.variant_extract.is_some() => {
1169                                // JSONExtract(this=alias_name, expression='value', variant_extract=true) → value AS alias_name
1170                                let alias_ident = match je.this.as_ref() {
1171                                    Expression::Identifier(ident) => Some(ident.clone()),
1172                                    Expression::Column(col) if col.table.is_none() => {
1173                                        Some(col.name.clone())
1174                                    }
1175                                    _ => None,
1176                                };
1177                                let value_expr = match je.expression.as_ref() {
1178                                    Expression::Literal(lit)
1179                                        if matches!(lit.as_ref(), Literal::String(_)) =>
1180                                    {
1181                                        let Literal::String(s) = lit.as_ref() else {
1182                                            unreachable!()
1183                                        };
1184                                        // Convert string path to column reference
1185                                        if s.contains('.') {
1186                                            // t.col → Column { name: col, table: t }
1187                                            let parts: Vec<&str> = s.splitn(2, '.').collect();
1188                                            Some(Expression::boxed_column(Column {
1189                                                name: Identifier::new(parts[1]),
1190                                                table: Some(Identifier::new(parts[0])),
1191                                                join_mark: false,
1192                                                trailing_comments: Vec::new(),
1193                                                span: None,
1194                                                inferred_type: None,
1195                                            }))
1196                                        } else {
1197                                            Some(Expression::boxed_column(Column {
1198                                                name: Identifier::new(s.as_str()),
1199                                                table: None,
1200                                                join_mark: false,
1201                                                trailing_comments: Vec::new(),
1202                                                span: None,
1203                                                inferred_type: None,
1204                                            }))
1205                                        }
1206                                    }
1207                                    _ => None,
1208                                };
1209
1210                                if let (Some(alias), Some(value)) = (alias_ident, value_expr) {
1211                                    Expression::Alias(Box::new(Alias {
1212                                        this: value,
1213                                        alias,
1214                                        column_aliases: Vec::new(),
1215                                        pre_alias_comments: Vec::new(),
1216                                        trailing_comments: Vec::new(),
1217                                        inferred_type: None,
1218                                    }))
1219                                } else {
1220                                    e
1221                                }
1222                            }
1223                            _ => e,
1224                        }
1225                    })
1226                    .collect();
1227
1228                // ===== DuckDB comma-join with UNNEST → JOIN ON TRUE =====
1229                // Transform FROM t1, UNNEST(...) AS t2 → FROM t1 JOIN UNNEST(...) AS t2 ON TRUE
1230                if let Some(ref mut from) = select.from {
1231                    if from.expressions.len() > 1 {
1232                        // Check if any expression after the first is UNNEST or Alias wrapping UNNEST
1233                        let mut new_from_exprs = Vec::new();
1234                        let mut new_joins = Vec::new();
1235
1236                        for (idx, expr) in from.expressions.drain(..).enumerate() {
1237                            if idx == 0 {
1238                                // First expression stays in FROM
1239                                new_from_exprs.push(expr);
1240                            } else {
1241                                // Check if this is UNNEST or Alias(UNNEST)
1242                                let is_unnest = match &expr {
1243                                    Expression::Unnest(_) => true,
1244                                    Expression::Alias(a) => matches!(a.this, Expression::Unnest(_)),
1245                                    _ => false,
1246                                };
1247
1248                                if is_unnest {
1249                                    // Convert to JOIN ON TRUE
1250                                    new_joins.push(crate::expressions::Join {
1251                                        this: expr,
1252                                        on: Some(Expression::Boolean(
1253                                            crate::expressions::BooleanLiteral { value: true },
1254                                        )),
1255                                        using: Vec::new(),
1256                                        kind: crate::expressions::JoinKind::Inner,
1257                                        use_inner_keyword: false,
1258                                        use_outer_keyword: false,
1259                                        deferred_condition: false,
1260                                        join_hint: None,
1261                                        match_condition: None,
1262                                        pivots: Vec::new(),
1263                                        comments: Vec::new(),
1264                                        nesting_group: 0,
1265                                        directed: false,
1266                                    });
1267                                } else {
1268                                    // Keep non-UNNEST expressions in FROM (comma-separated)
1269                                    new_from_exprs.push(expr);
1270                                }
1271                            }
1272                        }
1273
1274                        from.expressions = new_from_exprs;
1275
1276                        // Prepend the new joins before any existing joins
1277                        new_joins.append(&mut select.joins);
1278                        select.joins = new_joins;
1279                    }
1280                }
1281
1282                Ok(Expression::Select(select))
1283            }
1284
1285            // ===== INTERVAL splitting =====
1286            // DuckDB requires INTERVAL '1' HOUR format, not INTERVAL '1 hour'
1287            // When we have INTERVAL 'value unit' (single string with embedded unit),
1288            // split it into INTERVAL 'value' UNIT
1289            Expression::Interval(interval) => self.transform_interval(*interval),
1290
1291            // DuckDB CREATE FUNCTION (macro syntax): strip param types, suppress RETURNS
1292            Expression::CreateFunction(mut cf) => {
1293                // Strip parameter data types (DuckDB macros don't use types)
1294                cf.parameters = cf
1295                    .parameters
1296                    .into_iter()
1297                    .map(|p| FunctionParameter {
1298                        name: p.name,
1299                        data_type: DataType::Custom {
1300                            name: String::new(),
1301                        },
1302                        mode: None,
1303                        default: p.default,
1304                        mode_text: None,
1305                    })
1306                    .collect();
1307
1308                // For DuckDB macro syntax: suppress RETURNS output
1309                // Use a marker in returns_table_body to signal TABLE keyword in body
1310                let was_table_return = cf.returns_table_body.is_some()
1311                    || matches!(&cf.return_type, Some(DataType::Custom { ref name }) if name == "TABLE");
1312                cf.return_type = None;
1313                if was_table_return {
1314                    // Use empty marker to signal TABLE return without outputting RETURNS
1315                    cf.returns_table_body = Some(String::new());
1316                } else {
1317                    cf.returns_table_body = None;
1318                }
1319
1320                Ok(Expression::CreateFunction(cf))
1321            }
1322
1323            // ===== Snowflake-specific expression type transforms =====
1324
1325            // IFF(cond, true_val, false_val) -> CASE WHEN cond THEN true_val ELSE false_val END
1326            Expression::IfFunc(f) => Ok(Expression::Case(Box::new(Case {
1327                operand: None,
1328                whens: vec![(f.condition, f.true_value)],
1329                else_: f.false_value,
1330                comments: Vec::new(),
1331                inferred_type: None,
1332            }))),
1333
1334            // VAR_SAMP -> VARIANCE in DuckDB
1335            Expression::VarSamp(f) => Ok(Expression::Function(Box::new(Function::new(
1336                "VARIANCE".to_string(),
1337                vec![f.this],
1338            )))),
1339
1340            // NVL2(expr, val_if_not_null, val_if_null) -> CASE WHEN expr IS NOT NULL THEN val_if_not_null ELSE val_if_null END
1341            Expression::Nvl2(f) => {
1342                let condition = Expression::IsNull(Box::new(crate::expressions::IsNull {
1343                    this: f.this,
1344                    not: true,
1345                    postfix_form: false,
1346                }));
1347                Ok(Expression::Case(Box::new(Case {
1348                    operand: None,
1349                    whens: vec![(condition, f.true_value)],
1350                    else_: Some(f.false_value),
1351                    comments: Vec::new(),
1352                    inferred_type: None,
1353                })))
1354            }
1355
1356            // Pass through everything else
1357            _ => Ok(expr),
1358        }
1359    }
1360}
1361
1362impl DuckDBDialect {
1363    /// Extract a numeric value from a literal expression, if possible
1364    fn extract_number_value(expr: &Expression) -> Option<f64> {
1365        match expr {
1366            Expression::Literal(lit) if matches!(lit.as_ref(), Literal::Number(_)) => {
1367                let Literal::Number(n) = lit.as_ref() else {
1368                    unreachable!()
1369                };
1370                n.parse::<f64>().ok()
1371            }
1372            _ => None,
1373        }
1374    }
1375
1376    /// Convert an expression to a SQL string for template-based transformations
1377    fn expr_to_sql(expr: &Expression) -> String {
1378        crate::generator::Generator::sql(expr).unwrap_or_default()
1379    }
1380
1381    /// Extract the seed expression for random-based function emulations.
1382    /// Returns (seed_sql, is_random_no_seed) where:
1383    /// - For RANDOM(): ("RANDOM()", true)
1384    /// - For RANDOM(seed): ("seed", false) - extracts the seed
1385    /// - For literal seed: ("seed_value", false)
1386    fn extract_seed_info(gen: &Expression) -> (String, bool) {
1387        match gen {
1388            Expression::Function(func) if func.name.to_uppercase() == "RANDOM" => {
1389                if func.args.is_empty() {
1390                    ("RANDOM()".to_string(), true)
1391                } else {
1392                    // RANDOM(seed) -> extract the seed
1393                    (Self::expr_to_sql(&func.args[0]), false)
1394                }
1395            }
1396            Expression::Rand(r) => {
1397                if let Some(ref seed) = r.seed {
1398                    // RANDOM(seed) / RAND(seed) -> extract the seed
1399                    (Self::expr_to_sql(seed), false)
1400                } else {
1401                    ("RANDOM()".to_string(), true)
1402                }
1403            }
1404            Expression::Random(_) => ("RANDOM()".to_string(), true),
1405            _ => (Self::expr_to_sql(gen), false),
1406        }
1407    }
1408
1409    /// Parse a SQL template string and wrap it in a Subquery (parenthesized expression).
1410    /// Uses a thread with larger stack to handle deeply nested template SQL in debug builds.
1411    fn parse_as_subquery(sql: &str) -> Result<Expression> {
1412        let sql_owned = sql.to_string();
1413        let handle = std::thread::Builder::new()
1414            .stack_size(16 * 1024 * 1024) // 16MB stack for complex templates
1415            .spawn(move || match crate::parser::Parser::parse_sql(&sql_owned) {
1416                Ok(stmts) => {
1417                    if let Some(stmt) = stmts.into_iter().next() {
1418                        Ok(Expression::Subquery(Box::new(Subquery {
1419                            this: stmt,
1420                            alias: None,
1421                            column_aliases: Vec::new(),
1422                            order_by: None,
1423                            limit: None,
1424                            offset: None,
1425                            distribute_by: None,
1426                            sort_by: None,
1427                            cluster_by: None,
1428                            lateral: false,
1429                            modifiers_inside: false,
1430                            trailing_comments: Vec::new(),
1431                            inferred_type: None,
1432                        })))
1433                    } else {
1434                        Err(crate::error::Error::Generate(
1435                            "Failed to parse template SQL".to_string(),
1436                        ))
1437                    }
1438                }
1439                Err(e) => Err(e),
1440            })
1441            .map_err(|e| {
1442                crate::error::Error::Internal(format!("Failed to spawn parser thread: {}", e))
1443            })?;
1444
1445        handle
1446            .join()
1447            .map_err(|_| crate::error::Error::Internal("Parser thread panicked".to_string()))?
1448    }
1449
1450    /// Normalize CAST({} AS MAP(...)) style expressions to CAST(MAP() AS MAP(...)).
1451    fn normalize_empty_map_expr(expr: Expression) -> Expression {
1452        match expr {
1453            Expression::Cast(mut c) if matches!(&c.to, DataType::Map { .. }) => {
1454                if matches!(&c.this, Expression::Struct(s) if s.fields.is_empty()) {
1455                    c.this =
1456                        Expression::Function(Box::new(Function::new("MAP".to_string(), vec![])));
1457                }
1458                Expression::Cast(c)
1459            }
1460            other => other,
1461        }
1462    }
1463
1464    /// Convert bracket notation ["key with spaces"] to quoted dot notation ."key with spaces"
1465    /// in JSON path strings. This is needed because Snowflake uses bracket notation for keys
1466    /// with special characters, but DuckDB uses quoted dot notation.
1467    fn convert_bracket_to_quoted_path(path: &str) -> String {
1468        let mut result = String::new();
1469        let mut chars = path.chars().peekable();
1470        while let Some(c) = chars.next() {
1471            if c == '[' && chars.peek() == Some(&'"') {
1472                // Found [" - start of bracket notation
1473                chars.next(); // consume "
1474                let mut key = String::new();
1475                while let Some(kc) = chars.next() {
1476                    if kc == '"' && chars.peek() == Some(&']') {
1477                        chars.next(); // consume ]
1478                        break;
1479                    }
1480                    key.push(kc);
1481                }
1482                // Convert to quoted dot notation: ."key"
1483                if !result.is_empty() && !result.ends_with('.') {
1484                    result.push('.');
1485                }
1486                result.push('"');
1487                result.push_str(&key);
1488                result.push('"');
1489            } else {
1490                result.push(c);
1491            }
1492        }
1493        result
1494    }
1495
1496    /// Transform data types according to DuckDB TYPE_MAPPING
1497    fn transform_data_type(&self, dt: crate::expressions::DataType) -> Result<Expression> {
1498        use crate::expressions::DataType;
1499        let transformed = match dt {
1500            // BINARY -> VarBinary (DuckDB generator maps VarBinary to BLOB), preserving length
1501            DataType::Binary { length } => DataType::VarBinary { length },
1502            // BLOB -> VarBinary (DuckDB generator maps VarBinary to BLOB)
1503            // This matches Python sqlglot's DuckDB parser mapping BLOB -> VARBINARY
1504            DataType::Blob => DataType::VarBinary { length: None },
1505            // CHAR/VARCHAR: Keep as-is, DuckDB generator maps to TEXT with length
1506            DataType::Char { .. } | DataType::VarChar { .. } => dt,
1507            // FLOAT -> REAL (use real_spelling flag so generator can decide)
1508            DataType::Float {
1509                precision, scale, ..
1510            } => DataType::Float {
1511                precision,
1512                scale,
1513                real_spelling: true,
1514            },
1515            // JSONB -> JSON
1516            DataType::JsonB => DataType::Json,
1517            // Handle Custom type aliases used in DuckDB
1518            DataType::Custom { ref name } => {
1519                let upper = name.to_uppercase();
1520                match upper.as_str() {
1521                    // INT64 -> BIGINT
1522                    "INT64" | "INT8" => DataType::BigInt { length: None },
1523                    // INT32, INT4, SIGNED -> INT
1524                    "INT32" | "INT4" | "SIGNED" => DataType::Int {
1525                        length: None,
1526                        integer_spelling: false,
1527                    },
1528                    // INT16 -> SMALLINT
1529                    "INT16" => DataType::SmallInt { length: None },
1530                    // INT1 -> TINYINT
1531                    "INT1" => DataType::TinyInt { length: None },
1532                    // HUGEINT -> INT128
1533                    "HUGEINT" => DataType::Custom {
1534                        name: "INT128".to_string(),
1535                    },
1536                    // UHUGEINT -> UINT128
1537                    "UHUGEINT" => DataType::Custom {
1538                        name: "UINT128".to_string(),
1539                    },
1540                    // BPCHAR -> TEXT
1541                    "BPCHAR" => DataType::Text,
1542                    // CHARACTER VARYING, CHAR VARYING -> TEXT
1543                    "CHARACTER VARYING" | "CHAR VARYING" => DataType::Text,
1544                    // FLOAT4, REAL -> REAL
1545                    "FLOAT4" => DataType::Custom {
1546                        name: "REAL".to_string(),
1547                    },
1548                    // LOGICAL -> BOOLEAN
1549                    "LOGICAL" => DataType::Boolean,
1550                    // TIMESTAMPNTZ / TIMESTAMP_NTZ -> TIMESTAMP
1551                    "TIMESTAMPNTZ" | "TIMESTAMP_NTZ" => DataType::Timestamp {
1552                        precision: None,
1553                        timezone: false,
1554                    },
1555                    // TIMESTAMP_US -> TIMESTAMP (DuckDB's default timestamp is microsecond precision)
1556                    "TIMESTAMP_US" => DataType::Timestamp {
1557                        precision: None,
1558                        timezone: false,
1559                    },
1560                    // TIMESTAMPLTZ / TIMESTAMPTZ / TIMESTAMP_LTZ / TIMESTAMP_TZ -> TIMESTAMPTZ
1561                    "TIMESTAMPLTZ" | "TIMESTAMP_LTZ" | "TIMESTAMPTZ" | "TIMESTAMP_TZ" => {
1562                        DataType::Timestamp {
1563                            precision: None,
1564                            timezone: true,
1565                        }
1566                    }
1567                    // DECFLOAT -> DECIMAL(38, 5) in DuckDB
1568                    "DECFLOAT" => DataType::Decimal {
1569                        precision: Some(38),
1570                        scale: Some(5),
1571                    },
1572                    // Keep other custom types as-is
1573                    _ => dt,
1574                }
1575            }
1576            // Keep all other types as-is
1577            other => other,
1578        };
1579        Ok(Expression::DataType(transformed))
1580    }
1581
1582    /// Transform interval to split embedded value+unit strings (e.g., '1 hour' -> '1' HOUR)
1583    /// DuckDB requires INTERVAL 'value' UNIT format, not INTERVAL 'value unit' format
1584    fn transform_interval(&self, interval: Interval) -> Result<Expression> {
1585        // Only transform if:
1586        // 1. There's a string literal value
1587        // 2. There's no unit already specified
1588        if interval.unit.is_some() {
1589            // Already has a unit, keep as-is
1590            return Ok(Expression::Interval(Box::new(interval)));
1591        }
1592
1593        if let Some(Expression::Literal(ref lit)) = interval.this {
1594            if let Literal::String(ref s) = lit.as_ref() {
1595                // Try to parse the string as "value unit" format
1596                if let Some((value, unit)) = Self::parse_interval_string(s) {
1597                    // Create new interval with separated value and unit
1598                    return Ok(Expression::Interval(Box::new(Interval {
1599                        this: Some(Expression::Literal(Box::new(Literal::String(
1600                            value.to_string(),
1601                        )))),
1602                        unit: Some(IntervalUnitSpec::Simple {
1603                            unit,
1604                            use_plural: false, // DuckDB uses singular form
1605                        }),
1606                    })));
1607                }
1608            }
1609        }
1610
1611        // No transformation needed
1612        Ok(Expression::Interval(Box::new(interval)))
1613    }
1614
1615    /// Parse an interval string like "1 hour" into (value, unit)
1616    /// Returns None if the string doesn't match the expected format
1617    fn parse_interval_string(s: &str) -> Option<(&str, IntervalUnit)> {
1618        let s = s.trim();
1619
1620        // Find where the number ends and the unit begins
1621        // Number can be: optional -, digits, optional decimal point, more digits
1622        let mut num_end = 0;
1623        let mut chars = s.chars().peekable();
1624
1625        // Skip leading minus
1626        if chars.peek() == Some(&'-') {
1627            chars.next();
1628            num_end += 1;
1629        }
1630
1631        // Skip digits
1632        while let Some(&c) = chars.peek() {
1633            if c.is_ascii_digit() {
1634                chars.next();
1635                num_end += 1;
1636            } else {
1637                break;
1638            }
1639        }
1640
1641        // Skip optional decimal point and more digits
1642        if chars.peek() == Some(&'.') {
1643            chars.next();
1644            num_end += 1;
1645            while let Some(&c) = chars.peek() {
1646                if c.is_ascii_digit() {
1647                    chars.next();
1648                    num_end += 1;
1649                } else {
1650                    break;
1651                }
1652            }
1653        }
1654
1655        if num_end == 0 || (num_end == 1 && s.starts_with('-')) {
1656            return None; // No number found
1657        }
1658
1659        let value = &s[..num_end];
1660        let rest = s[num_end..].trim();
1661
1662        // Rest should be alphabetic (the unit)
1663        if rest.is_empty() || !rest.chars().all(|c| c.is_ascii_alphabetic()) {
1664            return None;
1665        }
1666
1667        // Map unit string to IntervalUnit
1668        let unit = match rest.to_uppercase().as_str() {
1669            "YEAR" | "YEARS" | "Y" => IntervalUnit::Year,
1670            "MONTH" | "MONTHS" | "MON" | "MONS" => IntervalUnit::Month,
1671            "DAY" | "DAYS" | "D" => IntervalUnit::Day,
1672            "HOUR" | "HOURS" | "H" | "HR" | "HRS" => IntervalUnit::Hour,
1673            "MINUTE" | "MINUTES" | "MIN" | "MINS" | "M" => IntervalUnit::Minute,
1674            "SECOND" | "SECONDS" | "SEC" | "SECS" | "S" => IntervalUnit::Second,
1675            "MILLISECOND" | "MILLISECONDS" | "MS" => IntervalUnit::Millisecond,
1676            "MICROSECOND" | "MICROSECONDS" | "US" => IntervalUnit::Microsecond,
1677            "QUARTER" | "QUARTERS" | "Q" => IntervalUnit::Quarter,
1678            "WEEK" | "WEEKS" | "W" => IntervalUnit::Week,
1679            _ => return None, // Unknown unit
1680        };
1681
1682        Some((value, unit))
1683    }
1684
1685    fn transform_function(&self, f: Function) -> Result<Expression> {
1686        let name_upper = f.name.to_uppercase();
1687        match name_upper.as_str() {
1688            // IFNULL -> COALESCE
1689            "IFNULL" if f.args.len() == 2 => Ok(Expression::Coalesce(Box::new(VarArgFunc {
1690                original_name: None,
1691                expressions: f.args,
1692                inferred_type: None,
1693            }))),
1694
1695            // NVL -> COALESCE
1696            "NVL" if f.args.len() == 2 => Ok(Expression::Coalesce(Box::new(VarArgFunc {
1697                original_name: None,
1698                expressions: f.args,
1699                inferred_type: None,
1700            }))),
1701
1702            // ISNULL -> COALESCE
1703            "ISNULL" if f.args.len() == 2 => Ok(Expression::Coalesce(Box::new(VarArgFunc {
1704                original_name: None,
1705                expressions: f.args,
1706                inferred_type: None,
1707            }))),
1708
1709            // ARRAY_COMPACT(arr) -> LIST_FILTER(arr, _u -> NOT _u IS NULL)
1710            "ARRAY_COMPACT" if f.args.len() == 1 => {
1711                let arr = f.args.into_iter().next().unwrap();
1712                let lambda = Expression::Lambda(Box::new(crate::expressions::LambdaExpr {
1713                    parameters: vec![Identifier::new("_u".to_string())],
1714                    body: Expression::Not(Box::new(crate::expressions::UnaryOp {
1715                        this: Expression::IsNull(Box::new(crate::expressions::IsNull {
1716                            this: Expression::boxed_column(Column {
1717                                table: None,
1718                                name: Identifier::new("_u".to_string()),
1719                                join_mark: false,
1720                                trailing_comments: Vec::new(),
1721                                span: None,
1722                                inferred_type: None,
1723                            }),
1724                            not: false,
1725                            postfix_form: false,
1726                        })),
1727                        inferred_type: None,
1728                    })),
1729                    colon: false,
1730                    parameter_types: Vec::new(),
1731                }));
1732                Ok(Expression::Function(Box::new(Function::new(
1733                    "LIST_FILTER".to_string(),
1734                    vec![arr, lambda],
1735                ))))
1736            }
1737
1738            // ARRAY_CONSTRUCT_COMPACT: handled in the generator (to avoid source-transform interference)
1739            "ARRAY_CONSTRUCT_COMPACT" => Ok(Expression::Function(Box::new(f))),
1740
1741            // GROUP_CONCAT -> LISTAGG in DuckDB
1742            "GROUP_CONCAT" if !f.args.is_empty() => Ok(Expression::Function(Box::new(
1743                Function::new("LISTAGG".to_string(), f.args),
1744            ))),
1745
1746            // LISTAGG is native to DuckDB
1747            "LISTAGG" => Ok(Expression::Function(Box::new(f))),
1748
1749            // STRING_AGG -> LISTAGG in DuckDB
1750            "STRING_AGG" if !f.args.is_empty() => Ok(Expression::Function(Box::new(
1751                Function::new("LISTAGG".to_string(), f.args),
1752            ))),
1753
1754            // ARRAY_UNIQUE_AGG(col) -> LIST(DISTINCT col) FILTER(WHERE NOT col IS NULL)
1755            "ARRAY_UNIQUE_AGG" if f.args.len() == 1 => {
1756                let col = f.args.into_iter().next().unwrap();
1757                // NOT col IS NULL
1758                let filter_expr = Expression::Not(Box::new(UnaryOp {
1759                    this: Expression::IsNull(Box::new(IsNull {
1760                        this: col.clone(),
1761                        not: false,
1762                        postfix_form: false,
1763                    })),
1764                    inferred_type: None,
1765                }));
1766                Ok(Expression::ArrayAgg(Box::new(AggFunc {
1767                    this: col,
1768                    distinct: true,
1769                    filter: Some(filter_expr),
1770                    order_by: Vec::new(),
1771                    name: Some("LIST".to_string()),
1772                    ignore_nulls: None,
1773                    having_max: None,
1774                    limit: None,
1775                    inferred_type: None,
1776                })))
1777            }
1778
1779            // CHECK_JSON(x) -> CASE WHEN x IS NULL OR x = '' OR JSON_VALID(x) THEN NULL ELSE 'Invalid JSON' END
1780            "CHECK_JSON" if f.args.len() == 1 => {
1781                let x = f.args.into_iter().next().unwrap();
1782                // x IS NULL
1783                let is_null = Expression::IsNull(Box::new(IsNull {
1784                    this: x.clone(),
1785                    not: false,
1786                    postfix_form: false,
1787                }));
1788                // x = ''
1789                let eq_empty = Expression::Eq(Box::new(BinaryOp::new(
1790                    x.clone(),
1791                    Expression::Literal(Box::new(Literal::String(String::new()))),
1792                )));
1793                // JSON_VALID(x)
1794                let json_valid = Expression::Function(Box::new(Function::new(
1795                    "JSON_VALID".to_string(),
1796                    vec![x],
1797                )));
1798                // x IS NULL OR x = ''
1799                let or1 = Expression::Or(Box::new(BinaryOp::new(is_null, eq_empty)));
1800                // (x IS NULL OR x = '') OR JSON_VALID(x)
1801                let condition = Expression::Or(Box::new(BinaryOp::new(or1, json_valid)));
1802                Ok(Expression::Case(Box::new(Case {
1803                    operand: None,
1804                    whens: vec![(condition, Expression::Null(Null))],
1805                    else_: Some(Expression::Literal(Box::new(Literal::String(
1806                        "Invalid JSON".to_string(),
1807                    )))),
1808                    comments: Vec::new(),
1809                    inferred_type: None,
1810                })))
1811            }
1812
1813            // SUBSTR is native in DuckDB (keep as-is, don't convert to SUBSTRING)
1814            "SUBSTR" => Ok(Expression::Function(Box::new(f))),
1815
1816            // FLATTEN -> UNNEST in DuckDB
1817            "FLATTEN" => Ok(Expression::Function(Box::new(Function::new(
1818                "UNNEST".to_string(),
1819                f.args,
1820            )))),
1821
1822            // ARRAY_FLATTEN -> FLATTEN in DuckDB
1823            "ARRAY_FLATTEN" => Ok(Expression::Function(Box::new(Function::new(
1824                "FLATTEN".to_string(),
1825                f.args,
1826            )))),
1827
1828            // RPAD with 2 args -> RPAD with 3 args (default padding ' ')
1829            "RPAD" if f.args.len() == 2 => {
1830                let mut args = f.args;
1831                args.push(Expression::Literal(Box::new(Literal::String(
1832                    " ".to_string(),
1833                ))));
1834                Ok(Expression::Function(Box::new(Function::new(
1835                    "RPAD".to_string(),
1836                    args,
1837                ))))
1838            }
1839
1840            // BASE64_DECODE_STRING(x) -> DECODE(FROM_BASE64(x))
1841            // BASE64_DECODE_STRING(x, alphabet) -> DECODE(FROM_BASE64(REPLACE(REPLACE(REPLACE(x, '-', '+'), '_', '/'), '+', '=')))
1842            "BASE64_DECODE_STRING" => {
1843                let mut args = f.args;
1844                let input = args.remove(0);
1845                let has_alphabet = !args.is_empty();
1846                let decoded_input = if has_alphabet {
1847                    // Apply alphabet replacements: REPLACE(REPLACE(REPLACE(x, '-', '+'), '_', '/'), '+', '=')
1848                    let r1 = Expression::Function(Box::new(Function::new(
1849                        "REPLACE".to_string(),
1850                        vec![
1851                            input,
1852                            Expression::Literal(Box::new(Literal::String("-".to_string()))),
1853                            Expression::Literal(Box::new(Literal::String("+".to_string()))),
1854                        ],
1855                    )));
1856                    let r2 = Expression::Function(Box::new(Function::new(
1857                        "REPLACE".to_string(),
1858                        vec![
1859                            r1,
1860                            Expression::Literal(Box::new(Literal::String("_".to_string()))),
1861                            Expression::Literal(Box::new(Literal::String("/".to_string()))),
1862                        ],
1863                    )));
1864                    Expression::Function(Box::new(Function::new(
1865                        "REPLACE".to_string(),
1866                        vec![
1867                            r2,
1868                            Expression::Literal(Box::new(Literal::String("+".to_string()))),
1869                            Expression::Literal(Box::new(Literal::String("=".to_string()))),
1870                        ],
1871                    )))
1872                } else {
1873                    input
1874                };
1875                let from_base64 = Expression::Function(Box::new(Function::new(
1876                    "FROM_BASE64".to_string(),
1877                    vec![decoded_input],
1878                )));
1879                Ok(Expression::Function(Box::new(Function::new(
1880                    "DECODE".to_string(),
1881                    vec![from_base64],
1882                ))))
1883            }
1884
1885            // BASE64_DECODE_BINARY(x) -> FROM_BASE64(x)
1886            // BASE64_DECODE_BINARY(x, alphabet) -> FROM_BASE64(REPLACE(REPLACE(REPLACE(x, '-', '+'), '_', '/'), '+', '='))
1887            "BASE64_DECODE_BINARY" => {
1888                let mut args = f.args;
1889                let input = args.remove(0);
1890                let has_alphabet = !args.is_empty();
1891                let decoded_input = if has_alphabet {
1892                    let r1 = Expression::Function(Box::new(Function::new(
1893                        "REPLACE".to_string(),
1894                        vec![
1895                            input,
1896                            Expression::Literal(Box::new(Literal::String("-".to_string()))),
1897                            Expression::Literal(Box::new(Literal::String("+".to_string()))),
1898                        ],
1899                    )));
1900                    let r2 = Expression::Function(Box::new(Function::new(
1901                        "REPLACE".to_string(),
1902                        vec![
1903                            r1,
1904                            Expression::Literal(Box::new(Literal::String("_".to_string()))),
1905                            Expression::Literal(Box::new(Literal::String("/".to_string()))),
1906                        ],
1907                    )));
1908                    Expression::Function(Box::new(Function::new(
1909                        "REPLACE".to_string(),
1910                        vec![
1911                            r2,
1912                            Expression::Literal(Box::new(Literal::String("+".to_string()))),
1913                            Expression::Literal(Box::new(Literal::String("=".to_string()))),
1914                        ],
1915                    )))
1916                } else {
1917                    input
1918                };
1919                Ok(Expression::Function(Box::new(Function::new(
1920                    "FROM_BASE64".to_string(),
1921                    vec![decoded_input],
1922                ))))
1923            }
1924
1925            // SPACE(n) -> REPEAT(' ', CAST(n AS BIGINT))
1926            "SPACE" if f.args.len() == 1 => {
1927                let arg = f.args.into_iter().next().unwrap();
1928                let cast_arg = Expression::Cast(Box::new(Cast {
1929                    this: arg,
1930                    to: DataType::BigInt { length: None },
1931                    trailing_comments: Vec::new(),
1932                    double_colon_syntax: false,
1933                    format: None,
1934                    default: None,
1935                    inferred_type: None,
1936                }));
1937                Ok(Expression::Function(Box::new(Function::new(
1938                    "REPEAT".to_string(),
1939                    vec![
1940                        Expression::Literal(Box::new(Literal::String(" ".to_string()))),
1941                        cast_arg,
1942                    ],
1943                ))))
1944            }
1945
1946            // IS_ARRAY(x) -> JSON_TYPE(x) = 'ARRAY'
1947            "IS_ARRAY" if f.args.len() == 1 => {
1948                let arg = f.args.into_iter().next().unwrap();
1949                let json_type = Expression::Function(Box::new(Function::new(
1950                    "JSON_TYPE".to_string(),
1951                    vec![arg],
1952                )));
1953                Ok(Expression::Eq(Box::new(BinaryOp {
1954                    left: json_type,
1955                    right: Expression::Literal(Box::new(Literal::String("ARRAY".to_string()))),
1956                    left_comments: Vec::new(),
1957                    operator_comments: Vec::new(),
1958                    trailing_comments: Vec::new(),
1959                    inferred_type: None,
1960                })))
1961            }
1962
1963            // EXPLODE -> UNNEST
1964            "EXPLODE" => Ok(Expression::Function(Box::new(Function::new(
1965                "UNNEST".to_string(),
1966                f.args,
1967            )))),
1968
1969            // NOW -> CURRENT_TIMESTAMP
1970            "NOW" => Ok(Expression::CurrentTimestamp(
1971                crate::expressions::CurrentTimestamp {
1972                    precision: None,
1973                    sysdate: false,
1974                },
1975            )),
1976
1977            // GETDATE -> CURRENT_TIMESTAMP
1978            "GETDATE" => Ok(Expression::CurrentTimestamp(
1979                crate::expressions::CurrentTimestamp {
1980                    precision: None,
1981                    sysdate: false,
1982                },
1983            )),
1984
1985            // TODAY -> CURRENT_DATE
1986            "TODAY" => Ok(Expression::CurrentDate(crate::expressions::CurrentDate)),
1987
1988            // CURDATE -> CURRENT_DATE
1989            "CURDATE" => Ok(Expression::CurrentDate(crate::expressions::CurrentDate)),
1990
1991            // GET_CURRENT_TIME -> CURRENT_TIME
1992            "GET_CURRENT_TIME" => Ok(Expression::CurrentTime(crate::expressions::CurrentTime {
1993                precision: None,
1994            })),
1995
1996            // CURRENT_LOCALTIMESTAMP -> LOCALTIMESTAMP
1997            "CURRENT_LOCALTIMESTAMP" => Ok(Expression::Localtimestamp(Box::new(
1998                crate::expressions::Localtimestamp { this: None },
1999            ))),
2000
2001            // REGEXP_EXTRACT_ALL: strip default group_idx=0
2002            "REGEXP_EXTRACT_ALL" if f.args.len() == 3 => {
2003                // If third arg is literal 0, strip it
2004                if matches!(&f.args[2], Expression::Literal(lit) if matches!(lit.as_ref(), crate::expressions::Literal::Number(n) if n == "0"))
2005                {
2006                    Ok(Expression::Function(Box::new(Function::new(
2007                        "REGEXP_EXTRACT_ALL".to_string(),
2008                        vec![f.args[0].clone(), f.args[1].clone()],
2009                    ))))
2010                } else {
2011                    Ok(Expression::Function(Box::new(Function::new(
2012                        "REGEXP_EXTRACT_ALL".to_string(),
2013                        f.args,
2014                    ))))
2015                }
2016            }
2017
2018            // CURRENT_DATE is native
2019            "CURRENT_DATE" => Ok(Expression::CurrentDate(crate::expressions::CurrentDate)),
2020
2021            // TO_DATE with 1 arg -> CAST(x AS DATE)
2022            "TO_DATE" if f.args.len() == 1 => {
2023                let arg = f.args.into_iter().next().unwrap();
2024                Ok(Expression::Cast(Box::new(Cast {
2025                    this: arg,
2026                    to: DataType::Date,
2027                    trailing_comments: Vec::new(),
2028                    double_colon_syntax: false,
2029                    format: None,
2030                    default: None,
2031                    inferred_type: None,
2032                })))
2033            }
2034
2035            // TO_TIMESTAMP is native in DuckDB (kept as-is for identity)
2036
2037            // DATE_FORMAT -> STRFTIME in DuckDB with format conversion
2038            "DATE_FORMAT" if f.args.len() >= 2 => {
2039                let mut args = f.args;
2040                args[1] = Self::convert_format_to_duckdb(&args[1]);
2041                Ok(Expression::Function(Box::new(Function::new(
2042                    "STRFTIME".to_string(),
2043                    args,
2044                ))))
2045            }
2046
2047            // DATE_PARSE -> STRPTIME in DuckDB with format conversion
2048            "DATE_PARSE" if f.args.len() >= 2 => {
2049                let mut args = f.args;
2050                args[1] = Self::convert_format_to_duckdb(&args[1]);
2051                Ok(Expression::Function(Box::new(Function::new(
2052                    "STRPTIME".to_string(),
2053                    args,
2054                ))))
2055            }
2056
2057            // FORMAT_DATE -> STRFTIME in DuckDB
2058            "FORMAT_DATE" if f.args.len() >= 2 => {
2059                let mut args = f.args;
2060                args[1] = Self::convert_format_to_duckdb(&args[1]);
2061                Ok(Expression::Function(Box::new(Function::new(
2062                    "STRFTIME".to_string(),
2063                    args,
2064                ))))
2065            }
2066
2067            // TO_CHAR -> STRFTIME in DuckDB
2068            "TO_CHAR" if f.args.len() >= 2 => {
2069                let mut args = f.args;
2070                args[1] = Self::convert_format_to_duckdb(&args[1]);
2071                Ok(Expression::Function(Box::new(Function::new(
2072                    "STRFTIME".to_string(),
2073                    args,
2074                ))))
2075            }
2076
2077            // EPOCH_MS is native to DuckDB
2078            "EPOCH_MS" => Ok(Expression::Function(Box::new(f))),
2079
2080            // EPOCH -> EPOCH (native)
2081            "EPOCH" => Ok(Expression::Function(Box::new(f))),
2082
2083            // FROM_UNIXTIME -> TO_TIMESTAMP in DuckDB
2084            "FROM_UNIXTIME" if !f.args.is_empty() => Ok(Expression::Function(Box::new(
2085                Function::new("TO_TIMESTAMP".to_string(), f.args),
2086            ))),
2087
2088            // UNIX_TIMESTAMP -> EPOCH
2089            "UNIX_TIMESTAMP" => Ok(Expression::Function(Box::new(Function::new(
2090                "EPOCH".to_string(),
2091                f.args,
2092            )))),
2093
2094            // JSON_EXTRACT -> arrow operator (->)
2095            "JSON_EXTRACT" if f.args.len() == 2 => {
2096                let mut args = f.args;
2097                let path = args.pop().unwrap();
2098                let this = args.pop().unwrap();
2099                Ok(Expression::JsonExtract(Box::new(JsonExtractFunc {
2100                    this,
2101                    path,
2102                    returning: None,
2103                    arrow_syntax: true,
2104                    hash_arrow_syntax: false,
2105                    wrapper_option: None,
2106                    quotes_option: None,
2107                    on_scalar_string: false,
2108                    on_error: None,
2109                })))
2110            }
2111
2112            // JSON_EXTRACT_STRING -> arrow operator (->>)
2113            "JSON_EXTRACT_STRING" if f.args.len() == 2 => {
2114                let mut args = f.args;
2115                let path = args.pop().unwrap();
2116                let this = args.pop().unwrap();
2117                Ok(Expression::JsonExtractScalar(Box::new(JsonExtractFunc {
2118                    this,
2119                    path,
2120                    returning: None,
2121                    arrow_syntax: true,
2122                    hash_arrow_syntax: false,
2123                    wrapper_option: None,
2124                    quotes_option: None,
2125                    on_scalar_string: false,
2126                    on_error: None,
2127                })))
2128            }
2129
2130            // ARRAY_CONSTRUCT -> list_value or [a, b, c] syntax
2131            "ARRAY_CONSTRUCT" => Ok(Expression::Function(Box::new(Function::new(
2132                "list_value".to_string(),
2133                f.args,
2134            )))),
2135
2136            // ARRAY -> list_value
2137            // ARRAY -> list_value for non-subquery args, keep ARRAY for subquery args
2138            "ARRAY" => {
2139                // Check if any arg contains a query (subquery)
2140                let has_query = f
2141                    .args
2142                    .iter()
2143                    .any(|a| matches!(a, Expression::Subquery(_) | Expression::Select(_)));
2144                if has_query {
2145                    // Keep as ARRAY() for subquery args
2146                    Ok(Expression::Function(Box::new(Function::new(
2147                        "ARRAY".to_string(),
2148                        f.args,
2149                    ))))
2150                } else {
2151                    Ok(Expression::Function(Box::new(Function::new(
2152                        "list_value".to_string(),
2153                        f.args,
2154                    ))))
2155                }
2156            }
2157
2158            // LIST_VALUE -> Array literal notation [...]
2159            "LIST_VALUE" => Ok(Expression::Array(Box::new(crate::expressions::Array {
2160                expressions: f.args,
2161            }))),
2162
2163            // ARRAY_AGG -> LIST in DuckDB (or array_agg which is also supported)
2164            "ARRAY_AGG" => Ok(Expression::Function(Box::new(Function::new(
2165                "list".to_string(),
2166                f.args,
2167            )))),
2168
2169            // LIST_CONTAINS / ARRAY_CONTAINS -> keep normalized form
2170            "LIST_CONTAINS" | "ARRAY_CONTAINS" => Ok(Expression::Function(Box::new(
2171                Function::new("ARRAY_CONTAINS".to_string(), f.args),
2172            ))),
2173
2174            // ARRAY_SIZE/CARDINALITY -> ARRAY_LENGTH in DuckDB
2175            "ARRAY_SIZE" | "CARDINALITY" => Ok(Expression::Function(Box::new(Function::new(
2176                "ARRAY_LENGTH".to_string(),
2177                f.args,
2178            )))),
2179
2180            // LEN -> LENGTH
2181            "LEN" if f.args.len() == 1 => Ok(Expression::Length(Box::new(UnaryFunc::new(
2182                f.args.into_iter().next().unwrap(),
2183            )))),
2184
2185            // CEILING -> CEIL (both work)
2186            "CEILING" if f.args.len() == 1 => Ok(Expression::Ceil(Box::new(CeilFunc {
2187                this: f.args.into_iter().next().unwrap(),
2188                decimals: None,
2189                to: None,
2190            }))),
2191
2192            // LOGICAL_OR -> BOOL_OR with CAST to BOOLEAN
2193            "LOGICAL_OR" if f.args.len() == 1 => {
2194                let arg = f.args.into_iter().next().unwrap();
2195                Ok(Expression::Function(Box::new(Function::new(
2196                    "BOOL_OR".to_string(),
2197                    vec![Expression::Cast(Box::new(crate::expressions::Cast {
2198                        this: arg,
2199                        to: crate::expressions::DataType::Boolean,
2200                        trailing_comments: Vec::new(),
2201                        double_colon_syntax: false,
2202                        format: None,
2203                        default: None,
2204                        inferred_type: None,
2205                    }))],
2206                ))))
2207            }
2208
2209            // LOGICAL_AND -> BOOL_AND with CAST to BOOLEAN
2210            "LOGICAL_AND" if f.args.len() == 1 => {
2211                let arg = f.args.into_iter().next().unwrap();
2212                Ok(Expression::Function(Box::new(Function::new(
2213                    "BOOL_AND".to_string(),
2214                    vec![Expression::Cast(Box::new(crate::expressions::Cast {
2215                        this: arg,
2216                        to: crate::expressions::DataType::Boolean,
2217                        trailing_comments: Vec::new(),
2218                        double_colon_syntax: false,
2219                        format: None,
2220                        default: None,
2221                        inferred_type: None,
2222                    }))],
2223                ))))
2224            }
2225
2226            // REGEXP_LIKE -> REGEXP_MATCHES in DuckDB
2227            "REGEXP_LIKE" => Ok(Expression::Function(Box::new(Function::new(
2228                "REGEXP_MATCHES".to_string(),
2229                f.args,
2230            )))),
2231
2232            // POSITION is native
2233            "POSITION" => Ok(Expression::Function(Box::new(f))),
2234
2235            // CHARINDEX(substr, str) -> STRPOS(str, substr) in DuckDB
2236            "CHARINDEX" if f.args.len() == 2 => {
2237                let mut args = f.args;
2238                let substr = args.remove(0);
2239                let str_expr = args.remove(0);
2240                Ok(Expression::Function(Box::new(Function::new(
2241                    "STRPOS".to_string(),
2242                    vec![str_expr, substr],
2243                ))))
2244            }
2245
2246            // CHARINDEX(substr, str, pos) -> complex CASE expression for DuckDB
2247            // CASE WHEN STRPOS(SUBSTRING(str, CASE WHEN pos <= 0 THEN 1 ELSE pos END), substr) = 0
2248            // THEN 0
2249            // ELSE STRPOS(SUBSTRING(str, CASE WHEN pos <= 0 THEN 1 ELSE pos END), substr) + CASE WHEN pos <= 0 THEN 1 ELSE pos END - 1
2250            // END
2251            "CHARINDEX" if f.args.len() == 3 => {
2252                let mut args = f.args;
2253                let substr = args.remove(0);
2254                let str_expr = args.remove(0);
2255                let pos = args.remove(0);
2256
2257                let zero = Expression::Literal(Box::new(Literal::Number("0".to_string())));
2258                let one = Expression::Literal(Box::new(Literal::Number("1".to_string())));
2259
2260                // CASE WHEN pos <= 0 THEN 1 ELSE pos END
2261                let pos_case = Expression::Case(Box::new(Case {
2262                    operand: None,
2263                    whens: vec![(
2264                        Expression::Lte(Box::new(BinaryOp::new(pos.clone(), zero.clone()))),
2265                        one.clone(),
2266                    )],
2267                    else_: Some(pos.clone()),
2268                    comments: Vec::new(),
2269                    inferred_type: None,
2270                }));
2271
2272                // SUBSTRING(str, pos_case)
2273                let substring_expr = Expression::Substring(Box::new(SubstringFunc {
2274                    this: str_expr,
2275                    start: pos_case.clone(),
2276                    length: None,
2277                    from_for_syntax: false,
2278                }));
2279
2280                // STRPOS(SUBSTRING(...), substr)
2281                let strpos = Expression::Function(Box::new(Function::new(
2282                    "STRPOS".to_string(),
2283                    vec![substring_expr, substr],
2284                )));
2285
2286                // STRPOS(...) = 0
2287                let eq_zero = Expression::Eq(Box::new(BinaryOp::new(strpos.clone(), zero.clone())));
2288
2289                // STRPOS(...) + pos_case - 1
2290                let add_pos = Expression::Add(Box::new(BinaryOp::new(strpos, pos_case)));
2291                let sub_one = Expression::Sub(Box::new(BinaryOp::new(add_pos, one)));
2292
2293                Ok(Expression::Case(Box::new(Case {
2294                    operand: None,
2295                    whens: vec![(eq_zero, zero)],
2296                    else_: Some(sub_one),
2297                    comments: Vec::new(),
2298                    inferred_type: None,
2299                })))
2300            }
2301
2302            // SPLIT -> STR_SPLIT in DuckDB
2303            "SPLIT" => Ok(Expression::Function(Box::new(Function::new(
2304                "STR_SPLIT".to_string(),
2305                f.args,
2306            )))),
2307
2308            // STRING_SPLIT -> STR_SPLIT in DuckDB
2309            "STRING_SPLIT" => Ok(Expression::Function(Box::new(Function::new(
2310                "STR_SPLIT".to_string(),
2311                f.args,
2312            )))),
2313
2314            // STRTOK_TO_ARRAY -> STR_SPLIT
2315            "STRTOK_TO_ARRAY" => Ok(Expression::Function(Box::new(Function::new(
2316                "STR_SPLIT".to_string(),
2317                f.args,
2318            )))),
2319
2320            // REGEXP_SPLIT -> STR_SPLIT_REGEX in DuckDB
2321            "REGEXP_SPLIT" => Ok(Expression::Function(Box::new(Function::new(
2322                "STR_SPLIT_REGEX".to_string(),
2323                f.args,
2324            )))),
2325
2326            // EDITDIST3 -> LEVENSHTEIN in DuckDB
2327            "EDITDIST3" => Ok(Expression::Function(Box::new(Function::new(
2328                "LEVENSHTEIN".to_string(),
2329                f.args,
2330            )))),
2331
2332            // JSON_EXTRACT_PATH -> arrow operator (->)
2333            "JSON_EXTRACT_PATH" if f.args.len() >= 2 => {
2334                let mut args = f.args;
2335                let this = args.remove(0);
2336                let path = args.remove(0);
2337                Ok(Expression::JsonExtract(Box::new(JsonExtractFunc {
2338                    this,
2339                    path,
2340                    returning: None,
2341                    arrow_syntax: true,
2342                    hash_arrow_syntax: false,
2343                    wrapper_option: None,
2344                    quotes_option: None,
2345                    on_scalar_string: false,
2346                    on_error: None,
2347                })))
2348            }
2349
2350            // JSON_EXTRACT_PATH_TEXT -> arrow operator (->>)
2351            "JSON_EXTRACT_PATH_TEXT" if f.args.len() >= 2 => {
2352                let mut args = f.args;
2353                let this = args.remove(0);
2354                let path = args.remove(0);
2355                Ok(Expression::JsonExtractScalar(Box::new(JsonExtractFunc {
2356                    this,
2357                    path,
2358                    returning: None,
2359                    arrow_syntax: true,
2360                    hash_arrow_syntax: false,
2361                    wrapper_option: None,
2362                    quotes_option: None,
2363                    on_scalar_string: false,
2364                    on_error: None,
2365                })))
2366            }
2367
2368            // DATE_ADD(date, interval) -> date + interval in DuckDB
2369            "DATE_ADD" if f.args.len() == 2 => {
2370                let mut args = f.args;
2371                let date = args.remove(0);
2372                let interval = args.remove(0);
2373                Ok(Expression::Add(Box::new(BinaryOp {
2374                    left: date,
2375                    right: interval,
2376                    left_comments: Vec::new(),
2377                    operator_comments: Vec::new(),
2378                    trailing_comments: Vec::new(),
2379                    inferred_type: None,
2380                })))
2381            }
2382
2383            // DATE_SUB(date, interval) -> date - interval in DuckDB
2384            "DATE_SUB" if f.args.len() == 2 => {
2385                let mut args = f.args;
2386                let date = args.remove(0);
2387                let interval = args.remove(0);
2388                Ok(Expression::Sub(Box::new(BinaryOp {
2389                    left: date,
2390                    right: interval,
2391                    left_comments: Vec::new(),
2392                    operator_comments: Vec::new(),
2393                    trailing_comments: Vec::new(),
2394                    inferred_type: None,
2395                })))
2396            }
2397
2398            // RANGE(n) -> RANGE(0, n) in DuckDB
2399            "RANGE" if f.args.len() == 1 => {
2400                let mut new_args = vec![Expression::number(0)];
2401                new_args.extend(f.args);
2402                Ok(Expression::Function(Box::new(Function::new(
2403                    "RANGE".to_string(),
2404                    new_args,
2405                ))))
2406            }
2407
2408            // GENERATE_SERIES(n) -> GENERATE_SERIES(0, n) in DuckDB
2409            "GENERATE_SERIES" if f.args.len() == 1 => {
2410                let mut new_args = vec![Expression::number(0)];
2411                new_args.extend(f.args);
2412                Ok(Expression::Function(Box::new(Function::new(
2413                    "GENERATE_SERIES".to_string(),
2414                    new_args,
2415                ))))
2416            }
2417
2418            // REGEXP_EXTRACT(str, pattern, 0) -> REGEXP_EXTRACT(str, pattern) in DuckDB
2419            // Drop the group argument when it's 0 (default)
2420            "REGEXP_EXTRACT" if f.args.len() == 3 => {
2421                // Check if the third argument is 0
2422                let drop_group = match &f.args[2] {
2423                    Expression::Literal(lit) if matches!(lit.as_ref(), Literal::Number(_)) => {
2424                        let Literal::Number(n) = lit.as_ref() else {
2425                            unreachable!()
2426                        };
2427                        n == "0"
2428                    }
2429                    _ => false,
2430                };
2431                if drop_group {
2432                    Ok(Expression::Function(Box::new(Function::new(
2433                        "REGEXP_EXTRACT".to_string(),
2434                        vec![f.args[0].clone(), f.args[1].clone()],
2435                    ))))
2436                } else {
2437                    Ok(Expression::Function(Box::new(f)))
2438                }
2439            }
2440
2441            // STRUCT_PACK(a := 1, b := 2) -> {'a': 1, 'b': 2} (DuckDB struct literal)
2442            "STRUCT_PACK" => {
2443                let mut fields = Vec::new();
2444                for arg in f.args {
2445                    match arg {
2446                        Expression::NamedArgument(na) => {
2447                            fields.push((Some(na.name.name.clone()), na.value));
2448                        }
2449                        // Non-named arguments get positional keys
2450                        other => {
2451                            fields.push((None, other));
2452                        }
2453                    }
2454                }
2455                Ok(Expression::Struct(Box::new(Struct { fields })))
2456            }
2457
2458            // REPLACE with 2 args -> add empty string 3rd arg
2459            "REPLACE" if f.args.len() == 2 => {
2460                let mut args = f.args;
2461                args.push(Expression::Literal(Box::new(
2462                    Literal::String(String::new()),
2463                )));
2464                Ok(Expression::Function(Box::new(Function::new(
2465                    "REPLACE".to_string(),
2466                    args,
2467                ))))
2468            }
2469
2470            // TO_UNIXTIME -> EPOCH in DuckDB
2471            "TO_UNIXTIME" => Ok(Expression::Function(Box::new(Function::new(
2472                "EPOCH".to_string(),
2473                f.args,
2474            )))),
2475
2476            // FROM_ISO8601_TIMESTAMP -> CAST(x AS TIMESTAMPTZ) in DuckDB
2477            "FROM_ISO8601_TIMESTAMP" if f.args.len() == 1 => {
2478                use crate::expressions::{Cast, DataType};
2479                Ok(Expression::Cast(Box::new(Cast {
2480                    this: f.args.into_iter().next().unwrap(),
2481                    to: DataType::Timestamp {
2482                        precision: None,
2483                        timezone: true,
2484                    },
2485                    trailing_comments: Vec::new(),
2486                    double_colon_syntax: false,
2487                    format: None,
2488                    default: None,
2489                    inferred_type: None,
2490                })))
2491            }
2492
2493            // APPROX_DISTINCT -> APPROX_COUNT_DISTINCT in DuckDB
2494            "APPROX_DISTINCT" => {
2495                // Drop the accuracy parameter (second arg) if present
2496                let args = if f.args.len() > 1 {
2497                    vec![f.args.into_iter().next().unwrap()]
2498                } else {
2499                    f.args
2500                };
2501                Ok(Expression::Function(Box::new(Function::new(
2502                    "APPROX_COUNT_DISTINCT".to_string(),
2503                    args,
2504                ))))
2505            }
2506
2507            // ARRAY_SORT is native to DuckDB (but drop the lambda comparator)
2508            "ARRAY_SORT" => {
2509                let args = vec![f.args.into_iter().next().unwrap()];
2510                Ok(Expression::Function(Box::new(Function::new(
2511                    "ARRAY_SORT".to_string(),
2512                    args,
2513                ))))
2514            }
2515
2516            // TO_UTF8 -> ENCODE in DuckDB
2517            "TO_UTF8" => Ok(Expression::Function(Box::new(Function::new(
2518                "ENCODE".to_string(),
2519                f.args,
2520            )))),
2521
2522            // FROM_UTF8 -> DECODE in DuckDB
2523            "FROM_UTF8" => Ok(Expression::Function(Box::new(Function::new(
2524                "DECODE".to_string(),
2525                f.args,
2526            )))),
2527
2528            // ARBITRARY -> ANY_VALUE in DuckDB
2529            "ARBITRARY" => Ok(Expression::Function(Box::new(Function::new(
2530                "ANY_VALUE".to_string(),
2531                f.args,
2532            )))),
2533
2534            // MAX_BY -> ARG_MAX in DuckDB
2535            "MAX_BY" => Ok(Expression::Function(Box::new(Function::new(
2536                "ARG_MAX".to_string(),
2537                f.args,
2538            )))),
2539
2540            // MIN_BY -> ARG_MIN in DuckDB
2541            "MIN_BY" => Ok(Expression::Function(Box::new(Function::new(
2542                "ARG_MIN".to_string(),
2543                f.args,
2544            )))),
2545
2546            // ===== Snowflake-specific function transforms =====
2547            "IFF" if f.args.len() == 3 => {
2548                let mut args = f.args;
2549                let cond = args.remove(0);
2550                let true_val = args.remove(0);
2551                let false_val = args.remove(0);
2552                Ok(Expression::Case(Box::new(Case {
2553                    operand: None,
2554                    whens: vec![(cond, true_val)],
2555                    else_: Some(false_val),
2556                    comments: Vec::new(),
2557                    inferred_type: None,
2558                })))
2559            }
2560            "SKEW" => Ok(Expression::Function(Box::new(Function::new(
2561                "SKEWNESS".to_string(),
2562                f.args,
2563            )))),
2564            "VAR_SAMP" => Ok(Expression::Function(Box::new(Function::new(
2565                "VARIANCE".to_string(),
2566                f.args,
2567            )))),
2568            "VARIANCE_POP" => Ok(Expression::Function(Box::new(Function::new(
2569                "VAR_POP".to_string(),
2570                f.args,
2571            )))),
2572            "REGR_VALX" if f.args.len() == 2 => {
2573                let mut args = f.args;
2574                let y = args.remove(0);
2575                let x = args.remove(0);
2576                Ok(Expression::Case(Box::new(Case {
2577                    operand: None,
2578                    whens: vec![(
2579                        Expression::IsNull(Box::new(crate::expressions::IsNull {
2580                            this: y,
2581                            not: false,
2582                            postfix_form: false,
2583                        })),
2584                        Expression::Cast(Box::new(Cast {
2585                            this: Expression::Null(crate::expressions::Null),
2586                            to: DataType::Double {
2587                                precision: None,
2588                                scale: None,
2589                            },
2590                            trailing_comments: Vec::new(),
2591                            double_colon_syntax: false,
2592                            format: None,
2593                            default: None,
2594                            inferred_type: None,
2595                        })),
2596                    )],
2597                    else_: Some(x),
2598                    comments: Vec::new(),
2599                    inferred_type: None,
2600                })))
2601            }
2602            "REGR_VALY" if f.args.len() == 2 => {
2603                let mut args = f.args;
2604                let y = args.remove(0);
2605                let x = args.remove(0);
2606                Ok(Expression::Case(Box::new(Case {
2607                    operand: None,
2608                    whens: vec![(
2609                        Expression::IsNull(Box::new(crate::expressions::IsNull {
2610                            this: x,
2611                            not: false,
2612                            postfix_form: false,
2613                        })),
2614                        Expression::Cast(Box::new(Cast {
2615                            this: Expression::Null(crate::expressions::Null),
2616                            to: DataType::Double {
2617                                precision: None,
2618                                scale: None,
2619                            },
2620                            trailing_comments: Vec::new(),
2621                            double_colon_syntax: false,
2622                            format: None,
2623                            default: None,
2624                            inferred_type: None,
2625                        })),
2626                    )],
2627                    else_: Some(y),
2628                    comments: Vec::new(),
2629                    inferred_type: None,
2630                })))
2631            }
2632            "BOOLNOT" if f.args.len() == 1 => {
2633                let arg = f.args.into_iter().next().unwrap();
2634                // BOOLNOT(x) -> NOT (ROUND(x, 0))
2635                let rounded = Expression::Function(Box::new(Function::new(
2636                    "ROUND".to_string(),
2637                    vec![arg, Expression::number(0)],
2638                )));
2639                Ok(Expression::Not(Box::new(crate::expressions::UnaryOp {
2640                    this: Expression::Paren(Box::new(Paren {
2641                        this: rounded,
2642                        trailing_comments: Vec::new(),
2643                    })),
2644                    inferred_type: None,
2645                })))
2646            }
2647            "BITMAP_BIT_POSITION" if f.args.len() == 1 => {
2648                let n = f.args.into_iter().next().unwrap();
2649                let case_expr = Expression::Case(Box::new(Case {
2650                    operand: None,
2651                    whens: vec![(
2652                        Expression::Gt(Box::new(BinaryOp {
2653                            left: n.clone(),
2654                            right: Expression::number(0),
2655                            left_comments: Vec::new(),
2656                            operator_comments: Vec::new(),
2657                            trailing_comments: Vec::new(),
2658                            inferred_type: None,
2659                        })),
2660                        Expression::Sub(Box::new(BinaryOp {
2661                            left: n.clone(),
2662                            right: Expression::number(1),
2663                            left_comments: Vec::new(),
2664                            operator_comments: Vec::new(),
2665                            trailing_comments: Vec::new(),
2666                            inferred_type: None,
2667                        })),
2668                    )],
2669                    else_: Some(Expression::Abs(Box::new(UnaryFunc {
2670                        this: n,
2671                        original_name: None,
2672                        inferred_type: None,
2673                    }))),
2674                    comments: Vec::new(),
2675                    inferred_type: None,
2676                }));
2677                Ok(Expression::Mod(Box::new(BinaryOp {
2678                    left: Expression::Paren(Box::new(Paren {
2679                        this: case_expr,
2680                        trailing_comments: Vec::new(),
2681                    })),
2682                    right: Expression::number(32768),
2683                    left_comments: Vec::new(),
2684                    operator_comments: Vec::new(),
2685                    trailing_comments: Vec::new(),
2686                    inferred_type: None,
2687                })))
2688            }
2689            // GREATEST/LEAST - pass through (null-wrapping is handled by source dialect transforms)
2690            "GREATEST" | "LEAST" => Ok(Expression::Function(Box::new(f))),
2691            "GREATEST_IGNORE_NULLS" => Ok(Expression::Greatest(Box::new(VarArgFunc {
2692                expressions: f.args,
2693                original_name: None,
2694                inferred_type: None,
2695            }))),
2696            "LEAST_IGNORE_NULLS" => Ok(Expression::Least(Box::new(VarArgFunc {
2697                expressions: f.args,
2698                original_name: None,
2699                inferred_type: None,
2700            }))),
2701            "PARSE_JSON" => Ok(Expression::Function(Box::new(Function::new(
2702                "JSON".to_string(),
2703                f.args,
2704            )))),
2705            "OBJECT_CONSTRUCT_KEEP_NULL" => {
2706                // OBJECT_CONSTRUCT_KEEP_NULL -> JSON_OBJECT (preserves NULLs)
2707                Ok(Expression::Function(Box::new(Function::new(
2708                    "JSON_OBJECT".to_string(),
2709                    f.args,
2710                ))))
2711            }
2712            "OBJECT_CONSTRUCT" => {
2713                // Convert to DuckDB struct literal: {'key1': val1, 'key2': val2}
2714                let args = f.args;
2715                if args.is_empty() {
2716                    // Empty OBJECT_CONSTRUCT() -> STRUCT_PACK() (no args)
2717                    Ok(Expression::Function(Box::new(Function::new(
2718                        "STRUCT_PACK".to_string(),
2719                        vec![],
2720                    ))))
2721                } else {
2722                    // Build struct literal from key-value pairs
2723                    let mut fields = Vec::new();
2724                    let mut i = 0;
2725                    while i + 1 < args.len() {
2726                        let key = &args[i];
2727                        let value = args[i + 1].clone();
2728                        let key_name = match key {
2729                            Expression::Literal(lit)
2730                                if matches!(lit.as_ref(), Literal::String(_)) =>
2731                            {
2732                                let Literal::String(s) = lit.as_ref() else {
2733                                    unreachable!()
2734                                };
2735                                Some(s.clone())
2736                            }
2737                            _ => None,
2738                        };
2739                        fields.push((key_name, value));
2740                        i += 2;
2741                    }
2742                    Ok(Expression::Struct(Box::new(Struct { fields })))
2743                }
2744            }
2745            "IS_NULL_VALUE" if f.args.len() == 1 => {
2746                let arg = f.args.into_iter().next().unwrap();
2747                Ok(Expression::Eq(Box::new(BinaryOp {
2748                    left: Expression::Function(Box::new(Function::new(
2749                        "JSON_TYPE".to_string(),
2750                        vec![arg],
2751                    ))),
2752                    right: Expression::Literal(Box::new(Literal::String("NULL".to_string()))),
2753                    left_comments: Vec::new(),
2754                    operator_comments: Vec::new(),
2755                    trailing_comments: Vec::new(),
2756                    inferred_type: None,
2757                })))
2758            }
2759            "TRY_TO_DOUBLE" | "TRY_TO_NUMBER" | "TRY_TO_NUMERIC" | "TRY_TO_DECIMAL"
2760                if f.args.len() == 1 =>
2761            {
2762                let arg = f.args.into_iter().next().unwrap();
2763                Ok(Expression::TryCast(Box::new(Cast {
2764                    this: arg,
2765                    to: DataType::Double {
2766                        precision: None,
2767                        scale: None,
2768                    },
2769                    trailing_comments: Vec::new(),
2770                    double_colon_syntax: false,
2771                    format: None,
2772                    default: None,
2773                    inferred_type: None,
2774                })))
2775            }
2776            "TRY_TO_TIME" if f.args.len() == 1 => {
2777                let arg = f.args.into_iter().next().unwrap();
2778                Ok(Expression::TryCast(Box::new(Cast {
2779                    this: arg,
2780                    to: DataType::Time {
2781                        precision: None,
2782                        timezone: false,
2783                    },
2784                    trailing_comments: Vec::new(),
2785                    double_colon_syntax: false,
2786                    format: None,
2787                    default: None,
2788                    inferred_type: None,
2789                })))
2790            }
2791            "TRY_TO_TIME" if f.args.len() == 2 => {
2792                let mut args = f.args;
2793                let value = args.remove(0);
2794                let fmt = self.convert_snowflake_time_format(args.remove(0));
2795                Ok(Expression::TryCast(Box::new(Cast {
2796                    this: Expression::Function(Box::new(Function::new(
2797                        "TRY_STRPTIME".to_string(),
2798                        vec![value, fmt],
2799                    ))),
2800                    to: DataType::Time {
2801                        precision: None,
2802                        timezone: false,
2803                    },
2804                    trailing_comments: Vec::new(),
2805                    double_colon_syntax: false,
2806                    format: None,
2807                    default: None,
2808                    inferred_type: None,
2809                })))
2810            }
2811            "TRY_TO_TIMESTAMP" if f.args.len() == 1 => {
2812                let arg = f.args.into_iter().next().unwrap();
2813                Ok(Expression::TryCast(Box::new(Cast {
2814                    this: arg,
2815                    to: DataType::Timestamp {
2816                        precision: None,
2817                        timezone: false,
2818                    },
2819                    trailing_comments: Vec::new(),
2820                    double_colon_syntax: false,
2821                    format: None,
2822                    default: None,
2823                    inferred_type: None,
2824                })))
2825            }
2826            "TRY_TO_TIMESTAMP" if f.args.len() == 2 => {
2827                let mut args = f.args;
2828                let value = args.remove(0);
2829                let fmt = self.convert_snowflake_time_format(args.remove(0));
2830                Ok(Expression::Cast(Box::new(Cast {
2831                    this: Expression::Function(Box::new(Function::new(
2832                        "TRY_STRPTIME".to_string(),
2833                        vec![value, fmt],
2834                    ))),
2835                    to: DataType::Timestamp {
2836                        precision: None,
2837                        timezone: false,
2838                    },
2839                    trailing_comments: Vec::new(),
2840                    double_colon_syntax: false,
2841                    format: None,
2842                    default: None,
2843                    inferred_type: None,
2844                })))
2845            }
2846            "TRY_TO_DATE" if f.args.len() == 1 => {
2847                let arg = f.args.into_iter().next().unwrap();
2848                Ok(Expression::TryCast(Box::new(Cast {
2849                    this: arg,
2850                    to: DataType::Date,
2851                    trailing_comments: Vec::new(),
2852                    double_colon_syntax: false,
2853                    format: None,
2854                    default: None,
2855                    inferred_type: None,
2856                })))
2857            }
2858            "DAYOFWEEKISO" | "DAYOFWEEK_ISO" => Ok(Expression::Function(Box::new(Function::new(
2859                "ISODOW".to_string(),
2860                f.args,
2861            )))),
2862            "YEAROFWEEK" | "YEAROFWEEKISO" if f.args.len() == 1 => {
2863                let arg = f.args.into_iter().next().unwrap();
2864                Ok(Expression::Extract(Box::new(
2865                    crate::expressions::ExtractFunc {
2866                        this: arg,
2867                        field: crate::expressions::DateTimeField::Custom("ISOYEAR".to_string()),
2868                    },
2869                )))
2870            }
2871            "WEEKISO" => Ok(Expression::Function(Box::new(Function::new(
2872                "WEEKOFYEAR".to_string(),
2873                f.args,
2874            )))),
2875            "TIME_FROM_PARTS" | "TIMEFROMPARTS" if f.args.len() == 3 => {
2876                let args_ref = &f.args;
2877                // Check if all args are in-range literals: h < 24, m < 60, s < 60
2878                let all_in_range = if let (Some(h_val), Some(m_val), Some(s_val)) = (
2879                    Self::extract_number_value(&args_ref[0]),
2880                    Self::extract_number_value(&args_ref[1]),
2881                    Self::extract_number_value(&args_ref[2]),
2882                ) {
2883                    h_val >= 0.0
2884                        && h_val < 24.0
2885                        && m_val >= 0.0
2886                        && m_val < 60.0
2887                        && s_val >= 0.0
2888                        && s_val < 60.0
2889                } else {
2890                    false
2891                };
2892                if all_in_range {
2893                    // Use MAKE_TIME for normal values
2894                    Ok(Expression::Function(Box::new(Function::new(
2895                        "MAKE_TIME".to_string(),
2896                        f.args,
2897                    ))))
2898                } else {
2899                    // TIME_FROM_PARTS(h, m, s) -> CAST('00:00:00' AS TIME) + INTERVAL ((h * 3600) + (m * 60) + s) SECOND
2900                    // Use arithmetic approach to handle out-of-range values (e.g., 100 minutes)
2901                    let mut args = f.args;
2902                    let h = args.remove(0);
2903                    let m = args.remove(0);
2904                    let s = args.remove(0);
2905                    let seconds_expr = Expression::Add(Box::new(BinaryOp {
2906                        left: Expression::Add(Box::new(BinaryOp {
2907                            left: Expression::Paren(Box::new(Paren {
2908                                this: Expression::Mul(Box::new(BinaryOp {
2909                                    left: h,
2910                                    right: Expression::number(3600),
2911                                    left_comments: Vec::new(),
2912                                    operator_comments: Vec::new(),
2913                                    trailing_comments: Vec::new(),
2914                                    inferred_type: None,
2915                                })),
2916                                trailing_comments: Vec::new(),
2917                            })),
2918                            right: Expression::Paren(Box::new(Paren {
2919                                this: Expression::Mul(Box::new(BinaryOp {
2920                                    left: m,
2921                                    right: Expression::number(60),
2922                                    left_comments: Vec::new(),
2923                                    operator_comments: Vec::new(),
2924                                    trailing_comments: Vec::new(),
2925                                    inferred_type: None,
2926                                })),
2927                                trailing_comments: Vec::new(),
2928                            })),
2929                            left_comments: Vec::new(),
2930                            operator_comments: Vec::new(),
2931                            trailing_comments: Vec::new(),
2932                            inferred_type: None,
2933                        })),
2934                        right: s,
2935                        left_comments: Vec::new(),
2936                        operator_comments: Vec::new(),
2937                        trailing_comments: Vec::new(),
2938                        inferred_type: None,
2939                    }));
2940                    let base_time = Expression::Cast(Box::new(Cast {
2941                        this: Expression::Literal(Box::new(Literal::String(
2942                            "00:00:00".to_string(),
2943                        ))),
2944                        to: DataType::Time {
2945                            precision: None,
2946                            timezone: false,
2947                        },
2948                        trailing_comments: Vec::new(),
2949                        double_colon_syntax: false,
2950                        format: None,
2951                        default: None,
2952                        inferred_type: None,
2953                    }));
2954                    Ok(Expression::Add(Box::new(BinaryOp {
2955                        left: base_time,
2956                        right: Expression::Interval(Box::new(Interval {
2957                            this: Some(Expression::Paren(Box::new(crate::expressions::Paren {
2958                                this: seconds_expr,
2959                                trailing_comments: Vec::new(),
2960                            }))),
2961                            unit: Some(IntervalUnitSpec::Simple {
2962                                unit: IntervalUnit::Second,
2963                                use_plural: false,
2964                            }),
2965                        })),
2966                        left_comments: Vec::new(),
2967                        operator_comments: Vec::new(),
2968                        trailing_comments: Vec::new(),
2969                        inferred_type: None,
2970                    })))
2971                }
2972            }
2973            "TIME_FROM_PARTS" | "TIMEFROMPARTS" if f.args.len() == 4 => {
2974                let mut args = f.args;
2975                let h = args.remove(0);
2976                let m = args.remove(0);
2977                let s = args.remove(0);
2978                let ns = args.remove(0);
2979                let seconds_expr = Expression::Add(Box::new(BinaryOp {
2980                    left: Expression::Add(Box::new(BinaryOp {
2981                        left: Expression::Add(Box::new(BinaryOp {
2982                            left: Expression::Paren(Box::new(Paren {
2983                                this: Expression::Mul(Box::new(BinaryOp {
2984                                    left: h,
2985                                    right: Expression::number(3600),
2986                                    left_comments: Vec::new(),
2987                                    operator_comments: Vec::new(),
2988                                    trailing_comments: Vec::new(),
2989                                    inferred_type: None,
2990                                })),
2991                                trailing_comments: Vec::new(),
2992                            })),
2993                            right: Expression::Paren(Box::new(Paren {
2994                                this: Expression::Mul(Box::new(BinaryOp {
2995                                    left: m,
2996                                    right: Expression::number(60),
2997                                    left_comments: Vec::new(),
2998                                    operator_comments: Vec::new(),
2999                                    trailing_comments: Vec::new(),
3000                                    inferred_type: None,
3001                                })),
3002                                trailing_comments: Vec::new(),
3003                            })),
3004                            left_comments: Vec::new(),
3005                            operator_comments: Vec::new(),
3006                            trailing_comments: Vec::new(),
3007                            inferred_type: None,
3008                        })),
3009                        right: s,
3010                        left_comments: Vec::new(),
3011                        operator_comments: Vec::new(),
3012                        trailing_comments: Vec::new(),
3013                        inferred_type: None,
3014                    })),
3015                    right: Expression::Paren(Box::new(Paren {
3016                        this: Expression::Div(Box::new(BinaryOp {
3017                            left: ns,
3018                            right: Expression::Literal(Box::new(Literal::Number(
3019                                "1000000000.0".to_string(),
3020                            ))),
3021                            left_comments: Vec::new(),
3022                            operator_comments: Vec::new(),
3023                            trailing_comments: Vec::new(),
3024                            inferred_type: None,
3025                        })),
3026                        trailing_comments: Vec::new(),
3027                    })),
3028                    left_comments: Vec::new(),
3029                    operator_comments: Vec::new(),
3030                    trailing_comments: Vec::new(),
3031                    inferred_type: None,
3032                }));
3033                let base_time = Expression::Cast(Box::new(Cast {
3034                    this: Expression::Literal(Box::new(Literal::String("00:00:00".to_string()))),
3035                    to: DataType::Time {
3036                        precision: None,
3037                        timezone: false,
3038                    },
3039                    trailing_comments: Vec::new(),
3040                    double_colon_syntax: false,
3041                    format: None,
3042                    default: None,
3043                    inferred_type: None,
3044                }));
3045                Ok(Expression::Add(Box::new(BinaryOp {
3046                    left: base_time,
3047                    right: Expression::Interval(Box::new(Interval {
3048                        this: Some(Expression::Paren(Box::new(crate::expressions::Paren {
3049                            this: seconds_expr,
3050                            trailing_comments: Vec::new(),
3051                        }))),
3052                        unit: Some(IntervalUnitSpec::Simple {
3053                            unit: IntervalUnit::Second,
3054                            use_plural: false,
3055                        }),
3056                    })),
3057                    left_comments: Vec::new(),
3058                    operator_comments: Vec::new(),
3059                    trailing_comments: Vec::new(),
3060                    inferred_type: None,
3061                })))
3062            }
3063            "TIMESTAMP_FROM_PARTS" | "TIMESTAMPFROMPARTS" if f.args.len() == 6 => {
3064                Ok(Expression::Function(Box::new(Function::new(
3065                    "MAKE_TIMESTAMP".to_string(),
3066                    f.args,
3067                ))))
3068            }
3069            "TIMESTAMP_FROM_PARTS" | "TIMESTAMPFROMPARTS" | "TIMESTAMP_NTZ_FROM_PARTS"
3070                if f.args.len() == 2 =>
3071            {
3072                let mut args = f.args;
3073                let d = args.remove(0);
3074                let t = args.remove(0);
3075                Ok(Expression::Add(Box::new(BinaryOp {
3076                    left: d,
3077                    right: t,
3078                    left_comments: Vec::new(),
3079                    operator_comments: Vec::new(),
3080                    trailing_comments: Vec::new(),
3081                    inferred_type: None,
3082                })))
3083            }
3084            "TIMESTAMP_LTZ_FROM_PARTS" if f.args.len() == 6 => {
3085                Ok(Expression::Cast(Box::new(Cast {
3086                    this: Expression::Function(Box::new(Function::new(
3087                        "MAKE_TIMESTAMP".to_string(),
3088                        f.args,
3089                    ))),
3090                    to: DataType::Timestamp {
3091                        precision: None,
3092                        timezone: true,
3093                    },
3094                    trailing_comments: Vec::new(),
3095                    double_colon_syntax: false,
3096                    format: None,
3097                    default: None,
3098                    inferred_type: None,
3099                })))
3100            }
3101            "TIMESTAMP_TZ_FROM_PARTS" if f.args.len() == 8 => {
3102                let mut args = f.args;
3103                let ts_args = vec![
3104                    args.remove(0),
3105                    args.remove(0),
3106                    args.remove(0),
3107                    args.remove(0),
3108                    args.remove(0),
3109                    args.remove(0),
3110                ];
3111                let _nano = args.remove(0);
3112                let tz = args.remove(0);
3113                Ok(Expression::AtTimeZone(Box::new(
3114                    crate::expressions::AtTimeZone {
3115                        this: Expression::Function(Box::new(Function::new(
3116                            "MAKE_TIMESTAMP".to_string(),
3117                            ts_args,
3118                        ))),
3119                        zone: tz,
3120                    },
3121                )))
3122            }
3123            "BOOLAND_AGG" if f.args.len() == 1 => {
3124                let arg = f.args.into_iter().next().unwrap();
3125                Ok(Expression::Function(Box::new(Function::new(
3126                    "BOOL_AND".to_string(),
3127                    vec![Expression::Cast(Box::new(Cast {
3128                        this: arg,
3129                        to: DataType::Boolean,
3130                        trailing_comments: Vec::new(),
3131                        double_colon_syntax: false,
3132                        format: None,
3133                        default: None,
3134                        inferred_type: None,
3135                    }))],
3136                ))))
3137            }
3138            "BOOLOR_AGG" if f.args.len() == 1 => {
3139                let arg = f.args.into_iter().next().unwrap();
3140                Ok(Expression::Function(Box::new(Function::new(
3141                    "BOOL_OR".to_string(),
3142                    vec![Expression::Cast(Box::new(Cast {
3143                        this: arg,
3144                        to: DataType::Boolean,
3145                        trailing_comments: Vec::new(),
3146                        double_colon_syntax: false,
3147                        format: None,
3148                        default: None,
3149                        inferred_type: None,
3150                    }))],
3151                ))))
3152            }
3153            "NVL2" if f.args.len() == 3 => {
3154                let mut args = f.args;
3155                let a = args.remove(0);
3156                let b = args.remove(0);
3157                let c = args.remove(0);
3158                Ok(Expression::Case(Box::new(Case {
3159                    operand: None,
3160                    whens: vec![(
3161                        Expression::Not(Box::new(crate::expressions::UnaryOp {
3162                            this: Expression::IsNull(Box::new(crate::expressions::IsNull {
3163                                this: a,
3164                                not: false,
3165                                postfix_form: false,
3166                            })),
3167                            inferred_type: None,
3168                        })),
3169                        b,
3170                    )],
3171                    else_: Some(c),
3172                    comments: Vec::new(),
3173                    inferred_type: None,
3174                })))
3175            }
3176            "EQUAL_NULL" if f.args.len() == 2 => {
3177                let mut args = f.args;
3178                let a = args.remove(0);
3179                let b = args.remove(0);
3180                Ok(Expression::NullSafeEq(Box::new(BinaryOp {
3181                    left: a,
3182                    right: b,
3183                    left_comments: Vec::new(),
3184                    operator_comments: Vec::new(),
3185                    trailing_comments: Vec::new(),
3186                    inferred_type: None,
3187                })))
3188            }
3189            "EDITDISTANCE" if f.args.len() == 3 => {
3190                // EDITDISTANCE(a, b, max) -> CASE WHEN LEVENSHTEIN(a, b) IS NULL OR max IS NULL THEN NULL ELSE LEAST(LEVENSHTEIN(a, b), max) END
3191                let mut args = f.args;
3192                let a = args.remove(0);
3193                let b = args.remove(0);
3194                let max_dist = args.remove(0);
3195                let lev = Expression::Function(Box::new(Function::new(
3196                    "LEVENSHTEIN".to_string(),
3197                    vec![a, b],
3198                )));
3199                let lev_is_null = Expression::IsNull(Box::new(crate::expressions::IsNull {
3200                    this: lev.clone(),
3201                    not: false,
3202                    postfix_form: false,
3203                }));
3204                let max_is_null = Expression::IsNull(Box::new(crate::expressions::IsNull {
3205                    this: max_dist.clone(),
3206                    not: false,
3207                    postfix_form: false,
3208                }));
3209                let null_check = Expression::Or(Box::new(BinaryOp {
3210                    left: lev_is_null,
3211                    right: max_is_null,
3212                    left_comments: Vec::new(),
3213                    operator_comments: Vec::new(),
3214                    trailing_comments: Vec::new(),
3215                    inferred_type: None,
3216                }));
3217                let least = Expression::Least(Box::new(VarArgFunc {
3218                    expressions: vec![lev, max_dist],
3219                    original_name: None,
3220                    inferred_type: None,
3221                }));
3222                Ok(Expression::Case(Box::new(Case {
3223                    operand: None,
3224                    whens: vec![(null_check, Expression::Null(crate::expressions::Null))],
3225                    else_: Some(least),
3226                    comments: Vec::new(),
3227                    inferred_type: None,
3228                })))
3229            }
3230            "EDITDISTANCE" => Ok(Expression::Function(Box::new(Function::new(
3231                "LEVENSHTEIN".to_string(),
3232                f.args,
3233            )))),
3234            "BITAND" if f.args.len() == 2 => {
3235                let mut args = f.args;
3236                let left = args.remove(0);
3237                let right = args.remove(0);
3238                // Wrap shift expressions in parentheses for correct precedence
3239                let wrap = |e: Expression| -> Expression {
3240                    match &e {
3241                        Expression::BitwiseLeftShift(_) | Expression::BitwiseRightShift(_) => {
3242                            Expression::Paren(Box::new(Paren {
3243                                this: e,
3244                                trailing_comments: Vec::new(),
3245                            }))
3246                        }
3247                        _ => e,
3248                    }
3249                };
3250                Ok(Expression::BitwiseAnd(Box::new(BinaryOp {
3251                    left: wrap(left),
3252                    right: wrap(right),
3253                    left_comments: Vec::new(),
3254                    operator_comments: Vec::new(),
3255                    trailing_comments: Vec::new(),
3256                    inferred_type: None,
3257                })))
3258            }
3259            "BITOR" if f.args.len() == 2 => {
3260                let mut args = f.args;
3261                let left = args.remove(0);
3262                let right = args.remove(0);
3263                // Wrap shift expressions in parentheses for correct precedence
3264                let wrap = |e: Expression| -> Expression {
3265                    match &e {
3266                        Expression::BitwiseLeftShift(_) | Expression::BitwiseRightShift(_) => {
3267                            Expression::Paren(Box::new(Paren {
3268                                this: e,
3269                                trailing_comments: Vec::new(),
3270                            }))
3271                        }
3272                        _ => e,
3273                    }
3274                };
3275                Ok(Expression::BitwiseOr(Box::new(BinaryOp {
3276                    left: wrap(left),
3277                    right: wrap(right),
3278                    left_comments: Vec::new(),
3279                    operator_comments: Vec::new(),
3280                    trailing_comments: Vec::new(),
3281                    inferred_type: None,
3282                })))
3283            }
3284            "BITXOR" if f.args.len() == 2 => {
3285                let mut args = f.args;
3286                Ok(Expression::BitwiseXor(Box::new(BinaryOp {
3287                    left: args.remove(0),
3288                    right: args.remove(0),
3289                    left_comments: Vec::new(),
3290                    operator_comments: Vec::new(),
3291                    trailing_comments: Vec::new(),
3292                    inferred_type: None,
3293                })))
3294            }
3295            "BITNOT" if f.args.len() == 1 => {
3296                let arg = f.args.into_iter().next().unwrap();
3297                Ok(Expression::BitwiseNot(Box::new(
3298                    crate::expressions::UnaryOp {
3299                        this: Expression::Paren(Box::new(Paren {
3300                            this: arg,
3301                            trailing_comments: Vec::new(),
3302                        })),
3303                        inferred_type: None,
3304                    },
3305                )))
3306            }
3307            "BITSHIFTLEFT" if f.args.len() == 2 => {
3308                let mut args = f.args;
3309                let a = args.remove(0);
3310                let b = args.remove(0);
3311                // Check if first arg is BINARY/BLOB type (e.g., X'002A'::BINARY)
3312                let is_binary = if let Expression::Cast(ref c) = a {
3313                    matches!(
3314                        &c.to,
3315                        DataType::Binary { .. } | DataType::VarBinary { .. } | DataType::Blob
3316                    ) || matches!(&c.to, DataType::Custom { name } if name == "BLOB")
3317                } else {
3318                    false
3319                };
3320                if is_binary {
3321                    // CAST(CAST(a AS BIT) << b AS BLOB)
3322                    let cast_to_bit = Expression::Cast(Box::new(Cast {
3323                        this: a,
3324                        to: DataType::Custom {
3325                            name: "BIT".to_string(),
3326                        },
3327                        trailing_comments: Vec::new(),
3328                        double_colon_syntax: false,
3329                        format: None,
3330                        default: None,
3331                        inferred_type: None,
3332                    }));
3333                    let shift = Expression::BitwiseLeftShift(Box::new(BinaryOp {
3334                        left: cast_to_bit,
3335                        right: b,
3336                        left_comments: Vec::new(),
3337                        operator_comments: Vec::new(),
3338                        trailing_comments: Vec::new(),
3339                        inferred_type: None,
3340                    }));
3341                    Ok(Expression::Cast(Box::new(Cast {
3342                        this: shift,
3343                        to: DataType::Custom {
3344                            name: "BLOB".to_string(),
3345                        },
3346                        trailing_comments: Vec::new(),
3347                        double_colon_syntax: false,
3348                        format: None,
3349                        default: None,
3350                        inferred_type: None,
3351                    })))
3352                } else {
3353                    Ok(Expression::BitwiseLeftShift(Box::new(BinaryOp {
3354                        left: Expression::Cast(Box::new(Cast {
3355                            this: a,
3356                            to: DataType::Custom {
3357                                name: "INT128".to_string(),
3358                            },
3359                            trailing_comments: Vec::new(),
3360                            double_colon_syntax: false,
3361                            format: None,
3362                            default: None,
3363                            inferred_type: None,
3364                        })),
3365                        right: b,
3366                        left_comments: Vec::new(),
3367                        operator_comments: Vec::new(),
3368                        trailing_comments: Vec::new(),
3369                        inferred_type: None,
3370                    })))
3371                }
3372            }
3373            "BITSHIFTRIGHT" if f.args.len() == 2 => {
3374                let mut args = f.args;
3375                let a = args.remove(0);
3376                let b = args.remove(0);
3377                // Check if first arg is BINARY/BLOB type (e.g., X'002A'::BINARY)
3378                let is_binary = if let Expression::Cast(ref c) = a {
3379                    matches!(
3380                        &c.to,
3381                        DataType::Binary { .. } | DataType::VarBinary { .. } | DataType::Blob
3382                    ) || matches!(&c.to, DataType::Custom { name } if name == "BLOB")
3383                } else {
3384                    false
3385                };
3386                if is_binary {
3387                    // CAST(CAST(a AS BIT) >> b AS BLOB)
3388                    let cast_to_bit = Expression::Cast(Box::new(Cast {
3389                        this: a,
3390                        to: DataType::Custom {
3391                            name: "BIT".to_string(),
3392                        },
3393                        trailing_comments: Vec::new(),
3394                        double_colon_syntax: false,
3395                        format: None,
3396                        default: None,
3397                        inferred_type: None,
3398                    }));
3399                    let shift = Expression::BitwiseRightShift(Box::new(BinaryOp {
3400                        left: cast_to_bit,
3401                        right: b,
3402                        left_comments: Vec::new(),
3403                        operator_comments: Vec::new(),
3404                        trailing_comments: Vec::new(),
3405                        inferred_type: None,
3406                    }));
3407                    Ok(Expression::Cast(Box::new(Cast {
3408                        this: shift,
3409                        to: DataType::Custom {
3410                            name: "BLOB".to_string(),
3411                        },
3412                        trailing_comments: Vec::new(),
3413                        double_colon_syntax: false,
3414                        format: None,
3415                        default: None,
3416                        inferred_type: None,
3417                    })))
3418                } else {
3419                    Ok(Expression::BitwiseRightShift(Box::new(BinaryOp {
3420                        left: Expression::Cast(Box::new(Cast {
3421                            this: a,
3422                            to: DataType::Custom {
3423                                name: "INT128".to_string(),
3424                            },
3425                            trailing_comments: Vec::new(),
3426                            double_colon_syntax: false,
3427                            format: None,
3428                            default: None,
3429                            inferred_type: None,
3430                        })),
3431                        right: b,
3432                        left_comments: Vec::new(),
3433                        operator_comments: Vec::new(),
3434                        trailing_comments: Vec::new(),
3435                        inferred_type: None,
3436                    })))
3437                }
3438            }
3439            "SQUARE" if f.args.len() == 1 => {
3440                let arg = f.args.into_iter().next().unwrap();
3441                Ok(Expression::Function(Box::new(Function::new(
3442                    "POWER".to_string(),
3443                    vec![arg, Expression::number(2)],
3444                ))))
3445            }
3446            "UUID_STRING" => Ok(Expression::Function(Box::new(Function::new(
3447                "UUID".to_string(),
3448                vec![],
3449            )))),
3450            "ENDSWITH" => Ok(Expression::Function(Box::new(Function::new(
3451                "ENDS_WITH".to_string(),
3452                f.args,
3453            )))),
3454            // REGEXP_REPLACE: 'g' flag is handled by cross_dialect_normalize for source dialects
3455            // that default to global replacement (e.g., Snowflake). DuckDB defaults to first-match,
3456            // so no 'g' flag needed for DuckDB identity or PostgreSQL->DuckDB.
3457            "REGEXP_REPLACE" if f.args.len() == 2 => {
3458                // 2-arg form (subject, pattern) -> add empty replacement
3459                let mut args = f.args;
3460                args.push(Expression::Literal(Box::new(
3461                    Literal::String(String::new()),
3462                )));
3463                Ok(Expression::Function(Box::new(Function::new(
3464                    "REGEXP_REPLACE".to_string(),
3465                    args,
3466                ))))
3467            }
3468            "DIV0" if f.args.len() == 2 => {
3469                let mut args = f.args;
3470                let a = args.remove(0);
3471                let b = args.remove(0);
3472                Ok(Expression::Case(Box::new(Case {
3473                    operand: None,
3474                    whens: vec![(
3475                        Expression::And(Box::new(BinaryOp {
3476                            left: Expression::Eq(Box::new(BinaryOp {
3477                                left: b.clone(),
3478                                right: Expression::number(0),
3479                                left_comments: Vec::new(),
3480                                operator_comments: Vec::new(),
3481                                trailing_comments: Vec::new(),
3482                                inferred_type: None,
3483                            })),
3484                            right: Expression::Not(Box::new(crate::expressions::UnaryOp {
3485                                this: Expression::IsNull(Box::new(crate::expressions::IsNull {
3486                                    this: a.clone(),
3487                                    not: false,
3488                                    postfix_form: false,
3489                                })),
3490                                inferred_type: None,
3491                            })),
3492                            left_comments: Vec::new(),
3493                            operator_comments: Vec::new(),
3494                            trailing_comments: Vec::new(),
3495                            inferred_type: None,
3496                        })),
3497                        Expression::number(0),
3498                    )],
3499                    else_: Some(Expression::Div(Box::new(BinaryOp {
3500                        left: a,
3501                        right: b,
3502                        left_comments: Vec::new(),
3503                        operator_comments: Vec::new(),
3504                        trailing_comments: Vec::new(),
3505                        inferred_type: None,
3506                    }))),
3507                    comments: Vec::new(),
3508                    inferred_type: None,
3509                })))
3510            }
3511            "DIV0NULL" if f.args.len() == 2 => {
3512                let mut args = f.args;
3513                let a = args.remove(0);
3514                let b = args.remove(0);
3515                Ok(Expression::Case(Box::new(Case {
3516                    operand: None,
3517                    whens: vec![(
3518                        Expression::Or(Box::new(BinaryOp {
3519                            left: Expression::Eq(Box::new(BinaryOp {
3520                                left: b.clone(),
3521                                right: Expression::number(0),
3522                                left_comments: Vec::new(),
3523                                operator_comments: Vec::new(),
3524                                trailing_comments: Vec::new(),
3525                                inferred_type: None,
3526                            })),
3527                            right: Expression::IsNull(Box::new(crate::expressions::IsNull {
3528                                this: b.clone(),
3529                                not: false,
3530                                postfix_form: false,
3531                            })),
3532                            left_comments: Vec::new(),
3533                            operator_comments: Vec::new(),
3534                            trailing_comments: Vec::new(),
3535                            inferred_type: None,
3536                        })),
3537                        Expression::number(0),
3538                    )],
3539                    else_: Some(Expression::Div(Box::new(BinaryOp {
3540                        left: a,
3541                        right: b,
3542                        left_comments: Vec::new(),
3543                        operator_comments: Vec::new(),
3544                        trailing_comments: Vec::new(),
3545                        inferred_type: None,
3546                    }))),
3547                    comments: Vec::new(),
3548                    inferred_type: None,
3549                })))
3550            }
3551            "ZEROIFNULL" if f.args.len() == 1 => {
3552                let x = f.args.into_iter().next().unwrap();
3553                Ok(Expression::Case(Box::new(Case {
3554                    operand: None,
3555                    whens: vec![(
3556                        Expression::IsNull(Box::new(crate::expressions::IsNull {
3557                            this: x.clone(),
3558                            not: false,
3559                            postfix_form: false,
3560                        })),
3561                        Expression::number(0),
3562                    )],
3563                    else_: Some(x),
3564                    comments: Vec::new(),
3565                    inferred_type: None,
3566                })))
3567            }
3568            "NULLIFZERO" if f.args.len() == 1 => {
3569                let x = f.args.into_iter().next().unwrap();
3570                Ok(Expression::Case(Box::new(Case {
3571                    operand: None,
3572                    whens: vec![(
3573                        Expression::Eq(Box::new(BinaryOp {
3574                            left: x.clone(),
3575                            right: Expression::number(0),
3576                            left_comments: Vec::new(),
3577                            operator_comments: Vec::new(),
3578                            trailing_comments: Vec::new(),
3579                            inferred_type: None,
3580                        })),
3581                        Expression::Null(crate::expressions::Null),
3582                    )],
3583                    else_: Some(x),
3584                    comments: Vec::new(),
3585                    inferred_type: None,
3586                })))
3587            }
3588            "TO_DOUBLE" if f.args.len() == 1 => {
3589                let arg = f.args.into_iter().next().unwrap();
3590                Ok(Expression::Cast(Box::new(Cast {
3591                    this: arg,
3592                    to: DataType::Double {
3593                        precision: None,
3594                        scale: None,
3595                    },
3596                    trailing_comments: Vec::new(),
3597                    double_colon_syntax: false,
3598                    format: None,
3599                    default: None,
3600                    inferred_type: None,
3601                })))
3602            }
3603            "DATE" if f.args.len() == 1 => {
3604                let arg = f.args.into_iter().next().unwrap();
3605                Ok(Expression::Cast(Box::new(Cast {
3606                    this: arg,
3607                    to: DataType::Date,
3608                    trailing_comments: Vec::new(),
3609                    double_colon_syntax: false,
3610                    format: None,
3611                    default: None,
3612                    inferred_type: None,
3613                })))
3614            }
3615            "DATE" if f.args.len() == 2 => {
3616                let mut args = f.args;
3617                let value = args.remove(0);
3618                let fmt = self.convert_snowflake_date_format(args.remove(0));
3619                Ok(Expression::Cast(Box::new(Cast {
3620                    this: Expression::Function(Box::new(Function::new(
3621                        "STRPTIME".to_string(),
3622                        vec![value, fmt],
3623                    ))),
3624                    to: DataType::Date,
3625                    trailing_comments: Vec::new(),
3626                    double_colon_syntax: false,
3627                    format: None,
3628                    default: None,
3629                    inferred_type: None,
3630                })))
3631            }
3632            "SYSDATE" => Ok(Expression::AtTimeZone(Box::new(
3633                crate::expressions::AtTimeZone {
3634                    this: Expression::CurrentTimestamp(crate::expressions::CurrentTimestamp {
3635                        precision: None,
3636                        sysdate: false,
3637                    }),
3638                    zone: Expression::Literal(Box::new(Literal::String("UTC".to_string()))),
3639                },
3640            ))),
3641            "HEX_DECODE_BINARY" => Ok(Expression::Function(Box::new(Function::new(
3642                "UNHEX".to_string(),
3643                f.args,
3644            )))),
3645            "CONVERT_TIMEZONE" if f.args.len() == 3 => {
3646                let mut args = f.args;
3647                let src_tz = args.remove(0);
3648                let tgt_tz = args.remove(0);
3649                let ts = args.remove(0);
3650                let cast_ts = Expression::Cast(Box::new(Cast {
3651                    this: ts,
3652                    to: DataType::Timestamp {
3653                        precision: None,
3654                        timezone: false,
3655                    },
3656                    trailing_comments: Vec::new(),
3657                    double_colon_syntax: false,
3658                    format: None,
3659                    default: None,
3660                    inferred_type: None,
3661                }));
3662                Ok(Expression::AtTimeZone(Box::new(
3663                    crate::expressions::AtTimeZone {
3664                        this: Expression::AtTimeZone(Box::new(crate::expressions::AtTimeZone {
3665                            this: cast_ts,
3666                            zone: src_tz,
3667                        })),
3668                        zone: tgt_tz,
3669                    },
3670                )))
3671            }
3672            "CONVERT_TIMEZONE" if f.args.len() == 2 => {
3673                let mut args = f.args;
3674                let tgt_tz = args.remove(0);
3675                let ts = args.remove(0);
3676                let cast_ts = Expression::Cast(Box::new(Cast {
3677                    this: ts,
3678                    to: DataType::Timestamp {
3679                        precision: None,
3680                        timezone: false,
3681                    },
3682                    trailing_comments: Vec::new(),
3683                    double_colon_syntax: false,
3684                    format: None,
3685                    default: None,
3686                    inferred_type: None,
3687                }));
3688                Ok(Expression::AtTimeZone(Box::new(
3689                    crate::expressions::AtTimeZone {
3690                        this: cast_ts,
3691                        zone: tgt_tz,
3692                    },
3693                )))
3694            }
3695            "DATE_PART" | "DATEPART" if f.args.len() == 2 => self.transform_date_part(f.args),
3696            "DATEADD" | "TIMEADD" if f.args.len() == 3 => self.transform_dateadd(f.args),
3697            "TIMESTAMPADD" if f.args.len() == 3 => self.transform_dateadd(f.args),
3698            "DATEDIFF" | "TIMEDIFF" if f.args.len() == 3 => self.transform_datediff(f.args),
3699            "TIMESTAMPDIFF" if f.args.len() == 3 => self.transform_datediff(f.args),
3700            "CORR" if f.args.len() == 2 => {
3701                // DuckDB handles NaN natively - no ISNAN wrapping needed
3702                Ok(Expression::Function(Box::new(f)))
3703            }
3704            "TO_TIMESTAMP" | "TO_TIMESTAMP_NTZ" if f.args.len() == 2 => {
3705                let mut args = f.args;
3706                let value = args.remove(0);
3707                let second_arg = args.remove(0);
3708                match &second_arg {
3709                    Expression::Literal(lit) if matches!(lit.as_ref(), Literal::Number(_)) => Ok(
3710                        Expression::AtTimeZone(Box::new(crate::expressions::AtTimeZone {
3711                            this: Expression::Function(Box::new(Function::new(
3712                                "TO_TIMESTAMP".to_string(),
3713                                vec![Expression::Div(Box::new(BinaryOp {
3714                                    left: value,
3715                                    right: Expression::Function(Box::new(Function::new(
3716                                        "POWER".to_string(),
3717                                        vec![Expression::number(10), second_arg],
3718                                    ))),
3719                                    left_comments: Vec::new(),
3720                                    operator_comments: Vec::new(),
3721                                    trailing_comments: Vec::new(),
3722                                    inferred_type: None,
3723                                }))],
3724                            ))),
3725                            zone: Expression::Literal(Box::new(Literal::String("UTC".to_string()))),
3726                        })),
3727                    ),
3728                    _ => {
3729                        let fmt = self.convert_snowflake_time_format(second_arg);
3730                        Ok(Expression::Function(Box::new(Function::new(
3731                            "STRPTIME".to_string(),
3732                            vec![value, fmt],
3733                        ))))
3734                    }
3735                }
3736            }
3737            "TO_TIME" if f.args.len() == 1 => {
3738                let arg = f.args.into_iter().next().unwrap();
3739                Ok(Expression::Cast(Box::new(Cast {
3740                    this: arg,
3741                    to: DataType::Time {
3742                        precision: None,
3743                        timezone: false,
3744                    },
3745                    trailing_comments: Vec::new(),
3746                    double_colon_syntax: false,
3747                    format: None,
3748                    default: None,
3749                    inferred_type: None,
3750                })))
3751            }
3752            "TO_TIME" if f.args.len() == 2 => {
3753                let mut args = f.args;
3754                let value = args.remove(0);
3755                let fmt = self.convert_snowflake_time_format(args.remove(0));
3756                Ok(Expression::Cast(Box::new(Cast {
3757                    this: Expression::Function(Box::new(Function::new(
3758                        "STRPTIME".to_string(),
3759                        vec![value, fmt],
3760                    ))),
3761                    to: DataType::Time {
3762                        precision: None,
3763                        timezone: false,
3764                    },
3765                    trailing_comments: Vec::new(),
3766                    double_colon_syntax: false,
3767                    format: None,
3768                    default: None,
3769                    inferred_type: None,
3770                })))
3771            }
3772            "TO_DATE" if f.args.len() == 2 => {
3773                let mut args = f.args;
3774                let value = args.remove(0);
3775                let fmt = self.convert_snowflake_date_format(args.remove(0));
3776                Ok(Expression::Cast(Box::new(Cast {
3777                    this: Expression::Function(Box::new(Function::new(
3778                        "STRPTIME".to_string(),
3779                        vec![value, fmt],
3780                    ))),
3781                    to: DataType::Date,
3782                    trailing_comments: Vec::new(),
3783                    double_colon_syntax: false,
3784                    format: None,
3785                    default: None,
3786                    inferred_type: None,
3787                })))
3788            }
3789            // LAST_DAY with 2 args handled by comprehensive handler below
3790
3791            // SAFE_DIVIDE(x, y) -> CASE WHEN y <> 0 THEN x / y ELSE NULL END
3792            "SAFE_DIVIDE" if f.args.len() == 2 => {
3793                let mut args = f.args;
3794                let x = args.remove(0);
3795                let y = args.remove(0);
3796                Ok(Expression::Case(Box::new(Case {
3797                    operand: None,
3798                    whens: vec![(
3799                        Expression::Neq(Box::new(BinaryOp {
3800                            left: y.clone(),
3801                            right: Expression::number(0),
3802                            left_comments: Vec::new(),
3803                            operator_comments: Vec::new(),
3804                            trailing_comments: Vec::new(),
3805                            inferred_type: None,
3806                        })),
3807                        Expression::Div(Box::new(BinaryOp {
3808                            left: x,
3809                            right: y,
3810                            left_comments: Vec::new(),
3811                            operator_comments: Vec::new(),
3812                            trailing_comments: Vec::new(),
3813                            inferred_type: None,
3814                        })),
3815                    )],
3816                    else_: Some(Expression::Null(crate::expressions::Null)),
3817                    comments: Vec::new(),
3818                    inferred_type: None,
3819                })))
3820            }
3821
3822            // TO_HEX(x) -> LOWER(HEX(x)) in DuckDB (BigQuery TO_HEX returns lowercase)
3823            "TO_HEX" if f.args.len() == 1 => {
3824                let arg = f.args.into_iter().next().unwrap();
3825                Ok(Expression::Lower(Box::new(UnaryFunc::new(
3826                    Expression::Function(Box::new(Function::new("HEX".to_string(), vec![arg]))),
3827                ))))
3828            }
3829
3830            // EDIT_DISTANCE -> LEVENSHTEIN in DuckDB
3831            "EDIT_DISTANCE" if f.args.len() >= 2 => {
3832                // Only use the first two args (drop max_distance kwarg)
3833                let mut args = f.args;
3834                let a = args.remove(0);
3835                let b = args.remove(0);
3836                Ok(Expression::Function(Box::new(Function::new(
3837                    "LEVENSHTEIN".to_string(),
3838                    vec![a, b],
3839                ))))
3840            }
3841
3842            // UNIX_DATE(d) -> DATE_DIFF('DAY', CAST('1970-01-01' AS DATE), d) in DuckDB
3843            "UNIX_DATE" if f.args.len() == 1 => {
3844                let arg = f.args.into_iter().next().unwrap();
3845                Ok(Expression::Function(Box::new(Function::new(
3846                    "DATE_DIFF".to_string(),
3847                    vec![
3848                        Expression::Literal(Box::new(Literal::String("DAY".to_string()))),
3849                        Expression::Cast(Box::new(Cast {
3850                            this: Expression::Literal(Box::new(Literal::String(
3851                                "1970-01-01".to_string(),
3852                            ))),
3853                            to: DataType::Date,
3854                            trailing_comments: Vec::new(),
3855                            double_colon_syntax: false,
3856                            format: None,
3857                            default: None,
3858                            inferred_type: None,
3859                        })),
3860                        arg,
3861                    ],
3862                ))))
3863            }
3864
3865            // TIMESTAMP(x) -> CAST(x AS TIMESTAMPTZ) in DuckDB
3866            "TIMESTAMP" if f.args.len() == 1 => {
3867                let arg = f.args.into_iter().next().unwrap();
3868                Ok(Expression::Cast(Box::new(Cast {
3869                    this: arg,
3870                    to: DataType::Custom {
3871                        name: "TIMESTAMPTZ".to_string(),
3872                    },
3873                    trailing_comments: Vec::new(),
3874                    double_colon_syntax: false,
3875                    format: None,
3876                    default: None,
3877                    inferred_type: None,
3878                })))
3879            }
3880
3881            // TIME(h, m, s) -> MAKE_TIME(h, m, s) in DuckDB
3882            "TIME" if f.args.len() == 3 => Ok(Expression::Function(Box::new(Function::new(
3883                "MAKE_TIME".to_string(),
3884                f.args,
3885            )))),
3886
3887            // DATE(y, m, d) -> MAKE_DATE(y, m, d) in DuckDB
3888            "DATE" if f.args.len() == 3 => Ok(Expression::Function(Box::new(Function::new(
3889                "MAKE_DATE".to_string(),
3890                f.args,
3891            )))),
3892
3893            // DATETIME(y, m, d, h, min, sec) -> MAKE_TIMESTAMP(y, m, d, h, min, sec) in DuckDB
3894            "DATETIME" if f.args.len() == 6 => Ok(Expression::Function(Box::new(Function::new(
3895                "MAKE_TIMESTAMP".to_string(),
3896                f.args,
3897            )))),
3898
3899            // PARSE_TIMESTAMP(fmt, x) -> STRPTIME(x, fmt) in DuckDB (swap args)
3900            "PARSE_TIMESTAMP" if f.args.len() >= 2 => {
3901                let mut args = f.args;
3902                let fmt = args.remove(0);
3903                let value = args.remove(0);
3904                // Convert BigQuery format to DuckDB strptime format
3905                let duckdb_fmt = self.convert_bq_to_strptime_format(fmt);
3906                Ok(Expression::Function(Box::new(Function::new(
3907                    "STRPTIME".to_string(),
3908                    vec![value, duckdb_fmt],
3909                ))))
3910            }
3911
3912            // BOOLAND(a, b) -> ((ROUND(a, 0)) AND (ROUND(b, 0)))
3913            "BOOLAND" if f.args.len() == 2 => {
3914                let mut args = f.args;
3915                let a = args.remove(0);
3916                let b = args.remove(0);
3917                let ra = Expression::Function(Box::new(Function::new(
3918                    "ROUND".to_string(),
3919                    vec![a, Expression::number(0)],
3920                )));
3921                let rb = Expression::Function(Box::new(Function::new(
3922                    "ROUND".to_string(),
3923                    vec![b, Expression::number(0)],
3924                )));
3925                Ok(Expression::Paren(Box::new(Paren {
3926                    this: Expression::And(Box::new(BinaryOp {
3927                        left: Expression::Paren(Box::new(Paren {
3928                            this: ra,
3929                            trailing_comments: Vec::new(),
3930                        })),
3931                        right: Expression::Paren(Box::new(Paren {
3932                            this: rb,
3933                            trailing_comments: Vec::new(),
3934                        })),
3935                        left_comments: Vec::new(),
3936                        operator_comments: Vec::new(),
3937                        trailing_comments: Vec::new(),
3938                        inferred_type: None,
3939                    })),
3940                    trailing_comments: Vec::new(),
3941                })))
3942            }
3943
3944            // BOOLOR(a, b) -> ((ROUND(a, 0)) OR (ROUND(b, 0)))
3945            "BOOLOR" if f.args.len() == 2 => {
3946                let mut args = f.args;
3947                let a = args.remove(0);
3948                let b = args.remove(0);
3949                let ra = Expression::Function(Box::new(Function::new(
3950                    "ROUND".to_string(),
3951                    vec![a, Expression::number(0)],
3952                )));
3953                let rb = Expression::Function(Box::new(Function::new(
3954                    "ROUND".to_string(),
3955                    vec![b, Expression::number(0)],
3956                )));
3957                Ok(Expression::Paren(Box::new(Paren {
3958                    this: Expression::Or(Box::new(BinaryOp {
3959                        left: Expression::Paren(Box::new(Paren {
3960                            this: ra,
3961                            trailing_comments: Vec::new(),
3962                        })),
3963                        right: Expression::Paren(Box::new(Paren {
3964                            this: rb,
3965                            trailing_comments: Vec::new(),
3966                        })),
3967                        left_comments: Vec::new(),
3968                        operator_comments: Vec::new(),
3969                        trailing_comments: Vec::new(),
3970                        inferred_type: None,
3971                    })),
3972                    trailing_comments: Vec::new(),
3973                })))
3974            }
3975
3976            // BOOLXOR(a, b) -> (ROUND(a, 0) AND (NOT ROUND(b, 0))) OR ((NOT ROUND(a, 0)) AND ROUND(b, 0))
3977            "BOOLXOR" if f.args.len() == 2 => {
3978                let mut args = f.args;
3979                let a = args.remove(0);
3980                let b = args.remove(0);
3981                let ra = Expression::Function(Box::new(Function::new(
3982                    "ROUND".to_string(),
3983                    vec![a, Expression::number(0)],
3984                )));
3985                let rb = Expression::Function(Box::new(Function::new(
3986                    "ROUND".to_string(),
3987                    vec![b, Expression::number(0)],
3988                )));
3989                // (ra AND (NOT rb)) OR ((NOT ra) AND rb)
3990                let not_rb = Expression::Not(Box::new(crate::expressions::UnaryOp {
3991                    this: rb.clone(),
3992                    inferred_type: None,
3993                }));
3994                let not_ra = Expression::Not(Box::new(crate::expressions::UnaryOp {
3995                    this: ra.clone(),
3996                    inferred_type: None,
3997                }));
3998                let left_and = Expression::And(Box::new(BinaryOp {
3999                    left: ra,
4000                    right: Expression::Paren(Box::new(Paren {
4001                        this: not_rb,
4002                        trailing_comments: Vec::new(),
4003                    })),
4004                    left_comments: Vec::new(),
4005                    operator_comments: Vec::new(),
4006                    trailing_comments: Vec::new(),
4007                    inferred_type: None,
4008                }));
4009                let right_and = Expression::And(Box::new(BinaryOp {
4010                    left: Expression::Paren(Box::new(Paren {
4011                        this: not_ra,
4012                        trailing_comments: Vec::new(),
4013                    })),
4014                    right: rb,
4015                    left_comments: Vec::new(),
4016                    operator_comments: Vec::new(),
4017                    trailing_comments: Vec::new(),
4018                    inferred_type: None,
4019                }));
4020                Ok(Expression::Or(Box::new(BinaryOp {
4021                    left: Expression::Paren(Box::new(Paren {
4022                        this: left_and,
4023                        trailing_comments: Vec::new(),
4024                    })),
4025                    right: Expression::Paren(Box::new(Paren {
4026                        this: right_and,
4027                        trailing_comments: Vec::new(),
4028                    })),
4029                    left_comments: Vec::new(),
4030                    operator_comments: Vec::new(),
4031                    trailing_comments: Vec::new(),
4032                    inferred_type: None,
4033                })))
4034            }
4035
4036            // DECODE(expr, search1, result1, ..., default) -> CASE WHEN expr = search1 THEN result1 ... ELSE default END
4037            // For NULL search values, use IS NULL instead of = NULL
4038            "DECODE" if f.args.len() >= 3 => {
4039                let mut args = f.args;
4040                let expr = args.remove(0);
4041                let mut whens = Vec::new();
4042                let mut else_expr = None;
4043                while args.len() >= 2 {
4044                    let search = args.remove(0);
4045                    let result = args.remove(0);
4046                    // For NULL search values, use IS NULL; otherwise use =
4047                    let condition = if matches!(&search, Expression::Null(_)) {
4048                        Expression::IsNull(Box::new(crate::expressions::IsNull {
4049                            this: expr.clone(),
4050                            not: false,
4051                            postfix_form: false,
4052                        }))
4053                    } else {
4054                        Expression::Eq(Box::new(BinaryOp {
4055                            left: expr.clone(),
4056                            right: search,
4057                            left_comments: Vec::new(),
4058                            operator_comments: Vec::new(),
4059                            trailing_comments: Vec::new(),
4060                            inferred_type: None,
4061                        }))
4062                    };
4063                    whens.push((condition, result));
4064                }
4065                if !args.is_empty() {
4066                    else_expr = Some(args.remove(0));
4067                }
4068                Ok(Expression::Case(Box::new(Case {
4069                    operand: None,
4070                    whens,
4071                    else_: else_expr,
4072                    comments: Vec::new(),
4073                    inferred_type: None,
4074                })))
4075            }
4076
4077            // TRY_TO_BOOLEAN -> CASE WHEN UPPER(CAST(x AS TEXT)) = 'ON' THEN TRUE WHEN ... = 'OFF' THEN FALSE ELSE TRY_CAST(x AS BOOLEAN) END
4078            "TRY_TO_BOOLEAN" if f.args.len() == 1 => {
4079                let arg = f.args.into_iter().next().unwrap();
4080                let cast_text = Expression::Cast(Box::new(Cast {
4081                    this: arg.clone(),
4082                    to: DataType::Text,
4083                    trailing_comments: Vec::new(),
4084                    double_colon_syntax: false,
4085                    format: None,
4086                    default: None,
4087                    inferred_type: None,
4088                }));
4089                let upper_text = Expression::Upper(Box::new(UnaryFunc::new(cast_text)));
4090                Ok(Expression::Case(Box::new(Case {
4091                    operand: None,
4092                    whens: vec![
4093                        (
4094                            Expression::Eq(Box::new(BinaryOp {
4095                                left: upper_text.clone(),
4096                                right: Expression::Literal(Box::new(Literal::String(
4097                                    "ON".to_string(),
4098                                ))),
4099                                left_comments: Vec::new(),
4100                                operator_comments: Vec::new(),
4101                                trailing_comments: Vec::new(),
4102                                inferred_type: None,
4103                            })),
4104                            Expression::Boolean(crate::expressions::BooleanLiteral { value: true }),
4105                        ),
4106                        (
4107                            Expression::Eq(Box::new(BinaryOp {
4108                                left: upper_text,
4109                                right: Expression::Literal(Box::new(Literal::String(
4110                                    "OFF".to_string(),
4111                                ))),
4112                                left_comments: Vec::new(),
4113                                operator_comments: Vec::new(),
4114                                trailing_comments: Vec::new(),
4115                                inferred_type: None,
4116                            })),
4117                            Expression::Boolean(crate::expressions::BooleanLiteral {
4118                                value: false,
4119                            }),
4120                        ),
4121                    ],
4122                    else_: Some(Expression::TryCast(Box::new(Cast {
4123                        this: arg,
4124                        to: DataType::Boolean,
4125                        trailing_comments: Vec::new(),
4126                        double_colon_syntax: false,
4127                        format: None,
4128                        default: None,
4129                        inferred_type: None,
4130                    }))),
4131                    comments: Vec::new(),
4132                    inferred_type: None,
4133                })))
4134            }
4135
4136            // TO_BOOLEAN -> complex CASE expression
4137            "TO_BOOLEAN" if f.args.len() == 1 => {
4138                let arg = f.args.into_iter().next().unwrap();
4139                let cast_text = Expression::Cast(Box::new(Cast {
4140                    this: arg.clone(),
4141                    to: DataType::Text,
4142                    trailing_comments: Vec::new(),
4143                    double_colon_syntax: false,
4144                    format: None,
4145                    default: None,
4146                    inferred_type: None,
4147                }));
4148                let upper_text = Expression::Upper(Box::new(UnaryFunc::new(cast_text)));
4149                Ok(Expression::Case(Box::new(Case {
4150                    operand: None,
4151                    whens: vec![
4152                        (
4153                            Expression::Eq(Box::new(BinaryOp {
4154                                left: upper_text.clone(),
4155                                right: Expression::Literal(Box::new(Literal::String(
4156                                    "ON".to_string(),
4157                                ))),
4158                                left_comments: Vec::new(),
4159                                operator_comments: Vec::new(),
4160                                trailing_comments: Vec::new(),
4161                                inferred_type: None,
4162                            })),
4163                            Expression::Boolean(crate::expressions::BooleanLiteral { value: true }),
4164                        ),
4165                        (
4166                            Expression::Eq(Box::new(BinaryOp {
4167                                left: upper_text,
4168                                right: Expression::Literal(Box::new(Literal::String(
4169                                    "OFF".to_string(),
4170                                ))),
4171                                left_comments: Vec::new(),
4172                                operator_comments: Vec::new(),
4173                                trailing_comments: Vec::new(),
4174                                inferred_type: None,
4175                            })),
4176                            Expression::Boolean(crate::expressions::BooleanLiteral {
4177                                value: false,
4178                            }),
4179                        ),
4180                        (
4181                            Expression::Or(Box::new(BinaryOp {
4182                                left: Expression::Function(Box::new(Function::new(
4183                                    "ISNAN".to_string(),
4184                                    vec![Expression::TryCast(Box::new(Cast {
4185                                        this: arg.clone(),
4186                                        to: DataType::Custom {
4187                                            name: "REAL".to_string(),
4188                                        },
4189                                        trailing_comments: Vec::new(),
4190                                        double_colon_syntax: false,
4191                                        format: None,
4192                                        default: None,
4193                                        inferred_type: None,
4194                                    }))],
4195                                ))),
4196                                right: Expression::Function(Box::new(Function::new(
4197                                    "ISINF".to_string(),
4198                                    vec![Expression::TryCast(Box::new(Cast {
4199                                        this: arg.clone(),
4200                                        to: DataType::Custom {
4201                                            name: "REAL".to_string(),
4202                                        },
4203                                        trailing_comments: Vec::new(),
4204                                        double_colon_syntax: false,
4205                                        format: None,
4206                                        default: None,
4207                                        inferred_type: None,
4208                                    }))],
4209                                ))),
4210                                left_comments: Vec::new(),
4211                                operator_comments: Vec::new(),
4212                                trailing_comments: Vec::new(),
4213                                inferred_type: None,
4214                            })),
4215                            Expression::Function(Box::new(Function::new(
4216                                "ERROR".to_string(),
4217                                vec![Expression::Literal(Box::new(Literal::String(
4218                                    "TO_BOOLEAN: Non-numeric values NaN and INF are not supported"
4219                                        .to_string(),
4220                                )))],
4221                            ))),
4222                        ),
4223                    ],
4224                    else_: Some(Expression::Cast(Box::new(Cast {
4225                        this: arg,
4226                        to: DataType::Boolean,
4227                        trailing_comments: Vec::new(),
4228                        double_colon_syntax: false,
4229                        format: None,
4230                        default: None,
4231                        inferred_type: None,
4232                    }))),
4233                    comments: Vec::new(),
4234                    inferred_type: None,
4235                })))
4236            }
4237
4238            // OBJECT_INSERT(obj, key, value) -> STRUCT_INSERT(obj, key := value)
4239            // Special case: OBJECT_INSERT(OBJECT_CONSTRUCT(), key, value) -> STRUCT_PACK(key := value)
4240            "OBJECT_INSERT" if f.args.len() == 3 => {
4241                let mut args = f.args;
4242                let obj = args.remove(0);
4243                let key = args.remove(0);
4244                let value = args.remove(0);
4245                // Extract key string for named arg
4246                let key_name = match &key {
4247                    Expression::Literal(lit) if matches!(lit.as_ref(), Literal::String(_)) => {
4248                        let Literal::String(s) = lit.as_ref() else {
4249                            unreachable!()
4250                        };
4251                        s.clone()
4252                    }
4253                    _ => "key".to_string(),
4254                };
4255                let named_arg =
4256                    Expression::NamedArgument(Box::new(crate::expressions::NamedArgument {
4257                        name: Identifier::new(&key_name),
4258                        value,
4259                        separator: crate::expressions::NamedArgSeparator::ColonEq,
4260                    }));
4261                // Check if the inner object is an empty STRUCT_PACK or OBJECT_CONSTRUCT
4262                let is_empty_struct = match &obj {
4263                    Expression::Struct(s) if s.fields.is_empty() => true,
4264                    Expression::Function(f) => {
4265                        let n = f.name.to_uppercase();
4266                        (n == "STRUCT_PACK" || n == "OBJECT_CONSTRUCT") && f.args.is_empty()
4267                    }
4268                    _ => false,
4269                };
4270                if is_empty_struct {
4271                    // Collapse: OBJECT_INSERT(empty, key, value) -> STRUCT_PACK(key := value)
4272                    Ok(Expression::Function(Box::new(Function::new(
4273                        "STRUCT_PACK".to_string(),
4274                        vec![named_arg],
4275                    ))))
4276                } else {
4277                    Ok(Expression::Function(Box::new(Function::new(
4278                        "STRUCT_INSERT".to_string(),
4279                        vec![obj, named_arg],
4280                    ))))
4281                }
4282            }
4283
4284            // GET(array_or_obj, key) -> array[key+1] for arrays, obj -> '$.key' for objects
4285            "GET" if f.args.len() == 2 => {
4286                let mut args = f.args;
4287                let this = args.remove(0);
4288                let key = args.remove(0);
4289                match &key {
4290                    // String key -> JSON extract (object access)
4291                    Expression::Literal(lit) if matches!(lit.as_ref(), Literal::String(_)) => {
4292                        let Literal::String(s) = lit.as_ref() else {
4293                            unreachable!()
4294                        };
4295                        let json_path = format!("$.{}", s);
4296                        Ok(Expression::JsonExtract(Box::new(JsonExtractFunc {
4297                            this,
4298                            path: Expression::Literal(Box::new(Literal::String(json_path))),
4299                            returning: None,
4300                            arrow_syntax: true,
4301                            hash_arrow_syntax: false,
4302                            wrapper_option: None,
4303                            quotes_option: None,
4304                            on_scalar_string: false,
4305                            on_error: None,
4306                        })))
4307                    }
4308                    // Numeric key -> array subscript
4309                    // For MAP access: key is used as-is (map[key])
4310                    // For ARRAY access: Snowflake is 0-based, DuckDB is 1-based, so add 1
4311                    Expression::Literal(lit) if matches!(lit.as_ref(), Literal::Number(_)) => {
4312                        let Literal::Number(n) = lit.as_ref() else {
4313                            unreachable!()
4314                        };
4315                        let idx: i64 = n.parse().unwrap_or(0);
4316                        let is_map = matches!(&this, Expression::Cast(c) if matches!(c.to, DataType::Map { .. }));
4317                        let index_val = if is_map { idx } else { idx + 1 };
4318                        Ok(Expression::Subscript(Box::new(
4319                            crate::expressions::Subscript {
4320                                this,
4321                                index: Expression::number(index_val),
4322                            },
4323                        )))
4324                    }
4325                    _ => {
4326                        // Unknown key type - use JSON arrow
4327                        Ok(Expression::JsonExtract(Box::new(JsonExtractFunc {
4328                            this,
4329                            path: Expression::JSONPath(Box::new(JSONPath {
4330                                expressions: vec![
4331                                    Expression::JSONPathRoot(JSONPathRoot),
4332                                    Expression::JSONPathKey(Box::new(JSONPathKey {
4333                                        this: Box::new(key),
4334                                    })),
4335                                ],
4336                                escape: None,
4337                            })),
4338                            returning: None,
4339                            arrow_syntax: true,
4340                            hash_arrow_syntax: false,
4341                            wrapper_option: None,
4342                            quotes_option: None,
4343                            on_scalar_string: false,
4344                            on_error: None,
4345                        })))
4346                    }
4347                }
4348            }
4349
4350            // GET_PATH(obj, path) -> obj -> json_path in DuckDB
4351            "GET_PATH" if f.args.len() == 2 => {
4352                let mut args = f.args;
4353                let this = args.remove(0);
4354                let path = args.remove(0);
4355                // Convert Snowflake path to JSONPath
4356                let json_path = match &path {
4357                    Expression::Literal(lit) if matches!(lit.as_ref(), Literal::String(_)) => {
4358                        let Literal::String(s) = lit.as_ref() else {
4359                            unreachable!()
4360                        };
4361                        // Convert bracket notation ["key"] to quoted dot notation ."key"
4362                        let s = Self::convert_bracket_to_quoted_path(s);
4363                        // Convert Snowflake path (e.g., 'attr[0].name' or '[0].attr') to JSON path ($.attr[0].name or $[0].attr)
4364                        let normalized = if s.starts_with('$') {
4365                            s
4366                        } else if s.starts_with('[') {
4367                            format!("${}", s)
4368                        } else {
4369                            format!("$.{}", s)
4370                        };
4371                        Expression::Literal(Box::new(Literal::String(normalized)))
4372                    }
4373                    _ => path,
4374                };
4375                Ok(Expression::JsonExtract(Box::new(JsonExtractFunc {
4376                    this,
4377                    path: json_path,
4378                    returning: None,
4379                    arrow_syntax: true,
4380                    hash_arrow_syntax: false,
4381                    wrapper_option: None,
4382                    quotes_option: None,
4383                    on_scalar_string: false,
4384                    on_error: None,
4385                })))
4386            }
4387
4388            // BASE64_ENCODE(x) -> TO_BASE64(x)
4389            "BASE64_ENCODE" if f.args.len() == 1 => Ok(Expression::Function(Box::new(
4390                Function::new("TO_BASE64".to_string(), f.args),
4391            ))),
4392
4393            // BASE64_ENCODE(x, max_line_length) -> RTRIM(REGEXP_REPLACE(TO_BASE64(x), '(.{N})', '\1' || CHR(10), 'g'), CHR(10))
4394            "BASE64_ENCODE" if f.args.len() >= 2 => {
4395                let mut args = f.args;
4396                let x = args.remove(0);
4397                let line_len = args.remove(0);
4398                let line_len_str = match &line_len {
4399                    Expression::Literal(lit) if matches!(lit.as_ref(), Literal::Number(_)) => {
4400                        let Literal::Number(n) = lit.as_ref() else {
4401                            unreachable!()
4402                        };
4403                        n.clone()
4404                    }
4405                    _ => "76".to_string(),
4406                };
4407                let to_base64 =
4408                    Expression::Function(Box::new(Function::new("TO_BASE64".to_string(), vec![x])));
4409                let pattern = format!("(.{{{}}})", line_len_str);
4410                let chr_10 = Expression::Function(Box::new(Function::new(
4411                    "CHR".to_string(),
4412                    vec![Expression::number(10)],
4413                )));
4414                let replacement = Expression::Concat(Box::new(BinaryOp {
4415                    left: Expression::Literal(Box::new(Literal::String("\\1".to_string()))),
4416                    right: chr_10.clone(),
4417                    left_comments: Vec::new(),
4418                    operator_comments: Vec::new(),
4419                    trailing_comments: Vec::new(),
4420                    inferred_type: None,
4421                }));
4422                let regexp_replace = Expression::Function(Box::new(Function::new(
4423                    "REGEXP_REPLACE".to_string(),
4424                    vec![
4425                        to_base64,
4426                        Expression::Literal(Box::new(Literal::String(pattern))),
4427                        replacement,
4428                        Expression::Literal(Box::new(Literal::String("g".to_string()))),
4429                    ],
4430                )));
4431                Ok(Expression::Function(Box::new(Function::new(
4432                    "RTRIM".to_string(),
4433                    vec![regexp_replace, chr_10],
4434                ))))
4435            }
4436
4437            // TRY_TO_DATE with 2 args -> CAST(CAST(TRY_STRPTIME(value, fmt) AS TIMESTAMP) AS DATE)
4438            "TRY_TO_DATE" if f.args.len() == 2 => {
4439                let mut args = f.args;
4440                let value = args.remove(0);
4441                let fmt = self.convert_snowflake_date_format(args.remove(0));
4442                Ok(Expression::Cast(Box::new(Cast {
4443                    this: Expression::Cast(Box::new(Cast {
4444                        this: Expression::Function(Box::new(Function::new(
4445                            "TRY_STRPTIME".to_string(),
4446                            vec![value, fmt],
4447                        ))),
4448                        to: DataType::Timestamp {
4449                            precision: None,
4450                            timezone: false,
4451                        },
4452                        trailing_comments: Vec::new(),
4453                        double_colon_syntax: false,
4454                        format: None,
4455                        default: None,
4456                        inferred_type: None,
4457                    })),
4458                    to: DataType::Date,
4459                    trailing_comments: Vec::new(),
4460                    double_colon_syntax: false,
4461                    format: None,
4462                    default: None,
4463                    inferred_type: None,
4464                })))
4465            }
4466
4467            // REGEXP_REPLACE with 4 args: check if 4th arg is a number (Snowflake position) or flags (DuckDB native)
4468            // REGEXP_REPLACE with 4 args: check if 4th is a string flag (DuckDB native) or a numeric position
4469            "REGEXP_REPLACE" if f.args.len() == 4 => {
4470                let is_snowflake_position = matches!(&f.args[3], Expression::Literal(lit) if matches!(lit.as_ref(), Literal::Number(_)));
4471                if is_snowflake_position {
4472                    // Snowflake form: REGEXP_REPLACE(subject, pattern, replacement, position) -> add 'g' flag
4473                    let mut args = f.args;
4474                    let subject = args.remove(0);
4475                    let pattern = args.remove(0);
4476                    let replacement = args.remove(0);
4477                    Ok(Expression::Function(Box::new(Function::new(
4478                        "REGEXP_REPLACE".to_string(),
4479                        vec![
4480                            subject,
4481                            pattern,
4482                            replacement,
4483                            Expression::Literal(Box::new(Literal::String("g".to_string()))),
4484                        ],
4485                    ))))
4486                } else {
4487                    // DuckDB native form (string flags) or pass through
4488                    Ok(Expression::Function(Box::new(f)))
4489                }
4490            }
4491
4492            // REGEXP_REPLACE with 5+ args -> Snowflake form: (subject, pattern, replacement, position, occurrence, params)
4493            "REGEXP_REPLACE" if f.args.len() >= 5 => {
4494                let mut args = f.args;
4495                let subject = args.remove(0);
4496                let pattern = args.remove(0);
4497                let replacement = args.remove(0);
4498                let _position = args.remove(0);
4499                let occurrence = if !args.is_empty() {
4500                    Some(args.remove(0))
4501                } else {
4502                    None
4503                };
4504                let params = if !args.is_empty() {
4505                    Some(args.remove(0))
4506                } else {
4507                    None
4508                };
4509
4510                let mut flags = String::new();
4511                if let Some(Expression::Literal(lit)) = &params {
4512                    if let Literal::String(p) = lit.as_ref() {
4513                        flags = p.clone();
4514                    }
4515                }
4516                let is_global = match &occurrence {
4517                    Some(Expression::Literal(lit))
4518                        if matches!(lit.as_ref(), Literal::Number(_)) =>
4519                    {
4520                        let Literal::Number(n) = lit.as_ref() else {
4521                            unreachable!()
4522                        };
4523                        n == "0"
4524                    }
4525                    None => true,
4526                    _ => false,
4527                };
4528                if is_global && !flags.contains('g') {
4529                    flags.push('g');
4530                }
4531
4532                Ok(Expression::Function(Box::new(Function::new(
4533                    "REGEXP_REPLACE".to_string(),
4534                    vec![
4535                        subject,
4536                        pattern,
4537                        replacement,
4538                        Expression::Literal(Box::new(Literal::String(flags))),
4539                    ],
4540                ))))
4541            }
4542
4543            // ROUND with named args (EXPR =>, SCALE =>, ROUNDING_MODE =>)
4544            "ROUND"
4545                if f.args
4546                    .iter()
4547                    .any(|a| matches!(a, Expression::NamedArgument(_))) =>
4548            {
4549                let mut expr_val = None;
4550                let mut scale_val = None;
4551                let mut rounding_mode = None;
4552                for arg in &f.args {
4553                    if let Expression::NamedArgument(na) = arg {
4554                        match na.name.name.to_uppercase().as_str() {
4555                            "EXPR" => expr_val = Some(na.value.clone()),
4556                            "SCALE" => scale_val = Some(na.value.clone()),
4557                            "ROUNDING_MODE" => rounding_mode = Some(na.value.clone()),
4558                            _ => {}
4559                        }
4560                    }
4561                }
4562                if let Some(expr) = expr_val {
4563                    let scale = scale_val.unwrap_or(Expression::number(0));
4564                    let is_half_to_even = match &rounding_mode {
4565                        Some(Expression::Literal(lit))
4566                            if matches!(lit.as_ref(), Literal::String(_)) =>
4567                        {
4568                            let Literal::String(s) = lit.as_ref() else {
4569                                unreachable!()
4570                            };
4571                            s == "HALF_TO_EVEN"
4572                        }
4573                        _ => false,
4574                    };
4575                    if is_half_to_even {
4576                        Ok(Expression::Function(Box::new(Function::new(
4577                            "ROUND_EVEN".to_string(),
4578                            vec![expr, scale],
4579                        ))))
4580                    } else {
4581                        Ok(Expression::Function(Box::new(Function::new(
4582                            "ROUND".to_string(),
4583                            vec![expr, scale],
4584                        ))))
4585                    }
4586                } else {
4587                    Ok(Expression::Function(Box::new(f)))
4588                }
4589            }
4590
4591            // ROUND(x, scale, 'HALF_TO_EVEN') -> ROUND_EVEN(x, scale)
4592            // ROUND(x, scale, 'HALF_AWAY_FROM_ZERO') -> ROUND(x, scale)
4593            "ROUND" if f.args.len() == 3 => {
4594                let mut args = f.args;
4595                let x = args.remove(0);
4596                let scale = args.remove(0);
4597                let mode = args.remove(0);
4598                let is_half_to_even = match &mode {
4599                    Expression::Literal(lit) if matches!(lit.as_ref(), Literal::String(_)) => {
4600                        let Literal::String(s) = lit.as_ref() else {
4601                            unreachable!()
4602                        };
4603                        s == "HALF_TO_EVEN"
4604                    }
4605                    _ => false,
4606                };
4607                if is_half_to_even {
4608                    Ok(Expression::Function(Box::new(Function::new(
4609                        "ROUND_EVEN".to_string(),
4610                        vec![x, scale],
4611                    ))))
4612                } else {
4613                    // HALF_AWAY_FROM_ZERO is default in DuckDB, just drop the mode
4614                    Ok(Expression::Function(Box::new(Function::new(
4615                        "ROUND".to_string(),
4616                        vec![x, scale],
4617                    ))))
4618                }
4619            }
4620
4621            // ROUND(x, scale) where scale is non-integer -> ROUND(x, CAST(scale AS INT))
4622            "ROUND" if f.args.len() == 2 => {
4623                let mut args = f.args;
4624                let x = args.remove(0);
4625                let scale = args.remove(0);
4626                let needs_cast = match &scale {
4627                    Expression::Literal(lit) if matches!(lit.as_ref(), Literal::Number(_)) => {
4628                        let Literal::Number(n) = lit.as_ref() else {
4629                            unreachable!()
4630                        };
4631                        n.contains('.')
4632                    }
4633                    Expression::Cast(_) => {
4634                        // Already has a CAST - wrap in another CAST to INT
4635                        true
4636                    }
4637                    _ => false,
4638                };
4639                if needs_cast {
4640                    Ok(Expression::Function(Box::new(Function::new(
4641                        "ROUND".to_string(),
4642                        vec![
4643                            x,
4644                            Expression::Cast(Box::new(Cast {
4645                                this: scale,
4646                                to: DataType::Int {
4647                                    length: None,
4648                                    integer_spelling: false,
4649                                },
4650                                trailing_comments: Vec::new(),
4651                                double_colon_syntax: false,
4652                                format: None,
4653                                default: None,
4654                                inferred_type: None,
4655                            })),
4656                        ],
4657                    ))))
4658                } else {
4659                    Ok(Expression::Function(Box::new(Function::new(
4660                        "ROUND".to_string(),
4661                        vec![x, scale],
4662                    ))))
4663                }
4664            }
4665
4666            // FLOOR(x, scale) -> ROUND(FLOOR(x * POWER(10, scale)) / POWER(10, scale), scale)
4667            "FLOOR" if f.args.len() == 2 => {
4668                let mut args = f.args;
4669                let x = args.remove(0);
4670                let scale = args.remove(0);
4671                // Check if scale needs CAST to INT
4672                let needs_cast = match &scale {
4673                    Expression::Literal(lit) if matches!(lit.as_ref(), Literal::Number(_)) => {
4674                        let Literal::Number(n) = lit.as_ref() else {
4675                            unreachable!()
4676                        };
4677                        n.contains('.')
4678                    }
4679                    _ => false,
4680                };
4681                let int_scale = if needs_cast {
4682                    Expression::Cast(Box::new(Cast {
4683                        this: scale.clone(),
4684                        to: DataType::Int {
4685                            length: None,
4686                            integer_spelling: false,
4687                        },
4688                        trailing_comments: Vec::new(),
4689                        double_colon_syntax: false,
4690                        format: None,
4691                        default: None,
4692                        inferred_type: None,
4693                    }))
4694                } else {
4695                    scale.clone()
4696                };
4697                let power_10 = Expression::Function(Box::new(Function::new(
4698                    "POWER".to_string(),
4699                    vec![Expression::number(10), int_scale.clone()],
4700                )));
4701                let x_paren = match &x {
4702                    Expression::Add(_)
4703                    | Expression::Sub(_)
4704                    | Expression::Mul(_)
4705                    | Expression::Div(_) => Expression::Paren(Box::new(Paren {
4706                        this: x,
4707                        trailing_comments: Vec::new(),
4708                    })),
4709                    _ => x,
4710                };
4711                let multiplied = Expression::Mul(Box::new(BinaryOp {
4712                    left: x_paren,
4713                    right: power_10.clone(),
4714                    left_comments: Vec::new(),
4715                    operator_comments: Vec::new(),
4716                    trailing_comments: Vec::new(),
4717                    inferred_type: None,
4718                }));
4719                let floored = Expression::Function(Box::new(Function::new(
4720                    "FLOOR".to_string(),
4721                    vec![multiplied],
4722                )));
4723                let divided = Expression::Div(Box::new(BinaryOp {
4724                    left: floored,
4725                    right: power_10,
4726                    left_comments: Vec::new(),
4727                    operator_comments: Vec::new(),
4728                    trailing_comments: Vec::new(),
4729                    inferred_type: None,
4730                }));
4731                Ok(Expression::Function(Box::new(Function::new(
4732                    "ROUND".to_string(),
4733                    vec![divided, int_scale],
4734                ))))
4735            }
4736
4737            // CEIL(x, scale) -> ROUND(CEIL(x * POWER(10, scale)) / POWER(10, scale), scale)
4738            "CEIL" | "CEILING" if f.args.len() == 2 => {
4739                let mut args = f.args;
4740                let x = args.remove(0);
4741                let scale = args.remove(0);
4742                let needs_cast = match &scale {
4743                    Expression::Literal(lit) if matches!(lit.as_ref(), Literal::Number(_)) => {
4744                        let Literal::Number(n) = lit.as_ref() else {
4745                            unreachable!()
4746                        };
4747                        n.contains('.')
4748                    }
4749                    _ => false,
4750                };
4751                let int_scale = if needs_cast {
4752                    Expression::Cast(Box::new(Cast {
4753                        this: scale.clone(),
4754                        to: DataType::Int {
4755                            length: None,
4756                            integer_spelling: false,
4757                        },
4758                        trailing_comments: Vec::new(),
4759                        double_colon_syntax: false,
4760                        format: None,
4761                        default: None,
4762                        inferred_type: None,
4763                    }))
4764                } else {
4765                    scale.clone()
4766                };
4767                let power_10 = Expression::Function(Box::new(Function::new(
4768                    "POWER".to_string(),
4769                    vec![Expression::number(10), int_scale.clone()],
4770                )));
4771                let x_paren = match &x {
4772                    Expression::Add(_)
4773                    | Expression::Sub(_)
4774                    | Expression::Mul(_)
4775                    | Expression::Div(_) => Expression::Paren(Box::new(Paren {
4776                        this: x,
4777                        trailing_comments: Vec::new(),
4778                    })),
4779                    _ => x,
4780                };
4781                let multiplied = Expression::Mul(Box::new(BinaryOp {
4782                    left: x_paren,
4783                    right: power_10.clone(),
4784                    left_comments: Vec::new(),
4785                    operator_comments: Vec::new(),
4786                    trailing_comments: Vec::new(),
4787                    inferred_type: None,
4788                }));
4789                let ceiled = Expression::Function(Box::new(Function::new(
4790                    "CEIL".to_string(),
4791                    vec![multiplied],
4792                )));
4793                let divided = Expression::Div(Box::new(BinaryOp {
4794                    left: ceiled,
4795                    right: power_10,
4796                    left_comments: Vec::new(),
4797                    operator_comments: Vec::new(),
4798                    trailing_comments: Vec::new(),
4799                    inferred_type: None,
4800                }));
4801                Ok(Expression::Function(Box::new(Function::new(
4802                    "ROUND".to_string(),
4803                    vec![divided, int_scale],
4804                ))))
4805            }
4806
4807            // ADD_MONTHS(date, n) -> CASE WHEN LAST_DAY(date) = date THEN LAST_DAY(date + INTERVAL n MONTH) ELSE date + INTERVAL n MONTH END
4808            "ADD_MONTHS" if f.args.len() == 2 => {
4809                let mut args = f.args;
4810                let date_expr_raw = args.remove(0);
4811                let months_expr = args.remove(0);
4812
4813                // Track whether the raw expression was a string literal
4814                let was_string_literal = matches!(&date_expr_raw, Expression::Literal(lit) if matches!(lit.as_ref(), Literal::String(_)));
4815
4816                // Wrap string literals in CAST(... AS TIMESTAMP) for DuckDB
4817                let date_expr = match &date_expr_raw {
4818                    Expression::Literal(lit) if matches!(lit.as_ref(), Literal::String(_)) => {
4819                        Expression::Cast(Box::new(Cast {
4820                            this: date_expr_raw,
4821                            to: DataType::Timestamp {
4822                                precision: None,
4823                                timezone: false,
4824                            },
4825                            trailing_comments: Vec::new(),
4826                            double_colon_syntax: false,
4827                            format: None,
4828                            default: None,
4829                            inferred_type: None,
4830                        }))
4831                    }
4832                    _ => date_expr_raw,
4833                };
4834
4835                // Determine the type of the date expression for outer CAST
4836                // But NOT if the CAST was added by us (for string literal wrapping)
4837                let date_type = if was_string_literal {
4838                    None
4839                } else {
4840                    match &date_expr {
4841                        Expression::Cast(c) => Some(c.to.clone()),
4842                        _ => None,
4843                    }
4844                };
4845
4846                // Determine interval expression - for non-integer months, use TO_MONTHS(CAST(ROUND(n) AS INT))
4847                let is_non_integer_months = match &months_expr {
4848                    Expression::Literal(lit) if matches!(lit.as_ref(), Literal::Number(_)) => {
4849                        let Literal::Number(n) = lit.as_ref() else {
4850                            unreachable!()
4851                        };
4852                        n.contains('.')
4853                    }
4854                    Expression::Neg(_) => {
4855                        if let Expression::Neg(um) = &months_expr {
4856                            matches!(&um.this, Expression::Literal(lit) if matches!(lit.as_ref(), Literal::Number(n) if n.contains('.')))
4857                        } else {
4858                            false
4859                        }
4860                    }
4861                    // Cast to DECIMAL type means non-integer months
4862                    Expression::Cast(c) => matches!(&c.to, DataType::Decimal { .. }),
4863                    _ => false,
4864                };
4865
4866                let is_negative = match &months_expr {
4867                    Expression::Neg(_) => true,
4868                    Expression::Literal(lit) if matches!(lit.as_ref(), Literal::Number(_)) => {
4869                        let Literal::Number(n) = lit.as_ref() else {
4870                            unreachable!()
4871                        };
4872                        n.starts_with('-')
4873                    }
4874                    _ => false,
4875                };
4876                let is_null = matches!(&months_expr, Expression::Null(_));
4877
4878                let interval_expr = if is_non_integer_months {
4879                    // For non-integer: TO_MONTHS(CAST(ROUND(n) AS INT))
4880                    Expression::Function(Box::new(Function::new(
4881                        "TO_MONTHS".to_string(),
4882                        vec![Expression::Cast(Box::new(Cast {
4883                            this: Expression::Function(Box::new(Function::new(
4884                                "ROUND".to_string(),
4885                                vec![months_expr.clone()],
4886                            ))),
4887                            to: DataType::Int {
4888                                length: None,
4889                                integer_spelling: false,
4890                            },
4891                            trailing_comments: Vec::new(),
4892                            double_colon_syntax: false,
4893                            format: None,
4894                            default: None,
4895                            inferred_type: None,
4896                        }))],
4897                    )))
4898                } else if is_negative || is_null {
4899                    // For negative or NULL: INTERVAL (n) MONTH
4900                    Expression::Interval(Box::new(Interval {
4901                        this: Some(Expression::Paren(Box::new(Paren {
4902                            this: months_expr.clone(),
4903                            trailing_comments: Vec::new(),
4904                        }))),
4905                        unit: Some(IntervalUnitSpec::Simple {
4906                            unit: IntervalUnit::Month,
4907                            use_plural: false,
4908                        }),
4909                    }))
4910                } else {
4911                    // For positive integer: INTERVAL n MONTH
4912                    Expression::Interval(Box::new(Interval {
4913                        this: Some(months_expr.clone()),
4914                        unit: Some(IntervalUnitSpec::Simple {
4915                            unit: IntervalUnit::Month,
4916                            use_plural: false,
4917                        }),
4918                    }))
4919                };
4920
4921                let date_plus_interval = Expression::Add(Box::new(BinaryOp {
4922                    left: date_expr.clone(),
4923                    right: interval_expr.clone(),
4924                    left_comments: Vec::new(),
4925                    operator_comments: Vec::new(),
4926                    trailing_comments: Vec::new(),
4927                    inferred_type: None,
4928                }));
4929
4930                let case_expr = Expression::Case(Box::new(Case {
4931                    operand: None,
4932                    whens: vec![(
4933                        Expression::Eq(Box::new(BinaryOp {
4934                            left: Expression::Function(Box::new(Function::new(
4935                                "LAST_DAY".to_string(),
4936                                vec![date_expr.clone()],
4937                            ))),
4938                            right: date_expr.clone(),
4939                            left_comments: Vec::new(),
4940                            operator_comments: Vec::new(),
4941                            trailing_comments: Vec::new(),
4942                            inferred_type: None,
4943                        })),
4944                        Expression::Function(Box::new(Function::new(
4945                            "LAST_DAY".to_string(),
4946                            vec![date_plus_interval.clone()],
4947                        ))),
4948                    )],
4949                    else_: Some(date_plus_interval),
4950                    comments: Vec::new(),
4951                    inferred_type: None,
4952                }));
4953
4954                // Wrap in CAST if date had explicit type
4955                if let Some(dt) = date_type {
4956                    Ok(Expression::Cast(Box::new(Cast {
4957                        this: case_expr,
4958                        to: dt,
4959                        trailing_comments: Vec::new(),
4960                        double_colon_syntax: false,
4961                        format: None,
4962                        default: None,
4963                        inferred_type: None,
4964                    })))
4965                } else {
4966                    Ok(case_expr)
4967                }
4968            }
4969
4970            // TIME_SLICE(date, n, 'UNIT') -> TIME_BUCKET(INTERVAL n UNIT, date)
4971            // TIME_SLICE(date, n, 'UNIT', 'END') -> TIME_BUCKET(INTERVAL n UNIT, date) + INTERVAL n UNIT
4972            "TIME_SLICE" if f.args.len() >= 3 => {
4973                let mut args = f.args;
4974                let date_expr = args.remove(0);
4975                let n = args.remove(0);
4976                let unit_str = args.remove(0);
4977                let alignment = if !args.is_empty() {
4978                    Some(args.remove(0))
4979                } else {
4980                    None
4981                };
4982
4983                // Extract unit string
4984                let unit = match &unit_str {
4985                    Expression::Literal(lit) if matches!(lit.as_ref(), Literal::String(_)) => {
4986                        let Literal::String(s) = lit.as_ref() else {
4987                            unreachable!()
4988                        };
4989                        s.to_uppercase()
4990                    }
4991                    Expression::Column(c) => c.name.name.to_uppercase(),
4992                    Expression::Identifier(i) => i.name.to_uppercase(),
4993                    _ => "DAY".to_string(),
4994                };
4995
4996                let interval_unit = match unit.as_str() {
4997                    "YEAR" => IntervalUnit::Year,
4998                    "QUARTER" => IntervalUnit::Quarter,
4999                    "MONTH" => IntervalUnit::Month,
5000                    "WEEK" => IntervalUnit::Week,
5001                    "DAY" => IntervalUnit::Day,
5002                    "HOUR" => IntervalUnit::Hour,
5003                    "MINUTE" => IntervalUnit::Minute,
5004                    "SECOND" => IntervalUnit::Second,
5005                    _ => IntervalUnit::Day,
5006                };
5007
5008                let interval = Expression::Interval(Box::new(Interval {
5009                    this: Some(n.clone()),
5010                    unit: Some(IntervalUnitSpec::Simple {
5011                        unit: interval_unit.clone(),
5012                        use_plural: false,
5013                    }),
5014                }));
5015
5016                let time_bucket = Expression::Function(Box::new(Function::new(
5017                    "TIME_BUCKET".to_string(),
5018                    vec![interval.clone(), date_expr.clone()],
5019                )));
5020
5021                let is_end = match &alignment {
5022                    Some(Expression::Literal(lit))
5023                        if matches!(lit.as_ref(), Literal::String(_)) =>
5024                    {
5025                        let Literal::String(s) = lit.as_ref() else {
5026                            unreachable!()
5027                        };
5028                        s.to_uppercase() == "END"
5029                    }
5030                    _ => false,
5031                };
5032
5033                // Determine if date is a DATE type (needs CAST)
5034                let is_date_type = match &date_expr {
5035                    Expression::Cast(c) => matches!(&c.to, DataType::Date),
5036                    _ => false,
5037                };
5038
5039                if is_end {
5040                    let bucket_plus = Expression::Add(Box::new(BinaryOp {
5041                        left: time_bucket,
5042                        right: Expression::Interval(Box::new(Interval {
5043                            this: Some(n),
5044                            unit: Some(IntervalUnitSpec::Simple {
5045                                unit: interval_unit,
5046                                use_plural: false,
5047                            }),
5048                        })),
5049                        left_comments: Vec::new(),
5050                        operator_comments: Vec::new(),
5051                        trailing_comments: Vec::new(),
5052                        inferred_type: None,
5053                    }));
5054                    if is_date_type {
5055                        Ok(Expression::Cast(Box::new(Cast {
5056                            this: bucket_plus,
5057                            to: DataType::Date,
5058                            trailing_comments: Vec::new(),
5059                            double_colon_syntax: false,
5060                            format: None,
5061                            default: None,
5062                            inferred_type: None,
5063                        })))
5064                    } else {
5065                        Ok(bucket_plus)
5066                    }
5067                } else {
5068                    Ok(time_bucket)
5069                }
5070            }
5071
5072            // DATE_FROM_PARTS(year, month, day) -> CAST(MAKE_DATE(year, 1, 1) + INTERVAL (month - 1) MONTH + INTERVAL (day - 1) DAY AS DATE)
5073            "DATE_FROM_PARTS" | "DATEFROMPARTS" if f.args.len() == 3 => {
5074                let mut args = f.args;
5075                let year = args.remove(0);
5076                let month = args.remove(0);
5077                let day = args.remove(0);
5078
5079                let make_date = Expression::Function(Box::new(Function::new(
5080                    "MAKE_DATE".to_string(),
5081                    vec![year, Expression::number(1), Expression::number(1)],
5082                )));
5083
5084                // Wrap compound expressions in parens to get ((expr) - 1) instead of (expr - 1)
5085                let month_wrapped = match &month {
5086                    Expression::Add(_)
5087                    | Expression::Sub(_)
5088                    | Expression::Mul(_)
5089                    | Expression::Div(_) => Expression::Paren(Box::new(Paren {
5090                        this: month,
5091                        trailing_comments: Vec::new(),
5092                    })),
5093                    _ => month,
5094                };
5095                let day_wrapped = match &day {
5096                    Expression::Add(_)
5097                    | Expression::Sub(_)
5098                    | Expression::Mul(_)
5099                    | Expression::Div(_) => Expression::Paren(Box::new(Paren {
5100                        this: day,
5101                        trailing_comments: Vec::new(),
5102                    })),
5103                    _ => day,
5104                };
5105                let month_minus_1 = Expression::Sub(Box::new(BinaryOp {
5106                    left: month_wrapped,
5107                    right: Expression::number(1),
5108                    left_comments: Vec::new(),
5109                    operator_comments: Vec::new(),
5110                    trailing_comments: Vec::new(),
5111                    inferred_type: None,
5112                }));
5113                let month_interval = Expression::Interval(Box::new(Interval {
5114                    this: Some(Expression::Paren(Box::new(Paren {
5115                        this: month_minus_1,
5116                        trailing_comments: Vec::new(),
5117                    }))),
5118                    unit: Some(IntervalUnitSpec::Simple {
5119                        unit: IntervalUnit::Month,
5120                        use_plural: false,
5121                    }),
5122                }));
5123
5124                let day_minus_1 = Expression::Sub(Box::new(BinaryOp {
5125                    left: day_wrapped,
5126                    right: Expression::number(1),
5127                    left_comments: Vec::new(),
5128                    operator_comments: Vec::new(),
5129                    trailing_comments: Vec::new(),
5130                    inferred_type: None,
5131                }));
5132                let day_interval = Expression::Interval(Box::new(Interval {
5133                    this: Some(Expression::Paren(Box::new(Paren {
5134                        this: day_minus_1,
5135                        trailing_comments: Vec::new(),
5136                    }))),
5137                    unit: Some(IntervalUnitSpec::Simple {
5138                        unit: IntervalUnit::Day,
5139                        use_plural: false,
5140                    }),
5141                }));
5142
5143                let result = Expression::Add(Box::new(BinaryOp {
5144                    left: Expression::Add(Box::new(BinaryOp {
5145                        left: make_date,
5146                        right: month_interval,
5147                        left_comments: Vec::new(),
5148                        operator_comments: Vec::new(),
5149                        trailing_comments: Vec::new(),
5150                        inferred_type: None,
5151                    })),
5152                    right: day_interval,
5153                    left_comments: Vec::new(),
5154                    operator_comments: Vec::new(),
5155                    trailing_comments: Vec::new(),
5156                    inferred_type: None,
5157                }));
5158
5159                Ok(Expression::Cast(Box::new(Cast {
5160                    this: result,
5161                    to: DataType::Date,
5162                    trailing_comments: Vec::new(),
5163                    double_colon_syntax: false,
5164                    format: None,
5165                    default: None,
5166                    inferred_type: None,
5167                })))
5168            }
5169
5170            // NEXT_DAY(date, 'day_name') -> complex expression using ISODOW
5171            "NEXT_DAY" if f.args.len() == 2 => {
5172                let mut args = f.args;
5173                let date = args.remove(0);
5174                let day_name = args.remove(0);
5175
5176                // Parse day name to ISO day number (1=Monday..7=Sunday)
5177                let day_num = match &day_name {
5178                    Expression::Literal(lit) if matches!(lit.as_ref(), Literal::String(_)) => {
5179                        let Literal::String(s) = lit.as_ref() else {
5180                            unreachable!()
5181                        };
5182                        let upper = s.to_uppercase();
5183                        if upper.starts_with("MO") {
5184                            Some(1)
5185                        } else if upper.starts_with("TU") {
5186                            Some(2)
5187                        } else if upper.starts_with("WE") {
5188                            Some(3)
5189                        } else if upper.starts_with("TH") {
5190                            Some(4)
5191                        } else if upper.starts_with("FR") {
5192                            Some(5)
5193                        } else if upper.starts_with("SA") {
5194                            Some(6)
5195                        } else if upper.starts_with("SU") {
5196                            Some(7)
5197                        } else {
5198                            None
5199                        }
5200                    }
5201                    _ => None,
5202                };
5203
5204                let target_day_expr = if let Some(n) = day_num {
5205                    Expression::number(n)
5206                } else {
5207                    // Dynamic day name: CASE WHEN STARTS_WITH(UPPER(day_column), 'MO') THEN 1 ... END
5208                    Expression::Case(Box::new(Case {
5209                        operand: None,
5210                        whens: vec![
5211                            (
5212                                Expression::Function(Box::new(Function::new(
5213                                    "STARTS_WITH".to_string(),
5214                                    vec![
5215                                        Expression::Upper(Box::new(UnaryFunc::new(
5216                                            day_name.clone(),
5217                                        ))),
5218                                        Expression::Literal(Box::new(Literal::String(
5219                                            "MO".to_string(),
5220                                        ))),
5221                                    ],
5222                                ))),
5223                                Expression::number(1),
5224                            ),
5225                            (
5226                                Expression::Function(Box::new(Function::new(
5227                                    "STARTS_WITH".to_string(),
5228                                    vec![
5229                                        Expression::Upper(Box::new(UnaryFunc::new(
5230                                            day_name.clone(),
5231                                        ))),
5232                                        Expression::Literal(Box::new(Literal::String(
5233                                            "TU".to_string(),
5234                                        ))),
5235                                    ],
5236                                ))),
5237                                Expression::number(2),
5238                            ),
5239                            (
5240                                Expression::Function(Box::new(Function::new(
5241                                    "STARTS_WITH".to_string(),
5242                                    vec![
5243                                        Expression::Upper(Box::new(UnaryFunc::new(
5244                                            day_name.clone(),
5245                                        ))),
5246                                        Expression::Literal(Box::new(Literal::String(
5247                                            "WE".to_string(),
5248                                        ))),
5249                                    ],
5250                                ))),
5251                                Expression::number(3),
5252                            ),
5253                            (
5254                                Expression::Function(Box::new(Function::new(
5255                                    "STARTS_WITH".to_string(),
5256                                    vec![
5257                                        Expression::Upper(Box::new(UnaryFunc::new(
5258                                            day_name.clone(),
5259                                        ))),
5260                                        Expression::Literal(Box::new(Literal::String(
5261                                            "TH".to_string(),
5262                                        ))),
5263                                    ],
5264                                ))),
5265                                Expression::number(4),
5266                            ),
5267                            (
5268                                Expression::Function(Box::new(Function::new(
5269                                    "STARTS_WITH".to_string(),
5270                                    vec![
5271                                        Expression::Upper(Box::new(UnaryFunc::new(
5272                                            day_name.clone(),
5273                                        ))),
5274                                        Expression::Literal(Box::new(Literal::String(
5275                                            "FR".to_string(),
5276                                        ))),
5277                                    ],
5278                                ))),
5279                                Expression::number(5),
5280                            ),
5281                            (
5282                                Expression::Function(Box::new(Function::new(
5283                                    "STARTS_WITH".to_string(),
5284                                    vec![
5285                                        Expression::Upper(Box::new(UnaryFunc::new(
5286                                            day_name.clone(),
5287                                        ))),
5288                                        Expression::Literal(Box::new(Literal::String(
5289                                            "SA".to_string(),
5290                                        ))),
5291                                    ],
5292                                ))),
5293                                Expression::number(6),
5294                            ),
5295                            (
5296                                Expression::Function(Box::new(Function::new(
5297                                    "STARTS_WITH".to_string(),
5298                                    vec![
5299                                        Expression::Upper(Box::new(UnaryFunc::new(day_name))),
5300                                        Expression::Literal(Box::new(Literal::String(
5301                                            "SU".to_string(),
5302                                        ))),
5303                                    ],
5304                                ))),
5305                                Expression::number(7),
5306                            ),
5307                        ],
5308                        else_: None,
5309                        comments: Vec::new(),
5310                        inferred_type: None,
5311                    }))
5312                };
5313
5314                let isodow = Expression::Function(Box::new(Function::new(
5315                    "ISODOW".to_string(),
5316                    vec![date.clone()],
5317                )));
5318                // ((target_day - ISODOW(date) + 6) % 7) + 1
5319                let diff = Expression::Add(Box::new(BinaryOp {
5320                    left: Expression::Paren(Box::new(Paren {
5321                        this: Expression::Mod(Box::new(BinaryOp {
5322                            left: Expression::Paren(Box::new(Paren {
5323                                this: Expression::Add(Box::new(BinaryOp {
5324                                    left: Expression::Paren(Box::new(Paren {
5325                                        this: Expression::Sub(Box::new(BinaryOp {
5326                                            left: target_day_expr,
5327                                            right: isodow,
5328                                            left_comments: Vec::new(),
5329                                            operator_comments: Vec::new(),
5330                                            trailing_comments: Vec::new(),
5331                                            inferred_type: None,
5332                                        })),
5333                                        trailing_comments: Vec::new(),
5334                                    })),
5335                                    right: Expression::number(6),
5336                                    left_comments: Vec::new(),
5337                                    operator_comments: Vec::new(),
5338                                    trailing_comments: Vec::new(),
5339                                    inferred_type: None,
5340                                })),
5341                                trailing_comments: Vec::new(),
5342                            })),
5343                            right: Expression::number(7),
5344                            left_comments: Vec::new(),
5345                            operator_comments: Vec::new(),
5346                            trailing_comments: Vec::new(),
5347                            inferred_type: None,
5348                        })),
5349                        trailing_comments: Vec::new(),
5350                    })),
5351                    right: Expression::number(1),
5352                    left_comments: Vec::new(),
5353                    operator_comments: Vec::new(),
5354                    trailing_comments: Vec::new(),
5355                    inferred_type: None,
5356                }));
5357
5358                let result = Expression::Add(Box::new(BinaryOp {
5359                    left: date,
5360                    right: Expression::Interval(Box::new(Interval {
5361                        this: Some(Expression::Paren(Box::new(Paren {
5362                            this: diff,
5363                            trailing_comments: Vec::new(),
5364                        }))),
5365                        unit: Some(IntervalUnitSpec::Simple {
5366                            unit: IntervalUnit::Day,
5367                            use_plural: false,
5368                        }),
5369                    })),
5370                    left_comments: Vec::new(),
5371                    operator_comments: Vec::new(),
5372                    trailing_comments: Vec::new(),
5373                    inferred_type: None,
5374                }));
5375
5376                Ok(Expression::Cast(Box::new(Cast {
5377                    this: result,
5378                    to: DataType::Date,
5379                    trailing_comments: Vec::new(),
5380                    double_colon_syntax: false,
5381                    format: None,
5382                    default: None,
5383                    inferred_type: None,
5384                })))
5385            }
5386
5387            // PREVIOUS_DAY(date, 'day_name') -> complex expression using ISODOW
5388            "PREVIOUS_DAY" if f.args.len() == 2 => {
5389                let mut args = f.args;
5390                let date = args.remove(0);
5391                let day_name = args.remove(0);
5392
5393                let day_num = match &day_name {
5394                    Expression::Literal(lit) if matches!(lit.as_ref(), Literal::String(_)) => {
5395                        let Literal::String(s) = lit.as_ref() else {
5396                            unreachable!()
5397                        };
5398                        let upper = s.to_uppercase();
5399                        if upper.starts_with("MO") {
5400                            Some(1)
5401                        } else if upper.starts_with("TU") {
5402                            Some(2)
5403                        } else if upper.starts_with("WE") {
5404                            Some(3)
5405                        } else if upper.starts_with("TH") {
5406                            Some(4)
5407                        } else if upper.starts_with("FR") {
5408                            Some(5)
5409                        } else if upper.starts_with("SA") {
5410                            Some(6)
5411                        } else if upper.starts_with("SU") {
5412                            Some(7)
5413                        } else {
5414                            None
5415                        }
5416                    }
5417                    _ => None,
5418                };
5419
5420                let target_day_expr = if let Some(n) = day_num {
5421                    Expression::number(n)
5422                } else {
5423                    Expression::Case(Box::new(Case {
5424                        operand: None,
5425                        whens: vec![
5426                            (
5427                                Expression::Function(Box::new(Function::new(
5428                                    "STARTS_WITH".to_string(),
5429                                    vec![
5430                                        Expression::Upper(Box::new(UnaryFunc::new(
5431                                            day_name.clone(),
5432                                        ))),
5433                                        Expression::Literal(Box::new(Literal::String(
5434                                            "MO".to_string(),
5435                                        ))),
5436                                    ],
5437                                ))),
5438                                Expression::number(1),
5439                            ),
5440                            (
5441                                Expression::Function(Box::new(Function::new(
5442                                    "STARTS_WITH".to_string(),
5443                                    vec![
5444                                        Expression::Upper(Box::new(UnaryFunc::new(
5445                                            day_name.clone(),
5446                                        ))),
5447                                        Expression::Literal(Box::new(Literal::String(
5448                                            "TU".to_string(),
5449                                        ))),
5450                                    ],
5451                                ))),
5452                                Expression::number(2),
5453                            ),
5454                            (
5455                                Expression::Function(Box::new(Function::new(
5456                                    "STARTS_WITH".to_string(),
5457                                    vec![
5458                                        Expression::Upper(Box::new(UnaryFunc::new(
5459                                            day_name.clone(),
5460                                        ))),
5461                                        Expression::Literal(Box::new(Literal::String(
5462                                            "WE".to_string(),
5463                                        ))),
5464                                    ],
5465                                ))),
5466                                Expression::number(3),
5467                            ),
5468                            (
5469                                Expression::Function(Box::new(Function::new(
5470                                    "STARTS_WITH".to_string(),
5471                                    vec![
5472                                        Expression::Upper(Box::new(UnaryFunc::new(
5473                                            day_name.clone(),
5474                                        ))),
5475                                        Expression::Literal(Box::new(Literal::String(
5476                                            "TH".to_string(),
5477                                        ))),
5478                                    ],
5479                                ))),
5480                                Expression::number(4),
5481                            ),
5482                            (
5483                                Expression::Function(Box::new(Function::new(
5484                                    "STARTS_WITH".to_string(),
5485                                    vec![
5486                                        Expression::Upper(Box::new(UnaryFunc::new(
5487                                            day_name.clone(),
5488                                        ))),
5489                                        Expression::Literal(Box::new(Literal::String(
5490                                            "FR".to_string(),
5491                                        ))),
5492                                    ],
5493                                ))),
5494                                Expression::number(5),
5495                            ),
5496                            (
5497                                Expression::Function(Box::new(Function::new(
5498                                    "STARTS_WITH".to_string(),
5499                                    vec![
5500                                        Expression::Upper(Box::new(UnaryFunc::new(
5501                                            day_name.clone(),
5502                                        ))),
5503                                        Expression::Literal(Box::new(Literal::String(
5504                                            "SA".to_string(),
5505                                        ))),
5506                                    ],
5507                                ))),
5508                                Expression::number(6),
5509                            ),
5510                            (
5511                                Expression::Function(Box::new(Function::new(
5512                                    "STARTS_WITH".to_string(),
5513                                    vec![
5514                                        Expression::Upper(Box::new(UnaryFunc::new(day_name))),
5515                                        Expression::Literal(Box::new(Literal::String(
5516                                            "SU".to_string(),
5517                                        ))),
5518                                    ],
5519                                ))),
5520                                Expression::number(7),
5521                            ),
5522                        ],
5523                        else_: None,
5524                        comments: Vec::new(),
5525                        inferred_type: None,
5526                    }))
5527                };
5528
5529                let isodow = Expression::Function(Box::new(Function::new(
5530                    "ISODOW".to_string(),
5531                    vec![date.clone()],
5532                )));
5533                // ((ISODOW(date) - target_day + 6) % 7) + 1
5534                let diff = Expression::Add(Box::new(BinaryOp {
5535                    left: Expression::Paren(Box::new(Paren {
5536                        this: Expression::Mod(Box::new(BinaryOp {
5537                            left: Expression::Paren(Box::new(Paren {
5538                                this: Expression::Add(Box::new(BinaryOp {
5539                                    left: Expression::Paren(Box::new(Paren {
5540                                        this: Expression::Sub(Box::new(BinaryOp {
5541                                            left: isodow,
5542                                            right: target_day_expr,
5543                                            left_comments: Vec::new(),
5544                                            operator_comments: Vec::new(),
5545                                            trailing_comments: Vec::new(),
5546                                            inferred_type: None,
5547                                        })),
5548                                        trailing_comments: Vec::new(),
5549                                    })),
5550                                    right: Expression::number(6),
5551                                    left_comments: Vec::new(),
5552                                    operator_comments: Vec::new(),
5553                                    trailing_comments: Vec::new(),
5554                                    inferred_type: None,
5555                                })),
5556                                trailing_comments: Vec::new(),
5557                            })),
5558                            right: Expression::number(7),
5559                            left_comments: Vec::new(),
5560                            operator_comments: Vec::new(),
5561                            trailing_comments: Vec::new(),
5562                            inferred_type: None,
5563                        })),
5564                        trailing_comments: Vec::new(),
5565                    })),
5566                    right: Expression::number(1),
5567                    left_comments: Vec::new(),
5568                    operator_comments: Vec::new(),
5569                    trailing_comments: Vec::new(),
5570                    inferred_type: None,
5571                }));
5572
5573                let result = Expression::Sub(Box::new(BinaryOp {
5574                    left: date,
5575                    right: Expression::Interval(Box::new(Interval {
5576                        this: Some(Expression::Paren(Box::new(Paren {
5577                            this: diff,
5578                            trailing_comments: Vec::new(),
5579                        }))),
5580                        unit: Some(IntervalUnitSpec::Simple {
5581                            unit: IntervalUnit::Day,
5582                            use_plural: false,
5583                        }),
5584                    })),
5585                    left_comments: Vec::new(),
5586                    operator_comments: Vec::new(),
5587                    trailing_comments: Vec::new(),
5588                    inferred_type: None,
5589                }));
5590
5591                Ok(Expression::Cast(Box::new(Cast {
5592                    this: result,
5593                    to: DataType::Date,
5594                    trailing_comments: Vec::new(),
5595                    double_colon_syntax: false,
5596                    format: None,
5597                    default: None,
5598                    inferred_type: None,
5599                })))
5600            }
5601
5602            // LAST_DAY(date, YEAR) -> MAKE_DATE(EXTRACT(YEAR FROM date), 12, 31)
5603            // LAST_DAY(date, QUARTER) -> LAST_DAY(MAKE_DATE(EXTRACT(YEAR FROM date), EXTRACT(QUARTER FROM date) * 3, 1))
5604            // LAST_DAY(date, WEEK) -> CAST(date + INTERVAL ((7 - EXTRACT(DAYOFWEEK FROM date)) % 7) DAY AS DATE)
5605            "LAST_DAY" if f.args.len() == 2 => {
5606                let mut args = f.args;
5607                let date = args.remove(0);
5608                let unit = args.remove(0);
5609                let unit_str = match &unit {
5610                    Expression::Column(c) => c.name.name.to_uppercase(),
5611                    Expression::Identifier(i) => i.name.to_uppercase(),
5612                    _ => String::new(),
5613                };
5614
5615                match unit_str.as_str() {
5616                    "MONTH" => Ok(Expression::Function(Box::new(Function::new(
5617                        "LAST_DAY".to_string(),
5618                        vec![date],
5619                    )))),
5620                    "YEAR" => Ok(Expression::Function(Box::new(Function::new(
5621                        "MAKE_DATE".to_string(),
5622                        vec![
5623                            Expression::Extract(Box::new(crate::expressions::ExtractFunc {
5624                                this: date,
5625                                field: crate::expressions::DateTimeField::Year,
5626                            })),
5627                            Expression::number(12),
5628                            Expression::number(31),
5629                        ],
5630                    )))),
5631                    "QUARTER" => {
5632                        let year = Expression::Extract(Box::new(crate::expressions::ExtractFunc {
5633                            this: date.clone(),
5634                            field: crate::expressions::DateTimeField::Year,
5635                        }));
5636                        let quarter_month = Expression::Mul(Box::new(BinaryOp {
5637                            left: Expression::Extract(Box::new(crate::expressions::ExtractFunc {
5638                                this: date,
5639                                field: crate::expressions::DateTimeField::Custom(
5640                                    "QUARTER".to_string(),
5641                                ),
5642                            })),
5643                            right: Expression::number(3),
5644                            left_comments: Vec::new(),
5645                            operator_comments: Vec::new(),
5646                            trailing_comments: Vec::new(),
5647                            inferred_type: None,
5648                        }));
5649                        let make_date = Expression::Function(Box::new(Function::new(
5650                            "MAKE_DATE".to_string(),
5651                            vec![year, quarter_month, Expression::number(1)],
5652                        )));
5653                        Ok(Expression::Function(Box::new(Function::new(
5654                            "LAST_DAY".to_string(),
5655                            vec![make_date],
5656                        ))))
5657                    }
5658                    "WEEK" => {
5659                        let dow = Expression::Extract(Box::new(crate::expressions::ExtractFunc {
5660                            this: date.clone(),
5661                            field: crate::expressions::DateTimeField::Custom(
5662                                "DAYOFWEEK".to_string(),
5663                            ),
5664                        }));
5665                        let diff = Expression::Mod(Box::new(BinaryOp {
5666                            left: Expression::Paren(Box::new(Paren {
5667                                this: Expression::Sub(Box::new(BinaryOp {
5668                                    left: Expression::number(7),
5669                                    right: dow,
5670                                    left_comments: Vec::new(),
5671                                    operator_comments: Vec::new(),
5672                                    trailing_comments: Vec::new(),
5673                                    inferred_type: None,
5674                                })),
5675                                trailing_comments: Vec::new(),
5676                            })),
5677                            right: Expression::number(7),
5678                            left_comments: Vec::new(),
5679                            operator_comments: Vec::new(),
5680                            trailing_comments: Vec::new(),
5681                            inferred_type: None,
5682                        }));
5683                        let result = Expression::Add(Box::new(BinaryOp {
5684                            left: date,
5685                            right: Expression::Interval(Box::new(Interval {
5686                                this: Some(Expression::Paren(Box::new(Paren {
5687                                    this: diff,
5688                                    trailing_comments: Vec::new(),
5689                                }))),
5690                                unit: Some(IntervalUnitSpec::Simple {
5691                                    unit: IntervalUnit::Day,
5692                                    use_plural: false,
5693                                }),
5694                            })),
5695                            left_comments: Vec::new(),
5696                            operator_comments: Vec::new(),
5697                            trailing_comments: Vec::new(),
5698                            inferred_type: None,
5699                        }));
5700                        Ok(Expression::Cast(Box::new(Cast {
5701                            this: result,
5702                            to: DataType::Date,
5703                            trailing_comments: Vec::new(),
5704                            double_colon_syntax: false,
5705                            format: None,
5706                            default: None,
5707                            inferred_type: None,
5708                        })))
5709                    }
5710                    _ => Ok(Expression::Function(Box::new(Function::new(
5711                        "LAST_DAY".to_string(),
5712                        vec![date, unit],
5713                    )))),
5714                }
5715            }
5716
5717            // SEQ1/SEQ2/SEQ4/SEQ8 -> (ROW_NUMBER() OVER (ORDER BY 1 NULLS FIRST) - 1) % range
5718            // When FROM clause is RANGE(n), a post-transform replaces this with `range % N`
5719            "SEQ1" | "SEQ2" | "SEQ4" | "SEQ8" => {
5720                let (range, half): (u128, u128) = match name_upper.as_str() {
5721                    "SEQ1" => (256, 128),
5722                    "SEQ2" => (65536, 32768),
5723                    "SEQ4" => (4294967296, 2147483648),
5724                    "SEQ8" => (18446744073709551616, 9223372036854775808),
5725                    _ => unreachable!("sequence type already matched in caller"),
5726                };
5727
5728                let is_signed = match f.args.first() {
5729                    Some(Expression::Literal(lit))
5730                        if matches!(lit.as_ref(), Literal::Number(_)) =>
5731                    {
5732                        let Literal::Number(n) = lit.as_ref() else {
5733                            unreachable!()
5734                        };
5735                        n == "1"
5736                    }
5737                    _ => false,
5738                };
5739
5740                let row_num = Expression::Sub(Box::new(BinaryOp {
5741                    left: Expression::WindowFunction(Box::new(
5742                        crate::expressions::WindowFunction {
5743                            this: Expression::Function(Box::new(Function::new(
5744                                "ROW_NUMBER".to_string(),
5745                                vec![],
5746                            ))),
5747                            over: crate::expressions::Over {
5748                                window_name: None,
5749                                partition_by: vec![],
5750                                order_by: vec![crate::expressions::Ordered {
5751                                    this: Expression::number(1),
5752                                    desc: false,
5753                                    nulls_first: Some(true),
5754                                    explicit_asc: false,
5755                                    with_fill: None,
5756                                }],
5757                                frame: None,
5758                                alias: None,
5759                            },
5760                            keep: None,
5761                            inferred_type: None,
5762                        },
5763                    )),
5764                    right: Expression::number(1),
5765                    left_comments: Vec::new(),
5766                    operator_comments: Vec::new(),
5767                    trailing_comments: Vec::new(),
5768                    inferred_type: None,
5769                }));
5770
5771                let modded = Expression::Mod(Box::new(BinaryOp {
5772                    left: Expression::Paren(Box::new(Paren {
5773                        this: row_num,
5774                        trailing_comments: Vec::new(),
5775                    })),
5776                    right: Expression::Literal(Box::new(Literal::Number(range.to_string()))),
5777                    left_comments: Vec::new(),
5778                    operator_comments: Vec::new(),
5779                    trailing_comments: Vec::new(),
5780                    inferred_type: None,
5781                }));
5782
5783                if is_signed {
5784                    // CASE WHEN val >= half THEN val - range ELSE val END
5785                    let cond = Expression::Gte(Box::new(BinaryOp {
5786                        left: modded.clone(),
5787                        right: Expression::Literal(Box::new(Literal::Number(half.to_string()))),
5788                        left_comments: Vec::new(),
5789                        operator_comments: Vec::new(),
5790                        trailing_comments: Vec::new(),
5791                        inferred_type: None,
5792                    }));
5793                    let signed_val = Expression::Sub(Box::new(BinaryOp {
5794                        left: modded.clone(),
5795                        right: Expression::Literal(Box::new(Literal::Number(range.to_string()))),
5796                        left_comments: Vec::new(),
5797                        operator_comments: Vec::new(),
5798                        trailing_comments: Vec::new(),
5799                        inferred_type: None,
5800                    }));
5801                    Ok(Expression::Paren(Box::new(Paren {
5802                        this: Expression::Case(Box::new(Case {
5803                            operand: None,
5804                            whens: vec![(cond, signed_val)],
5805                            else_: Some(modded),
5806                            comments: Vec::new(),
5807                            inferred_type: None,
5808                        })),
5809                        trailing_comments: Vec::new(),
5810                    })))
5811                } else {
5812                    Ok(modded)
5813                }
5814            }
5815
5816            // TABLE(fn) -> fn (unwrap TABLE wrapper for DuckDB)
5817            // Also handles TABLE(GENERATOR(ROWCOUNT => n)) -> RANGE(n) directly
5818            "TABLE" if f.args.len() == 1 => {
5819                let inner = f.args.into_iter().next().unwrap();
5820                // If inner is GENERATOR, transform it to RANGE
5821                if let Expression::Function(ref gen_f) = inner {
5822                    if gen_f.name.to_uppercase() == "GENERATOR" {
5823                        let mut rowcount = None;
5824                        for arg in &gen_f.args {
5825                            if let Expression::NamedArgument(na) = arg {
5826                                if na.name.name.to_uppercase() == "ROWCOUNT" {
5827                                    rowcount = Some(na.value.clone());
5828                                }
5829                            }
5830                        }
5831                        if let Some(n) = rowcount {
5832                            return Ok(Expression::Function(Box::new(Function::new(
5833                                "RANGE".to_string(),
5834                                vec![n],
5835                            ))));
5836                        }
5837                    }
5838                }
5839                Ok(inner)
5840            }
5841
5842            // GENERATOR(ROWCOUNT => n) -> RANGE(n) in DuckDB
5843            "GENERATOR" => {
5844                let mut rowcount = None;
5845                for arg in &f.args {
5846                    if let Expression::NamedArgument(na) = arg {
5847                        if na.name.name.to_uppercase() == "ROWCOUNT" {
5848                            rowcount = Some(na.value.clone());
5849                        }
5850                    }
5851                }
5852                if let Some(n) = rowcount {
5853                    Ok(Expression::Function(Box::new(Function::new(
5854                        "RANGE".to_string(),
5855                        vec![n],
5856                    ))))
5857                } else {
5858                    Ok(Expression::Function(Box::new(f)))
5859                }
5860            }
5861
5862            // UNIFORM(low, high, gen) -> CAST(FLOOR(low + RANDOM() * (high - low + 1)) AS BIGINT)
5863            // or with seed: CAST(FLOOR(low + (ABS(HASH(seed)) % 1000000) / 1000000.0 * (high - low + 1)) AS BIGINT)
5864            "UNIFORM" if f.args.len() == 3 => {
5865                let mut args = f.args;
5866                let low = args.remove(0);
5867                let high = args.remove(0);
5868                let gen = args.remove(0);
5869
5870                let range = Expression::Add(Box::new(BinaryOp {
5871                    left: Expression::Sub(Box::new(BinaryOp {
5872                        left: high,
5873                        right: low.clone(),
5874                        left_comments: Vec::new(),
5875                        operator_comments: Vec::new(),
5876                        trailing_comments: Vec::new(),
5877                        inferred_type: None,
5878                    })),
5879                    right: Expression::number(1),
5880                    left_comments: Vec::new(),
5881                    operator_comments: Vec::new(),
5882                    trailing_comments: Vec::new(),
5883                    inferred_type: None,
5884                }));
5885
5886                // Check if gen is RANDOM() (function) or a literal seed
5887                let random_val = match &gen {
5888                    Expression::Rand(_) | Expression::Random(_) => {
5889                        // RANDOM() - use directly
5890                        Expression::Function(Box::new(Function::new("RANDOM".to_string(), vec![])))
5891                    }
5892                    Expression::Function(func) if func.name.to_uppercase() == "RANDOM" => {
5893                        // RANDOM(seed) or RANDOM() - just use RANDOM()
5894                        Expression::Function(Box::new(Function::new("RANDOM".to_string(), vec![])))
5895                    }
5896                    _ => {
5897                        // Seed-based: (ABS(HASH(seed)) % 1000000) / 1000000.0
5898                        let hash = Expression::Function(Box::new(Function::new(
5899                            "HASH".to_string(),
5900                            vec![gen],
5901                        )));
5902                        let abs_hash = Expression::Abs(Box::new(UnaryFunc::new(hash)));
5903                        let modded = Expression::Mod(Box::new(BinaryOp {
5904                            left: abs_hash,
5905                            right: Expression::number(1000000),
5906                            left_comments: Vec::new(),
5907                            operator_comments: Vec::new(),
5908                            trailing_comments: Vec::new(),
5909                            inferred_type: None,
5910                        }));
5911                        let paren_modded = Expression::Paren(Box::new(Paren {
5912                            this: modded,
5913                            trailing_comments: Vec::new(),
5914                        }));
5915                        Expression::Div(Box::new(BinaryOp {
5916                            left: paren_modded,
5917                            right: Expression::Literal(Box::new(Literal::Number(
5918                                "1000000.0".to_string(),
5919                            ))),
5920                            left_comments: Vec::new(),
5921                            operator_comments: Vec::new(),
5922                            trailing_comments: Vec::new(),
5923                            inferred_type: None,
5924                        }))
5925                    }
5926                };
5927
5928                let inner = Expression::Function(Box::new(Function::new(
5929                    "FLOOR".to_string(),
5930                    vec![Expression::Add(Box::new(BinaryOp {
5931                        left: low,
5932                        right: Expression::Mul(Box::new(BinaryOp {
5933                            left: random_val,
5934                            right: Expression::Paren(Box::new(Paren {
5935                                this: range,
5936                                trailing_comments: Vec::new(),
5937                            })),
5938                            left_comments: Vec::new(),
5939                            operator_comments: Vec::new(),
5940                            trailing_comments: Vec::new(),
5941                            inferred_type: None,
5942                        })),
5943                        left_comments: Vec::new(),
5944                        operator_comments: Vec::new(),
5945                        trailing_comments: Vec::new(),
5946                        inferred_type: None,
5947                    }))],
5948                )));
5949
5950                Ok(Expression::Cast(Box::new(Cast {
5951                    this: inner,
5952                    to: DataType::BigInt { length: None },
5953                    trailing_comments: Vec::new(),
5954                    double_colon_syntax: false,
5955                    format: None,
5956                    default: None,
5957                    inferred_type: None,
5958                })))
5959            }
5960
5961            // NORMAL(mean, stddev, gen) -> Box-Muller transform
5962            // mean + (stddev * SQRT(-2 * LN(GREATEST(u1, 1e-10))) * COS(2 * PI() * u2))
5963            // where u1 and u2 are uniform random values derived from gen
5964            "NORMAL" if f.args.len() == 3 => {
5965                let mut args = f.args;
5966                let mean = args.remove(0);
5967                let stddev = args.remove(0);
5968                let gen = args.remove(0);
5969
5970                // Helper to create seed-based random: (ABS(HASH(seed)) % 1000000) / 1000000.0
5971                let make_seed_random = |seed: Expression| -> Expression {
5972                    let hash = Expression::Function(Box::new(Function::new(
5973                        "HASH".to_string(),
5974                        vec![seed],
5975                    )));
5976                    let abs_hash = Expression::Abs(Box::new(UnaryFunc::new(hash)));
5977                    let modded = Expression::Mod(Box::new(BinaryOp {
5978                        left: abs_hash,
5979                        right: Expression::number(1000000),
5980                        left_comments: Vec::new(),
5981                        operator_comments: Vec::new(),
5982                        trailing_comments: Vec::new(),
5983                        inferred_type: None,
5984                    }));
5985                    let paren_modded = Expression::Paren(Box::new(Paren {
5986                        this: modded,
5987                        trailing_comments: Vec::new(),
5988                    }));
5989                    Expression::Div(Box::new(BinaryOp {
5990                        left: paren_modded,
5991                        right: Expression::Literal(Box::new(Literal::Number(
5992                            "1000000.0".to_string(),
5993                        ))),
5994                        left_comments: Vec::new(),
5995                        operator_comments: Vec::new(),
5996                        trailing_comments: Vec::new(),
5997                        inferred_type: None,
5998                    }))
5999                };
6000
6001                // Determine u1 and u2 based on gen type
6002                let is_random_no_seed = match &gen {
6003                    Expression::Random(_) => true,
6004                    Expression::Rand(r) => r.seed.is_none(),
6005                    _ => false,
6006                };
6007                let (u1, u2) = if is_random_no_seed {
6008                    // RANDOM() -> u1 = RANDOM(), u2 = RANDOM()
6009                    let u1 =
6010                        Expression::Function(Box::new(Function::new("RANDOM".to_string(), vec![])));
6011                    let u2 =
6012                        Expression::Function(Box::new(Function::new("RANDOM".to_string(), vec![])));
6013                    (u1, u2)
6014                } else {
6015                    // Seed-based: extract the seed value
6016                    let seed = match gen {
6017                        Expression::Rand(r) => r.seed.map(|s| *s).unwrap_or(Expression::number(0)),
6018                        Expression::Function(func) if func.name.to_uppercase() == "RANDOM" => {
6019                            if func.args.len() == 1 {
6020                                func.args.into_iter().next().unwrap()
6021                            } else {
6022                                Expression::number(0)
6023                            }
6024                        }
6025                        other => other,
6026                    };
6027                    let u1 = make_seed_random(seed.clone());
6028                    let seed_plus_1 = Expression::Add(Box::new(BinaryOp {
6029                        left: seed,
6030                        right: Expression::number(1),
6031                        left_comments: Vec::new(),
6032                        operator_comments: Vec::new(),
6033                        trailing_comments: Vec::new(),
6034                        inferred_type: None,
6035                    }));
6036                    let u2 = make_seed_random(seed_plus_1);
6037                    (u1, u2)
6038                };
6039
6040                // GREATEST(u1, 1e-10)
6041                let greatest = Expression::Greatest(Box::new(VarArgFunc {
6042                    expressions: vec![
6043                        u1,
6044                        Expression::Literal(Box::new(Literal::Number("1e-10".to_string()))),
6045                    ],
6046                    original_name: None,
6047                    inferred_type: None,
6048                }));
6049
6050                // SQRT(-2 * LN(GREATEST(u1, 1e-10)))
6051                let neg2 = Expression::Neg(Box::new(crate::expressions::UnaryOp {
6052                    this: Expression::number(2),
6053                    inferred_type: None,
6054                }));
6055                let ln_greatest =
6056                    Expression::Function(Box::new(Function::new("LN".to_string(), vec![greatest])));
6057                let neg2_times_ln = Expression::Mul(Box::new(BinaryOp {
6058                    left: neg2,
6059                    right: ln_greatest,
6060                    left_comments: Vec::new(),
6061                    operator_comments: Vec::new(),
6062                    trailing_comments: Vec::new(),
6063                    inferred_type: None,
6064                }));
6065                let sqrt_part = Expression::Function(Box::new(Function::new(
6066                    "SQRT".to_string(),
6067                    vec![neg2_times_ln],
6068                )));
6069
6070                // COS(2 * PI() * u2)
6071                let pi = Expression::Function(Box::new(Function::new("PI".to_string(), vec![])));
6072                let two_pi = Expression::Mul(Box::new(BinaryOp {
6073                    left: Expression::number(2),
6074                    right: pi,
6075                    left_comments: Vec::new(),
6076                    operator_comments: Vec::new(),
6077                    trailing_comments: Vec::new(),
6078                    inferred_type: None,
6079                }));
6080                let two_pi_u2 = Expression::Mul(Box::new(BinaryOp {
6081                    left: two_pi,
6082                    right: u2,
6083                    left_comments: Vec::new(),
6084                    operator_comments: Vec::new(),
6085                    trailing_comments: Vec::new(),
6086                    inferred_type: None,
6087                }));
6088                let cos_part = Expression::Function(Box::new(Function::new(
6089                    "COS".to_string(),
6090                    vec![two_pi_u2],
6091                )));
6092
6093                // stddev * sqrt_part * cos_part
6094                let stddev_times_sqrt = Expression::Mul(Box::new(BinaryOp {
6095                    left: stddev,
6096                    right: sqrt_part,
6097                    left_comments: Vec::new(),
6098                    operator_comments: Vec::new(),
6099                    trailing_comments: Vec::new(),
6100                    inferred_type: None,
6101                }));
6102                let inner = Expression::Mul(Box::new(BinaryOp {
6103                    left: stddev_times_sqrt,
6104                    right: cos_part,
6105                    left_comments: Vec::new(),
6106                    operator_comments: Vec::new(),
6107                    trailing_comments: Vec::new(),
6108                    inferred_type: None,
6109                }));
6110                let paren_inner = Expression::Paren(Box::new(Paren {
6111                    this: inner,
6112                    trailing_comments: Vec::new(),
6113                }));
6114
6115                // mean + (inner)
6116                Ok(Expression::Add(Box::new(BinaryOp {
6117                    left: mean,
6118                    right: paren_inner,
6119                    left_comments: Vec::new(),
6120                    operator_comments: Vec::new(),
6121                    trailing_comments: Vec::new(),
6122                    inferred_type: None,
6123                })))
6124            }
6125
6126            // DATE_TRUNC: DuckDB supports natively, just pass through
6127            // (DuckDB returns the correct type automatically)
6128
6129            // BITOR/BITAND with BITSHIFT need parenthesization
6130            // This is handled via the BITOR/BITAND transforms which create BitwiseOr/BitwiseAnd
6131            // The issue is operator precedence: BITOR(BITSHIFTLEFT(a, b), BITSHIFTLEFT(c, d))
6132            // should generate (a << b) | (c << d), not a << b | c << d
6133
6134            // ZIPF(s, n, gen) -> CTE-based emulation for DuckDB
6135            "ZIPF" if f.args.len() == 3 => {
6136                let mut args = f.args;
6137                let s_expr = args.remove(0);
6138                let n_expr = args.remove(0);
6139                let gen_expr = args.remove(0);
6140
6141                let s_sql = Self::expr_to_sql(&s_expr);
6142                let n_sql = Self::expr_to_sql(&n_expr);
6143                let (seed_sql, is_random) = Self::extract_seed_info(&gen_expr);
6144
6145                let rand_sql = if is_random {
6146                    format!("SELECT {} AS r", seed_sql)
6147                } else {
6148                    format!(
6149                        "SELECT (ABS(HASH({})) % 1000000) / 1000000.0 AS r",
6150                        seed_sql
6151                    )
6152                };
6153
6154                let template = format!(
6155                    "WITH rand AS ({}), weights AS (SELECT i, 1.0 / POWER(i, {}) AS w FROM RANGE(1, {} + 1) AS t(i)), cdf AS (SELECT i, SUM(w) OVER (ORDER BY i NULLS FIRST) / SUM(w) OVER () AS p FROM weights) SELECT MIN(i) FROM cdf WHERE p >= (SELECT r FROM rand)",
6156                    rand_sql, s_sql, n_sql
6157                );
6158
6159                Self::parse_as_subquery(&template)
6160            }
6161
6162            // RANDSTR(len, gen) -> subquery-based emulation for DuckDB
6163            "RANDSTR" if f.args.len() == 2 => {
6164                let mut args = f.args;
6165                let len_expr = args.remove(0);
6166                let gen_expr = args.remove(0);
6167
6168                let len_sql = Self::expr_to_sql(&len_expr);
6169                let (seed_sql, is_random) = Self::extract_seed_info(&gen_expr);
6170
6171                let random_value_sql = if is_random {
6172                    format!("(ABS(HASH(i + {})) % 1000) / 1000.0", seed_sql)
6173                } else {
6174                    format!("(ABS(HASH(i + {})) % 1000) / 1000.0", seed_sql)
6175                };
6176
6177                let template = format!(
6178                    "SELECT LISTAGG(SUBSTRING('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz', 1 + CAST(FLOOR(random_value * 62) AS INT), 1), '') FROM (SELECT {} AS random_value FROM RANGE({}) AS t(i))",
6179                    random_value_sql, len_sql
6180                );
6181
6182                Self::parse_as_subquery(&template)
6183            }
6184
6185            // MAP_CAT(map1, map2) -> explicit merge semantics for DuckDB
6186            "MAP_CAT" if f.args.len() == 2 => {
6187                let mut args = f.args;
6188                let left = Self::normalize_empty_map_expr(args.remove(0));
6189                let right = Self::normalize_empty_map_expr(args.remove(0));
6190                let left_is_null = Expression::IsNull(Box::new(crate::expressions::IsNull {
6191                    this: left.clone(),
6192                    not: false,
6193                    postfix_form: false,
6194                }));
6195                let right_is_null = Expression::IsNull(Box::new(crate::expressions::IsNull {
6196                    this: right.clone(),
6197                    not: false,
6198                    postfix_form: false,
6199                }));
6200                let null_cond = Expression::Or(Box::new(BinaryOp {
6201                    left: left_is_null,
6202                    right: right_is_null,
6203                    left_comments: Vec::new(),
6204                    operator_comments: Vec::new(),
6205                    trailing_comments: Vec::new(),
6206                    inferred_type: None,
6207                }));
6208
6209                let list_concat = Expression::Function(Box::new(Function::new(
6210                    "LIST_CONCAT".to_string(),
6211                    vec![
6212                        Expression::Function(Box::new(Function::new(
6213                            "MAP_KEYS".to_string(),
6214                            vec![left.clone()],
6215                        ))),
6216                        Expression::Function(Box::new(Function::new(
6217                            "MAP_KEYS".to_string(),
6218                            vec![right.clone()],
6219                        ))),
6220                    ],
6221                )));
6222                let list_distinct = Expression::Function(Box::new(Function::new(
6223                    "LIST_DISTINCT".to_string(),
6224                    vec![list_concat],
6225                )));
6226
6227                let k_ident = Identifier::new("__k");
6228                let k_ref = Expression::boxed_column(Column {
6229                    table: None,
6230                    name: k_ident.clone(),
6231                    join_mark: false,
6232                    trailing_comments: Vec::new(),
6233                    span: None,
6234                    inferred_type: None,
6235                });
6236                let right_key = Expression::Subscript(Box::new(crate::expressions::Subscript {
6237                    this: right.clone(),
6238                    index: k_ref.clone(),
6239                }));
6240                let left_key = Expression::Subscript(Box::new(crate::expressions::Subscript {
6241                    this: left.clone(),
6242                    index: k_ref.clone(),
6243                }));
6244                let key_value = Expression::Coalesce(Box::new(VarArgFunc {
6245                    expressions: vec![right_key, left_key],
6246                    original_name: None,
6247                    inferred_type: None,
6248                }));
6249                let struct_pack = Expression::Function(Box::new(Function::new(
6250                    "STRUCT_PACK".to_string(),
6251                    vec![
6252                        Expression::NamedArgument(Box::new(crate::expressions::NamedArgument {
6253                            name: Identifier::new("key"),
6254                            value: k_ref.clone(),
6255                            separator: crate::expressions::NamedArgSeparator::ColonEq,
6256                        })),
6257                        Expression::NamedArgument(Box::new(crate::expressions::NamedArgument {
6258                            name: Identifier::new("value"),
6259                            value: key_value,
6260                            separator: crate::expressions::NamedArgSeparator::ColonEq,
6261                        })),
6262                    ],
6263                )));
6264                let lambda_k = Expression::Lambda(Box::new(crate::expressions::LambdaExpr {
6265                    parameters: vec![k_ident],
6266                    body: struct_pack,
6267                    colon: false,
6268                    parameter_types: Vec::new(),
6269                }));
6270
6271                let list_transform = Expression::Function(Box::new(Function::new(
6272                    "LIST_TRANSFORM".to_string(),
6273                    vec![list_distinct, lambda_k],
6274                )));
6275
6276                let x_ident = Identifier::new("__x");
6277                let x_ref = Expression::boxed_column(Column {
6278                    table: None,
6279                    name: x_ident.clone(),
6280                    join_mark: false,
6281                    trailing_comments: Vec::new(),
6282                    span: None,
6283                    inferred_type: None,
6284                });
6285                let x_value = Expression::Dot(Box::new(crate::expressions::DotAccess {
6286                    this: x_ref,
6287                    field: Identifier::new("value"),
6288                }));
6289                let x_value_is_null = Expression::IsNull(Box::new(crate::expressions::IsNull {
6290                    this: x_value,
6291                    not: false,
6292                    postfix_form: false,
6293                }));
6294                let lambda_x = Expression::Lambda(Box::new(crate::expressions::LambdaExpr {
6295                    parameters: vec![x_ident],
6296                    body: Expression::Not(Box::new(crate::expressions::UnaryOp {
6297                        this: x_value_is_null,
6298                        inferred_type: None,
6299                    })),
6300                    colon: false,
6301                    parameter_types: Vec::new(),
6302                }));
6303
6304                let list_filter = Expression::Function(Box::new(Function::new(
6305                    "LIST_FILTER".to_string(),
6306                    vec![list_transform, lambda_x],
6307                )));
6308                let merged_map = Expression::Function(Box::new(Function::new(
6309                    "MAP_FROM_ENTRIES".to_string(),
6310                    vec![list_filter],
6311                )));
6312
6313                Ok(Expression::Case(Box::new(Case {
6314                    operand: None,
6315                    whens: vec![(null_cond, Expression::Null(crate::expressions::Null))],
6316                    else_: Some(merged_map),
6317                    comments: Vec::new(),
6318                    inferred_type: None,
6319                })))
6320            }
6321
6322            // MINHASH(num_perm, value) -> DuckDB emulation using JSON state payload
6323            "MINHASH" if f.args.len() == 2 => {
6324                let mut args = f.args;
6325                let num_perm = args.remove(0);
6326                let value = args.remove(0);
6327
6328                let num_perm_sql = Self::expr_to_sql(&num_perm);
6329                let value_sql = Self::expr_to_sql(&value);
6330
6331                let template = format!(
6332                    "SELECT JSON_OBJECT('state', LIST(min_h ORDER BY seed NULLS FIRST), 'type', 'minhash', 'version', 1) FROM (SELECT seed, LIST_MIN(LIST_TRANSFORM(vals, __v -> HASH(CAST(__v AS TEXT) || CAST(seed AS TEXT)))) AS min_h FROM (SELECT LIST({value}) AS vals), RANGE(0, {num_perm}) AS t(seed))",
6333                    value = value_sql,
6334                    num_perm = num_perm_sql
6335                );
6336
6337                Self::parse_as_subquery(&template)
6338            }
6339
6340            // MINHASH_COMBINE(sig) -> merge minhash JSON signatures in DuckDB
6341            "MINHASH_COMBINE" if f.args.len() == 1 => {
6342                let sig_sql = Self::expr_to_sql(&f.args[0]);
6343                let template = format!(
6344                    "SELECT JSON_OBJECT('state', LIST(min_h ORDER BY idx NULLS FIRST), 'type', 'minhash', 'version', 1) FROM (SELECT pos AS idx, MIN(val) AS min_h FROM UNNEST(LIST({sig})) AS _(sig) JOIN UNNEST(CAST(sig -> '$.state' AS UBIGINT[])) WITH ORDINALITY AS t(val, pos) ON TRUE GROUP BY pos)",
6345                    sig = sig_sql
6346                );
6347                Self::parse_as_subquery(&template)
6348            }
6349
6350            // APPROXIMATE_SIMILARITY(sig) -> jaccard estimate from minhash signatures
6351            "APPROXIMATE_SIMILARITY" if f.args.len() == 1 => {
6352                let sig_sql = Self::expr_to_sql(&f.args[0]);
6353                let template = format!(
6354                    "SELECT CAST(SUM(CASE WHEN num_distinct = 1 THEN 1 ELSE 0 END) AS DOUBLE) / COUNT(*) FROM (SELECT pos, COUNT(DISTINCT h) AS num_distinct FROM (SELECT h, pos FROM UNNEST(LIST({sig})) AS _(sig) JOIN UNNEST(CAST(sig -> '$.state' AS UBIGINT[])) WITH ORDINALITY AS s(h, pos) ON TRUE) GROUP BY pos)",
6355                    sig = sig_sql
6356                );
6357                Self::parse_as_subquery(&template)
6358            }
6359
6360            // ARRAYS_ZIP(a1, a2, ...) -> struct list construction in DuckDB
6361            "ARRAYS_ZIP" if !f.args.is_empty() => {
6362                let args = f.args;
6363                let n = args.len();
6364                let is_null = |expr: Expression| {
6365                    Expression::IsNull(Box::new(crate::expressions::IsNull {
6366                        this: expr,
6367                        not: false,
6368                        postfix_form: false,
6369                    }))
6370                };
6371                let length_of = |expr: Expression| {
6372                    Expression::Function(Box::new(Function::new("LENGTH".to_string(), vec![expr])))
6373                };
6374                let eq_zero = |expr: Expression| {
6375                    Expression::Eq(Box::new(BinaryOp {
6376                        left: expr,
6377                        right: Expression::number(0),
6378                        left_comments: Vec::new(),
6379                        operator_comments: Vec::new(),
6380                        trailing_comments: Vec::new(),
6381                        inferred_type: None,
6382                    }))
6383                };
6384                let and_expr = |left: Expression, right: Expression| {
6385                    Expression::And(Box::new(BinaryOp {
6386                        left,
6387                        right,
6388                        left_comments: Vec::new(),
6389                        operator_comments: Vec::new(),
6390                        trailing_comments: Vec::new(),
6391                        inferred_type: None,
6392                    }))
6393                };
6394                let or_expr = |left: Expression, right: Expression| {
6395                    Expression::Or(Box::new(BinaryOp {
6396                        left,
6397                        right,
6398                        left_comments: Vec::new(),
6399                        operator_comments: Vec::new(),
6400                        trailing_comments: Vec::new(),
6401                        inferred_type: None,
6402                    }))
6403                };
6404
6405                let null_cond = args.iter().cloned().map(is_null).reduce(or_expr).unwrap();
6406                let empty_cond = args
6407                    .iter()
6408                    .cloned()
6409                    .map(|a| eq_zero(length_of(a)))
6410                    .reduce(and_expr)
6411                    .unwrap();
6412
6413                let null_struct = Expression::Struct(Box::new(Struct {
6414                    fields: (1..=n)
6415                        .map(|i| {
6416                            (
6417                                Some(format!("${}", i)),
6418                                Expression::Null(crate::expressions::Null),
6419                            )
6420                        })
6421                        .collect(),
6422                }));
6423                let empty_result = Expression::Array(Box::new(crate::expressions::Array {
6424                    expressions: vec![null_struct],
6425                }));
6426
6427                let range_upper = if n == 1 {
6428                    length_of(args[0].clone())
6429                } else {
6430                    let length_null_cond = args
6431                        .iter()
6432                        .cloned()
6433                        .map(|a| is_null(length_of(a)))
6434                        .reduce(or_expr)
6435                        .unwrap();
6436                    let greatest_len = Expression::Greatest(Box::new(VarArgFunc {
6437                        expressions: args.iter().cloned().map(length_of).collect(),
6438                        original_name: None,
6439                        inferred_type: None,
6440                    }));
6441                    Expression::Case(Box::new(Case {
6442                        operand: None,
6443                        whens: vec![(length_null_cond, Expression::Null(crate::expressions::Null))],
6444                        else_: Some(greatest_len),
6445                        comments: Vec::new(),
6446                        inferred_type: None,
6447                    }))
6448                };
6449
6450                let range_expr = Expression::Function(Box::new(Function::new(
6451                    "RANGE".to_string(),
6452                    vec![Expression::number(0), range_upper],
6453                )));
6454
6455                let i_ident = Identifier::new("__i");
6456                let i_ref = Expression::boxed_column(Column {
6457                    table: None,
6458                    name: i_ident.clone(),
6459                    join_mark: false,
6460                    trailing_comments: Vec::new(),
6461                    span: None,
6462                    inferred_type: None,
6463                });
6464                let i_plus_one = Expression::Add(Box::new(BinaryOp {
6465                    left: i_ref,
6466                    right: Expression::number(1),
6467                    left_comments: Vec::new(),
6468                    operator_comments: Vec::new(),
6469                    trailing_comments: Vec::new(),
6470                    inferred_type: None,
6471                }));
6472                let empty_array = Expression::Array(Box::new(crate::expressions::Array {
6473                    expressions: vec![],
6474                }));
6475                let zipped_struct = Expression::Struct(Box::new(Struct {
6476                    fields: args
6477                        .iter()
6478                        .enumerate()
6479                        .map(|(i, a)| {
6480                            let coalesced = Expression::Coalesce(Box::new(VarArgFunc {
6481                                expressions: vec![a.clone(), empty_array.clone()],
6482                                original_name: None,
6483                                inferred_type: None,
6484                            }));
6485                            let item =
6486                                Expression::Subscript(Box::new(crate::expressions::Subscript {
6487                                    this: coalesced,
6488                                    index: i_plus_one.clone(),
6489                                }));
6490                            (Some(format!("${}", i + 1)), item)
6491                        })
6492                        .collect(),
6493                }));
6494                let lambda_i = Expression::Lambda(Box::new(crate::expressions::LambdaExpr {
6495                    parameters: vec![i_ident],
6496                    body: zipped_struct,
6497                    colon: false,
6498                    parameter_types: Vec::new(),
6499                }));
6500                let zipped_result = Expression::Function(Box::new(Function::new(
6501                    "LIST_TRANSFORM".to_string(),
6502                    vec![range_expr, lambda_i],
6503                )));
6504
6505                Ok(Expression::Case(Box::new(Case {
6506                    operand: None,
6507                    whens: vec![
6508                        (null_cond, Expression::Null(crate::expressions::Null)),
6509                        (empty_cond, empty_result),
6510                    ],
6511                    else_: Some(zipped_result),
6512                    comments: Vec::new(),
6513                    inferred_type: None,
6514                })))
6515            }
6516
6517            // STRTOK(str, delim, pos) -> complex CASE expression
6518            // Snowflake's STRTOK treats each character in delim as a separate delimiter,
6519            // so we use REGEXP_SPLIT_TO_ARRAY with a character class regex.
6520            "STRTOK" if f.args.len() == 3 => {
6521                let mut args = f.args.into_iter();
6522                let str_arg = args.next().unwrap();
6523                let delim_arg = args.next().unwrap();
6524                let pos_arg = args.next().unwrap();
6525
6526                // Helper: create empty string literal ''
6527                let empty_str = || Expression::string("".to_string());
6528                // Helper: create NULL literal
6529                let null_expr = || Expression::Null(crate::expressions::Null);
6530
6531                // WHEN delim = '' AND str = '' THEN NULL
6532                let when1_cond = Expression::And(Box::new(BinaryOp::new(
6533                    Expression::Eq(Box::new(BinaryOp::new(delim_arg.clone(), empty_str()))),
6534                    Expression::Eq(Box::new(BinaryOp::new(str_arg.clone(), empty_str()))),
6535                )));
6536
6537                // WHEN delim = '' AND pos = 1 THEN str
6538                let when2_cond = Expression::And(Box::new(BinaryOp::new(
6539                    Expression::Eq(Box::new(BinaryOp::new(delim_arg.clone(), empty_str()))),
6540                    Expression::Eq(Box::new(BinaryOp::new(
6541                        pos_arg.clone(),
6542                        Expression::number(1),
6543                    ))),
6544                )));
6545
6546                // WHEN delim = '' THEN NULL
6547                let when3_cond =
6548                    Expression::Eq(Box::new(BinaryOp::new(delim_arg.clone(), empty_str())));
6549
6550                // WHEN pos < 0 THEN NULL
6551                let when4_cond = Expression::Lt(Box::new(BinaryOp::new(
6552                    pos_arg.clone(),
6553                    Expression::number(0),
6554                )));
6555
6556                // WHEN str IS NULL OR delim IS NULL OR pos IS NULL THEN NULL
6557                let str_is_null = Expression::IsNull(Box::new(crate::expressions::IsNull {
6558                    this: str_arg.clone(),
6559                    not: false,
6560                    postfix_form: false,
6561                }));
6562                let delim_is_null = Expression::IsNull(Box::new(crate::expressions::IsNull {
6563                    this: delim_arg.clone(),
6564                    not: false,
6565                    postfix_form: false,
6566                }));
6567                let pos_is_null = Expression::IsNull(Box::new(crate::expressions::IsNull {
6568                    this: pos_arg.clone(),
6569                    not: false,
6570                    postfix_form: false,
6571                }));
6572                let when5_cond = Expression::Or(Box::new(BinaryOp::new(
6573                    Expression::Or(Box::new(BinaryOp::new(str_is_null, delim_is_null))),
6574                    pos_is_null,
6575                )));
6576
6577                // Inner CASE for the regex pattern:
6578                // CASE WHEN delim = '' THEN ''
6579                //      ELSE '[' || REGEXP_REPLACE(delim, '([\[\]^.\-*+?(){}|$\\])', '\\\1', 'g') || ']'
6580                // END
6581                let regex_replace = Expression::Function(Box::new(Function::new(
6582                    "REGEXP_REPLACE".to_string(),
6583                    vec![
6584                        delim_arg.clone(),
6585                        Expression::string(r"([\[\]^.\-*+?(){}|$\\])".to_string()),
6586                        Expression::string(r"\\\1".to_string()),
6587                        Expression::string("g".to_string()),
6588                    ],
6589                )));
6590
6591                // '[' || REGEXP_REPLACE(...) || ']'
6592                let concat_regex = Expression::DPipe(Box::new(crate::expressions::DPipe {
6593                    this: Box::new(Expression::DPipe(Box::new(crate::expressions::DPipe {
6594                        this: Box::new(Expression::string("[".to_string())),
6595                        expression: Box::new(regex_replace),
6596                        safe: None,
6597                    }))),
6598                    expression: Box::new(Expression::string("]".to_string())),
6599                    safe: None,
6600                }));
6601
6602                let inner_case = Expression::Case(Box::new(Case {
6603                    operand: None,
6604                    whens: vec![(
6605                        Expression::Eq(Box::new(BinaryOp::new(delim_arg.clone(), empty_str()))),
6606                        empty_str(),
6607                    )],
6608                    else_: Some(concat_regex),
6609                    comments: Vec::new(),
6610                    inferred_type: None,
6611                }));
6612
6613                // REGEXP_SPLIT_TO_ARRAY(str, <inner_case>)
6614                let regexp_split = Expression::Function(Box::new(Function::new(
6615                    "REGEXP_SPLIT_TO_ARRAY".to_string(),
6616                    vec![str_arg.clone(), inner_case],
6617                )));
6618
6619                // Lambda: x -> NOT x = ''
6620                let lambda = Expression::Lambda(Box::new(crate::expressions::LambdaExpr {
6621                    parameters: vec![Identifier::new("x".to_string())],
6622                    body: Expression::Not(Box::new(crate::expressions::UnaryOp {
6623                        this: Expression::Eq(Box::new(BinaryOp::new(
6624                            Expression::boxed_column(Column {
6625                                table: None,
6626                                name: Identifier::new("x".to_string()),
6627                                join_mark: false,
6628                                trailing_comments: Vec::new(),
6629                                span: None,
6630                                inferred_type: None,
6631                            }),
6632                            empty_str(),
6633                        ))),
6634                        inferred_type: None,
6635                    })),
6636                    colon: false,
6637                    parameter_types: Vec::new(),
6638                }));
6639
6640                // LIST_FILTER(<regexp_split>, <lambda>)
6641                let list_filter = Expression::Function(Box::new(Function::new(
6642                    "LIST_FILTER".to_string(),
6643                    vec![regexp_split, lambda],
6644                )));
6645
6646                // LIST_FILTER(...)[pos]
6647                let subscripted = Expression::Subscript(Box::new(crate::expressions::Subscript {
6648                    this: list_filter,
6649                    index: pos_arg.clone(),
6650                }));
6651
6652                Ok(Expression::Case(Box::new(Case {
6653                    operand: None,
6654                    whens: vec![
6655                        (when1_cond, null_expr()),
6656                        (when2_cond, str_arg.clone()),
6657                        (when3_cond, null_expr()),
6658                        (when4_cond, null_expr()),
6659                        (when5_cond, null_expr()),
6660                    ],
6661                    else_: Some(subscripted),
6662                    comments: Vec::new(),
6663                    inferred_type: None,
6664                })))
6665            }
6666
6667            // Pass through everything else
6668            _ => Ok(Expression::Function(Box::new(f))),
6669        }
6670    }
6671
6672    /// Convert Snowflake date format to DuckDB strptime format
6673    fn convert_snowflake_date_format(&self, fmt: Expression) -> Expression {
6674        match fmt {
6675            Expression::Literal(lit) if matches!(lit.as_ref(), Literal::String(_)) => {
6676                let Literal::String(s) = lit.as_ref() else {
6677                    unreachable!()
6678                };
6679                let converted = Self::snowflake_to_strptime(&s);
6680                Expression::Literal(Box::new(Literal::String(converted)))
6681            }
6682            _ => fmt,
6683        }
6684    }
6685
6686    /// Convert Snowflake time format to DuckDB strptime format
6687    fn convert_snowflake_time_format(&self, fmt: Expression) -> Expression {
6688        match fmt {
6689            Expression::Literal(lit) if matches!(lit.as_ref(), Literal::String(_)) => {
6690                let Literal::String(s) = lit.as_ref() else {
6691                    unreachable!()
6692                };
6693                let converted = Self::snowflake_to_strptime(&s);
6694                Expression::Literal(Box::new(Literal::String(converted)))
6695            }
6696            _ => fmt,
6697        }
6698    }
6699
6700    /// Token-based conversion from Snowflake format strings (both original and normalized) to DuckDB strptime format.
6701    /// Handles both uppercase Snowflake originals (YYYY, MM, DD) and normalized lowercase forms (yyyy, mm, DD).
6702    fn snowflake_to_strptime(s: &str) -> String {
6703        let mut result = String::new();
6704        let chars: Vec<char> = s.chars().collect();
6705        let len = chars.len();
6706        let mut i = 0;
6707        while i < len {
6708            let remaining = &s[i..];
6709            let remaining_upper: String =
6710                remaining.chars().take(8).collect::<String>().to_uppercase();
6711
6712            // Compound patterns first
6713            if remaining_upper.starts_with("HH24MISS") {
6714                result.push_str("%H%M%S");
6715                i += 8;
6716            } else if remaining_upper.starts_with("MMMM") {
6717                result.push_str("%B");
6718                i += 4;
6719            } else if remaining_upper.starts_with("YYYY") {
6720                result.push_str("%Y");
6721                i += 4;
6722            } else if remaining_upper.starts_with("YY") {
6723                result.push_str("%y");
6724                i += 2;
6725            } else if remaining_upper.starts_with("MON") {
6726                result.push_str("%b");
6727                i += 3;
6728            } else if remaining_upper.starts_with("HH24") {
6729                result.push_str("%H");
6730                i += 4;
6731            } else if remaining_upper.starts_with("HH12") {
6732                result.push_str("%I");
6733                i += 4;
6734            } else if remaining_upper.starts_with("HH") {
6735                result.push_str("%I");
6736                i += 2;
6737            } else if remaining_upper.starts_with("MISS") {
6738                result.push_str("%M%S");
6739                i += 4;
6740            } else if remaining_upper.starts_with("MI") {
6741                result.push_str("%M");
6742                i += 2;
6743            } else if remaining_upper.starts_with("MM") {
6744                result.push_str("%m");
6745                i += 2;
6746            } else if remaining_upper.starts_with("DD") {
6747                result.push_str("%d");
6748                i += 2;
6749            } else if remaining_upper.starts_with("DY") {
6750                result.push_str("%a");
6751                i += 2;
6752            } else if remaining_upper.starts_with("SS") {
6753                result.push_str("%S");
6754                i += 2;
6755            } else if remaining_upper.starts_with("FF") {
6756                // FF with optional digit (FF, FF1-FF9)
6757                // %f = microseconds (6 digits, FF1-FF6), %n = nanoseconds (9 digits, FF7-FF9)
6758                let ff_pos = i + 2;
6759                if ff_pos < len && chars[ff_pos].is_ascii_digit() {
6760                    let digit = chars[ff_pos].to_digit(10).unwrap_or(6);
6761                    if digit >= 7 {
6762                        result.push_str("%n");
6763                    } else {
6764                        result.push_str("%f");
6765                    }
6766                    i += 3; // skip FF + digit
6767                } else {
6768                    result.push_str("%f");
6769                    i += 2;
6770                }
6771            } else if remaining_upper.starts_with("PM") || remaining_upper.starts_with("AM") {
6772                result.push_str("%p");
6773                i += 2;
6774            } else if remaining_upper.starts_with("TZH") {
6775                result.push_str("%z");
6776                i += 3;
6777            } else if remaining_upper.starts_with("TZM") {
6778                // TZM is part of timezone, skip
6779                i += 3;
6780            } else {
6781                result.push(chars[i]);
6782                i += 1;
6783            }
6784        }
6785        result
6786    }
6787
6788    /// Convert BigQuery format string to DuckDB strptime format
6789    /// BigQuery: %E6S -> DuckDB: %S.%f (seconds with microseconds)
6790    fn convert_bq_to_strptime_format(&self, fmt: Expression) -> Expression {
6791        match fmt {
6792            Expression::Literal(lit) if matches!(lit.as_ref(), Literal::String(_)) => {
6793                let Literal::String(s) = lit.as_ref() else {
6794                    unreachable!()
6795                };
6796                let converted = s.replace("%E6S", "%S.%f").replace("%E*S", "%S.%f");
6797                Expression::Literal(Box::new(Literal::String(converted)))
6798            }
6799            _ => fmt,
6800        }
6801    }
6802
6803    /// Transform DATE_PART(unit, expr) for DuckDB
6804    fn transform_date_part(&self, args: Vec<Expression>) -> Result<Expression> {
6805        let mut args = args;
6806        let unit_expr = args.remove(0);
6807        let date_expr = args.remove(0);
6808        let unit_name = match &unit_expr {
6809            Expression::Column(c) => c.name.name.to_uppercase(),
6810            Expression::Identifier(i) => i.name.to_uppercase(),
6811            Expression::Var(v) => v.this.to_uppercase(),
6812            Expression::Literal(lit) if matches!(lit.as_ref(), Literal::String(_)) => {
6813                let Literal::String(s) = lit.as_ref() else {
6814                    unreachable!()
6815                };
6816                s.to_uppercase()
6817            }
6818            _ => {
6819                return Ok(Expression::Function(Box::new(Function::new(
6820                    "DATE_PART".to_string(),
6821                    vec![unit_expr, date_expr],
6822                ))))
6823            }
6824        };
6825        match unit_name.as_str() {
6826            "EPOCH_SECOND" | "EPOCH" => Ok(Expression::Cast(Box::new(Cast {
6827                this: Expression::Function(Box::new(Function::new(
6828                    "EPOCH".to_string(),
6829                    vec![date_expr],
6830                ))),
6831                to: DataType::BigInt { length: None },
6832                trailing_comments: Vec::new(),
6833                double_colon_syntax: false,
6834                format: None,
6835                default: None,
6836                inferred_type: None,
6837            }))),
6838            "EPOCH_MILLISECOND" | "EPOCH_MILLISECONDS" => Ok(Expression::Function(Box::new(
6839                Function::new("EPOCH_MS".to_string(), vec![date_expr]),
6840            ))),
6841            "EPOCH_MICROSECOND" | "EPOCH_MICROSECONDS" => Ok(Expression::Function(Box::new(
6842                Function::new("EPOCH_US".to_string(), vec![date_expr]),
6843            ))),
6844            "EPOCH_NANOSECOND" | "EPOCH_NANOSECONDS" => Ok(Expression::Function(Box::new(
6845                Function::new("EPOCH_NS".to_string(), vec![date_expr]),
6846            ))),
6847            "DAYOFWEEKISO" | "DAYOFWEEK_ISO" => Ok(Expression::Extract(Box::new(
6848                crate::expressions::ExtractFunc {
6849                    this: date_expr,
6850                    field: crate::expressions::DateTimeField::Custom("ISODOW".to_string()),
6851                },
6852            ))),
6853            "YEAROFWEEK" | "YEAROFWEEKISO" => Ok(Expression::Cast(Box::new(Cast {
6854                this: Expression::Function(Box::new(Function::new(
6855                    "STRFTIME".to_string(),
6856                    vec![
6857                        date_expr,
6858                        Expression::Literal(Box::new(Literal::String("%G".to_string()))),
6859                    ],
6860                ))),
6861                to: DataType::Int {
6862                    length: None,
6863                    integer_spelling: false,
6864                },
6865                trailing_comments: Vec::new(),
6866                double_colon_syntax: false,
6867                format: None,
6868                default: None,
6869                inferred_type: None,
6870            }))),
6871            "WEEKISO" => Ok(Expression::Cast(Box::new(Cast {
6872                this: Expression::Function(Box::new(Function::new(
6873                    "STRFTIME".to_string(),
6874                    vec![
6875                        date_expr,
6876                        Expression::Literal(Box::new(Literal::String("%V".to_string()))),
6877                    ],
6878                ))),
6879                to: DataType::Int {
6880                    length: None,
6881                    integer_spelling: false,
6882                },
6883                trailing_comments: Vec::new(),
6884                double_colon_syntax: false,
6885                format: None,
6886                default: None,
6887                inferred_type: None,
6888            }))),
6889            "NANOSECOND" | "NANOSECONDS" | "NS" => Ok(Expression::Cast(Box::new(Cast {
6890                this: Expression::Function(Box::new(Function::new(
6891                    "STRFTIME".to_string(),
6892                    vec![
6893                        Expression::Cast(Box::new(Cast {
6894                            this: date_expr,
6895                            to: DataType::Custom {
6896                                name: "TIMESTAMP_NS".to_string(),
6897                            },
6898                            trailing_comments: Vec::new(),
6899                            double_colon_syntax: false,
6900                            format: None,
6901                            default: None,
6902                            inferred_type: None,
6903                        })),
6904                        Expression::Literal(Box::new(Literal::String("%n".to_string()))),
6905                    ],
6906                ))),
6907                to: DataType::BigInt { length: None },
6908                trailing_comments: Vec::new(),
6909                double_colon_syntax: false,
6910                format: None,
6911                default: None,
6912                inferred_type: None,
6913            }))),
6914            "DAYOFMONTH" => Ok(Expression::Extract(Box::new(
6915                crate::expressions::ExtractFunc {
6916                    this: date_expr,
6917                    field: crate::expressions::DateTimeField::Day,
6918                },
6919            ))),
6920            _ => {
6921                let field = match unit_name.as_str() {
6922                    "YEAR" | "YY" | "YYYY" => crate::expressions::DateTimeField::Year,
6923                    "MONTH" | "MON" | "MM" => crate::expressions::DateTimeField::Month,
6924                    "DAY" | "DD" | "D" => crate::expressions::DateTimeField::Day,
6925                    "HOUR" | "HH" => crate::expressions::DateTimeField::Hour,
6926                    "MINUTE" | "MI" | "MIN" => crate::expressions::DateTimeField::Minute,
6927                    "SECOND" | "SEC" | "SS" => crate::expressions::DateTimeField::Second,
6928                    "MILLISECOND" | "MS" => crate::expressions::DateTimeField::Millisecond,
6929                    "MICROSECOND" | "US" => crate::expressions::DateTimeField::Microsecond,
6930                    "QUARTER" | "QTR" => crate::expressions::DateTimeField::Quarter,
6931                    "WEEK" | "WK" => crate::expressions::DateTimeField::Week,
6932                    "DAYOFWEEK" | "DOW" => crate::expressions::DateTimeField::DayOfWeek,
6933                    "DAYOFYEAR" | "DOY" => crate::expressions::DateTimeField::DayOfYear,
6934                    "TIMEZONE_HOUR" => crate::expressions::DateTimeField::TimezoneHour,
6935                    "TIMEZONE_MINUTE" => crate::expressions::DateTimeField::TimezoneMinute,
6936                    _ => crate::expressions::DateTimeField::Custom(unit_name),
6937                };
6938                Ok(Expression::Extract(Box::new(
6939                    crate::expressions::ExtractFunc {
6940                        this: date_expr,
6941                        field,
6942                    },
6943                )))
6944            }
6945        }
6946    }
6947
6948    /// Transform DATEADD(unit, amount, date) for DuckDB
6949    fn transform_dateadd(&self, args: Vec<Expression>) -> Result<Expression> {
6950        let mut args = args;
6951        let unit_expr = args.remove(0);
6952        let amount = args.remove(0);
6953        let date = args.remove(0);
6954        let unit_name = match &unit_expr {
6955            Expression::Column(c) => c.name.name.to_uppercase(),
6956            Expression::Identifier(i) => i.name.to_uppercase(),
6957            Expression::Var(v) => v.this.to_uppercase(),
6958            Expression::Literal(lit) if matches!(lit.as_ref(), Literal::String(_)) => {
6959                let Literal::String(s) = lit.as_ref() else {
6960                    unreachable!()
6961                };
6962                s.to_uppercase()
6963            }
6964            _ => String::new(),
6965        };
6966        if unit_name == "NANOSECOND" || unit_name == "NS" {
6967            let epoch_ns = Expression::Function(Box::new(Function::new(
6968                "EPOCH_NS".to_string(),
6969                vec![Expression::Cast(Box::new(Cast {
6970                    this: date,
6971                    to: DataType::Custom {
6972                        name: "TIMESTAMP_NS".to_string(),
6973                    },
6974                    trailing_comments: Vec::new(),
6975                    double_colon_syntax: false,
6976                    format: None,
6977                    default: None,
6978                    inferred_type: None,
6979                }))],
6980            )));
6981            return Ok(Expression::Function(Box::new(Function::new(
6982                "MAKE_TIMESTAMP_NS".to_string(),
6983                vec![Expression::Add(Box::new(BinaryOp {
6984                    left: epoch_ns,
6985                    right: amount,
6986                    left_comments: Vec::new(),
6987                    operator_comments: Vec::new(),
6988                    trailing_comments: Vec::new(),
6989                    inferred_type: None,
6990                }))],
6991            ))));
6992        }
6993        let (interval_unit, multiplied_amount) = match unit_name.as_str() {
6994            "YEAR" | "YY" | "YYYY" => (IntervalUnit::Year, amount),
6995            "MONTH" | "MON" | "MM" => (IntervalUnit::Month, amount),
6996            "DAY" | "DD" | "D" => (IntervalUnit::Day, amount),
6997            "HOUR" | "HH" => (IntervalUnit::Hour, amount),
6998            "MINUTE" | "MI" | "MIN" => (IntervalUnit::Minute, amount),
6999            "SECOND" | "SEC" | "SS" => (IntervalUnit::Second, amount),
7000            "MILLISECOND" | "MS" => (IntervalUnit::Millisecond, amount),
7001            "MICROSECOND" | "US" => (IntervalUnit::Microsecond, amount),
7002            "WEEK" | "WK" => (
7003                IntervalUnit::Day,
7004                Expression::Mul(Box::new(BinaryOp {
7005                    left: amount,
7006                    right: Expression::number(7),
7007                    left_comments: Vec::new(),
7008                    operator_comments: Vec::new(),
7009                    trailing_comments: Vec::new(),
7010                    inferred_type: None,
7011                })),
7012            ),
7013            "QUARTER" | "QTR" => (
7014                IntervalUnit::Month,
7015                Expression::Mul(Box::new(BinaryOp {
7016                    left: amount,
7017                    right: Expression::number(3),
7018                    left_comments: Vec::new(),
7019                    operator_comments: Vec::new(),
7020                    trailing_comments: Vec::new(),
7021                    inferred_type: None,
7022                })),
7023            ),
7024            _ => (IntervalUnit::Day, amount),
7025        };
7026        Ok(Expression::Add(Box::new(BinaryOp {
7027            left: date,
7028            right: Expression::Interval(Box::new(Interval {
7029                this: Some(multiplied_amount),
7030                unit: Some(IntervalUnitSpec::Simple {
7031                    unit: interval_unit,
7032                    use_plural: false,
7033                }),
7034            })),
7035            left_comments: Vec::new(),
7036            operator_comments: Vec::new(),
7037            trailing_comments: Vec::new(),
7038            inferred_type: None,
7039        })))
7040    }
7041
7042    /// Transform DATEDIFF(unit, start, end) for DuckDB
7043    fn transform_datediff(&self, args: Vec<Expression>) -> Result<Expression> {
7044        let mut args = args;
7045        let unit_expr = args.remove(0);
7046        let start = args.remove(0);
7047        let end = args.remove(0);
7048        let unit_name = match &unit_expr {
7049            Expression::Column(c) => c.name.name.to_uppercase(),
7050            Expression::Identifier(i) => i.name.to_uppercase(),
7051            Expression::Var(v) => v.this.to_uppercase(),
7052            Expression::Literal(lit) if matches!(lit.as_ref(), Literal::String(_)) => {
7053                let Literal::String(s) = lit.as_ref() else {
7054                    unreachable!()
7055                };
7056                s.to_uppercase()
7057            }
7058            _ => String::new(),
7059        };
7060        if unit_name == "NANOSECOND" || unit_name == "NS" {
7061            let epoch_end = Expression::Function(Box::new(Function::new(
7062                "EPOCH_NS".to_string(),
7063                vec![Expression::Cast(Box::new(Cast {
7064                    this: end,
7065                    to: DataType::Custom {
7066                        name: "TIMESTAMP_NS".to_string(),
7067                    },
7068                    trailing_comments: Vec::new(),
7069                    double_colon_syntax: false,
7070                    format: None,
7071                    default: None,
7072                    inferred_type: None,
7073                }))],
7074            )));
7075            let epoch_start = Expression::Function(Box::new(Function::new(
7076                "EPOCH_NS".to_string(),
7077                vec![Expression::Cast(Box::new(Cast {
7078                    this: start,
7079                    to: DataType::Custom {
7080                        name: "TIMESTAMP_NS".to_string(),
7081                    },
7082                    trailing_comments: Vec::new(),
7083                    double_colon_syntax: false,
7084                    format: None,
7085                    default: None,
7086                    inferred_type: None,
7087                }))],
7088            )));
7089            return Ok(Expression::Sub(Box::new(BinaryOp {
7090                left: epoch_end,
7091                right: epoch_start,
7092                left_comments: Vec::new(),
7093                operator_comments: Vec::new(),
7094                trailing_comments: Vec::new(),
7095                inferred_type: None,
7096            })));
7097        }
7098        if unit_name == "WEEK" || unit_name == "WK" {
7099            let trunc_start = Expression::Function(Box::new(Function::new(
7100                "DATE_TRUNC".to_string(),
7101                vec![
7102                    Expression::Literal(Box::new(Literal::String("WEEK".to_string()))),
7103                    Expression::Cast(Box::new(Cast {
7104                        this: start,
7105                        to: DataType::Date,
7106                        trailing_comments: Vec::new(),
7107                        double_colon_syntax: false,
7108                        format: None,
7109                        default: None,
7110                        inferred_type: None,
7111                    })),
7112                ],
7113            )));
7114            let trunc_end = Expression::Function(Box::new(Function::new(
7115                "DATE_TRUNC".to_string(),
7116                vec![
7117                    Expression::Literal(Box::new(Literal::String("WEEK".to_string()))),
7118                    Expression::Cast(Box::new(Cast {
7119                        this: end,
7120                        to: DataType::Date,
7121                        trailing_comments: Vec::new(),
7122                        double_colon_syntax: false,
7123                        format: None,
7124                        default: None,
7125                        inferred_type: None,
7126                    })),
7127                ],
7128            )));
7129            return Ok(Expression::Function(Box::new(Function::new(
7130                "DATE_DIFF".to_string(),
7131                vec![
7132                    Expression::Literal(Box::new(Literal::String("WEEK".to_string()))),
7133                    trunc_start,
7134                    trunc_end,
7135                ],
7136            ))));
7137        }
7138        let cast_if_string = |e: Expression| -> Expression {
7139            match &e {
7140                Expression::Literal(lit) if matches!(lit.as_ref(), Literal::String(_)) => {
7141                    Expression::Cast(Box::new(Cast {
7142                        this: e,
7143                        to: DataType::Date,
7144                        trailing_comments: Vec::new(),
7145                        double_colon_syntax: false,
7146                        format: None,
7147                        default: None,
7148                        inferred_type: None,
7149                    }))
7150                }
7151                _ => e,
7152            }
7153        };
7154        let start = cast_if_string(start);
7155        let end = cast_if_string(end);
7156        Ok(Expression::Function(Box::new(Function::new(
7157            "DATE_DIFF".to_string(),
7158            vec![
7159                Expression::Literal(Box::new(Literal::String(unit_name))),
7160                start,
7161                end,
7162            ],
7163        ))))
7164    }
7165
7166    fn transform_aggregate_function(
7167        &self,
7168        f: Box<crate::expressions::AggregateFunction>,
7169    ) -> Result<Expression> {
7170        let name_upper = f.name.to_uppercase();
7171        match name_upper.as_str() {
7172            // GROUP_CONCAT -> LISTAGG
7173            "GROUP_CONCAT" if !f.args.is_empty() => Ok(Expression::Function(Box::new(
7174                Function::new("LISTAGG".to_string(), f.args),
7175            ))),
7176
7177            // LISTAGG is native to DuckDB
7178            "LISTAGG" => Ok(Expression::AggregateFunction(f)),
7179
7180            // STRING_AGG -> LISTAGG
7181            "STRING_AGG" if !f.args.is_empty() => Ok(Expression::Function(Box::new(
7182                Function::new("LISTAGG".to_string(), f.args),
7183            ))),
7184
7185            // ARRAY_AGG -> list (or array_agg, both work)
7186            "ARRAY_AGG" if !f.args.is_empty() => Ok(Expression::Function(Box::new(Function::new(
7187                "list".to_string(),
7188                f.args,
7189            )))),
7190
7191            // LOGICAL_OR -> BOOL_OR with CAST to BOOLEAN
7192            "LOGICAL_OR" if !f.args.is_empty() => {
7193                let arg = f.args.into_iter().next().unwrap();
7194                Ok(Expression::Function(Box::new(Function::new(
7195                    "BOOL_OR".to_string(),
7196                    vec![Expression::Cast(Box::new(crate::expressions::Cast {
7197                        this: arg,
7198                        to: crate::expressions::DataType::Boolean,
7199                        trailing_comments: Vec::new(),
7200                        double_colon_syntax: false,
7201                        format: None,
7202                        default: None,
7203                        inferred_type: None,
7204                    }))],
7205                ))))
7206            }
7207
7208            // LOGICAL_AND -> BOOL_AND with CAST to BOOLEAN
7209            "LOGICAL_AND" if !f.args.is_empty() => {
7210                let arg = f.args.into_iter().next().unwrap();
7211                Ok(Expression::Function(Box::new(Function::new(
7212                    "BOOL_AND".to_string(),
7213                    vec![Expression::Cast(Box::new(crate::expressions::Cast {
7214                        this: arg,
7215                        to: crate::expressions::DataType::Boolean,
7216                        trailing_comments: Vec::new(),
7217                        double_colon_syntax: false,
7218                        format: None,
7219                        default: None,
7220                        inferred_type: None,
7221                    }))],
7222                ))))
7223            }
7224
7225            // SKEW -> SKEWNESS
7226            "SKEW" => Ok(Expression::Function(Box::new(Function::new(
7227                "SKEWNESS".to_string(),
7228                f.args,
7229            )))),
7230
7231            // REGR_VALX(y, x) -> CASE WHEN y IS NULL THEN CAST(NULL AS DOUBLE) ELSE x END
7232            "REGR_VALX" if f.args.len() == 2 => {
7233                let mut args = f.args;
7234                let y = args.remove(0);
7235                let x = args.remove(0);
7236                Ok(Expression::Case(Box::new(Case {
7237                    operand: None,
7238                    whens: vec![(
7239                        Expression::IsNull(Box::new(crate::expressions::IsNull {
7240                            this: y,
7241                            not: false,
7242                            postfix_form: false,
7243                        })),
7244                        Expression::Cast(Box::new(Cast {
7245                            this: Expression::Null(crate::expressions::Null),
7246                            to: DataType::Double {
7247                                precision: None,
7248                                scale: None,
7249                            },
7250                            trailing_comments: Vec::new(),
7251                            double_colon_syntax: false,
7252                            format: None,
7253                            default: None,
7254                            inferred_type: None,
7255                        })),
7256                    )],
7257                    else_: Some(x),
7258                    comments: Vec::new(),
7259                    inferred_type: None,
7260                })))
7261            }
7262
7263            // REGR_VALY(y, x) -> CASE WHEN x IS NULL THEN CAST(NULL AS DOUBLE) ELSE y END
7264            "REGR_VALY" if f.args.len() == 2 => {
7265                let mut args = f.args;
7266                let y = args.remove(0);
7267                let x = args.remove(0);
7268                Ok(Expression::Case(Box::new(Case {
7269                    operand: None,
7270                    whens: vec![(
7271                        Expression::IsNull(Box::new(crate::expressions::IsNull {
7272                            this: x,
7273                            not: false,
7274                            postfix_form: false,
7275                        })),
7276                        Expression::Cast(Box::new(Cast {
7277                            this: Expression::Null(crate::expressions::Null),
7278                            to: DataType::Double {
7279                                precision: None,
7280                                scale: None,
7281                            },
7282                            trailing_comments: Vec::new(),
7283                            double_colon_syntax: false,
7284                            format: None,
7285                            default: None,
7286                            inferred_type: None,
7287                        })),
7288                    )],
7289                    else_: Some(y),
7290                    comments: Vec::new(),
7291                    inferred_type: None,
7292                })))
7293            }
7294
7295            // BOOLAND_AGG -> BOOL_AND(CAST(arg AS BOOLEAN))
7296            "BOOLAND_AGG" if !f.args.is_empty() => {
7297                let arg = f.args.into_iter().next().unwrap();
7298                Ok(Expression::Function(Box::new(Function::new(
7299                    "BOOL_AND".to_string(),
7300                    vec![Expression::Cast(Box::new(Cast {
7301                        this: arg,
7302                        to: DataType::Boolean,
7303                        trailing_comments: Vec::new(),
7304                        double_colon_syntax: false,
7305                        format: None,
7306                        default: None,
7307                        inferred_type: None,
7308                    }))],
7309                ))))
7310            }
7311
7312            // BOOLOR_AGG -> BOOL_OR(CAST(arg AS BOOLEAN))
7313            "BOOLOR_AGG" if !f.args.is_empty() => {
7314                let arg = f.args.into_iter().next().unwrap();
7315                Ok(Expression::Function(Box::new(Function::new(
7316                    "BOOL_OR".to_string(),
7317                    vec![Expression::Cast(Box::new(Cast {
7318                        this: arg,
7319                        to: DataType::Boolean,
7320                        trailing_comments: Vec::new(),
7321                        double_colon_syntax: false,
7322                        format: None,
7323                        default: None,
7324                        inferred_type: None,
7325                    }))],
7326                ))))
7327            }
7328
7329            // BOOLXOR_AGG(c) -> COUNT_IF(CAST(c AS BOOLEAN)) = 1
7330            "BOOLXOR_AGG" if !f.args.is_empty() => {
7331                let arg = f.args.into_iter().next().unwrap();
7332                Ok(Expression::Eq(Box::new(BinaryOp {
7333                    left: Expression::Function(Box::new(Function::new(
7334                        "COUNT_IF".to_string(),
7335                        vec![Expression::Cast(Box::new(Cast {
7336                            this: arg,
7337                            to: DataType::Boolean,
7338                            trailing_comments: Vec::new(),
7339                            double_colon_syntax: false,
7340                            format: None,
7341                            default: None,
7342                            inferred_type: None,
7343                        }))],
7344                    ))),
7345                    right: Expression::number(1),
7346                    left_comments: Vec::new(),
7347                    operator_comments: Vec::new(),
7348                    trailing_comments: Vec::new(),
7349                    inferred_type: None,
7350                })))
7351            }
7352
7353            // MAX_BY -> ARG_MAX
7354            "MAX_BY" if f.args.len() == 2 => Ok(Expression::AggregateFunction(Box::new(
7355                crate::expressions::AggregateFunction {
7356                    name: "ARG_MAX".to_string(),
7357                    ..(*f)
7358                },
7359            ))),
7360
7361            // MIN_BY -> ARG_MIN
7362            "MIN_BY" if f.args.len() == 2 => Ok(Expression::AggregateFunction(Box::new(
7363                crate::expressions::AggregateFunction {
7364                    name: "ARG_MIN".to_string(),
7365                    ..(*f)
7366                },
7367            ))),
7368
7369            // CORR - pass through (DuckDB handles NaN natively)
7370            "CORR" if f.args.len() == 2 => Ok(Expression::AggregateFunction(f)),
7371
7372            // BITMAP_CONSTRUCT_AGG(v) -> complex DuckDB subquery emulation
7373            "BITMAP_CONSTRUCT_AGG" if f.args.len() == 1 => {
7374                let v_sql = Self::expr_to_sql(&f.args[0]);
7375
7376                let template = format!(
7377                    "SELECT CASE WHEN l IS NULL OR LENGTH(l) = 0 THEN NULL WHEN LENGTH(l) <> LENGTH(LIST_FILTER(l, __v -> __v BETWEEN 0 AND 32767)) THEN NULL WHEN LENGTH(l) < 5 THEN UNHEX(PRINTF('%04X', LENGTH(l)) || h || REPEAT('00', GREATEST(0, 4 - LENGTH(l)) * 2)) ELSE UNHEX('08000000000000000000' || h) END FROM (SELECT l, COALESCE(LIST_REDUCE(LIST_TRANSFORM(l, __x -> PRINTF('%02X%02X', CAST(__x AS INT) & 255, (CAST(__x AS INT) >> 8) & 255)), (__a, __b) -> __a || __b, ''), '') AS h FROM (SELECT LIST_SORT(LIST_DISTINCT(LIST({v}) FILTER(WHERE NOT {v} IS NULL))) AS l))",
7378                    v = v_sql
7379                );
7380
7381                Self::parse_as_subquery(&template)
7382            }
7383
7384            // Pass through everything else
7385            _ => Ok(Expression::AggregateFunction(f)),
7386        }
7387    }
7388
7389    /// Convert Presto/MySQL format string to DuckDB format string
7390    /// DuckDB uses strftime/strptime C-style format specifiers
7391    /// Key difference: %i (Presto minutes) -> %M (DuckDB minutes)
7392    fn convert_format_to_duckdb(expr: &Expression) -> Expression {
7393        if let Expression::Literal(lit) = expr {
7394            if let Literal::String(s) = lit.as_ref() {
7395                let duckdb_fmt = Self::presto_to_duckdb_format(s);
7396                Expression::Literal(Box::new(Literal::String(duckdb_fmt)))
7397            } else {
7398                expr.clone()
7399            }
7400        } else {
7401            expr.clone()
7402        }
7403    }
7404
7405    /// Convert Presto format specifiers to DuckDB strftime format
7406    fn presto_to_duckdb_format(fmt: &str) -> String {
7407        let mut result = String::new();
7408        let chars: Vec<char> = fmt.chars().collect();
7409        let mut i = 0;
7410        while i < chars.len() {
7411            if chars[i] == '%' && i + 1 < chars.len() {
7412                match chars[i + 1] {
7413                    'i' => {
7414                        // Presto %i (minutes) -> DuckDB %M (minutes)
7415                        result.push_str("%M");
7416                        i += 2;
7417                    }
7418                    'T' => {
7419                        // Presto %T (time shorthand %H:%M:%S)
7420                        result.push_str("%H:%M:%S");
7421                        i += 2;
7422                    }
7423                    'F' => {
7424                        // Presto %F (date shorthand %Y-%m-%d)
7425                        result.push_str("%Y-%m-%d");
7426                        i += 2;
7427                    }
7428                    _ => {
7429                        result.push('%');
7430                        result.push(chars[i + 1]);
7431                        i += 2;
7432                    }
7433                }
7434            } else {
7435                result.push(chars[i]);
7436                i += 1;
7437            }
7438        }
7439        result
7440    }
7441}
7442
7443#[cfg(test)]
7444mod tests {
7445    use super::*;
7446    use crate::dialects::Dialect;
7447
7448    fn transpile_to_duckdb(sql: &str) -> String {
7449        let dialect = Dialect::get(DialectType::Generic);
7450        let result = dialect
7451            .transpile_to(sql, DialectType::DuckDB)
7452            .expect("Transpile failed");
7453        result[0].clone()
7454    }
7455
7456    #[test]
7457    fn test_ifnull_to_coalesce() {
7458        let result = transpile_to_duckdb("SELECT IFNULL(a, b)");
7459        assert!(
7460            result.contains("COALESCE"),
7461            "Expected COALESCE, got: {}",
7462            result
7463        );
7464    }
7465
7466    #[test]
7467    fn test_nvl_to_coalesce() {
7468        let result = transpile_to_duckdb("SELECT NVL(a, b)");
7469        assert!(
7470            result.contains("COALESCE"),
7471            "Expected COALESCE, got: {}",
7472            result
7473        );
7474    }
7475
7476    #[test]
7477    fn test_basic_select() {
7478        let result = transpile_to_duckdb("SELECT a, b FROM users WHERE id = 1");
7479        assert!(result.contains("SELECT"));
7480        assert!(result.contains("FROM users"));
7481    }
7482
7483    #[test]
7484    fn test_group_concat_to_listagg() {
7485        let result = transpile_to_duckdb("SELECT GROUP_CONCAT(name)");
7486        assert!(
7487            result.contains("LISTAGG"),
7488            "Expected LISTAGG, got: {}",
7489            result
7490        );
7491    }
7492
7493    #[test]
7494    fn test_listagg_preserved() {
7495        let result = transpile_to_duckdb("SELECT LISTAGG(name)");
7496        assert!(
7497            result.contains("LISTAGG"),
7498            "Expected LISTAGG, got: {}",
7499            result
7500        );
7501    }
7502
7503    #[test]
7504    fn test_date_format_to_strftime() {
7505        let result = transpile_to_duckdb("SELECT DATE_FORMAT(d, '%Y-%m-%d')");
7506        // Generator uppercases function names
7507        assert!(
7508            result.to_uppercase().contains("STRFTIME"),
7509            "Expected STRFTIME, got: {}",
7510            result
7511        );
7512    }
7513
7514    #[test]
7515    fn test_regexp_like_to_regexp_matches() {
7516        let result = transpile_to_duckdb("SELECT REGEXP_LIKE(name, 'pattern')");
7517        // Generator uppercases function names
7518        assert!(
7519            result.to_uppercase().contains("REGEXP_MATCHES"),
7520            "Expected REGEXP_MATCHES, got: {}",
7521            result
7522        );
7523    }
7524
7525    #[test]
7526    fn test_double_quote_identifiers() {
7527        // DuckDB uses double quotes for identifiers
7528        let dialect = Dialect::get(DialectType::DuckDB);
7529        let config = dialect.generator_config();
7530        assert_eq!(config.identifier_quote, '"');
7531    }
7532
7533    /// Helper for DuckDB identity tests (parse with DuckDB, generate with DuckDB)
7534    fn duckdb_identity(sql: &str) -> String {
7535        let dialect = Dialect::get(DialectType::DuckDB);
7536        let ast = dialect.parse(sql).expect("Parse failed");
7537        let transformed = dialect.transform(ast[0].clone()).expect("Transform failed");
7538        dialect.generate(&transformed).expect("Generate failed")
7539    }
7540
7541    #[test]
7542    fn test_interval_quoting() {
7543        // Test 137: INTERVAL value should be quoted for DuckDB
7544        let result = duckdb_identity("SELECT DATE_ADD(CAST('2020-01-01' AS DATE), INTERVAL 1 DAY)");
7545        assert_eq!(
7546            result, "SELECT CAST('2020-01-01' AS DATE) + INTERVAL '1' DAY",
7547            "Interval value should be quoted as string"
7548        );
7549    }
7550
7551    #[test]
7552    fn test_struct_pack_to_curly_brace() {
7553        // Test 221: STRUCT_PACK should become curly brace notation
7554        let result = duckdb_identity("CAST([STRUCT_PACK(a := 1)] AS STRUCT(a BIGINT)[])");
7555        assert_eq!(
7556            result, "CAST([{'a': 1}] AS STRUCT(a BIGINT)[])",
7557            "STRUCT_PACK should be transformed to curly brace notation"
7558        );
7559    }
7560
7561    #[test]
7562    fn test_struct_pack_nested() {
7563        // Test 220: Nested STRUCT_PACK
7564        let result = duckdb_identity("CAST([[STRUCT_PACK(a := 1)]] AS STRUCT(a BIGINT)[][])");
7565        assert_eq!(
7566            result, "CAST([[{'a': 1}]] AS STRUCT(a BIGINT)[][])",
7567            "Nested STRUCT_PACK should be transformed"
7568        );
7569    }
7570
7571    #[test]
7572    fn test_struct_pack_cast() {
7573        // Test 222: STRUCT_PACK with :: cast
7574        let result = duckdb_identity("STRUCT_PACK(a := 'b')::json");
7575        assert_eq!(
7576            result, "CAST({'a': 'b'} AS JSON)",
7577            "STRUCT_PACK with cast should be transformed"
7578        );
7579    }
7580
7581    #[test]
7582    fn test_list_value_to_bracket() {
7583        // Test 309: LIST_VALUE should become bracket notation
7584        let result = duckdb_identity("SELECT LIST_VALUE(1)[i]");
7585        assert_eq!(
7586            result, "SELECT [1][i]",
7587            "LIST_VALUE should be transformed to bracket notation"
7588        );
7589    }
7590
7591    #[test]
7592    fn test_list_value_in_struct_literal() {
7593        // Test 310: LIST_VALUE inside struct literal
7594        let result = duckdb_identity("{'x': LIST_VALUE(1)[i]}");
7595        assert_eq!(
7596            result, "{'x': [1][i]}",
7597            "LIST_VALUE inside struct literal should be transformed"
7598        );
7599    }
7600
7601    #[test]
7602    fn test_struct_pack_simple() {
7603        // Simple STRUCT_PACK without nesting
7604        let result = duckdb_identity("SELECT STRUCT_PACK(a := 1)");
7605        eprintln!("STRUCT_PACK result: {}", result);
7606        assert!(
7607            result.contains("{"),
7608            "Expected curly brace, got: {}",
7609            result
7610        );
7611    }
7612
7613    #[test]
7614    fn test_not_in_position() {
7615        // Test 78: NOT IN should become NOT (...) IN (...)
7616        // DuckDB prefers `NOT (expr) IN (list)` over `expr NOT IN (list)`
7617        let result = duckdb_identity(
7618            "SELECT col FROM t WHERE JSON_EXTRACT_STRING(col, '$.id') NOT IN ('b')",
7619        );
7620        assert_eq!(
7621            result, "SELECT col FROM t WHERE NOT (col ->> '$.id') IN ('b')",
7622            "NOT IN should have NOT moved outside and JSON expression wrapped"
7623        );
7624    }
7625
7626    #[test]
7627    fn test_unnest_comma_join_to_join_on_true() {
7628        // Test 310: Comma-join with UNNEST should become JOIN ... ON TRUE
7629        let result = duckdb_identity(
7630            "WITH _data AS (SELECT [{'a': 1, 'b': 2}, {'a': 2, 'b': 3}] AS col) SELECT t.col['b'] FROM _data, UNNEST(_data.col) AS t(col) WHERE t.col['a'] = 1",
7631        );
7632        assert_eq!(
7633            result,
7634            "WITH _data AS (SELECT [{'a': 1, 'b': 2}, {'a': 2, 'b': 3}] AS col) SELECT t.col['b'] FROM _data JOIN UNNEST(_data.col) AS t(col) ON TRUE WHERE t.col['a'] = 1",
7635            "Comma-join with UNNEST should become JOIN ON TRUE"
7636        );
7637    }
7638}