Re: [HACKERS] Bug about column references within subqueries used in selects

[Merlin Moncure]

On 4/12/07, NikhilS [EMAIL PROTECTED] wrote:

Hi,

Shouldn't the final command below cause a 'column b does not exist error'?

create table update_test (a int, b int);
create table supdate_test(x int, y int);
insert into update_test values (20, 30);
insert into supdate_test values (40, 50);
select a, (select b from supdate_test) from update_test;

 a  ?column?
-- -
2030

Is the problem with the code in colNameToVar or maybe we should add checks
in transformSubLink?


I don't think so...the columns of update_test are visible to the
scalar subquery...that way you can use fields from 'a' to filter the
subquery...
select a, (select y from supdate_test where x = a) from update_test;

merlin

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [HACKERS] Bug about column references within subqueries used in selects

[NikhilS]

Hi,

On 4/12/07, Merlin Moncure [EMAIL PROTECTED] wrote:


On 4/12/07, NikhilS [EMAIL PROTECTED] wrote:
 Hi,

 Shouldn't the final command below cause a 'column b does not exist
error'?

 create table update_test (a int, b int);
 create table supdate_test(x int, y int);
 insert into update_test values (20, 30);
 insert into supdate_test values (40, 50);
 select a, (select b from supdate_test) from update_test;

  a  ?column?
 -- -
 2030

 Is the problem with the code in colNameToVar or maybe we should add
checks
 in transformSubLink?

I don't think so...the columns of update_test are visible to the
scalar subquery...that way you can use fields from 'a' to filter the
subquery...
select a, (select y from supdate_test where x = a) from update_test;



Yes this is fine, but in select columnname from tablename using column
references of the other involved table is what I am objecting to.

Regards,
Nikhils


--
EnterpriseDB   http://www.enterprisedb.com

Re: [HACKERS] Bug about column references within subqueries used in selects

[Bort, Paul]

   
   I don't think so...the columns of update_test are visible to the
   scalar subquery...that way you can use fields from 'a' 
 to filter the
   subquery...
   select a, (select y from supdate_test where x = a) from 
 update_test; 
 
 
 Yes this is fine, but in select columnname from tablename 
 using column references of the other involved table is what I 
 am objecting to.
 

There's nothing here to object to, the system is acting correctly. Your
column name b is ambiguous, and the system takes the column b that
exists, rather than returning an error on a column that doesn't exist.
If you were explicit in your column name, you would get an error:

=# select a, (select supdate_test.b from supdate_test) from update_test;
ERROR:  No such attribute supdate_test.b 

Regards,
Paul

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] Bug about column references within subqueries used in selects

[Tom Lane]

NikhilS [EMAIL PROTECTED] writes:
 Yes this is fine, but in select columnname from tablename using column
 references of the other involved table is what I am objecting to.

You can object till you're blue in the face, but this behavior is not
changing because it's *required by spec*.  Outer references are a
standard and indeed essential part of SQL.

regards, tom lane

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org