Thread: Erratic error message "ERROR: column "id_compte" does not exist"
Hi everybody, I've just run into a seemingly strange behaviour of postgresql and I'd like to know if it's normal or what. I've got a table "operation" in which there _isn't_ any column "id_compte". So when i do : "select id_compte from operation where not compta_g5;" I have : "ERROR: column "id_compte" does not exist" Everything ok until now. But when i put this query inside another query, i don't have anymore the error message : select distinct id_operation from compte where id_compte in (select id_compte from operation where not compta_g5); No error message. The postgresql version is the debian one : 7.4.7-2 What do you think of that ? David -- dpradier@apartia.fr - tel: 01.46.47.21.33 - fax: 01.46.47.21.37
David Pradier wrote: > But when i put this query inside another query, i don't have anymore the > error message : > select distinct id_operation from compte where id_compte in > (select id_compte from operation where not compta_g5); > No error message. It looks like it's binding to the outer query - where you seem to have an id_compte. -- Richard Huxton Archonet Ltd
Actually, the column "id_compte" is the primary key of the table "compte". The column "id_compte" doesn't exist in the table "operation". But i still don't understand why there is no error message, could you explain a little more ? David On Wed, Mar 16, 2005 at 11:49:28AM +0000, Richard Huxton wrote: > David Pradier wrote: > >But when i put this query inside another query, i don't have anymore the > >error message : > >select distinct id_operation from compte where id_compte in > >(select id_compte from operation where not compta_g5); > >No error message. > > It looks like it's binding to the outer query - where you seem to have > an id_compte. > > -- > Richard Huxton > Archonet Ltd > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match -- dpradier@apartia.fr - tel: 01.46.47.21.33 - fax: 01.46.47.21.37
David Pradier wrote: > Actually, the column "id_compte" is the primary key of the table "compte". > The column "id_compte" doesn't exist in the table "operation". > > But i still don't understand why there is no error message, could you > explain a little more ? I think it's intended for use in the WHERE clause: SELECT a.* FROM a WHERE a1 IN (SELECT b3 FROM b WHERE b2=a2) -- Richard Huxton Archonet Ltd
Ok, understood. One can access the columns of the main query from the subquery, therefore in my own query the column "id_compte" is found, therefore there is no error message. Doesn't this count as a bug ? On Wed, Mar 16, 2005 at 02:24:48PM +0000, Richard Huxton wrote: > David Pradier wrote: > >Actually, the column "id_compte" is the primary key of the table "compte". > >The column "id_compte" doesn't exist in the table "operation". > > > >But i still don't understand why there is no error message, could you > >explain a little more ? > > I think it's intended for use in the WHERE clause: > SELECT a.* FROM a WHERE a1 IN (SELECT b3 FROM b WHERE b2=a2) > > -- > Richard Huxton > Archonet Ltd > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org -- dpradier@apartia.fr - tel: 01.46.47.21.33 - fax: 01.46.47.21.37
David Pradier wrote: > Ok, understood. > One can access the columns of the main query from the subquery, > therefore in my own query the column "id_compte" is found, > therefore there is no error message. > > Doesn't this count as a bug ? Tricky, you could have something like: SELECT a.* FROM a WHERE (a1,a2) IN (SELECT a1,b3 FROM b ...) If that's legal, then so is your query. > On Wed, Mar 16, 2005 at 02:24:48PM +0000, Richard Huxton wrote: > >>David Pradier wrote: >> >>>Actually, the column "id_compte" is the primary key of the table "compte". >>>The column "id_compte" doesn't exist in the table "operation". >>> >>>But i still don't understand why there is no error message, could you >>>explain a little more ? >> >>I think it's intended for use in the WHERE clause: >> SELECT a.* FROM a WHERE a1 IN (SELECT b3 FROM b WHERE b2=a2) -- Richard Huxton Archonet Ltd
David Pradier <dpradier@apartia.fr> writes:
> One can access the columns of the main query from the subquery,
> therefore in my own query the column "id_compte" is found,
> therefore there is no error message.
> Doesn't this count as a bug ?
No; it's required behavior per the SQL specification. The spec doesn't
restrict the sub-query to reference outer columns in only some places;
it can use them anywhere.
(If you think that's weird, you should try putting the outer reference
in an aggregate function ... it then counts as an aggregate of the outer
query, not the inner. Also per spec.)
regards, tom lane
Ok then, if it's per spec, nothing to say. Thanks to everybody, Richard and Tom, for your time. PS : well yes, i think it is reasonably weird. I sure don't want to try and imagine the case you're proposing. Brain's too precious to burn. I'm confident in you to be assured it's weirder and weirdest :-) On Wed, Mar 16, 2005 at 10:17:17AM -0500, Tom Lane wrote: > David Pradier <dpradier@apartia.fr> writes: > > One can access the columns of the main query from the subquery, > > therefore in my own query the column "id_compte" is found, > > therefore there is no error message. > > > Doesn't this count as a bug ? > > No; it's required behavior per the SQL specification. The spec doesn't > restrict the sub-query to reference outer columns in only some places; > it can use them anywhere. > > (If you think that's weird, you should try putting the outer reference > in an aggregate function ... it then counts as an aggregate of the outer > query, not the inner. Also per spec.) > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org -- dpradier@apartia.fr - tel: 01.46.47.21.33 - fax: 01.46.47.21.37