TIME(1) default CURRENT_TIME reported wrong values

[ying-wen ku]

expected default CURRENT_TIME ti01 returned -34.07.06.
not as expected 19:55:28.6

SQL>create table a05table (
    int1 integer no default not null
  , vch2 CHAR VARYING(8)
  , dt00 DATE
  , tidf TIME default CURRENT_TIME
  , tsdf TIMESTAMP(0) default CURRENT_TIMESTAMP
  , dt01 DATE default CURRENT_DATE
  , ti01 TIME(1) default CURRENT_TIME
  , ts01 TIMESTAMP(1) default CURRENT_TIMESTAMP
  , primary key (int1 asc) not droppable
)
store by primary key
;

--- SQL operation complete.

SQL>insert into a05table (int1, vch2) values (1,'row_1');

--- 1 row(s) inserted.

SQL>insert into a05table (int1, tsdf ) values
  (13, timestamp '1997-01-31:13:13:13');

--- 1 row(s) inserted.

SQL>select * from a05table order by int1;

INT1 VCH2 DT00 TIDF TSDF DT01 TI01 TS01
----------- -------- ---------- -------- ------------------- ---------- ---------- ---------------------
          1 row_1 NULL 19:55:27 2014-06-02 19:55:27 2014-06-02 -34:07:06. 2014-06-02 19:55:27.6
         13 NULL NULL 19:55:28 1997-01-31 13:13:13 2014-06-02 19:55:28.6 2014-06-02 19:55:28.6

--- 2 row(s) selected.

SQL>select int1, dt01, ti01, ts01 from a05table order by int1;

INT1 DT01 TI01 TS01
----------- ---------- ---------- ---------------------
          1 2014-06-02 -34:07:06. 2014-06-02 19:55:27.6
         13 2014-06-02 19:55:28.6 2014-06-02 19:55:28.6

--- 2 row(s) selected.

SQL>insert into a05table (int1, dt01) values
  (20, date '2005-03-31');

--- 1 row(s) inserted.

SQL>select int1, dt01, ti01, ts01 from a05table order by int1;

INT1 DT01 TI01 TS01
----------- ---------- ---------- ---------------------
          1 2014-06-02 -34:07:06. 2014-06-02 19:55:27.6
         13 2014-06-02 19:55:28.6 2014-06-02 19:55:28.6
         20 2005-03-31 -34:07:06. 2014-06-02 19:55:28.6

--- 3 row(s) selected.

SQL>exit;

-- test script
log a09log clear;
drop schema debug_addt105 cascade;
create schema debug_addt105;
set schema debug_addt105;
drop table a05table;

-- table start with 2 fixed length field
create table a05table (
    int1 integer no default not null
  , vch2 CHAR VARYING(8)
  , dt00 DATE
  , tidf TIME default CURRENT_TIME
  , tsdf TIMESTAMP(0) default CURRENT_TIMESTAMP
  , dt01 DATE default CURRENT_DATE
  , ti01 TIME(1) default CURRENT_TIME
  , ts01 TIMESTAMP(1) default CURRENT_TIMESTAMP
  , primary key (int1 asc) not droppable
)
store by primary key
;

insert into a05table (int1, vch2) values (1,'row_1');
insert into a05table (int1, tsdf ) values
  (13, timestamp '1997-01-31:13:13:13');
select * from a05table order by int1;
select int1, dt01, ti01, ts01 from a05table order by int1;
insert into a05table (int1, dt01) values
  (20, date '2005-03-31');
select int1, dt01, ti01, ts01 from a05table order by int1;
exit;

GIT 0530 build. Also failed on 0601 build.

[Anoop Sharma (anoop-sharma)]

This pbm shows up with pcode.
If pcode is turned off, it doesn't.
Something in the way pcode is being generated when
there are 2 current functions usage in the query.
Forwarding to Jim Capps.

here is a run with a smaller table and pcode on/off:

>>create table a05table (
+> int1 integer no default not null
+> , tsdf TIMESTAMP(0) default CURRENT_TIMESTAMP not null
+> , ti01 TIME(1) default CURRENT_TIME not null
+> , primary key (int1 asc) not droppable
+>)
+>;

--- SQL operation complete.
>>

Trafodion Conversational Interface 0.8.4
(c) Copyright 2014 Hewlett-Packard Development Company, LP.
>>insert into a05table (int1) values (1);

--- 1 row(s) inserted.
>>select * from a05table;

