[jira] [Created] (TRAFODION-344) LP Bug: 1325803 - select return INTERVAL is not valid and cannot be converted

["Alice Chen (JIRA)" <ji...@apache.org>]

Alice Chen created TRAFODION-344:
------------------------------------

             Summary: LP Bug: 1325803 - select return INTERVAL is not valid and cannot be converted
                 Key: TRAFODION-344
                 URL: https://issues.apache.org/jira/browse/TRAFODION-344
             Project: Apache Trafodion
          Issue Type: Bug
            Reporter: Apache Trafodion
            Assignee: Anoop Sharma
             Fix For: 0.8 (pre-incubation)


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;



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)