This checkin is not allowed on the release branch: it changes the ABI by introducing a function MTIMEdate_extract_dayofworkweek. What's wrong with changing the implementation of MTIMEdate_extract_dayofweek to do what MTIMEdate_extract_dayofworkweek does now (and dropping MTIMEdate_extract_dayofworkweek)? On 2013-08-25 16:23, Niels Nes wrote:

Changeset: 0620e9df2003 for MonetDB URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=0620e9df2003 Added Files: sql/test/BugTracker-2013/Tests/iso8601_weekofyear.Bug-3232.sql sql/test/BugTracker-2013/Tests/iso8601_weekofyear.Bug-3232.stable.err sql/test/BugTracker-2013/Tests/iso8601_weekofyear.Bug-3232.stable.out Modified Files: clients/mapiclient/mclient.c monetdb5/modules/atoms/mtime.c monetdb5/modules/atoms/mtime.h monetdb5/modules/atoms/mtime.mal sql/backends/monet5/sql.mx sql/common/sql_types.c sql/scripts/mtime.sql sql/server/rel_select.c sql/test/BugTracker-2012/Tests/predicate_select.Bug-3090.stable.err sql/test/BugTracker-2012/Tests/predicate_select.Bug-3090.stable.out sql/test/BugTracker-2013/Tests/All sql/test/leaks/Tests/check1.stable.out sql/test/leaks/Tests/check2.stable.out sql/test/leaks/Tests/check3.stable.out sql/test/leaks/Tests/check4.stable.out sql/test/leaks/Tests/check5.stable.out sql/test/mapi/Tests/php_monetdb.stable.out Branch: Feb2013 Log Message:

fixed bug 3232, use dayofweek, starting at Monday fixed bug 2962, convert timestamp with time zone to local time zone, when used as input to the extract functions. fixed bug 2781, use correct time zone in mclient

diffs (truncated from 761 to 300 lines):

diff --git a/clients/mapiclient/mclient.c b/clients/mapiclient/mclient.c --- a/clients/mapiclient/mclient.c +++ b/clients/mapiclient/mclient.c @@ -2559,6 +2559,7 @@ set_timezone(Mapi mid) tmp = gmtime(&t); gt = mktime(tmp); tmp = localtime(&t); + tmp->tm_isdst=0; /* We need the difference without dst */ lt = mktime(tmp); assert((lng) gt - (lng) lt >= (lng) INT_MIN && (lng) gt - (lng) lt <= (lng) INT_MAX); tzone = (int) (gt - lt); diff --git a/monetdb5/modules/atoms/mtime.c b/monetdb5/modules/atoms/mtime.c --- a/monetdb5/modules/atoms/mtime.c +++ b/monetdb5/modules/atoms/mtime.c @@ -427,6 +427,7 @@ parse_substr(int *ret, str s, int min, s return j; }

+/* Sunday = 1, Saturday = 7 */ static int date_dayofweek(date v) { @@ -434,6 +435,17 @@ date_dayofweek(date v) return v <= 0 ? v + 7 : v; }

+/* Monday = 1, Sunday = 7 */ +static int +date_dayofworkweek(date v) +{ + int res = date_dayofweek(v); + + if (res >= 2) + return res -1; + return 7; +} + #define SKIP_DAYS(d,w,i) d += i; w = (w + i)%7; if (w <= 0) w += 7;

static date @@ -1310,10 +1322,10 @@ date_extract_weekofyear(int *ret, date *

fromdate((int) *v, &dummy, &dummy, &year); year_jan_1 = todate(1, 1, year); - dayofweek = date_dayofweek(year_jan_1); + dayofweek = date_dayofworkweek(year_jan_1);

if (dayofweek <= 4) { - /* week of jan 1 belongs to this year */ + /* 4 or more days in first week, ie week of jan 1 belongs to this year */ *ret = (int) (1 + (*v - year_jan_1 + dayofweek - 1) / 7); } else if (*v - year_jan_1 > 7 - dayofweek) { /* week of jan 1 belongs to last year; but this is a later week */ @@ -1340,6 +1352,18 @@ date_extract_dayofweek(int *ret, date *v return MAL_SUCCEED; }

+/* Returns the current day of the week where 1=monday, .., 7=sunday */ +static str +date_extract_dayofworkweek(int *ret, date *v) +{ + if (*v == date_nil) { + *ret = int_nil; + } else { + *ret = date_dayofworkweek(*v); + } + return MAL_SUCCEED; +} + /* extracts hour from daytime (value between 0 and 23) */ static str daytime_extract_hours(int *ret, daytime *v) @@ -1959,6 +1983,12 @@ MTIMEdate_extract_dayofweek(int *ret, da }

str +MTIMEdate_extract_dayofworkweek(int *ret, date *v) +{ + return date_extract_dayofworkweek(ret, v); +} + +str MTIMEdaytime_extract_hours(int *ret, daytime *v) { return daytime_extract_hours(ret, v); diff --git a/monetdb5/modules/atoms/mtime.h b/monetdb5/modules/atoms/mtime.h --- a/monetdb5/modules/atoms/mtime.h +++ b/monetdb5/modules/atoms/mtime.h @@ -163,6 +163,7 @@ mtime_export str MTIMEdate_extract_day(i mtime_export str MTIMEdate_extract_dayofyear(int *ret, date *v); mtime_export str MTIMEdate_extract_weekofyear(int *ret, date *v); mtime_export str MTIMEdate_extract_dayofweek(int *ret, date *v); +mtime_export str MTIMEdate_extract_dayofworkweek(int *ret, date *v); mtime_export str MTIMEdaytime_extract_hours(int *ret, daytime *v); mtime_export str MTIMEdaytime_extract_minutes(int *ret, daytime *v); mtime_export str MTIMEdaytime_extract_seconds(int *ret, daytime *v); diff --git a/monetdb5/modules/atoms/mtime.mal b/monetdb5/modules/atoms/mtime.mal --- a/monetdb5/modules/atoms/mtime.mal +++ b/monetdb5/modules/atoms/mtime.mal @@ -633,6 +633,10 @@ command dayofweek(d:date) :int address MTIMEdate_extract_dayofweek comment "Returns the current day of the week where 1=sunday, .., 7=saturday"; +command dayofworkweek(d:date) :int +address MTIMEdate_extract_dayofworkweek +comment "Returns the current day of the week + where 1=monday, .., 7=sunday";

command add(v:timestamp, msecs:lng) :timestamp address MTIMEtimestamp_add diff --git a/sql/backends/monet5/sql.mx b/sql/backends/monet5/sql.mx --- a/sql/backends/monet5/sql.mx +++ b/sql/backends/monet5/sql.mx @@ -714,6 +714,14 @@ comment "cast @1 to @1 and check for ove @:mal_casttime(timestamp)@ @:mal_casttime(daytime)@

+pattern current_time() :daytime +address SQLcurrent_daytime +comment "Get the clients current daytime"; + +pattern current_timestamp() :timestamp +address SQLcurrent_timestamp +comment "Get the clients current timestamp"; + command calc.date( v:void ) :date address nil_2_date comment "cast to date"; @@ -6381,6 +6389,8 @@ sql5_export str second_interval_daytime( sql5_export str second_interval_2_daytime( daytime *res, lng *s, int *d); sql5_export str timestamp_2_daytime( daytime *res, timestamp *v, int *d); sql5_export str date_2_timestamp( timestamp *res, date *v, int *d); +str SQLcurrent_daytime(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr pci); +str SQLcurrent_timestamp(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr pci); @c str second_interval_daytime( lng *res, daytime *s, int *d, int *sk ) @@ -6415,6 +6425,36 @@ second_interval_daytime( lng *res, dayti return MAL_SUCCEED; }

+str SQLcurrent_daytime(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr pci) +{ + mvc *m = NULL; + str msg = getSQLContext(cntxt, mb, &m, NULL); + daytime t, *res = (daytime *) getArgReference(stk, pci, 0); + + if (msg) + return msg; + + if ((msg = MTIMEcurrent_time(&t)) == MAL_SUCCEED) + *res = t+m->timezone; + return msg; +} + +str SQLcurrent_timestamp(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr pci) +{ + mvc *m = NULL; + str msg = getSQLContext(cntxt, mb, &m, NULL); + timestamp t, *res = (timestamp *) getArgReference(stk, pci, 0); + + if (msg) + return msg; + + if ((msg = MTIMEcurrent_timestamp(&t)) == MAL_SUCCEED) { + lng offset = m->timezone; + return MTIMEtimestamp_add(res, &t, &offset); + } + return msg; +} + /* str dump_cache(int *r); */ str dump_cache(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr pci) diff --git a/sql/common/sql_types.c b/sql/common/sql_types.c --- a/sql/common/sql_types.c +++ b/sql/common/sql_types.c @@ -1457,8 +1457,8 @@ sqltypeinit( sql_allocator *sa) sql_create_func(sa, "curtime", "mtime", "current_time", NULL, NULL, TMETZ, SCALE_NONE); sql_create_func(sa, "current_time", "mtime", "current_time", NULL, NULL, TMETZ, SCALE_NONE); sql_create_func(sa, "current_timestamp", "mtime", "current_timestamp", NULL, NULL, TMESTAMPTZ, SCALE_NONE); - sql_create_func(sa, "localtime", "mtime", "current_time", NULL, NULL, TME, SCALE_NONE); - sql_create_func(sa, "localtimestamp", "mtime", "current_timestamp", NULL, NULL, TMESTAMP, SCALE_NONE); + sql_create_func(sa, "localtime", "sql", "current_time", NULL, NULL, TME, SCALE_NONE); + sql_create_func(sa, "localtimestamp", "sql", "current_timestamp", NULL, NULL, TMESTAMP, SCALE_NONE);

sql_create_func(sa, "sql_sub", "mtime", "date_sub_msec_interval", DTE, SECINT, DTE, SCALE_FIX); sql_create_func(sa, "sql_sub", "mtime", "date_sub_month_interval", DTE, MONINT, DTE, SCALE_FIX); @@ -1470,6 +1470,7 @@ sqltypeinit( sql_allocator *sa)

sql_create_func(sa, "sql_sub", "mtime", "diff", DTE, DTE, INT, SCALE_FIX); sql_create_func(sa, "sql_sub", "mtime", "diff", TMESTAMP, TMESTAMP, LNG, SCALE_FIX); + sql_create_func(sa, "sql_sub", "mtime", "diff", TMESTAMPTZ, TMESTAMPTZ, LNG, SCALE_NONE);

sql_create_func(sa, "sql_add", "mtime", "date_add_msec_interval", DTE, SECINT, DTE, SCALE_NONE); sql_create_func(sa, "sql_add", "mtime", "addmonths", DTE, MONINT, DTE, SCALE_NONE); @@ -1498,6 +1499,15 @@ sqltypeinit( sql_allocator *sa) /* fix result type */ f->res.scale = 3;

+ sql_create_func(sa, "year", "mtime", "year", TMESTAMPTZ, NULL, INT, SCALE_FIX); + sql_create_func(sa, "month", "mtime", "month", TMESTAMPTZ, NULL, INT, SCALE_FIX); + sql_create_func(sa, "day", "mtime", "day", TMESTAMPTZ, NULL, INT, SCALE_FIX); + sql_create_func(sa, "hour", "mtime", "hours", TMESTAMPTZ, NULL, INT, SCALE_FIX); + sql_create_func(sa, "minute", "mtime", "minutes", TMESTAMPTZ, NULL, INT, SCALE_FIX); + f = sql_create_func(sa, "second", "mtime", "sql_seconds", TMESTAMPTZ, NULL, DEC, SCALE_NONE); + /* fix result type */ + f->res.scale = 3; + sql_create_func(sa, "year", "mtime", "year", MONINT, NULL, INT, SCALE_NONE); sql_create_func(sa, "month", "mtime", "month", MONINT, NULL, INT, SCALE_NONE); sql_create_func(sa, "day", "mtime", "day", SECINT, NULL, LNG, SCALE_NONE); @@ -1507,7 +1517,7 @@ sqltypeinit( sql_allocator *sa)

sql_create_func(sa, "dayofyear", "mtime", "dayofyear", DTE, NULL, INT, SCALE_FIX); sql_create_func(sa, "weekofyear", "mtime", "weekofyear", DTE, NULL, INT, SCALE_FIX); - sql_create_func(sa, "dayofweek", "mtime", "dayofweek", DTE, NULL, INT, SCALE_FIX); + sql_create_func(sa, "dayofweek", "mtime", "dayofworkweek", DTE, NULL, INT, SCALE_FIX); sql_create_func(sa, "dayofmonth", "mtime", "day", DTE, NULL, INT, SCALE_FIX); sql_create_func(sa, "week", "mtime", "weekofyear", DTE, NULL, INT, SCALE_FIX);

diff --git a/sql/scripts/mtime.sql b/sql/scripts/mtime.sql --- a/sql/scripts/mtime.sql +++ b/sql/scripts/mtime.sql @@ -29,16 +29,16 @@ create function "current_timestamp"( ) r external name mtime."current_timestamp";

create function "localtime"( ) returns TIME - external name mtime."current_time"; + external name sql."current_time"; + create function "localtimestamp"( ) returns TIMESTAMP - external name mtime."current_timestamp"; + external name sql."current_timestamp";

create type second_interval as interval day to sec;

create function local_timezone( ) returns second_interval external name mtime.local_timezone;

- create function sql_sub( date, second_interval ) returns date external name mtime.date_sub_sec_interval; create function sql_sub( date, month_interval ) returns date diff --git a/sql/server/rel_select.c b/sql/server/rel_select.c --- a/sql/server/rel_select.c +++ b/sql/server/rel_select.c @@ -3290,6 +3290,17 @@ rel_unop(mvc *sql, sql_rel **rel, symbol f = sql_bind_func(sql->sa, s, fname, t, NULL, F_AGGR); if (f && IS_AGGR(f->func)) return _rel_aggr(sql, rel, 0, s, fname, l->next, fs); + + if (f && type_has_tz(t) && f->func->fix_scale == SCALE_FIX) { + /* set timezone (using msec) */ + sql_subtype *intsec = sql_bind_subtype(sql->sa, "sec_interval", 10 /*hour to second */, 0); + sql_exp *tz = exp_atom_lng(sql->sa, sql->timezone); + + tz = exp_convert(sql->sa, tz, exp_subtype(tz), intsec); + e = rel_binop_(sql, e, tz, NULL, "sql_add", ek.card); + if (!e) + return NULL; + } return rel_unop_(sql, e, s, fname, ek.card); }

diff --git a/sql/test/BugTracker-2012/Tests/predicate_select.Bug-3090.stable.err b/sql/test/BugTracker-2012/Tests/predicate_select.Bug-3090.stable.err --- a/sql/test/BugTracker-2012/Tests/predicate_select.Bug-3090.stable.err +++ b/sql/test/BugTracker-2012/Tests/predicate_select.Bug-3090.stable.err @@ -30,9 +30,9 @@ stderr of test 'predicate_select.Bug-309 # 18:53:13 > "mclient" "-lsql" "-ftest" "-Eutf-8" "-i" "-e" "--host=niels" "--port=32323" # 18:53:13 >

-MAPI = (monetdb) /var/tmp/mtest-16462/.s.monetdb.36324 +MAPI = (monetdb) /var/tmp/mtest-4932/.s.monetdb.34449 QUERY = select * from tables where name; -ERROR = !conversion of string 'r' to type bit failed. +ERROR = !conversion of string 'storagemodelinput' to type bit failed.

# 18:53:13 > # 18:53:13 > "Done." diff --git a/sql/test/BugTracker-2012/Tests/predicate_select.Bug-3090.stable.out b/sql/test/BugTracker-2012/Tests/predicate_select.Bug-3090.stable.out --- a/sql/test/BugTracker-2012/Tests/predicate_select.Bug-3090.stable.out +++ b/sql/test/BugTracker-2012/Tests/predicate_select.Bug-3090.stable.out @@ -47,19 +47,19 @@ Ready. [ 2130, "idxs", 2102, NULL, 0, true, 2, false, 0 ] [ 2135, "triggers", 2102, NULL, 0, true, 2, false, 0 ] [ 2146, "objects", 2102, NULL, 0, true, 2, false, 0 ] -[ 5060, "tables", 2000, "SELECT * FROM (SELECT p.*, 0 AS \"temporary\" FROM \"sys\".\"_tables\" AS p UNION ALL SELECT t.*, 1 AS \"temporary\" FROM \"tmp\".\"_tables\" AS t) AS tables where tables.type <> 2;", 1, true, 0, false, 0 ] -[ 5070, "columns", 2000, "SELECT * FROM (SELECT p.* FROM \"sys\".\"_columns\" AS p UNION ALL SELECT t.* FROM \"tmp\".\"_columns\" AS t) AS columns;", 1, true, 0, false, 0 ] -[ 5090, "db_user_info", 2000, NULL, 0, true, 0, false, 0 ] -[ 5098, "users", 2000, "SELECT u.\"name\" AS \"name\", ui.\"fullname\", ui.\"default_schema\" FROM db_users() AS u LEFT JOIN \"sys\".\"db_user_info\" AS ui ON u.\"name\" = ui.\"name\" ;", 1, true, 0, false, 0 ] -[ 5102, "user_role", 2000, NULL, 0, true, 0, false, 0 ] -[ 5105, "auths", 2000, NULL, 0, true, 0, false, 0 ] -[ 5109, "privileges", 2000, NULL, 0, true, 0, false, 0 ] -[ 5262, "queryhistory", 2000, NULL, 0, true, 0, false, 0 ] -[ 5276, "callhistory", 2000, NULL, 0, true, 0, false, 0 ] -[ 5293, "querylog", 2000, "create view querylog as\nselect qd.*, ql.ctime, ql.arguments, ql.exec, ql.result, ql.foot, ql.memory, ql.tuples, ql.inblock, ql.oublock from queryhistory qd, callhistory ql\nwhere qd.id = ql.id;", 1, true, 0, false, 0 ] -[ 6059, "storagemodelinput", 2000, NULL, 0, true, 0, false, 0 ] -[ 6100, "tablestoragemodel", 2000, "-- A summary of the table storage requirement is is available as a table view.\n-- The auxillary column denotes the maximum space if all non-sorted columns\n-- would be augmented with a hash (rare situation)\ncreate view sys.tablestoragemodel\nas select \"schema\",\"table\",max(count) as \"count\",\n\tsum(columnsize) as columnsize,\n\tsum(heapsize) as heapsize,\n\tsum(indices) as indices,\n\tsum(case when sorted = false then 8 * count else 0 end) as auxillary\nfrom sys.storagemodel() group by \"schema\",\"table\";", 1, true, 0, false, 0 ] -[ 6122, "systemfunctions", 2000, NULL, 0, true, 0, false, 0 ] _______________________________________________ checkin-list mailing list checkin-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/checkin-list

-- Sjoerd Mullender