Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.11.4
-
debian 12
Description
Trying to follow an obscure statement, I created another one that provided weird incorrect result. Simplified variant looks like:
with |
xxx as ( |
select 2 /*as id*/, 'row 2' |
union all |
select 3, 'row 3' |
),
|
tab as ( |
select 1 as id, 'row 1' as txt |
union all |
select * from xxx |
)
|
select * from tab |
where id in (select id from xxx)}} |
that "mistakenly" resulting to:
id txt
|
1 row 1
|
2 row 2
|
3 row 3
|
however initially expected this (row 1 was unexpected):
id txt
|
2 row 2
|
3 row 3
|
however after attentive view the expectation is an error:
Unknown column 'id' in 'field list'
(in sub-select, because xxx really misses it).
Although the reason for that was a "mistake" from my side: forgotten the commented part as id that provides a column id to the xxx-statement, the wrong result is totally unexpected here.
By the way it looks like the union-ing in the second with-statement tab plays an important role by the issue, because somehow it seems to "follow" the column name id from this second statement (after union) in a sub-select to the first one (just doing it wrong).
If one rewrite it like this (with a column col instead of id which then completely missed in with-statements:
...
|
- where id in (select id from xxx)}}
|
+ where id in (select col from xxx)}} |
it would fail as expected with an error:
Unknown column 'col' in 'field list'
So a conjunction to id by a with-statement + union seems to arise there internally.