[FRG-20] casting fails for TIMESTAMP to DATE/TIME and VARBINARY to BINARY when selecting from one table to insert into another

[dynamobi-build]

[reporter="elin", created="Mon, 16 Jan 2006 16:22:51 -0500 (GMT-05:00)"] Casting for the data types below get an UnsupportedOperationException when you do the following procedure.

TIMESTAMP <-> TIME
TIMESTAMP <-> DATE
VARBINARY <-> BINARY
---------------------------
Procedure:
create table t1(pkey integer, colname varchar(20), coltmstamp timestamp, primary key(pkey));
insert into t1 values(1, 'TIME', '1945-2-24 12:42:25.34');

create table t2(coltime time primary key);
insert into t2 select coltmstamp from t1 where colname = 'TIME';


Error log:
Error: java.lang.UnsupportedOperationException: class java.lang.String: Cast from 'TIMESTAMP' to 'TIME' (state=,code=0)
net.sf.farrago.util.FarragoUtil$FarragoSqlException: java.lang.UnsupportedOperationException: class java.lang.String: Cast from 'TIMESTAMP' to 'TIME'
        at org.eigenbase.util.Util.needToImplement(Util.java:976)
        at com.disruptivetech.farrago.calc.CalcRexImplementorTableImpl$CastImplementor.implement(CalcRexImplementorTableImpl.java:1117)
        at com.disruptivetech.farrago.calc.RexToCalcTranslator.implementNode(RexToCalcTranslator.java:919)
        at com.disruptivetech.farrago.calc.RexToCalcTranslator.visitCall(RexToCalcTranslator.java:736)
        at org.eigenbase.rex.RexCall.accept(RexCall.java:201)
        at com.disruptivetech.farrago.calc.RexToCalcTranslator.implementNode(RexToCalcTranslator.java:716)
        at com.disruptivetech.farrago.calc.RexToCalcTranslator.getProgram(RexToCalcTranslator.java:480)
        at com.disruptivetech.farrago.calc.RexToCalcTranslator.getProgram(RexToCalcTranslator.java:398)
        at com.disruptivetech.farrago.rel.FennelCalcRel.toStreamDef(FennelCalcRel.java:188)
        at net.sf.farrago.query.FarragoRelImplementor.visitFennelChild(FarragoRelImplementor.java:93)
        at com.lucidera.lcs.LcsTableAppendRel.toStreamDef(LcsTableAppendRel.java:333)
        at net.sf.farrago.query.FarragoRelImplementor.visitFennelChild(FarragoRelImplementor.java:93)
        at net.sf.farrago.query.FennelToIteratorConverter.childToStreamDef(FennelToIteratorConverter.java:418)
        at net.sf.farrago.query.FennelToIteratorConverter.implement(FennelToIteratorConverter.java:146)
        at org.eigenbase.oj.rel.JavaRelImplementor.visitChildInternal(JavaRelImplementor.java:380)
        at net.sf.farrago.query.FarragoRelImplementor.visitChildInternal(FarragoRelImplementor.java:108)
        at org.eigenbase.oj.rel.JavaRelImplementor.visitChild(JavaRelImplementor.java:367)
        at org.eigenbase.oj.rel.JavaRelImplementor.visitJavaChild(JavaRelImplementor.java:341)
        at org.eigenbase.oj.rel.JavaRelImplementor.implementRoot(JavaRelImplementor.java:397)
        at net.sf.farrago.query.FarragoRelImplementor.implementRoot(FarragoRelImplementor.java:265)
        at org.eigenbase.oj.stmt.OJPreparingStmt.implement(OJPreparingStmt.java:301)
        at org.eigenbase.oj.stmt.OJPreparingStmt.prepareSql(OJPreparingStmt.java:256)
        at net.sf.farrago.query.FarragoPreparingStmt.prepare(FarragoPreparingStmt.java:238)
        at net.sf.farrago.db.FarragoDatabase$2.initializeEntry(FarragoDatabase.java:630)
        at net.sf.farrago.util.FarragoObjectCache.pin(FarragoObjectCache.java:157)
        at net.sf.farrago.db.FarragoDatabase.prepareStmtImpl(FarragoDatabase.java:640)
        at net.sf.farrago.db.FarragoDatabase.prepareStmt(FarragoDatabase.java:520)
        at net.sf.farrago.db.FarragoDbSession.prepareImpl(FarragoDbSession.java:773)
        at net.sf.farrago.db.FarragoDbSession.prepare(FarragoDbSession.java:724)
        at net.sf.farrago.db.FarragoDbStmtContext.prepare(FarragoDbStmtContext.java:152)
        at net.sf.farrago.jdbc.engine.FarragoJdbcEngineStatement.execute(FarragoJdbcEngineStatement.java:104)
        at sqlline.SqlLine$Commands.execute(Unknown Source)
        at sqlline.SqlLine$Commands.sql(Unknown Source)
        at sqlline.SqlLine.dispatch(Unknown Source)
        at sqlline.SqlLine.begin(Unknown Source)
        at sqlline.SqlLine.mainWithInputRedirection(Unknown Source)
        at sqlline.SqlLine.main(Unknown Source)

