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

polyglot_sql/dialects/
tsql.rs