INT1 TSDF TI01
----------- ------------------- ----------

          1 2014-08-27 08:42:58 -,:07:08.5

--- 1 row(s) selected.
>>
>>
>>delete from a05table;

--- 1 row(s) deleted.
>>cqd pcode_opt_level 'OFF';

--- SQL operation complete.
>>insert into a05table (int1) values (1);

--- 1 row(s) inserted.
>>select * from a05table;
INT1 TSDF TI01
----------- ------------------- ----------

          1 2014-08-27 08:43:16 08:43:16.0

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

[Weishiun Tsai (wei-shiun-tsai)]

Verified on the v0205 build, this problem has been fixed:

>>create schema mytest;

--- SQL operation complete.
>>set schema mytest;

--- SQL operation complete.
>>
>>create table a05table (
+>int1 integer no default not null
+>, vch2 CHAR VARYING(8)
+>, dt00 DATE
+> , tidf TIME default CURRENT_TIME
+> , tsdf TIMESTAMP(0) default CURRENT_TIMESTAMP
+> , dt01 DATE default CURRENT_DATE
+> , ti01 TIME(1) default CURRENT_TIME
+> , ts01 TIMESTAMP(1) default CURRENT_TIMESTAMP
+> , primary key (int1 asc) not droppable
+> )
+> store by primary key
+> ;

--- SQL operation complete.
>>
>>insert into a05table (int1, vch2) values (1,'row_1');

--- 1 row(s) inserted.
>>insert into a05table (int1, tsdf ) values
+> (13, timestamp '1997-01-31:13:13:13');

--- 1 row(s) inserted.
>>select * from a05table order by int1;

INT1 VCH2 DT00 TIDF TSDF DT01 TI01 TS01
----------- -------- ---------- -------- ------------------- ---------- ---------- ---------------------

          1 row_1 ? 20:57:01 2015-02-05 20:57:01 2015-02-05 20:57:01.8 2015-02-05 20:57:01.8
         13 ? ? 20:57:01 1997-01-31 13:13:13 2015-02-05 20:57:01.8 2015-02-05 20:57:01.8

--- 2 row(s) selected.
>>select int1, dt01, ti01, ts01 from a05table order by int1;

INT1 DT01 TI01 TS01
----------- ---------- ---------- ---------------------

          1 2015-02-05 20:57:01.8 2015-02-05 20:57:01.8
         13 2015-02-05 20:57:01.8 2015-02-05 20:57:01.8

--- 2 row(s) selected.
>>insert into a05table (int1, dt01) values
+> (20, date '2005-03-31');

--- 1 row(s) inserted.
>>select int1, dt01, ti01, ts01 from a05table order by int1;

INT1 DT01 TI01 TS01
----------- ---------- ---------- ---------------------

          1 2015-02-05 20:57:01.8 2015-02-05 20:57:01.8
         13 2015-02-05 20:57:01.8 2015-02-05 20:57:01.8
         20 2005-03-31 20:57:01.9 2015-02-05 20:57:01.9

--- 3 row(s) selected.
>>drop schema debug_addt105 cascade;

*** ERROR[1003] Schema TRAFODION.DEBUG_ADDT105 does not exist.

--- SQL operation failed with errors.
>>create schema debug_addt105;

--- SQL operation complete.
>>set schema debug_addt105;

--- SQL operation complete.
>>drop table a05table;

*** ERROR[1389] Object TRAFODION.DEBUG_ADDT105.A05TABLE does not exist in Trafodion.

--- SQL operation failed with errors.
>>
>>create table a05table (
+> int1 integer no default not null
+> , vch2 CHAR VARYING(8)
+> , dt00 DATE
+> , tidf TIME default CURRENT_TIME
+> , tsdf TIMESTAMP(0) default CURRENT_TIMESTAMP
+> , dt01 DATE default CURRENT_DATE
+> , ti01 TIME(1) default CURRENT_TIME
+> , ts01 TIMESTAMP(1) default CURRENT_TIMESTAMP
+> , primary key (int1 asc) not droppable
+> )
+> store by primary key
+> ;

--- SQL operation complete.
>>
>>insert into a05table (int1, vch2) values (1,'row_1');

--- 1 row(s) inserted.
>>insert into a05table (int1, tsdf ) values
+> (13, timestamp '1997-01-31:13:13:13');

--- 1 row(s) inserted.
>>select * from a05table order by int1;

INT1 VCH2 DT00 TIDF TSDF DT...