Topic
  • No replies
Callix
Callix
2 Posts

Pinned topic Is LIMIT...OFFSET with xmlcast column broken in V10.5 ?

‏2013-09-18T22:22:33Z |

DB2 Express-C V10.5 on Linux (Ubuntu 12.04 LTS, 64bits), COMPATIBILITY MODE set to MYS

Code that is working with DB2 Express-C V9.7 on Linux doesn't behave the same in V10.5. Has someone else been experiencing similar issue ?I

It seems that records returning a column with empty xmlquery that is xmlcast to a VARCHAR is ignored when OFFSET is applied.

Steps to reproduce:

CREATE TABLE test001 (ID INTEGER NOT NULL, TVAL VARCHAR(24),INFO XML );
    
INSERT INTO test001 VALUES(1, 'VALUE1', '<Info><Trail><Create><UserID>106</UserID><DateTime>2013-08-05 15:23:17</DateTime></Create><Launch><UserID>106</UserID><DateTime>2013-08-05 15:23:17</DateTime></Launch><Started><UserID>0</UserID><DateTime>2013-08-05 15:23:17</DateTime></Started><Completed><UserID>0</UserID><DateTime>2013-08-05 15:23:33</DateTime><Log>1375716197.1836.txt</Log></Completed></Trail></Info>');
INSERT INTO test001 VALUES(2, NULL, '<Info><Trail><Create><UserID>106</UserID><DateTime>2013-08-05 15:23:17</DateTime></Create><Launch><UserID>106</UserID><DateTime>2013-08-05 15:23:17</DateTime></Launch><Started><UserID>0</UserID><DateTime>2013-08-05 15:23:17</DateTime></Started></Trail></Info>');
INSERT INTO test001 VALUES(3, 'VALUE3','<Info><Trail><Create><UserID>106</UserID><DateTime>2013-08-05 15:23:17</DateTime></Create><Launch><UserID>106</UserID><DateTime>2013-08-05 15:23:17</DateTime></Launch><Started><UserID>0</UserID><DateTime>2013-08-05 15:23:17</DateTime></Started><Completed><UserID>0</UserID><DateTime>2013-08-05 15:23:33</DateTime><Log>1375716197.1836.txt</Log></Completed></Trail></Info>');

-- Record with ID 2    has no 'Completed' node in XML in column INFO



-- SELECT statement with xmlcast of xmlquery works as expected
select id, tval, xmlcast(xmlquery('$c/Info/Trail/Completed[1]/DateTime' passing INFO as "c") as varchar(24)) from test001 ORDER BY ID;

1    VALUE1    2013-08-05 15:23:33
2    <null>    <null>
3    VALUE3    2013-08-05 15:23:33


-- Works when applying a LIMIT clause (DB2 in MYS compatibility mode)
select id, tval, xmlcast(xmlquery('$c/Info/Trail/Completed[1]/DateTime' passing INFO as "c") as varchar(24)) from test001 ORDER BY ID LIMIT 2;

1    VALUE1    2013-08-05 15:23:33
2    <null>    <null>


-- DOESN'T Work with an OFFSET clause. Record with NULL XML cast column is ignored completely. We're expecting records with ID 1 and 2, as in query above.
select id, tval, xmlcast(xmlquery('$c/Info/Trail/Completed[1]/DateTime' passing INFO as "c") as varchar(24)) from test001 ORDER BY ID LIMIT 2 OFFSET 0;

1    VALUE1    2013-08-05 15:23:33
3    VALUE3    2013-08-05 15:23:33
 

select id, tval, xmlcast(xmlquery('$c/Info/Trail/Completed[1]/DateTime' passing INFO as "c") as varchar(24)) from test001 ORDER BY ID LIMIT 2 OFFSET 1

1    VALUE3    2013-08-05 15:23:33


-- It isn't related to the NULL value but the xmlcast
select id, tval from test001 ORDER BY ID LIMIT 2 OFFSET 0

1    VALUE1
2    <null>
 

-- Without the xmlcast(), records 1 and 2 are correctly returned...
select id, xmlquery('$c/Info/Trail/Completed[1]/DateTime' passing INFO as "c") from test001 ORDER BY ID LIMIT 2 OFFSET 0
 

1    <DateTime>2013-08-05 15:23:33</DateTime>
2    
 


 

Updated on 2013-09-18T22:29:19Z at 2013-09-18T22:29:19Z by Callix