iframe-proxy

Manerone · 2026-03-12T18:37:48Z

Changelog category

Improvement

Changelog entry

Adds a new setting input_format_column_name_matching_mode which allows different case sensitivities for input formats.

Documentation entry for user-facing changes

The possible options for the new setting are:

match_case: will do a case-sensitive search of the input columns.
ignore_case: will do a case-insensitive search of the input columns.
auto: will first try to match case-sensitive, if it fails tries to match case-insensitive.

NOTE: case-insensitive searches can have ambiguity (e.g. Name and namE are ambiguous). If any ambiguity is detected, an exception will be thrown.

Supported formats:

JSONEachRow
CSVWithNames
JSONColumns
BSONEachRow
RowBinaryWithNames

Examples

Match Case

CREATE TABLE test (id Int, name String);
SET input_format_column_name_matching_mode = 'match_case'
INSERT INTO test FORMAT JSONEachRow {"id": 0, "NAME": "aaa"};

SELECT * FROM test
┌─id─┬─name─┐
│  0 │      │
└────┴──────┘

Ignore Case

CREATE TABLE test (id Int, name String);
SET input_format_column_name_matching_mode = 'ignore_case'
INSERT INTO test FORMAT JSONEachRow {"id": 0, "NAME": "aaa"};

SELECT * FROM test
┌─id─┬─name─┐
│  0 │ aaa  │
└────┴──────┘

-- Ambiguity
CREATE TABLE test2 (id Int, ID Int);
SET input_format_column_name_matching_mode = 'ignore_case'
INSERT INTO test FORMAT JSONEachRow {"id": 0}

DB::Exception: Ambiguous field (`id`) when processing data: (at row 1)

-- Ambiguity is only detected if the columns are referenced
CREATE TABLE test(id Int, ID Int, name String)
SET input_format_column_name_matching_mode = 'ignore_case'
INSERT INTO test FORMAT JSONEachRow {"name": "aa"}

SELECT * FROM test

┌─id─┬─ID─┬─name─┐
│  0 │  0 │ aa   │
└────┴────┴──────┘

Auto Case

CREATE TABLE test (id Int, ID Int);
SET input_format_column_name_matching_mode = 'auto'
INSERT INTO test FORMAT JSONEachRow {"id": 1}

SELECT * FROM test

┌─id─┬─ID─┐
│  1 │  0 │
└────┴────┘

-- Ambiguity
CREATE TABLE test (id Int, ID Int);
SET input_format_column_name_matching_mode = 'auto'
INSERT INTO test FORMAT JSONEachRow {"Id": 1} -- Notice that `Id` doesn't match any column, so it is matched by a case insensitive search

DB::Exception: Ambiguous field (`id`) when processing data: (at row 1)

Dicussion - possible corner case

CREATE TABLE test(id Int);

SET input_format_column_name_matching_mode = 'auto'

INSERT INTO json_test FORMAT JSONEachRow {"ID": 444, "id": 123};

SELECT * FROM json_test

   ┌──id─┐
1. │ 444 │
   └─────┘

Influence of flaginput_format_json_ignore_unnecessary_fields. If the flag is set to true, we set the case match to auto, then the reader will match ID with the table field id and it will consider the rest of the columns irrelevant. We can, of course, still check the remaining columns, but it would go against a previous comment found in the code:

            // Keep parsing the remaining fields in case of the json is invalid.
            // But not look up the name in the name_map since the cost cannot be ignored

The ambiguity check works fine in the following scenario:

CREATE TABLE test(id Int, name String, age Int);

SET input_format_column_name_matching_mode = 'auto'

INSERT INTO json_test FORMAT JSONEachRow {"ID": 444, "id": 123};

SELECT * FROM json_test

DB::Exception: Ambiguous field (`id`) when processing data: (at row 1)

Here it will work because total_columns is 3, and the ambiguity happens before reaching the total_columns.

Note

