Last week, developers reported some unexpected behavior on a test system. I thought I would take advantage of the long weekend here in Hong Kong to build the simplest possible reproducible testcase. I find this is useful to not only in allowing others to easily test on their system, but also to distill exactly what is and is not relevant to the problem, an essential first step for understanding what is going on.
The result confirms something I think I heard from Jonathan Lewis, in that when you start to mix certain features that majority of customers are not using, in this case in-memory column store, tables with >255 columns, and NCHAR columns, then you can start to see some ‘interesting’ results.
For my testcase I am running 220.127.116.11 with 170418 DBBP.
Observe the first part of the setup, I simply use PL/SQL block to create a table with 256 columns (note the first is an NCHAR) and populate it with a couple of rows. The rows both have c1 set to ‘X’, and on one I populate the last field c256 with value 0, on the other it is NULL. A query from the the table where c1=’X’ returns both rows as expected.
SQL> DECLARE 2 sqltext VARCHAR2(32767); 3 BEGIN 4 sqltext := 'CREATE TABLE IM256 (C1 NCHAR(1), '; 5 FOR loopy IN 2..256 LOOP 6 sqltext := sqltext 7 || 'c' 8 || loopy 9 || ' NUMBER,'; 10 END LOOP; 11 -- Remove trailing comma and add close braket 12 sqltext := SUBSTR(sqltext, 1, LENGTH(SQLTEXT)-1) || ')' ; 13 EXECUTE IMMEDIATE sqltext; 14 END; 15 / PL/SQL procedure successfully completed. SQL> SQL> INSERT INTO IM256(C1, C256) VALUES ('X' , 0); 1 row inserted. SQL> INSERT INTO IM256(C1) VALUES ('X'); 1 row inserted. SQL> COMMIT; Commit complete. SQL> SQL> SELECT * FROM IM256 WHERE C1 = 'X'; X 0 X 2 rows selected. SQL>
As soon as I move the table into In-Memory Column Store, the query with the predicate starts to return only one row. The query without predicate still returns both rows.
SQL> ALTER TABLE IM256 INMEMORY NO MEMCOMPRESS; Table IM256 altered. SQL> SELECT * FROM IM256 WHERE C1 = 'X'; X 0 1 row selected. SQL> SELECT * FROM IM256; X 0 X 0 2 rows selected. SQL>
Stefan Koehler pointed me towards event 12099 which is mentioned in a couple of bug reports in this area, as a way to disable ‘turboscan’ and indeed setting this event causes the expected results to appear.
SQL> alter session set events '12099 trace name context forever, level 1'; Session altered. SQL> SELECT * FROM IM256 WHERE C1 = 'X'; X 0 X 2 rows selected.
Oracle support have identified this as bug 20508559 which is described in further detail in document 20508559.8 – Wrong Results / ORA-600 [kdmv_check_row] with more than 255 columns with nulls.
Edit: I have just tested the patch and after applying it the issue is resolved.