Topic
  • No replies
Callix
Callix
1 Post

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

‏2013-09-12T12:41:57Z |

DB2 V10.5 on Linux (Ubuntu 12.04 LTS), COMPATIBILITY MODE set to MYS

Code that is working with DB2 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