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