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

polyglot_sql/dialects/
tsql.rs
