Re: Is this a bug, or am I loosing it

From: JayDBA <jaykash_at_hotmail.com>
Date: Thu, 1 Feb 2007 14:27:44 -0600
Message-ID: <BAY113-DAV5C62AC91B212C7B8C6766ACA40@phx.gbl>

Thanks for the link Jacques. I too am not very convinced with that, but one part of me says thats correct and the other says its not. What I fail to understand is, fine a <null> != <value> but there are 121K rows with NULL's and 31K rows with NOT NULL's in table XX. Shouldnt the rows with values return something. IF that is the case then the NOT EXISTS shouldnt have returned any values eiither.

I forgot to mention that we are on Oracle 10.1.0.3.0 on Solaris (64-bit).

Regards,
J

Original Message ----- From: Jacques Kilchoer To: jaykash_at_hotmail.com ; oracle-l-freelists Sent: Thursday, February 01, 2007 2:08 PM Subject: RE: Is this a bug, or am I loosing it

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:36834798025416

Tom Kyte says: with the null values included, it is "not known" if the values are in the table or not. His explanation never really convinced me that this is the "correct" way to handle the query, but who am I to criticize Tom Kyte?

  De : oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] De la part de JayDBA   Envoyé : jeudi, 1. février 2007 11:47
  À : oracle-l-freelists
  Objet : Is this a bug, or am I loosing it

I am getting weird results from a NOT IN and NOT EXISTS query. Could this be because of NULL's or is it a bug?

XX is the child table. YY is the parent table. There are some NULL's in XX.PH column whereas there are no NULL's in YY.PHONE_NUMBER.

DEVDB 1{MYACCT}> desc xx

   Name                         Null?    Type
   ---------------------------- -------- ------------------
   PH                                    NUMBER(10)

  DEVDB 1{MYACCT}> desc yy
   Name                         Null?    Type
   ---------------------------- -------- ------------------
   PHONE_NUMBER                 NOT NULL NUMBER(10)

DEVDB 1{MYACCT}> select count(*) from yy; JAY SAYS {ENTER} .. COUNT(*)

688431

1 row selected.

Elapsed: 00:00:00.54
DEVDB 1{MYACCT}> select count(*) from xx; JAY SAYS {ENTER} .. COUNT(*)

152892

1 row selected.

Elapsed: 00:00:00.36
DEVDB 1{MYACCT}> SELECT phone_number FROM yy WHERE phone_number NOT IN ( SELECT ph FROM xx);

no rows selected

  Elapsed: 00:00:28.93
  DEVDB 1{MYACCT}>
  DEVDB 1{MYACCT}> SELECT COUNT(phone_number) FROM yy WHERE NOT EXISTS (SELECT 1 FROM xx WHERE ph = phone_number);   JAY SAYS {ENTER} ...   COUNT(PHONE_NUMBER)

666936

1 row selected.

Elapsed: 00:00:01.46
DEVDB 1{MYACCT}> SELECT count(phone_number) FROM yy WHERE phone_number NOT IN ( SELECT ph FROM xx); JAY SAYS {ENTER} .. COUNT(PHONE_NUMBER)

1 row selected.

Elapsed: 00:00:31.83

DEVDB 1{MYACCT}> SELECT count(phone_number) FROM yy WHERE phone_number NOT IN ( SELECT ph FROM xx WHERE ph IS NOT NULL); JAY SAYS {ENTER} ... COUNT(PHONE_NUMBER)

666936

Regards,
J

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Feb 01 2007 - 14:27:44 CST