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