Thread: 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

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

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

>
> vac=> select count(*) from test where exists (select t1.y from test t1
where t1.y = x);
>
^
> Is this correlated subquery or not ?

No! Informix behaves in the same manner, I think it is ok.

Andreas

>
> 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

> 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 ?

Yes