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

polyglot_sql/dialects/
tsql.rs