Medium Risk
Changes column-name lookup behavior across multiple input formats and adds new ambiguity/duplicate-field errors, which could alter ingestion outcomes for existing pipelines relying on previous matching/skip behavior.

Overview
Adds a new setting, input_format_with_names_case_insensitive_column_matching, to control how input formats that carry column names match those names to table columns: case-sensitive, case-insensitive, or AUTO (exact match first, then case-insensitive).

Introduces CaseAwareBlockNameMap to implement this matching (including ambiguity detection when multiple table columns collide under case-insensitive comparison) and wires it into JSONEachRow, JSONColumns, BSONEachRow, and RowBinaryWithNames/CSVWithNames header mapping. Parsing now throws on ambiguous mappings and on repeated fields that resolve to the same target column, with new stateless tests covering normal and ambiguous cases.

^{Written by Cursor Bugbot for commit 77a9311. This will update automatically on new commits. Configure here.}

Version info

Merged into: 26.4.1.1102

- Re-enable the optimization at JSONEachRowRowInputFormat::columnIndex - Test the other modes - Implement other format readers - Write tests for each format reader - Fix the settings, to allow changing it during execution

- PIMPL idiom to avoid virtual calls and to hide implementation details, since they are only relevant within the context of that class

…s-92851

Change from clientError to serverError

Manerone · 2026-04-09T19:35:05Z

Avogar · 2026-04-10T11:02:28Z

@Manerone I downloaded the build from one of the commits, and this query just don't throw any client or server error:

avogar-dev :) CREATE TABLE json_test (age Int, AGE Int);

CREATE TABLE json_test
(
    `age` Int,
    `AGE` Int
)

Query id: acfecf28-12a4-4ef7-921b-f41534385db7

Ok.

0 rows in set. Elapsed: 0.003 sec.

avogar-dev :) INSERT INTO json_test FORMAT JSONEachRow {"AgE": 1, "aGe": 20};

INSERT INTO json_test FORMAT JSONEachRow

Query id: 2f9829fc-cef0-443f-b31d-1c5bf0568555

Ok.

1 row in set. Elapsed: 0.004 sec.

avogar-dev :) SELECT * FROM json_test

SELECT *
FROM json_test

Query id: 0ea65591-136b-4fb0-8462-c45247bb51be

   ┌─age─┬─AGE─┐
1. │   0 │   0 │
   └─────┴─────┘

1 row in set. Elapsed: 0.003 sec.

So this is the issue, as I understand, you expect it to throw. But it doesn't.

Manerone · 2026-04-10T11:20:47Z

@Avogar just to be sure, did you set input_format_column_name_matching_mode to auto or ignore_case? I think if it stays in the default option (match_case) it will not find any matches and use the default value of the columns.

Avogar · 2026-04-10T11:38:40Z

Ah, right sorry, forgot to change the setting 🤦‍♂️

The actual reason is randomized setting async_insert in the tests. With async inserts errors don't happen on client, but are collected somewhere on server. You need to use clientError and add -- Tags: no-async-insert as the first line in the test.

Manerone · 2026-04-10T11:56:11Z

@Avogar thanks a lot for the help! I have applied the changes, lets see if it finally passes now.

clickhouse-gh · 2026-04-10T12:03:39Z

+$CLICKHOUSE_CLIENT -q "CREATE TABLE test (id Int, iD Int, name String, NAME String)"
+$CLICKHOUSE_CLIENT -q "SET input_format_csv_detect_header=true;
+                       SET input_format_column_name_matching_mode='auto';
+                       INSERT INTO test FROM INFILE '$CURDIR/data_csv/csv_with_names.csv' FORMAT CSVWithNames; -- { clientError 117 }"


These -- { clientError 117 } annotations are inside the SQL string passed to clickhouse-client, so they are treated as SQL comments and are not validated by the shell test harness.

As written, this script does not assert that ambiguity queries actually fail; a regression to successful execution would still pass this test.

