search

LucidDB / luciddb

DEFUNCT: See README
https://github.com/LucidDB/luciddb
Apache License 2.0
53 stars 24 forks source link

[FRG-176] Illegal use of NULL exception during validation when using current_timestamp in export_schema_incremental_to_csv udp #696

Open dynamobi-build opened 12 years ago

dynamobi-build commented 12 years ago

[reporter="elin", created="Sat, 29 Jul 2006 13:22:09 -0500 (GMT-05:00)"] To repro:

create schema udptest;
set schema 'udptest';

create table mytable(cola int, lmts timestamp);

insert into mytable values
(1, current_timestamp),
(2, current_timestamp);

select sys_boot.mgmt.sleep(1000) from (values(0));

call applib.create_var('udptest', null, 'appvars for udptest');
call applib.create_var('udptest', 'startTs', 'current timestamp taken before tests');
call applib.set_var('udptest', 'startTs', cast(current_timestamp as varchar(25)));
call applib.flush_var('udptest', 'startTs');

insert into mytable values
(3, cast(applib.get_var('udptest', 'startTs') as timestamp)),
(4, TIMESTAMP'2007-12-12 12:12:12');

-- this causes the exception
call sys_root.export_schema_incremental_to_csv('LOCALDB', 'UDPTEST', false, 'MYTABLE', null, current_timestamp, 'LMTS', '/home/elin/local/inc', true, true);

-- this one has no problems
call sys_root.export_schema_incremental_to_csv('LOCALDB', 'UDPTEST', false, 'MYTABLE', null, TIMESTAMP'2006-12-12 12:12:12', 'LMTS', '/home/elin/local/inc', true, true);

+++++++++++++++++++++++++

Trace:
0: jdbc:luciddb:> call sys_root.export_schema_incremental_to_csv('LOCALDB', 'UDPTEST', false, 'MYTABLE', null, current_timestamp, 'LMTS', '/home/elin/local/inc', true, true);
Error: From line 1, column 88 to line 1, column 91: Illegal use of 'NULL' (state=,code=0)
net.sf.farrago.jdbc.FarragoJdbcUtil$FarragoSqlException: From line 1, column 88 to line 1, column 91: Illegal use of 'NULL'
        at org.eigenbase.resource.EigenbaseResource$_Def10.ex(EigenbaseResource.java:783)
        at org.eigenbase.sql.SqlUtil.newContextException(SqlUtil.java:729)
        at org.eigenbase.sql.SqlUtil.newContextException(SqlUtil.java:716)
        at org.eigenbase.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:2726)
        at org.eigenbase.sql.validate.SqlValidatorImpl.inferUnknownTypes(SqlValidatorImpl.java:1154)
        at org.eigenbase.sql.validate.SqlValidatorImpl.inferUnknownTypes(SqlValidatorImpl.java:1242)
        at org.eigenbase.sql.validate.SqlValidatorImpl.inferUnknownTypes(SqlValidatorImpl.java:1194)
        at org.eigenbase.sql.validate.SqlValidatorImpl.validateSelectList(SqlValidatorImpl.java:2294)
        at org.eigenbase.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:2069)
        at org.eigenbase.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:65)
        at org.eigenbase.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:88)
        at org.eigenbase.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:562)
        at org.eigenbase.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:551)
        at org.eigenbase.sql.SqlSelect.validate(SqlSelect.java:154)
        at org.eigenbase.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:532)
        at org.eigenbase.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:357)
        at net.sf.farrago.query.FarragoSqlValidator.validate(FarragoSqlValidator.java:66)
        at net.sf.farrago.db.FarragoDatabase.prepareStmtImpl(FarragoDatabase.java:779)
        at net.sf.farrago.db.FarragoDatabase.prepareStmt(FarragoDatabase.java:707)
        at net.sf.farrago.db.FarragoDbSession.prepareImpl(FarragoDbSession.java:935)
        at net.sf.farrago.db.FarragoDbSession.prepare(FarragoDbSession.java:862)at net.sf.farrago.db.FarragoDbStmtContext.prepare(FarragoDbStmtContext.java:105)
        at net.sf.farrago.jdbc.engine.FarragoJdbcEngineStatement.execute(FarragoJdbcEngineStatement.java:107)
        at sqlline.SqlLine$Commands.execute(Ljava.lang.String;Z)Z(Unknown Source)
0: jdbc:luciddb:>

dynamobi-build commented 12 years ago

[author="jvs", created="Mon, 31 Jul 2006 14:02:12 -0500 (GMT-05:00)"] Workaround is to cast the null explicitly:

call sys_root.export_schema_incremental_to_csv('LOCALDB', 'UDPTEST', false, 'MYTABLE', cast(null as varchar(1)), current_timestamp, 'LMTS', '/home/jvs/tmp/inc', true, true);

See also FRG-128. It appears that somehow using current_timestamp rather than timestamp literal causes the UDF behavior to be used instead of the UDP behavior; that is mysterious. Strictly speaking, conforming SQL statements should always cast the null in routine arguments, but that's really a pain.