Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Please ask technical questions in the appropriate category. Thank you!

OUTER JOIN and XMLTABLE does not always work

nikosMay 9 2018 — edited May 9 2018

I have been experimenting with XMLTABLE in combination with OUTER JOIN and have discovered that it does not always work correctly. I have created a test database and test cases below so that you can test this. The problematic behaviors I have experienced apply to both version 12c and 11g R2 on Windows but I guess the behavior is the same in other versions and platforms. My conclusion is that OUTER JOIN does not work correctly when there is a where clause in the XQuery statement of XMLTABLE and also when the XPath expression of XMLTABLE ends with a predicate (unless the condition uses a reverse axis, like parent or ancestor). A separate issue that I identified is that the let clause of the XQuery statement of XMLTABLE does not behave correctly either, perhaps only when the assigned value is a text node. Some of the test cases below demonstrate that problem. I hope these bugs can be fixed in a coming version.

CREATE SEQUENCE person_id_seq START WITH 1 INCREMENT BY 1;

CREATE TABLE person (

  pid integer NOT NULL PRIMARY KEY,

  pname varchar2(20) NOT NULL UNIQUE,

  contact XMLTYPE NOT NULL

);

INSERT INTO person VALUES

(person_id_seq.nextval,'Bob','<contact><tel type="home">163578</tel><tel type="work">867744</tel><tel type="mobile">073564889</tel><address pcode ="16102" city="Bromma"><street>Aaa</street><number>6</number></address></contact>');

INSERT INTO person VALUES

(person_id_seq.nextval,'Anne','<contact><tel type="home">373789</tel><tel type="mobile">073148964</tel><address pcode ="16429" city="Kista"><street>Lll</street><number>3</number></address></contact>');

INSERT INTO person VALUES

(person_id_seq.nextval,'Jeff','<contact><tel type="home">325305</tel><address pcode="16107" city="Bromma"><street>Aaa</street><number>4</number></address></contact>');

INSERT INTO person VALUES

(person_id_seq.nextval,'Robert','<contact><tel type="work">123435</tel><tel type="mobile">073552491</tel><address pcode ="16425" city="Kista"><street>Eee</street><number>1</number></address></contact>');

INSERT INTO person VALUES

(person_id_seq.nextval,'Lois','<contact><tel type="home">203045</tel><tel type="work">867334</tel><address pcode="16966" city="Solna"><street>Sss</street><number>3</number></address></contact>');

INSERT INTO person VALUES

(person_id_seq.nextval,'Clark','<contact><tel type="home">452678</tel><address pcode="16431" city="Kista"><street>Bbb</street><number>2</number></address></contact>');

INSERT INTO person VALUES

(person_id_seq.nextval,'Sarah','<contact><tel type="home">245578</tel><tel type="mobile">073691934</tel><address pcode ="16429" city="Kista"><street>Bbb</street><number>24</number></address></contact>');

INSERT INTO person VALUES

(person_id_seq.nextval,'James','<contact><tel type="home">192292</tel><tel type="mobile">073318175</tel><address pcode ="19735" city="Bro"><street>Ddd</street><number>112</number></address></contact>');

INSERT INTO person VALUES

(person_id_seq.nextval,'Eva','<contact><tel type="home">131187</tel><tel type="mobile">073559185</tel><tel type="work">191427</tel><address pcode ="16966" city="Solna"><street>Rrr</street><number>5</number></address></contact>');

INSERT INTO person VALUES

(person_id_seq.nextval,'Laura','<contact><address pcode ="18754" city="Taby"><street>Lll</street><number>3</number></address></contact>');

INSERT INTO person VALUES

(person_id_seq.nextval,'Gina','<contact><tel type="work">131187</tel><address pcode ="18754" city="Taby"><street>Lll</street><number>4</number></address></contact>');