Please capture and check the exit code (e.g., via run_with_error from shell_config.sh) for the expected-failure cases.

I don't see run_with_error being used anywhere in the tests. I actually see the shell tests using the same pattern as me. Example files:

tests/queries/0_stateless/02878_use_structure_from_insertion_table_with_explicit_insert_columns.sh

tests/queries/0_stateless/02932_refreshable_materialized_views_1.sh

tests/queries/0_stateless/03410_variant_bad_binary_data.sh

clickhouse-gh · 2026-04-10T12:03:53Z

+$CLICKHOUSE_CLIENT -q "DROP TABLE IF EXISTS test"
+$CLICKHOUSE_CLIENT -q "CREATE TABLE test (id Int, iD Int, name String, NAME String)"
+$CLICKHOUSE_CLIENT -q "SET input_format_column_name_matching_mode='auto';
+                       INSERT INTO test FROM INFILE '$CURDIR/data_binary/row_binary_with_names.bin' FORMAT RowBinaryWithNames; -- { clientError 117 }"


Same issue here as in the CSV shell test: -- { clientError 117 } appears inside the SQL text, so it is not interpreted by the test harness as an expected error check.

Please validate failure cases explicitly (e.g., call run_with_error "$CLICKHOUSE_CLIENT" -q "..." and assert the returned code is 117). Otherwise these negative cases can silently pass if the query unexpectedly succeeds.

clickhouse-gh · 2026-04-10T12:04:09Z

+$CLICKHOUSE_CLIENT -q "DROP TABLE IF EXISTS test"
+$CLICKHOUSE_CLIENT -q "CREATE TABLE test (id Int, iD Int, name String, NAME String)"
+$CLICKHOUSE_CLIENT -q "SET input_format_column_name_matching_mode='auto';
+                       INSERT INTO test FROM INFILE '$CURDIR/data_bson/bson_with_names.bson' FORMAT BSONEachRow; -- { clientError 117 }"


This shell test has the same false-negative pattern: expected-error markers like -- { clientError 117 } are embedded in the SQL query string and are not checked by the shell test framework.

Please assert the error code explicitly for these negative cases (for example with run_with_error + an exit-code check). Without that, regressions where ambiguity is no longer rejected can pass unnoticed.

Avogar · 2026-04-13T13:51:32Z

The CI is not in a good shape right now. We are working on reducing flaky failures. I will postpone merging it for a few days until the PRs with CI fixes are merged.

…s-92851

clickhouse-gh · 2026-04-20T18:52:22Z

LLVM Coverage Report

Changed lines: 99.03% (204/206) · Uncovered code

Full report · Diff report

Manerone added 30 commits March 6, 2026 16:25

WIP: Added initial case aware maps

e435a1d

For the JSONEachRow, auto seems to be working. Need to:

46512f2

- Re-enable the optimization at JSONEachRowRowInputFormat::columnIndex - Test the other modes - Implement other format readers - Write tests for each format reader - Fix the settings, to allow changing it during execution

Change architecture of the map

9809af4

- PIMPL idiom to avoid virtual calls and to hide implementation details, since they are only relevant within the context of that class

settings are working, adding tests

47511bc

adding tests

908a9d5

Adding test reference and improving code a bit

981338a

CSVWithNames

c1c351f

Adding tests for CSVWithNames

8bdf00d

JSONColumns

66e8994

BSONEachRow

049122a

Adding tests for RowBinaryWithNames

e6c67ea

improving JSONEachRow tests

d9f5f3a

improving CSVWithNames tests

78195bf

Improving tests fro JSONColumns

b8f40ea

BSON and RowBinaryWithNames

3a98f44

fix comment

8ffa1d3

better hash function

5b59528

rework CaseAwareNameMap initialization

0e83b88

refactoring

87b6c7e

small improvements

0e2cef2

fixing but for JSONEachRow

7681e84

JSONColumns fix

1e2a7f0

RowInputFormatWithNamesAndTypes fix

0d405e3

BSONEachRow fix

b2e1ee6

RowBinaryWithNames fix

a2c138f

Part 1: review comments

ba016c2

Part 2: review comments

126c587

fix compilation

49076b3

Merge branch 'master' into matheus-92851

02ac6ff

Fix tests

fe081f0

Merge branch 'master' of github.com:ClickHouse/ClickHouse into matheu…

e778c05

…s-92851

Avogar changed the title ~~[Proof of Concept #92851] For input formats with column names, search columns case-insensitively if there is no ambiguity~~ For input formats with column names, search columns case-insensitively if there is no ambiguity Apr 7, 2026

Merge branch 'ClickHouse:master' into matheus-92851

8e3bfca

clickhouse-gh Bot reviewed Apr 7, 2026

View reviewed changes

Comment thread src/Core/CaseAwareBlockNameMap.h

Update CaseAwareBlockNameMap.h

71ea452

clickhouse-gh Bot reviewed Apr 7, 2026

View reviewed changes

Comment thread src/Core/CaseAwareBlockNameMap.cpp

Manerone added 3 commits April 7, 2026 20:29

Update CaseAwareBlockNameMap.cpp

5d61b52

Update 04027_json_input_format.sql

4e19f97

Change from clientError to serverError

Merge branch 'ClickHouse:master' into matheus-92851

0f1c41d

Manerone added 3 commits April 10, 2026 13:49

Update 04027_json_input_format.sql

b3fa114

Update 04029_json_columns_input_format.sql

4e23c30

Merge branch 'ClickHouse:master' into matheus-92851

ce87988

clickhouse-gh Bot reviewed Apr 10, 2026

View reviewed changes

Merge branch 'ClickHouse:master' into matheus-92851

eff0dcc

Merge branch 'master' of github.com:ClickHouse/ClickHouse into matheu…

8e10790

…s-92851

Avogar added this pull request to the merge queue Apr 21, 2026

Merged via the queue into ClickHouse:master with commit 2dde042 Apr 21, 2026
159 of 161 checks passed

robot-clickhouse-ci-2 added the pr-synced-to-cloud The PR is synced to the cloud repo label Apr 21, 2026

This was referenced Apr 29, 2026

Enable use_top_k_dynamic_filtering and use_skip_indexes_for_top_k by default #99537

Merged

Cap BuzzHouse max_depth in CI to prevent ASan allocation-size-too-big #103736

Merged

Sunbelt Computer Software

PL/B Language Development and Support

Uh oh!

Conversation

Manerone commented Mar 12, 2026 • edited by robot-clickhouse Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Changelog category

Changelog entry

Documentation entry for user-facing changes

Examples

Match Case

Ignore Case

Auto Case

Dicussion - possible corner case

Version info

Uh oh!

Uh oh!

Uh oh!

Manerone commented Apr 9, 2026

Uh oh!

Avogar commented Apr 10, 2026

Uh oh!

Manerone commented Apr 10, 2026

Uh oh!

Avogar commented Apr 10, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

Manerone commented Apr 10, 2026

Uh oh!

clickhouse-gh Bot Apr 10, 2026

Choose a reason for hiding this comment

Uh oh!

Manerone Apr 10, 2026

Choose a reason for hiding this comment

Uh oh!

clickhouse-gh Bot Apr 10, 2026

Choose a reason for hiding this comment

Uh oh!

clickhouse-gh Bot Apr 10, 2026

Choose a reason for hiding this comment

Uh oh!

Avogar commented Apr 13, 2026

Uh oh!

clickhouse-gh Bot commented Apr 20, 2026

LLVM Coverage Report

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

6 participants

Manerone commented Mar 12, 2026 •

edited by robot-clickhouse

Loading

Avogar commented Apr 10, 2026 •

edited

Loading