Re: [SQL] Weird NOT IN effect with NULL values
> SELECT name > FROM customer > WHERE NOT EXISTS ( > SELECT customer_id > FROM salesorder > WHERE customer_id = customer.customer_id > ); > > Bruce, you may want to consider editing your next edition to include the > above modification. WHERE ... NOT IN is a bad idea for any subselect on > medium-large tables. FAQ item mentions this, and section 8.2 shows eqivalency at the end of the section. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [SQL] Weird NOT IN effect with NULL values
Frank Joerdens <[EMAIL PROTECTED]> writes: > When doing a subselect with NOT IN, as in > SELECT name > FROM customer > WHERE customer_id NOT IN ( > SELECT customer_id > FROM salesorder > ); > 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? This is correct because of SQL's 3-valued boolean logic. SQL92 defines "A NOT IN B" as equivalent to "NOT (A = SOME B)", and the latter has the rules c) If the implied is true for at least one row RT in T, then "R T" is true. d) If T is empty or if the implied is false for every row RT in T, then "R T" is false. e) If "R T" is neither true nor false, then it is unknown. Now the implied comparison will generate TRUE for the subselect rows that contain a matching customer_id, FALSE for the rows that contain non-matching (but not null) customer_id, and UNKNOWN (null) for the rows that contain nulls. So if you have nulls then case (d) never holds: the result of A = SOME B is either true or unknown. And so the result of NOT IN is either false or unknown, and either way the outer WHERE fails. This can be justified intuitively if you consider that null means "don't know": you can say for sure that the target customer_id IS in the subselect if you find it there, but you can't say for sure that it IS NOT there, because you don't know all the subselect result elements. Bottom line: you probably want to suppress nulls in the subselect... regards, tom lane
Re: [SQL] Weird NOT IN effect with NULL values
this is kind of weird but it is how it works. You cannot use equality for null... Null does not equal Null Null means no value, since it's not a value it can't equal anything another no value. SELECT name FROM customer WHERE customer_id NOT IN ( SELECT customer_id FROM salesorder ) and customer_id is not null; should work Ken 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. > > Regards, Frank
Re: [SQL] Weird NOT IN effect with NULL values
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= for at least one row, IN returns true so NOT IN returns false. If customer_id= is false for every row, IN returns false 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.
Re: [SQL] Weird NOT IN effect with NULL values
Frank Joerdens writes: > 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? It is correct. customer_id NOT IN (value1, value2, value3, ...) (which is what the subselect would essentially resolve to) is equivalent to NOT (customer_id = value1 OR customer_id = value2 OR customer_id = value3 ...) Say value2 is NULL. Then we have NOT (customer_id = value1 OR customer_id = NULL OR customer_id = value3 ...) NOT (customer_id = value1 OR NULL OR customer_id = value3 ...) NOT (NULL) NULL which means FALSE in a WHERE condition, so no rows are returned. Note that 'xxx = NULL' is different from 'xxx IS NULL'. Also note that NULL is not the same as FALSE in general. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] Weird NOT IN effect with NULL values
> 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. Read more in the book. It covers subqueries with nulls, bottom of pages 96. Not sure about web URL but it is in the subqueries section titled "NOT IN and Subqueries with NULL Values". -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [SQL] Weird NOT IN effect with NULL values
Mr. Joerdens, > 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 can see how that bug would happen. You may want to forward your e-mail to pgsql-bugs. Regardless, you'll find that you get faster results (as well as avoiding the NULL bug) if you use the following form of the query: SELECT name FROM customer WHERE NOT EXISTS ( SELECT customer_id FROM salesorder WHERE customer_id = customer.customer_id ); Bruce, you may want to consider editing your next edition to include the above modification. WHERE ... NOT IN is a bad idea for any subselect on medium-large tables. -Josh Berkus -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
[SQL] Weird NOT IN effect with NULL values
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. Regards, Frank
Re: [SQL] BufTableDelete: BufferLookup table corrupted
Blaise Carrupt <[EMAIL PROTECTED]> writes: > When I connect to database (with psql, e.g.), I receive the message > 'FATAL 1: BufTableDelete: BufferLookup table corrupted'. Try restarting the postmaster. AFAICS your problems are just with datastructures in shared memory, so a postmaster restart should clean them up. If you can figure out the sequence of actions that produced this state in the first place, please file a bug report with details ... regards, tom lane
Re: [SQL] BufTableDelete: BufferLookup table corrupted
I restarted Postmaster and it works now ! Thanks. The only thing I can say about it is that I was in the middle of a transaction executed in background, using 100% CPU, and I killed the postmaster forked for my transaction (I work under HP-UX). Maybe I shouldn't do this... :) ___ B. Carrupt
[SQL] BufTableDelete: BufferLookup table corrupted
Hi all ! I'm working with PostgreSQL 7.0.2, and I may have a little problem : my database seems to be corrupted. When I connect to database (with psql, e.g.), I receive the message 'FATAL 1: BufTableDelete: BufferLookup table corrupted'. If I insist, I can enter the database. But then, a couple of things doesn't work any more : \dt, to display tables, tells me 'ERROR: SearchSysCache: recursive use of cache 17'. Statements seem to work, but I'm afraid the problem could extend. So, is it so bad it seems to be ? Does someone has an idea how I can fix that problem without loosing all ? Thanks in advance. _ B. Carrupt
Re: [SQL] Array as parameter in plpgSQL functions
On Thu, 01 Feb 2001 09:47, Jie Liang wrote:
> e.g.
>
> create function foo(_int4) returns int2 as'
> declare
> a _int4 alias for $1;
> i int:=1;
> begin
> while a[i] loop
> i:=i+1;
> end loop;
> return i-1;
> end;
> ' language 'plpgsql';
>
> you can call it by:
>
> select foo('{1232131,12312321,3424234}');
>
> you should get 3.
In order to get it to work I had to alter it very slightly vis:-
chris=# create function array_element_count(_int4) returns integer as '
chris'# declare
chris'# a alias for $1;
chris'# i integer;
chris'# begin
chris'# i := 1;
chris'# while a[i] loop
chris'#i := i+1;
chris'# end loop;
chris'# return i-1;
chris'# end;' language 'plpgsql' with(isstrict,iscachable);
CREATE
chris=# select
array_element_count('{59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,8
7}');
array_element_count
-
29
(1 row)
--
Sincerely etc.,
NAME Christopher Sawtell
CELL PHONE 021 257 4451
ICQ UIN45863470
EMAIL csawtell @ xtra . co . nz
CNOTES ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz
-->> Please refrain from using HTML or WORD attachments in e-mails to me
<<--
