RE: 9205 Query behavior
- From: Natural Join B.V. <lex.de.haan@xxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 18 May 2004 17:23:35 MET
"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@xxxxxxxxxxxxxxxxx QuadTech, Sussex, WI
USA
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
