`IN` operator rejects a ClickHouse query-parameter placeholder as its right-hand side without parenthesis

[dinmukhamedm]

Summary

ClickHouse supports server-side query parameters with the {name:Type} substitution syntax. When the parameter is an array, it is idiomatic to write it directly as the right-hand side of an IN operator:

WHERE id IN {ids: Array(UUID)}

sqlparser fails to parse this with:

sql parser error: Expected: (, found: { at Line: 1, Column: ...

The parser's IN handler (Parser::parse_in) unconditionally requires a ( (or UNNEST) after the IN keyword, so a parameter placeholder standing in for the list is rejected. The same placeholder parses fine everywhere else (e.g. as the RHS of =, BETWEEN, a function argument), because in prefix position {name:Type} is parsed as a Dictionary expression.

However, the parenthesized form works:

WHERE span_id IN ({spanIds: Array(UUID)})   -- parses OK

so the two forms are inconsistent, even though ClickHouse accepts both and treats them identically.

Reproduction (Rust)

use sqlparser::dialect::ClickHouseDialect;
use sqlparser::parser::Parser;

fn main() {
    let d = ClickHouseDialect {};

    // FAILS: "Expected: (, found: {"
    let bare = "SELECT name FROM users WHERE id IN {ids: Array(UInt64)}";
    println!("{:?}", Parser::parse_sql(&d, bare));

    // OK
    let paren = "SELECT name FROM users WHERE id IN ({ids: Array(UInt64)})";
    println!("{:?}", Parser::parse_sql(&d, paren));

    // OK — placeholder accepted in non-IN positions
    let eq = "SELECT name FROM users WHERE id = {id: UInt64}";
    println!("{:?}", Parser::parse_sql(&d, eq));
}

Reproduction (ClickHouse — confirming the syntax is valid)

-- 1. Create a dummy in-memory table.
CREATE TABLE users
(
    id   UInt64,
    name String
)
ENGINE = Memory;

-- 2. Insert dummy data.
INSERT INTO users VALUES
    (1, 'alice'),
    (2, 'bob'),
    (3, 'carol');

-- 3. Define an array query parameter for this session.
SET param_ids = '[1, 2]';

-- 4. Use the parameter as the right-hand side of IN — runs successfully.
SELECT name
FROM users
WHERE id IN {ids: Array(UInt64)};
-- → alice
--   bob

Root cause

Parser::parse_in (src/parser/mod.rs, ~line 4290 in 0.62.0):

pub fn parse_in(&mut self, expr: Expr, negated: bool) -> Result<Expr, ParserError> {
    if self.parse_keyword(Keyword::UNNEST) { /* ... */ }
    self.expect_token(&Token::LParen)?;          // <-- rejects `{...}` placeholder
    let in_op = match self.maybe_parse(|p| p.parse_query())? {
        Some(subquery) => Expr::InSubquery { /* ... */ },
        None => Expr::InList { /* ... */ },
    };
    self.expect_token(&Token::RParen)?;
    Ok(in_op)
}

expect_token(LParen) leaves no room for a bare placeholder. In prefix position the same token sequence is handled by parse_lbrace_expr→parse_dictionary(gated onDialect::supports_dictionary_syntax, which ClickHouseDialectreturnstruefor), producingExpr::Dictionary(...). So the AST node to represent the placeholder already exists; parse_in` simply never reaches that path.

Suggested fix

When the token after IN is not ( (and not UNNEST), and the dialect supports the placeholder/dictionary syntax, parse a single prefix expression and wrap it as the IN right-hand side — e.g. a new Expr::InList-like variant carrying a single expression, or
reuse the existing structure with a one-element list. This would make x IN {p:Array(T)} parse equivalently to x IN ({p:Array(T)}).

This should be gated behind a dialect capability (mirroring supports_dictionary_syntax / supports_in_empty_list) so other dialects are unaffected.