RE: 9205 Query behavior

From: Natural Join B.V. <lex.de.haan_at_naturaljoin.nl>
Date: Tue, 18 May 2004 17:23:35 MET
Message-Id: <20040518152335.5839D1D52FE@ha-smtp3.tiscali.nl>

"select * from (select distinct ename from emp)" returns wrong result if the ENAME column contains NULL values and has a unique index defined.

SQL> select banner from v$version where rownum=1;

BANNER

Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod

SCOTT SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

----- ---------- --------- -------- --------- -------- -------- --------
 7369 SMITH      CLERK         7902 17-DEC-80      800                20
 7499 ALLEN      SALESMAN      7698 20-FEB-81     1600      300       30
 7521 WARD       SALESMAN      7698 22-FEB-81     1250      500       30
 7566 JONES      MANAGER       7839 02-APR-81     2975                20
 7654 MARTIN     SALESMAN      7698 28-SEP-81     1250     1400       30
 7698 BLAKE      MANAGER       7839 01-MAY-81     2850                30
 7782 CLARK      MANAGER       7839 09-JUN-81     2450                10
 7788 SCOTT      ANALYST       7566 19-APR-87     3000                20
 7839 KING       PRESIDENT          17-NOV-81     5000                10
 7844 TURNER     SALESMAN      7698 08-SEP-81     1500        0       30
 7876 ADAMS      CLERK         7788 23-MAY-87     1100                20
 7900 JAMES      CLERK         7698 03-DEC-81      950                30
 7902 FORD       ANALYST       7566 03-DEC-81     3000                20
 7934 MILLER     CLERK         7782 23-JAN-82     1300                10

SCOTT SQL> update emp set ename=null where ename like 'A%'; 2 rows updated.

SCOTT SQL> create unique index blah on emp(ename); Index created.

SCOTT SQL> select distinct ename from emp;

ENAME

BLAKE
CLARK
FORD
JAMES
JONES
KING
MARTIN
MILLER
SCOTT
SMITH
TURNER
WARD 13 rows selected.

SCOTT SQL> select * from (select distinct ename from emp);

ENAME

SMITH
..
FORD
MILLER 14 rows selected.

Note that in both cases the index itself is not used; its presence is enough. If we drop the index, the problem is gone:

SCOTT SQL> drop index blah;
Index dropped.

SCOTT SQL> select distinct ename from emp;

ENAME

BLAKE
..
WARD 13 rows selected.

SCOTT SQL> select * from (select distinct ename from emp);

ENAME

CLARK
..
WARD 13 rows selected.

or, if you want to reduce the problem further:

create table lex (a number, b number);

begin
  insert into lex values (null,null);
  insert into lex values (null,null);
  for i in 1..8 loop
    insert into lex values (i,i);
  end loop;
end;
/

create unique index lex_ix on lex(a);
analyze table lex compute statistics;

select * from (select distinct a from lex);

This will return 10 rows, 2 with A as NULL, disregarding the DISTINCT. The plan shows the DISTINCT is removed.

> What's the gist of that bug, as it doesn't appear to be public?
>
> Rich
>
> Rich Jesse System/Database Administrator
> rich.jesse_at_quadtechworld.com QuadTech, Sussex, WI
USA

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Tue May 18 2004 - 10:21:50 CDT