[dynamobi-build]

[author="jvs", created="Mon, 16 Jan 2006 16:30:45 -0500 (GMT-05:00)"] Angel, could you handle or reassign this one? The Java calc seems to codegen this correctly (at least for the TIMESTAMP->TIME case in the procedure; I didn't test the others.)

To verify that the Java calc works, or use it as a workaround, do

alter system set "calcVirtualMachine" = 'CALCVM_JAVA' before running the second INSERT.

[dynamobi-build]

[author="schoi", created="Fri, 24 Feb 2006 16:08:57 -0500 (GMT-05:00)"] even using java calc, this TIMESTAMP->TIME case does not seem to work: (this is with change 5586; not sure if it worked before that change)

create table t1(pkey integer, colname varchar(20), coltmstamp timestamp, primary key(pkey));
insert into t1 values(1, 'TIME', timestamp '1945-2-24 12:42:25.34');

create table t2(coltime time primary key);
insert into t2 select coltmstamp from t1 where colname = 'TIME';

Error: Cannot assign to target field 'COLTIME' of type TIME from source field 'COLTMSTAMP' of type TIMESTAMP (state=,code=0)
net.sf.farrago.util.FarragoUtil$FarragoSqlException: Cannot assign to target field 'COLTIME' of type TIME from source field 'COLTMSTAMP' of type TIMESTAMP
        at org.eigenbase.resource.EigenbaseResource$_Def13.ex(EigenbaseResource.java:780)
        at org.eigenbase.sql.validate.SqlValidatorImpl.checkTypeAssignment(SqlValidatorImpl.java:2315)
        at org.eigenbase.sql.validate.SqlValidatorImpl.validateInsert(SqlValidatorImpl.java:2282)
        at net.sf.farrago.query.FarragoSqlValidator.validateInsert(FarragoSqlValidator.java:159)
        at org.eigenbase.sql.SqlInsert.validate(SqlInsert.java:130)
        at org.eigenbase.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:448)
        at org.eigenbase.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:273)
        at net.sf.farrago.query.FarragoSqlValidator.validate(FarragoSqlValidator.java:62)
        at net.sf.farrago.db.FarragoDatabase.prepareStmtImpl(FarragoDatabase.java:594)
        at net.sf.farrago.db.FarragoDatabase.prepareStmt(FarragoDatabase.java:530)
        at net.sf.farrago.db.FarragoDbSession.prepareImpl(FarragoDbSession.java:769)
        at net.sf.farrago.db.FarragoDbSession.prepare(FarragoDbSession.java:720)
        at net.sf.farrago.db.FarragoDbStmtContext.prepare(FarragoDbStmtContext.java:154)
        at net.sf.farrago.jdbc.engine.FarragoJdbcEngineStatement.execute(FarragoJdbcEngineStatement.java:1

[dynamobi-build]

[author="jvs", created="Fri, 24 Feb 2006 16:12:26 -0500 (GMT-05:00)"] The new behavior is correct. We weren't supposed to be allowing you to store from TIMESTAMP -> TIME without a cast.

[dynamobi-build]

[author="jvs", created="Fri, 24 Feb 2006 16:14:15 -0500 (GMT-05:00)"] SQL:2003 Part 2 Section 4.6.2:

"A datetime is assignable to a site only if the source and target of the assignment are both of type DATE, or both
of type TIME (regardless whether WITH TIME ZONE or WITHOUT TIME ZONE is specified or implicit),
or both of type TIMESTAMP (regardless whether WITH TIME ZONE or WITHOUT TIME ZONE is specified
or implicit)."

[dynamobi-build]

[author="schoi", created="Fri, 24 Feb 2006 17:24:32 -0500 (GMT-05:00)"] ok; I can store from TIMESTAMP -> TIME with a cast.

but I still cannot store from TIME->TIMESTAMP (with a cast - table 3 in section 4.6.2)

simplest repro:
------------------------
values cast (cast ('00:00:00' as time) as timestamp);

Error: Cast function cannot convert value of type TIME to type TIMESTAMP (state=,code=0)

[dynamobi-build]

[author="schoi", created="Fri, 24 Feb 2006 17:26:31 -0500 (GMT-05:00)"] also DATE->TIMESTAMP

[dynamobi-build]

[author="jvs", created="Fri, 24 Feb 2006 17:32:05 -0500 (GMT-05:00)"] OK, I give up, this one's for real. At least for TIME->TIMESTAMP, it may not be purely a calc issue since we may need to handle it via an optimizer rule to get the CURRENT_DATE.

[dynamobi-build]

[author="schoi", created="Fri, 24 Feb 2006 17:42:33 -0500 (GMT-05:00)"] sorry; just a note that the original problem of the fennel calc is still there (TIMESTAMP->TIME with cast); but the workaround from FRG-20#action_10062 will work.


create table t (t time);
-- workaround
-- alter system set "calcVirtualMachine" = 'CALCVM_JAVA';
insert into t values cast(cast('1999-01-11 00:00:00' as timestamp) as time);

--> output:
Error: java.lang.UnsupportedOperationException: class java.lang.String: Cast from 'TIMESTAMP' to 'TIME' (state=,code=0)
net.sf.farrago.util.FarragoUtil$FarragoSqlException: java.lang.UnsupportedOperationException: class java.lang.String: Cast from 'TIMESTAMP' to 'TIME'
        at org.eigenbase.util.Util.needToImplement(Util.java:932)
        at com.disruptivetech.farrago.calc.CalcRexImplementorTableImpl$CastImplementor.implement(CalcRexImplementorTableImpl.java:1172)

[dynamobi-build]

[author="angel", created="Sun, 26 Feb 2006 18:48:32 -0500 (GMT-05:00)"] Eigenbase dt change 5622 partially fixes the casting problem.
Casting from time, date to timestamp is now allowed, and the fennel calc will do something similar to the java calc.

The results of the cast doesn't quite conform to the sql standard though. The current behavior pretty much just takes one datetime type (represented as milliseconds since epoch) and presents it as a different datetime type (with the same number of milliseconds since epoch). This results in the following behavior:

Casting from TIMESTAMP to DATE will still preserve the TIME component
Casting from TIMESTAMP to TIME will still preserve the DATE component
Casting from TIME to TIMESTAMP doesn't use the CURRENT_DATE.
  - Casting from a TIME literal to TIMESTAMP will have 1970-01-01 as the date
  - Casting from a TIMESTAMP to a TIME to a TIMESTAMP will result in the same original timestamp
Casting from DATE to TIMESTAMP doesn't set the TIME component to 0.
  - Casting from a DATE literal to TIMESTAMP will have a TIME of 0 (same as SQL standard)
  - Casting from a TIMESTAMP to a DATE to a TIMESTAMP will result in the same original timestamp

[dynamobi-build]

[author="jvs", created="Mon, 27 Feb 2006 00:53:58 -0500 (GMT-05:00)"] Angel, I'm thinking the right way to get to SQL conformance is to work at the rex level instead of the calc level (same strategy as for decimal). We'll need an optimizer rule to do it. I think we can do it all via string manipulation since the ISO format is easy. This might not be the most efficient mechanism, but it's not too bad. For example, suppose we want to handle TIME to TIMESTAMP. We can do that by rewriting

CAST(t AS TIMESTAMP)

to

CAST((CAST(CURRENT_DATE as CHAR(10)) || ' ' || CAST(t AS CHAR(8))) AS TIMESTAMP)

and so on. (This particular one will force usage of the Java calc since I don't think the Fennel calc supports CURRENT_DATE.)

What do you think?

[dynamobi-build]

[author="jvs", created="Mon, 27 Feb 2006 00:58:34 -0500 (GMT-05:00)"] Also, Angel, I'm running into a few merge conflicts because I don't think you were able to start from my latest changes (Julian hadn't merged them to dt). I'll merge once Jason fixes the dt build.

[dynamobi-build]

[author="elin", created="Thu, 19 Oct 2006 17:43:56 -0500 (GMT-05:00)"] Just more things noted - because of the way casting between timestamps and date or time works, order by for times or dates which have been inserted via some form of casting doesn't work as expected atm.

Are we supporting fractional seconds atm? Something like: values(time'4:5:5.34' = time'4:5:5')
will return false, which would be right if fractional seconds are supported.

[dynamobi-build]

[author="jvs", created="Mon, 16 Jul 2007 12:14:20 -0500 (GMT-05:00)"] Khanh noted that there have been fixes from Disruptive Tech recently, so we may be closer to resolution on this.

I added a 26-Jun-2007 REVIEW comment in SqlDateTimeWithoutTZ.SqlTimestamp.setCurrentDate regarding a remaining non-conformance.