Cross-column time computation doesn't appear to work in Oct 2012 release
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
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] =
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
Thanks for the quick investigation! It's logged.
http://bugs.monetdb.org/show_bug.cgi?id=3190
Cheers,
Percy
On Mon, Nov 19, 2012 at 12:39 PM, Stefan Manegold
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] =
[1] := sql.bind(X_2=0,"sys","time_table","atime",0); | | 22 | X_8:bat[:oid,:timestamp] = [0] := sql.bind(X_2=0,"sys","time_table","btime",2); | | 128 | X_12:bat[:oid,:timestamp] = [0] := sql.bind(X_2=0,"sys","time_table","btime",1); | | 32 | X_23:bat[:oid,:timestamp] = [0] := sql.bind(X_2=0,"sys","time_table","atime",1); | | 38 | X_3:bat[:oid,:timestamp] = [1] := sql.bind(X_2=0,"sys","time_table","btime",0); | | 277 | X_20:bat[:oid,:timestamp] = [0] := sql.bind(X_2=0,"sys","time_table","atime",2); | | 207 | X_15:bat[:oid,:oid] = [0] := sql.bind_dbat(X_2=0,"sys","time_table",1); | | 14 | X_10= [1] := algebra.kdifference(X_3==nil:bat[:oid,:timestamp],X_8==" ":bat[:oid,:timestamp][0]); | | 15 | X_21= [1] := algebra.kdifference(X_18==nil:bat[:oid,:timestamp],X_20==" ":bat[:oid,:timestamp][0]); | | 21 | X_11= [1] := algebra.kunion(X_10==nil:timestamp,X_8==nil:bat[:oid,:timestamp]); | | 5 | X_16= [0] := bat.reverse(X_15=nil:bat[:oid,:oid]); | | 22 | X_22= [1] := algebra.kunion(X_21==nil:timestamp,X_20==nil:bat[:oid,:timestamp]); | | 15 | X_14= [1] := algebra.kunion(X_11==nil:timestamp,X_12==nil:bat[:oid,:timestamp]); | | 11 | X_24= [1] := algebra.kunion(X_22==nil:timestamp,X_23==nil:bat[:oid,:timestamp]); | | 7 | X_17= [1] := algebra.kdifference(X_14==nil:timestamp,X_16= [0]); | | 7 | X_25= [1] := algebra.kdifference(X_24==nil:timestamp,X_16=nil); | | 9 | X_26:bat[:oid,:lng] = [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= :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 _______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
participants (2)
-
Percy Wegmann -
Stefan Manegold