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

polyglot_sql/dialects/
tsql.rs
