Re: Weird NOT IN effect with NULL values

Поиск
Список
Период
Сортировка
От Stephan Szabo
Тема Re: Weird NOT IN effect with NULL values
Дата
Msg-id Pine.BSF.4.21.0103011104550.47142-100000@megazone23.bigpanda.com
обсуждение исходный текст
Ответ на Weird NOT IN effect with NULL values  (Frank Joerdens <frank@joerdens.de>)
Список pgsql-sql
On Thu, 1 Mar 2001, Frank Joerdens wrote:

> When doing a subselect with NOT IN, as in
> 
> SELECT name
> FROM customer
> WHERE customer_id NOT IN (
> SELECT customer_id
> FROM salesorder
> );
> 
> (from Bruce Momjian's book)
> 
> I get no rows if the result column returned by the subselect
> contains NULL values. It works as expected if I remove the NULL values
> from the result set. Is this behaviour correct and if so, why?
> 
> I am using 7.1 beta 4.

I believe it may be actually correct.  If my reading of the spec is
correct (which it possibly is not), customer_id NOT IN (subselect) is
effectively, NOT ( customer_id = ANY (subselect) ) and then:

Using the rules for ANY,
If customer_id=<inner customer_id> for at least one row, IN returns trueso NOT IN returns false.
If customer_id=<inner customer_id> is false for every row, IN returnsfalse so NOT IN returns true.
Otherwise IN and NOT IN both return unknown.

Since customer_id=NULL is unknown, you're getting at least one unknown in
the ANY expression so NOT IN doesn't return true, it returns unknown
which is not sufficient for making the where clause return the row.



В списке pgsql-sql по дате отправления:

Предыдущее
От: Peter Eisentraut
Дата:
Сообщение: Re: Weird NOT IN effect with NULL values
Следующее
От: Ken Kline
Дата:
Сообщение: Re: Weird NOT IN effect with NULL values