RSS Feed

Method R Discussion Group

Can you post the test case that produced the wrong results in 10g with a RULE hint?

I've been pushing people to stop using the RULE optimizer and usually I've been successful.  However I do have some that are still holding back.  If I can clearly demonstrate it's not just performance features but also reliable results at stake it seems like a slam dunk argument.

This example affects versions >= 9.2.0.6 but the patch set for 10.1.0.5 has a fix implemented (of course, the fix causes another bug which I'm also showing below).  Depending on what version your running, you may or may not be able to reproduce this, but it's the example I have at hand.  I hope it helps!


The problem with this "bug" is that rule based optimization of a query with a range predicate on a concatenated index with NULL data in the index column used in the predicate can give wrong results.

Test case:
create table jci_test (user1 date,lnkey varchar2(20));
create index jci_user1 on jci_test (user1,lnkey);
insert into jci_test values (sysdate - 1.5,'AA1');
insert into jci_test values (sysdate - 1,'AA2');
insert into jci_test values (null,'AA3');
insert into jci_test values (null,null);
insert into jci_test values (sysdate - 1,null);
commit;
select /*+ RULE */ user1,lnkey from jci_test
  where user1 >= sysdate - 10;
 
This returns rows where user1 is NULL which it should not.

If the patch set has been applied with the fix for this problem, it then creates another bug (4925103) such that SQL that includes a MAX() function can return a wrong result (NULL) if the execution plan uses "FIRST ROW" optimization from an "INDEX RANGE SCAN (MIN/MAX)"
and the column contains NULL values in addition to non NULLs.

Test case:
create table TEST(
    col1 VARCHAR(2),
    col2 NUMBER
);
CREATE INDEX TEST_IDX ON TEST (col1,col2);
insert into TEST values('01',1);
insert into TEST values('01',2);
insert into TEST values('01',3);
insert into TEST values('01',NULL);
commit;
analyze table TEST compute statistics;

SELECT /*+ INDEX(TEST TEST_IDX) */ MAX(col2)
  FROM TEST
 WHERE col1 = '01'
  AND col2 > 0;
 
This returns a NULL if FIRST ROW optimization is used in the execution plan.