Skip to Main Content
  • Questions
  • LEFT JOIN on nested table and TABLE function: unexpected results

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Connor McDonald

Thanks for the question, Daniel.

Asked: July 07, 2016 - 4:55 pm UTC

Answered by: Connor McDonald - Last updated: July 09, 2016 - 1:50 am UTC

Category: Developer - Version: 11g Enterprise Edition Release 11.2.0.4.0

Viewed 1000+ times

Whilst you are here, check out some content from the AskTom team: Calculate Stock Market Returns Using SQL II

You Asked

Hi!

This is my first question here, so don't hesitate to tell me if I am unclear or if key information is missing.

I have been getting unexpected results while trying a LEFT JOIN on a nested table.

Here's my tables:
CREATE OR REPLACE TYPE row_identifier_test AS OBJECT
(
    identifier_number           VARCHAR2(15),
    update_date                 DATE
);

CREATE OR REPLACE TYPE tab_identifier_test AS TABLE OF row_identifier_test;

CREATE TABLE sample_test
(
  sample_id       NUMBER(16),
  identifiers tab_identifier_test DEFAULT tab_identifier_test(),
  CONSTRAINT sample_test_pk PRIMARY KEY (sample_id)
)
NESTED TABLE identifiers STORE AS sample_identifiers_table;

CREATE TABLE identifier_test
(
  identifier_id          NUMBER(16),
  identifier_number      VARCHAR2(15),
  CONSTRAINT identifier_test_pk PRIMARY KEY (identifier_id)
);

And here's the data:
INSERT INTO identifier_test VALUES (1, 'AAAAA');
INSERT INTO identifier_test VALUES (2, 'BBBBB');
INSERT INTO identifier_test VALUES (3, 'CCCCC');
INSERT INTO identifier_test VALUES (4, 'DDDDD');
INSERT INTO identifier_test VALUES (5, 'EEEEE');
INSERT INTO identifier_test VALUES (6, 'FFFFF');
INSERT INTO identifier_test VALUES (7, 'GGGGG');
INSERT INTO identifier_test VALUES (8, 'HHHHH');
INSERT INTO identifier_test VALUES (9, 'IIIII');

INSERT INTO sample_test VALUES (111, tab_identifier_test(row_identifier_test('AAAAA', SYSDATE),
                                                         row_identifier_test('BBBBB', SYSDATE),
                                                         row_identifier_test('CCCCC', SYSDATE),
                                                         row_identifier_test('WWWWW', SYSDATE)));
INSERT INTO sample_test VALUES (222, tab_identifier_test(row_identifier_test('DDDDD', SYSDATE),
                                                         row_identifier_test('EEEEE', SYSDATE),
                                                         row_identifier_test('FFFFF', SYSDATE),
                                                         row_identifier_test('XXXXX', SYSDATE)));
INSERT INTO sample_test VALUES (333, tab_identifier_test(row_identifier_test('AAAAA', SYSDATE),
                                                         row_identifier_test('DDDDD', SYSDATE),
                                                         row_identifier_test('YYYYY', SYSDATE)));

My goal is to output ALL rows of the identifiers nested table for the sample 111, along with the corresponding rows of the identifier_test table (left join on identifier_number).

(1) I am not very familiar with nested tables, so my first try looked like this:
SELECT *
  FROM TABLE (SELECT identifiers FROM sample_test WHERE sample_id = 111) nest
  LEFT JOIN identifier_test b
    ON b.identifier_number = nest.identifier_number
 ORDER BY nest.identifier_number

The query "worked", but in the results it looks like the condition sample_id = 111 was ignore since data from samples 222 and 333 also have been outputed (without their corresponding rows of the identifier_test table though).

Results:
IDENTIFIER_NUMBER         UPDATE_DATE IDENTIFIER_ID IDENTIFIER_NUMBER
----------------- ------------------- ------------- -----------------
AAAAA             2016-07-07 09:48:53             1 AAAAA            
AAAAA             2016-07-07 09:49:41                                
BBBBB             2016-07-07 09:48:53             2 BBBBB            
CCCCC             2016-07-07 09:48:53             3 CCCCC            
DDDDD             2016-07-07 09:49:41                                
DDDDD             2016-07-07 09:49:39                                
EEEEE             2016-07-07 09:49:39                                
FFFFF             2016-07-07 09:49:39                                
WWWWW             2016-07-07 09:48:53                                
XXXXX             2016-07-07 09:49:39                                
YYYYY             2016-07-07 09:49:41                                

(2) Since nested tables are new to me, I thought I might be using the TABLE function the wrong way, so I rewrote the 2nd line of the query and I got the expected results:
SELECT *
  FROM (SELECT * FROM sample_test, TABLE(identifiers) WHERE sample_id = 111) nest
  LEFT JOIN identifier_test b
    ON b.identifier_number = nest.identifier_number
 ORDER BY nest.identifier_number

SAMPLE_ID IDENTIFIERS  IDENTIFIER_NUMBER         UPDATE_DATE IDENTIFIER_ID IDENTIFIER_NUMBER
--------- ------------ ----------------- ------------------- ------------- -----------------
      111 <Collection> AAAAA             2016-07-07 09:48:53             1 AAAAA            
      111 <Collection> BBBBB             2016-07-07 09:48:53             2 BBBBB            
      111 <Collection> CCCCC             2016-07-07 09:48:53             3 CCCCC            
      111 <Collection> WWWWW             2016-07-07 09:48:53                                

Can you tell me why I did not get the expected results with my 1st query? Why am I getting results from samples 222 and 333?

Thanks!
Daniel

and we said...

Hi Daniel,

