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.
Thinking about that a bit, I guess the behavior is correct - the column id exists in context of whole select (it simply means the id of main), so it simply quasi the same as this:
- where id in (select id from xxx)
+ where id in (select id /* "twice" */)
# or basically this as a result:
and has nothing to do with a with-statement "conjunction" that I thought existed here.
So basically it is weird constellation but fully correct result for that statement. I was just confused by a large statement on that I working before what after all resulted to a wrong simplification and incorrect assumption.
Thus can be closed as incorrect.
Sorry for noise.