polyglot_sql/dialects/
clickhouse.rs

1//! ClickHouse Dialect
2//!
3//! ClickHouse-specific transformations based on sqlglot patterns.
4//! ClickHouse is case-sensitive and has unique function naming conventions.
5
6use super::{DialectImpl, DialectType};
7use crate::error::Result;
8use crate::expressions::{
9    AggregateFunction, BinaryOp, Case, Cast, Expression, Function, In, IsNull, LikeOp,
10    MapConstructor, Paren, UnaryOp,
11};
12use crate::generator::GeneratorConfig;
13use crate::tokens::TokenizerConfig;
14
15/// ClickHouse dialect
16pub struct ClickHouseDialect;
17
18impl DialectImpl for ClickHouseDialect {
19    fn dialect_type(&self) -> DialectType {
20        DialectType::ClickHouse
21    }
22
23    fn tokenizer_config(&self) -> TokenizerConfig {
24        let mut config = TokenizerConfig::default();
25        // ClickHouse uses double quotes and backticks for identifiers
26        config.identifiers.insert('"', '"');
27        config.identifiers.insert('`', '`');
28        // ClickHouse supports nested comments
29        config.nested_comments = true;
30        // ClickHouse allows identifiers to start with digits
31        config.identifiers_can_start_with_digit = true;
32        // ClickHouse uses backslash escaping in strings
33        config.string_escapes.push('\\');
34        // ClickHouse supports # as single-line comment
35        config.hash_comments = true;
36        // ClickHouse allows $ in identifiers
37        config.dollar_sign_is_identifier = true;
38        // ClickHouse: INSERT ... FORMAT <name> is followed by raw data
39        config.insert_format_raw_data = true;
40        // ClickHouse supports 0xDEADBEEF hex integer literals
41        config.hex_number_strings = true;
42        config.hex_string_is_integer_type = true;
43        config
44    }
45
46    fn generator_config(&self) -> GeneratorConfig {
47        use crate::generator::{IdentifierQuoteStyle, NormalizeFunctions};
48        GeneratorConfig {
49            identifier_quote: '"',
50            identifier_quote_style: IdentifierQuoteStyle::DOUBLE_QUOTE,
51            dialect: Some(DialectType::ClickHouse),
52            // ClickHouse uses uppercase keywords (matching Python SQLGlot behavior)
53            uppercase_keywords: true,
54            // ClickHouse function names are case-sensitive and typically camelCase
55            normalize_functions: NormalizeFunctions::None,
56            // ClickHouse identifiers are case-sensitive
57            case_sensitive_identifiers: true,
58            tablesample_keywords: "SAMPLE",
59            tablesample_requires_parens: false,
60            identifiers_can_start_with_digit: true,
61            // ClickHouse uses bracket-only notation for arrays: [1, 2, 3]
62            array_bracket_only: true,
63            ..Default::default()
64        }
65    }
66
67    fn transform_expr(&self, expr: Expression) -> Result<Expression> {
68        let wrap_predicate_left = |expr: Expression| -> Expression {
69            let needs_parens = matches!(
70                expr,
71                Expression::Add(_)
72                    | Expression::Sub(_)
73                    | Expression::Mul(_)
74                    | Expression::Div(_)
75                    | Expression::Mod(_)
76                    | Expression::Concat(_)
77                    | Expression::And(_)
78                    | Expression::Or(_)
79                    | Expression::Not(_)
80                    | Expression::Case(_)
81            );
82
83            if needs_parens {
84                Expression::Paren(Box::new(Paren {
85                    this: expr,
86                    trailing_comments: Vec::new(),
87                }))
88            } else {
89                expr
90            }
91        };
92
93        let wrap_not_target = |expr: Expression| -> Expression {
94            match expr {
95                Expression::Paren(_) => expr,
96                Expression::In(_)
97                | Expression::Between(_)
98                | Expression::Is(_)
99                | Expression::IsNull(_)
100                | Expression::IsTrue(_)
101                | Expression::IsFalse(_)
102                | Expression::IsJson(_)
103                | Expression::Like(_)
104                | Expression::ILike(_)
105                | Expression::SimilarTo(_)
106                | Expression::Glob(_)
107                | Expression::RegexpLike(_)
108                | Expression::RegexpILike(_)
109                | Expression::MemberOf(_) => Expression::Paren(Box::new(Paren {
110                    this: expr,
111                    trailing_comments: Vec::new(),
112                })),
113                _ => expr,
114            }
115        };
116
117        let unwrap_in_array = |mut expressions: Vec<Expression>,
118                               query: &Option<Expression>,
119                               unnest: &Option<Box<Expression>>|
120         -> Vec<Expression> {
121            if query.is_none() && unnest.is_none() && expressions.len() == 1 {
122                if matches!(expressions[0], Expression::ArrayFunc(_)) {
123                    if let Expression::ArrayFunc(arr) = expressions.remove(0) {
124                        return arr.expressions;
125                    }
126                }
127            }
128            expressions
129        };
130
131        match expr {
132            // TryCast stays as TryCast (ClickHouse doesn't have TRY_CAST by default)
133            // But we can emulate with toXOrNull functions
134            Expression::TryCast(c) => {
135                // For simplicity, just use regular CAST
136                // ClickHouse has toXOrNull/toXOrZero functions for safe casts
137                Ok(Expression::Cast(c))
138            }
139
140            // SafeCast -> CAST in ClickHouse
141            Expression::SafeCast(c) => Ok(Expression::Cast(c)),
142
143            // CountIf is native in ClickHouse (lowercase)
144            Expression::CountIf(f) => Ok(Expression::Function(Box::new(Function::new(
145                "countIf".to_string(),
146                vec![f.this],
147            )))),
148
149            // UNNEST -> arrayJoin in ClickHouse
150            Expression::Unnest(f) => Ok(Expression::Function(Box::new(Function::new(
151                "arrayJoin".to_string(),
152                vec![f.this],
153            )))),
154
155            // EXPLODE -> arrayJoin in ClickHouse
156            Expression::Explode(f) => Ok(Expression::Function(Box::new(Function::new(
157                "arrayJoin".to_string(),
158                vec![f.this],
159            )))),
160
161            // ExplodeOuter -> arrayJoin in ClickHouse
162            Expression::ExplodeOuter(f) => Ok(Expression::Function(Box::new(Function::new(
163                "arrayJoin".to_string(),
164                vec![f.this],
165            )))),
166
167            // RAND -> randCanonical() in ClickHouse
168            Expression::Rand(_) => Ok(Expression::Function(Box::new(Function::new(
169                "randCanonical".to_string(),
170                vec![],
171            )))),
172
173            // Random -> randCanonical() in ClickHouse
174            Expression::Random(_) => Ok(Expression::Function(Box::new(Function::new(
175                "randCanonical".to_string(),
176                vec![],
177            )))),
178
179            // startsWith -> startsWith
180            Expression::StartsWith(f) => Ok(Expression::Function(Box::new(Function::new(
181                "startsWith".to_string(),
182                vec![f.this, f.expression],
183            )))),
184
185            // endsWith -> endsWith
186            Expression::EndsWith(f) => Ok(Expression::Function(Box::new(Function::new(
187                "endsWith".to_string(),
188                vec![f.this, f.expression],
189            )))),
190
191            // ClickHouse prefers NOT (x IN (...)) over x NOT IN (...)
192            Expression::In(in_expr) if in_expr.not => {
193                if in_expr.global {
194                    return Ok(Expression::In(in_expr));
195                }
196                let In {
197                    this,
198                    expressions,
199                    query,
200                    unnest,
201                    global,
202                    is_field,
203                    ..
204                } = *in_expr;
205                let expressions = unwrap_in_array(expressions, &query, &unnest);
206                let base = Expression::In(Box::new(In {
207                    this: wrap_predicate_left(this),
208                    expressions,
209                    query,
210                    not: false,
211                    global,
212                    unnest,
213                    is_field,
214                }));
215                Ok(Expression::Not(Box::new(UnaryOp {
216                    this: wrap_not_target(base),
217                    inferred_type: None,
218                })))
219            }
220
221            // ClickHouse prefers NOT (x IS NULL) over x IS NOT NULL
222            Expression::IsNull(is_null) if is_null.not => {
223                let IsNull { this, .. } = *is_null;
224                let base = Expression::IsNull(Box::new(IsNull {
225                    this: wrap_predicate_left(this),
226                    not: false,
227                    postfix_form: false,
228                }));
229                Ok(Expression::Not(Box::new(UnaryOp {
230                    this: wrap_not_target(base),
231                    inferred_type: None,
232                })))
233            }
234
235            Expression::In(mut in_expr) => {
236                in_expr.expressions =
237                    unwrap_in_array(in_expr.expressions, &in_expr.query, &in_expr.unnest);
238                in_expr.this = wrap_predicate_left(in_expr.this);
239                Ok(Expression::In(in_expr))
240            }
241
242            Expression::IsNull(mut is_null) => {
243                is_null.this = wrap_predicate_left(is_null.this);
244                Ok(Expression::IsNull(is_null))
245            }
246
247            // IF(cond, true, false) -> CASE WHEN cond THEN true ELSE false END
248            Expression::IfFunc(f) => {
249                let f = *f;
250                Ok(Expression::Case(Box::new(Case {
251                    operand: None,
252                    whens: vec![(f.condition, f.true_value)],
253                    else_: f.false_value,
254                    comments: Vec::new(),
255                    inferred_type: None,
256                })))
257            }
258
259            Expression::Is(mut is_expr) => {
260                is_expr.left = wrap_predicate_left(is_expr.left);
261                Ok(Expression::Is(is_expr))
262            }
263
264            Expression::Or(op) => {
265                let BinaryOp {
266                    left,
267                    right,
268                    left_comments,
269                    operator_comments,
270                    trailing_comments,
271                    ..
272                } = *op;
273                let left = if matches!(left, Expression::And(_)) {
274                    Expression::Paren(Box::new(Paren {
275                        this: left,
276                        trailing_comments: Vec::new(),
277                    }))
278                } else {
279                    left
280                };
281                let right = if matches!(right, Expression::And(_)) {
282                    Expression::Paren(Box::new(Paren {
283                        this: right,
284                        trailing_comments: Vec::new(),
285                    }))
286                } else {
287                    right
288                };
289                Ok(Expression::Or(Box::new(BinaryOp {
290                    left,
291                    right,
292                    left_comments,
293                    operator_comments,
294                    trailing_comments,
295                    inferred_type: None,
296                })))
297            }
298
299            Expression::Not(op) => {
300                let inner = wrap_not_target(op.this);
301                Ok(Expression::Not(Box::new(UnaryOp {
302                    this: inner,
303                    inferred_type: None,
304                })))
305            }
306
307            Expression::MapFunc(map) if map.curly_brace_syntax => {
308                let MapConstructor { keys, values, .. } = *map;
309                let mut args = Vec::with_capacity(keys.len() * 2);
310                for (key, value) in keys.into_iter().zip(values.into_iter()) {
311                    args.push(key);
312                    args.push(value);
313                }
314                Ok(Expression::Function(Box::new(Function::new(
315                    "map".to_string(),
316                    args,
317                ))))
318            }
319
320            Expression::Insert(mut insert) => {
321                for row in insert.values.iter_mut() {
322                    for value in row.iter_mut() {
323                        if !matches!(value, Expression::Paren(_)) {
324                            let wrapped = Expression::Paren(Box::new(Paren {
325                                this: value.clone(),
326                                trailing_comments: Vec::new(),
327                            }));
328                            *value = wrapped;
329                        }
330                    }
331                }
332                Ok(Expression::Insert(insert))
333            }
334
335            // Generic function transformations
336            Expression::Function(f) => self.transform_function(*f),
337
338            // Generic aggregate function transformations
339            Expression::AggregateFunction(f) => self.transform_aggregate_function(f),
340
341            // Cast transformations
342            Expression::Cast(c) => self.transform_cast(*c),
343
344            // TYPEOF -> toTypeName in ClickHouse
345            Expression::Typeof(f) => Ok(Expression::Function(Box::new(Function::new(
346                "toTypeName".to_string(),
347                vec![f.this],
348            )))),
349
350            // Pass through everything else
351            _ => Ok(expr),
352        }
353    }
354}
355
356impl ClickHouseDialect {
357    fn transform_function(&self, f: Function) -> Result<Expression> {
358        let name_upper = f.name.to_uppercase();
359        match name_upper.as_str() {
360            "CURRENTDATABASE" | "CURRENT_DATABASE" => Ok(Expression::Function(Box::new(
361                Function::new("CURRENT_DATABASE".to_string(), f.args),
362            ))),
363            "CURRENTSCHEMAS" | "CURRENT_SCHEMAS" => Ok(Expression::Function(Box::new(
364                Function::new("CURRENT_SCHEMAS".to_string(), f.args),
365            ))),
366            "LEVENSHTEIN" | "LEVENSHTEINDISTANCE" | "EDITDISTANCE" => Ok(Expression::Function(
367                Box::new(Function::new("editDistance".to_string(), f.args)),
368            )),
369            "CHAR" | "CHR" => Ok(Expression::Function(Box::new(Function::new(
370                "CHAR".to_string(),
371                f.args,
372            )))),
373            "STR_TO_DATE" => Ok(Expression::Function(Box::new(Function::new(
374                "STR_TO_DATE".to_string(),
375                f.args,
376            )))),
377            "JSONEXTRACTSTRING" => Ok(Expression::Function(Box::new(Function::new(
378                "JSONExtractString".to_string(),
379                f.args,
380            )))),
381            "MATCH" => Ok(Expression::Function(Box::new(Function::new(
382                "match".to_string(),
383                f.args,
384            )))),
385            "LIKE" if f.args.len() == 2 => {
386                let left = f.args[0].clone();
387                let right = f.args[1].clone();
388                Ok(Expression::Like(Box::new(LikeOp::new(left, right))))
389            }
390            "NOTLIKE" if f.args.len() == 2 => {
391                let left = f.args[0].clone();
392                let right = f.args[1].clone();
393                let like = Expression::Like(Box::new(LikeOp::new(left, right)));
394                Ok(Expression::Not(Box::new(UnaryOp {
395                    this: like,
396                    inferred_type: None,
397                })))
398            }
399            "ILIKE" if f.args.len() == 2 => {
400                let left = f.args[0].clone();
401                let right = f.args[1].clone();
402                Ok(Expression::ILike(Box::new(LikeOp::new(left, right))))
403            }
404            "AND" if f.args.len() >= 2 => {
405                let mut iter = f.args.into_iter();
406                let mut expr = iter.next().unwrap();
407                for arg in iter {
408                    expr = Expression::And(Box::new(BinaryOp::new(expr, arg)));
409                }
410                Ok(expr)
411            }
412            "OR" if f.args.len() >= 2 => {
413                let mut iter = f.args.into_iter();
414                let mut expr = iter.next().unwrap();
415                for arg in iter {
416                    expr = Expression::Or(Box::new(BinaryOp::new(expr, arg)));
417                }
418                self.transform_expr(expr)
419            }
420            // TYPEOF -> toTypeName in ClickHouse
421            "TYPEOF" => Ok(Expression::Function(Box::new(Function::new(
422                "toTypeName".to_string(),
423                f.args,
424            )))),
425
426            // DATE_TRUNC: ClickHouse uses dateTrunc (camelCase)
427            "DATE_TRUNC" | "DATETRUNC" => Ok(Expression::Function(Box::new(Function::new(
428                "dateTrunc".to_string(),
429                f.args,
430            )))),
431            "TOSTARTOFDAY" if f.args.len() == 1 => {
432                Ok(Expression::Function(Box::new(Function::new(
433                    "dateTrunc".to_string(),
434                    vec![Expression::string("DAY"), f.args[0].clone()],
435                ))))
436            }
437
438            // SUBSTRING_INDEX: preserve original case (substringIndex in ClickHouse)
439            "SUBSTRING_INDEX" => Ok(Expression::Function(Box::new(Function::new(
440                f.name.clone(),
441                f.args,
442            )))),
443
444            // IS_NAN / ISNAN -> isNaN (ClickHouse camelCase)
445            "IS_NAN" | "ISNAN" => Ok(Expression::Function(Box::new(Function::new(
446                "isNaN".to_string(),
447                f.args,
448            )))),
449
450            _ => Ok(Expression::Function(Box::new(f))),
451        }
452    }
453
454    fn transform_aggregate_function(
455        &self,
456        f: Box<crate::expressions::AggregateFunction>,
457    ) -> Result<Expression> {
458        let name_upper = f.name.to_uppercase();
459        match name_upper.as_str() {
460            // COUNT_IF -> countIf
461            "COUNT_IF" if !f.args.is_empty() => Ok(Expression::Function(Box::new(Function::new(
462                "countIf".to_string(),
463                f.args,
464            )))),
465
466            // SUM_IF -> sumIf
467            "SUM_IF" if !f.args.is_empty() => Ok(Expression::Function(Box::new(Function::new(
468                "sumIf".to_string(),
469                f.args,
470            )))),
471
472            // AVG_IF -> avgIf
473            "AVG_IF" if !f.args.is_empty() => Ok(Expression::Function(Box::new(Function::new(
474                "avgIf".to_string(),
475                f.args,
476            )))),
477
478            // ANY_VALUE -> any in ClickHouse
479            "ANY_VALUE" if !f.args.is_empty() => Ok(Expression::Function(Box::new(Function::new(
480                "any".to_string(),
481                f.args,
482            )))),
483
484            // GROUP_CONCAT -> groupArray + arrayStringConcat
485            "GROUP_CONCAT" if !f.args.is_empty() => {
486                let mut args = f.args;
487                let first = args.remove(0);
488                let separator = args.pop();
489                let group_array = Expression::Function(Box::new(Function::new(
490                    "groupArray".to_string(),
491                    vec![first],
492                )));
493                if let Some(sep) = separator {
494                    Ok(Expression::Function(Box::new(Function::new(
495                        "arrayStringConcat".to_string(),
496                        vec![group_array, sep],
497                    ))))
498                } else {
499                    Ok(Expression::Function(Box::new(Function::new(
500                        "arrayStringConcat".to_string(),
501                        vec![group_array],
502                    ))))
503                }
504            }
505
506            // STRING_AGG -> groupArray + arrayStringConcat
507            "STRING_AGG" if !f.args.is_empty() => {
508                let mut args = f.args;
509                let first = args.remove(0);
510                let separator = args.pop();
511                let group_array = Expression::Function(Box::new(Function::new(
512                    "groupArray".to_string(),
513                    vec![first],
514                )));
515                if let Some(sep) = separator {
516                    Ok(Expression::Function(Box::new(Function::new(
517                        "arrayStringConcat".to_string(),
518                        vec![group_array, sep],
519                    ))))
520                } else {
521                    Ok(Expression::Function(Box::new(Function::new(
522                        "arrayStringConcat".to_string(),
523                        vec![group_array],
524                    ))))
525                }
526            }
527
528            // LISTAGG -> groupArray + arrayStringConcat
529            "LISTAGG" if !f.args.is_empty() => {
530                let mut args = f.args;
531                let first = args.remove(0);
532                let separator = args.pop();
533                let group_array = Expression::Function(Box::new(Function::new(
534                    "groupArray".to_string(),
535                    vec![first],
536                )));
537                if let Some(sep) = separator {
538                    Ok(Expression::Function(Box::new(Function::new(
539                        "arrayStringConcat".to_string(),
540                        vec![group_array, sep],
541                    ))))
542                } else {
543                    Ok(Expression::Function(Box::new(Function::new(
544                        "arrayStringConcat".to_string(),
545                        vec![group_array],
546                    ))))
547                }
548            }
549
550            // ARRAY_AGG -> groupArray
551            "ARRAY_AGG" if !f.args.is_empty() => Ok(Expression::Function(Box::new(Function::new(
552                "groupArray".to_string(),
553                f.args,
554            )))),
555
556            // STDDEV -> stddevSamp in ClickHouse (sample stddev)
557            "STDDEV" if !f.args.is_empty() => {
558                Ok(Expression::AggregateFunction(Box::new(AggregateFunction {
559                    name: "stddevSamp".to_string(),
560                    args: f.args,
561                    distinct: f.distinct,
562                    filter: f.filter,
563                    order_by: Vec::new(),
564                    limit: None,
565                    ignore_nulls: None,
566                    inferred_type: None,
567                })))
568            }
569
570            // STDDEV_POP -> stddevPop
571            "STDDEV_POP" if !f.args.is_empty() => {
572                Ok(Expression::AggregateFunction(Box::new(AggregateFunction {
573                    name: "stddevPop".to_string(),
574                    args: f.args,
575                    distinct: f.distinct,
576                    filter: f.filter,
577                    order_by: Vec::new(),
578                    limit: None,
579                    ignore_nulls: None,
580                    inferred_type: None,
581                })))
582            }
583
584            // VARIANCE -> varSamp in ClickHouse
585            "VARIANCE" if !f.args.is_empty() => {
586                Ok(Expression::AggregateFunction(Box::new(AggregateFunction {
587                    name: "varSamp".to_string(),
588                    args: f.args,
589                    distinct: f.distinct,
590                    filter: f.filter,
591                    order_by: Vec::new(),
592                    limit: None,
593                    ignore_nulls: None,
594                    inferred_type: None,
595                })))
596            }
597
598            // VAR_POP -> varPop
599            "VAR_POP" if !f.args.is_empty() => {
600                Ok(Expression::AggregateFunction(Box::new(AggregateFunction {
601                    name: "varPop".to_string(),
602                    args: f.args,
603                    distinct: f.distinct,
604                    filter: f.filter,
605                    order_by: Vec::new(),
606                    limit: None,
607                    ignore_nulls: None,
608                    inferred_type: None,
609                })))
610            }
611
612            // MEDIAN -> median
613            "MEDIAN" if !f.args.is_empty() => {
614                Ok(Expression::AggregateFunction(Box::new(AggregateFunction {
615                    name: "median".to_string(),
616                    args: f.args,
617                    distinct: f.distinct,
618                    filter: f.filter,
619                    order_by: Vec::new(),
620                    limit: None,
621                    ignore_nulls: None,
622                    inferred_type: None,
623                })))
624            }
625
626            // APPROX_COUNT_DISTINCT -> uniq in ClickHouse
627            "APPROX_COUNT_DISTINCT" if !f.args.is_empty() => Ok(Expression::Function(Box::new(
628                Function::new("uniq".to_string(), f.args),
629            ))),
630
631            // APPROX_DISTINCT -> uniq
632            "APPROX_DISTINCT" if !f.args.is_empty() => Ok(Expression::Function(Box::new(
633                Function::new("uniq".to_string(), f.args),
634            ))),
635
636            _ => Ok(Expression::AggregateFunction(f)),
637        }
638    }
639
640    fn transform_cast(&self, c: Cast) -> Result<Expression> {
641        // ClickHouse type mappings are handled in the generator
642        Ok(Expression::Cast(Box::new(c)))
643    }
644}
polyglot_sql/dialects/clickhouse.rs

polyglot_sql/dialects/
clickhouse.rs