-- Test cases with XMLTABLE and LEFT OUTER JOIN (some work, some don't)

-- Does not work

SELECT pname, telnumber

FROM person LEFT OUTER JOIN XMLTABLE('//tel[@type != "mobile"]' PASSING contact COLUMNS telnumber VARCHAR(10) PATH 'text()') x ON (1=1);

-- Works

SELECT pname, telnumber

FROM person LEFT OUTER JOIN XMLTABLE('//tel[@type != "mobile"]/.' PASSING contact COLUMNS telnumber VARCHAR(10) PATH 'text()') x ON (1=1);

-- Does not work

SELECT pname, telnumber

FROM person LEFT OUTER JOIN XMLTABLE('for $t in //tel where $t/@type != "mobile" return $t' PASSING contact COLUMNS telnumber VARCHAR(10) PATH 'text()') x ON (1=1);

-- Works

SELECT pname, telnumber

FROM person LEFT OUTER JOIN XMLTABLE('for $t in //tel/text() where $t/../@type != "mobile" return $t' PASSING contact COLUMNS telnumber VARCHAR(10) PATH '.') x ON (1=1);

-- Works

SELECT pname, telnumber

FROM person LEFT OUTER JOIN XMLTABLE('for $t in //tel/text() where $t/ancestor::tel/@type != "mobile" return $t' PASSING contact COLUMNS telnumber VARCHAR(10) PATH '.') x ON (1=1);

-- Does not work (and the result is unexpected)

SELECT pname, telnumber

FROM person LEFT OUTER JOIN XMLTABLE('for $t in //tel let $n := $t/text() where $t/@type != "mobile" return $n' PASSING contact COLUMNS telnumber VARCHAR(10) PATH '.') x ON (1=1);

-- Does not work (but does not give the unexpected behavior of the previous)

SELECT pname, telnumber

FROM person LEFT OUTER JOIN XMLTABLE('for $t in //tel, $n in $t/text() where $t/@type != "mobile" return $n' PASSING contact COLUMNS telnumber VARCHAR(10) PATH '.') x ON (1=1);

-- Does not work

SELECT pname, telnumber

FROM person LEFT OUTER JOIN XMLTABLE('for $t in //tel let $n := $t where $t/@type != "mobile" return $n' PASSING contact COLUMNS telnumber VARCHAR(10) PATH 'text()') x ON (1=1);

-- Does not work

SELECT pname, telnumber

FROM person LEFT OUTER JOIN XMLTABLE('for $t in //tel let $n := $t/text() where $t/@type != "mobile" return element X {$n}' PASSING contact COLUMNS telnumber VARCHAR(10) PATH '.') x ON (1=1);

-- Does not work

SELECT pname, telnumber

FROM person LEFT OUTER JOIN XMLTABLE('for $t in //tel, $n in $t/text() where $t/@type != "mobile" return element X {$n}' PASSING contact COLUMNS telnumber VARCHAR(10) PATH '.') x ON (1=1);

-- Works

SELECT pname, telnumber

FROM person LEFT OUTER JOIN XMLTABLE('//tel[@type != "mobile"]/text()' PASSING contact COLUMNS telnumber VARCHAR(10) PATH '.') x ON (1=1);

-- Works

SELECT pname, telnumber

FROM person LEFT OUTER JOIN XMLTABLE('//tel/text()[../@type != "mobile"]' PASSING contact COLUMNS telnumber VARCHAR(10) PATH '.') x ON (1=1);

-- Works

SELECT pname, telnumber

FROM person LEFT OUTER JOIN XMLTABLE('//tel/@type[. != "mobile"]/..' PASSING contact COLUMNS telnumber VARCHAR(10) PATH '.') x ON (1=1);

-- Works (but it is semantically different)

SELECT pname, telnumber

FROM person LEFT OUTER JOIN XMLTABLE('//tel' PASSING contact COLUMNS telnumber VARCHAR(10) PATH '.[@type != "mobile"x ON (1=1);

-- Works (but it is semantically different)

SELECT pname, telnumber

FROM person LEFT OUTER JOIN XMLTABLE('for $t in //tel return if ($t/@type != "mobile") then $t else ""' PASSING contact COLUMNS telnumber VARCHAR(10) PATH 'text()') x ON (1=1);

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 6 2018
Added on May 9 2018
1 comment
886 views