polyglot_sql/dialects/
tsql.rs

1//! T-SQL (SQL Server) Dialect
2//!
3//! SQL Server-specific transformations based on sqlglot patterns.
4//! Key differences:
5//! - TOP instead of LIMIT
6//! - ISNULL instead of COALESCE (though COALESCE also works)
7//! - Square brackets for identifiers
8//! - + for string concatenation
9//! - CONVERT vs CAST
10//! - CROSS APPLY / OUTER APPLY for lateral joins
11//! - Different date functions (GETDATE, DATEADD, DATEDIFF, DATENAME)
12
13use super::{DialectImpl, DialectType};
14use crate::error::Result;
15use crate::expressions::{Alias, Cast, Cte, DataType, Expression, Function, Identifier, Join, JoinKind, LikeOp, Literal, StringAggFunc, Subquery, UnaryFunc};
16use crate::generator::GeneratorConfig;
17use crate::tokens::TokenizerConfig;
18
19/// T-SQL (SQL Server) dialect
20pub struct TSQLDialect;
21
22impl DialectImpl for TSQLDialect {
23    fn dialect_type(&self) -> DialectType {
24        DialectType::TSQL
25    }
26
27    fn tokenizer_config(&self) -> TokenizerConfig {
28        let mut config = TokenizerConfig::default();
29        // SQL Server uses square brackets for identifiers
30        config.identifiers.insert('[', ']');
31        // SQL Server also supports double quotes (when QUOTED_IDENTIFIER is ON)
32        config.identifiers.insert('"', '"');
33        config
34    }
35
36    fn generator_config(&self) -> GeneratorConfig {
37        use crate::generator::IdentifierQuoteStyle;
38        GeneratorConfig {
39            // Use square brackets by default for SQL Server
40            identifier_quote: '[',
41            identifier_quote_style: IdentifierQuoteStyle::BRACKET,
42            dialect: Some(DialectType::TSQL),
43            // T-SQL specific settings from Python sqlglot
44            // SQL Server uses TOP/FETCH instead of LIMIT
45            limit_fetch_style: crate::generator::LimitFetchStyle::FetchFirst,
46            // NULLS FIRST/LAST not supported in SQL Server
47            null_ordering_supported: false,
48            // SQL Server supports SELECT INTO
49            supports_select_into: true,
50            // ALTER TABLE doesn't require COLUMN keyword
51            alter_table_include_column_keyword: false,
52            // Computed columns don't need type declaration
53            computed_column_with_type: false,
54            // RECURSIVE keyword not required in CTEs
55            cte_recursive_keyword_required: false,
56            // Ensure boolean expressions
57            ensure_bools: true,
58            // CONCAT requires at least 2 args
59            supports_single_arg_concat: false,
60            // TABLESAMPLE REPEATABLE
61            tablesample_seed_keyword: "REPEATABLE",
62            // JSON path without brackets
63            json_path_bracketed_key_supported: false,
64            // No TO_NUMBER function
65            supports_to_number: false,
66            // SET operation modifiers not supported
67            set_op_modifiers: false,
68            // COPY params need equals sign
69            copy_params_eq_required: true,
70            // No ALL clause for EXCEPT/INTERSECT
71            except_intersect_support_all_clause: false,
72            // ALTER SET is wrapped
73            alter_set_wrapped: true,
74            // T-SQL supports TRY_CAST
75            try_supported: true,
76            // No NVL2 support
77            nvl2_supported: false,
78            // TSQL uses = instead of DEFAULT for parameter defaults
79            parameter_default_equals: true,
80            // No window EXCLUDE support
81            supports_window_exclude: false,
82            // No DISTINCT with multiple args
83            multi_arg_distinct: false,
84            // TSQL doesn't support FOR UPDATE/SHARE
85            locking_reads_supported: false,
86            ..Default::default()
87        }
88    }
89
90    fn transform_expr(&self, expr: Expression) -> Result<Expression> {
91        match expr {
92            // ===== SELECT a = 1 → SELECT 1 AS a =====
93            // In T-SQL, `SELECT a = expr` is equivalent to `SELECT expr AS a`
94            // BUT: `SELECT @a = expr` is a variable assignment, not an alias!
95            // Python sqlglot handles this at parser level via _parse_projections()
96            Expression::Select(mut select) => {
97                select.expressions = select.expressions.into_iter().map(|e| {
98                    match e {
99                        Expression::Eq(op) => {
100                            // Check if left side is an identifier (column name)
101                            // Don't transform if it's a variable (starts with @)
102                            match &op.left {
103                                Expression::Column(col) if col.table.is_none() && !col.name.name.starts_with('@') => {
104                                    Expression::Alias(Box::new(Alias {
105                                        this: op.right,
106                                        alias: col.name.clone(),
107                                        column_aliases: Vec::new(),
108                                        pre_alias_comments: Vec::new(),
109                                        trailing_comments: Vec::new(),
110                                    }))
111                                }
112                                Expression::Identifier(ident) if !ident.name.starts_with('@') => {
113                                    Expression::Alias(Box::new(Alias {
114                                        this: op.right,
115                                        alias: ident.clone(),
116                                        column_aliases: Vec::new(),
117                                        pre_alias_comments: Vec::new(),
118                                        trailing_comments: Vec::new(),
119                                    }))
120                                }
121                                _ => Expression::Eq(op),
122                            }
123                        }
124                        other => other,
125                    }
126                }).collect();
127
128                // Transform CTEs in the WITH clause to add auto-aliases
129                if let Some(ref mut with) = select.with {
130                    with.ctes = with.ctes.drain(..).map(|cte| {
131                        self.transform_cte_inner(cte)
132                    }).collect();
133                }
134
135                Ok(Expression::Select(select))
136            }
137
138            // ===== Data Type Mappings =====
139            Expression::DataType(dt) => self.transform_data_type(dt),
140
141            // ===== Boolean IS TRUE/FALSE -> = 1/0 for TSQL =====
142            // TSQL doesn't have IS TRUE/IS FALSE syntax
143            Expression::IsTrue(it) => {
144                let one = Expression::Literal(crate::expressions::Literal::Number("1".to_string()));
145                if it.not {
146                    // a IS NOT TRUE -> NOT a = 1
147                    Ok(Expression::Not(Box::new(crate::expressions::UnaryOp {
148                        this: Expression::Eq(Box::new(crate::expressions::BinaryOp {
149                            left: it.this,
150                            right: one,
151                            left_comments: vec![],
152                            operator_comments: vec![],
153                            trailing_comments: vec![],
154                        })),
155                    })))
156                } else {
157                    // a IS TRUE -> a = 1
158                    Ok(Expression::Eq(Box::new(crate::expressions::BinaryOp {
159                        left: it.this,
160                        right: one,
161                        left_comments: vec![],
162                        operator_comments: vec![],
163                        trailing_comments: vec![],
164                    })))
165                }
166            }
167            Expression::IsFalse(it) => {
168                let zero = Expression::Literal(crate::expressions::Literal::Number("0".to_string()));
169                if it.not {
170                    // a IS NOT FALSE -> NOT a = 0
171                    Ok(Expression::Not(Box::new(crate::expressions::UnaryOp {
172                        this: Expression::Eq(Box::new(crate::expressions::BinaryOp {
173                            left: it.this,
174                            right: zero,
175                            left_comments: vec![],
176                            operator_comments: vec![],
177                            trailing_comments: vec![],
178                        })),
179                    })))
180                } else {
181                    // a IS FALSE -> a = 0
182                    Ok(Expression::Eq(Box::new(crate::expressions::BinaryOp {
183                        left: it.this,
184                        right: zero,
185                        left_comments: vec![],
186                        operator_comments: vec![],
187                        trailing_comments: vec![],
188                    })))
189                }
190            }
191
192            // Note: CASE WHEN boolean conditions are handled in ensure_bools preprocessing
193
194            // NOT IN -> NOT ... IN for TSQL (TSQL prefers NOT prefix)
195            Expression::In(mut in_expr) if in_expr.not => {
196                in_expr.not = false;
197                Ok(Expression::Not(Box::new(crate::expressions::UnaryOp {
198                    this: Expression::In(in_expr),
199                })))
200            }
201
202            // COALESCE with 2 args -> ISNULL in SQL Server (optimization)
203            // Note: COALESCE works in SQL Server, ISNULL is just more idiomatic
204            Expression::Coalesce(f) if f.expressions.len() == 2 => Ok(Expression::Function(Box::new(
205                Function::new("ISNULL".to_string(), f.expressions),
206            ))),
207
208            // NVL -> ISNULL in SQL Server
209            Expression::Nvl(f) => Ok(Expression::Function(Box::new(Function::new(
210                "ISNULL".to_string(),
211                vec![f.this, f.expression],
212            )))),
213
214            // GROUP_CONCAT -> STRING_AGG in SQL Server (SQL Server 2017+)
215            Expression::GroupConcat(f) => Ok(Expression::StringAgg(Box::new(StringAggFunc {
216                this: f.this,
217                separator: f.separator,
218                order_by: f.order_by,
219                distinct: f.distinct,
220                filter: f.filter,
221                limit: None,
222            }))),
223
224            // LISTAGG -> STRING_AGG in SQL Server (SQL Server 2017+)
225            Expression::ListAgg(f) => Ok(Expression::StringAgg(Box::new(StringAggFunc {
226                this: f.this,
227                separator: f.separator,
228                order_by: f.order_by,
229                distinct: f.distinct,
230                filter: f.filter,
231                limit: None,
232            }))),
233
234            // TryCast -> TRY_CAST (SQL Server supports TRY_CAST starting from 2012)
235            Expression::TryCast(c) => Ok(Expression::TryCast(c)),
236
237            // SafeCast -> TRY_CAST
238            Expression::SafeCast(c) => Ok(Expression::TryCast(c)),
239
240            // ILIKE -> LOWER() LIKE LOWER() in SQL Server (no ILIKE support)
241            Expression::ILike(op) => {
242                // SQL Server is case-insensitive by default based on collation
243                // But for explicit case-insensitive matching, use LOWER
244                let lower_left = Expression::Lower(Box::new(UnaryFunc::new(op.left)));
245                let lower_right = Expression::Lower(Box::new(UnaryFunc::new(op.right)));
246                Ok(Expression::Like(Box::new(LikeOp {
247                    left: lower_left,
248                    right: lower_right,
249                    escape: op.escape,
250                    quantifier: op.quantifier,
251                })))
252            }
253
254            // || (Concat operator) -> + in SQL Server
255            // SQL Server uses + for string concatenation
256            Expression::Concat(op) => {
257                // Convert || to + operator (Add)
258                Ok(Expression::Add(op))
259            }
260
261            // RANDOM -> RAND in SQL Server
262            Expression::Random(_) => Ok(Expression::Rand(Box::new(crate::expressions::Rand {
263                seed: None, lower: None, upper: None,
264            }))),
265
266            // UNNEST -> Not directly supported, use CROSS APPLY with STRING_SPLIT or OPENJSON
267            Expression::Unnest(f) => {
268                // For basic cases, we'll use a placeholder
269                // Full support would require context-specific transformation
270                Ok(Expression::Function(Box::new(Function::new(
271                    "OPENJSON".to_string(),
272                    vec![f.this],
273                ))))
274            }
275
276            // EXPLODE -> Similar to UNNEST, use CROSS APPLY
277            Expression::Explode(f) => Ok(Expression::Function(Box::new(Function::new(
278                "OPENJSON".to_string(),
279                vec![f.this],
280            )))),
281
282            // LATERAL JOIN -> CROSS APPLY in SQL Server
283            Expression::Join(join) if join.kind == JoinKind::Lateral => {
284                Ok(Expression::Join(Box::new(Join {
285                    this: join.this,
286                    on: None,
287                    using: join.using,
288                    kind: JoinKind::CrossApply,
289                    use_inner_keyword: false,
290                    use_outer_keyword: false,
291                    deferred_condition: false,
292                    join_hint: None,
293                    match_condition: None,
294                    pivots: join.pivots,
295                })))
296            }
297
298            // LEFT LATERAL JOIN -> OUTER APPLY in SQL Server
299            Expression::Join(join) if join.kind == JoinKind::LeftLateral => {
300                Ok(Expression::Join(Box::new(Join {
301                    this: join.this,
302                    on: None, // APPLY doesn't use ON clause
303                    using: join.using,
304                    kind: JoinKind::OuterApply,
305                    use_inner_keyword: false,
306                    use_outer_keyword: false,
307                    deferred_condition: false,
308                    join_hint: None,
309                    match_condition: None,
310                    pivots: join.pivots,
311                })))
312            }
313
314            // LENGTH -> LEN in SQL Server
315            Expression::Length(f) => Ok(Expression::Function(Box::new(Function::new(
316                "LEN".to_string(),
317                vec![f.this],
318            )))),
319
320            // STDDEV -> STDEV in SQL Server
321            Expression::Stddev(f) => Ok(Expression::Function(Box::new(Function::new(
322                "STDEV".to_string(),
323                vec![f.this],
324            )))),
325
326            // Boolean literals TRUE/FALSE -> 1/0 in SQL Server
327            Expression::Boolean(b) => {
328                let value = if b.value { 1 } else { 0 };
329                Ok(Expression::Literal(crate::expressions::Literal::Number(
330                    value.to_string(),
331                )))
332            }
333
334            // LN -> LOG in SQL Server
335            Expression::Ln(f) => Ok(Expression::Function(Box::new(Function::new(
336                "LOG".to_string(),
337                vec![f.this],
338            )))),
339
340            // ===== Date/time =====
341            // CurrentDate -> CAST(GETDATE() AS DATE) in SQL Server
342            Expression::CurrentDate(_) => {
343                let getdate = Expression::Function(Box::new(Function::new("GETDATE".to_string(), vec![])));
344                Ok(Expression::Cast(Box::new(crate::expressions::Cast {
345                    this: getdate,
346                    to: crate::expressions::DataType::Date,
347                    trailing_comments: Vec::new(),
348                    double_colon_syntax: false,
349                    format: None,
350                    default: None,
351                })))
352            }
353
354            // CurrentTimestamp -> GETDATE() in SQL Server
355            Expression::CurrentTimestamp(_) => Ok(Expression::Function(Box::new(Function::new(
356                "GETDATE".to_string(),
357                vec![],
358            )))),
359
360            // DateDiff -> DATEDIFF
361            Expression::DateDiff(f) => {
362                // TSQL: DATEDIFF(unit, start, end)
363                let unit_str = match f.unit {
364                    Some(crate::expressions::IntervalUnit::Year) => "YEAR",
365                    Some(crate::expressions::IntervalUnit::Quarter) => "QUARTER",
366                    Some(crate::expressions::IntervalUnit::Month) => "MONTH",
367                    Some(crate::expressions::IntervalUnit::Week) => "WEEK",
368                    Some(crate::expressions::IntervalUnit::Day) => "DAY",
369                    Some(crate::expressions::IntervalUnit::Hour) => "HOUR",
370                    Some(crate::expressions::IntervalUnit::Minute) => "MINUTE",
371                    Some(crate::expressions::IntervalUnit::Second) => "SECOND",
372                    Some(crate::expressions::IntervalUnit::Millisecond) => "MILLISECOND",
373                    Some(crate::expressions::IntervalUnit::Microsecond) => "MICROSECOND",
374                    None => "DAY",
375                };
376                let unit = Expression::Identifier(crate::expressions::Identifier {
377                    name: unit_str.to_string(),
378                    quoted: false,
379                    trailing_comments: Vec::new(),
380                });
381                Ok(Expression::Function(Box::new(Function::new(
382                    "DATEDIFF".to_string(),
383                    vec![unit, f.expression, f.this], // Note: order is different in TSQL
384                ))))
385            }
386
387            // DateAdd -> DATEADD
388            Expression::DateAdd(f) => {
389                let unit_str = match f.unit {
390                    crate::expressions::IntervalUnit::Year => "YEAR",
391                    crate::expressions::IntervalUnit::Quarter => "QUARTER",
392                    crate::expressions::IntervalUnit::Month => "MONTH",
393                    crate::expressions::IntervalUnit::Week => "WEEK",
394                    crate::expressions::IntervalUnit::Day => "DAY",
395                    crate::expressions::IntervalUnit::Hour => "HOUR",
396                    crate::expressions::IntervalUnit::Minute => "MINUTE",
397                    crate::expressions::IntervalUnit::Second => "SECOND",
398                    crate::expressions::IntervalUnit::Millisecond => "MILLISECOND",
399                    crate::expressions::IntervalUnit::Microsecond => "MICROSECOND",
400                };
401                let unit = Expression::Identifier(crate::expressions::Identifier {
402                    name: unit_str.to_string(),
403                    quoted: false,
404                    trailing_comments: Vec::new(),
405                });
406                Ok(Expression::Function(Box::new(Function::new(
407                    "DATEADD".to_string(),
408                    vec![unit, f.interval, f.this],
409                ))))
410            }
411
412            // ===== UUID =====
413            // Uuid -> NEWID in SQL Server
414            Expression::Uuid(_) => Ok(Expression::Function(Box::new(Function::new(
415                "NEWID".to_string(),
416                vec![],
417            )))),
418
419            // ===== Conditional =====
420            // IfFunc -> IIF in SQL Server
421            Expression::IfFunc(f) => {
422                let false_val = f.false_value.unwrap_or(Expression::Null(crate::expressions::Null));
423                Ok(Expression::Function(Box::new(Function::new(
424                    "IIF".to_string(),
425                    vec![f.condition, f.true_value, false_val],
426                ))))
427            }
428
429            // ===== String functions =====
430            // StringAgg -> STRING_AGG in SQL Server 2017+ - keep as-is to preserve ORDER BY
431            Expression::StringAgg(f) => Ok(Expression::StringAgg(f)),
432
433            // LastDay -> EOMONTH (note: TSQL doesn't support date part argument)
434            Expression::LastDay(f) => Ok(Expression::Function(Box::new(Function::new(
435                "EOMONTH".to_string(),
436                vec![f.this.clone()],
437            )))),
438
439            // Ceil -> CEILING
440            Expression::Ceil(f) => Ok(Expression::Function(Box::new(Function::new(
441                "CEILING".to_string(),
442                vec![f.this],
443            )))),
444
445            // Repeat -> REPLICATE in SQL Server
446            Expression::Repeat(f) => Ok(Expression::Function(Box::new(Function::new(
447                "REPLICATE".to_string(),
448                vec![f.this, f.times],
449            )))),
450
451            // Chr -> CHAR in SQL Server
452            Expression::Chr(f) => Ok(Expression::Function(Box::new(Function::new(
453                "CHAR".to_string(),
454                vec![f.this],
455            )))),
456
457            // ===== Variance =====
458            // VarPop -> VARP
459            Expression::VarPop(f) => Ok(Expression::Function(Box::new(Function::new(
460                "VARP".to_string(),
461                vec![f.this],
462            )))),
463
464            // Variance -> VAR
465            Expression::Variance(f) => Ok(Expression::Function(Box::new(Function::new(
466                "VAR".to_string(),
467                vec![f.this],
468            )))),
469
470            // ===== Hash functions =====
471            // MD5Digest -> HASHBYTES('MD5', ...)
472            Expression::MD5Digest(f) => Ok(Expression::Function(Box::new(Function::new(
473                "HASHBYTES".to_string(),
474                vec![Expression::string("MD5"), *f.this],
475            )))),
476
477            // SHA -> HASHBYTES('SHA1', ...)
478            Expression::SHA(f) => Ok(Expression::Function(Box::new(Function::new(
479                "HASHBYTES".to_string(),
480                vec![Expression::string("SHA1"), f.this],
481            )))),
482
483            // SHA1Digest -> HASHBYTES('SHA1', ...)
484            Expression::SHA1Digest(f) => Ok(Expression::Function(Box::new(Function::new(
485                "HASHBYTES".to_string(),
486                vec![Expression::string("SHA1"), f.this],
487            )))),
488
489            // ===== Array functions =====
490            // ArrayToString -> STRING_AGG
491            Expression::ArrayToString(f) => Ok(Expression::Function(Box::new(Function::new(
492                "STRING_AGG".to_string(),
493                vec![f.this],
494            )))),
495
496            // ===== DDL Column Constraints =====
497            // AutoIncrementColumnConstraint -> IDENTITY in SQL Server
498            Expression::AutoIncrementColumnConstraint(_) => {
499                Ok(Expression::Function(Box::new(Function::new(
500                    "IDENTITY".to_string(),
501                    vec![],
502                ))))
503            }
504
505            // ===== DDL three-part name stripping =====
506            // TSQL strips database (catalog) prefix from 3-part names for CREATE VIEW/DROP VIEW
507            // Python sqlglot: expression.this.set("catalog", None)
508            Expression::CreateView(mut view) => {
509                // Strip catalog from three-part name (a.b.c -> b.c)
510                view.name.catalog = None;
511                Ok(Expression::CreateView(view))
512            }
513
514            Expression::DropView(mut view) => {
515                // Strip catalog from three-part name (a.b.c -> b.c)
516                view.name.catalog = None;
517                Ok(Expression::DropView(view))
518            }
519
520            // ParseJson: handled by generator (emits just the string literal for TSQL)
521
522            // JSONExtract with variant_extract (Snowflake colon syntax) -> ISNULL(JSON_QUERY, JSON_VALUE)
523            Expression::JSONExtract(e) if e.variant_extract.is_some() => {
524                let path = match *e.expression {
525                    Expression::Literal(Literal::String(s)) => {
526                        let normalized = if s.starts_with('$') {
527                            s
528                        } else if s.starts_with('[') {
529                            format!("${}", s)
530                        } else {
531                            format!("$.{}", s)
532                        };
533                        Expression::Literal(Literal::String(normalized))
534                    }
535                    other => other,
536                };
537                let json_query = Expression::Function(Box::new(Function::new(
538                    "JSON_QUERY".to_string(),
539                    vec![(*e.this).clone(), path.clone()],
540                )));
541                let json_value = Expression::Function(Box::new(Function::new(
542                    "JSON_VALUE".to_string(),
543                    vec![*e.this, path],
544                )));
545                Ok(Expression::Function(Box::new(Function::new(
546                    "ISNULL".to_string(),
547                    vec![json_query, json_value],
548                ))))
549            }
550
551            // Generic function transformations
552            Expression::Function(f) => self.transform_function(*f),
553
554            // Generic aggregate function transformations
555            Expression::AggregateFunction(f) => self.transform_aggregate_function(f),
556
557            // ===== CTEs need auto-aliased outputs =====
558            // In TSQL, bare expressions in CTEs need explicit aliases
559            Expression::Cte(cte) => self.transform_cte(*cte),
560
561            // ===== Subqueries need auto-aliased outputs =====
562            // In TSQL, bare expressions in aliased subqueries need explicit aliases
563            Expression::Subquery(subquery) => self.transform_subquery(*subquery),
564
565            // Convert JsonQuery struct to ISNULL(JSON_QUERY(..., path), JSON_VALUE(..., path))
566            Expression::JsonQuery(f) => {
567                let json_query = Expression::Function(Box::new(Function::new(
568                    "JSON_QUERY".to_string(),
569                    vec![f.this.clone(), f.path.clone()],
570                )));
571                let json_value = Expression::Function(Box::new(Function::new(
572                    "JSON_VALUE".to_string(),
573                    vec![f.this, f.path],
574                )));
575                Ok(Expression::Function(Box::new(Function::new(
576                    "ISNULL".to_string(),
577                    vec![json_query, json_value],
578                ))))
579            }
580            // Convert JsonValue struct to Function("JSON_VALUE", ...) for uniform handling
581            Expression::JsonValue(f) => {
582                Ok(Expression::Function(Box::new(Function::new(
583                    "JSON_VALUE".to_string(),
584                    vec![f.this, f.path],
585                ))))
586            }
587
588            // Pass through everything else
589            _ => Ok(expr),
590        }
591    }
592}
593
594impl TSQLDialect {
595    /// Transform data types according to T-SQL TYPE_MAPPING
596    fn transform_data_type(&self, dt: crate::expressions::DataType) -> Result<Expression> {
597        use crate::expressions::DataType;
598        let transformed = match dt {
599            // BOOLEAN -> BIT
600            DataType::Boolean => DataType::Custom {
601                name: "BIT".to_string(),
602            },
603            // INT stays as INT in TSQL (native type)
604            DataType::Int { .. } => dt,
605            // DOUBLE stays as Double internally (TSQL generator outputs FLOAT for it)
606            // DECIMAL -> NUMERIC
607            DataType::Decimal { precision, scale } => DataType::Custom {
608                name: if let (Some(p), Some(s)) = (&precision, &scale) {
609                    format!("NUMERIC({}, {})", p, s)
610                } else if let Some(p) = &precision {
611                    format!("NUMERIC({})", p)
612                } else {
613                    "NUMERIC".to_string()
614                },
615            },
616            // TEXT -> VARCHAR(MAX)
617            DataType::Text => DataType::Custom {
618                name: "VARCHAR(MAX)".to_string(),
619            },
620            // TIMESTAMP -> DATETIME2
621            DataType::Timestamp { .. } => DataType::Custom {
622                name: "DATETIME2".to_string(),
623            },
624            // UUID -> UNIQUEIDENTIFIER
625            DataType::Uuid => DataType::Custom {
626                name: "UNIQUEIDENTIFIER".to_string(),
627            },
628            // Keep all other types as-is
629            other => other,
630        };
631        Ok(Expression::DataType(transformed))
632    }
633
634    fn transform_function(&self, f: Function) -> Result<Expression> {
635        let name_upper = f.name.to_uppercase();
636        match name_upper.as_str() {
637            // COALESCE -> ISNULL for 2 args (optimization)
638            "COALESCE" if f.args.len() == 2 => Ok(Expression::Function(Box::new(Function::new(
639                "ISNULL".to_string(),
640                f.args,
641            )))),
642
643            // NVL -> ISNULL (SQL Server function)
644            "NVL" if f.args.len() == 2 => Ok(Expression::Function(Box::new(Function::new(
645                "ISNULL".to_string(),
646                f.args,
647            )))),
648
649            // GROUP_CONCAT -> STRING_AGG in SQL Server 2017+
650            "GROUP_CONCAT" if !f.args.is_empty() => Ok(Expression::Function(Box::new(
651                Function::new("STRING_AGG".to_string(), f.args),
652            ))),
653
654            // STRING_AGG is native to SQL Server 2017+
655            "STRING_AGG" => Ok(Expression::Function(Box::new(f))),
656
657            // LISTAGG -> STRING_AGG
658            "LISTAGG" if !f.args.is_empty() => Ok(Expression::Function(Box::new(Function::new(
659                "STRING_AGG".to_string(),
660                f.args,
661            )))),
662
663            // SUBSTR -> SUBSTRING
664            "SUBSTR" => Ok(Expression::Function(Box::new(Function::new(
665                "SUBSTRING".to_string(),
666                f.args,
667            )))),
668
669            // LENGTH -> LEN in SQL Server
670            "LENGTH" if f.args.len() == 1 => Ok(Expression::Function(Box::new(Function::new(
671                "LEN".to_string(),
672                f.args,
673            )))),
674
675            // RANDOM -> RAND
676            "RANDOM" => Ok(Expression::Rand(Box::new(crate::expressions::Rand {
677                seed: None, lower: None, upper: None,
678            }))),
679
680            // NOW -> GETDATE or CURRENT_TIMESTAMP (both work)
681            "NOW" => Ok(Expression::Function(Box::new(Function::new(
682                "GETDATE".to_string(),
683                vec![],
684            )))),
685
686            // CURRENT_TIMESTAMP -> GETDATE (SQL Server prefers GETDATE)
687            "CURRENT_TIMESTAMP" => Ok(Expression::Function(Box::new(Function::new(
688                "GETDATE".to_string(),
689                vec![],
690            )))),
691
692            // CURRENT_DATE -> CAST(GETDATE() AS DATE)
693            "CURRENT_DATE" => {
694                // In SQL Server, use CAST(GETDATE() AS DATE)
695                Ok(Expression::Function(Box::new(Function::new(
696                    "CAST".to_string(),
697                    vec![
698                        Expression::Function(Box::new(Function::new("GETDATE".to_string(), vec![]))),
699                        Expression::Identifier(crate::expressions::Identifier::new("DATE")),
700                    ],
701                ))))
702            }
703
704            // TO_DATE -> CONVERT or CAST
705            "TO_DATE" => Ok(Expression::Function(Box::new(Function::new(
706                "CONVERT".to_string(),
707                f.args,
708            )))),
709
710            // TO_TIMESTAMP -> CONVERT
711            "TO_TIMESTAMP" => Ok(Expression::Function(Box::new(Function::new(
712                "CONVERT".to_string(),
713                f.args,
714            )))),
715
716            // TO_CHAR -> FORMAT in SQL Server 2012+
717            "TO_CHAR" => Ok(Expression::Function(Box::new(Function::new(
718                "FORMAT".to_string(),
719                f.args,
720            )))),
721
722            // DATE_FORMAT -> FORMAT
723            "DATE_FORMAT" => Ok(Expression::Function(Box::new(Function::new(
724                "FORMAT".to_string(),
725                f.args,
726            )))),
727
728            // DATE_TRUNC -> DATETRUNC in SQL Server 2022+
729            // For older versions, use DATEADD/DATEDIFF combo
730            "DATE_TRUNC" | "DATETRUNC" => {
731                let mut args = Self::uppercase_first_arg_if_identifier(f.args);
732                // Cast string literal date arg to DATETIME2
733                if args.len() >= 2 {
734                    if let Expression::Literal(Literal::String(_)) = &args[1] {
735                        args[1] = Expression::Cast(Box::new(Cast {
736                            this: args[1].clone(),
737                            to: DataType::Custom { name: "DATETIME2".to_string() },
738                            trailing_comments: Vec::new(),
739                            double_colon_syntax: false,
740                            format: None,
741                            default: None,
742                        }));
743                    }
744                }
745                Ok(Expression::Function(Box::new(Function::new(
746                    "DATETRUNC".to_string(),
747                    args,
748                ))))
749            }
750
751            // DATEADD is native to SQL Server - uppercase the unit
752            "DATEADD" => {
753                let args = Self::uppercase_first_arg_if_identifier(f.args);
754                Ok(Expression::Function(Box::new(Function::new(
755                    "DATEADD".to_string(),
756                    args,
757                ))))
758            }
759
760            // DATEDIFF is native to SQL Server - uppercase the unit
761            "DATEDIFF" => {
762                let args = Self::uppercase_first_arg_if_identifier(f.args);
763                Ok(Expression::Function(Box::new(Function::new(
764                    "DATEDIFF".to_string(),
765                    args,
766                ))))
767            }
768
769            // EXTRACT -> DATEPART in SQL Server
770            "EXTRACT" => Ok(Expression::Function(Box::new(Function::new(
771                "DATEPART".to_string(),
772                f.args,
773            )))),
774
775            // STRPOS / POSITION -> CHARINDEX
776            "STRPOS" | "POSITION" if f.args.len() >= 2 => {
777                // CHARINDEX(substring, string) - same arg order as POSITION
778                Ok(Expression::Function(Box::new(Function::new(
779                    "CHARINDEX".to_string(),
780                    f.args,
781                ))))
782            }
783
784            // CHARINDEX is native
785            "CHARINDEX" => Ok(Expression::Function(Box::new(f))),
786
787            // CEILING -> CEILING (native)
788            "CEILING" | "CEIL" if f.args.len() == 1 => {
789                Ok(Expression::Function(Box::new(Function::new(
790                    "CEILING".to_string(),
791                    f.args,
792                ))))
793            }
794
795            // ARRAY functions don't exist in SQL Server
796            // Would need JSON or table-valued parameters
797
798            // JSON_EXTRACT -> JSON_VALUE or JSON_QUERY
799            "JSON_EXTRACT" => Ok(Expression::Function(Box::new(Function::new(
800                "JSON_VALUE".to_string(),
801                f.args,
802            )))),
803
804            // JSON_EXTRACT_SCALAR -> JSON_VALUE
805            "JSON_EXTRACT_SCALAR" => Ok(Expression::Function(Box::new(Function::new(
806                "JSON_VALUE".to_string(),
807                f.args,
808            )))),
809
810            // PARSE_JSON -> strip in TSQL (just keep the string argument)
811            "PARSE_JSON" if f.args.len() == 1 => Ok(f.args.into_iter().next().unwrap()),
812
813            // GET_PATH(obj, path) -> ISNULL(JSON_QUERY(obj, path), JSON_VALUE(obj, path)) in TSQL
814            "GET_PATH" if f.args.len() == 2 => {
815                let mut args = f.args;
816                let this = args.remove(0);
817                let path = args.remove(0);
818                let json_path = match &path {
819                    Expression::Literal(Literal::String(s)) => {
820                        let normalized = if s.starts_with('$') {
821                            s.clone()
822                        } else if s.starts_with('[') {
823                            format!("${}", s)
824                        } else {
825                            format!("$.{}", s)
826                        };
827                        Expression::Literal(Literal::String(normalized))
828                    }
829                    _ => path,
830                };
831                // ISNULL(JSON_QUERY(obj, path), JSON_VALUE(obj, path))
832                let json_query = Expression::Function(Box::new(Function::new(
833                    "JSON_QUERY".to_string(),
834                    vec![this.clone(), json_path.clone()],
835                )));
836                let json_value = Expression::Function(Box::new(Function::new(
837                    "JSON_VALUE".to_string(),
838                    vec![this, json_path],
839                )));
840                Ok(Expression::Function(Box::new(Function::new(
841                    "ISNULL".to_string(),
842                    vec![json_query, json_value],
843                ))))
844            }
845
846            // JSON_QUERY with 1 arg: add '$' path and wrap in ISNULL
847            // JSON_QUERY with 2 args: leave as-is (already processed or inside ISNULL)
848            "JSON_QUERY" if f.args.len() == 1 => {
849                let this = f.args.into_iter().next().unwrap();
850                let path = Expression::Literal(Literal::String("$".to_string()));
851                let json_query = Expression::Function(Box::new(Function::new(
852                    "JSON_QUERY".to_string(),
853                    vec![this.clone(), path.clone()],
854                )));
855                let json_value = Expression::Function(Box::new(Function::new(
856                    "JSON_VALUE".to_string(),
857                    vec![this, path],
858                )));
859                Ok(Expression::Function(Box::new(Function::new(
860                    "ISNULL".to_string(),
861                    vec![json_query, json_value],
862                ))))
863            }
864
865            // SPLIT -> STRING_SPLIT (returns a table, needs CROSS APPLY)
866            "SPLIT" => Ok(Expression::Function(Box::new(Function::new(
867                "STRING_SPLIT".to_string(),
868                f.args,
869            )))),
870
871            // REGEXP_LIKE -> Not directly supported, use LIKE or PATINDEX
872            // SQL Server has limited regex support via PATINDEX and LIKE
873            "REGEXP_LIKE" => {
874                // Fall back to LIKE (loses regex functionality)
875                Ok(Expression::Function(Box::new(Function::new(
876                    "PATINDEX".to_string(),
877                    f.args,
878                ))))
879            }
880
881            // LN -> LOG in SQL Server
882            "LN" if f.args.len() == 1 => Ok(Expression::Function(Box::new(Function::new(
883                "LOG".to_string(),
884                f.args,
885            )))),
886
887            // LOG with 2 args is LOG(base, value) in most DBs but LOG(value, base) in SQL Server
888            // This needs careful handling
889
890            // STDDEV -> STDEV in SQL Server
891            "STDDEV" | "STDDEV_SAMP" => Ok(Expression::Function(Box::new(Function::new(
892                "STDEV".to_string(),
893                f.args,
894            )))),
895
896            // STDDEV_POP -> STDEVP in SQL Server
897            "STDDEV_POP" => Ok(Expression::Function(Box::new(Function::new(
898                "STDEVP".to_string(),
899                f.args,
900            )))),
901
902            // VAR_SAMP -> VAR in SQL Server
903            "VARIANCE" | "VAR_SAMP" => Ok(Expression::Function(Box::new(Function::new(
904                "VAR".to_string(),
905                f.args,
906            )))),
907
908            // VAR_POP -> VARP in SQL Server
909            "VAR_POP" => Ok(Expression::Function(Box::new(Function::new(
910                "VARP".to_string(),
911                f.args,
912            )))),
913
914            // DATE_ADD(date, interval) -> DATEADD(DAY, interval, date)
915            "DATE_ADD" => {
916                if f.args.len() == 2 {
917                    let mut args = f.args;
918                    let date = args.remove(0);
919                    let interval = args.remove(0);
920                    let unit = Expression::Identifier(crate::expressions::Identifier {
921                        name: "DAY".to_string(),
922                        quoted: false,
923                        trailing_comments: Vec::new(),
924                    });
925                    Ok(Expression::Function(Box::new(Function::new(
926                        "DATEADD".to_string(),
927                        vec![unit, interval, date],
928                    ))))
929                } else {
930                    let args = Self::uppercase_first_arg_if_identifier(f.args);
931                    Ok(Expression::Function(Box::new(Function::new(
932                        "DATEADD".to_string(),
933                        args,
934                    ))))
935                }
936            }
937
938            // INSERT → STUFF (Snowflake/MySQL string INSERT → T-SQL STUFF)
939            "INSERT" => Ok(Expression::Function(Box::new(Function::new(
940                "STUFF".to_string(),
941                f.args,
942            )))),
943
944            // SUSER_NAME(), SUSER_SNAME(), SYSTEM_USER() -> CURRENT_USER
945            "SUSER_NAME" | "SUSER_SNAME" | "SYSTEM_USER" => {
946                Ok(Expression::CurrentUser(Box::new(crate::expressions::CurrentUser { this: None })))
947            }
948
949            // Pass through everything else
950            _ => Ok(Expression::Function(Box::new(f))),
951        }
952    }
953
954    fn transform_aggregate_function(
955        &self,
956        f: Box<crate::expressions::AggregateFunction>,
957    ) -> Result<Expression> {
958        let name_upper = f.name.to_uppercase();
959        match name_upper.as_str() {
960            // GROUP_CONCAT -> STRING_AGG
961            "GROUP_CONCAT" if !f.args.is_empty() => Ok(Expression::Function(Box::new(
962                Function::new("STRING_AGG".to_string(), f.args),
963            ))),
964
965            // LISTAGG -> STRING_AGG
966            "LISTAGG" if !f.args.is_empty() => Ok(Expression::Function(Box::new(Function::new(
967                "STRING_AGG".to_string(),
968                f.args,
969            )))),
970
971            // ARRAY_AGG -> Not directly supported in SQL Server
972            // Would need to use FOR XML PATH or STRING_AGG
973            "ARRAY_AGG" if !f.args.is_empty() => {
974                // Fall back to STRING_AGG (loses array semantics)
975                Ok(Expression::Function(Box::new(Function::new(
976                    "STRING_AGG".to_string(),
977                    f.args,
978                ))))
979            }
980
981            // Pass through everything else
982            _ => Ok(Expression::AggregateFunction(f)),
983        }
984    }
985
986    /// Transform CTEs to add auto-aliases to bare expressions in SELECT
987    /// In TSQL, when a CTE doesn't have explicit column aliases, bare expressions
988    /// in the SELECT need to be aliased
989    fn transform_cte(&self, cte: Cte) -> Result<Expression> {
990        Ok(Expression::Cte(Box::new(self.transform_cte_inner(cte))))
991    }
992
993    /// Inner method to transform a CTE, returning the modified Cte struct
994    fn transform_cte_inner(&self, mut cte: Cte) -> Cte {
995        // Only transform if the CTE doesn't have explicit column aliases
996        // If it has column aliases like `WITH t(a, b) AS (...)`, we don't need to auto-alias
997        if cte.columns.is_empty() {
998            cte.this = self.qualify_derived_table_outputs(cte.this);
999        }
1000        cte
1001    }
1002
1003    /// Transform Subqueries to add auto-aliases to bare expressions in SELECT
1004    /// In TSQL, when a subquery has a table alias but no column aliases,
1005    /// bare expressions need to be aliased
1006    fn transform_subquery(&self, mut subquery: Subquery) -> Result<Expression> {
1007        // Only transform if the subquery has a table alias but no column aliases
1008        // e.g., `(SELECT 1) AS subq` needs aliasing, but `(SELECT 1) AS subq(a)` doesn't
1009        if subquery.alias.is_some() && subquery.column_aliases.is_empty() {
1010            subquery.this = self.qualify_derived_table_outputs(subquery.this);
1011        }
1012        Ok(Expression::Subquery(Box::new(subquery)))
1013    }
1014
1015    /// Add aliases to bare (unaliased) expressions in a SELECT statement
1016    /// This transforms expressions like `SELECT 1` into `SELECT 1 AS [1]`
1017    /// BUT only when the SELECT has no FROM clause (i.e., it's a value expression)
1018    fn qualify_derived_table_outputs(&self, expr: Expression) -> Expression {
1019        match expr {
1020            Expression::Select(mut select) => {
1021                // Only auto-alias if the SELECT has NO from clause
1022                // If there's a FROM clause, column references already have names from the source tables
1023                let has_from = select.from.is_some();
1024                if !has_from {
1025                    select.expressions = select
1026                        .expressions
1027                        .into_iter()
1028                        .map(|e| self.maybe_alias_expression(e))
1029                        .collect();
1030                }
1031                Expression::Select(select)
1032            }
1033            // For UNION/INTERSECT/EXCEPT, transform the first SELECT
1034            Expression::Union(mut u) => {
1035                u.left = self.qualify_derived_table_outputs(u.left);
1036                Expression::Union(u)
1037            }
1038            Expression::Intersect(mut i) => {
1039                i.left = self.qualify_derived_table_outputs(i.left);
1040                Expression::Intersect(i)
1041            }
1042            Expression::Except(mut e) => {
1043                e.left = self.qualify_derived_table_outputs(e.left);
1044                Expression::Except(e)
1045            }
1046            // Already wrapped in a Subquery (nested), transform the inner
1047            Expression::Subquery(mut s) => {
1048                s.this = self.qualify_derived_table_outputs(s.this);
1049                Expression::Subquery(s)
1050            }
1051            // Pass through anything else
1052            other => other,
1053        }
1054    }
1055
1056    /// Add an alias to a bare expression if needed
1057    /// Returns the expression unchanged if it already has an alias or is a star
1058    /// NOTE: This is only called for SELECTs without a FROM clause, so all bare
1059    /// expressions (including identifiers and columns) need to be aliased.
1060    fn maybe_alias_expression(&self, expr: Expression) -> Expression {
1061        match &expr {
1062            // Already has an alias, leave it alone
1063            Expression::Alias(_) => expr,
1064            // Multiple aliases, leave it alone
1065            Expression::Aliases(_) => expr,
1066            // Star (including qualified star like t.*) doesn't need an alias
1067            Expression::Star(_) => expr,
1068            // When there's no FROM clause (which is the only case when this method is called),
1069            // we need to alias columns and identifiers too since they're standalone values
1070            // that need explicit names for the derived table output.
1071            // Everything else (literals, functions, columns, identifiers, etc.) needs an alias
1072            _ => {
1073                if let Some(output_name) = self.get_output_name(&expr) {
1074                    Expression::Alias(Box::new(Alias {
1075                        this: expr,
1076                        alias: Identifier {
1077                            name: output_name,
1078                            quoted: true, // Force quoting for TSQL bracket syntax
1079                            trailing_comments: Vec::new(),
1080                        },
1081                        column_aliases: Vec::new(),
1082                        pre_alias_comments: Vec::new(),
1083                        trailing_comments: Vec::new(),
1084                    }))
1085                } else {
1086                    // No output name, leave as-is (shouldn't happen for valid expressions)
1087                    expr
1088                }
1089            }
1090        }
1091    }
1092
1093    /// Get the "output name" of an expression for auto-aliasing
1094    /// For literals, this is the literal value
1095    /// For columns, this is the column name
1096    fn get_output_name(&self, expr: &Expression) -> Option<String> {
1097        match expr {
1098            // Literals - use the literal value as the name
1099            Expression::Literal(lit) => match lit {
1100                Literal::Number(n) => Some(n.clone()),
1101                Literal::String(s) => Some(s.clone()),
1102                Literal::HexString(h) => Some(format!("0x{}", h)),
1103                Literal::HexNumber(h) => Some(format!("0x{}", h)),
1104                Literal::BitString(b) => Some(format!("b{}", b)),
1105                Literal::ByteString(b) => Some(format!("b'{}'", b)),
1106                Literal::NationalString(s) => Some(format!("N'{}'", s)),
1107                Literal::Date(d) => Some(d.clone()),
1108                Literal::Time(t) => Some(t.clone()),
1109                Literal::Timestamp(ts) => Some(ts.clone()),
1110                Literal::Datetime(dt) => Some(dt.clone()),
1111                Literal::TripleQuotedString(s, _) => Some(s.clone()),
1112                Literal::EscapeString(s) => Some(s.clone()),
1113                Literal::DollarString(s) => Some(s.clone()),
1114                Literal::RawString(s) => Some(s.clone()),
1115            },
1116            // Columns - use the column name
1117            Expression::Column(col) => Some(col.name.name.clone()),
1118            // Identifiers - use the identifier name
1119            Expression::Identifier(ident) => Some(ident.name.clone()),
1120            // Boolean literals
1121            Expression::Boolean(b) => Some(if b.value { "1" } else { "0" }.to_string()),
1122            // NULL
1123            Expression::Null(_) => Some("NULL".to_string()),
1124            // For functions, use the function name as a fallback
1125            Expression::Function(f) => Some(f.name.clone()),
1126            // For aggregates, use the function name
1127            Expression::AggregateFunction(f) => Some(f.name.clone()),
1128            // For other expressions, generate a generic name
1129            _ => Some(format!("_col_{}", 0)),
1130        }
1131    }
1132
1133    /// Helper to uppercase the first argument if it's an identifier or column (for DATEDIFF, DATEADD units)
1134    fn uppercase_first_arg_if_identifier(mut args: Vec<Expression>) -> Vec<Expression> {
1135        use crate::expressions::Identifier;
1136        if !args.is_empty() {
1137            match &args[0] {
1138                Expression::Identifier(id) => {
1139                    args[0] = Expression::Identifier(Identifier {
1140                        name: id.name.to_uppercase(),
1141                        quoted: id.quoted,
1142                        trailing_comments: id.trailing_comments.clone(),
1143                    });
1144                }
1145                Expression::Column(col) if col.table.is_none() => {
1146                    args[0] = Expression::Identifier(Identifier {
1147                        name: col.name.name.to_uppercase(),
1148                        quoted: col.name.quoted,
1149                        trailing_comments: col.name.trailing_comments.clone(),
1150                    });
1151                }
1152                _ => {}
1153            }
1154        }
1155        args
1156    }
1157}
1158
1159#[cfg(test)]
1160mod tests {
1161    use super::*;
1162    use crate::dialects::Dialect;
1163
1164    fn transpile_to_tsql(sql: &str) -> String {
1165        let dialect = Dialect::get(DialectType::Generic);
1166        let result = dialect
1167            .transpile_to(sql, DialectType::TSQL)
1168            .expect("Transpile failed");
1169        result[0].clone()
1170    }
1171
1172    #[test]
1173    fn test_nvl_to_isnull() {
1174        let result = transpile_to_tsql("SELECT NVL(a, b)");
1175        assert!(
1176            result.contains("ISNULL"),
1177            "Expected ISNULL, got: {}",
1178            result
1179        );
1180    }
1181
1182    #[test]
1183    fn test_coalesce_to_isnull() {
1184        let result = transpile_to_tsql("SELECT COALESCE(a, b)");
1185        assert!(
1186            result.contains("ISNULL"),
1187            "Expected ISNULL, got: {}",
1188            result
1189        );
1190    }
1191
1192    #[test]
1193    fn test_basic_select() {
1194        let result = transpile_to_tsql("SELECT a, b FROM users WHERE id = 1");
1195        assert!(result.contains("SELECT"));
1196        assert!(result.contains("FROM users"));
1197    }
1198
1199    #[test]
1200    fn test_length_to_len() {
1201        let result = transpile_to_tsql("SELECT LENGTH(name)");
1202        assert!(result.contains("LEN"), "Expected LEN, got: {}", result);
1203    }
1204
1205    #[test]
1206    fn test_now_to_getdate() {
1207        let result = transpile_to_tsql("SELECT NOW()");
1208        assert!(
1209            result.contains("GETDATE"),
1210            "Expected GETDATE, got: {}",
1211            result
1212        );
1213    }
1214
1215    #[test]
1216    fn test_group_concat_to_string_agg() {
1217        let result = transpile_to_tsql("SELECT GROUP_CONCAT(name)");
1218        assert!(
1219            result.contains("STRING_AGG"),
1220            "Expected STRING_AGG, got: {}",
1221            result
1222        );
1223    }
1224
1225    #[test]
1226    fn test_listagg_to_string_agg() {
1227        let result = transpile_to_tsql("SELECT LISTAGG(name)");
1228        assert!(
1229            result.contains("STRING_AGG"),
1230            "Expected STRING_AGG, got: {}",
1231            result
1232        );
1233    }
1234
1235    #[test]
1236    fn test_ln_to_log() {
1237        let result = transpile_to_tsql("SELECT LN(x)");
1238        assert!(result.contains("LOG"), "Expected LOG, got: {}", result);
1239    }
1240
1241    #[test]
1242    fn test_stddev_to_stdev() {
1243        let result = transpile_to_tsql("SELECT STDDEV(x)");
1244        assert!(result.contains("STDEV"), "Expected STDEV, got: {}", result);
1245    }
1246
1247    #[test]
1248    fn test_bracket_identifiers() {
1249        // SQL Server uses square brackets for identifiers
1250        let dialect = Dialect::get(DialectType::TSQL);
1251        let config = dialect.generator_config();
1252        assert_eq!(config.identifier_quote, '[');
1253    }
1254
1255    #[test]
1256    fn test_json_query_isnull_wrapper_simple() {
1257        // JSON_QUERY with two args needs ISNULL wrapper when transpiling to TSQL
1258        let dialect = Dialect::get(DialectType::TSQL);
1259        let result = dialect.transpile_to(r#"JSON_QUERY(x, '$')"#, DialectType::TSQL)
1260            .expect("transpile failed");
1261        assert!(result[0].contains("ISNULL"), "JSON_QUERY should be wrapped with ISNULL: {}", result[0]);
1262    }
1263
1264    #[test]
1265    fn test_json_query_isnull_wrapper_nested() {
1266        // Run in a thread with larger stack to avoid stack overflow
1267        std::thread::Builder::new()
1268            .stack_size(16 * 1024 * 1024)
1269            .spawn(|| {
1270                let dialect = Dialect::get(DialectType::TSQL);
1271                let result = dialect.transpile_to(
1272                    r#"JSON_QUERY(REPLACE(REPLACE(x, '''', '"'), '""', '"'))"#,
1273                    DialectType::TSQL,
1274                ).expect("transpile failed");
1275                let expected = r#"ISNULL(JSON_QUERY(REPLACE(REPLACE(x, '''', '"'), '""', '"'), '$'), JSON_VALUE(REPLACE(REPLACE(x, '''', '"'), '""', '"'), '$'))"#;
1276                assert_eq!(result[0], expected, "JSON_QUERY should be wrapped with ISNULL");
1277            })
1278            .expect("Failed to spawn test thread")
1279            .join()
1280            .expect("Test thread panicked");
1281    }
1282}
polyglot_sql/dialects/tsql.rs

polyglot_sql/dialects/
tsql.rs
