Replace JSON column in the subquery to requested subcolumns #75538
Description
Company or project name
ClickHouse
Use case
Right now when subcolumns are read from the JSON column selected in the subquery we read the whole JSON column and use getSubcolumn function:
:) create table test (data JSON, c1 UInt32, c2 UInt32) engine=MergeTree order by tuple();
CREATE TABLE test
(
`data` JSON,
`c1` UInt32,
`c2` UInt32
)
ENGINE = MergeTree
ORDER BY tuple()
Query id: 05d1ba3c-51de-4f03-8483-07be6479e6df
Ok.
0 rows in set. Elapsed: 0.011 sec.
:) explain actions=1 select data.a, data.b from (select * from test)
EXPLAIN actions = 1
SELECT
data.a,
data.b
FROM
(
SELECT *
FROM test
)
Query id: 1eab9c56-71ba-4863-9c49-6881f2489e78
┌─explain────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
1. │ Expression ((Project names + (Projection + (Change column names to column identifiers + (Project names + (Projection + Change column names to column identifiers)))))) │
2. │ Actions: INPUT : 0 -> data JSON : 0 │
3. │ COLUMN Const(String) -> 'a'_String String : 1 │
4. │ COLUMN Const(String) -> 'b'_String String : 2 │
5. │ ALIAS data :: 0 -> __table2.data JSON : 3 │
6. │ ALIAS __table2.data :: 3 -> data JSON : 0 │
7. │ ALIAS data :: 0 -> __table1.data JSON : 3 │
8. │ FUNCTION getSubcolumn(__table1.data : 3, 'a'_String :: 1) -> getSubcolumn(__table1.data, 'a'_String) Dynamic : 0 │
9. │ FUNCTION getSubcolumn(__table1.data :: 3, 'b'_String :: 2) -> getSubcolumn(__table1.data, 'b'_String) Dynamic : 1 │
10. │ ALIAS getSubcolumn(__table1.data, 'a'_String) :: 0 -> data.a Dynamic : 2 │
11. │ ALIAS getSubcolumn(__table1.data, 'b'_String) :: 1 -> data.b Dynamic : 0 │
12. │ Positions: 2 0 │
13. │ ReadFromPreparedSource (Read from NullSource) │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
13 rows in set. Elapsed: 0.002 sec.
Describe the solution you'd like
We could propogate subcolumns to the subquery so only requested subcolumns are read from the table. Like this:
:) explain actions=1 select data.a, data.b from (select data.a, data.b from test)
EXPLAIN actions = 1
SELECT
data.a,
data.b
FROM
(
SELECT
data.a,
data.b
FROM test
)
Query id: a0d40b82-fa91-4534-9b7b-0e719cac3d9d
┌─explain────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
1. │ Expression ((Project names + (Projection + (Change column names to column identifiers + (Project names + (Projection + Change column names to column identifiers)))))) │
2. │ Actions: INPUT : 0 -> data.a Dynamic : 0 │
3. │ INPUT : 1 -> data.b Dynamic : 1 │
4. │ ALIAS data.a :: 0 -> __table2.data.a Dynamic : 2 │
5. │ ALIAS data.b :: 1 -> __table2.data.b Dynamic : 0 │
6. │ ALIAS __table2.data.a :: 2 -> data.a Dynamic : 1 │
7. │ ALIAS __table2.data.b :: 0 -> data.b Dynamic : 2 │
8. │ ALIAS data.a :: 1 -> __table1.data.a Dynamic : 0 │
9. │ ALIAS data.b :: 2 -> __table1.data.b Dynamic : 1 │
10. │ ALIAS __table1.data.a :: 0 -> data.a Dynamic : 2 │
11. │ ALIAS __table1.data.b :: 1 -> data.b Dynamic : 0 │
12. │ Positions: 2 0 │
13. │ ReadFromPreparedSource (Read from NullSource) │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
13 rows in set. Elapsed: 0.002 sec.Describe alternatives you've considered
No response
Additional context
No response