03 June, 2013

Bug 10013177 running Aggregation on Expression indexed by an FBI

I noticed an interesting Bug on MOS today.  When running an Aggregation against an Expression that has a Function Based Index, results may be truncated.  The bug notes that dumps and internal errors are also possible.

Here's a demo.  (tested in 11.2.0.2)

Start with a table and an FBI
SQL> create table test_10013177 (col_1 number(6,4));

Table created.

SQL> insert into test_10013177 values (12.3456);

1 row created.

SQL> create index index_10013177 on test_10013177(col_1+1);

Index created.

SQL>
SQL> exec dbms_stats.gather_table_stats('','TEST_10013177');

PL/SQL procedure successfully completed.

SQL> select col_1+1 from test_10013177;

   COL_1+1
----------
   13.3456

SQL> select max(col_1+1) from test_10013177;

MAX(COL_1+1)
------------
     13.3456

SQL> select /*+ full (t) */ max(col_1+1) from test_10013177 t;

MAX(COL_1+1)
------------
          13

SQL> 
Funnily, the results in the last query are truncated !
.
.
.

No comments: