Thread

1. what standard say ...

   Vadim B. Mikheev <vadim@sable.krasnoyarsk.su> — 1998-02-06T04:35:05Z

   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
   
   

2. Re: [HACKERS] what standard say ...

   Thomas Lockhart <lockhart@alumni.caltech.edu> — 1998-02-06T07:23:22Z

   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
   
   
   

3. Re: [HACKERS] what standard say ...

   Vadim B. Mikheev <vadim@sable.krasnoyarsk.su> — 1998-02-06T07:48:58Z

   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
   
   

4. Re: [HACKERS] what standard say ...

   Bruce Momjian <maillist@candle.pha.pa.us> — 1998-02-06T16:23:57Z

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