[jira] [Created] (TRAFODION-345) LP Bug: 1326060 - Select view fails to convert INTERVAL with ERROR 8422

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

Alice Chen created TRAFODION-345:
------------------------------------

             Summary: LP Bug: 1326060 - Select view fails to convert INTERVAL with ERROR 8422
                 Key: TRAFODION-345
                 URL: https://issues.apache.org/jira/browse/TRAFODION-345
             Project: Apache Trafodion
          Issue Type: Bug
          Components: sql-exe
            Reporter: Weishiun Tsai
            Assignee: Anoop Sharma
            Priority: Critical


The following sequence of statements fails at SELECT * FROM VIEW.  It returns ERROR 8422 complaining that the INTERVAL is not valid and cannot be converted.  This is seen on the GIT v0530_1530 build installed on a workstation.

Here is the entire script to reproduce this problem:

-------------------------------------------------------

create table mytab (
date_14 date not null not droppable primary key,
int_17  interval year to month not null,
unique  (date_14, int_17)
);

insert into mytab values
(date '2001-01-01', interval '01-01' year to month),
(date '2001-01-02', interval '02-02' year to month);

create view myview (noval1)
as select date_14 from mytab where date_14 > date '2002-07-23';

select * from myview;

-------------------------------------------------------

Here is the execution output from Trafodion:

>>create table mytab (
+>date_14 date not null not droppable primary key,
+>int_17  interval year to month not null,
+>unique  (date_14, int_17)
+>);

--- SQL operation complete.
>>
>>insert into mytab values
+>(date '2001-01-01', interval '01-01' year to month),
+>(date '2001-01-02', interval '02-02' year to month);

--- 2 row(s) inserted.
>>
>>create view myview (noval1)
+>as select date_14 from mytab where date_14 > date '2002-07-23';

--- SQL operation complete.
>>
>>select * from myview;

*** ERROR[8422] The provided INTERVAL is not valid and cannot be converted.

--- 0 row(s) selected.

-------------------------------------------------------

For reference purpose, this is the execution output from SQ: 

>>create table mytab (
+>date_14 date not null not droppable primary key,
+>int_17  interval year to month not null,
+>unique  (date_14, int_17)
+>);

--- SQL operation complete.
>>
>>insert into mytab values
+>(date '2001-01-01', interval '01-01' year to month),
+>(date '2001-01-02', interval '02-02' year to month);

--- 2 row(s) inserted.
>>
>>create view myview (noval1)
+>as select date_14 from mytab where date_14 > date '2002-07-23';

--- SQL operation complete.
>>
>>select * from myview;

--- 0 row(s) selected.



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