No issues about clarity - you've given us a nice concise test case, which puts you WAY ahead of the game :-)

I think you've perhaps come onto a query transformation bug. Here's your test case


SQL> drop table sample_test purge;

Table dropped.

SQL> drop table identifier_test purge;

Table dropped.

SQL> drop type tab_identifier_test;

Type dropped.

SQL> drop TYPE row_identifier_test;

Type dropped.

SQL>
SQL> CREATE OR REPLACE TYPE row_identifier_test AS OBJECT
  2  (
  3      identifier_number           VARCHAR2(15),
  4      update_date                 DATE
  5  );
  6  /

Type created.

SQL>
SQL> CREATE OR REPLACE TYPE tab_identifier_test AS TABLE OF row_identifier_test;
  2  /

Type created.

SQL>
SQL> CREATE TABLE sample_test
  2  (
  3    sample_id       NUMBER(16),
  4    identifiers tab_identifier_test DEFAULT tab_identifier_test(),
  5    CONSTRAINT sample_test_pk PRIMARY KEY (sample_id)
  6  )
  7  NESTED TABLE identifiers STORE AS sample_identifiers_table;

Table created.

SQL>
SQL> CREATE TABLE identifier_test
  2  (
  3    identifier_id          NUMBER(16),
  4    identifier_number      VARCHAR2(15),
  5    CONSTRAINT identifier_test_pk PRIMARY KEY (identifier_id)
  6  );

Table created.

SQL>
SQL>
SQL> INSERT INTO identifier_test VALUES (1, 'AAAAA');

1 row created.

SQL> INSERT INTO identifier_test VALUES (2, 'BBBBB');

1 row created.

SQL> INSERT INTO identifier_test VALUES (3, 'CCCCC');

1 row created.

SQL> INSERT INTO identifier_test VALUES (4, 'DDDDD');

1 row created.

SQL> INSERT INTO identifier_test VALUES (5, 'EEEEE');

1 row created.

SQL> INSERT INTO identifier_test VALUES (6, 'FFFFF');

1 row created.

SQL> INSERT INTO identifier_test VALUES (7, 'GGGGG');

1 row created.

SQL> INSERT INTO identifier_test VALUES (8, 'HHHHH');

1 row created.

SQL> INSERT INTO identifier_test VALUES (9, 'IIIII');

1 row created.

SQL>
SQL> INSERT INTO sample_test VALUES (111,
  2  tab_identifier_test(
  3  row_identifier_test('AAAAA', SYSDATE),
  4  row_identifier_test('BBBBB', SYSDATE),
  5  row_identifier_test('CCCCC', SYSDATE),
  6  row_identifier_test('WWWWW', SYSDATE)));

1 row created.

SQL>
SQL> INSERT INTO sample_test VALUES (222,
  2  tab_identifier_test(row_identifier_test('DDDDD', SYSDATE),
  3  row_identifier_test('EEEEE', SYSDATE),
  4  row_identifier_test('FFFFF', SYSDATE),
  5  row_identifier_test('XXXXX', SYSDATE)));

1 row created.

SQL>
SQL> INSERT INTO sample_test VALUES (333,
  2  tab_identifier_test(row_identifier_test('AAAAA', SYSDATE),
  3  row_identifier_test('DDDDD', SYSDATE),
  4  row_identifier_test('YYYYY', SYSDATE)));

1 row created.

SQL>
SQL> SELECT *
  2    FROM TABLE(SELECT identifiers FROM sample_test WHERE sample_id = 111) nest
  3    LEFT JOIN identifier_test b
  4      ON b.identifier_number = nest.identifier_number
  5   ORDER BY nest.identifier_number;

IDENTIFIER_NUMB UPDATE_DA IDENTIFIER_ID IDENTIFIER_NUMB
--------------- --------- ------------- ---------------
AAAAA           08-JUL-16             1 AAAAA
AAAAA           08-JUL-16
BBBBB           08-JUL-16             2 BBBBB
CCCCC           08-JUL-16             3 CCCCC
DDDDD           08-JUL-16
DDDDD           08-JUL-16
EEEEE           08-JUL-16
FFFFF           08-JUL-16
WWWWW           08-JUL-16
XXXXX           08-JUL-16
YYYYY           08-JUL-16

11 rows selected.


Now that last query should be equivalent the following one where I've used the conventional join syntax
SQL> SELECT *
  2    FROM TABLE(SELECT identifiers FROM sample_test WHERE sample_id = 111) nest
  3    ,identifier_test b
  4    where  nest.identifier_number = b.identifier_number(+)
  5   ORDER BY nest.identifier_number;

IDENTIFIER_NUMB UPDATE_DA IDENTIFIER_ID IDENTIFIER_NUMB
--------------- --------- ------------- ---------------
AAAAA           08-JUL-16             1 AAAAA
BBBBB           08-JUL-16             2 BBBBB
CCCCC           08-JUL-16             3 CCCCC
WWWWW           08-JUL-16

4 rows selected.

SQL>
SQL>
SQL>


So looks like an issue with the ansi join syntax. I'd suggest you log it with MOS.

Hope this helps.


and you rated our response

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

Trace and bug

July 08, 2016 - 8:43 am UTC

Reviewer: Ghassan

Do a trace to find how oracle rewrites the query when using ansi .
It seems that is a known bug .
See also
https://support.oracle.com/rs?type=bug&id=20363558

Thanks

July 08, 2016 - 1:20 pm UTC

Reviewer: Daniel

Thanks! I am disappointed with the 2 syntax not giving the same result, but I am very happy with your fast, accurate, helpful answer! Thanks again!
Connor McDonald

Followup  

July 09, 2016 - 1:50 am UTC

Glad we could help