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

polyglot_sql/dialects/
singlestore.rs
