7 Replies Latest reply on Aug 23, 2006 5:57 AM by 405435

    BUG: when using RBO and index, query can return NULL values where it should

    405435
      Here is the test case:
      Connected to Oracle9i Enterprise Edition Release 9.2.0.6.0 
      Connected as tsku1134
      
      SQL> create table null_test(a number, b number);
      Table created
      
      SQL> insert into null_test values(null, 1);
      1 row inserted
      
      SQL> commit;
      Commit complete
      
      SQL> select /*+rule*/ * from null_test where a>1;
               A          B
      ---------- ----------
      So far, the results are correct. After creating the index, the row is returned:
      SQL> create index null_test_idx on null_test(a,b);
      Index created
      
      SQL> select /*+rule*/ * from null_test where a>1;
               A          B
      ---------- ----------
                          1
      
      SQL> select /*+first_rows*/ * from null_test where a>1;
               A          B
      ---------- ----------
      And this is even funnier, query like this should never return a row, because it contains contradictory conditions:
      SQL> select /*+rule*/ * from null_test where a>0 and a is null;
               A          B
      ---------- ----------
                          1
      Viliam