SQL Performance (MOSC)

MOSC Banner

Unnesting returns wrong value in 12c 12.1.0.X

edited Apr 27, 2015 10:48AM in SQL Performance (MOSC) 4 commentsAnswered ✓

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

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center