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            // LOCATE is native in SingleStore
303            "LOCATE" => Ok(Expression::Function(Box::new(f))),
304
305            // INSTR is native in SingleStore
306            "INSTR" => Ok(Expression::Function(Box::new(f))),
307
308            // DATE_FORMAT is native in SingleStore
309            "DATE_FORMAT" => Ok(Expression::Function(Box::new(f))),
310
311            // strftime -> DATE_FORMAT
312            "STRFTIME" if f.args.len() >= 2 => {
313                let mut args = f.args;
314                let format = args.remove(0);
315                let date = args.remove(0);
316                Ok(Expression::Function(Box::new(Function::new(
317                    "DATE_FORMAT".to_string(),
318                    vec![date, format],
319                ))))
320            }
321
322            // TO_CHAR is native in SingleStore - preserve as function
323            "TO_CHAR" => Ok(Expression::Function(Box::new(f))),
324
325            // TO_DATE is native in SingleStore
326            "TO_DATE" => Ok(Expression::Function(Box::new(f))),
327
328            // TO_TIMESTAMP is native in SingleStore
329            "TO_TIMESTAMP" => Ok(Expression::Function(Box::new(f))),
330
331            // JSON_EXTRACT_JSON is native in SingleStore
332            "JSON_EXTRACT_JSON" => Ok(Expression::Function(Box::new(f))),
333
334            // JSON_EXTRACT -> JSON_EXTRACT_JSON
335            "JSON_EXTRACT" if f.args.len() >= 2 => Ok(Expression::Function(Box::new(
336                Function::new("JSON_EXTRACT_JSON".to_string(), f.args),
337            ))),
338
339            // GET_JSON_OBJECT -> JSON_EXTRACT_STRING
340            "GET_JSON_OBJECT" if f.args.len() == 2 => Ok(Expression::Function(Box::new(
341                Function::new("JSON_EXTRACT_STRING".to_string(), f.args),
342            ))),
343
344            // REGEXP_LIKE -> RLIKE
345            "REGEXP_LIKE" if f.args.len() >= 2 => Ok(Expression::Function(Box::new(
346                Function::new("RLIKE".to_string(), f.args),
347            ))),
348
349            // RLIKE is native in SingleStore
350            "RLIKE" => Ok(Expression::Function(Box::new(f))),
351
352            // TIME_BUCKET is native in SingleStore
353            "TIME_BUCKET" => Ok(Expression::Function(Box::new(f))),
354
355            // DATE_BIN -> TIME_BUCKET
356            "DATE_BIN" if f.args.len() >= 2 => Ok(Expression::Function(Box::new(Function::new(
357                "TIME_BUCKET".to_string(),
358                f.args,
359            )))),
360
361            // TIME_FORMAT -> DATE_FORMAT with cast to TIME(6)
362            // TIME_FORMAT(date, fmt) -> DATE_FORMAT(date :> TIME(6), fmt)
363            "TIME_FORMAT" if f.args.len() == 2 => {
364                let mut args = f.args;
365                let date = args.remove(0);
366                let format = args.remove(0);
367                // Cast date to TIME(6)
368                let cast_to_time = Expression::Cast(Box::new(Cast {
369                    this: date,
370                    to: DataType::Time {
371                        precision: Some(6),
372                        timezone: false,
373                    },
374                    trailing_comments: Vec::new(),
375                    double_colon_syntax: false,
376                    format: None,
377                    default: None,
378                    inferred_type: None,
379                }));
380                Ok(Expression::Function(Box::new(Function::new(
381                    "DATE_FORMAT".to_string(),
382                    vec![cast_to_time, format],
383                ))))
384            }
385
386            // DAYNAME -> DATE_FORMAT with '%W'
387            "DAYNAME" if f.args.len() == 1 => {
388                let date = f.args.into_iter().next().unwrap();
389                Ok(Expression::Function(Box::new(Function::new(
390                    "DATE_FORMAT".to_string(),
391                    vec![date, Expression::string("%W")],
392                ))))
393            }
394
395            // MONTHNAME -> DATE_FORMAT with '%M'
396            "MONTHNAME" if f.args.len() == 1 => {
397                let date = f.args.into_iter().next().unwrap();
398                Ok(Expression::Function(Box::new(Function::new(
399                    "DATE_FORMAT".to_string(),
400                    vec![date, Expression::string("%M")],
401                ))))
402            }
403
404            // HOUR -> DATE_FORMAT with '%k' :> INT
405            "HOUR" if f.args.len() == 1 => {
406                let date = f.args.into_iter().next().unwrap();
407                // Cast date to TIME(6) first
408                let cast_to_time = Expression::Cast(Box::new(Cast {
409                    this: date,
410                    to: DataType::Time {
411                        precision: Some(6),
412                        timezone: false,
413                    },
414                    trailing_comments: Vec::new(),
415                    double_colon_syntax: false,
416                    format: None,
417                    default: None,
418                    inferred_type: None,
419                }));
420                // DATE_FORMAT(... :> TIME(6), '%k')
421                let date_format = Expression::Function(Box::new(Function::new(
422                    "DATE_FORMAT".to_string(),
423                    vec![cast_to_time, Expression::string("%k")],
424                )));
425                // Cast result to INT
426                Ok(Expression::Cast(Box::new(Cast {
427                    this: date_format,
428                    to: DataType::Int {
429                        length: None,
430                        integer_spelling: false,
431                    },
432                    trailing_comments: Vec::new(),
433                    double_colon_syntax: false,
434                    format: None,
435                    default: None,
436                    inferred_type: None,
437                })))
438            }
439
440            // MINUTE -> DATE_FORMAT with '%i' :> INT
441            "MINUTE" if f.args.len() == 1 => {
442                let date = f.args.into_iter().next().unwrap();
443                // Cast date to TIME(6) first
444                let cast_to_time = Expression::Cast(Box::new(Cast {
445                    this: date,
446                    to: DataType::Time {
447                        precision: Some(6),
448                        timezone: false,
449                    },
450                    trailing_comments: Vec::new(),
451                    double_colon_syntax: false,
452                    format: None,
453                    default: None,
454                    inferred_type: None,
455                }));
456                // DATE_FORMAT(... :> TIME(6), '%i')
457                let date_format = Expression::Function(Box::new(Function::new(
458                    "DATE_FORMAT".to_string(),
459                    vec![cast_to_time, Expression::string("%i")],
460                )));
461                // Cast result to INT
462                Ok(Expression::Cast(Box::new(Cast {
463                    this: date_format,
464                    to: DataType::Int {
465                        length: None,
466                        integer_spelling: false,
467                    },
468                    trailing_comments: Vec::new(),
469                    double_colon_syntax: false,
470                    format: None,
471                    default: None,
472                    inferred_type: None,
473                })))
474            }
475
476            // SECOND -> DATE_FORMAT with '%s' :> INT
477            "SECOND" if f.args.len() == 1 => {
478                let date = f.args.into_iter().next().unwrap();
479                // Cast date to TIME(6) first
480                let cast_to_time = Expression::Cast(Box::new(Cast {
481                    this: date,
482                    to: DataType::Time {
483                        precision: Some(6),
484                        timezone: false,
485                    },
486                    trailing_comments: Vec::new(),
487                    double_colon_syntax: false,
488                    format: None,
489                    default: None,
490                    inferred_type: None,
491                }));
492                // DATE_FORMAT(... :> TIME(6), '%s')
493                let date_format = Expression::Function(Box::new(Function::new(
494                    "DATE_FORMAT".to_string(),
495                    vec![cast_to_time, Expression::string("%s")],
496                )));
497                // Cast result to INT
498                Ok(Expression::Cast(Box::new(Cast {
499                    this: date_format,
500                    to: DataType::Int {
501                        length: None,
502                        integer_spelling: false,
503                    },
504                    trailing_comments: Vec::new(),
505                    double_colon_syntax: false,
506                    format: None,
507                    default: None,
508                    inferred_type: None,
509                })))
510            }
511
512            // MICROSECOND -> DATE_FORMAT with '%f' :> INT
513            "MICROSECOND" if f.args.len() == 1 => {
514                let date = f.args.into_iter().next().unwrap();
515                // Cast date to TIME(6) first
516                let cast_to_time = Expression::Cast(Box::new(Cast {
517                    this: date,
518                    to: DataType::Time {
519                        precision: Some(6),
520                        timezone: false,
521                    },
522                    trailing_comments: Vec::new(),
523                    double_colon_syntax: false,
524                    format: None,
525                    default: None,
526                    inferred_type: None,
527                }));
528                // DATE_FORMAT(... :> TIME(6), '%f')
529                let date_format = Expression::Function(Box::new(Function::new(
530                    "DATE_FORMAT".to_string(),
531                    vec![cast_to_time, Expression::string("%f")],
532                )));
533                // Cast result to INT
534                Ok(Expression::Cast(Box::new(Cast {
535                    this: date_format,
536                    to: DataType::Int {
537                        length: None,
538                        integer_spelling: false,
539                    },
540                    trailing_comments: Vec::new(),
541                    double_colon_syntax: false,
542                    format: None,
543                    default: None,
544                    inferred_type: None,
545                })))
546            }
547
548            // WEEKDAY -> (DAYOFWEEK(...) + 5) % 7
549            "WEEKDAY" if f.args.len() == 1 => {
550                let date = f.args.into_iter().next().unwrap();
551                // DAYOFWEEK(date)
552                let dayofweek = Expression::Function(Box::new(Function::new(
553                    "DAYOFWEEK".to_string(),
554                    vec![date],
555                )));
556                // (DAYOFWEEK(date) + 5) - wrap in explicit parentheses
557                let add_five =
558                    Expression::Add(Box::new(BinaryOp::new(dayofweek, Expression::number(5))));
559                let add_five_paren = Expression::Paren(Box::new(Paren {
560                    this: add_five,
561                    trailing_comments: Vec::new(),
562                }));
563                // (DAYOFWEEK(date) + 5) % 7
564                Ok(Expression::Mod(Box::new(BinaryOp::new(
565                    add_five_paren,
566                    Expression::number(7),
567                ))))
568            }
569
570            // Pass through everything else
571            _ => Ok(Expression::Function(Box::new(f))),
572        }
573    }
574
575    fn transform_aggregate_function(
576        &self,
577        f: Box<crate::expressions::AggregateFunction>,
578    ) -> Result<Expression> {
579        let name_upper = f.name.to_uppercase();
580        match name_upper.as_str() {
581            // COUNT_IF -> SUM(CASE WHEN...)
582            "COUNT_IF" if !f.args.is_empty() => {
583                let condition = f.args.into_iter().next().unwrap();
584                let case_expr = Expression::Case(Box::new(Case {
585                    operand: None,
586                    whens: vec![(condition, Expression::number(1))],
587                    else_: Some(Expression::number(0)),
588                    comments: Vec::new(),
589                    inferred_type: None,
590                }));
591                Ok(Expression::Sum(Box::new(AggFunc {
592                    ignore_nulls: None,
593                    having_max: None,
594                    this: case_expr,
595                    distinct: f.distinct,
596                    filter: f.filter,
597                    order_by: Vec::new(),
598                    name: None,
599                    limit: None,
600                    inferred_type: None,
601                })))
602            }
603
604            // APPROX_COUNT_DISTINCT is native in SingleStore
605            "APPROX_COUNT_DISTINCT" => Ok(Expression::AggregateFunction(f)),
606
607            // HLL -> APPROX_COUNT_DISTINCT
608            "HLL" if !f.args.is_empty() => Ok(Expression::Function(Box::new(Function::new(
609                "APPROX_COUNT_DISTINCT".to_string(),
610                f.args,
611            )))),
612
613            // VARIANCE -> VAR_SAMP
614            "VARIANCE" if !f.args.is_empty() => Ok(Expression::Function(Box::new(Function::new(
615                "VAR_SAMP".to_string(),
616                f.args,
617            )))),
618
619            // VAR_POP is native in SingleStore
620            "VAR_POP" => Ok(Expression::AggregateFunction(f)),
621
622            // VAR_SAMP is native in SingleStore
623            "VAR_SAMP" => Ok(Expression::AggregateFunction(f)),
624
625            // Pass through everything else
626            _ => Ok(Expression::AggregateFunction(f)),
627        }
628    }
629
630    fn transform_cast(&self, c: Cast) -> Result<Expression> {
631        // SingleStore type mappings are handled in the generator
632        Ok(Expression::Cast(Box::new(c)))
633    }
634}
polyglot_sql/dialects/singlestore.rs

polyglot_sql/dialects/
singlestore.rs
