UNSUPPORTED_METHOD error when adding alias based on JSON #78183
Description
Company or project name
No response
Describe the unexpected behaviour
i've noticed change in behavior between ClickHouse 24.12.1 and 25.3.1.
i'm trying to migrate existing data structure that's based on arrays of ints, arrays of arrays of into JSON. to preserve backwards compatibility with existing queries - i'm trying to use alias, but those don't work in 25.3.1.
this code works fine in the older version but not in the latest stable:
set enable_json_type = 1;
create or replace table inp ( id UInt64, name Array(UInt32), value Array(Array(UInt32)) ) order by id;
create or replace table out0 ( id UInt64, j JSON ) order by id;
insert into inp ( id, name, value ) values(1, [1,2], [[3],[4]]);
insert into out0 select id, toJSONString(mapFromArrays(name,value)) from inp;
create or replace table out1 ( id UInt64, j JSON, name Array(UInt32) ALIAS arrayMap(x -> toUInt32(x), JSONAllPaths(j)), value Array(Array(UInt32)) alias arrayMap(x -> JSONExtract(CAST(j, 'String'), indexOf(name, x), 'Array(UInt32)'), name) )order by id;
insert into out1 select id, toJSONString(mapFromArrays(name,value)) from inp;
-- so far so good
select * from out1;
SELECT will return expected result in 24.12.1 and error in 25.3.1:
Code: 1. DB::Exception: Received from localhost:9000. DB::Exception: Resolved identifier 'j' in parent scope to expression 'default.out1.j' with correlated column 'j'. In scope x -> JSONExtract(CAST(j, 'String'), indexOf(name, x), 'Array(UInt32)'). (UNSUPPORTED_METHOD)
How to reproduce
this code works fine in the older version but not in the latest stable:
set enable_json_type = 1;
create or replace table inp ( id UInt64, name Array(UInt32), value Array(Array(UInt32)) ) order by id;
create or replace table out0 ( id UInt64, j JSON ) order by id;
insert into inp ( id, name, value ) values(1, [1,2], [[3],[4]]);
insert into out0 select id, toJSONString(mapFromArrays(name,value)) from inp;
select arrayMap(x -> toUInt32(x), JSONAllPaths(j)) as name, arrayMap(x -> JSONExtract(CAST(j, 'String'), indexOf(name, x), 'Array(UInt32)'), name) from out0;
create or replace table out1 ( id UInt64, j JSON, name Array(UInt32) ALIAS arrayMap(x -> toUInt32(x), JSONAllPaths(j)), value Array(Array(UInt32)) alias arrayMap(x -> JSONExtract(CAST(j, 'String'), indexOf(name, x), 'Array(UInt32)'), name) )order by id;
insert into out1 select id, toJSONString(mapFromArrays(name,value)) from inp;
-- so far so good
select * from out1;
SELECT will return expected result in 24.12.1 and error in 25.3.1:
Code: 1. DB::Exception: Received from localhost:9000. DB::Exception: Resolved identifier 'j' in parent scope to expression 'default.out1.j' with correlated column 'j'. In scope x -> JSONExtract(CAST(j, 'String'), indexOf(name, x), 'Array(UInt32)'). (UNSUPPORTED_METHOD)
Expected behavior
above select should succeed as it does in 24.12.1
Error message and/or stacktrace
No response
Additional context
clickhouse .deb from your builds. amd64 Debian linux.
thx!