You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@datafusion.apache.org by gi...@apache.org on 2026/10/12 21:03:24 UTC
(datafusion-sqlparser-rs) 01/01: ClickHouse: Support GLOBAL IN / GLOBAL NOT IN (#2598)
This is an automated email from the ASF dual-hosted git repository.
github-merge-queue[bot] pushed a commit to branch gh-readonly-queue/main/pr-2598-e790703ab91df423fe38d0e52669da0b41b658f2
in repository https://gitbox.apache.org/repos/asf/datafusion-sqlparser-rs.git
commit dacdbc99cd16c335fccd353ba41f6c18a7cd73b8
Author: Dmitri B. <s5...@proton.me>
AuthorDate: Mon Oct 12 21:02:57 2026 +0000
ClickHouse: Support GLOBAL IN / GLOBAL NOT IN (#2598)
---
fuzz/fuzz_seeds/clickhouse_global_in.sql | 63 ++++++++++++++++
src/ast/mod.rs | 16 +++--
src/ast/spans.rs | 2 +
src/dialect/clickhouse.rs | 5 ++
src/dialect/mod.rs | 19 +++++
src/parser/mod.rs | 23 ++++--
tests/sqlparser_clickhouse.rs | 119 +++++++++++++++++++++++++++++++
tests/sqlparser_common.rs | 8 +++
8 files changed, 246 insertions(+), 9 deletions(-)
diff --git a/fuzz/fuzz_seeds/clickhouse_global_in.sql b/fuzz/fuzz_seeds/clickhouse_global_in.sql
new file mode 100644
index 00000000..ba2b6bc6
--- /dev/null
+++ b/fuzz/fuzz_seeds/clickhouse_global_in.sql
@@ -0,0 +1,63 @@
+SELECT * FROM t WHERE x GLOBAL IN (1, 2);
+SELECT * FROM t WHERE x GLOBAL NOT IN (1, 2);
+SELECT * FROM t WHERE x GLOBAL IN (SELECT y FROM t2);
+SELECT * FROM t WHERE x GLOBAL NOT IN (SELECT y FROM t2);
+SELECT * FROM t WHERE x GLOBAL IN t2;
+SELECT * FROM t WHERE x GLOBAL IN (t2);
+SELECT * FROM t WHERE x GLOBAL IN db.t2;
+SELECT * FROM t WHERE x GLOBAL IN t2 AND y = 2;
+SELECT * FROM t WHERE x global in (1) AND y Global Not In (2);
+SELECT * FROM t WHERE x GLOBAL IN (1);
+SELECT * FROM t WHERE x GLOBAL IN (NULL, 1, 'a', 1.5, -1, 1 + 2, y * 2);
+SELECT * FROM t WHERE x GLOBAL IN ('', '''', 'GLOBAL IN');
+SELECT * FROM t WHERE x GLOBAL IN ((1), ((2)), (((3))));
+SELECT * FROM t WHERE x GLOBAL IN ((SELECT 1), 2);
+SELECT * FROM t WHERE x GLOBAL IN ((SELECT 1));
+SELECT * FROM t WHERE x GLOBAL NOT IN (((SELECT 1)), (SELECT 2) + 1);
+SELECT * FROM t WHERE x GLOBAL IN (SELECT 1 UNION ALL SELECT 2);
+SELECT * FROM t WHERE x GLOBAL IN (SELECT y FROM t2 WHERE z GLOBAL IN (SELECT w FROM t3 WHERE v GLOBAL NOT IN (1)));
+SELECT * FROM t WHERE (a, b) GLOBAL IN ((1, 2), (3, 4));
+SELECT * FROM t WHERE (a, b) GLOBAL NOT IN (SELECT a, b FROM t2);
+SELECT * FROM t WHERE tuple(a, b) GLOBAL IN (SELECT a, b FROM t2);
+SELECT * FROM t WHERE (SELECT 1) GLOBAL IN (1, 2);
+SELECT * FROM t WHERE toInt32(x) GLOBAL IN (lower('a'), upper('b'));
+SELECT * FROM t WHERE CAST(x AS String) GLOBAL IN ('1', '2');
+SELECT * FROM t WHERE x GLOBAL IN ([1, 2], [3]);
+SELECT * FROM t WHERE arr[1] GLOBAL IN (1, 2);
+SELECT * FROM t WHERE tup.1 GLOBAL IN (1, 2);
+SELECT * FROM t WHERE NOT x GLOBAL IN (1, 2);
+SELECT * FROM t WHERE NOT (x GLOBAL NOT IN (1, 2));
+SELECT * FROM t WHERE x GLOBAL IN (1) AND y = 2 OR z GLOBAL NOT IN (3) AND w = 4;
+SELECT * FROM t WHERE a = 1 OR x GLOBAL IN (1) AND y BETWEEN 1 AND 2;
+SELECT * FROM t WHERE (x GLOBAL IN (1)) IS NULL;
+SELECT * FROM t WHERE (x GLOBAL IN (1)) = (y GLOBAL IN (2));
+SELECT * FROM t WHERE x GLOBAL IN (1) AND y IN (2) AND z NOT IN (3) AND w GLOBAL NOT IN (4);
+SELECT * FROM t WHERE x GLOBAL IN (1) AND EXISTS (SELECT 1 FROM u WHERE u.x GLOBAL IN (1));
+SELECT x GLOBAL IN (1, 2) AS flag, x GLOBAL NOT IN (SELECT y FROM t2) FROM t;
+SELECT CASE WHEN x GLOBAL IN (1, 2) THEN 'a' WHEN x GLOBAL NOT IN (SELECT y FROM t2) THEN 'b' ELSE 'c' END FROM t;
+SELECT if(x GLOBAL IN (1), 1, 0), multiIf(x GLOBAL NOT IN (2), 1, 0) FROM t;
+SELECT count() FROM t GROUP BY x GLOBAL IN (1, 2) HAVING count() GLOBAL IN (SELECT c FROM counts);
+SELECT * FROM t ORDER BY x GLOBAL IN (1, 2) DESC, y GLOBAL NOT IN (3) ASC LIMIT 10;
+SELECT * FROM t PREWHERE x GLOBAL IN (SELECT y FROM t2) WHERE z GLOBAL NOT IN (1);
+SELECT * FROM t JOIN u ON t.x = u.x AND u.y GLOBAL IN (SELECT y FROM t2);
+SELECT * FROM t LEFT JOIN u ON t.x GLOBAL IN (1, 2) WHERE u.y GLOBAL NOT IN (3);
+SELECT * FROM t ARRAY JOIN arr WHERE arr GLOBAL IN (1, 2);
+SELECT arrayFilter(v -> v GLOBAL IN (1, 2), arr) FROM t;
+SELECT * FROM (SELECT * FROM t WHERE x GLOBAL IN (1)) AS sub WHERE sub.x GLOBAL NOT IN (2);
+WITH c AS (SELECT 1 AS v) SELECT * FROM t WHERE x GLOBAL IN (SELECT v FROM c);
+SELECT * FROM t WHERE x GLOBAL IN (1, 2) SETTINGS max_threads = 1;
+SELECT * FROM t WHERE x GLOBAL IN (1) UNION ALL SELECT * FROM t WHERE x GLOBAL NOT IN (2);
+SELECT * FROM t WHERE x GLOBAL IN (SELECT y FROM t2) ORDER BY x LIMIT 1 BY x;
+SELECT sum(x) OVER (PARTITION BY y GLOBAL IN (1, 2)) FROM t;
+INSERT INTO t2 SELECT * FROM t WHERE x GLOBAL IN (1, 2);
+DELETE FROM t WHERE x GLOBAL IN (SELECT y FROM t2);
+UPDATE t SET y = 1 WHERE x GLOBAL IN (SELECT y FROM t2);
+CREATE VIEW v AS SELECT * FROM t WHERE x GLOBAL IN (1, 2);
+SELECT /* GLOBAL IN */ * FROM t WHERE x GLOBAL -- NOT
+IN (1, 2);
+SELECT * FROM t WHERE x
+GLOBAL
+NOT
+IN
+(1,
+2)
\ No newline at end of file
diff --git a/src/ast/mod.rs b/src/ast/mod.rs
index de925be2..9d3ee77e 100644
--- a/src/ast/mod.rs
+++ b/src/ast/mod.rs
@@ -975,7 +975,7 @@ pub enum Expr {
/// `true` when `NOT` is present.
negated: bool,
},
- /// `[ NOT ] IN (val1, val2, ...)`
+ /// `[ GLOBAL ] [ NOT ] IN (val1, val2, ...)`
InList {
/// Left-hand expression to test for membership.
expr: Box<Expr>,
@@ -983,8 +983,10 @@ pub enum Expr {
list: Vec<Expr>,
/// `true` when the `NOT` modifier is present.
negated: bool,
+ /// `true` when the ClickHouse `GLOBAL` modifier is present.
+ global: bool,
},
- /// `[ NOT ] IN (SELECT ...)`
+ /// `[ GLOBAL ] [ NOT ] IN (SELECT ...)`
InSubquery {
/// Left-hand expression to test for membership.
expr: Box<Expr>,
@@ -992,6 +994,8 @@ pub enum Expr {
subquery: Box<Query>,
/// `true` when the `NOT` modifier is present.
negated: bool,
+ /// `true` when the ClickHouse `GLOBAL` modifier is present.
+ global: bool,
},
/// `[ NOT ] IN UNNEST(array_expression)`
InUnnest {
@@ -1801,10 +1805,12 @@ impl fmt::Display for Expr {
expr,
list,
negated,
+ global,
} => write!(
f,
- "{} {}IN ({})",
+ "{} {}{}IN ({})",
expr,
+ if *global { "GLOBAL " } else { "" },
if *negated { "NOT " } else { "" },
display_comma_separated(list)
),
@@ -1812,10 +1818,12 @@ impl fmt::Display for Expr {
expr,
subquery,
negated,
+ global,
} => write!(
f,
- "{} {}IN ({})",
+ "{} {}{}IN ({})",
expr,
+ if *global { "GLOBAL " } else { "" },
if *negated { "NOT " } else { "" },
subquery
),
diff --git a/src/ast/spans.rs b/src/ast/spans.rs
index 945cbb09..cae24f95 100644
--- a/src/ast/spans.rs
+++ b/src/ast/spans.rs
@@ -1513,6 +1513,7 @@ impl Spanned for Expr {
expr,
list,
negated: _,
+ global: _,
} => union_spans(
core::iter::once(expr.span()).chain(list.iter().map(|item| item.span())),
),
@@ -1520,6 +1521,7 @@ impl Spanned for Expr {
expr,
subquery,
negated: _,
+ global: _,
} => expr.span().union(&subquery.span()),
Expr::InUnnest {
expr,
diff --git a/src/dialect/clickhouse.rs b/src/dialect/clickhouse.rs
index d5c130d0..d256da28 100644
--- a/src/dialect/clickhouse.rs
+++ b/src/dialect/clickhouse.rs
@@ -100,6 +100,11 @@ impl Dialect for ClickHouseDialect {
true
}
+ // See <https://clickhouse.com/docs/sql-reference/operators/in#distributed-subqueries>
+ fn supports_global_in(&self) -> bool {
+ true
+ }
+
/// See <https://clickhouse.com/docs/en/sql-reference/functions#higher-order-functions---operator-and-lambdaparams-expr-function>
fn supports_lambda_functions(&self) -> bool {
true
diff --git a/src/dialect/mod.rs b/src/dialect/mod.rs
index 2beb5655..f57907e1 100644
--- a/src/dialect/mod.rs
+++ b/src/dialect/mod.rs
@@ -461,6 +461,11 @@ pub trait Dialect: Debug + Any {
false
}
+ /// Returns true if the dialect supports `GLOBAL IN` and `GLOBAL NOT IN`.
+ fn supports_global_in(&self) -> bool {
+ false
+ }
+
/// Returns true if the dialect supports `BEGIN {DEFERRED | IMMEDIATE | EXCLUSIVE | TRY | CATCH} [TRANSACTION]` statements
fn supports_start_transaction_modifier(&self) -> bool {
false
@@ -914,6 +919,20 @@ pub trait Dialect: Debug + Any {
Ok(p!(Is))
}
Token::Word(w) if w.keyword == Keyword::IS => Ok(p!(Is)),
+ Token::Word(w) if w.keyword == Keyword::GLOBAL && self.supports_global_in() => {
+ match (
+ &parser.peek_nth_token_ref(1).token,
+ &parser.peek_nth_token_ref(2).token,
+ ) {
+ (Token::Word(w), _) if w.keyword == Keyword::IN => Ok(p!(Between)),
+ (Token::Word(w), Token::Word(w2))
+ if w.keyword == Keyword::NOT && w2.keyword == Keyword::IN =>
+ {
+ Ok(p!(Between))
+ }
+ _ => Ok(self.prec_unknown()),
+ }
+ }
Token::Word(w) if w.keyword == Keyword::IN => Ok(p!(Between)),
Token::Word(w) if w.keyword == Keyword::BETWEEN => Ok(p!(Between)),
Token::Word(w) if w.keyword == Keyword::OVERLAPS => Ok(p!(Between)),
diff --git a/src/parser/mod.rs b/src/parser/mod.rs
index edac4173..6daad0f6 100644
--- a/src/parser/mod.rs
+++ b/src/parser/mod.rs
@@ -4170,6 +4170,11 @@ impl<'a> Parser<'a> {
time_zone: Box::new(self.parse_subexpr(precedence)?),
})
}
+ Keyword::GLOBAL if self.dialect.supports_global_in() => {
+ let negated = self.parse_keyword(Keyword::NOT);
+ self.expect_keyword_is(Keyword::IN)?;
+ self.parse_in(expr, negated, true)
+ }
Keyword::NOT
| Keyword::IN
| Keyword::BETWEEN
@@ -4199,7 +4204,7 @@ impl<'a> Parser<'a> {
} else if negated && null {
Ok(Expr::IsNotNull(Box::new(expr)))
} else if self.parse_keyword(Keyword::IN) {
- self.parse_in(expr, negated)
+ self.parse_in(expr, negated, false)
} else if self.parse_keyword(Keyword::BETWEEN) {
self.parse_between(expr, negated)
} else if self.parse_keyword(Keyword::LIKE) {
@@ -4462,11 +4467,16 @@ impl<'a> Parser<'a> {
Ok(JsonPath { path })
}
- /// Parses the parens following the `[ NOT ] IN` operator.
- pub fn parse_in(&mut self, expr: Expr, negated: bool) -> Result<Expr, ParserError> {
+ /// Parses the parens following the `[ GLOBAL ] [ NOT ] IN` operator.
+ pub fn parse_in(
+ &mut self,
+ expr: Expr,
+ negated: bool,
+ global: bool,
+ ) -> Result<Expr, ParserError> {
// BigQuery allows `IN UNNEST(array_expression)`
// https://cloud.google.com/bigquery/docs/reference/standard-sql/operators#in_operators
- if self.parse_keyword(Keyword::UNNEST) {
+ if !global && self.parse_keyword(Keyword::UNNEST) {
self.expect_token(&Token::LParen)?;
let array_expr = self.parse_expr()?;
self.expect_token(&Token::RParen)?;
@@ -4481,8 +4491,9 @@ impl<'a> Parser<'a> {
{
return Ok(Expr::InList {
expr: Box::new(expr),
- list: vec![self.parse_expr()?],
+ list: vec![self.parse_subexpr(self.dialect.prec_value(Precedence::Eq))?],
negated,
+ global,
});
}
self.expect_token(&Token::LParen)?;
@@ -4501,6 +4512,7 @@ impl<'a> Parser<'a> {
expr: Box::new(expr),
subquery,
negated,
+ global,
});
}
missing_rparen = Some(self.index);
@@ -4521,6 +4533,7 @@ impl<'a> Parser<'a> {
expr: Box::new(expr),
list,
negated,
+ global,
}),
Err(ParserError::RecursionLimitExceeded) => Err(ParserError::RecursionLimitExceeded),
Err(e) => match missing_rparen {
diff --git a/tests/sqlparser_clickhouse.rs b/tests/sqlparser_clickhouse.rs
index 17d55852..ff818811 100644
--- a/tests/sqlparser_clickhouse.rs
+++ b/tests/sqlparser_clickhouse.rs
@@ -2084,6 +2084,125 @@ fn parse_tuple_element_access() {
}
}
+#[test]
+fn parse_global_in() {
+ let select = clickhouse().verified_only_select("SELECT * FROM t WHERE x GLOBAL IN (1, 2)");
+ assert_eq!(
+ select.selection.unwrap(),
+ Expr::InList {
+ expr: Box::new(Identifier(Ident::new("x"))),
+ list: vec![
+ Expr::Value(number("1").with_empty_span()),
+ Expr::Value(number("2").with_empty_span()),
+ ],
+ negated: false,
+ global: true,
+ }
+ );
+
+ let select = clickhouse()
+ .verified_only_select("SELECT * FROM t WHERE x GLOBAL NOT IN (SELECT y FROM t2)");
+ assert_eq!(
+ select.selection.unwrap(),
+ Expr::InSubquery {
+ expr: Box::new(Identifier(Ident::new("x"))),
+ subquery: Box::new(clickhouse().verified_query("SELECT y FROM t2")),
+ negated: true,
+ global: true,
+ }
+ );
+
+ clickhouse().one_statement_parses_to(
+ "SELECT * FROM t WHERE x GLOBAL IN t2",
+ "SELECT * FROM t WHERE x GLOBAL IN (t2)",
+ );
+
+ let select =
+ clickhouse().verified_only_select("SELECT * FROM t WHERE x GLOBAL IN (1) AND y = 2");
+ assert!(matches!(
+ select.selection.unwrap(),
+ BinaryOp {
+ op: BinaryOperator::And,
+ ..
+ }
+ ));
+
+ assert!(clickhouse()
+ .parse_sql_statements("SELECT * FROM t WHERE x GLOBAL y")
+ .is_err());
+
+ for dialect in all_dialects_where(|d| !d.supports_global_in()).dialects {
+ assert!(TestedDialects::new(vec![dialect])
+ .parse_sql_statements("SELECT * FROM t WHERE x GLOBAL IN (1)")
+ .is_err());
+ }
+}
+
+#[test]
+fn parse_in_unparenthesized_expr_precedence() {
+ for (sql, canonical, op) in [
+ (
+ "SELECT * FROM t WHERE x IN t2 AND y = 2",
+ "SELECT * FROM t WHERE x IN (t2) AND y = 2",
+ BinaryOperator::And,
+ ),
+ (
+ "SELECT * FROM t WHERE x GLOBAL IN t2 AND y = 2",
+ "SELECT * FROM t WHERE x GLOBAL IN (t2) AND y = 2",
+ BinaryOperator::And,
+ ),
+ (
+ "SELECT * FROM t WHERE x GLOBAL NOT IN t2 OR y = 2",
+ "SELECT * FROM t WHERE x GLOBAL NOT IN (t2) OR y = 2",
+ BinaryOperator::Or,
+ ),
+ (
+ "SELECT * FROM t WHERE x IN t2 = 1",
+ "SELECT * FROM t WHERE x IN (t2) = 1",
+ BinaryOperator::Eq,
+ ),
+ ] {
+ let select = clickhouse().verified_only_select_with_canonical(sql, canonical);
+ match select.selection.unwrap() {
+ BinaryOp { op: got, .. } => assert_eq!(got, op, "{sql}"),
+ other => panic!("{sql}: expected BinaryOp, got {other:?}"),
+ }
+ }
+
+ // Higher-precedence operators still belong to the IN operand.
+ clickhouse().one_statement_parses_to(
+ "SELECT * FROM t WHERE x IN 1 + 2",
+ "SELECT * FROM t WHERE x IN (1 + 2)",
+ );
+}
+
+#[test]
+fn global_in_bare_table_binds_before_and() {
+ let expr =
+ clickhouse().expr_parses_to("x GLOBAL IN t2 AND y = 2", "x GLOBAL IN (t2) AND y = 2");
+ assert!(matches!(
+ expr,
+ BinaryOp {
+ left,
+ op: BinaryOperator::And,
+ ..
+ } if matches!(*left, Expr::InList { global: true, .. })
+ ));
+}
+
+#[test]
+fn global_in_bare_table_binds_before_equality() {
+ let expr = clickhouse().expr_parses_to("x GLOBAL IN t2 = 1", "x GLOBAL IN (t2) = 1");
+ assert!(matches!(
+ expr,
+ BinaryOp {
+ left,
+ op: BinaryOperator::Eq,
+ ..
+ } if matches!(*left, Expr::InList { global: true, .. })
+ ));
+}
+
fn clickhouse() -> TestedDialects {
TestedDialects::new(vec![Box::new(ClickHouseDialect {})])
}
diff --git a/tests/sqlparser_common.rs b/tests/sqlparser_common.rs
index b6707d17..ea542b50 100644
--- a/tests/sqlparser_common.rs
+++ b/tests/sqlparser_common.rs
@@ -2259,6 +2259,7 @@ fn parse_not_precedence() {
(Value::SingleQuotedString("a".into())).with_empty_span()
)],
negated: true,
+ global: false,
}),
},
);
@@ -2541,6 +2542,7 @@ fn parse_in_list() {
Expr::Value((Value::SingleQuotedString("MED".to_string())).with_empty_span()),
],
negated,
+ global: false,
},
select.selection.unwrap()
);
@@ -2558,6 +2560,7 @@ fn parse_in_subquery() {
expr: Box::new(Expr::Identifier(Ident::new("segment"))),
subquery: Box::new(verified_query("SELECT segm FROM bar")),
negated: false,
+ global: false,
},
select.selection.unwrap()
);
@@ -2574,6 +2577,7 @@ fn parse_in_union() {
"(SELECT segm FROM bar) UNION (SELECT segm FROM bar2)"
)),
negated: false,
+ global: false,
},
select.selection.unwrap()
);
@@ -20312,6 +20316,7 @@ fn parse_in_list_leading_subquery() {
expr: x(),
list: vec![subquery(), Expr::value(number("1"))],
negated: false,
+ global: false,
},
select.selection.unwrap()
);
@@ -20322,6 +20327,7 @@ fn parse_in_list_leading_subquery() {
expr: x(),
list: vec![Expr::Nested(Box::new(subquery())), Expr::value(number("1"))],
negated: true,
+ global: false,
},
select.selection.unwrap()
);
@@ -20339,6 +20345,7 @@ fn parse_in_list_leading_subquery() {
Expr::value(number("2"))
],
negated: false,
+ global: false,
},
select.selection.unwrap()
);
@@ -20357,6 +20364,7 @@ fn parse_in_list_leading_subquery() {
expr: x(),
subquery: Box::new(verified_query("(SELECT a FROM u)")),
negated: false,
+ global: false,
},
select.selection.unwrap()
);
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@datafusion.apache.org
For additional commands, e-mail: commits-help@datafusion.apache.org