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

polyglot_sql/dialects/
tsql.rs
