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

polyglot_sql/dialects/
singlestore.rs
