Re: [HACKERS] Subselects and NOTs - Mailing list pgsql-hackers

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