Unnesting returns wrong value in 12c 12.1.0.X
I have encountered this issue with Oracle 12c 12.1.0.1 and 12c 12.1.0.2 on Windows x64:
Using the hint /*+ unnest */ causes the query to return WRONG value (see below query # 1).
Issue does not appear in previous Oracle versions.
Please advise if bug tracked/present.
DROP TABLE table_master;
CREATE TABLE table_master (
id NUMBER,
description VARCHAR2(100),
CONSTRAINT pk_table_master PRIMARY KEY (id)
);
DROP TABLE table_detail;
CREATE TABLE table_detail (
id NUMBER,
id_master NUMBER,
CONSTRAINT pk_table_detail PRIMARY KEY (id)
);
CREATE INDEX i_table_detail ON table_detail(id_master);
INSERT INTO table_detail VALUES (1, NULL);
COMMIT;
#1
-- Query with /*+ unnest */ hint, wrong result, the execution plan uses NESTED_LOOPS