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                });
406                Ok(Expression::Function(Box::new(Function::new(
407                    "DATEDIFF".to_string(),
408                    vec![unit, f.expression, f.this], // Note: order is different in TSQL
409                ))))
410            }
411
412            // DateAdd -> DATEADD
413            Expression::DateAdd(f) => {
414                let unit_str = match f.unit {
415                    crate::expressions::IntervalUnit::Year => "YEAR",
416                    crate::expressions::IntervalUnit::Quarter => "QUARTER",
417                    crate::expressions::IntervalUnit::Month => "MONTH",
418                    crate::expressions::IntervalUnit::Week => "WEEK",
419                    crate::expressions::IntervalUnit::Day => "DAY",
420                    crate::expressions::IntervalUnit::Hour => "HOUR",
421                    crate::expressions::IntervalUnit::Minute => "MINUTE",
422                    crate::expressions::IntervalUnit::Second => "SECOND",
423                    crate::expressions::IntervalUnit::Millisecond => "MILLISECOND",
424                    crate::expressions::IntervalUnit::Microsecond => "MICROSECOND",
425                    crate::expressions::IntervalUnit::Nanosecond => "NANOSECOND",
426                };
427                let unit = Expression::Identifier(crate::expressions::Identifier {
428                    name: unit_str.to_string(),
429                    quoted: false,
430                    trailing_comments: Vec::new(),
431                });
432                Ok(Expression::Function(Box::new(Function::new(
433                    "DATEADD".to_string(),
434                    vec![unit, f.interval, f.this],
435                ))))
436            }
437
438            // ===== UUID =====
439            // Uuid -> NEWID in SQL Server
440            Expression::Uuid(_) => Ok(Expression::Function(Box::new(Function::new(
441                "NEWID".to_string(),
442                vec![],
443            )))),
444
445            // ===== Conditional =====
446            // IfFunc -> IIF in SQL Server
447            Expression::IfFunc(f) => {
448                let false_val = f
449                    .false_value
450                    .unwrap_or(Expression::Null(crate::expressions::Null));
451                Ok(Expression::Function(Box::new(Function::new(
452                    "IIF".to_string(),
453                    vec![f.condition, f.true_value, false_val],
454                ))))
455            }
456
457            // ===== String functions =====
458            // StringAgg -> STRING_AGG in SQL Server 2017+ - keep as-is to preserve ORDER BY
459            Expression::StringAgg(f) => Ok(Expression::StringAgg(f)),
460
461            // LastDay -> EOMONTH (note: TSQL doesn't support date part argument)
462            Expression::LastDay(f) => Ok(Expression::Function(Box::new(Function::new(
463                "EOMONTH".to_string(),
464                vec![f.this.clone()],
465            )))),
466
467            // Ceil -> CEILING
468            Expression::Ceil(f) => Ok(Expression::Function(Box::new(Function::new(
469                "CEILING".to_string(),
470                vec![f.this],
471            )))),
472
473            // Repeat -> REPLICATE in SQL Server
474            Expression::Repeat(f) => Ok(Expression::Function(Box::new(Function::new(
475                "REPLICATE".to_string(),
476                vec![f.this, f.times],
477            )))),
478
479            // Chr -> CHAR in SQL Server
480            Expression::Chr(f) => Ok(Expression::Function(Box::new(Function::new(
481                "CHAR".to_string(),
482                vec![f.this],
483            )))),
484
485            // ===== Variance =====
486            // VarPop -> VARP
487            Expression::VarPop(f) => Ok(Expression::Function(Box::new(Function::new(
488                "VARP".to_string(),
489                vec![f.this],
490            )))),
491
492            // Variance -> VAR
493            Expression::Variance(f) => Ok(Expression::Function(Box::new(Function::new(
494                "VAR".to_string(),
495                vec![f.this],
496            )))),
497
498            // ===== Hash functions =====
499            // MD5Digest -> HASHBYTES('MD5', ...)
500            Expression::MD5Digest(f) => Ok(Expression::Function(Box::new(Function::new(
501                "HASHBYTES".to_string(),
502                vec![Expression::string("MD5"), *f.this],
503            )))),
504
505            // SHA -> HASHBYTES('SHA1', ...)
506            Expression::SHA(f) => Ok(Expression::Function(Box::new(Function::new(
507                "HASHBYTES".to_string(),
508                vec![Expression::string("SHA1"), f.this],
509            )))),
510
511            // SHA1Digest -> HASHBYTES('SHA1', ...)
512            Expression::SHA1Digest(f) => Ok(Expression::Function(Box::new(Function::new(
513                "HASHBYTES".to_string(),
514                vec![Expression::string("SHA1"), f.this],
515            )))),
516
517            // ===== Array functions =====
518            // ArrayToString -> STRING_AGG
519            Expression::ArrayToString(f) => Ok(Expression::Function(Box::new(Function::new(
520                "STRING_AGG".to_string(),
521                vec![f.this],
522            )))),
523
524            // ===== DDL Column Constraints =====
525            // AutoIncrementColumnConstraint -> IDENTITY in SQL Server
526            Expression::AutoIncrementColumnConstraint(_) => Ok(Expression::Function(Box::new(
527                Function::new("IDENTITY".to_string(), vec![]),
528            ))),
529
530            // ===== DDL three-part name stripping =====
531            // TSQL strips database (catalog) prefix from 3-part names for CREATE VIEW/DROP VIEW
532            // Python sqlglot: expression.this.set("catalog", None)
533            Expression::CreateView(mut view) => {
534                // Strip catalog from three-part name (a.b.c -> b.c)
535                view.name.catalog = None;
536                Ok(Expression::CreateView(view))
537            }
538
539            Expression::DropView(mut view) => {
540                // Strip catalog from three-part name (a.b.c -> b.c)
541                view.name.catalog = None;
542                Ok(Expression::DropView(view))
543            }
544
545            // ParseJson: handled by generator (emits just the string literal for TSQL)
546
547            // JSONExtract with variant_extract (Snowflake colon syntax) -> ISNULL(JSON_QUERY, JSON_VALUE)
548            Expression::JSONExtract(e) if e.variant_extract.is_some() => {
549                let path = match *e.expression {
550                    Expression::Literal(Literal::String(s)) => {
551                        let normalized = if s.starts_with('$') {
552                            s
553                        } else if s.starts_with('[') {
554                            format!("${}", s)
555                        } else {
556                            format!("$.{}", s)
557                        };
558                        Expression::Literal(Literal::String(normalized))
559                    }
560                    other => other,
561                };
562                let json_query = Expression::Function(Box::new(Function::new(
563                    "JSON_QUERY".to_string(),
564                    vec![(*e.this).clone(), path.clone()],
565                )));
566                let json_value = Expression::Function(Box::new(Function::new(
567                    "JSON_VALUE".to_string(),
568                    vec![*e.this, path],
569                )));
570                Ok(Expression::Function(Box::new(Function::new(
571                    "ISNULL".to_string(),
572                    vec![json_query, json_value],
573                ))))
574            }
575
576            // Generic function transformations
577            Expression::Function(f) => self.transform_function(*f),
578
579            // Generic aggregate function transformations
580            Expression::AggregateFunction(f) => self.transform_aggregate_function(f),
581
582            // ===== CTEs need auto-aliased outputs =====
583            // In TSQL, bare expressions in CTEs need explicit aliases
584            Expression::Cte(cte) => self.transform_cte(*cte),
585
586            // ===== Subqueries need auto-aliased outputs =====
587            // In TSQL, bare expressions in aliased subqueries need explicit aliases
588            Expression::Subquery(subquery) => self.transform_subquery(*subquery),
589
590            // Convert JsonQuery struct to ISNULL(JSON_QUERY(..., path), JSON_VALUE(..., path))
591            Expression::JsonQuery(f) => {
592                let json_query = Expression::Function(Box::new(Function::new(
593                    "JSON_QUERY".to_string(),
594                    vec![f.this.clone(), f.path.clone()],
595                )));
596                let json_value = Expression::Function(Box::new(Function::new(
597                    "JSON_VALUE".to_string(),
598                    vec![f.this, f.path],
599                )));
600                Ok(Expression::Function(Box::new(Function::new(
601                    "ISNULL".to_string(),
602                    vec![json_query, json_value],
603                ))))
604            }
605            // Convert JsonValue struct to Function("JSON_VALUE", ...) for uniform handling
606            Expression::JsonValue(f) => Ok(Expression::Function(Box::new(Function::new(
607                "JSON_VALUE".to_string(),
608                vec![f.this, f.path],
609            )))),
610
611            // Pass through everything else
612            _ => Ok(expr),
613        }
614    }
615}
616
617impl TSQLDialect {
618    /// Transform data types according to T-SQL TYPE_MAPPING
619    fn transform_data_type(&self, dt: crate::expressions::DataType) -> Result<Expression> {
620        use crate::expressions::DataType;
621        let transformed = match dt {
622            // BOOLEAN -> BIT
623            DataType::Boolean => DataType::Custom {
624                name: "BIT".to_string(),
625            },
626            // INT stays as INT in TSQL (native type)
627            DataType::Int { .. } => dt,
628            // DOUBLE stays as Double internally (TSQL generator outputs FLOAT for it)
629            // DECIMAL -> NUMERIC
630            DataType::Decimal { precision, scale } => DataType::Custom {
631                name: if let (Some(p), Some(s)) = (&precision, &scale) {
632                    format!("NUMERIC({}, {})", p, s)
633                } else if let Some(p) = &precision {
634                    format!("NUMERIC({})", p)
635                } else {
636                    "NUMERIC".to_string()
637                },
638            },
639            // TEXT -> VARCHAR(MAX)
640            DataType::Text => DataType::Custom {
641                name: "VARCHAR(MAX)".to_string(),
642            },
643            // TIMESTAMP -> DATETIME2
644            DataType::Timestamp { .. } => DataType::Custom {
645                name: "DATETIME2".to_string(),
646            },
647            // UUID -> UNIQUEIDENTIFIER
648            DataType::Uuid => DataType::Custom {
649                name: "UNIQUEIDENTIFIER".to_string(),
650            },
651            // Keep all other types as-is
652            other => other,
653        };
654        Ok(Expression::DataType(transformed))
655    }
656
657    fn transform_function(&self, f: Function) -> Result<Expression> {
658        let name_upper = f.name.to_uppercase();
659        match name_upper.as_str() {
660            // COALESCE -> ISNULL for 2 args (optimization)
661            "COALESCE" if f.args.len() == 2 => Ok(Expression::Function(Box::new(Function::new(
662                "ISNULL".to_string(),
663                f.args,
664            )))),
665
666            // NVL -> ISNULL (SQL Server function)
667            "NVL" if f.args.len() == 2 => Ok(Expression::Function(Box::new(Function::new(
668                "ISNULL".to_string(),
669                f.args,
670            )))),
671
672            // GROUP_CONCAT -> STRING_AGG in SQL Server 2017+
673            "GROUP_CONCAT" if !f.args.is_empty() => Ok(Expression::Function(Box::new(
674                Function::new("STRING_AGG".to_string(), f.args),
675            ))),
676
677            // STRING_AGG is native to SQL Server 2017+
678            "STRING_AGG" => Ok(Expression::Function(Box::new(f))),
679
680            // LISTAGG -> STRING_AGG
681            "LISTAGG" if !f.args.is_empty() => Ok(Expression::Function(Box::new(Function::new(
682                "STRING_AGG".to_string(),
683                f.args,
684            )))),
685
686            // SUBSTR -> SUBSTRING
687            "SUBSTR" => Ok(Expression::Function(Box::new(Function::new(
688                "SUBSTRING".to_string(),
689                f.args,
690            )))),
691
692            // LENGTH -> LEN in SQL Server
693            "LENGTH" if f.args.len() == 1 => Ok(Expression::Function(Box::new(Function::new(
694                "LEN".to_string(),
695                f.args,
696            )))),
697
698            // RANDOM -> RAND
699            "RANDOM" => Ok(Expression::Rand(Box::new(crate::expressions::Rand {
700                seed: None,
701                lower: None,
702                upper: None,
703            }))),
704
705            // NOW -> GETDATE or CURRENT_TIMESTAMP (both work)
706            "NOW" => Ok(Expression::Function(Box::new(Function::new(
707                "GETDATE".to_string(),
708                vec![],
709            )))),
710
711            // CURRENT_TIMESTAMP -> GETDATE (SQL Server prefers GETDATE)
712            "CURRENT_TIMESTAMP" => Ok(Expression::Function(Box::new(Function::new(
713                "GETDATE".to_string(),
714                vec![],
715            )))),
716
717            // CURRENT_DATE -> CAST(GETDATE() AS DATE)
718            "CURRENT_DATE" => {
719                // In SQL Server, use CAST(GETDATE() AS DATE)
720                Ok(Expression::Function(Box::new(Function::new(
721                    "CAST".to_string(),
722                    vec![
723                        Expression::Function(Box::new(Function::new(
724                            "GETDATE".to_string(),
725                            vec![],
726                        ))),
727                        Expression::Identifier(crate::expressions::Identifier::new("DATE")),
728                    ],
729                ))))
730            }
731
732            // TO_DATE -> CONVERT or CAST
733            "TO_DATE" => Ok(Expression::Function(Box::new(Function::new(
734                "CONVERT".to_string(),
735                f.args,
736            )))),
737
738            // TO_TIMESTAMP -> CONVERT
739            "TO_TIMESTAMP" => Ok(Expression::Function(Box::new(Function::new(
740                "CONVERT".to_string(),
741                f.args,
742            )))),
743
744            // TO_CHAR -> FORMAT in SQL Server 2012+
745            "TO_CHAR" => Ok(Expression::Function(Box::new(Function::new(
746                "FORMAT".to_string(),
747                f.args,
748            )))),
749
750            // DATE_FORMAT -> FORMAT
751            "DATE_FORMAT" => Ok(Expression::Function(Box::new(Function::new(
752                "FORMAT".to_string(),
753                f.args,
754            )))),
755
756            // DATE_TRUNC -> DATETRUNC in SQL Server 2022+
757            // For older versions, use DATEADD/DATEDIFF combo
758            "DATE_TRUNC" | "DATETRUNC" => {
759                let mut args = Self::uppercase_first_arg_if_identifier(f.args);
760                // Cast string literal date arg to DATETIME2
761                if args.len() >= 2 {
762                    if let Expression::Literal(Literal::String(_)) = &args[1] {
763                        args[1] = Expression::Cast(Box::new(Cast {
764                            this: args[1].clone(),
765                            to: DataType::Custom {
766                                name: "DATETIME2".to_string(),
767                            },
768                            trailing_comments: Vec::new(),
769                            double_colon_syntax: false,
770                            format: None,
771                            default: None,
772                        }));
773                    }
774                }
775                Ok(Expression::Function(Box::new(Function::new(
776                    "DATETRUNC".to_string(),
777                    args,
778                ))))
779            }
780
781            // DATEADD is native to SQL Server - uppercase the unit
782            "DATEADD" => {
783                let args = Self::uppercase_first_arg_if_identifier(f.args);
784                Ok(Expression::Function(Box::new(Function::new(
785                    "DATEADD".to_string(),
786                    args,
787                ))))
788            }
789
790            // DATEDIFF is native to SQL Server - uppercase the unit
791            "DATEDIFF" => {
792                let args = Self::uppercase_first_arg_if_identifier(f.args);
793                Ok(Expression::Function(Box::new(Function::new(
794                    "DATEDIFF".to_string(),
795                    args,
796                ))))
797            }
798
799            // EXTRACT -> DATEPART in SQL Server
800            "EXTRACT" => Ok(Expression::Function(Box::new(Function::new(
801                "DATEPART".to_string(),
802                f.args,
803            )))),
804
805            // STRPOS / POSITION -> CHARINDEX
806            "STRPOS" | "POSITION" if f.args.len() >= 2 => {
807                // CHARINDEX(substring, string) - same arg order as POSITION
808                Ok(Expression::Function(Box::new(Function::new(
809                    "CHARINDEX".to_string(),
810                    f.args,
811                ))))
812            }
813
814            // CHARINDEX is native
815            "CHARINDEX" => Ok(Expression::Function(Box::new(f))),
816
817            // CEILING -> CEILING (native)
818            "CEILING" | "CEIL" if f.args.len() == 1 => Ok(Expression::Function(Box::new(
819                Function::new("CEILING".to_string(), f.args),
820            ))),
821
822            // ARRAY functions don't exist in SQL Server
823            // Would need JSON or table-valued parameters
824
825            // JSON_EXTRACT -> JSON_VALUE or JSON_QUERY
826            "JSON_EXTRACT" => Ok(Expression::Function(Box::new(Function::new(
827                "JSON_VALUE".to_string(),
828                f.args,
829            )))),
830
831            // JSON_EXTRACT_SCALAR -> JSON_VALUE
832            "JSON_EXTRACT_SCALAR" => Ok(Expression::Function(Box::new(Function::new(
833                "JSON_VALUE".to_string(),
834                f.args,
835            )))),
836
837            // PARSE_JSON -> strip in TSQL (just keep the string argument)
838            "PARSE_JSON" if f.args.len() == 1 => Ok(f.args.into_iter().next().unwrap()),
839
840            // GET_PATH(obj, path) -> ISNULL(JSON_QUERY(obj, path), JSON_VALUE(obj, path)) in TSQL
841            "GET_PATH" if f.args.len() == 2 => {
842                let mut args = f.args;
843                let this = args.remove(0);
844                let path = args.remove(0);
845                let json_path = match &path {
846                    Expression::Literal(Literal::String(s)) => {
847                        let normalized = if s.starts_with('$') {
848                            s.clone()
849                        } else if s.starts_with('[') {
850                            format!("${}", s)
851                        } else {
852                            format!("$.{}", s)
853                        };
854                        Expression::Literal(Literal::String(normalized))
855                    }
856                    _ => path,
857                };
858                // ISNULL(JSON_QUERY(obj, path), JSON_VALUE(obj, path))
859                let json_query = Expression::Function(Box::new(Function::new(
860                    "JSON_QUERY".to_string(),
861                    vec![this.clone(), json_path.clone()],
862                )));
863                let json_value = Expression::Function(Box::new(Function::new(
864                    "JSON_VALUE".to_string(),
865                    vec![this, json_path],
866                )));
867                Ok(Expression::Function(Box::new(Function::new(
868                    "ISNULL".to_string(),
869                    vec![json_query, json_value],
870                ))))
871            }
872
873            // JSON_QUERY with 1 arg: add '$' path and wrap in ISNULL
874            // JSON_QUERY with 2 args: leave as-is (already processed or inside ISNULL)
875            "JSON_QUERY" if f.args.len() == 1 => {
876                let this = f.args.into_iter().next().unwrap();
877                let path = Expression::Literal(Literal::String("$".to_string()));
878                let json_query = Expression::Function(Box::new(Function::new(
879                    "JSON_QUERY".to_string(),
880                    vec![this.clone(), path.clone()],
881                )));
882                let json_value = Expression::Function(Box::new(Function::new(
883                    "JSON_VALUE".to_string(),
884                    vec![this, path],
885                )));
886                Ok(Expression::Function(Box::new(Function::new(
887                    "ISNULL".to_string(),
888                    vec![json_query, json_value],
889                ))))
890            }
891
892            // SPLIT -> STRING_SPLIT (returns a table, needs CROSS APPLY)
893            "SPLIT" => Ok(Expression::Function(Box::new(Function::new(
894                "STRING_SPLIT".to_string(),
895                f.args,
896            )))),
897
898            // REGEXP_LIKE -> Not directly supported, use LIKE or PATINDEX
899            // SQL Server has limited regex support via PATINDEX and LIKE
900            "REGEXP_LIKE" => {
901                // Fall back to LIKE (loses regex functionality)
902                Ok(Expression::Function(Box::new(Function::new(
903                    "PATINDEX".to_string(),
904                    f.args,
905                ))))
906            }
907
908            // LN -> LOG in SQL Server
909            "LN" if f.args.len() == 1 => Ok(Expression::Function(Box::new(Function::new(
910                "LOG".to_string(),
911                f.args,
912            )))),
913
914            // LOG with 2 args is LOG(base, value) in most DBs but LOG(value, base) in SQL Server
915            // This needs careful handling
916
917            // STDDEV -> STDEV in SQL Server
918            "STDDEV" | "STDDEV_SAMP" => Ok(Expression::Function(Box::new(Function::new(
919                "STDEV".to_string(),
920                f.args,
921            )))),
922
923            // STDDEV_POP -> STDEVP in SQL Server
924            "STDDEV_POP" => Ok(Expression::Function(Box::new(Function::new(
925                "STDEVP".to_string(),
926                f.args,
927            )))),
928
929            // VAR_SAMP -> VAR in SQL Server
930            "VARIANCE" | "VAR_SAMP" => Ok(Expression::Function(Box::new(Function::new(
931                "VAR".to_string(),
932                f.args,
933            )))),
934
935            // VAR_POP -> VARP in SQL Server
936            "VAR_POP" => Ok(Expression::Function(Box::new(Function::new(
937                "VARP".to_string(),
938                f.args,
939            )))),
940
941            // DATE_ADD(date, interval) -> DATEADD(DAY, interval, date)
942            "DATE_ADD" => {
943                if f.args.len() == 2 {
944                    let mut args = f.args;
945                    let date = args.remove(0);
946                    let interval = args.remove(0);
947                    let unit = Expression::Identifier(crate::expressions::Identifier {
948                        name: "DAY".to_string(),
949                        quoted: false,
950                        trailing_comments: Vec::new(),
951                    });
952                    Ok(Expression::Function(Box::new(Function::new(
953                        "DATEADD".to_string(),
954                        vec![unit, interval, date],
955                    ))))
956                } else {
957                    let args = Self::uppercase_first_arg_if_identifier(f.args);
958                    Ok(Expression::Function(Box::new(Function::new(
959                        "DATEADD".to_string(),
960                        args,
961                    ))))
962                }
963            }
964
965            // INSERT → STUFF (Snowflake/MySQL string INSERT → T-SQL STUFF)
966            "INSERT" => Ok(Expression::Function(Box::new(Function::new(
967                "STUFF".to_string(),
968                f.args,
969            )))),
970
971            // SUSER_NAME(), SUSER_SNAME(), SYSTEM_USER() -> CURRENT_USER
972            "SUSER_NAME" | "SUSER_SNAME" | "SYSTEM_USER" => Ok(Expression::CurrentUser(Box::new(
973                crate::expressions::CurrentUser { this: None },
974            ))),
975
976            // Pass through everything else
977            _ => Ok(Expression::Function(Box::new(f))),
978        }
979    }
980
981    fn transform_aggregate_function(
982        &self,
983        f: Box<crate::expressions::AggregateFunction>,
984    ) -> Result<Expression> {
985        let name_upper = f.name.to_uppercase();
986        match name_upper.as_str() {
987            // GROUP_CONCAT -> STRING_AGG
988            "GROUP_CONCAT" if !f.args.is_empty() => Ok(Expression::Function(Box::new(
989                Function::new("STRING_AGG".to_string(), f.args),
990            ))),
991
992            // LISTAGG -> STRING_AGG
993            "LISTAGG" if !f.args.is_empty() => Ok(Expression::Function(Box::new(Function::new(
994                "STRING_AGG".to_string(),
995                f.args,
996            )))),
997
998            // ARRAY_AGG -> Not directly supported in SQL Server
999            // Would need to use FOR XML PATH or STRING_AGG
1000            "ARRAY_AGG" if !f.args.is_empty() => {
1001                // Fall back to STRING_AGG (loses array semantics)
1002                Ok(Expression::Function(Box::new(Function::new(
1003                    "STRING_AGG".to_string(),
1004                    f.args,
1005                ))))
1006            }
1007
1008            // Pass through everything else
1009            _ => Ok(Expression::AggregateFunction(f)),
1010        }
1011    }
1012
1013    /// Transform CTEs to add auto-aliases to bare expressions in SELECT
1014    /// In TSQL, when a CTE doesn't have explicit column aliases, bare expressions
1015    /// in the SELECT need to be aliased
1016    fn transform_cte(&self, cte: Cte) -> Result<Expression> {
1017        Ok(Expression::Cte(Box::new(self.transform_cte_inner(cte))))
1018    }
1019
1020    /// Inner method to transform a CTE, returning the modified Cte struct
1021    fn transform_cte_inner(&self, mut cte: Cte) -> Cte {
1022        // Only transform if the CTE doesn't have explicit column aliases
1023        // If it has column aliases like `WITH t(a, b) AS (...)`, we don't need to auto-alias
1024        if cte.columns.is_empty() {
1025            cte.this = self.qualify_derived_table_outputs(cte.this);
1026        }
1027        cte
1028    }
1029
1030    /// Transform Subqueries to add auto-aliases to bare expressions in SELECT
1031    /// In TSQL, when a subquery has a table alias but no column aliases,
1032    /// bare expressions need to be aliased
1033    fn transform_subquery(&self, mut subquery: Subquery) -> Result<Expression> {
1034        // Only transform if the subquery has a table alias but no column aliases
1035        // e.g., `(SELECT 1) AS subq` needs aliasing, but `(SELECT 1) AS subq(a)` doesn't
1036        if subquery.alias.is_some() && subquery.column_aliases.is_empty() {
1037            subquery.this = self.qualify_derived_table_outputs(subquery.this);
1038        }
1039        Ok(Expression::Subquery(Box::new(subquery)))
1040    }
1041
1042    /// Add aliases to bare (unaliased) expressions in a SELECT statement
1043    /// This transforms expressions like `SELECT 1` into `SELECT 1 AS [1]`
1044    /// BUT only when the SELECT has no FROM clause (i.e., it's a value expression)
1045    fn qualify_derived_table_outputs(&self, expr: Expression) -> Expression {
1046        match expr {
1047            Expression::Select(mut select) => {
1048                // Only auto-alias if the SELECT has NO from clause
1049                // If there's a FROM clause, column references already have names from the source tables
1050                let has_from = select.from.is_some();
1051                if !has_from {
1052                    select.expressions = select
1053                        .expressions
1054                        .into_iter()
1055                        .map(|e| self.maybe_alias_expression(e))
1056                        .collect();
1057                }
1058                Expression::Select(select)
1059            }
1060            // For UNION/INTERSECT/EXCEPT, transform the first SELECT
1061            Expression::Union(mut u) => {
1062                u.left = self.qualify_derived_table_outputs(u.left);
1063                Expression::Union(u)
1064            }
1065            Expression::Intersect(mut i) => {
1066                i.left = self.qualify_derived_table_outputs(i.left);
1067                Expression::Intersect(i)
1068            }
1069            Expression::Except(mut e) => {
1070                e.left = self.qualify_derived_table_outputs(e.left);
1071                Expression::Except(e)
1072            }
1073            // Already wrapped in a Subquery (nested), transform the inner
1074            Expression::Subquery(mut s) => {
1075                s.this = self.qualify_derived_table_outputs(s.this);
1076                Expression::Subquery(s)
1077            }
1078            // Pass through anything else
1079            other => other,
1080        }
1081    }
1082
1083    /// Add an alias to a bare expression if needed
1084    /// Returns the expression unchanged if it already has an alias or is a star
1085    /// NOTE: This is only called for SELECTs without a FROM clause, so all bare
1086    /// expressions (including identifiers and columns) need to be aliased.
1087    fn maybe_alias_expression(&self, expr: Expression) -> Expression {
1088        match &expr {
1089            // Already has an alias, leave it alone
1090            Expression::Alias(_) => expr,
1091            // Multiple aliases, leave it alone
1092            Expression::Aliases(_) => expr,
1093            // Star (including qualified star like t.*) doesn't need an alias
1094            Expression::Star(_) => expr,
1095            // When there's no FROM clause (which is the only case when this method is called),
1096            // we need to alias columns and identifiers too since they're standalone values
1097            // that need explicit names for the derived table output.
1098            // Everything else (literals, functions, columns, identifiers, etc.) needs an alias
1099            _ => {
1100                if let Some(output_name) = self.get_output_name(&expr) {
1101                    Expression::Alias(Box::new(Alias {
1102                        this: expr,
1103                        alias: Identifier {
1104                            name: output_name,
1105                            quoted: true, // Force quoting for TSQL bracket syntax
1106                            trailing_comments: Vec::new(),
1107                        },
1108                        column_aliases: Vec::new(),
1109                        pre_alias_comments: Vec::new(),
1110                        trailing_comments: Vec::new(),
1111                    }))
1112                } else {
1113                    // No output name, leave as-is (shouldn't happen for valid expressions)
1114                    expr
1115                }
1116            }
1117        }
1118    }
1119
1120    /// Get the "output name" of an expression for auto-aliasing
1121    /// For literals, this is the literal value
1122    /// For columns, this is the column name
1123    fn get_output_name(&self, expr: &Expression) -> Option<String> {
1124        match expr {
1125            // Literals - use the literal value as the name
1126            Expression::Literal(lit) => match lit {
1127                Literal::Number(n) => Some(n.clone()),
1128                Literal::String(s) => Some(s.clone()),
1129                Literal::HexString(h) => Some(format!("0x{}", h)),
1130                Literal::HexNumber(h) => Some(format!("0x{}", h)),
1131                Literal::BitString(b) => Some(format!("b{}", b)),
1132                Literal::ByteString(b) => Some(format!("b'{}'", b)),
1133                Literal::NationalString(s) => Some(format!("N'{}'", s)),
1134                Literal::Date(d) => Some(d.clone()),
1135                Literal::Time(t) => Some(t.clone()),
1136                Literal::Timestamp(ts) => Some(ts.clone()),
1137                Literal::Datetime(dt) => Some(dt.clone()),
1138                Literal::TripleQuotedString(s, _) => Some(s.clone()),
1139                Literal::EscapeString(s) => Some(s.clone()),
1140                Literal::DollarString(s) => Some(s.clone()),
1141                Literal::RawString(s) => Some(s.clone()),
1142            },
1143            // Columns - use the column name
1144            Expression::Column(col) => Some(col.name.name.clone()),
1145            // Identifiers - use the identifier name
1146            Expression::Identifier(ident) => Some(ident.name.clone()),
1147            // Boolean literals
1148            Expression::Boolean(b) => Some(if b.value { "1" } else { "0" }.to_string()),
1149            // NULL
1150            Expression::Null(_) => Some("NULL".to_string()),
1151            // For functions, use the function name as a fallback
1152            Expression::Function(f) => Some(f.name.clone()),
1153            // For aggregates, use the function name
1154            Expression::AggregateFunction(f) => Some(f.name.clone()),
1155            // For other expressions, generate a generic name
1156            _ => Some(format!("_col_{}", 0)),
1157        }
1158    }
1159
1160    /// Helper to uppercase the first argument if it's an identifier or column (for DATEDIFF, DATEADD units)
1161    fn uppercase_first_arg_if_identifier(mut args: Vec<Expression>) -> Vec<Expression> {
1162        use crate::expressions::Identifier;
1163        if !args.is_empty() {
1164            match &args[0] {
1165                Expression::Identifier(id) => {
1166                    args[0] = Expression::Identifier(Identifier {
1167                        name: id.name.to_uppercase(),
1168                        quoted: id.quoted,
1169                        trailing_comments: id.trailing_comments.clone(),
1170                    });
1171                }
1172                Expression::Column(col) if col.table.is_none() => {
1173                    args[0] = Expression::Identifier(Identifier {
1174                        name: col.name.name.to_uppercase(),
1175                        quoted: col.name.quoted,
1176                        trailing_comments: col.name.trailing_comments.clone(),
1177                    });
1178                }
1179                _ => {}
1180            }
1181        }
1182        args
1183    }
1184}
1185
1186#[cfg(test)]
1187mod tests {
1188    use super::*;
1189    use crate::dialects::Dialect;
1190
1191    fn transpile_to_tsql(sql: &str) -> String {
1192        let dialect = Dialect::get(DialectType::Generic);
1193        let result = dialect
1194            .transpile_to(sql, DialectType::TSQL)
1195            .expect("Transpile failed");
1196        result[0].clone()
1197    }
1198
1199    #[test]
1200    fn test_nvl_to_isnull() {
1201        let result = transpile_to_tsql("SELECT NVL(a, b)");
1202        assert!(
1203            result.contains("ISNULL"),
1204            "Expected ISNULL, got: {}",
1205            result
1206        );
1207    }
1208
1209    #[test]
1210    fn test_coalesce_to_isnull() {
1211        let result = transpile_to_tsql("SELECT COALESCE(a, b)");
1212        assert!(
1213            result.contains("ISNULL"),
1214            "Expected ISNULL, got: {}",
1215            result
1216        );
1217    }
1218
1219    #[test]
1220    fn test_basic_select() {
1221        let result = transpile_to_tsql("SELECT a, b FROM users WHERE id = 1");
1222        assert!(result.contains("SELECT"));
1223        assert!(result.contains("FROM users"));
1224    }
1225
1226    #[test]
1227    fn test_length_to_len() {
1228        let result = transpile_to_tsql("SELECT LENGTH(name)");
1229        assert!(result.contains("LEN"), "Expected LEN, got: {}", result);
1230    }
1231
1232    #[test]
1233    fn test_now_to_getdate() {
1234        let result = transpile_to_tsql("SELECT NOW()");
1235        assert!(
1236            result.contains("GETDATE"),
1237            "Expected GETDATE, got: {}",
1238            result
1239        );
1240    }
1241
1242    #[test]
1243    fn test_group_concat_to_string_agg() {
1244        let result = transpile_to_tsql("SELECT GROUP_CONCAT(name)");
1245        assert!(
1246            result.contains("STRING_AGG"),
1247            "Expected STRING_AGG, got: {}",
1248            result
1249        );
1250    }
1251
1252    #[test]
1253    fn test_listagg_to_string_agg() {
1254        let result = transpile_to_tsql("SELECT LISTAGG(name)");
1255        assert!(
1256            result.contains("STRING_AGG"),
1257            "Expected STRING_AGG, got: {}",
1258            result
1259        );
1260    }
1261
1262    #[test]
1263    fn test_ln_to_log() {
1264        let result = transpile_to_tsql("SELECT LN(x)");
1265        assert!(result.contains("LOG"), "Expected LOG, got: {}", result);
1266    }
1267
1268    #[test]
1269    fn test_stddev_to_stdev() {
1270        let result = transpile_to_tsql("SELECT STDDEV(x)");
1271        assert!(result.contains("STDEV"), "Expected STDEV, got: {}", result);
1272    }
1273
1274    #[test]
1275    fn test_bracket_identifiers() {
1276        // SQL Server uses square brackets for identifiers
1277        let dialect = Dialect::get(DialectType::TSQL);
1278        let config = dialect.generator_config();
1279        assert_eq!(config.identifier_quote, '[');
1280    }
1281
1282    #[test]
1283    fn test_json_query_isnull_wrapper_simple() {
1284        // JSON_QUERY with two args needs ISNULL wrapper when transpiling to TSQL
1285        let dialect = Dialect::get(DialectType::TSQL);
1286        let result = dialect
1287            .transpile_to(r#"JSON_QUERY(x, '$')"#, DialectType::TSQL)
1288            .expect("transpile failed");
1289        assert!(
1290            result[0].contains("ISNULL"),
1291            "JSON_QUERY should be wrapped with ISNULL: {}",
1292            result[0]
1293        );
1294    }
1295
1296    #[test]
1297    fn test_json_query_isnull_wrapper_nested() {
1298        // Run in a thread with larger stack to avoid stack overflow
1299        std::thread::Builder::new()
1300            .stack_size(16 * 1024 * 1024)
1301            .spawn(|| {
1302                let dialect = Dialect::get(DialectType::TSQL);
1303                let result = dialect.transpile_to(
1304                    r#"JSON_QUERY(REPLACE(REPLACE(x, '''', '"'), '""', '"'))"#,
1305                    DialectType::TSQL,
1306                ).expect("transpile failed");
1307                let expected = r#"ISNULL(JSON_QUERY(REPLACE(REPLACE(x, '''', '"'), '""', '"'), '$'), JSON_VALUE(REPLACE(REPLACE(x, '''', '"'), '""', '"'), '$'))"#;
1308                assert_eq!(result[0], expected, "JSON_QUERY should be wrapped with ISNULL");
1309            })
1310            .expect("Failed to spawn test thread")
1311            .join()
1312            .expect("Test thread panicked");
1313    }
1314}
polyglot_sql/dialects/tsql.rs

polyglot_sql/dialects/
tsql.rs
