polyglot_sql/dialects/
tsql.rs

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

polyglot_sql/dialects/
tsql.rs
