Thread
-
Subselects and NOTs
Vadim Mikheev <vadim@sable.krasnoyarsk.su> — 1998-02-17T06:51:05Z
Oracle 6, there is NULL into table b: SQL> select * from a where x in (select * from b); X ---------- 2 SQL> select * from a where x not in (select * from b); no rows selected SQL> select * from a where not x in (select * from b); no rows selected Is 'not X in' the same as 'X not in' ? Currently we have: vac=> select * from a where not x in (select * from b); x - 1 (1 row) : subselect clause is "atomic" and NOT-s are never pushed into it. Once again - what standard says ? Vadim -
Re: [HACKERS] Subselects and NOTs
Bruce Momjian <maillist@candle.pha.pa.us> — 1998-02-17T15:59:32Z
> > Oracle 6, there is NULL into table b: > > SQL> select * from a where x in (select * from b); > > X > ---------- > 2 > > SQL> select * from a where x not in (select * from b); > > no rows selected > > SQL> select * from a where not x in (select * from b); > > no rows selected > > Is 'not X in' the same as 'X not in' ? Currently we have: I am not sure about this, but I believe 'not X in subselect' is evaluated as 'not (x in subselect)' and not as 'X not in subselect'. Am I missing something? There is also some interesting stuff about comparisons: ( 1,2,NULL) = (3, NULL,4) false ( 1,2,NULL) < (3, NULL,4) true ( 1,2,NULL) = (1, NULL,4) unknown ( 1,2,NULL) > (NULL, 2,4) unknown This happens because the comparisons are: left < right is true of and only if there exists some j such that Lj < Rj is true and for all i < j, and Li = Ri is true so it seems it compares these things from left to right, trying to make the comparison. For = and <>, is doesn't matter, but for the <, it does matter. Also they show: select * from test where x <> (select y from test2) When test2 returns no rows, the query returns no rows because the subquery returns a single row of NULL values. Hope this helps. I can give more detail if you want it. > > vac=> select * from a where not x in (select * from b); > x > - > 1 > (1 row) > > : subselect clause is "atomic" and NOT-s are never pushed into it. > > Once again - what standard says ? > > Vadim > > -- Bruce Momjian maillist@candle.pha.pa.us
-
Re: [HACKERS] Subselects and NOTs
Vadim Mikheev <vadim@sable.krasnoyarsk.su> — 1998-02-18T13:38:55Z
Bruce Momjian wrote: > > > > > Is 'not X in' the same as 'X not in' ? Currently we have: > > I am not sure about this, but I believe 'not X in subselect' is > evaluated as 'not (x in subselect)' and not as 'X not in subselect'. Am > I missing something? Even 'not (x in subselect)' doesn't help in Oracle! This works just as 'x not in subselect'. Ok, if we don't know what standard say then could you test this in Informix, etc. Don't forget put NULL into table in subselect to get 'no rows selected' for 'x not in subselect'... > > Also they show: > > select * > from test > where x <> (select y > from test2) > > When test2 returns no rows, the query returns no rows because the > subquery returns a single row of NULL values. This is exactly how it works in postgres and Oracle. Vadim
-
Re: [HACKERS] Subselects and NOTs
Thomas Lockhart <lockhart@alumni.caltech.edu> — 1998-02-18T15:33:17Z
> > > Is 'not X in' the same as 'X not in' ? Currently we have: > > I am not sure about this, but I believe 'not X in subselect' is > > evaluated as 'not (x in subselect)' and not as 'X not in subselect'. > > Even 'not (x in subselect)' doesn't help in Oracle! This works just > as 'x not in subselect'. > > Also they show: > > > > select * > > from test > > where x <> (select y > > from test2) > > > > When test2 returns no rows, the query returns no rows because the > > subquery returns a single row of NULL values. The SQL92 standard sez: 1) If NOT is specified in a <boolean test>, then let BP be the contained <boolean primary> and let TV be the contained <truth value>. The <boolean test> is equivalent to: ( NOT ( BP IS TV ) ) However, "a NOT IN b" is not the same as "NOT (a IN b)", and my SQL book points out that "IN" is shorthand for "=ANY" and "NOT IN" is shorthand for "<>ALL". Also, my Date book sez: In general, an all-or-any condition evaluates to TRUE if and only if the corresponding comparison condition without the ALL (or ANY, respectively) evaluates to TRUE for ALL (or ANY, respectively) of the rows in the table represented by the table expression. (NOTE: If that table is empty, the ALL conditions return TRUE, the ANY conditions return FALSE). So, it looks to me that WHERE x IN (SELECT y FROM empty_table) evaluates to FALSE, and WHERE x NOT IN (SELECT y FROM empty_table) evaluates to TRUE. I looked through my two reference books and my online draft copy of the SQL92 standard, and could not find much mention of operator precendence, and even less on "NOT" precendence. The only mention I could find was the statement ... Where the precedence of operators is determined by the Formats of this International Standard or by parentheses, those operators are ef- fectively applied in the order specified by that precedence. Where the precedence is not determined by the Formats or by parentheses, effective evaluation of expressions is generally performed from left to right. However, it is implementation-dependent whether ex- pressions are actually evaluated left to right, particularly when operands or operators might cause conditions to be raised or if the results of the expressions can be determined without completely evaluating all parts of the expression. However, it wasn't clear to me whether the above statement was referring to operators in expressions or to operators in the BNF notation used to define the language. Also, in a completely different place: Operations on numbers are performed according to the normal rules of arithmetic, within implementation-defined limits, except as provided for in Subclause 6.12, "<numeric value expression>". I can't believe that the standard isn't more explicit somewhere about operator precedence, or that it is strictly a left-to-right evaluation. - Tom