vac=> \d test

Table    = test
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| x                                | int4                             |     4 |
| y                                | int4                             |     4 |
+----------------------------------+----------------------------------+-------+
vac=> select count(*) from test where exists (select t1.y from test t1 where t1.y = x);
                                                                                    ^
Is this correlated subquery or not ?
(Note, that I don't use x with t1. prefix here)
With current parser this works as un-correlated subquery...
Is this Ok and I have to re-write query as

vac=> select count(*) from test t2 where exists 
                                ^^
(select t1.y from test t1 where t1.y = t2.x);
                                       ^^^
to get correlated one ?

Vadim

Fri, 06 Feb 1998 11:35:05 +0700
"Vadim B. Mikheev" <vadim@sable.krasnoyarsk.su>
what standard say ...
Vadim B. Mikheev wrote:

> vac=> \d test
>
> Table    = test
> +----------------------------------+----------------------------------+-------+
> |              Field               |              Type                | Length|
> +----------------------------------+----------------------------------+-------+
> | x                                | int4                             |     4 |
> | y                                | int4                             |     4 |
> +----------------------------------+----------------------------------+-------+
> vac=> select count(*) from test where exists (select t1.y from test t1 where t1.y = x);
>                                                                                     ^
> Is this correlated subquery or not ?
> (Note, that I don't use x with t1. prefix here)
> With current parser this works as un-correlated subquery...
> Is this Ok and I have to re-write query as
>
> vac=> select count(*) from test t2 where exists
>                                 ^^
> (select t1.y from test t1 where t1.y = t2.x);
>                                        ^^^
> to get correlated one ?

>From "The SQL Standard", 3rd ed., Date and Darwen:

"... each unqualified column name is _implicitly_ qualified by a range variable name
defined (explicitly or implicitly) in the nearest applicable FROM clause." (the emphasis
is from the book, not me)

It goes on to recommend reading the standard for full understanding, but it is pretty
clear that your interpretation is correct; in the example above x is implicitly equivalent
to t1.x.

                                                     - Tom


Fri, 06 Feb 1998 07:23:22 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] what standard say ...
Thomas G. Lockhart wrote:
> 
> > vac=> select count(*) from test where exists (select t1.y from test t1 where t1.y = x);
> >                                                                                     ^
> > Is this correlated subquery or not ?
> > (Note, that I don't use x with t1. prefix here)
> > With current parser this works as un-correlated subquery...
> 
> >From "The SQL Standard", 3rd ed., Date and Darwen:
> 
> "... each unqualified column name is _implicitly_ qualified by a range variable name
> defined (explicitly or implicitly) in the nearest applicable FROM clause." (the emphasis
> is from the book, not me)
> 
> It goes on to recommend reading the standard for full understanding, but it is pretty
> clear that your interpretation is correct; in the example above x is implicitly equivalent
> to t1.x.

Ok. Nice to know that we are correct here :)

Vadim

Fri, 06 Feb 1998 14:48:58 +0700
"Vadim B. Mikheev" <vadim@sable.krasnoyarsk.su>
Re: [HACKERS] what standard say ...
> 
> vac=> \d test
> 
> Table    = test
> +----------------------------------+----------------------------------+-------+
> |              Field               |              Type                | Length|
> +----------------------------------+----------------------------------+-------+
> | x                                | int4                             |     4 |
> | y                                | int4                             |     4 |
> +----------------------------------+----------------------------------+-------+
> vac=> select count(*) from test where exists (select t1.y from test t1 where t1.y = x);
>                                                                                     ^
> Is this correlated subquery or not ?
> (Note, that I don't use x with t1. prefix here)
> With current parser this works as un-correlated subquery...
> Is this Ok and I have to re-write query as
> 
> vac=> select count(*) from test t2 where exists 
>                                 ^^
> (select t1.y from test t1 where t1.y = t2.x);
>                                        ^^^
> to get correlated one ?
> 
> Vadim
> 
> 

I am almost sure this is uncorrelated.  If an unqualified varaiable
appears in a subquery, it matches the closest table it can find.

I am not sure about the standard, but logic would suggest this is the
way it should work.

And, of course, that is what the parser does.

-- 
Bruce Momjian
maillist@candle.pha.pa.us

Fri, 6 Feb 1998 11:23:57 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] what standard say ...