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