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

polyglot_sql/dialects/
singlestore.rs
