[jira] [Commented] (TRAFODION-123) LP Bug: 1233323 - SELECT query returns error 8415 when DATE type is in primary key

["Atanu Mishra (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/TRAFODION-123?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14696114#comment-14696114 ] 

Atanu Mishra commented on TRAFODION-123:
----------------------------------------

Weishiun Tsai (wei-shiun-tsai) wrote on 2013-10-23:	#1
Verified on a build from 10/23/2013, this problem is fixed:

>>create table t (id int no default not null, date1 date no default not null) pr
imary key (id, date1);

--- SQL operation complete.
>>insert into t values (3, date '2013-01-01'), (2, date '2011-01-01'), (1, date
'2012-01-01');

--- 3 row(s) inserted.
>>select id, date1 from t where id < 2;

ID DATE1
----------- ----------

          1 2012-01-01

--- 1 row(s) selected.
>>

Anoop Sharma (anoop-sharma) on 2013-10-31
Changed in trafodion:
assignee:	nobody → Anoop Sharma (anoop-sharma)
Weishiun Tsai (wei-shiun-tsai) on 2013-11-05
Changed in trafodion:
status:	New → Fix Released


> LP Bug: 1233323 - SELECT query returns error 8415 when DATE type is in primary key
> ----------------------------------------------------------------------------------
>
>                 Key: TRAFODION-123
>                 URL: https://issues.apache.org/jira/browse/TRAFODION-123
>             Project: Apache Trafodion
>          Issue Type: Bug
>          Components: sql-general
>            Reporter: Weishiun Tsai
>            Assignee: Anoop Sharma
>            Priority: Critical
>
> When the DATE type is used as a primary key, the SELECT query returns error 8415, as shown in the following example.  Also shown in the example is that the same query runs fine if DATE is not a primary key.  A similar query without the WHERE clause also runs fine when DATE is a primary key.
> >>cqd mode_seabase 'on';
> --- SQL operation complete.
> >>set schema seabase.phoenix;
> --- SQL operation complete.
> >>create table t (id int no default not null, date1 date no default not null) primary key (id);
> --- SQL operation complete.
> >>insert into t values (3, date '2013-01-01'), (2, date '2011-01-01'), (1, date '2012-01-01');
> --- 3 row(s) inserted.
> >>select id, date1 from t where id < 2;
> ID           DATE1
> -----------  ----------
>           1  2012-01-01
> --- 1 row(s) selected.
> >>drop table t;
> --- SQL operation complete.
> >>create table t (id int no default not null, date1 date no default not null) primary key (id, date1);
> --- SQL operation complete.
> >>insert into t values (3, date '2013-01-01'), (2, date '2011-01-01'), (1, date '2012-01-01');
> --- 3 row(s) inserted.
> >>select id, date1 from t where id < 2;                                         
> *** ERROR[8415] The provided DATE, TIME, or TIMESTAMP is not valid and cannot be converted.
> --- 0 row(s) selected.
> >>select id, date1 from t;
> ID           DATE1
> -----------  ----------
>           1  2012-01-01
>           2  2011-01-01
>           3  2013-01-01
> --- 3 row(s) selected.



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