Update: I reported about this bug to Oracle, and they fixed it in version 20.1
I found a “wrong results” bug yesterday, easily reproduced in 11g, 12c and 18c.
In short, we may get wrong results under the following circumstances:
- We have an Index-Organized Table (IOT) with multi-column primary key, populated with rows
- The table has a secondary index on part of the primary key columns
- We add another column to the existing IOT
- We select from the IOT while accessing it via the secondary index
Following is a simple example (also uploaded to livesql.oracle.com).
We create an index-organized table named iot, including the columns x and y.
The primary key is comprised of both columns.
SQL> create table iot ( 2 x number, 3 y number, 4 constraint iot_pk primary key (x,y) 5 ) organization index; Table created.
We create a secondary index on y:
SQL> create index secondary_idx on iot (y); Index created.
We populate the table with some rows:
SQL> insert into iot select rownum,mod(rownum,3) 2 from dual 3 connect by level<=7; 7 rows created. SQL> commit; Commit complete.
Now we add another column, z, to the table, and we set some value in z in one row:
SQL> alter table iot add z number; Table altered. SQL> update iot set z=42 where x=1; 1 row updated. SQL> commit; Commit complete.
So currently this is the content of the table:
SQL> select * from iot;
X Y Z
---------- ---------- ----------
1 1 42
2 2
3 0
4 1
5 2
6 0
7 1
7 rows selected.
Note that z contains the value 42 in the first row and null in all the other rows.
But if we access the table via the secondary index – for example getting all the records where y=1 – the value of z from the first row “spills over” into the other rows, and we get wrong results:
SQL> set autotrace on explain
SQL> select * from iot where y=1;
X Y Z
---------- ---------- ----------
1 1 42
4 1 42
7 1 42
3 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 177722221
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 117 | 1 (0)| 00:00:01 |
|* 1 | INDEX UNIQUE SCAN| IOT_PK | 3 | 117 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN| SECONDARY_IDX | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("Y"=1)
2 - access("Y"=1)
Note
-----
- dynamic sampling used for this statement (level=2)
It seems that the problem is not with the secondary index itself, as we get the correct rows, but something with accessing the table via the secondary index causes the wrong results.
If we hint the query to access the table directly we get the correct results:
SQL> select /*+ index_ffs (iot) */ * from iot where y=1;
X Y Z
---------- ---------- ----------
1 1 42
4 1
7 1
3 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3252171408
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 117 | 2 (0)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| IOT_PK | 3 | 117 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("Y"=1)
Note
-----
- dynamic sampling used for this statement (level=2)