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

polyglot_sql/dialects/
tsql.rs
