#120634 Schema inference: a quoted JSON string first seen past the inference sample is parsed as a number and silently loses its text ("007" -> 7, "" -> 0)

Company or project name

No response

Describe the unexpected behaviour

Schema inference reads only a prefix of the input — input_format_max_rows_to_read_for_schema_inference, default 25000 rows. If a column's values in that prefix are all numbers, the column is inferred Nullable(Int64). A quoted JSON string appearing after the prefix is then parsed into that numeric column, and its text is silently changed. No error, no warning.

In JSON, quoting is not a formatting convention — it is type information. "007" is a string, and ClickHouse agrees: when the same value appears inside the sample, inference produces Nullable(String) and 007 reads back intact. Whether the quotes are honoured therefore depends only on where in the file the value appears.

One file, 30000 JSONEachRow records of {"a": 1}, with {"a": "007"} first appearing at row k:

first row holding `"007"`	inferred type	value at row `k`	rows still holding `007`
24998	`Nullable(String)`	`007`	5002
24999	`Nullable(String)`	`007`	5001
25000	`Nullable(Int64)`	`7`	0
25001	`Nullable(Int64)`	`7`	0
28000	`Nullable(Int64)`	`7`	0

Row 24999 is the last row inference reads. One row later, all 5000 strings in the file are gone.

Which quoted values are silently changed, and which are rejected

Same file shape, "007" replaced by each value, first appearing at row 28000:

written	read back
`"007"`	`7`	leading zeros gone
`"+1"`	`1`	sign gone
`"-0"`	`0`	sign of zero gone
`""`	`0`	an empty string becomes a real zero
`"9223372036854775808"`	`-9223372036854775808`	wrapped (same wrap as #120503)
`"1.50"`, `" 1"`, `"1e5"`, `"0x10"`, `"inf"`, `"x"`	`Code: 27 … Cannot parse input`	rejected

The reader does validate each value against the inferred type and raises when it cannot parse — six of the eleven shapes above raise. So "the reader has no way to know" is not an explanation for the other five. The five it accepts are precisely the five where a string and a number differ in text but not in parsed value: a zero-padded identifier, a +-prefixed code, a signed zero, an empty field, an integer past Int64. Those are the five cases where the difference is the whole point of the string being a string.

"" is the clearest one. An empty string is not a number under any reading, and input_format_json_empty_as_default is 0 by default, so nothing has authorized a default value here. It still becomes 0 — not NULL, not an error — indistinguishable from a measured zero:

SELECT toTypeName(a), isNull(a), toString(a) FROM file('empty.jsonl', JSONEachRow) LIMIT 1 OFFSET 28000
-- Nullable(Int64)   0   0

and it is unaffected by every related setting (input_format_json_empty_as_default, input_format_null_as_default, input_format_json_read_numbers_as_strings, input_format_defaults_for_omitted_fields).

Inference and deserialization disagree, in the direction the default forbids

input_format_json_try_infer_numbers_from_strings is documented "Disabled by default" — when inference sees a quoted number it refuses to treat it as one:

$ printf '{"a": "007"}\n{"a": "12"}\n' > allq.jsonl
$ clickhouse local --query "SELECT toTypeName(a), toString(a) FROM file('allq.jsonl', JSONEachRow)"
Nullable(String)  007
Nullable(String)  12

$ ... SETTINGS input_format_json_try_infer_numbers_from_strings=1
Nullable(Int64)   7
Nullable(Int64)   12

So at stock settings, a quoted 007 that inference sees is a string; the same quoted 007 that inference does not see is the number 7. The behaviour past row 25000 is the behaviour a non-default setting exists to opt into.

This is the same class of mismatch that #112453 ("Make schema inference use the same float parser as deserialization", merged 2026-07-31) fixed for floats, and it was fixed there by making the disagreement resolve to String.

The sample is the only variable

Same file, "007" first at row 28000, nothing else changed:

input_format_max_rows_to_read_for_schema_inference = 25000  ->  Nullable(Int64)   7
input_format_max_rows_to_read_for_schema_inference = 30000  ->  Nullable(String)  007

and what the sample happens to contain settles the whole column:

sample holds  1      ->  Nullable(Int64)    "007" becomes 7
sample holds  1.5    ->  Nullable(Float64)  "007" becomes 7
sample holds  "x"    ->  Nullable(String)   "007" stays 007
sample holds  null   ->  Nullable(String)   "007" stays 007

A single non-numeric value anywhere in the first 25000 rows saves every string in the file. Two files holding the same multiset of records give different answers depending on write order.

It reaches storage

CREATE TABLE t ENGINE = MergeTree ORDER BY tuple()
AS SELECT * FROM file('f.jsonl', JSONEachRow);
-- column Nullable(Int64); 2000 rows hold 7, and every one of them was written as "007"

CSVWithNames does the same

CSVWithNames has its own field reader and the same threshold, and input_format_csv_try_infer_numbers_from_strings is likewise 0 by default:

written (quoted)	first at row 24999	first at row 25000
`"007"`	`String` -> `007`	`Int64` -> `7`
`"+1"`	`String` -> `+1`	`Int64` -> `1`
`"-0"`	`String` -> `-0`	`Int64` -> `0`
`"9223372036854775808"`	`String` -> the number	`Int64` -> `-9223372036854775808`
`""`	`String` -> `''`	`Int64` -> `0`
`"x"`	`String` -> `x`	raises

An empty CSV field written without quotes reads as NULL on both sides of the threshold, so in a CSV export the difference between "absent" and "present but empty" decides whether the value survives.

TSVWithNames: quoted values are rejected, but a bare `007` is still lost

TSVWithNames rejects every quoted value above on both sides of the threshold. It is not unaffected, though: a bare 007 is inferred String inside the sample and Int64 past it.

first row holding `007`	inferred type	rows still holding `007`
24999	`Nullable(String)`	5001
25000	`Nullable(Int64)`	0

(In CSV a bare 007 is Int64 at every position — that is #112226, deliberate, and not part of this report. A bare -0 is 0 everywhere in CSV, likewise not part of this report.)

Which ClickHouse versions are affected?

26.10.1.11 — master, GIT_HASH 718e603a5d63fecbf85b834d605fbd419201f4ad, GIT_DATE 2026-09-17 01:04:39 +0000, binary from https://s3.amazonaws.com/clickhouse-builds/master/amd64/clickhouse. Confirmed both via clickhouse local and via a clickhouse server started from the same binary.
26.8.5.13 — clickhouse/clickhouse-server:latest (current stable).
26.9.1.1051 — local single binary.

Identical on all three. This is not fixed on master.

How to reproduce

Needs only the single binary and python3. repro-local.sh in full is attached below; it exits 0 when the defect reproduces.

python3 - <<'EOF'
with open('f.jsonl', 'w') as fh:
    for i in range(30000):
        fh.write('{"a": %s}\n' % ('1' if i < 28000 else '"007"'))
EOF

clickhouse local --query "
  SELECT toTypeName(a), toString(a) FROM file('f.jsonl', JSONEachRow) LIMIT 1 OFFSET 28000"
-- Nullable(Int64)   7        <-- the file says "007"

clickhouse local --query "
  SELECT countIf(toString(a) = '007') FROM file('f.jsonl', JSONEachRow)"
-- 0                          <-- 2000 rows were written as "007"

clickhouse local --query "
  SELECT toTypeName(a), toString(a) FROM file('f.jsonl', JSONEachRow) LIMIT 1 OFFSET 28000
  SETTINGS input_format_max_rows_to_read_for_schema_inference = 30000"
-- Nullable(String)  007      <-- same bytes

Move the first "007" to row 24999 instead of 28000 and the column is Nullable(String) with all 5001 strings intact.

repro-local.sh — full script, covers the boundary, the value table, the recovery, the sample dependence, and the MergeTree write

#!/bin/bash
# BUG-038 minimal reproducer.  Needs only the clickhouse single binary.
#   CH=/path/to/clickhouse ./repro-local.sh
# Exits 0 when the defect reproduces.
set -u
CH="${CH:-./clickhouse}"
export TZ=UTC
d=$(mktemp -d); trap 'rm -rf "$d"' EXIT
q() { "$CH" local --path "$d/db" --query "$1" 2>&1 | head -1; }
echo "clickhouse $(q 'SELECT version()')  git $(q "SELECT value FROM system.build_options WHERE name='GIT_HASH'")  branch $(q "SELECT value FROM system.build_options WHERE name='GIT_BRANCH'")"

gen() {  # file, first-row-holding-late, late-value, early-value
  python3 -c '
import sys
p,k,late,early = sys.argv[1],int(sys.argv[2]),sys.argv[3],sys.argv[4]
with open(p,"w") as fh:
    for i in range(30000): fh.write("{\"a\": %s}\n" % (early if i<k else late))' "$@"
}

bad=1
echo
echo '30000 JSONEachRow rows of {"a": 1}; the quoted string "007" first appears at row k'
echo '  (row 24999 is the last row schema inference reads at the default 25000)'
for k in 24998 24999 25000 25001 28000; do
  gen "$d/f.jsonl" "$k" '"007"' '1'
  t=$(q "SELECT toTypeName(a) FROM file('$d/f.jsonl', JSONEachRow) LIMIT 1")
  v=$(q "SELECT toString(a) FROM file('$d/f.jsonl', JSONEachRow) LIMIT 1 OFFSET $k")
  n=$(q "SELECT countIf(toString(a) = '007') FROM file('$d/f.jsonl', JSONEachRow)")
  [[ "$v" == "007" ]] && m="" || { m="<-- the string is gone"; bad=0; }
  printf "  k=%-6s inferred %-18s row k = %-8s rows still '007' = %-6s %s\n" "$k" "$t" "$v" "$n" "$m"
done

echo
echo 'which quoted values are silently changed and which are rejected (first at row 28000)'
for s in '"007"' '"+1"' '"-0"' '"9223372036854775808"' '""' '"1.50"' '" 1"' '"1e5"' '"0x10"' '"inf"' '"x"'; do
  gen "$d/v.jsonl" 28000 "$s" '1'
  v=$(q "SELECT if(a IS NULL,'NULL',toString(a)) FROM file('$d/v.jsonl', JSONEachRow) LIMIT 1 OFFSET 28000")
  printf "  wrote %-24s -> %s\n" "$s" "${v:0:64}"
done

echo
echo 'the sample is the only variable: same file, sample raised past row 28000'
gen "$d/r.jsonl" 28000 '"007"' '1'
for m in 25000 30000; do
  t=$(q "SELECT toTypeName(a) FROM file('$d/r.jsonl', JSONEachRow) LIMIT 1 SETTINGS input_format_max_rows_to_read_for_schema_inference=$m")
  v=$(q "SELECT toString(a) FROM file('$d/r.jsonl', JSONEachRow) LIMIT 1 OFFSET 28000 SETTINGS input_format_max_rows_to_read_for_schema_inference=$m")
  printf "  input_format_max_rows_to_read_for_schema_inference=%-6s %-20s %s\n" "$m" "$t" "$v"
done

echo
echo 'one non-numeric value anywhere in the sample saves the whole column'
for e in '1' '1.5' '"x"' 'null'; do
  gen "$d/s.jsonl" 28000 '"007"' "$e"
  t=$(q "SELECT toTypeName(a) FROM file('$d/s.jsonl', JSONEachRow) LIMIT 1")
  v=$(q "SELECT toString(a) FROM file('$d/s.jsonl', JSONEachRow) LIMIT 1 OFFSET 28000")
  printf "  sample holds %-6s -> %-20s value %s\n" "$e" "$t" "$v"
done

echo
echo 'it reaches storage'
q "CREATE TABLE t ENGINE=MergeTree ORDER BY tuple() AS SELECT * FROM file('$d/r.jsonl', JSONEachRow)" >/dev/null
printf "  MergeTree column %s; %s rows now hold 7, and they were written as \"007\"\n" \
  "$(q "SELECT toTypeName(a) FROM t LIMIT 1")" "$(q "SELECT countIf(a = 7) FROM t")"
exit $bad

Output on master 26.10.1.11 (718e603a):

clickhouse 26.10.1.11  git 718e603a5d63fecbf85b834d605fbd419201f4ad  branch master

30000 JSONEachRow rows of {"a": 1}; the quoted string "007" first appears at row k
  (row 24999 is the last row schema inference reads at the default 25000)
  k=24998  inferred Nullable(String)   row k = 007      rows still '007' = 5002
  k=24999  inferred Nullable(String)   row k = 007      rows still '007' = 5001
  k=25000  inferred Nullable(Int64)    row k = 7        rows still '007' = 0      <-- the string is gone
  k=25001  inferred Nullable(Int64)    row k = 7        rows still '007' = 0      <-- the string is gone
  k=28000  inferred Nullable(Int64)    row k = 7        rows still '007' = 0      <-- the string is gone

which quoted values are silently changed and which are rejected (first at row 28000)
  wrote "007"                    -> 7
  wrote "+1"                     -> 1
  wrote "-0"                     -> 0
  wrote "9223372036854775808"    -> -9223372036854775808
  wrote ""                       -> 0
  wrote "1.50"                   -> Code: 27. DB::Exception: Cannot parse input: expected '"' before
  wrote " 1"                     -> Code: 27. DB::Exception: Cannot parse input: expected '"' before
  wrote "1e5"                    -> Code: 27. DB::Exception: Cannot parse input: expected '"' before
  wrote "0x10"                   -> Code: 27. DB::Exception: Cannot parse input: expected '"' before
  wrote "inf"                    -> Code: 27. DB::Exception: Cannot parse input: expected '"' before
  wrote "x"                      -> Code: 27. DB::Exception: Cannot parse input: expected '"' before

the sample is the only variable: same file, sample raised past row 28000
  input_format_max_rows_to_read_for_schema_inference=25000  Nullable(Int64)      7
  input_format_max_rows_to_read_for_schema_inference=30000  Nullable(String)     007

one non-numeric value anywhere in the sample saves the whole column
  sample holds 1      -> Nullable(Int64)      value 7
  sample holds 1.5    -> Nullable(Float64)    value 7
  sample holds "x"    -> Nullable(String)     value 007
  sample holds null   -> Nullable(String)     value 007

it reaches storage
  MergeTree column Nullable(Int64); 2000 rows now hold 7, and they were written as "007"

Expected behavior

Either of these would be sound; the first matches how #112453 resolved the same disagreement for floats:

Deserialization follows input_format_*_try_infer_numbers_from_strings. With it at its default 0, a quoted JSON string arriving in an inferred numeric column is an error, the same way "x" already is — rather than a silent text change. A quoted value in a column the user declared Int64 can keep today's lenient behaviour; the setting is about inference, and this is an inferred column.
Or the mismatch widens the column, as it does when the string is inside the sample: the column becomes String and the text is preserved.

At minimum, "" should never become 0 while input_format_json_empty_as_default = 0.

Whatever is chosen, the same 30000 records should not read back differently depending on which row they were written in.

Error message and/or stacktrace

None — that is the report. The queries succeed and return wrong values. Nothing appears in system.text_log. Six other quoted shapes in the identical position raise Code: 27 CANNOT_PARSE_INPUT_ASSERTION_FAILED, which is what makes the silence on these five inconsistent.

Related issues and pull requests

#112453 — "Make schema inference use the same float parser as deserialization" (merged 2026-07-31). Same class of mismatch, for floats, resolved toward String. The nearest prior art.
#120503 — the unquoted integer past the same sample being wrapped. The "9223372036854775808" row here is that wrap reached through a quoted string; the other four shapes are not covered by it.
#120348 — a JSON key first seen past the same sample being dropped. Same threshold, different symptom.
#112226 — "Infer leading-zero decimals as numbers in TSV schema inference". About what inference should call a bare 007 when it sees it; the opposite direction, and explicitly not what is reported here.
#66594 — JSONExtract and leading zeros. Different surface.

Additional context