DB-API executemany bulk insert path misparses valid INSERT statements (TABLE keyword, table functions, multi-line, double-quoted columns)

[polyglotAI-bot]

Cursor._try_bulk_insert (clickhouse_connect/dbapi/cursor.py) parses the INSERT statement passed to executemany so it can send the rows through client.insert. The parse is too narrow, and several INSERT forms that the server accepts are mishandled. Verified against ClickHouse 26.5.1.882: all of the statements below are accepted by the server.

1. The optional TABLE keyword is parsed as the table name

INSERT INTO TABLE tbl (id, name) VALUES (...) is valid ClickHouse. The client parses TABLE as the table name and the column list is lost as well, so the rows are sent as client.insert("TABLE", rows, "*").

2. A table function target is parsed as the table name

INSERT INTO FUNCTION null('id UInt32') VALUES (%s) and INSERT INTO TABLE FUNCTION ... are valid. The client sends client.insert("FUNCTION", ...) and client.insert("TABLE", ...). There is no table to bulk insert into here, so the statement should stay on the row-by-row path.

3. A multi-line INSERT silently loses the fast path

insert_re is ^\s*INSERT\s+INTO\s+(.*$) with no re.DOTALL, so group 1 stops at the end of the first line. Any INSERT formatted over several lines, which is common, has no VALUES in the captured remainder and quietly degrades to one query per row. The table name is also terminated on a literal space only, so a newline between the table name and VALUES is not a terminator either.

4. Double-quoted column names keep their quotes

INSERT INTO tbl ("id", "name") VALUES (%s, %s) is valid. The column names are normalized with unescape_identifier, which removes backtick quoting only, so the names stay "id" and "name". With sequence rows those are passed to client.insert and raise ProgrammingError: Unrecognized column '"id"'. With dict rows the column-name comparison fails and the fast path is silently lost. quote_identifier already accepts both backticks and double quotes as valid quoting, so unescape_identifier is the asymmetric half of the pair.

Reproduction

from unittest.mock import Mock
from clickhouse_connect.dbapi.cursor import Cursor

for statement in [
    "INSERT INTO TABLE tbl (id, name) VALUES (%s, %s)",
    "INSERT INTO FUNCTION null('id UInt32') VALUES (%s)",
    "INSERT INTO tbl\n  (id, name)\nVALUES (%s, %s)",
    'INSERT INTO tbl ("id", "name") VALUES (%s, %s)',
]:
    client = Mock()
    cursor = Cursor(client)
    cursor.executemany(statement, [(13, "user_1")])
    print(statement, "->", client.insert.call_args, "queries:", client.query.call_count)

Output on main:

INSERT INTO TABLE tbl (id, name) VALUES (%s, %s) -> call('TABLE', [(13, 'user_1')], '*', settings=None) queries: 0
INSERT INTO FUNCTION null('id UInt32') VALUES (%s) -> call('FUNCTION', [(13, 'user_1')], '*', settings=None) queries: 0
INSERT INTO tbl
  (id, name)
VALUES (%s, %s) -> None queries: 1
INSERT INTO tbl ("id", "name") VALUES (%s, %s) -> call('tbl', [(13, 'user_1')], ['"id"', '"name"'], settings=None) queries: 0

Expected: the first, third and fourth insert into tbl with columns id and name, and the second runs row by row.