Re: [GENERAL] why isn't this subquery wrong?

2017-04-20 Thread David G. Johnston
On Thu, Apr 20, 2017 at 3:43 PM, Tom Lane wrote: > jonathan vanasco writes: > > Can anyone explain to me why the following is valid (running 9.6) ? > > > SELECT foo_id > > FROM example_a__data > > WHERE foo_id IN (SELECT bar_id FROM

Re: [GENERAL] why isn't this subquery wrong?

2017-04-20 Thread David G. Johnston
On Thu, Apr 20, 2017 at 3:17 PM, jonathan vanasco wrote: > > SELECT foo_id > FROM example_a__data > WHERE foo_id IN (SELECT bar_id FROM example_a__rollup) > ; > > > ​Or write it the idiomatic way (i.e., as a proper semi-join): ​SELECT foo_id FROM example_a__data WHERE EXISTS

Re: [GENERAL] why isn't this subquery wrong?

2017-04-20 Thread Steve Crawford
On Thu, Apr 20, 2017 at 3:56 PM, jonathan vanasco wrote: > thanks all! > > On Apr 20, 2017, at 6:42 PM, David G. Johnston wrote: > > ​Subqueries can see all columns of the parent. When the subquery actually > uses one of them it is called a "correlated subquery". > > > i

Re: [GENERAL] why isn't this subquery wrong?

2017-04-20 Thread jonathan vanasco
thanks all! On Apr 20, 2017, at 6:42 PM, David G. Johnston wrote: > ​Subqueries can see all columns of the parent. When the subquery actually > uses one of them it is called a "correlated subquery". i thought a correlated subquery had to note that table/alias, not a raw column. I guess

Re: [GENERAL] why isn't this subquery wrong?

2017-04-20 Thread Tom Lane
jonathan vanasco writes: > Can anyone explain to me why the following is valid (running 9.6) ? > SELECT foo_id > FROM example_a__data > WHERE foo_id IN (SELECT bar_id FROM example_a__rollup) > ; Per the SQL standard, bar_id is interpreted as an "outer

Re: [GENERAL] why isn't this subquery wrong?

2017-04-20 Thread David G. Johnston
On Thu, Apr 20, 2017 at 3:17 PM, jonathan vanasco wrote: > postgres doesn't raise an error because example_a__data does have a bar_id > -- but example_a__rollup doesn't and there's no explicit correlation in the > query. > > ​Subqueries can see all columns of the parent. When

Re: [GENERAL] why isn't this subquery wrong?

2017-04-20 Thread Steve Crawford
On Thu, Apr 20, 2017 at 3:17 PM, jonathan vanasco wrote: > > I ran into an issue while changing a database schema around. Some queries > still worked, even though I didn't expect them to. > > Can anyone explain to me why the following is valid (running 9.6) ? > > schema > >

[GENERAL] why isn't this subquery wrong?

2017-04-20 Thread jonathan vanasco
I ran into an issue while changing a database schema around. Some queries still worked, even though I didn't expect them to. Can anyone explain to me why the following is valid (running 9.6) ? schema CREATE TEMPORARY TABLE example_a__data ( foo_id INT, bar_id INT );