[FRG-22] char/varchar datatypes which aren't in time/timestamp format are incorrectly allowed to be inserted into time/timestamp datatype column

[dynamobi-build]

[reporter="elin", created="Tue, 17 Jan 2006 18:04:28 -0500 (GMT-05:00)"] Here's a few examples below.

create table t1(colname varchar(20),colchar char(17), colvchar varchar(100));
insert into t1 values('BADDATA', '34-12', '45-56:78');
create table t2(coltime time);
insert into t2 select colvchar from t1 where colname = 'BADDATA';
insert into t2 select colchar from t1 where colname = 'BADDATA';
select * from t2;

+-----------+
| COLTIME |
+-----------+
| 21:57:18 | <--- was 45-56:78
| 10:12:00 | <--- was 34-12
+-----------+

============================

create table t1(colchar char(17));
insert into t1 values ('2323-6-26');
create table t2(colts timestamp);
insert into t2 select colchar from t1;
select * from t2;
+------------------------+
| COLTS |
+------------------------+
| 2323-09-30 19:06:26.0 | <--- was 2323-6-26
+------------------------+

[dynamobi-build]

[author="jvs", created="Wed, 18 Jan 2006 10:46:13 -0500 (GMT-05:00)"] Angel, over to you for dispatch on this one too. The Java calc does the right thing, rejecting the rows with the error messages below. The Fennel calc (which is used by default) is causing the problem. Note that Liz used LucidDB in the scripts to reproduce, which is why she didn't have to specify a primary key.

Java calc error messages:

Cannot assign '34-12 ' to a TIME value: not in format 'HH:mm:ss'

and

Cannot assign '2323-6-26' to a TIMESTAMP value: not in format 'yyyy-MM-dd HH:mm:ss'

[dynamobi-build]

[author="angel", created="Mon, 20 Feb 2006 16:24:49 -0500 (GMT-05:00)"] Fixed with eigenbase dt change 5518.

[dynamobi-build]

[author="jvs", created="Mon, 20 Feb 2006 16:29:46 -0500 (GMT-05:00)"] Note to Liz: when Angel says "dt change 5518" she means it's on the dt/dev branch, so it won't be immediately visible on LucidEra's branch (lu/dev). It'll come over during the next integration, which I'll probably attempt tonight.

[dynamobi-build]

[author="schoi", created="Thu, 23 Feb 2006 00:25:27 -0500 (GMT-05:00)"] Fennel calc and Java calc now have different behaviors; Fennel requires literal to have prefix identifying TIME,TIMESTAMP, but Java implicitly casts.

-- Java:
alter system set "calcVirtualMachine"='CALCVM_JAVA';
create schema s;
set schema 's';

create table t(coltime time primary key, coldate date, coltimestmp timestamp);

insert into t values('0:0:0', '1917-11-7','9-9-30 21:19:51.175');
select * from t;

+-----------+-------------+--------------------------+
| COLTIME | COLDATE | COLTIMESTMP|
+-----------+-------------+--------------------------+
| 00:00:00 | 1917-11-07 | 0009-09-30 21:19:51.175 |
+-----------+-------------+--------------------------+


-- Fennel
alter system set "calcVirtualMachine"='CALCVM_FENNEL';
create schema s;
set schema 's';

create table t(coltime time primary key, coldate date, coltimestmp timestamp);

insert into t values('0:0:0', '1917-11-7','9-9-30 21:19:51.175');

=> Error: could not calculate results for the following row:
[ 0 ]
Messages:
[0]:PC=0 Code=22007 (state=,code=0)
net.sf.farrago.util.FarragoUtil$FarragoSqlException: could not calculate results for the following row:
[ 0 ]

insert into t values(TIME'0:0:0', DATE'1917-11-7', TIMESTAMP'9-9-30 21:19:51.175');

select * from t;
=> same output as Java calc

[dynamobi-build]

[author="jvs", created="Thu, 23 Feb 2006 01:24:04 -0500 (GMT-05:00)"] Both behaviors are wrong because we should be preventing this at validation time (character string is not assignable to datetime without an explicit cast). See LDB-25.

[dynamobi-build]

[author="jvs", created="Thu, 23 Feb 2006 01:24:55 -0500 (GMT-05:00)"] Reassigned to myself and will resolve once I fix LDB-25.

[dynamobi-build]

[author="jvs", created="Fri, 24 Feb 2006 07:42:04 -0500 (GMT-05:00)"] Fixed in eigenchange 5586.