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

polyglot_sql/dialects/
bigquery.rs
