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                })))
218            }
219
220            // ClickHouse prefers NOT (x IS NULL) over x IS NOT NULL
221            Expression::IsNull(is_null) if is_null.not => {
222                let IsNull { this, .. } = *is_null;
223                let base = Expression::IsNull(Box::new(IsNull {
224                    this: wrap_predicate_left(this),
225                    not: false,
226                    postfix_form: false,
227                }));
228                Ok(Expression::Not(Box::new(UnaryOp {
229                    this: wrap_not_target(base),
230                })))
231            }
232
233            Expression::In(mut in_expr) => {
234                in_expr.expressions =
235                    unwrap_in_array(in_expr.expressions, &in_expr.query, &in_expr.unnest);
236                in_expr.this = wrap_predicate_left(in_expr.this);
237                Ok(Expression::In(in_expr))
238            }
239
240            Expression::IsNull(mut is_null) => {
241                is_null.this = wrap_predicate_left(is_null.this);
242                Ok(Expression::IsNull(is_null))
243            }
244
245            // IF(cond, true, false) -> CASE WHEN cond THEN true ELSE false END
246            Expression::IfFunc(f) => {
247                let f = *f;
248                Ok(Expression::Case(Box::new(Case {
249                    operand: None,
250                    whens: vec![(f.condition, f.true_value)],
251                    else_: f.false_value,
252                    comments: Vec::new(),
253                })))
254            }
255
256            Expression::Is(mut is_expr) => {
257                is_expr.left = wrap_predicate_left(is_expr.left);
258                Ok(Expression::Is(is_expr))
259            }
260
261            Expression::Or(op) => {
262                let BinaryOp {
263                    left,
264                    right,
265                    left_comments,
266                    operator_comments,
267                    trailing_comments,
268                } = *op;
269                let left = if matches!(left, Expression::And(_)) {
270                    Expression::Paren(Box::new(Paren {
271                        this: left,
272                        trailing_comments: Vec::new(),
273                    }))
274                } else {
275                    left
276                };
277                let right = if matches!(right, Expression::And(_)) {
278                    Expression::Paren(Box::new(Paren {
279                        this: right,
280                        trailing_comments: Vec::new(),
281                    }))
282                } else {
283                    right
284                };
285                Ok(Expression::Or(Box::new(BinaryOp {
286                    left,
287                    right,
288                    left_comments,
289                    operator_comments,
290                    trailing_comments,
291                })))
292            }
293
294            Expression::Not(op) => {
295                let inner = wrap_not_target(op.this);
296                Ok(Expression::Not(Box::new(UnaryOp { this: inner })))
297            }
298
299            Expression::MapFunc(map) if map.curly_brace_syntax => {
300                let MapConstructor { keys, values, .. } = *map;
301                let mut args = Vec::with_capacity(keys.len() * 2);
302                for (key, value) in keys.into_iter().zip(values.into_iter()) {
303                    args.push(key);
304                    args.push(value);
305                }
306                Ok(Expression::Function(Box::new(Function::new(
307                    "map".to_string(),
308                    args,
309                ))))
310            }
311
312            Expression::Insert(mut insert) => {
313                for row in insert.values.iter_mut() {
314                    for value in row.iter_mut() {
315                        if !matches!(value, Expression::Paren(_)) {
316                            let wrapped = Expression::Paren(Box::new(Paren {
317                                this: value.clone(),
318                                trailing_comments: Vec::new(),
319                            }));
320                            *value = wrapped;
321                        }
322                    }
323                }
324                Ok(Expression::Insert(insert))
325            }
326
327            // Generic function transformations
328            Expression::Function(f) => self.transform_function(*f),
329
330            // Generic aggregate function transformations
331            Expression::AggregateFunction(f) => self.transform_aggregate_function(f),
332
333            // Cast transformations
334            Expression::Cast(c) => self.transform_cast(*c),
335
336            // TYPEOF -> toTypeName in ClickHouse
337            Expression::Typeof(f) => Ok(Expression::Function(Box::new(Function::new(
338                "toTypeName".to_string(),
339                vec![f.this],
340            )))),
341
342            // Pass through everything else
343            _ => Ok(expr),
344        }
345    }
346}
347
348impl ClickHouseDialect {
349    fn transform_function(&self, f: Function) -> Result<Expression> {
350        let name_upper = f.name.to_uppercase();
351        match name_upper.as_str() {
352            "CURRENTDATABASE" | "CURRENT_DATABASE" => Ok(Expression::Function(Box::new(
353                Function::new("CURRENT_DATABASE".to_string(), f.args),
354            ))),
355            "CURRENTSCHEMAS" | "CURRENT_SCHEMAS" => Ok(Expression::Function(Box::new(
356                Function::new("CURRENT_SCHEMAS".to_string(), f.args),
357            ))),
358            "LEVENSHTEIN" | "LEVENSHTEINDISTANCE" | "EDITDISTANCE" => Ok(Expression::Function(
359                Box::new(Function::new("editDistance".to_string(), f.args)),
360            )),
361            "CHAR" | "CHR" => Ok(Expression::Function(Box::new(Function::new(
362                "CHAR".to_string(),
363                f.args,
364            )))),
365            "STR_TO_DATE" => Ok(Expression::Function(Box::new(Function::new(
366                "STR_TO_DATE".to_string(),
367                f.args,
368            )))),
369            "JSONEXTRACTSTRING" => Ok(Expression::Function(Box::new(Function::new(
370                "JSONExtractString".to_string(),
371                f.args,
372            )))),
373            "MATCH" => Ok(Expression::Function(Box::new(Function::new(
374                "match".to_string(),
375                f.args,
376            )))),
377            "LIKE" if f.args.len() == 2 => {
378                let left = f.args[0].clone();
379                let right = f.args[1].clone();
380                Ok(Expression::Like(Box::new(LikeOp::new(left, right))))
381            }
382            "NOTLIKE" if f.args.len() == 2 => {
383                let left = f.args[0].clone();
384                let right = f.args[1].clone();
385                let like = Expression::Like(Box::new(LikeOp::new(left, right)));
386                Ok(Expression::Not(Box::new(UnaryOp { this: like })))
387            }
388            "ILIKE" if f.args.len() == 2 => {
389                let left = f.args[0].clone();
390                let right = f.args[1].clone();
391                Ok(Expression::ILike(Box::new(LikeOp::new(left, right))))
392            }
393            "AND" if f.args.len() >= 2 => {
394                let mut iter = f.args.into_iter();
395                let mut expr = iter.next().unwrap();
396                for arg in iter {
397                    expr = Expression::And(Box::new(BinaryOp::new(expr, arg)));
398                }
399                Ok(expr)
400            }
401            "OR" if f.args.len() >= 2 => {
402                let mut iter = f.args.into_iter();
403                let mut expr = iter.next().unwrap();
404                for arg in iter {
405                    expr = Expression::Or(Box::new(BinaryOp::new(expr, arg)));
406                }
407                self.transform_expr(expr)
408            }
409            // TYPEOF -> toTypeName in ClickHouse
410            "TYPEOF" => Ok(Expression::Function(Box::new(Function::new(
411                "toTypeName".to_string(),
412                f.args,
413            )))),
414
415            // DATE_TRUNC: ClickHouse uses dateTrunc (camelCase)
416            "DATE_TRUNC" | "DATETRUNC" => Ok(Expression::Function(Box::new(Function::new(
417                "dateTrunc".to_string(),
418                f.args,
419            )))),
420            "TOSTARTOFDAY" if f.args.len() == 1 => {
421                Ok(Expression::Function(Box::new(Function::new(
422                    "dateTrunc".to_string(),
423                    vec![Expression::string("DAY"), f.args[0].clone()],
424                ))))
425            }
426
427            // SUBSTRING_INDEX: preserve original case (substringIndex in ClickHouse)
428            "SUBSTRING_INDEX" => Ok(Expression::Function(Box::new(Function::new(
429                f.name.clone(),
430                f.args,
431            )))),
432
433            // IS_NAN / ISNAN -> isNaN (ClickHouse camelCase)
434            "IS_NAN" | "ISNAN" => Ok(Expression::Function(Box::new(Function::new(
435                "isNaN".to_string(),
436                f.args,
437            )))),
438
439            _ => Ok(Expression::Function(Box::new(f))),
440        }
441    }
442
443    fn transform_aggregate_function(
444        &self,
445        f: Box<crate::expressions::AggregateFunction>,
446    ) -> Result<Expression> {
447        let name_upper = f.name.to_uppercase();
448        match name_upper.as_str() {
449            // COUNT_IF -> countIf
450            "COUNT_IF" if !f.args.is_empty() => Ok(Expression::Function(Box::new(Function::new(
451                "countIf".to_string(),
452                f.args,
453            )))),
454
455            // SUM_IF -> sumIf
456            "SUM_IF" if !f.args.is_empty() => Ok(Expression::Function(Box::new(Function::new(
457                "sumIf".to_string(),
458                f.args,
459            )))),
460
461            // AVG_IF -> avgIf
462            "AVG_IF" if !f.args.is_empty() => Ok(Expression::Function(Box::new(Function::new(
463                "avgIf".to_string(),
464                f.args,
465            )))),
466
467            // ANY_VALUE -> any in ClickHouse
468            "ANY_VALUE" if !f.args.is_empty() => Ok(Expression::Function(Box::new(Function::new(
469                "any".to_string(),
470                f.args,
471            )))),
472
473            // GROUP_CONCAT -> groupArray + arrayStringConcat
474            "GROUP_CONCAT" if !f.args.is_empty() => {
475                let mut args = f.args;
476                let first = args.remove(0);
477                let separator = args.pop();
478                let group_array = Expression::Function(Box::new(Function::new(
479                    "groupArray".to_string(),
480                    vec![first],
481                )));
482                if let Some(sep) = separator {
483                    Ok(Expression::Function(Box::new(Function::new(
484                        "arrayStringConcat".to_string(),
485                        vec![group_array, sep],
486                    ))))
487                } else {
488                    Ok(Expression::Function(Box::new(Function::new(
489                        "arrayStringConcat".to_string(),
490                        vec![group_array],
491                    ))))
492                }
493            }
494
495            // STRING_AGG -> groupArray + arrayStringConcat
496            "STRING_AGG" if !f.args.is_empty() => {
497                let mut args = f.args;
498                let first = args.remove(0);
499                let separator = args.pop();
500                let group_array = Expression::Function(Box::new(Function::new(
501                    "groupArray".to_string(),
502                    vec![first],
503                )));
504                if let Some(sep) = separator {
505                    Ok(Expression::Function(Box::new(Function::new(
506                        "arrayStringConcat".to_string(),
507                        vec![group_array, sep],
508                    ))))
509                } else {
510                    Ok(Expression::Function(Box::new(Function::new(
511                        "arrayStringConcat".to_string(),
512                        vec![group_array],
513                    ))))
514                }
515            }
516
517            // LISTAGG -> groupArray + arrayStringConcat
518            "LISTAGG" if !f.args.is_empty() => {
519                let mut args = f.args;
520                let first = args.remove(0);
521                let separator = args.pop();
522                let group_array = Expression::Function(Box::new(Function::new(
523                    "groupArray".to_string(),
524                    vec![first],
525                )));
526                if let Some(sep) = separator {
527                    Ok(Expression::Function(Box::new(Function::new(
528                        "arrayStringConcat".to_string(),
529                        vec![group_array, sep],
530                    ))))
531                } else {
532                    Ok(Expression::Function(Box::new(Function::new(
533                        "arrayStringConcat".to_string(),
534                        vec![group_array],
535                    ))))
536                }
537            }
538
539            // ARRAY_AGG -> groupArray
540            "ARRAY_AGG" if !f.args.is_empty() => Ok(Expression::Function(Box::new(Function::new(
541                "groupArray".to_string(),
542                f.args,
543            )))),
544
545            // STDDEV -> stddevSamp in ClickHouse (sample stddev)
546            "STDDEV" if !f.args.is_empty() => {
547                Ok(Expression::AggregateFunction(Box::new(AggregateFunction {
548                    name: "stddevSamp".to_string(),
549                    args: f.args,
550                    distinct: f.distinct,
551                    filter: f.filter,
552                    order_by: Vec::new(),
553                    limit: None,
554                    ignore_nulls: None,
555                })))
556            }
557
558            // STDDEV_POP -> stddevPop
559            "STDDEV_POP" if !f.args.is_empty() => {
560                Ok(Expression::AggregateFunction(Box::new(AggregateFunction {
561                    name: "stddevPop".to_string(),
562                    args: f.args,
563                    distinct: f.distinct,
564                    filter: f.filter,
565                    order_by: Vec::new(),
566                    limit: None,
567                    ignore_nulls: None,
568                })))
569            }
570
571            // VARIANCE -> varSamp in ClickHouse
572            "VARIANCE" if !f.args.is_empty() => {
573                Ok(Expression::AggregateFunction(Box::new(AggregateFunction {
574                    name: "varSamp".to_string(),
575                    args: f.args,
576                    distinct: f.distinct,
577                    filter: f.filter,
578                    order_by: Vec::new(),
579                    limit: None,
580                    ignore_nulls: None,
581                })))
582            }
583
584            // VAR_POP -> varPop
585            "VAR_POP" if !f.args.is_empty() => {
586                Ok(Expression::AggregateFunction(Box::new(AggregateFunction {
587                    name: "varPop".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                })))
595            }
596
597            // MEDIAN -> median
598            "MEDIAN" if !f.args.is_empty() => {
599                Ok(Expression::AggregateFunction(Box::new(AggregateFunction {
600                    name: "median".to_string(),
601                    args: f.args,
602                    distinct: f.distinct,
603                    filter: f.filter,
604                    order_by: Vec::new(),
605                    limit: None,
606                    ignore_nulls: None,
607                })))
608            }
609
610            // APPROX_COUNT_DISTINCT -> uniq in ClickHouse
611            "APPROX_COUNT_DISTINCT" if !f.args.is_empty() => Ok(Expression::Function(Box::new(
612                Function::new("uniq".to_string(), f.args),
613            ))),
614
615            // APPROX_DISTINCT -> uniq
616            "APPROX_DISTINCT" if !f.args.is_empty() => Ok(Expression::Function(Box::new(
617                Function::new("uniq".to_string(), f.args),
618            ))),
619
620            _ => Ok(Expression::AggregateFunction(f)),
621        }
622    }
623
624    fn transform_cast(&self, c: Cast) -> Result<Expression> {
625        // ClickHouse type mappings are handled in the generator
626        Ok(Expression::Cast(Box::new(c)))
627    }
628}
polyglot_sql/dialects/clickhouse.rs

polyglot_sql/dialects/
clickhouse.rs
