select return INTERVAL is not valid and cannot be converted

[ying-wen ku]

If interval is defined as part of the primrary key, select failed with error 8422.

SQL>SELECT * FROM Target_Rows WHERE pic_x_8 > 'AAA' and
        pic_x_8 < 'TROWC' and pic_x_8 is not null;

*** ERROR[8422] The provided INTERVAL is not valid and cannot be converted. [2014-06-03 03:24:49]

== test result
SQL>CREATE TABLE target_rows (
        char_3 char(3),
        pic_x_8 pic x(8) upshift not null,
        ih_to_s interval hour to second not null,
        PRIMARY KEY (pic_x_8, ih_to_s)
  );

--- SQL operation complete.

SQL>INSERT INTO target_rows (char_3, pic_x_8, ih_to_s)
                   VALUES ('AXX', 'TRowA',
                           interval '19:59:59.999999' hour to second),
                          ('BXX', 'TRowB',
                           interval '29:29:29.222222' hour to second),
                          ('CXX', 'TRowC',
                           interval '39:59:59.333333' hour to second),
                          ('DXX', 'TRowD',
                           interval '49:59:59.999999' hour to second),
                          ('EXX', 'TargRows',
                           interval '59:59:59.999999' hour to second);

--- 5 row(s) inserted.

SQL>showddl target_rows;

CREATE TABLE TRAFODION.DEBUG_ATOM.TARGET_ROWS
  (
    CHAR_3 CHAR(3) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , PIC_X_8 CHAR(8) CHARACTER SET ISO88591 COLLATE
      DEFAULT UPSHIFT NO DEFAULT NOT NULL NOT DROPPABLE
  , IH_TO_S INTERVAL HOUR(2) TO SECOND(6) NO DEFAULT
      NOT NULL NOT DROPPABLE
  , PRIMARY KEY (PIC_X_8 ASC, IH_TO_S ASC)
  )
;

--- SQL operation complete.

SQL>select * from target_rows;

CHAR_3 PIC_X_8 IH_TO_S
------ -------- ----------------
EXX TARGROWS 59:59:59.999999
AXX TROWA 19:59:59.999999
BXX TROWB 29:29:29.222222
CXX TROWC 39:59:59.333333
DXX TROWD 49:59:59.999999

--- 5 row(s) selected.

SQL>SELECT * FROM Target_Rows WHERE pic_x_8 > 'AAA' and
        pic_x_8 < 'TROWC' and pic_x_8 is not null;

*** ERROR[8422] The provided INTERVAL is not valid and cannot be converted. [2014-06-03 03:24:49]

SQL>SELECT * FROM Target_Rows WHERE pic_x_8 = 'TROWC' and pic_x_8 is not null;

CHAR_3 PIC_X_8 IH_TO_S
------ -------- ----------------
CXX TROWC 39:59:59.333333

--- 1 row(s) selected.

SQL>SELECT * FROM Target_Rows WHERE pic_x_8 = 'TROWC';

CHAR_3 PIC_X_8 IH_TO_S
------ -------- ----------------
CXX TROWC 39:59:59.333333

--- 1 row(s) selected.

SQL>log off;

-- test script:
log t4log clear;
drop SCHEMA debug_atom cascade;
CREATE SCHEMA debug_atom;
set schema debug_atom;
CREATE TABLE target_rows (
        char_3 char(3),
        pic_x_8 pic x(8) upshift not null,
        ih_to_s interval hour to second not null,
        PRIMARY KEY (pic_x_8, ih_to_s)
  );

INSERT INTO target_rows (char_3, pic_x_8, ih_to_s)
                   VALUES ('AXX', 'TRowA',
                           interval '19:59:59.999999' hour to second),

                          ('BXX', 'TRowB',
                           interval '29:29:29.222222' hour to second),

                          ('CXX', 'TRowC',
                           interval '39:59:59.333333' hour to second),

                          ('DXX', 'TRowD',
                           interval '49:59:59.999999' hour to second),

                          ('EXX', 'TargRows',
                           interval '59:59:59.999999' hour to second);

showddl target_rows;
select * from target_rows;
SELECT * FROM Target_Rows WHERE pic_x_8 > 'AAA' and
        pic_x_8 < 'TROWC' and pic_x_8 is not null;
SELECT * FROM Target_Rows WHERE pic_x_8 = 'TROWC' and pic_x_8 is not null;
SELECT * FROM Target_Rows WHERE pic_x_8 = 'TROWC';
log off;
exit;

[Anoop Sharma (anoop-sharma)]

fixed in july rc1 build