Percy

On Mon, Nov 19, 2012 at 12:39 PM, Stefan Manegold <Stefan.Manegold@cwi.nl> wrote:

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

Percy Wegmann

+1 512 637 8500 ext 148