PrepareBatch: FORMAT/VALUES stripping is not quote-aware — column list silently dropped, quoted values corrupted, or query made invalid

[polyglotAI-bot]

Observed

extractInsertQueryComponents in batch.go strips the FORMAT clause and the VALUES
suffix with two plain regexes that run over the raw query text:

// batch.go:10-11
var truncateFormat = regexp.MustCompile(`(?i)\sFORMAT\s+[^\s]+`)
var truncateValues = regexp.MustCompile(`\sVALUES\s.*$`)

Both keywords are structural SQL positions, but the regexes match any occurrence,
including one inside a quoted identifier or a string literal. Three distinct failures
follow, all reproduced on main (06550b5) against server 26.7.3.19, on the native and
the HTTP protocol:

1. A column list is silently discarded, so values are written to the wrong columns.
   With a column identifier that contains VALUES, truncateValues cuts the query at
   that point, the column list disappears, and the batch falls back to the table's full
   column list in table order:

   CREATE TABLE lead_swap (c1 UInt32, `a VALUES b` UInt32) ENGINE Memory
   PrepareBatch("INSERT INTO lead_swap (`a VALUES b`, c1)")
   Append(111, 222)                      // caller means `a VALUES b`=111, c1=222
   -> stored c1=111, `a VALUES b`=222    // swapped, no error
   

   The equivalent with FORMAT mangles the identifier instead:
   INSERT INTO t ("a FORMAT JSON b", col2) is normalized to
   INSERT INTO t ("a b", col2) FORMAT Native.

2. A quoted setting value is silently altered. FORMAT <word> is deleted from inside
   the literal:

   PrepareBatch("INSERT INTO lead_lc SETTINGS log_comment='a FORMAT JSON b'")
   

   system.query_log records log_comment = 'a b' — the value the caller asked for was
   never sent.

3. The query becomes syntactically invalid. A VALUES token inside a quoted setting
   value truncates the query mid-literal:

   PrepareBatch("INSERT INTO lead_lc SETTINGS log_comment='x VALUES y'")
   

   The client sends INSERT INTO lead_lc SETTINGS log_comment='x FORMAT Native and the
   server rejects it:

   • native: code: 62, message: Single quoted string is not closed: Syntax error: failed at position 41 ('x FORMAT Native)
   • HTTP: failed to init block for HTTP batch: failed to determine columns for HTTP insert: sendQuery: [HTTP 400] code: 62 ... (SYNTAX_ERROR)

   The same query with log_comment='xy' succeeds, so the token inside the literal is the
   only difference.

log_comment is a normal setting that carries free text, so a value holding the word
VALUES or FORMAT is a realistic input rather than a synthetic one.

Note: cases 2 and 3 are visible only when the query has no column list. With a column
list, everything after it is dropped by normalizeInsertQueryMatch anyway — that is the
separate, already-reported #1919.

Expected behaviour

FORMAT and VALUES should be recognized only at a top-level structural position. A
keyword inside a single-quoted string, a double-quoted identifier, or a backtick-quoted
identifier is data and must be left untouched:

• the column list must survive a column name containing VALUES / FORMAT;
• a SETTINGS value must reach the server unchanged;
• a valid INSERT must never be turned into a query the server cannot parse.

Code example

package main

import (
	"context"
	"fmt"

	"github.com/ClickHouse/clickhouse-go/v2"
)

func main() {
	ctx := context.Background()
	conn, _ := clickhouse.Open(&clickhouse.Options{
		Addr:     []string{"localhost:9000"},
		Protocol: clickhouse.Native,
		Auth:     clickhouse.Auth{Database: "default", Username: "default"},
	})
	defer conn.Close()

	_ = conn.Exec(ctx, "DROP TABLE IF EXISTS lead_swap")
	_ = conn.Exec(ctx, "CREATE TABLE lead_swap (c1 UInt32, `a VALUES b` UInt32) ENGINE Memory")

	// 1. column list silently dropped -> positional mapping -> swapped values
	b, err := conn.PrepareBatch(ctx, "INSERT INTO lead_swap (`a VALUES b`, c1)")
	if err != nil {
		panic(err)
	}
	_ = b.Append(uint32(111), uint32(222))
	_ = b.Send()

	var c1, av uint32
	_ = conn.QueryRow(ctx, "SELECT c1, `a VALUES b` FROM lead_swap").Scan(&c1, &av)
	fmt.Printf("c1=%d `a VALUES b`=%d\n", c1, av) // c1=111 `a VALUES b`=222 -- swapped

	// 2. FORMAT deleted from inside a quoted setting value
	_ = conn.Exec(ctx, "DROP TABLE IF EXISTS lead_lc")
	_ = conn.Exec(ctx, "CREATE TABLE lead_lc (c1 UInt32) ENGINE Memory")
	b2, _ := conn.PrepareBatch(ctx, "INSERT INTO lead_lc SETTINGS log_comment='a FORMAT JSON b'")
	_ = b2.Append(uint32(1))
	_ = b2.Send()
	_ = conn.Exec(ctx, "SYSTEM FLUSH LOGS")
	var lc string
	_ = conn.QueryRow(ctx, "SELECT log_comment FROM system.query_log WHERE query LIKE '%lead_lc%' AND type='QueryFinish' ORDER BY event_time DESC LIMIT 1").Scan(&lc)
	fmt.Printf("log_comment=%q\n", lc) // "a b" -- not "a FORMAT JSON b"

	// 3. VALUES inside a quoted setting value -> invalid query
	_, err = conn.PrepareBatch(ctx, "INSERT INTO lead_lc SETTINGS log_comment='x VALUES y'")
	fmt.Println("err:", err) // code: 62, Single quoted string is not closed
}

Error log

code: 62, message: Single quoted string is not closed: Syntax error: failed at position 41 ('x FORMAT Native): 'x FORMAT Native.

Details

Root cause: batch.go:26-27 applies truncateFormat and truncateValues to the whole
query string before any parsing, so neither knows about quoting.

Suggested fix: replace the two regexes with a single left-to-right byte scan that tracks
single-quote, double-quote and backtick quoting (plus backslash escapes and -- / #
comments) and reports the offset of the first FORMAT / VALUES keyword found outside
quotes; truncate there. Contrast cases that must keep their current behavior:
INSERT INTO t (a, b) VALUES (1, 2), INSERT INTO t (a, b) FORMAT JSONEachRow, and a
lowercase / mixed-case format clause.

Related but distinct — same family of "the INSERT parser is a regex, not a parser":
#1950 (comments), #1952 (TABLE keyword and table functions), #1827 and #1401 (backticks
when splitting the column list), #1919 (SETTINGS after a column list).

Provenance: found by automated analysis of batch.go while working on an adjacent change,
then verified against a live server on both protocols — not reported from inspection alone.

Environment

• clickhouse-go version: main @ 06550b5

• Interface: ClickHouse API (native and HTTP; both affected)

• Go version: go1.25.11

• Operating system: Linux

• ClickHouse version: 26.7.3.19

• Is it a ClickHouse Cloud? No

• ClickHouse Server non-default settings, if any: none

• CREATE TABLE statements for tables involved:

  CREATE TABLE lead_swap (c1 UInt32, `a VALUES b` UInt32) ENGINE Memory;
  CREATE TABLE lead_lc (c1 UInt32) ENGINE Memory;

• Sample data for all these tables: see the snippet