Skip to main content

polyglot_sql/dialects/
bigquery.rs

1//! BigQuery Dialect
2//!
3//! BigQuery-specific transformations based on sqlglot patterns.
4//! Key differences:
5//! - Uses backticks for identifiers (especially for project.dataset.table)
6//! - SAFE_ prefix for safe operations
7//! - Different date/time function names (DATE_DIFF, FORMAT_DATE, PARSE_DATE)
8//! - STRUCT and ARRAY syntax differences
9//! - No ILIKE support
10//! - QUALIFY clause support
11
12use super::{DialectImpl, DialectType};
13use crate::error::Result;
14use crate::expressions::{
15    Alias, BinaryOp, CeilFunc, Column, Exists, Expression, From, Function, FunctionBody,
16    Identifier, JsonExtractFunc, LikeOp, Literal, Select, SplitFunc, StringAggFunc, UnaryFunc,
17    UnnestFunc, VarArgFunc, Where,
18};
19#[cfg(feature = "generate")]
20use crate::generator::GeneratorConfig;
21use crate::tokens::TokenizerConfig;
22
23/// BigQuery dialect
24pub struct BigQueryDialect;
25
26impl DialectImpl for BigQueryDialect {
27    fn dialect_type(&self) -> DialectType {
28        DialectType::BigQuery
29    }
30
31    fn tokenizer_config(&self) -> TokenizerConfig {
32        let mut config = TokenizerConfig::default();
33        // BigQuery uses backticks for identifiers, NOT double quotes
34        // Remove double quote from identifiers (it's in the default config)
35        config.identifiers.remove(&'"');
36        config.identifiers.insert('`', '`');
37        // BigQuery supports double quotes for strings (in addition to single quotes)
38        config.quotes.insert("\"".to_string(), "\"".to_string());
39        // BigQuery supports triple-quoted strings
40        config.quotes.insert("'''".to_string(), "'''".to_string());
41        config
42            .quotes
43            .insert("\"\"\"".to_string(), "\"\"\"".to_string());
44        // BigQuery supports backslash escaping in strings
45        config.string_escapes = vec!['\'', '\\'];
46        // In BigQuery, b'...' is a byte string (bytes), not a bit string (binary digits)
47        config.b_prefix_is_byte_string = true;
48        // BigQuery supports hex number strings like 0xA, 0xFF
49        config.hex_number_strings = true;
50        // BigQuery: 0xA represents integer 10 (not binary/blob)
51        config.hex_string_is_integer_type = true;
52        // BigQuery supports # as single-line comments.
53        config.hash_comments = true;
54        config
55    }
56
57    #[cfg(feature = "generate")]
58
59    fn generator_config(&self) -> GeneratorConfig {
60        use crate::generator::{IdentifierQuoteStyle, NormalizeFunctions};
61        GeneratorConfig {
62            identifier_quote: '`',
63            identifier_quote_style: IdentifierQuoteStyle::BACKTICK,
64            dialect: Some(DialectType::BigQuery),
65            // BigQuery doesn't normalize function names (Python: NORMALIZE_FUNCTIONS = False)
66            normalize_functions: NormalizeFunctions::None,
67            // BigQuery-specific settings from Python sqlglot
68            interval_allows_plural_form: false,
69            join_hints: false,
70            query_hints: false,
71            table_hints: false,
72            limit_fetch_style: crate::generator::LimitFetchStyle::Limit,
73            rename_table_with_db: false,
74            nvl2_supported: false,
75            unnest_with_ordinality: false,
76            collate_is_func: true,
77            limit_only_literals: true,
78            supports_table_alias_columns: false,
79            unpivot_aliases_are_identifiers: false,
80            json_key_value_pair_sep: ",",
81            null_ordering_supported: false,
82            ignore_nulls_in_func: true,
83            json_path_single_quote_escape: true,
84            can_implement_array_any: true,
85            supports_to_number: false,
86            named_placeholder_token: "@",
87            hex_func: "TO_HEX",
88            with_properties_prefix: "OPTIONS",
89            supports_exploding_projections: false,
90            except_intersect_support_all_clause: false,
91            supports_unix_seconds: true,
92            // BigQuery uses SAFE_ prefix for safe operations
93            try_supported: true,
94            // BigQuery does not support SEMI/ANTI JOIN syntax
95            semi_anti_join_with_side: false,
96            ..Default::default()
97        }
98    }
99
100    #[cfg(feature = "transpile")]
101
102    fn transform_expr(&self, expr: Expression) -> Result<Expression> {
103        match expr {
104            // ===== Data Type Mappings =====
105            Expression::DataType(dt) => self.transform_data_type(dt),
106
107            Expression::Table(mut table)
108                if table.catalog.is_none()
109                    && table.alias.is_none()
110                    && table.schema.as_ref().map_or(false, |schema| {
111                        schema.quoted && schema.name.contains("INFORMATION_SCHEMA")
112                    }) =>
113            {
114                let schema = table.schema.take().expect("schema checked above");
115                let old_name = table.name.clone();
116                let alias = old_name.name.clone();
117                table.name = Identifier {
118                    name: format!("{}.{}", schema.name, old_name.name),
119                    quoted: true,
120                    trailing_comments: old_name.trailing_comments,
121                    span: old_name.span,
122                };
123                table.alias = Some(Identifier::new(alias));
124                Ok(Expression::Table(table))
125            }
126
127            // ===== Null handling =====
128            // IFNULL is native to BigQuery - keep as-is for identity
129            Expression::IfNull(f) => Ok(Expression::IfNull(f)),
130
131            // NVL -> IFNULL in BigQuery (BigQuery uses IFNULL, not NVL)
132            Expression::Nvl(f) => Ok(Expression::IfNull(f)),
133
134            // Coalesce stays as Coalesce
135            Expression::Coalesce(f) => Ok(Expression::Coalesce(f)),
136
137            // ===== String aggregation =====
138            // GROUP_CONCAT -> STRING_AGG in BigQuery
139            Expression::GroupConcat(f) => Ok(Expression::StringAgg(Box::new(StringAggFunc {
140                this: f.this,
141                separator: f.separator,
142                order_by: f.order_by,
143                distinct: f.distinct,
144                filter: f.filter,
145                limit: None,
146                inferred_type: None,
147            }))),
148
149            // ===== Cast operations =====
150            // Cast data types are transformed by transform_recursive in mod.rs
151            // which calls transform_data_type via transform_expr(Expression::DataType(...))
152
153            // TryCast -> SafeCast in BigQuery with type transformation
154            Expression::TryCast(c) => {
155                let transformed_type = match self.transform_data_type(c.to)? {
156                    Expression::DataType(dt) => dt,
157                    _ => return Err(crate::error::Error::parse("Expected DataType", 0, 0, 0, 0)),
158                };
159                Ok(Expression::SafeCast(Box::new(crate::expressions::Cast {
160                    this: c.this,
161                    to: transformed_type,
162                    trailing_comments: c.trailing_comments,
163                    double_colon_syntax: c.double_colon_syntax,
164                    format: c.format,
165                    default: c.default,
166                    inferred_type: None,
167                })))
168            }
169
170            // ===== Pattern matching =====
171            // ILIKE -> LOWER() LIKE LOWER() in BigQuery (no ILIKE support)
172            Expression::ILike(op) => {
173                let lower_left = Expression::Lower(Box::new(UnaryFunc::new(op.left)));
174                let lower_right = Expression::Lower(Box::new(UnaryFunc::new(op.right)));
175                Ok(Expression::Like(Box::new(LikeOp {
176                    left: lower_left,
177                    right: lower_right,
178                    escape: op.escape,
179                    quantifier: op.quantifier,
180                    inferred_type: None,
181                })))
182            }
183
184            // RegexpLike -> REGEXP_CONTAINS in BigQuery
185            Expression::RegexpLike(f) => Ok(Expression::Function(Box::new(Function::new(
186                "REGEXP_CONTAINS".to_string(),
187                vec![f.this, f.pattern],
188            )))),
189
190            // ===== Array operations =====
191            // EXPLODE -> UNNEST in BigQuery
192            Expression::Explode(f) => Ok(Expression::Unnest(Box::new(
193                crate::expressions::UnnestFunc {
194                    this: f.this,
195                    expressions: Vec::new(),
196                    with_ordinality: false,
197                    alias: None,
198                    offset_alias: None,
199                    inferred_type: None,
200                },
201            ))),
202
203            // ExplodeOuter -> UNNEST with LEFT JOIN semantics
204            Expression::ExplodeOuter(f) => Ok(Expression::Unnest(Box::new(
205                crate::expressions::UnnestFunc {
206                    this: f.this,
207                    expressions: Vec::new(),
208                    with_ordinality: false,
209                    alias: None,
210                    offset_alias: None,
211                    inferred_type: None,
212                },
213            ))),
214
215            // GenerateSeries -> GENERATE_ARRAY in BigQuery
216            Expression::GenerateSeries(f) => {
217                let mut args = Vec::new();
218                if let Some(start) = f.start {
219                    args.push(*start);
220                }
221                if let Some(end) = f.end {
222                    args.push(*end);
223                }
224                if let Some(step) = f.step {
225                    args.push(*step);
226                }
227                Ok(Expression::Function(Box::new(Function::new(
228                    "GENERATE_ARRAY".to_string(),
229                    args,
230                ))))
231            }
232
233            // ===== Bitwise operations =====
234            // BitwiseAndAgg -> BIT_AND
235            Expression::BitwiseAndAgg(f) => Ok(Expression::Function(Box::new(Function::new(
236                "BIT_AND".to_string(),
237                vec![f.this],
238            )))),
239
240            // BitwiseOrAgg -> BIT_OR
241            Expression::BitwiseOrAgg(f) => Ok(Expression::Function(Box::new(Function::new(
242                "BIT_OR".to_string(),
243                vec![f.this],
244            )))),
245
246            // BitwiseXorAgg -> BIT_XOR
247            Expression::BitwiseXorAgg(f) => Ok(Expression::Function(Box::new(Function::new(
248                "BIT_XOR".to_string(),
249                vec![f.this],
250            )))),
251
252            // BitwiseCount -> BIT_COUNT
253            Expression::BitwiseCount(f) => Ok(Expression::Function(Box::new(Function::new(
254                "BIT_COUNT".to_string(),
255                vec![f.this],
256            )))),
257
258            // ByteLength -> BYTE_LENGTH
259            Expression::ByteLength(f) => Ok(Expression::Function(Box::new(Function::new(
260                "BYTE_LENGTH".to_string(),
261                vec![f.this],
262            )))),
263
264            // IntDiv -> DIV
265            Expression::IntDiv(f) => Ok(Expression::Function(Box::new(Function::new(
266                "DIV".to_string(),
267                vec![f.this, f.expression],
268            )))),
269
270            // Int64 -> INT64
271            Expression::Int64(f) => Ok(Expression::Function(Box::new(Function::new(
272                "INT64".to_string(),
273                vec![f.this],
274            )))),
275
276            // ===== Random =====
277            // RANDOM -> RAND in BigQuery
278            Expression::Random(_) => Ok(Expression::Rand(Box::new(crate::expressions::Rand {
279                seed: None,
280                lower: None,
281                upper: None,
282            }))),
283
284            // ===== UUID =====
285            // Uuid -> GENERATE_UUID in BigQuery
286            Expression::Uuid(_) => Ok(Expression::Function(Box::new(Function::new(
287                "GENERATE_UUID".to_string(),
288                vec![],
289            )))),
290
291            // ===== Approximate functions =====
292            // ApproxDistinct -> APPROX_COUNT_DISTINCT
293            Expression::ApproxDistinct(f) => Ok(Expression::Function(Box::new(Function::new(
294                "APPROX_COUNT_DISTINCT".to_string(),
295                vec![f.this],
296            )))),
297
298            // ArgMax -> MAX_BY in BigQuery
299            Expression::ArgMax(f) => Ok(Expression::Function(Box::new(Function::new(
300                "MAX_BY".to_string(),
301                vec![*f.this, *f.expression],
302            )))),
303
304            // ArgMin -> MIN_BY in BigQuery
305            Expression::ArgMin(f) => Ok(Expression::Function(Box::new(Function::new(
306                "MIN_BY".to_string(),
307                vec![*f.this, *f.expression],
308            )))),
309
310            // ===== Conditional =====
311            // CountIf -> COUNTIF in BigQuery
312            Expression::CountIf(f) => Ok(Expression::Function(Box::new(Function::new(
313                "COUNTIF".to_string(),
314                vec![f.this],
315            )))),
316
317            // ===== String functions =====
318            // StringAgg -> STRING_AGG in BigQuery - keep as-is to preserve ORDER BY
319            Expression::StringAgg(f) => Ok(Expression::StringAgg(f)),
320
321            // ===== Conversion =====
322            // Unhex -> FROM_HEX
323            Expression::Unhex(f) => Ok(Expression::Function(Box::new(Function::new(
324                "FROM_HEX".to_string(),
325                vec![*f.this],
326            )))),
327
328            // UnixToTime -> TIMESTAMP_SECONDS/MILLIS/MICROS based on scale
329            Expression::UnixToTime(f) => {
330                let scale = f.scale.unwrap_or(0);
331                match scale {
332                    0 => Ok(Expression::Function(Box::new(Function::new(
333                        "TIMESTAMP_SECONDS".to_string(),
334                        vec![*f.this],
335                    )))),
336                    3 => Ok(Expression::Function(Box::new(Function::new(
337                        "TIMESTAMP_MILLIS".to_string(),
338                        vec![*f.this],
339                    )))),
340                    6 => Ok(Expression::Function(Box::new(Function::new(
341                        "TIMESTAMP_MICROS".to_string(),
342                        vec![*f.this],
343                    )))),
344                    _ => {
345                        // TIMESTAMP_SECONDS(CAST(value / POWER(10, scale) AS INT64))
346                        let div_expr =
347                            Expression::Div(Box::new(crate::expressions::BinaryOp::new(
348                                *f.this,
349                                Expression::Function(Box::new(Function::new(
350                                    "POWER".to_string(),
351                                    vec![Expression::number(10), Expression::number(scale)],
352                                ))),
353                            )));
354                        let cast_expr = Expression::Cast(Box::new(crate::expressions::Cast {
355                            this: div_expr,
356                            to: crate::expressions::DataType::Custom {
357                                name: "INT64".to_string(),
358                            },
359                            double_colon_syntax: false,
360                            trailing_comments: vec![],
361                            format: None,
362                            default: None,
363                            inferred_type: None,
364                        }));
365                        Ok(Expression::Function(Box::new(Function::new(
366                            "TIMESTAMP_SECONDS".to_string(),
367                            vec![cast_expr],
368                        ))))
369                    }
370                }
371            }
372
373            // ===== Date/time =====
374            // DateDiff -> DATE_DIFF in BigQuery
375            Expression::DateDiff(f) => {
376                // BigQuery: DATE_DIFF(date1, date2, part)
377                let unit_str = match f.unit {
378                    Some(crate::expressions::IntervalUnit::Year) => "YEAR",
379                    Some(crate::expressions::IntervalUnit::Quarter) => "QUARTER",
380                    Some(crate::expressions::IntervalUnit::Month) => "MONTH",
381                    Some(crate::expressions::IntervalUnit::Week) => "WEEK",
382                    Some(crate::expressions::IntervalUnit::Day) => "DAY",
383                    Some(crate::expressions::IntervalUnit::Hour) => "HOUR",
384                    Some(crate::expressions::IntervalUnit::Minute) => "MINUTE",
385                    Some(crate::expressions::IntervalUnit::Second) => "SECOND",
386                    Some(crate::expressions::IntervalUnit::Millisecond) => "MILLISECOND",
387                    Some(crate::expressions::IntervalUnit::Microsecond) => "MICROSECOND",
388                    Some(crate::expressions::IntervalUnit::Nanosecond) => "NANOSECOND",
389                    None => "DAY",
390                };
391                let unit = Expression::Identifier(crate::expressions::Identifier {
392                    name: unit_str.to_string(),
393                    quoted: false,
394                    trailing_comments: Vec::new(),
395                    span: None,
396                });
397                Ok(Expression::Function(Box::new(Function::new(
398                    "DATE_DIFF".to_string(),
399                    vec![f.this, f.expression, unit],
400                ))))
401            }
402
403            // ===== Variance =====
404            // VarPop -> VAR_POP
405            Expression::VarPop(f) => Ok(Expression::Function(Box::new(Function::new(
406                "VAR_POP".to_string(),
407                vec![f.this],
408            )))),
409
410            // ===== Hash functions =====
411            // SHA -> SHA1
412            Expression::SHA(f) => Ok(Expression::Function(Box::new(Function::new(
413                "SHA1".to_string(),
414                vec![f.this],
415            )))),
416
417            // SHA1Digest -> SHA1
418            Expression::SHA1Digest(f) => Ok(Expression::Function(Box::new(Function::new(
419                "SHA1".to_string(),
420                vec![f.this],
421            )))),
422
423            // MD5Digest -> MD5
424            Expression::MD5Digest(f) => Ok(Expression::Function(Box::new(Function::new(
425                "MD5".to_string(),
426                vec![*f.this],
427            )))),
428
429            // ===== Type conversion =====
430            // JSONBool -> BOOL
431            Expression::JSONBool(f) => Ok(Expression::Function(Box::new(Function::new(
432                "BOOL".to_string(),
433                vec![f.this],
434            )))),
435
436            // StringFunc -> STRING
437            Expression::StringFunc(f) => Ok(Expression::Function(Box::new(Function::new(
438                "STRING".to_string(),
439                vec![*f.this],
440            )))),
441
442            // ===== Date/time from parts =====
443            // DateFromUnixDate -> DATE_FROM_UNIX_DATE
444            Expression::DateFromUnixDate(f) => Ok(Expression::Function(Box::new(Function::new(
445                "DATE_FROM_UNIX_DATE".to_string(),
446                vec![f.this],
447            )))),
448
449            // UnixDate -> UNIX_DATE
450            Expression::UnixDate(f) => Ok(Expression::Function(Box::new(Function::new(
451                "UNIX_DATE".to_string(),
452                vec![f.this],
453            )))),
454
455            // TimestampDiff -> TIMESTAMP_DIFF
456            Expression::TimestampDiff(f) => Ok(Expression::Function(Box::new(Function::new(
457                "TIMESTAMP_DIFF".to_string(),
458                vec![*f.this, *f.expression],
459            )))),
460
461            // FromTimeZone -> DATETIME
462            Expression::FromTimeZone(f) => Ok(Expression::Function(Box::new(Function::new(
463                "DATETIME".to_string(),
464                vec![*f.this],
465            )))),
466
467            // TsOrDsToDatetime -> DATETIME
468            Expression::TsOrDsToDatetime(f) => Ok(Expression::Function(Box::new(Function::new(
469                "DATETIME".to_string(),
470                vec![f.this],
471            )))),
472
473            // TsOrDsToTimestamp -> TIMESTAMP
474            Expression::TsOrDsToTimestamp(f) => Ok(Expression::Function(Box::new(Function::new(
475                "TIMESTAMP".to_string(),
476                vec![f.this],
477            )))),
478
479            // Preserve typed BigQuery constructors through parsing, then expose their
480            // canonical spellings to cross-dialect normalization.
481            Expression::Timestamp(f) => {
482                let mut args = Vec::new();
483                if let Some(this) = f.this {
484                    args.push(*this);
485                }
486                if let Some(zone) = f.zone {
487                    args.push(*zone);
488                }
489                Ok(Expression::Function(Box::new(Function::new(
490                    "TIMESTAMP".to_string(),
491                    args,
492                ))))
493            }
494
495            Expression::Round(f) => {
496                let mut args = vec![f.this];
497                if let Some(decimals) = f.decimals {
498                    args.push(decimals);
499                }
500                Ok(Expression::Function(Box::new(Function::new(
501                    "ROUND".to_string(),
502                    args,
503                ))))
504            }
505
506            // ===== IfFunc -> IF in BigQuery =====
507            Expression::IfFunc(f) => {
508                let mut args = vec![f.condition, f.true_value];
509                if let Some(false_val) = f.false_value {
510                    args.push(false_val);
511                } else {
512                    args.push(Expression::Null(crate::expressions::Null));
513                }
514                Ok(Expression::Function(Box::new(Function::new(
515                    "IF".to_string(),
516                    args,
517                ))))
518            }
519
520            // ===== HexString -> FROM_HEX =====
521            Expression::HexStringExpr(f) => Ok(Expression::Function(Box::new(Function::new(
522                "FROM_HEX".to_string(),
523                vec![*f.this],
524            )))),
525
526            // ===== Additional auto-generated transforms from Python sqlglot =====
527            // ApproxTopK -> APPROX_TOP_COUNT
528            Expression::ApproxTopK(f) => {
529                let mut args = vec![*f.this];
530                if let Some(expr) = f.expression {
531                    args.push(*expr);
532                }
533                Ok(Expression::Function(Box::new(Function::new(
534                    "APPROX_TOP_COUNT".to_string(),
535                    args,
536                ))))
537            }
538
539            // SafeDivide -> SAFE_DIVIDE
540            Expression::SafeDivide(f) => Ok(Expression::Function(Box::new(Function::new(
541                "SAFE_DIVIDE".to_string(),
542                vec![*f.this, *f.expression],
543            )))),
544
545            // JSONKeysAtDepth -> JSON_KEYS
546            Expression::JSONKeysAtDepth(f) => Ok(Expression::Function(Box::new(Function::new(
547                "JSON_KEYS".to_string(),
548                vec![*f.this],
549            )))),
550
551            // JSONValueArray -> JSON_VALUE_ARRAY
552            Expression::JSONValueArray(f) => Ok(Expression::Function(Box::new(Function::new(
553                "JSON_VALUE_ARRAY".to_string(),
554                vec![*f.this],
555            )))),
556
557            // DateFromParts -> DATE
558            Expression::DateFromParts(f) => {
559                let mut args = Vec::new();
560                if let Some(y) = f.year {
561                    args.push(*y);
562                }
563                if let Some(m) = f.month {
564                    args.push(*m);
565                }
566                if let Some(d) = f.day {
567                    args.push(*d);
568                }
569                Ok(Expression::Function(Box::new(Function::new(
570                    "DATE".to_string(),
571                    args,
572                ))))
573            }
574
575            // SPLIT: BigQuery defaults to comma separator when none provided
576            // SPLIT(foo) -> SPLIT(foo, ',')
577            Expression::Split(f) => {
578                // Check if delimiter is empty or a placeholder - add default comma
579                let delimiter = match &f.delimiter {
580                    Expression::Literal(lit) if matches!(lit.as_ref(), Literal::String(s) if s.is_empty()) =>
581                    {
582                        let Literal::String(_) = lit.as_ref() else {
583                            unreachable!()
584                        };
585                        Expression::Literal(Box::new(Literal::String(",".to_string())))
586                    }
587                    _ => f.delimiter,
588                };
589                Ok(Expression::Split(Box::new(SplitFunc {
590                    this: f.this,
591                    delimiter,
592                })))
593            }
594
595            // Cast: Transform the target type according to BigQuery TYPE_MAPPING
596            // Special case: CAST to JSON -> PARSE_JSON in BigQuery
597            // Special case: CAST(x AS temporal FORMAT 'fmt') -> PARSE_DATE/PARSE_TIMESTAMP
598            Expression::Cast(c) => {
599                use crate::expressions::DataType;
600                // Check if casting to JSON - use PARSE_JSON instead
601                let is_json = matches!(c.to, DataType::Json | DataType::JsonB)
602                    || matches!(&c.to, DataType::Custom { name } if name.eq_ignore_ascii_case("JSON") || name.eq_ignore_ascii_case("JSONB"));
603                if is_json {
604                    return Ok(Expression::ParseJson(Box::new(UnaryFunc::new(c.this))));
605                }
606                // CAST(x AS temporal_type FORMAT 'fmt') -> PARSE_DATE/PARSE_TIMESTAMP(strftime_fmt, x)
607                if c.format.is_some() {
608                    let is_temporal = matches!(
609                        c.to,
610                        DataType::Date | DataType::Timestamp { .. } | DataType::Time { .. }
611                    ) || matches!(&c.to, DataType::Custom { name } if
612                        name.eq_ignore_ascii_case("TIMESTAMP") ||
613                        name.eq_ignore_ascii_case("DATE") ||
614                        name.eq_ignore_ascii_case("DATETIME") ||
615                        name.eq_ignore_ascii_case("TIME")
616                    );
617                    if is_temporal {
618                        let format_expr = c.format.as_ref().unwrap().as_ref();
619                        // Extract the actual format expr and timezone (if AT TIME ZONE is present)
620                        let (actual_format, timezone) = match format_expr {
621                            Expression::AtTimeZone(ref atz) => {
622                                (atz.this.clone(), Some(atz.zone.clone()))
623                            }
624                            _ => (format_expr.clone(), None),
625                        };
626                        let strftime_fmt = Self::bq_cast_format_to_strftime(&actual_format);
627                        let func_name = match &c.to {
628                            DataType::Date => "PARSE_DATE",
629                            DataType::Custom { name } if name.eq_ignore_ascii_case("DATE") => {
630                                "PARSE_DATE"
631                            }
632                            DataType::Custom { name } if name.eq_ignore_ascii_case("DATETIME") => {
633                                "PARSE_DATETIME"
634                            }
635                            _ => "PARSE_TIMESTAMP",
636                        };
637                        let mut func_args = vec![strftime_fmt, c.this];
638                        if let Some(tz) = timezone {
639                            func_args.push(tz);
640                        }
641                        return Ok(Expression::Function(Box::new(Function::new(
642                            func_name.to_string(),
643                            func_args,
644                        ))));
645                    }
646                }
647                let transformed_type = match self.transform_data_type(c.to)? {
648                    Expression::DataType(dt) => dt,
649                    _ => return Err(crate::error::Error::parse("Expected DataType", 0, 0, 0, 0)),
650                };
651                Ok(Expression::Cast(Box::new(crate::expressions::Cast {
652                    this: c.this,
653                    to: transformed_type,
654                    trailing_comments: c.trailing_comments,
655                    double_colon_syntax: c.double_colon_syntax,
656                    format: c.format,
657                    default: c.default,
658                    inferred_type: None,
659                })))
660            }
661
662            // SafeCast: Transform the target type according to BigQuery TYPE_MAPPING
663            Expression::SafeCast(c) => {
664                let transformed_type = match self.transform_data_type(c.to)? {
665                    Expression::DataType(dt) => dt,
666                    _ => return Err(crate::error::Error::parse("Expected DataType", 0, 0, 0, 0)),
667                };
668                Ok(Expression::SafeCast(Box::new(crate::expressions::Cast {
669                    this: c.this,
670                    to: transformed_type,
671                    trailing_comments: c.trailing_comments,
672                    double_colon_syntax: c.double_colon_syntax,
673                    format: c.format,
674                    default: c.default,
675                    inferred_type: None,
676                })))
677            }
678
679            // ===== SELECT-level transforms =====
680            // BigQuery: GROUP BY expression → alias when both GROUP BY and ORDER BY exist
681            Expression::Select(mut select) => {
682                if select.group_by.is_some() && select.order_by.is_some() {
683                    // Build map: expression → alias name for aliased projections
684                    let aliases: Vec<(Expression, Identifier)> = select
685                        .expressions
686                        .iter()
687                        .filter_map(|e| {
688                            if let Expression::Alias(a) = e {
689                                Some((a.this.clone(), a.alias.clone()))
690                            } else {
691                                None
692                            }
693                        })
694                        .collect();
695
696                    if let Some(ref mut group_by) = select.group_by {
697                        for grouped in group_by.expressions.iter_mut() {
698                            // Skip numeric indices (already aliased)
699                            if matches!(grouped, Expression::Literal(lit) if matches!(lit.as_ref(), Literal::Number(_)))
700                            {
701                                continue;
702                            }
703                            // Check if this GROUP BY expression matches a SELECT alias
704                            for (expr, alias_ident) in &aliases {
705                                if grouped == expr {
706                                    *grouped = Expression::boxed_column(Column {
707                                        name: alias_ident.clone(),
708                                        table: None,
709                                        join_mark: false,
710                                        trailing_comments: Vec::new(),
711                                        span: None,
712                                        inferred_type: None,
713                                    });
714                                    break;
715                                }
716                            }
717                        }
718                    }
719                }
720                Ok(Expression::Select(select))
721            }
722
723            // ===== ArrayContains → EXISTS(SELECT 1 FROM UNNEST(arr) AS _col WHERE _col = val) =====
724            Expression::ArrayContains(f) => {
725                let array_expr = f.this;
726                let value_expr = f.expression;
727
728                // Build: SELECT 1 FROM UNNEST(array) AS _col WHERE _col = value
729                let unnest = Expression::Unnest(Box::new(UnnestFunc {
730                    this: array_expr,
731                    expressions: Vec::new(),
732                    with_ordinality: false,
733                    alias: None,
734                    offset_alias: None,
735                    inferred_type: None,
736                }));
737                let aliased_unnest = Expression::Alias(Box::new(Alias {
738                    this: unnest,
739                    alias: Identifier::new("_col"),
740                    column_aliases: Vec::new(),
741                    alias_explicit_as: false,
742                    alias_keyword: None,
743                    pre_alias_comments: Vec::new(),
744                    trailing_comments: Vec::new(),
745                    inferred_type: None,
746                }));
747                let col_ref = Expression::boxed_column(Column {
748                    name: Identifier::new("_col"),
749                    table: None,
750                    join_mark: false,
751                    trailing_comments: Vec::new(),
752                    span: None,
753                    inferred_type: None,
754                });
755                let where_clause = Where {
756                    this: Expression::Eq(Box::new(BinaryOp {
757                        left: col_ref,
758                        right: value_expr,
759                        left_comments: Vec::new(),
760                        operator_comments: Vec::new(),
761                        trailing_comments: Vec::new(),
762                        inferred_type: None,
763                    })),
764                };
765                let inner_select = Expression::Select(Box::new(Select {
766                    expressions: vec![Expression::Literal(Box::new(Literal::Number(
767                        "1".to_string(),
768                    )))],
769                    from: Some(From {
770                        expressions: vec![aliased_unnest],
771                    }),
772                    where_clause: Some(where_clause),
773                    ..Default::default()
774                }));
775                Ok(Expression::Exists(Box::new(Exists {
776                    this: inner_select,
777                    not: false,
778                })))
779            }
780
781            // ===== JSON_OBJECT array form → key-value pairs =====
782            // BigQuery "signature 2": JSON_OBJECT(['a', 'b'], [10, NULL]) → JSON_OBJECT('a', 10, 'b', NULL)
783            Expression::JsonObject(mut f) => {
784                if f.pairs.len() == 1 {
785                    // Extract expressions from both Array and ArrayFunc variants
786                    let keys_exprs = match &f.pairs[0].0 {
787                        Expression::Array(arr) => Some(&arr.expressions),
788                        Expression::ArrayFunc(arr) => Some(&arr.expressions),
789                        _ => None,
790                    };
791                    let vals_exprs = match &f.pairs[0].1 {
792                        Expression::Array(arr) => Some(&arr.expressions),
793                        Expression::ArrayFunc(arr) => Some(&arr.expressions),
794                        _ => None,
795                    };
796                    if let (Some(keys), Some(vals)) = (keys_exprs, vals_exprs) {
797                        if keys.len() == vals.len() {
798                            let new_pairs: Vec<(Expression, Expression)> = keys
799                                .iter()
800                                .zip(vals.iter())
801                                .map(|(k, v)| (k.clone(), v.clone()))
802                                .collect();
803                            f.pairs = new_pairs;
804                        }
805                    }
806                }
807                Ok(Expression::JsonObject(f))
808            }
809
810            // ===== MOD function: unwrap unnecessary Paren from first argument =====
811            // BigQuery normalizes MOD((a + 1), b) -> MOD(a + 1, b)
812            Expression::ModFunc(mut f) => {
813                // Unwrap Paren from first argument if present
814                if let Expression::Paren(paren) = f.this {
815                    f.this = paren.this;
816                }
817                Ok(Expression::ModFunc(f))
818            }
819
820            // JSONExtract with variant_extract (Snowflake colon syntax) -> JSON_EXTRACT
821            Expression::JSONExtract(e) if e.variant_extract.is_some() => {
822                let path = match *e.expression {
823                    Expression::Literal(lit) if matches!(lit.as_ref(), Literal::String(_)) => {
824                        let Literal::String(s) = lit.as_ref() else {
825                            unreachable!()
826                        };
827                        let normalized = if s.starts_with('$') {
828                            s.clone()
829                        } else if s.starts_with('[') {
830                            format!("${}", s)
831                        } else {
832                            format!("$.{}", s)
833                        };
834                        Expression::Literal(Box::new(Literal::String(normalized)))
835                    }
836                    other => other,
837                };
838                Ok(Expression::Function(Box::new(Function::new(
839                    "JSON_EXTRACT".to_string(),
840                    vec![*e.this, path],
841                ))))
842            }
843
844            // Generic function transformations
845            Expression::Function(f) => self.transform_function(*f),
846
847            // Generic aggregate function transformations
848            Expression::AggregateFunction(f) => self.transform_aggregate_function(f),
849
850            // MethodCall: Handle SAFE.PARSE_DATE, SAFE.PARSE_DATETIME, SAFE.PARSE_TIMESTAMP
851            // These are parsed as MethodCall(this=SAFE, method=PARSE_DATE, args=[...])
852            Expression::MethodCall(mc) => self.transform_method_call(*mc),
853
854            // CreateFunction: Convert RETURNS TABLE(...) to RETURNS TABLE <...> for BigQuery
855            // and set is_table_function = true for TABLE FUNCTION syntax
856            Expression::CreateFunction(mut cf) => {
857                if let Some(ref mut rtb) = cf.returns_table_body {
858                    if rtb.starts_with("TABLE (") || rtb.starts_with("TABLE(") {
859                        // Convert TABLE (...) to TABLE <...> with BigQuery types
860                        let inner = if rtb.starts_with("TABLE (") {
861                            &rtb["TABLE (".len()..rtb.len() - 1]
862                        } else {
863                            &rtb["TABLE(".len()..rtb.len() - 1]
864                        };
865                        // Convert common types to BigQuery equivalents
866                        let converted = inner
867                            .replace(" INT,", " INT64,")
868                            .replace(" INT)", " INT64)")
869                            .replace(" INTEGER,", " INT64,")
870                            .replace(" INTEGER)", " INT64)")
871                            .replace(" FLOAT,", " FLOAT64,")
872                            .replace(" FLOAT)", " FLOAT64)")
873                            .replace(" BOOLEAN,", " BOOL,")
874                            .replace(" BOOLEAN)", " BOOL)")
875                            .replace(" VARCHAR", " STRING")
876                            .replace(" TEXT", " STRING");
877                        // Handle trailing type (no comma, no paren)
878                        let converted = if converted.ends_with(" INT") {
879                            format!("{}{}", &converted[..converted.len() - 4], " INT64")
880                        } else {
881                            converted
882                        };
883                        *rtb = format!("TABLE <{}>", converted);
884                        cf.is_table_function = true;
885                    }
886                }
887                // Convert string literal body to expression body for BigQuery TABLE FUNCTIONs only
888                if cf.is_table_function {
889                    if let Some(ref body) = cf.body {
890                        if matches!(body, FunctionBody::StringLiteral(_)) {
891                            if let Some(FunctionBody::StringLiteral(sql)) = cf.body.take() {
892                                // Parse the SQL string into an expression
893                                if let Ok(parsed) = crate::parser::Parser::parse_sql(&sql) {
894                                    if let Some(stmt) = parsed.into_iter().next() {
895                                        cf.body = Some(FunctionBody::Expression(stmt));
896                                    } else {
897                                        cf.body = Some(FunctionBody::StringLiteral(sql));
898                                    }
899                                } else {
900                                    cf.body = Some(FunctionBody::StringLiteral(sql));
901                                }
902                            }
903                        }
904                    }
905                }
906                Ok(Expression::CreateFunction(cf))
907            }
908
909            // Pass through everything else
910            _ => Ok(expr),
911        }
912    }
913}
914
915#[cfg(feature = "transpile")]
916impl BigQueryDialect {
917    /// Transform data types according to BigQuery TYPE_MAPPING
918    fn transform_data_type(&self, dt: crate::expressions::DataType) -> Result<Expression> {
919        use crate::expressions::DataType;
920        let transformed = match dt {
921            // BIGINT -> INT64
922            DataType::BigInt { .. } => DataType::Custom {
923                name: "INT64".to_string(),
924            },
925            // INT -> INT64
926            DataType::Int { .. } => DataType::Custom {
927                name: "INT64".to_string(),
928            },
929            // SMALLINT -> INT64
930            DataType::SmallInt { .. } => DataType::Custom {
931                name: "INT64".to_string(),
932            },
933            // TINYINT -> INT64
934            DataType::TinyInt { .. } => DataType::Custom {
935                name: "INT64".to_string(),
936            },
937            // FLOAT -> FLOAT64
938            DataType::Float { .. } => DataType::Custom {
939                name: "FLOAT64".to_string(),
940            },
941            // DOUBLE -> FLOAT64
942            DataType::Double { .. } => DataType::Custom {
943                name: "FLOAT64".to_string(),
944            },
945            // BOOLEAN -> BOOL
946            DataType::Boolean => DataType::Custom {
947                name: "BOOL".to_string(),
948            },
949            // CHAR -> STRING
950            DataType::Char { .. } => DataType::Custom {
951                name: "STRING".to_string(),
952            },
953            // VARCHAR -> STRING
954            DataType::VarChar { .. } => DataType::Custom {
955                name: "STRING".to_string(),
956            },
957            // TEXT -> STRING
958            DataType::Text => DataType::Custom {
959                name: "STRING".to_string(),
960            },
961            // STRING(n) -> STRING (BigQuery doesn't support length for STRING)
962            DataType::String { .. } => DataType::Custom {
963                name: "STRING".to_string(),
964            },
965            // BINARY -> BYTES
966            DataType::Binary { .. } => DataType::Custom {
967                name: "BYTES".to_string(),
968            },
969            // VARBINARY -> BYTES
970            DataType::VarBinary { .. } => DataType::Custom {
971                name: "BYTES".to_string(),
972            },
973            // BLOB -> BYTES
974            DataType::Blob => DataType::Custom {
975                name: "BYTES".to_string(),
976            },
977            // DECIMAL -> NUMERIC (BigQuery strips precision in CAST context)
978            DataType::Decimal { .. } => DataType::Custom {
979                name: "NUMERIC".to_string(),
980            },
981            // For BigQuery identity: preserve TIMESTAMP/DATETIME as Custom types
982            // This avoids the issue where parsed TIMESTAMP (timezone: false) would
983            // be converted to DATETIME by the generator
984            DataType::Timestamp {
985                timezone: false, ..
986            } => DataType::Custom {
987                name: "TIMESTAMP".to_string(),
988            },
989            DataType::Timestamp { timezone: true, .. } => DataType::Custom {
990                name: "TIMESTAMP".to_string(),
991            },
992            // UUID -> STRING (BigQuery doesn't have native UUID type)
993            DataType::Uuid => DataType::Custom {
994                name: "STRING".to_string(),
995            },
996            // RECORD -> STRUCT in BigQuery
997            DataType::Custom { ref name } if name.eq_ignore_ascii_case("RECORD") => {
998                DataType::Custom {
999                    name: "STRUCT".to_string(),
1000                }
1001            }
1002            // TIMESTAMPTZ (custom) -> TIMESTAMP
1003            DataType::Custom { ref name } if name.eq_ignore_ascii_case("TIMESTAMPTZ") => {
1004                DataType::Custom {
1005                    name: "TIMESTAMP".to_string(),
1006                }
1007            }
1008            // BYTEINT (custom) -> INT64
1009            DataType::Custom { ref name } if name.eq_ignore_ascii_case("BYTEINT") => {
1010                DataType::Custom {
1011                    name: "INT64".to_string(),
1012                }
1013            }
1014            // Keep all other types as-is
1015            other => other,
1016        };
1017        Ok(Expression::DataType(transformed))
1018    }
1019
1020    fn transform_function(&self, f: Function) -> Result<Expression> {
1021        let name_upper = f.name.to_uppercase();
1022        match name_upper.as_str() {
1023            // IFNULL -> COALESCE (both work in BigQuery)
1024            "IFNULL" if f.args.len() == 2 => Ok(Expression::Coalesce(Box::new(VarArgFunc {
1025                original_name: None,
1026                expressions: f.args,
1027                inferred_type: None,
1028            }))),
1029
1030            // NVL -> COALESCE
1031            "NVL" if f.args.len() == 2 => Ok(Expression::Coalesce(Box::new(VarArgFunc {
1032                original_name: None,
1033                expressions: f.args,
1034                inferred_type: None,
1035            }))),
1036
1037            // ISNULL -> COALESCE
1038            "ISNULL" if f.args.len() == 2 => Ok(Expression::Coalesce(Box::new(VarArgFunc {
1039                original_name: None,
1040                expressions: f.args,
1041                inferred_type: None,
1042            }))),
1043
1044            // GROUP_CONCAT -> STRING_AGG in BigQuery
1045            "GROUP_CONCAT" if !f.args.is_empty() => Ok(Expression::Function(Box::new(
1046                Function::new("STRING_AGG".to_string(), f.args),
1047            ))),
1048
1049            // SUBSTR -> SUBSTRING (both work)
1050            "SUBSTR" => Ok(Expression::Function(Box::new(Function::new(
1051                "SUBSTRING".to_string(),
1052                f.args,
1053            )))),
1054
1055            // RANDOM -> RAND
1056            "RANDOM" => Ok(Expression::Rand(Box::new(crate::expressions::Rand {
1057                seed: None,
1058                lower: None,
1059                upper: None,
1060            }))),
1061
1062            // CURRENT_DATE -> CURRENT_DATE() in BigQuery
1063            // Keep as Function when it has args (e.g., CURRENT_DATE('UTC'))
1064            "CURRENT_DATE" if f.args.is_empty() => {
1065                Ok(Expression::CurrentDate(crate::expressions::CurrentDate))
1066            }
1067            "CURRENT_DATE" => Ok(Expression::Function(Box::new(Function {
1068                name: "CURRENT_DATE".to_string(),
1069                args: f.args,
1070                distinct: false,
1071                trailing_comments: Vec::new(),
1072                use_bracket_syntax: false,
1073                no_parens: false,
1074                quoted: false,
1075                span: None,
1076                inferred_type: None,
1077            }))),
1078
1079            // NOW -> CURRENT_TIMESTAMP in BigQuery
1080            "NOW" => Ok(Expression::CurrentTimestamp(
1081                crate::expressions::CurrentTimestamp {
1082                    precision: None,
1083                    sysdate: false,
1084                },
1085            )),
1086
1087            // TO_DATE -> PARSE_DATE in BigQuery
1088            "TO_DATE" => Ok(Expression::Function(Box::new(Function::new(
1089                "PARSE_DATE".to_string(),
1090                f.args,
1091            )))),
1092
1093            // TO_TIMESTAMP -> PARSE_TIMESTAMP in BigQuery
1094            "TO_TIMESTAMP" => Ok(Expression::Function(Box::new(Function::new(
1095                "PARSE_TIMESTAMP".to_string(),
1096                f.args,
1097            )))),
1098
1099            // TO_TIME -> TIME in BigQuery
1100            "TO_TIME" if f.args.len() == 1 => Ok(Expression::Function(Box::new(Function::new(
1101                "TIME".to_string(),
1102                f.args,
1103            )))),
1104
1105            // DATE_FORMAT -> FORMAT_DATE in BigQuery (argument order may differ)
1106            "DATE_FORMAT" => Ok(Expression::Function(Box::new(Function::new(
1107                "FORMAT_DATE".to_string(),
1108                f.args,
1109            )))),
1110
1111            // POSITION -> STRPOS in BigQuery
1112            // BigQuery uses STRPOS(string, substring)
1113            "POSITION" if f.args.len() == 2 => {
1114                let mut args = f.args;
1115                // Swap arguments: POSITION(sub IN str) -> STRPOS(str, sub)
1116                let first = args.remove(0);
1117                let second = args.remove(0);
1118                Ok(Expression::Function(Box::new(Function::new(
1119                    "STRPOS".to_string(),
1120                    vec![second, first],
1121                ))))
1122            }
1123
1124            // LEN -> LENGTH
1125            "LEN" if f.args.len() == 1 => Ok(Expression::Length(Box::new(UnaryFunc::new(
1126                f.args.into_iter().next().unwrap(),
1127            )))),
1128
1129            // CEILING -> CEIL (both work)
1130            "CEILING" if f.args.len() == 1 => Ok(Expression::Ceil(Box::new(CeilFunc {
1131                this: f.args.into_iter().next().unwrap(),
1132                decimals: None,
1133                to: None,
1134            }))),
1135
1136            // GETDATE -> CURRENT_TIMESTAMP
1137            "GETDATE" => Ok(Expression::CurrentTimestamp(
1138                crate::expressions::CurrentTimestamp {
1139                    precision: None,
1140                    sysdate: false,
1141                },
1142            )),
1143
1144            // ARRAY_LENGTH -> ARRAY_LENGTH (native)
1145            // CARDINALITY -> ARRAY_LENGTH
1146            "CARDINALITY" if f.args.len() == 1 => Ok(Expression::ArrayLength(Box::new(
1147                UnaryFunc::new(f.args.into_iter().next().unwrap()),
1148            ))),
1149
1150            // UNNEST is native to BigQuery
1151
1152            // GENERATE_SERIES -> GENERATE_ARRAY in BigQuery
1153            "GENERATE_SERIES" => Ok(Expression::Function(Box::new(Function::new(
1154                "GENERATE_ARRAY".to_string(),
1155                f.args,
1156            )))),
1157
1158            // APPROX_COUNT_DISTINCT -> APPROX_COUNT_DISTINCT (native)
1159            // APPROX_DISTINCT -> APPROX_COUNT_DISTINCT
1160            "APPROX_DISTINCT" => Ok(Expression::Function(Box::new(Function::new(
1161                "APPROX_COUNT_DISTINCT".to_string(),
1162                f.args,
1163            )))),
1164
1165            // COUNT_IF -> COUNTIF in BigQuery
1166            "COUNT_IF" => Ok(Expression::Function(Box::new(Function::new(
1167                "COUNTIF".to_string(),
1168                f.args,
1169            )))),
1170
1171            // SHA1 -> SHA1 (native), SHA -> SHA1
1172            "SHA" => Ok(Expression::Function(Box::new(Function::new(
1173                "SHA1".to_string(),
1174                f.args,
1175            )))),
1176
1177            // SHA256/SHA2 -> SHA256
1178            "SHA2" => Ok(Expression::Function(Box::new(Function::new(
1179                "SHA256".to_string(),
1180                f.args,
1181            )))),
1182
1183            // MD5 in BigQuery returns bytes, often combined with TO_HEX
1184            // TO_HEX(MD5(x)) pattern
1185            "MD5" => Ok(Expression::Function(Box::new(Function::new(
1186                "MD5".to_string(),
1187                f.args,
1188            )))),
1189
1190            // VARIANCE/VAR_SAMP -> VAR_SAMP (native)
1191            // VAR_POP -> VAR_POP (native)
1192
1193            // DATEADD(unit, amount, date) → DATE_ADD(date, INTERVAL amount unit) for BigQuery
1194            "DATEADD" if f.args.len() == 3 => {
1195                let mut args = f.args;
1196                let unit_expr = args.remove(0);
1197                let amount = args.remove(0);
1198                let date = args.remove(0);
1199                // Convert unit identifier to IntervalUnit
1200                let unit_name = match &unit_expr {
1201                    Expression::Identifier(id) => id.name.to_uppercase(),
1202                    Expression::Var(v) => v.this.to_uppercase(),
1203                    Expression::Column(col) if col.table.is_none() => col.name.name.to_uppercase(),
1204                    _ => "DAY".to_string(),
1205                };
1206                let unit = match unit_name.as_str() {
1207                    "YEAR" | "YEARS" | "YY" | "YYYY" => crate::expressions::IntervalUnit::Year,
1208                    "QUARTER" | "QUARTERS" | "QQ" | "Q" => {
1209                        crate::expressions::IntervalUnit::Quarter
1210                    }
1211                    "MONTH" | "MONTHS" | "MM" | "M" => crate::expressions::IntervalUnit::Month,
1212                    "WEEK" | "WEEKS" | "WK" | "WW" => crate::expressions::IntervalUnit::Week,
1213                    "DAY" | "DAYS" | "DD" | "D" | "DAYOFMONTH" => {
1214                        crate::expressions::IntervalUnit::Day
1215                    }
1216                    "HOUR" | "HOURS" | "HH" => crate::expressions::IntervalUnit::Hour,
1217                    "MINUTE" | "MINUTES" | "MI" | "N" => crate::expressions::IntervalUnit::Minute,
1218                    "SECOND" | "SECONDS" | "SS" | "S" => crate::expressions::IntervalUnit::Second,
1219                    "MILLISECOND" | "MILLISECONDS" | "MS" => {
1220                        crate::expressions::IntervalUnit::Millisecond
1221                    }
1222                    "MICROSECOND" | "MICROSECONDS" | "US" => {
1223                        crate::expressions::IntervalUnit::Microsecond
1224                    }
1225                    _ => crate::expressions::IntervalUnit::Day,
1226                };
1227                Ok(Expression::DateAdd(Box::new(
1228                    crate::expressions::DateAddFunc {
1229                        this: date,
1230                        interval: amount,
1231                        unit,
1232                    },
1233                )))
1234            }
1235            "DATE_ADD" => Ok(Expression::Function(Box::new(Function::new(
1236                "DATE_ADD".to_string(),
1237                f.args,
1238            )))),
1239
1240            // DATE_DIFF in BigQuery (native)
1241            "DATEDIFF" => Ok(Expression::Function(Box::new(Function::new(
1242                "DATE_DIFF".to_string(),
1243                f.args,
1244            )))),
1245
1246            // TIMESTAMP_DIFF in BigQuery
1247            "TIMESTAMPDIFF" => Ok(Expression::Function(Box::new(Function::new(
1248                "TIMESTAMP_DIFF".to_string(),
1249                f.args,
1250            )))),
1251
1252            // TIME -> TIME (native)
1253            // DATETIME -> DATETIME (native)
1254
1255            // SAFE_DIVIDE -> SAFE_DIVIDE (native)
1256
1257            // NEWID/UUID -> GENERATE_UUID
1258            "NEWID" | "UUID" => Ok(Expression::Function(Box::new(Function::new(
1259                "GENERATE_UUID".to_string(),
1260                vec![],
1261            )))),
1262
1263            // LEVENSHTEIN -> EDIT_DISTANCE (BigQuery naming)
1264            "LEVENSHTEIN" => Ok(Expression::Function(Box::new(Function::new(
1265                "EDIT_DISTANCE".to_string(),
1266                f.args,
1267            )))),
1268
1269            // UNIX_TIMESTAMP -> UNIX_SECONDS
1270            "UNIX_TIMESTAMP" => Ok(Expression::Function(Box::new(Function::new(
1271                "UNIX_SECONDS".to_string(),
1272                f.args,
1273            )))),
1274
1275            // FROM_UNIXTIME -> TIMESTAMP_SECONDS
1276            "FROM_UNIXTIME" => Ok(Expression::Function(Box::new(Function::new(
1277                "TIMESTAMP_SECONDS".to_string(),
1278                f.args,
1279            )))),
1280
1281            // CHAR_LENGTH / CHARACTER_LENGTH -> LENGTH
1282            "CHAR_LENGTH" | "CHARACTER_LENGTH" => Ok(Expression::Function(Box::new(
1283                Function::new("LENGTH".to_string(), f.args),
1284            ))),
1285
1286            // OCTET_LENGTH -> BYTE_LENGTH in BigQuery
1287            "OCTET_LENGTH" => Ok(Expression::Function(Box::new(Function::new(
1288                "BYTE_LENGTH".to_string(),
1289                f.args,
1290            )))),
1291
1292            // JSON_EXTRACT_STRING_ARRAY -> JSON_VALUE_ARRAY in BigQuery
1293            "JSON_EXTRACT_STRING_ARRAY" => Ok(Expression::Function(Box::new(Function::new(
1294                "JSON_VALUE_ARRAY".to_string(),
1295                f.args,
1296            )))),
1297
1298            // INSTR is native to BigQuery
1299
1300            // SPLIT: BigQuery defaults to comma separator when none provided
1301            // SPLIT(foo) -> SPLIT(foo, ',')
1302            "SPLIT" if f.args.len() == 1 => {
1303                let mut args = f.args;
1304                args.push(Expression::Literal(Box::new(Literal::String(
1305                    ",".to_string(),
1306                ))));
1307                Ok(Expression::Split(Box::new(SplitFunc {
1308                    this: args.remove(0),
1309                    delimiter: args.remove(0),
1310                })))
1311            }
1312
1313            // SPLIT with two args - convert to Split expression
1314            "SPLIT" if f.args.len() == 2 => {
1315                let mut args = f.args;
1316                Ok(Expression::Split(Box::new(SplitFunc {
1317                    this: args.remove(0),
1318                    delimiter: args.remove(0),
1319                })))
1320            }
1321
1322            // REGEXP_SUBSTR -> REGEXP_EXTRACT in BigQuery (strip extra Snowflake args)
1323            "REGEXP_SUBSTR" if f.args.len() >= 2 => {
1324                // BigQuery REGEXP_EXTRACT supports (subject, pattern, pos, occ) max 4 args
1325                let args = if f.args.len() > 4 {
1326                    f.args[..4].to_vec()
1327                } else {
1328                    f.args
1329                };
1330                Ok(Expression::Function(Box::new(Function::new(
1331                    "REGEXP_EXTRACT".to_string(),
1332                    args,
1333                ))))
1334            }
1335            "REGEXP_SUBSTR" => Ok(Expression::Function(Box::new(Function::new(
1336                "REGEXP_EXTRACT".to_string(),
1337                f.args,
1338            )))),
1339
1340            // REGEXP_REPLACE - strip extra Snowflake-specific args
1341            "REGEXP_REPLACE" if f.args.len() > 3 => {
1342                let args = f.args[..3].to_vec();
1343                Ok(Expression::Function(Box::new(Function::new(
1344                    "REGEXP_REPLACE".to_string(),
1345                    args,
1346                ))))
1347            }
1348
1349            // OBJECT_CONSTRUCT_KEEP_NULL -> JSON_OBJECT
1350            "OBJECT_CONSTRUCT_KEEP_NULL" => Ok(Expression::Function(Box::new(Function::new(
1351                "JSON_OBJECT".to_string(),
1352                f.args,
1353            )))),
1354
1355            // EDITDISTANCE -> EDIT_DISTANCE with named max_distance parameter
1356            "EDITDISTANCE" if f.args.len() == 3 => {
1357                let col1 = f.args[0].clone();
1358                let col2 = f.args[1].clone();
1359                let max_dist = f.args[2].clone();
1360                Ok(Expression::Function(Box::new(Function::new(
1361                    "EDIT_DISTANCE".to_string(),
1362                    vec![
1363                        col1,
1364                        col2,
1365                        Expression::NamedArgument(Box::new(crate::expressions::NamedArgument {
1366                            name: crate::expressions::Identifier::new("max_distance".to_string()),
1367                            value: max_dist,
1368                            separator: crate::expressions::NamedArgSeparator::DArrow,
1369                        })),
1370                    ],
1371                ))))
1372            }
1373            "EDITDISTANCE" if f.args.len() == 2 => Ok(Expression::Function(Box::new(
1374                Function::new("EDIT_DISTANCE".to_string(), f.args),
1375            ))),
1376
1377            // HEX_DECODE_BINARY -> FROM_HEX
1378            "HEX_DECODE_BINARY" => Ok(Expression::Function(Box::new(Function::new(
1379                "FROM_HEX".to_string(),
1380                f.args,
1381            )))),
1382
1383            // BigQuery format string normalization for PARSE_DATE/DATETIME/TIMESTAMP functions
1384            // %Y-%m-%d -> %F and %H:%M:%S -> %T
1385            "PARSE_DATE"
1386            | "PARSE_DATETIME"
1387            | "PARSE_TIMESTAMP"
1388            | "SAFE.PARSE_DATE"
1389            | "SAFE.PARSE_DATETIME"
1390            | "SAFE.PARSE_TIMESTAMP" => {
1391                let args = self.normalize_time_format_args(f.args);
1392                Ok(Expression::Function(Box::new(Function {
1393                    name: f.name,
1394                    args,
1395                    distinct: f.distinct,
1396                    no_parens: f.no_parens,
1397                    trailing_comments: f.trailing_comments,
1398                    quoted: f.quoted,
1399                    use_bracket_syntax: f.use_bracket_syntax,
1400                    span: None,
1401                    inferred_type: None,
1402                })))
1403            }
1404
1405            // GET_PATH(obj, path) -> JSON_EXTRACT(obj, json_path) in BigQuery
1406            "GET_PATH" if f.args.len() == 2 => {
1407                let mut args = f.args;
1408                let this = args.remove(0);
1409                let path = args.remove(0);
1410                let json_path = match &path {
1411                    Expression::Literal(lit) if matches!(lit.as_ref(), Literal::String(_)) => {
1412                        let Literal::String(s) = lit.as_ref() else {
1413                            unreachable!()
1414                        };
1415                        let normalized = if s.starts_with('$') {
1416                            s.clone()
1417                        } else if s.starts_with('[') {
1418                            format!("${}", s)
1419                        } else {
1420                            format!("$.{}", s)
1421                        };
1422                        Expression::Literal(Box::new(Literal::String(normalized)))
1423                    }
1424                    _ => path,
1425                };
1426                Ok(Expression::JsonExtract(Box::new(JsonExtractFunc {
1427                    this,
1428                    path: json_path,
1429                    returning: None,
1430                    arrow_syntax: false,
1431                    hash_arrow_syntax: false,
1432                    wrapper_option: None,
1433                    quotes_option: None,
1434                    on_scalar_string: false,
1435                    on_error: None,
1436                })))
1437            }
1438
1439            // Pass through everything else
1440            _ => Ok(Expression::Function(Box::new(f))),
1441        }
1442    }
1443
1444    fn transform_aggregate_function(
1445        &self,
1446        f: Box<crate::expressions::AggregateFunction>,
1447    ) -> Result<Expression> {
1448        let name_upper = f.name.to_uppercase();
1449        match name_upper.as_str() {
1450            // GROUP_CONCAT -> STRING_AGG
1451            "GROUP_CONCAT" if !f.args.is_empty() => Ok(Expression::Function(Box::new(
1452                Function::new("STRING_AGG".to_string(), f.args),
1453            ))),
1454
1455            // Pass through everything else
1456            _ => Ok(Expression::AggregateFunction(f)),
1457        }
1458    }
1459
1460    /// Transform MethodCall expressions
1461    /// Handles SAFE.PARSE_DATE, SAFE.PARSE_DATETIME, SAFE.PARSE_TIMESTAMP format normalization
1462    fn transform_method_call(&self, mc: crate::expressions::MethodCall) -> Result<Expression> {
1463        use crate::expressions::MethodCall;
1464
1465        // Check if this is SAFE.PARSE_DATE/DATETIME/TIMESTAMP
1466        if let Expression::Column(ref col) = mc.this {
1467            if col.name.name.eq_ignore_ascii_case("SAFE") {
1468                let method_upper = mc.method.name.to_uppercase();
1469                if method_upper == "PARSE_DATE"
1470                    || method_upper == "PARSE_DATETIME"
1471                    || method_upper == "PARSE_TIMESTAMP"
1472                {
1473                    // Normalize the format string in the first argument
1474                    let args = self.normalize_time_format_args(mc.args);
1475                    return Ok(Expression::MethodCall(Box::new(MethodCall {
1476                        this: mc.this,
1477                        method: mc.method,
1478                        args,
1479                    })));
1480                }
1481            }
1482        }
1483
1484        // Pass through all other method calls
1485        Ok(Expression::MethodCall(Box::new(mc)))
1486    }
1487
1488    /// Normalize time format strings in function arguments
1489    /// BigQuery normalizes: %Y-%m-%d -> %F, %H:%M:%S -> %T
1490    fn normalize_time_format_args(&self, args: Vec<Expression>) -> Vec<Expression> {
1491        args.into_iter()
1492            .enumerate()
1493            .map(|(i, arg)| {
1494                // Only transform the first argument (the format string)
1495                if i == 0 {
1496                    if let Expression::Literal(ref lit) = arg {
1497                        if let Literal::String(s) = lit.as_ref() {
1498                            let normalized = self.normalize_time_format(&s);
1499                            return Expression::Literal(Box::new(Literal::String(normalized)));
1500                        }
1501                    }
1502                }
1503                arg
1504            })
1505            .collect()
1506    }
1507
1508    /// Normalize a time format string according to BigQuery conventions
1509    /// %Y-%m-%d -> %F (ISO date)
1510    /// %H:%M:%S -> %T (time)
1511    fn normalize_time_format(&self, format: &str) -> String {
1512        format.replace("%Y-%m-%d", "%F").replace("%H:%M:%S", "%T")
1513    }
1514
1515    /// Convert BigQuery CAST FORMAT elements to strftime equivalents,
1516    /// then normalize BigQuery shorthand forms (%Y-%m-%d -> %F, %H:%M:%S -> %T)
1517    fn bq_cast_format_to_strftime(format_expr: &Expression) -> Expression {
1518        use crate::expressions::Literal;
1519        if let Expression::Literal(lit) = format_expr {
1520            if let Literal::String(s) = lit.as_ref() {
1521                let result = s
1522                    .replace("YYYYMMDD", "%Y%m%d")
1523                    .replace("YYYY", "%Y")
1524                    .replace("YY", "%y")
1525                    .replace("MONTH", "%B")
1526                    .replace("MON", "%b")
1527                    .replace("MM", "%m")
1528                    .replace("DD", "%d")
1529                    .replace("HH24", "%H")
1530                    .replace("HH12", "%I")
1531                    .replace("HH", "%I")
1532                    .replace("MI", "%M")
1533                    .replace("SSTZH", "%S%z")
1534                    .replace("SS", "%S")
1535                    .replace("TZH", "%z");
1536                // Normalize: %Y-%m-%d -> %F, %H:%M:%S -> %T
1537                let normalized = result.replace("%Y-%m-%d", "%F").replace("%H:%M:%S", "%T");
1538                return Expression::Literal(Box::new(Literal::String(normalized)));
1539            }
1540        }
1541        format_expr.clone()
1542    }
1543}
1544
1545#[cfg(test)]
1546mod tests {
1547    use super::*;
1548    use crate::dialects::Dialect;
1549    use crate::parse_one;
1550
1551    fn transpile_to_bigquery(sql: &str) -> String {
1552        let dialect = Dialect::get(DialectType::Generic);
1553        let result = dialect
1554            .transpile(sql, DialectType::BigQuery)
1555            .expect("Transpile failed");
1556        result[0].clone()
1557    }
1558
1559    #[test]
1560    fn test_ifnull_identity() {
1561        // Generic -> BigQuery: IFNULL is normalized to COALESCE (matching sqlglot behavior)
1562        let result = transpile_to_bigquery("SELECT IFNULL(a, b)");
1563        assert!(
1564            result.contains("COALESCE"),
1565            "Expected COALESCE, got: {}",
1566            result
1567        );
1568    }
1569
1570    #[test]
1571    fn test_nvl_to_ifnull() {
1572        // NVL is converted to IFNULL in BigQuery
1573        let result = transpile_to_bigquery("SELECT NVL(a, b)");
1574        assert!(
1575            result.contains("IFNULL"),
1576            "Expected IFNULL, got: {}",
1577            result
1578        );
1579    }
1580
1581    #[test]
1582    fn test_try_cast_to_safe_cast() {
1583        let result = transpile_to_bigquery("SELECT TRY_CAST(a AS INT)");
1584        assert!(
1585            result.contains("SAFE_CAST"),
1586            "Expected SAFE_CAST, got: {}",
1587            result
1588        );
1589    }
1590
1591    #[test]
1592    fn test_random_to_rand() {
1593        let result = transpile_to_bigquery("SELECT RANDOM()");
1594        assert!(result.contains("RAND"), "Expected RAND, got: {}", result);
1595    }
1596
1597    #[test]
1598    fn test_basic_select() {
1599        let result = transpile_to_bigquery("SELECT a, b FROM users WHERE id = 1");
1600        assert!(result.contains("SELECT"));
1601        assert!(result.contains("FROM users"));
1602    }
1603
1604    #[test]
1605    fn test_group_concat_to_string_agg() {
1606        let result = transpile_to_bigquery("SELECT GROUP_CONCAT(name)");
1607        assert!(
1608            result.contains("STRING_AGG"),
1609            "Expected STRING_AGG, got: {}",
1610            result
1611        );
1612    }
1613
1614    #[test]
1615    fn test_generate_series_to_generate_array() {
1616        let result = transpile_to_bigquery("SELECT GENERATE_SERIES(1, 10)");
1617        assert!(
1618            result.contains("GENERATE_ARRAY"),
1619            "Expected GENERATE_ARRAY, got: {}",
1620            result
1621        );
1622    }
1623
1624    #[test]
1625    fn test_backtick_identifiers() {
1626        // BigQuery uses backticks for identifiers
1627        let dialect = BigQueryDialect;
1628        let config = dialect.generator_config();
1629        assert_eq!(config.identifier_quote, '`');
1630    }
1631
1632    fn bigquery_identity(sql: &str, expected: &str) {
1633        let dialect = Dialect::get(DialectType::BigQuery);
1634        let ast = dialect.parse(sql).expect("Parse failed");
1635        let transformed = dialect.transform(ast[0].clone()).expect("Transform failed");
1636        let result = dialect.generate(&transformed).expect("Generate failed");
1637        assert_eq!(result, expected, "SQL: {}", sql);
1638    }
1639
1640    #[test]
1641    fn test_safe_namespace_parses_as_function() {
1642        let expr = parse_one(
1643            "SELECT SAFE.PARSE_JSON(data) AS json_data FROM t",
1644            DialectType::BigQuery,
1645        )
1646        .expect("parse");
1647
1648        let Expression::Select(select) = expr else {
1649            panic!("expected SELECT");
1650        };
1651        let Expression::Alias(alias) = &select.expressions[0] else {
1652            panic!("expected alias");
1653        };
1654        let Expression::Function(function) = &alias.this else {
1655            panic!("expected SAFE namespace call to parse as Function");
1656        };
1657
1658        assert_eq!(function.name, "SAFE.PARSE_JSON");
1659        assert_eq!(function.args.len(), 1);
1660    }
1661
1662    #[test]
1663    fn test_safe_namespace_identity() {
1664        bigquery_identity("SAFE.PARSE_JSON(data)", "SAFE.PARSE_JSON(data)");
1665        bigquery_identity(
1666            "SAFE.PARSE_DATE('%Y-%m-%d', date_col)",
1667            "SAFE.PARSE_DATE('%F', date_col)",
1668        );
1669        bigquery_identity("SAFE.DIVIDE(a, b)", "SAFE.DIVIDE(a, b)");
1670    }
1671
1672    #[test]
1673    fn test_cast_char_to_string() {
1674        bigquery_identity("CAST(x AS CHAR)", "CAST(x AS STRING)");
1675    }
1676
1677    #[test]
1678    fn test_cast_varchar_to_string() {
1679        bigquery_identity("CAST(x AS VARCHAR)", "CAST(x AS STRING)");
1680    }
1681
1682    #[test]
1683    fn test_cast_nchar_to_string() {
1684        bigquery_identity("CAST(x AS NCHAR)", "CAST(x AS STRING)");
1685    }
1686
1687    #[test]
1688    fn test_cast_nvarchar_to_string() {
1689        bigquery_identity("CAST(x AS NVARCHAR)", "CAST(x AS STRING)");
1690    }
1691
1692    #[test]
1693    fn test_cast_timestamptz_to_timestamp() {
1694        bigquery_identity("CAST(x AS TIMESTAMPTZ)", "CAST(x AS TIMESTAMP)");
1695    }
1696
1697    #[test]
1698    fn test_cast_record_to_struct() {
1699        bigquery_identity("CAST(x AS RECORD)", "CAST(x AS STRUCT)");
1700    }
1701
1702    #[test]
1703    fn test_json_literal_to_parse_json() {
1704        // JSON 'string' literal syntax should be converted to PARSE_JSON()
1705        bigquery_identity(
1706            "SELECT JSON '\"foo\"' AS json_data",
1707            "SELECT PARSE_JSON('\"foo\"') AS json_data",
1708        );
1709    }
1710
1711    #[test]
1712    fn test_grant_as_alias_not_quoted() {
1713        // GRANT is not a reserved keyword in BigQuery, should not be backtick-quoted
1714        bigquery_identity(
1715            "SELECT GRANT FROM (SELECT 'input' AS GRANT)",
1716            "SELECT GRANT FROM (SELECT 'input' AS GRANT)",
1717        );
1718    }
1719
1720    #[test]
1721    fn test_timestamp_literal_to_cast() {
1722        // TIMESTAMP 'value' literal should be converted to CAST('value' AS TIMESTAMP)
1723        bigquery_identity(
1724            "CREATE VIEW `d.v` OPTIONS (expiration_timestamp=TIMESTAMP '2020-01-02T04:05:06.007Z') AS SELECT 1 AS c",
1725            "CREATE VIEW `d.v` OPTIONS (expiration_timestamp=CAST('2020-01-02T04:05:06.007Z' AS TIMESTAMP)) AS SELECT 1 AS c"
1726        );
1727    }
1728
1729    #[test]
1730    fn test_date_literal_to_cast_in_extract() {
1731        // Issue 1: DATE literal should become CAST syntax in BigQuery
1732        bigquery_identity(
1733            "EXTRACT(WEEK(THURSDAY) FROM DATE '2013-12-25')",
1734            "EXTRACT(WEEK(THURSDAY) FROM CAST('2013-12-25' AS DATE))",
1735        );
1736    }
1737
1738    #[test]
1739    fn test_json_object_with_json_literals() {
1740        // Issue 2: JSON literals in JSON_OBJECT should use PARSE_JSON, not CAST AS JSON
1741        bigquery_identity(
1742            "SELECT JSON_OBJECT('a', JSON '10') AS json_data",
1743            "SELECT JSON_OBJECT('a', PARSE_JSON('10')) AS json_data",
1744        );
1745    }
1746
1747    // NOTE: MOD paren unwrapping is tested in the conformance tests (sqlglot_dialect_identity).
1748    // The unit test version was removed due to stack overflow in debug builds (deep recursion).
1749    // Test case: MOD((a + 1), b) -> MOD(a + 1, b)
1750
1751    #[test]
1752    fn test_safe_parse_date_format_normalization() {
1753        // SAFE.PARSE_DATE format string normalization: %Y-%m-%d -> %F
1754        bigquery_identity(
1755            "SAFE.PARSE_DATE('%Y-%m-%d', '2024-01-15')",
1756            "SAFE.PARSE_DATE('%F', '2024-01-15')",
1757        );
1758    }
1759
1760    #[test]
1761    fn test_safe_parse_datetime_format_normalization() {
1762        // SAFE.PARSE_DATETIME format string normalization: %Y-%m-%d %H:%M:%S -> %F %T
1763        bigquery_identity(
1764            "SAFE.PARSE_DATETIME('%Y-%m-%d %H:%M:%S', '2024-01-15 10:30:00')",
1765            "SAFE.PARSE_DATETIME('%F %T', '2024-01-15 10:30:00')",
1766        );
1767    }
1768
1769    #[test]
1770    fn test_safe_parse_timestamp_format_normalization() {
1771        // SAFE.PARSE_TIMESTAMP format string normalization: %Y-%m-%d %H:%M:%S -> %F %T
1772        bigquery_identity(
1773            "SAFE.PARSE_TIMESTAMP('%Y-%m-%d %H:%M:%S', '2024-01-15 10:30:00')",
1774            "SAFE.PARSE_TIMESTAMP('%F %T', '2024-01-15 10:30:00')",
1775        );
1776    }
1777
1778    #[test]
1779    fn test_datetime_literal_to_cast() {
1780        // DATETIME 'value' literal should be converted to CAST('value' AS DATETIME)
1781        bigquery_identity(
1782            "LAST_DAY(DATETIME '2008-11-10 15:30:00', WEEK(SUNDAY))",
1783            "LAST_DAY(CAST('2008-11-10 15:30:00' AS DATETIME), WEEK)",
1784        );
1785    }
1786
1787    #[test]
1788    fn test_last_day_week_modifier_stripped() {
1789        // WEEK(SUNDAY) should become WEEK in BigQuery LAST_DAY function
1790        bigquery_identity("LAST_DAY(col, WEEK(MONDAY))", "LAST_DAY(col, WEEK)");
1791    }
1792
1793    #[test]
1794    fn test_hash_line_comment_parses() {
1795        // Regression test for issue #38:
1796        // BigQuery should accept # as a single-line comment.
1797        let result = parse_one("SELECT 1 as a #hello world", DialectType::BigQuery);
1798        assert!(result.is_ok(), "Expected parse to succeed, got: {result:?}");
1799    }
1800}