pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSubselect strange behaviour - bug?
3+ messages / 2 participants
[nested] [flat]
* Subselect strange behaviour - bug?
@ 2008-03-16 17:47 Mario Splivalo <mario.splivalo@megafon.hr>
2008-03-16 18:28 ` Re: Subselect strange behaviour - bug? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Mario Splivalo @ 2008-03-16 17:47 UTC (permalink / raw)
To: pgsql-sql
I have two tables, 'configured' like this:
melem=# \d t1
Table "public.t1"
Column | Type | Modifiers
--------+-------------------+-----------
id | integer |
value | character varying |
melem=# \d t2
Table "public.t2"
Column | Type | Modifiers
--------+-------------------+-----------
id1 | integer |
value | character varying |
And here is the data from both tables:
melem=# select * from t1;
id | value
----+-------
1 | 1
2 | 2
3 | 3
(3 rows)
melem=# select * from t2;
id1 | value
-----+-------
1 | 1
2 | 2
3 | 3
4 | 4
(4 rows)
And here is the 'problematic' query:
melem=# select * from t2 where id1 in (select id1 from t1);
id1 | value
-----+-------
1 | 1
2 | 2
3 | 3
4 | 4
(4 rows)
I guess postgres should tell me that column name id1 is nonexistant in
table t1.
Now, if I change subselect to select correct column name, everything is ok:
melem=# select * from t2 where id1 in (select id from t1);
id1 | value
-----+-------
1 | 1
2 | 2
3 | 3
(3 rows)
I have found out that this 'error' pops up only if the columns in both
subselect query and the 'super'select query are the same. For instance:
melem=# select * from t2 where id1 in (select id2 from t1);
ERROR: column "id2" does not exist
LINE 1: select * from t2 where id1 in (select id2 from t1);
^
melem=#
I have tested this on postgres 8.3.0, 8.2.6 and 8.2.4, and the results
are the same. I donwloaded the sources for three mentioned versions of
postgres, and built it on Linux 2.6.22-14 (Ubuntu 7.10) using gcc 4.1.3,
as well as on Debian stable (Linux 2.6.22.1 with gcc 4.1.2).
Mario
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Subselect strange behaviour - bug?
2008-03-16 17:47 Subselect strange behaviour - bug? Mario Splivalo <mario.splivalo@megafon.hr>
@ 2008-03-16 18:28 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-03-17 12:04 ` Re: Subselect strange behaviour - bug? Mario Splivalo <mario.splivalo@megafon.hr>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2008-03-16 18:28 UTC (permalink / raw)
To: Mario Splivalo <mario.splivalo@megafon.hr>; +Cc: pgsql-sql
Mario Splivalo <mario.splivalo@megafon.hr> writes:
> And here is the 'problematic' query:
> melem=# select * from t2 where id1 in (select id1 from t1);
> I guess postgres should tell me that column name id1 is nonexistant in
> table t1.
No, it shouldn't, because that's a perfectly legal outer reference;
that is, what you wrote is equivalent to
select * from t2 where id1 in (select t2.id1 from t1);
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Subselect strange behaviour - bug?
2008-03-16 17:47 Subselect strange behaviour - bug? Mario Splivalo <mario.splivalo@megafon.hr>
2008-03-16 18:28 ` Re: Subselect strange behaviour - bug? Tom Lane <tgl@sss.pgh.pa.us>
@ 2008-03-17 12:04 ` Mario Splivalo <mario.splivalo@megafon.hr>
0 siblings, 0 replies; 3+ messages in thread
From: Mario Splivalo @ 2008-03-17 12:04 UTC (permalink / raw)
To: pgsql-sql
Tom Lane wrote:
> Mario Splivalo <mario.splivalo@megafon.hr> writes:
>
>> And here is the 'problematic' query:
>> melem=# select * from t2 where id1 in (select id1 from t1);
>>
>> I guess postgres should tell me that column name id1 is nonexistant in
>> table t1.
>>
>
> No, it shouldn't, because that's a perfectly legal outer reference;
> that is, what you wrote is equivalent to
> select * from t2 where id1 in (select t2.id1 from t1);
>
>
Huh, you're right. Appologies for the noise :)
Mario
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2008-03-17 12:04 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-03-16 17:47 Subselect strange behaviour - bug? Mario Splivalo <mario.splivalo@megafon.hr>
2008-03-16 18:28 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-03-17 12:04 ` Mario Splivalo <mario.splivalo@megafon.hr>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox