polyglot_sql/dialects/
singlestore.rs

1//! SingleStore Dialect
2//!
3//! SingleStore (formerly MemSQL) specific transformations based on sqlglot patterns.
4//! SingleStore is MySQL-compatible with distributed database extensions.
5
6use super::{DialectImpl, DialectType};
7use crate::error::Result;
8use crate::expressions::{
9    AggFunc, BinaryOp, Case, Cast, CollationExpr, DataType, Expression, Function, Paren, VarArgFunc,
10};
11#[cfg(feature = "generate")]
12use crate::generator::GeneratorConfig;
13use crate::tokens::TokenizerConfig;
14
15/// SingleStore dialect (MySQL-compatible distributed database)
16pub struct SingleStoreDialect;
17
18impl DialectImpl for SingleStoreDialect {
19    fn dialect_type(&self) -> DialectType {
20        DialectType::SingleStore
21    }
22
23    fn tokenizer_config(&self) -> TokenizerConfig {
24        let mut config = TokenizerConfig::default();
25        // SingleStore uses backticks for identifiers (MySQL-style)
26        config.identifiers.insert('`', '`');
27        config.nested_comments = false;
28        config
29    }
30
31    #[cfg(feature = "generate")]
32
33    fn generator_config(&self) -> GeneratorConfig {
34        use crate::generator::IdentifierQuoteStyle;
35        GeneratorConfig {
36            identifier_quote: '`',
37            identifier_quote_style: IdentifierQuoteStyle::BACKTICK,
38            dialect: Some(DialectType::SingleStore),
39            ..Default::default()
40        }
41    }
42
43    #[cfg(feature = "transpile")]
44
45    fn transform_expr(&self, expr: Expression) -> Result<Expression> {
46        match expr {
47            // SHOW INDEXES/KEYS -> SHOW INDEX in SingleStore
48            Expression::Show(mut s) => {
49                // Normalize INDEXES and KEYS to INDEX
50                if s.this == "INDEXES" || s.this == "KEYS" {
51                    s.this = "INDEX".to_string();
52                }
53                Ok(Expression::Show(s))
54            }
55
56            // SingleStore: Cast followed by COLLATE needs double cast
57            // e.g., name :> LONGTEXT COLLATE 'utf8mb4_bin' -> name :> LONGTEXT :> LONGTEXT COLLATE 'utf8mb4_bin'
58            Expression::Collation(c) => {
59                if let Expression::Cast(inner_cast) = &c.this {
60                    // Wrap the cast in another cast with the same type
61                    let double_cast = Expression::Cast(Box::new(Cast {
62                        this: c.this.clone(),
63                        to: inner_cast.to.clone(),
64                        trailing_comments: Vec::new(),
65                        double_colon_syntax: false,
66                        format: None,
67                        default: None,
68                        inferred_type: None,
69                    }));
70                    Ok(Expression::Collation(Box::new(CollationExpr {
71                        this: double_cast,
72                        collation: c.collation.clone(),
73                        quoted: c.quoted,
74                        double_quoted: c.double_quoted,
75                    })))
76                } else {
77                    Ok(Expression::Collation(c))
78                }
79            }
80
81            // IFNULL is native in SingleStore (MySQL-style)
82            Expression::IfNull(f) => Ok(Expression::IfNull(f)),
83
84            // NVL -> IFNULL in SingleStore
85            Expression::Nvl(f) => Ok(Expression::IfNull(f)),
86
87            // TryCast -> not directly supported, use :> operator
88            Expression::TryCast(c) => Ok(Expression::TryCast(c)),
89
90            // SafeCast -> TryCast in SingleStore
91            Expression::SafeCast(c) => Ok(Expression::TryCast(c)),
92
93            // CountIf -> SUM(CASE WHEN condition THEN 1 ELSE 0 END)
94            Expression::CountIf(f) => {
95                let case_expr = Expression::Case(Box::new(Case {
96                    operand: None,
97                    whens: vec![(f.this.clone(), Expression::number(1))],
98                    else_: Some(Expression::number(0)),
99                    comments: Vec::new(),
100                    inferred_type: None,
101                }));
102                Ok(Expression::Sum(Box::new(AggFunc {
103                    ignore_nulls: None,
104                    having_max: None,
105                    this: case_expr,
106                    distinct: f.distinct,
107                    filter: f.filter,
108                    order_by: Vec::new(),
109                    name: None,
110                    limit: None,
111                    inferred_type: None,
112                })))
113            }
114
115            // RAND is native in SingleStore
116            Expression::Rand(r) => Ok(Expression::Rand(r)),
117
118            // Second -> DATE_FORMAT(..., '%s') :> INT (SingleStore doesn't have native SECOND)
119            Expression::Second(f) => {
120                let date = f.this;
121                // Cast to TIME(6) first
122                let cast_to_time = Expression::Cast(Box::new(Cast {
123                    this: date,
124                    to: DataType::Time {
125                        precision: Some(6),
126                        timezone: false,
127                    },
128                    trailing_comments: Vec::new(),
129                    double_colon_syntax: false,
130                    format: None,
131                    default: None,
132                    inferred_type: None,
133                }));
134                let date_format = Expression::Function(Box::new(Function::new(
135                    "DATE_FORMAT".to_string(),
136                    vec![cast_to_time, Expression::string("%s")],
137                )));
138                Ok(Expression::Cast(Box::new(Cast {
139                    this: date_format,
140                    to: DataType::Int {
141                        length: None,
142                        integer_spelling: false,
143                    },
144                    trailing_comments: Vec::new(),
145                    double_colon_syntax: false,
146                    format: None,
147                    default: None,
148                    inferred_type: None,
149                })))
150            }
151
152            // Hour -> DATE_FORMAT(..., '%k') :> INT (SingleStore uses DATE_FORMAT for HOUR)
153            Expression::Hour(f) => {
154                let date = f.this;
155                // Cast to TIME(6) first
156                let cast_to_time = Expression::Cast(Box::new(Cast {
157                    this: date,
158                    to: DataType::Time {
159                        precision: Some(6),
160                        timezone: false,
161                    },
162                    trailing_comments: Vec::new(),
163                    double_colon_syntax: false,
164                    format: None,
165                    default: None,
166                    inferred_type: None,
167                }));
168                let date_format = Expression::Function(Box::new(Function::new(
169                    "DATE_FORMAT".to_string(),
170                    vec![cast_to_time, Expression::string("%k")],
171                )));
172                Ok(Expression::Cast(Box::new(Cast {
173                    this: date_format,
174                    to: DataType::Int {
175                        length: None,
176                        integer_spelling: false,
177                    },
178                    trailing_comments: Vec::new(),
179                    double_colon_syntax: false,
180                    format: None,
181                    default: None,
182                    inferred_type: None,
183                })))
184            }
185
186            // Minute -> DATE_FORMAT(..., '%i') :> INT (SingleStore doesn't have native MINUTE)
187            Expression::Minute(f) => {
188                let date = f.this;
189                // Cast to TIME(6) first
190                let cast_to_time = Expression::Cast(Box::new(Cast {
191                    this: date,
192                    to: DataType::Time {
193                        precision: Some(6),
194                        timezone: false,
195                    },
196                    trailing_comments: Vec::new(),
197                    double_colon_syntax: false,
198                    format: None,
199                    default: None,
200                    inferred_type: None,
201                }));
202                let date_format = Expression::Function(Box::new(Function::new(
203                    "DATE_FORMAT".to_string(),
204                    vec![cast_to_time, Expression::string("%i")],
205                )));
206                Ok(Expression::Cast(Box::new(Cast {
207                    this: date_format,
208                    to: DataType::Int {
209                        length: None,
210                        integer_spelling: false,
211                    },
212                    trailing_comments: Vec::new(),
213                    double_colon_syntax: false,
214                    format: None,
215                    default: None,
216                    inferred_type: None,
217                })))
218            }
219
220            // Generic function transformations
221            Expression::Function(f) => self.transform_function(*f),
222
223            // Generic aggregate function transformations
224            Expression::AggregateFunction(f) => self.transform_aggregate_function(f),
225
226            // Cast transformations
227            Expression::Cast(c) => self.transform_cast(*c),
228
229            // Pass through everything else
230            _ => Ok(expr),
231        }
232    }
233}
234
235#[cfg(feature = "transpile")]
236impl SingleStoreDialect {
237    fn transform_function(&self, f: Function) -> Result<Expression> {
238        let name_upper = f.name.to_uppercase();
239        match name_upper.as_str() {
240            // NVL -> IFNULL
241            "NVL" if f.args.len() == 2 => Ok(Expression::Function(Box::new(Function::new(
242                "IFNULL".to_string(),
243                f.args,
244            )))),
245
246            // COALESCE is native in SingleStore
247            "COALESCE" => Ok(Expression::Coalesce(Box::new(VarArgFunc {
248                original_name: None,
249                expressions: f.args,
250                inferred_type: None,
251            }))),
252
253            // NOW is native in SingleStore - preserve as function
254            "NOW" => Ok(Expression::Function(Box::new(f))),
255
256            // GETDATE -> NOW
257            "GETDATE" => Ok(Expression::Function(Box::new(Function::new(
258                "NOW".to_string(),
259                f.args,
260            )))),
261
262            // GROUP_CONCAT is native in SingleStore
263            "GROUP_CONCAT" => Ok(Expression::Function(Box::new(f))),
264
265            // STRING_AGG -> GROUP_CONCAT
266            "STRING_AGG" if !f.args.is_empty() => Ok(Expression::Function(Box::new(
267                Function::new("GROUP_CONCAT".to_string(), f.args),
268            ))),
269
270            // LISTAGG -> GROUP_CONCAT
271            "LISTAGG" if !f.args.is_empty() => Ok(Expression::Function(Box::new(Function::new(
272                "GROUP_CONCAT".to_string(),
273                f.args,
274            )))),
275
276            // SUBSTR is native in SingleStore
277            "SUBSTR" => Ok(Expression::Function(Box::new(f))),
278
279            // SUBSTRING is native in SingleStore
280            "SUBSTRING" => Ok(Expression::Function(Box::new(f))),
281
282            // LENGTH is native in SingleStore
283            "LENGTH" => Ok(Expression::Function(Box::new(f))),
284
285            // LEN -> LENGTH
286            "LEN" if f.args.len() == 1 => Ok(Expression::Function(Box::new(Function::new(
287                "LENGTH".to_string(),
288                f.args,
289            )))),
290
291            // CHARINDEX -> INSTR (with swapped args)
292            "CHARINDEX" if f.args.len() >= 2 => {
293                let mut args = f.args;
294                let substring = args.remove(0);
295                let string = args.remove(0);
296                Ok(Expression::Function(Box::new(Function::new(
297                    "INSTR".to_string(),
298                    vec![string, substring],
299                ))))
300            }
301
302            // STRPOS -> INSTR
303            "STRPOS" if f.args.len() >= 2 => Ok(Expression::Function(Box::new(Function::new(
304                "INSTR".to_string(),
305                f.args,
306            )))),
307
308            // CURTIME -> CURRENT_TIME
309            "CURTIME" => Ok(Expression::CurrentTime(crate::expressions::CurrentTime {
310                precision: None,
311            })),
312
313            // LOCATE is native in SingleStore
314            "LOCATE" => Ok(Expression::Function(Box::new(f))),
315
316            // INSTR is native in SingleStore
317            "INSTR" => Ok(Expression::Function(Box::new(f))),
318
319            // DATE_FORMAT is native in SingleStore
320            "DATE_FORMAT" => Ok(Expression::Function(Box::new(f))),
321
322            // strftime -> DATE_FORMAT
323            "STRFTIME" if f.args.len() >= 2 => {
324                let mut args = f.args;
325                let format = args.remove(0);
326                let date = args.remove(0);
327                Ok(Expression::Function(Box::new(Function::new(
328                    "DATE_FORMAT".to_string(),
329                    vec![date, format],
330                ))))
331            }
332
333            // TO_CHAR is native in SingleStore - preserve as function
334            "TO_CHAR" => Ok(Expression::Function(Box::new(f))),
335
336            // TO_DATE is native in SingleStore
337            "TO_DATE" => Ok(Expression::Function(Box::new(f))),
338
339            // TO_TIMESTAMP is native in SingleStore
340            "TO_TIMESTAMP" => Ok(Expression::Function(Box::new(f))),
341
342            // JSON_EXTRACT_JSON is native in SingleStore
343            "JSON_EXTRACT_JSON" => Ok(Expression::Function(Box::new(f))),
344
345            // JSON_EXTRACT -> JSON_EXTRACT_JSON
346            "JSON_EXTRACT" if f.args.len() >= 2 => Ok(Expression::Function(Box::new(
347                Function::new("JSON_EXTRACT_JSON".to_string(), f.args),
348            ))),
349
350            // GET_JSON_OBJECT -> JSON_EXTRACT_STRING
351            "GET_JSON_OBJECT" if f.args.len() == 2 => Ok(Expression::Function(Box::new(
352                Function::new("JSON_EXTRACT_STRING".to_string(), f.args),
353            ))),
354
355            // REGEXP_LIKE -> RLIKE
356            "REGEXP_LIKE" if f.args.len() >= 2 => Ok(Expression::Function(Box::new(
357                Function::new("RLIKE".to_string(), f.args),
358            ))),
359
360            // RLIKE is native in SingleStore
361            "RLIKE" => Ok(Expression::Function(Box::new(f))),
362
363            // TIME_BUCKET is native in SingleStore
364            "TIME_BUCKET" => Ok(Expression::Function(Box::new(f))),
365
366            // DATE_BIN -> TIME_BUCKET
367            "DATE_BIN" if f.args.len() >= 2 => Ok(Expression::Function(Box::new(Function::new(
368                "TIME_BUCKET".to_string(),
369                f.args,
370            )))),
371
372            // TIME_FORMAT -> DATE_FORMAT with cast to TIME(6)
373            // TIME_FORMAT(date, fmt) -> DATE_FORMAT(date :> TIME(6), fmt)
374            "TIME_FORMAT" if f.args.len() == 2 => {
375                let mut args = f.args;
376                let date = args.remove(0);
377                let format = args.remove(0);
378                // Cast date to TIME(6)
379                let cast_to_time = Expression::Cast(Box::new(Cast {
380                    this: date,
381                    to: DataType::Time {
382                        precision: Some(6),
383                        timezone: false,
384                    },
385                    trailing_comments: Vec::new(),
386                    double_colon_syntax: false,
387                    format: None,
388                    default: None,
389                    inferred_type: None,
390                }));
391                Ok(Expression::Function(Box::new(Function::new(
392                    "DATE_FORMAT".to_string(),
393                    vec![cast_to_time, format],
394                ))))
395            }
396
397            // DAYNAME -> DATE_FORMAT with '%W'
398            "DAYNAME" if f.args.len() == 1 => {
399                let date = f.args.into_iter().next().unwrap();
400                Ok(Expression::Function(Box::new(Function::new(
401                    "DATE_FORMAT".to_string(),
402                    vec![date, Expression::string("%W")],
403                ))))
404            }
405
406            // MONTHNAME -> DATE_FORMAT with '%M'
407            "MONTHNAME" if f.args.len() == 1 => {
408                let date = f.args.into_iter().next().unwrap();
409                Ok(Expression::Function(Box::new(Function::new(
410                    "DATE_FORMAT".to_string(),
411                    vec![date, Expression::string("%M")],
412                ))))
413            }
414
415            // HOUR -> DATE_FORMAT with '%k' :> INT
416            "HOUR" if f.args.len() == 1 => {
417                let date = f.args.into_iter().next().unwrap();
418                // Cast date to TIME(6) first
419                let cast_to_time = Expression::Cast(Box::new(Cast {
420                    this: date,
421                    to: DataType::Time {
422                        precision: Some(6),
423                        timezone: false,
424                    },
425                    trailing_comments: Vec::new(),
426                    double_colon_syntax: false,
427                    format: None,
428                    default: None,
429                    inferred_type: None,
430                }));
431                // DATE_FORMAT(... :> TIME(6), '%k')
432                let date_format = Expression::Function(Box::new(Function::new(
433                    "DATE_FORMAT".to_string(),
434                    vec![cast_to_time, Expression::string("%k")],
435                )));
436                // Cast result to INT
437                Ok(Expression::Cast(Box::new(Cast {
438                    this: date_format,
439                    to: DataType::Int {
440                        length: None,
441                        integer_spelling: false,
442                    },
443                    trailing_comments: Vec::new(),
444                    double_colon_syntax: false,
445                    format: None,
446                    default: None,
447                    inferred_type: None,
448                })))
449            }
450
451            // MINUTE -> DATE_FORMAT with '%i' :> INT
452            "MINUTE" if f.args.len() == 1 => {
453                let date = f.args.into_iter().next().unwrap();
454                // Cast date to TIME(6) first
455                let cast_to_time = Expression::Cast(Box::new(Cast {
456                    this: date,
457                    to: DataType::Time {
458                        precision: Some(6),
459                        timezone: false,
460                    },
461                    trailing_comments: Vec::new(),
462                    double_colon_syntax: false,
463                    format: None,
464                    default: None,
465                    inferred_type: None,
466                }));
467                // DATE_FORMAT(... :> TIME(6), '%i')
468                let date_format = Expression::Function(Box::new(Function::new(
469                    "DATE_FORMAT".to_string(),
470                    vec![cast_to_time, Expression::string("%i")],
471                )));
472                // Cast result to INT
473                Ok(Expression::Cast(Box::new(Cast {
474                    this: date_format,
475                    to: DataType::Int {
476                        length: None,
477                        integer_spelling: false,
478                    },
479                    trailing_comments: Vec::new(),
480                    double_colon_syntax: false,
481                    format: None,
482                    default: None,
483                    inferred_type: None,
484                })))
485            }
486
487            // SECOND -> DATE_FORMAT with '%s' :> INT
488            "SECOND" if f.args.len() == 1 => {
489                let date = f.args.into_iter().next().unwrap();
490                // Cast date to TIME(6) first
491                let cast_to_time = Expression::Cast(Box::new(Cast {
492                    this: date,
493                    to: DataType::Time {
494                        precision: Some(6),
495                        timezone: false,
496                    },
497                    trailing_comments: Vec::new(),
498                    double_colon_syntax: false,
499                    format: None,
500                    default: None,
501                    inferred_type: None,
502                }));
503                // DATE_FORMAT(... :> TIME(6), '%s')
504                let date_format = Expression::Function(Box::new(Function::new(
505                    "DATE_FORMAT".to_string(),
506                    vec![cast_to_time, Expression::string("%s")],
507                )));
508                // Cast result to INT
509                Ok(Expression::Cast(Box::new(Cast {
510                    this: date_format,
511                    to: DataType::Int {
512                        length: None,
513                        integer_spelling: false,
514                    },
515                    trailing_comments: Vec::new(),
516                    double_colon_syntax: false,
517                    format: None,
518                    default: None,
519                    inferred_type: None,
520                })))
521            }
522
523            // MICROSECOND -> DATE_FORMAT with '%f' :> INT
524            "MICROSECOND" if f.args.len() == 1 => {
525                let date = f.args.into_iter().next().unwrap();
526                // Cast date to TIME(6) first
527                let cast_to_time = Expression::Cast(Box::new(Cast {
528                    this: date,
529                    to: DataType::Time {
530                        precision: Some(6),
531                        timezone: false,
532                    },
533                    trailing_comments: Vec::new(),
534                    double_colon_syntax: false,
535                    format: None,
536                    default: None,
537                    inferred_type: None,
538                }));
539                // DATE_FORMAT(... :> TIME(6), '%f')
540                let date_format = Expression::Function(Box::new(Function::new(
541                    "DATE_FORMAT".to_string(),
542                    vec![cast_to_time, Expression::string("%f")],
543                )));
544                // Cast result to INT
545                Ok(Expression::Cast(Box::new(Cast {
546                    this: date_format,
547                    to: DataType::Int {
548                        length: None,
549                        integer_spelling: false,
550                    },
551                    trailing_comments: Vec::new(),
552                    double_colon_syntax: false,
553                    format: None,
554                    default: None,
555                    inferred_type: None,
556                })))
557            }
558
559            // WEEKDAY -> (DAYOFWEEK(...) + 5) % 7
560            "WEEKDAY" if f.args.len() == 1 => {
561                let date = f.args.into_iter().next().unwrap();
562                // DAYOFWEEK(date)
563                let dayofweek = Expression::Function(Box::new(Function::new(
564                    "DAYOFWEEK".to_string(),
565                    vec![date],
566                )));
567                // (DAYOFWEEK(date) + 5) - wrap in explicit parentheses
568                let add_five =
569                    Expression::Add(Box::new(BinaryOp::new(dayofweek, Expression::number(5))));
570                let add_five_paren = Expression::Paren(Box::new(Paren {
571                    this: add_five,
572                    trailing_comments: Vec::new(),
573                }));
574                // (DAYOFWEEK(date) + 5) % 7
575                Ok(Expression::Mod(Box::new(BinaryOp::new(
576                    add_five_paren,
577                    Expression::number(7),
578                ))))
579            }
580
581            // Pass through everything else
582            _ => Ok(Expression::Function(Box::new(f))),
583        }
584    }
585
586    fn transform_aggregate_function(
587        &self,
588        f: Box<crate::expressions::AggregateFunction>,
589    ) -> Result<Expression> {
590        let name_upper = f.name.to_uppercase();
591        match name_upper.as_str() {
592            // COUNT_IF -> SUM(CASE WHEN...)
593            "COUNT_IF" if !f.args.is_empty() => {
594                let condition = f.args.into_iter().next().unwrap();
595                let case_expr = Expression::Case(Box::new(Case {
596                    operand: None,
597                    whens: vec![(condition, Expression::number(1))],
598                    else_: Some(Expression::number(0)),
599                    comments: Vec::new(),
600                    inferred_type: None,
601                }));
602                Ok(Expression::Sum(Box::new(AggFunc {
603                    ignore_nulls: None,
604                    having_max: None,
605                    this: case_expr,
606                    distinct: f.distinct,
607                    filter: f.filter,
608                    order_by: Vec::new(),
609                    name: None,
610                    limit: None,
611                    inferred_type: None,
612                })))
613            }
614
615            // APPROX_COUNT_DISTINCT is native in SingleStore
616            "APPROX_COUNT_DISTINCT" => Ok(Expression::AggregateFunction(f)),
617
618            // HLL -> APPROX_COUNT_DISTINCT
619            "HLL" if !f.args.is_empty() => Ok(Expression::Function(Box::new(Function::new(
620                "APPROX_COUNT_DISTINCT".to_string(),
621                f.args,
622            )))),
623
624            // VARIANCE -> VAR_SAMP
625            "VARIANCE" if !f.args.is_empty() => Ok(Expression::Function(Box::new(Function::new(
626                "VAR_SAMP".to_string(),
627                f.args,
628            )))),
629
630            // VAR_POP is native in SingleStore
631            "VAR_POP" => Ok(Expression::AggregateFunction(f)),
632
633            // VAR_SAMP is native in SingleStore
634            "VAR_SAMP" => Ok(Expression::AggregateFunction(f)),
635
636            // Pass through everything else
637            _ => Ok(Expression::AggregateFunction(f)),
638        }
639    }
640
641    fn transform_cast(&self, c: Cast) -> Result<Expression> {
642        // SingleStore type mappings are handled in the generator
643        Ok(Expression::Cast(Box::new(c)))
644    }
645}
polyglot_sql/dialects/singlestore.rs

polyglot_sql/dialects/
singlestore.rs
