Skip site navigation (1) Skip section navigation (2)

Subselect strange behaviour - bug?

From: Mario Splivalo <mario(dot)splivalo(at)megafon(dot)hr>
To: pgsql-sql(at)postgresql(dot)org
Subject: Subselect strange behaviour - bug?
Date: 2008-03-16 17:47:38
Message-ID: 47DD5D3A.6060100@megafon.hr (view raw or flat)
Thread:
Lists: 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

Responses

pgsql-sql by date

Next:From: Tom LaneDate: 2008-03-16 18:28:08
Subject: Re: Subselect strange behaviour - bug?
Previous:From: Aarni RuuhimäkiDate: 2008-03-15 17:06:43
Subject: Re: Counting days ...

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group