Skip to content

Hybrid: WHERE fails with DATA_TYPE_CANNOT_BE_USED_IN_KEY when table has a JSON column #2122

Description

@DimensionWieldr

Describe the bug

A Hybrid table that includes a JSON column fails filters with:

Code: 549. DB::Exception: Column with type Variant/Dynamic/JSON is not allowed in key expression. (DATA_TYPE_CANNOT_BE_USED_IN_KEY)

Full scans / ORDER BY without WHERE work. The same WHERE on MergeTree and on remote() works. Notably, the failure still happens even when the JSON column is not in the SELECT list.

How to reproduce

SET allow_experimental_hybrid_table = 1;
SET enable_analyzer = 1;

CREATE TABLE left (
    id Int32,
    value Int32,
    date_col Date,
    default_json ALIAS $${"a":{"b":42},"c":[1,2,3]}$$::JSON
) ENGINE = MergeTree
PARTITION BY toYYYYMM(date_col)
ORDER BY (date_col, id);

CREATE TABLE right AS left;

INSERT INTO left VALUES (1, 6000, '2025-02-01'), (2, 100, '2014-01-01');
INSERT INTO right VALUES (3, 7000, '2025-02-01'), (4, 200, '2014-01-01');

CREATE TABLE hybrid (
    id Int32,
    value Int32,
    date_col Date,
    default_json JSON
) ENGINE = Hybrid(
    remote('localhost', currentDatabase(), 'left'), date_col >= '2014-09-14',
    remote('localhost', currentDatabase(), 'right'), date_col < '2014-09-14'
);

-- OK
SELECT id, value, default_json FROM hybrid ORDER BY id;

-- FAIL (JSON in SELECT)
SELECT id, value, default_json FROM hybrid WHERE value > 5000 ORDER BY id;

-- FAIL (JSON not even selected)
SELECT id, value FROM hybrid WHERE value > 5000 ORDER BY id;

Observed on 26.3.13.20001.altinityantalya.

Expected behavior

WHERE on non-JSON columns should work the same as MergeTree / remote(), regardless of other JSON columns on the Hybrid table.

Regression

iceberg/tests/hybrid/hybrid_alias/tests/constants/default_json.py

Metadata

Metadata

Assignees

No one assigned

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions