Hi Percy,

thanks for reporting. This seems to be a bug in batmtime.diff(), which appears to return an empty result on a non-empty input:

sql>trace select btime - atime from time_table; +---------------+ | sql_sub_btime | +===============+ +---------------+ 0 tuples (29.511ms) +-------+-----------------------------------------------------------------------------------------------------------------------+ | ticks | stmt | +=======+=======================================================================================================================+ | 1 | X_26:bat[:oid,:lng] := nil:bat[:oid,:lng]; | | 5 | X_2 := sql.mvc(); | | 31 | X_18:bat[:oid,:timestamp] =<tmp_1301>[1] := sql.bind(X_2=0,"sys","time_table","atime",0); | | 22 | X_8:bat[:oid,:timestamp] =<tmp_1113>[0] := sql.bind(X_2=0,"sys","time_table","btime",2); | | 128 | X_12:bat[:oid,:timestamp] =<tmp_1114>[0] := sql.bind(X_2=0,"sys","time_table","btime",1); | | 32 | X_23:bat[:oid,:timestamp] =<tmp_1114>[0] := sql.bind(X_2=0,"sys","time_table","atime",1); | | 38 | X_3:bat[:oid,:timestamp] =<tmp_1314>[1] := sql.bind(X_2=0,"sys","time_table","btime",0); | | 277 | X_20:bat[:oid,:timestamp] =<tmp_1113>[0] := sql.bind(X_2=0,"sys","time_table","atime",2); | | 207 | X_15:bat[:oid,:oid] =<tmp_1313>[0] := sql.bind_dbat(X_2=0,"sys","time_table",1); | | 14 | X_10=<tmp_1271>[1] := algebra.kdifference(X_3==nil:bat[:oid,:timestamp],X_8=="<tmp_1113>":bat[:oid,:timestamp][0]); | | 15 | X_21=<tmp_1270>[1] := algebra.kdifference(X_18==nil:bat[:oid,:timestamp],X_20=="<tmp_1113>":bat[:oid,:timestamp][0]); | | 21 | X_11=<tmp_1316>[1] := algebra.kunion(X_10==nil:timestamp,X_8==nil:bat[:oid,:timestamp]); | | 5 | X_16=<tmpr_1313>[0] := bat.reverse(X_15=nil:bat[:oid,:oid]); | | 22 | X_22=<tmp_1271>[1] := algebra.kunion(X_21==nil:timestamp,X_20==nil:bat[:oid,:timestamp]); | | 15 | X_14=<tmp_1270>[1] := algebra.kunion(X_11==nil:timestamp,X_12==nil:bat[:oid,:timestamp]); | | 11 | X_24=<tmp_1316>[1] := algebra.kunion(X_22==nil:timestamp,X_23==nil:bat[:oid,:timestamp]); | | 7 | X_17=<tmp_1271>[1] := algebra.kdifference(X_14==nil:timestamp,X_16=<tmpr_1313>[0]); | | 7 | X_25=<tmp_1270>[1] := algebra.kdifference(X_24==nil:timestamp,X_16=nil); | | 9 | X_26:bat[:oid,:lng] =<tmp_1316>[0] := batmtime.diff(X_17==nil:timestamp,X_25==nil:timestamp); | | 2301 | barrier X_44 := language.dataflow(); | | 4 | X_28 := sql.resultSet(1,1,X_26=<tmp_1316>:bat[:oid,:lng][0]); | | 5 | sql.rsColumn(X_28=5,"sys.","sql_sub_btime","bigint",64,0,X_26=nil:bat[:oid,:lng]); | | 1 | X_34 := io.stdout(); | | 13 | sql.exportResult(X_34=="104d2":streams,X_28=5); | | 2 | end s7_1; | | 2582 | X_5:void := user.s7_1(); | +-------+-----------------------------------------------------------------------------------------------------------------------+ 26 tuples (29.537ms)

Could you please file a bug report and also include my above analysis? Thanks!

Stefan

----- Original Message -----

I couldn't find any existing bug logs for the below issue and want to make sure that it's not a known issue before I log it.

Basically, performing time computation across columns doesn't work.

percy@percy-sandbox:~$ uname -a Linux percy-sandbox 3.2.0-33-generic #52-*Ubuntu* SMP Thu Oct 18 16:29:15 UTC 2012 x86_64 x86_64 x86_64 GNU/Linux

percy@percy-sandbox:~$ monetdbd -v *MonetDB Database Server v1.6 (Oct2012)*

percy@percy-sandbox:~$ mclient -u monetdb -d click password: Welcome to mclient, the MonetDB/SQL interactive terminal (Oct2012) Database: *MonetDB v11.13.3 (Oct2012)*, 'mapi:monetdb://percy-sandbox:50000/click' Type \q to quit, ? for a list of available commands auto commit mode: on

sql>*create table time_table (atime timestamp, btime timestamp, ctime date);

operation successful (33.944ms)

sql>*insert into time_table values(timestamp '1970-JAN-1', timestamp '1980-DEC-31', date '2012-JAN-1');* 1 affected row (20.649ms)

sql>select * from time_table; +----------------------------+----------------------------+------------+ | atime | btime | ctime | | +============================+============================+============+ | 1970-01-01 00:00:00.000000 | 1980-12-31 00:00:00.000000 | | 2012-01-01 | +----------------------------+----------------------------+------------+ *1 tuple* (1.402ms)

sql>select btime <> atime from time_table; +----------+ | <>_btime | +==========+ | true | +----------+ *1 tuple* (1.208ms)

sql>select btime - timestamp '1975-JAN-01' from time_table; +---------------+ | sql_sub_btime | +===============+ | 189302400000 | +---------------+ *1 tuple* (1.389ms)

sql>select btime - atime from time_table; +---------------+ | sql_sub_btime | +===============+ +---------------+ *0 tuples* (1.450ms)

Expected result: All queries return 1 tuple Actual result: The query that performs date subtraction between two columns returns 0 tuples.

Thanks,

--

Percy Wegmann

---

users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list