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
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.