CTE returns wrong column name

[l1t1]

What happens?

following sql should output id ,but output s, and the values are of column id

To Reproduce

create schema poker24;
create table poker24.cards as (with t as(select i::int i from generate_series(1,10)t(i)) select row_number()over()id, a.i c1,b.i c2,c.i c3,d.i c4 from t a,t b,t c,t d);
with a1(s) as materialized(select unnest(array[
'1118(1+1+1)*8','1126(1+1+2)*6','1127(1+2)*(1+7)','1128(1*1+2)*8','1129(1+2)*(9-1)','1120(1+1)*(2+0)','1134(1+1)*3*4','78000-(8-0)*7','88808-(8-0)*8'])
),
a(s,result)as
(select substr(a1.s,1,4)::int,replace(substr(a1.s,5),'0','10')from a1)
/*把每组c1-c4按从小到大排序拼接成字符串*/
,q as(select id,c1,c2,c3,c4,string_agg(case when a=10 then '0' else a::varchar end ,''order by a)::int s from (select id,c1,c2,c3,c4,unnest(array[c1,c2,c3,c4])a from poker24.cards)b group by id,c1,c2,c3,c4)
select id,c1,c2,c3,c4,result from q left join a on a.s = q.s
;

┌───────┬───────┬───────┬───────┬───────┬──────────────┐
│   s   │  c1   │  c2   │  c3   │  c4   │    result    │

OS:

windows7

DuckDB Version:

0.9.2

DuckDB Client:

CLI

Full Name:

lutao

Affiliation:

study

Have you tried this on the latest main branch?

I have tested with a main build

Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?

• Yes, I have

[szarnyasg]

Hi @l1t1, thanks for reporting this. Could you please provide a minimal reproducible example that demonstrates this issue?

[l1t1]

it is because of materialized ,

--right
with cards(id,c1,c2,c3,c4) as (values(1,1,1,1,8)),
a(s,result)as (select 1118,'(1+1+1)*8')
,q as(select id,c1,c2,c3,c4,string_agg(case when a=10 then '0' else a::varchar end ,''order by a)::int s 
from (select id,c1,c2,c3,c4,unnest(array[c1,c2,c3,c4])a from cards)b group by id,c1,c2,c3,c4)
select id,c1,c2,c3,c4,result from q left join a on a.s = q.s
;
--wrong
with cards(id,c1,c2,c3,c4) as (values(1,1,1,1,8)),
a(s,result)as materialized(select 1118,'(1+1+1)*8')
,q as(select id,c1,c2,c3,c4,string_agg(case when a=10 then '0' else a::varchar end ,''order by a)::int s 
from (select id,c1,c2,c3,c4,unnest(array[c1,c2,c3,c4])a from cards)b group by id,c1,c2,c3,c4)
select id,c1,c2,c3,c4,result from q left join a on a.s = q.s
;

[l1t1]

the minimal reproducible example

with q(id,c1,c2,c3,c4,s) as (values(1,1,1,1,8,1118)),
a(s,result)as materialized(select 1118,'(1+1+1)*8')
select id,c1,c2,c3,c4,result from q left join a on a.s = q.s
;