Changeset: 21a1e0080451 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/21a1e0080451
Modified Files:
monetdb5/modules/atoms/mtime.c
monetdb5/modules/atoms/mtime.h
sql/common/sql_types.c
sql/server/sql_parser.y
sql/test/odbc-escape-sequences/Tests/time-date-interval-functions.test
Branch: escape-sequences
Log Message:
timestampadd corner cases
diffs (188 lines):
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
@@ -523,6 +523,26 @@ func2(MTIMEtimestamp_add_msec_interval,
GET_NEXT_VAR, GET_NEXT_VAR,
APPEND_VAR, FINISH_INT_SINGLE, CLEAR_NOTHING)
+func2(MTIMEodbc_timestamp_add_msec_interval_time,
"odbc_timestamp_add_msec_interval_time",
+ daytime, lng, timestamp, odbc_timestamp_add_msec_interval_time,
func2_except,
+ DEC_VAR_R, DEC_VAR_R, DEC_VAR_R, DEC_INT,
+ INIT_VARIN, INIT_VARIN, INIT_VAROUT,
+ GET_NEXT_VAR, GET_NEXT_VAR,
+ APPEND_VAR, FINISH_INT_SINGLE, CLEAR_NOTHING)
+func2(MTIMEodbc_timestamp_add_month_interval_time,
"odbc_timestamp_add_month_interval_time",
+ daytime, int, timestamp, odbc_timestamp_add_month_interval_time,
func2_except,
+ DEC_VAR_R, DEC_VAR_R, DEC_VAR_R, DEC_INT,
+ INIT_VARIN, INIT_VARIN, INIT_VAROUT,
+ GET_NEXT_VAR, GET_NEXT_VAR,
+ APPEND_VAR, FINISH_INT_SINGLE, CLEAR_NOTHING)
+func2(MTIMEodbc_timestamp_add_msec_interval_date,
"odbc_timestamp_add_msec_interval_date",
+ date, lng, timestamp, odbc_timestamp_add_msec_interval_date,
func2_except,
+ DEC_VAR_R, DEC_VAR_R, DEC_VAR_R, DEC_INT,
+ INIT_VARIN, INIT_VARIN, INIT_VAROUT,
+ GET_NEXT_VAR, GET_NEXT_VAR,
+ APPEND_VAR, FINISH_INT_SINGLE, CLEAR_NOTHING)
+
+
func2(MTIMEtimestamp_sub_month_interval, "timestamp_sub_month_interval",
timestamp, int, timestamp, timestamp_sub_month_interval, func2_except,
DEC_VAR_R, DEC_VAR_R, DEC_VAR_R, DEC_INT,
@@ -1101,6 +1121,7 @@ static mel_func mtime_init_funcs[] = {
pattern("batmtime", "timestamp_sub_month_interval",
MTIMEtimestamp_sub_month_interval_bulk, false, "", args(1,5,
batarg("",timestamp),batarg("t",timestamp),batarg("s",int),batarg("s1",oid),batarg("s2",oid))),
pattern("batmtime", "timestamp_sub_month_interval",
MTIMEtimestamp_sub_month_interval_bulk_p1, false, "", args(1,4,
batarg("",timestamp),arg("t",timestamp),batarg("s",int),batarg("s",oid))),
pattern("batmtime", "timestamp_sub_month_interval",
MTIMEtimestamp_sub_month_interval_bulk_p2, false, "", args(1,4,
batarg("",timestamp),batarg("t",timestamp),arg("s",int),batarg("s",oid))),
+ // --
command("mtime", "timestamp_add_month_interval",
MTIMEtimestamp_add_month_interval, false, "Add months to a timestamp",
args(1,3, arg("",timestamp),arg("t",timestamp),arg("s",int))),
pattern("batmtime", "timestamp_add_month_interval",
MTIMEtimestamp_add_month_interval_bulk, false, "", args(1,3,
batarg("",timestamp),batarg("t",timestamp),batarg("s",int))),
pattern("batmtime", "timestamp_add_month_interval",
MTIMEtimestamp_add_month_interval_bulk_p1, false, "", args(1,3,
batarg("",timestamp),arg("t",timestamp),batarg("s",int))),
@@ -1108,6 +1129,31 @@ static mel_func mtime_init_funcs[] = {
pattern("batmtime", "timestamp_add_month_interval",
MTIMEtimestamp_add_month_interval_bulk, false, "", args(1,5,
batarg("",timestamp),batarg("t",timestamp),batarg("s",int),batarg("s1",oid),batarg("s2",oid))),
pattern("batmtime", "timestamp_add_month_interval",
MTIMEtimestamp_add_month_interval_bulk_p1, false, "", args(1,4,
batarg("",timestamp),arg("t",timestamp),batarg("s",int),batarg("s",oid))),
pattern("batmtime", "timestamp_add_month_interval",
MTIMEtimestamp_add_month_interval_bulk_p2, false, "", args(1,4,
batarg("",timestamp),batarg("t",timestamp),arg("s",int),batarg("s",oid))),
+// odbc timestampadd corner cases
+ command("mtime", "odbc_timestamp_add_msec_time",
MTIMEodbc_timestamp_add_msec_interval_time, false, "", args(1,3,
arg("",timestamp),arg("t", daytime),arg("ms",lng))),
+ pattern("batmtime", "odbc_timestamp_add_msec_time",
MTIMEodbc_timestamp_add_msec_interval_time_bulk, false, "", args(1,3,
batarg("",timestamp),batarg("t",daytime),batarg("ms",lng))),
+ pattern("batmtime", "odbc_timestamp_add_msec_time",
MTIMEodbc_timestamp_add_msec_interval_time_bulk_p1, false, "", args(1,3,
batarg("",timestamp),arg("t",daytime),batarg("ms",lng))),
+ pattern("batmtime", "odbc_timestamp_add_msec_time",
MTIMEodbc_timestamp_add_msec_interval_time_bulk_p2, false, "", args(1,3,
batarg("",timestamp),batarg("t",daytime),arg("ms",lng))),
+ pattern("batmtime", "odbc_timestamp_add_msec_time",
MTIMEodbc_timestamp_add_msec_interval_time_bulk, false, "", args(1,5,
batarg("",timestamp),batarg("t",daytime),
batarg("ms",lng),batarg("s1",oid),batarg("s2",oid))),
+ pattern("batmtime", "odbc_timestamp_add_msec_time",
MTIMEodbc_timestamp_add_msec_interval_time_bulk_p1, false, "", args(1,4,
batarg("",timestamp),arg("t", daytime),batarg("ms",lng),batarg("s",oid))),
+ pattern("batmtime", "odbc_timestamp_add_msec_time",
MTIMEodbc_timestamp_add_msec_interval_time_bulk_p2, false, "", args(1,4,
batarg("",timestamp),batarg("t", daytime),arg("ms",lng),batarg("s",oid))),
+ // --
+ command("mtime", "odbc_timestamp_add_month_time",
MTIMEodbc_timestamp_add_month_interval_time, false, "Add months to a time",
args(1,3, arg("",timestamp),arg("t",daytime),arg("s",int))),
+ pattern("batmtime", "odbc_timestamp_add_month_time",
MTIMEodbc_timestamp_add_month_interval_time_bulk, false, "", args(1,3,
batarg("",timestamp),batarg("t",daytime),batarg("s",int))),
+ pattern("batmtime", "odbc_timestamp_add_month_time",
MTIMEodbc_timestamp_add_month_interval_time_bulk_p1, false, "", args(1,3,
batarg("",timestamp),arg("t",daytime),batarg("s",int))),
+ pattern("batmtime", "odbc_timestamp_add_month_time",
MTIMEodbc_timestamp_add_month_interval_time_bulk_p2, false, "", args(1,3,
batarg("",timestamp),batarg("t",daytime),arg("s",int))),
+ pattern("batmtime", "odbc_timestamp_add_month_time",
MTIMEodbc_timestamp_add_month_interval_time_bulk, false, "", args(1,5,
batarg("",timestamp),batarg("t",daytime),batarg("s",int),batarg("s1",oid),batarg("s2",oid))),
+ pattern("batmtime", "odbc_timestamp_add_month_time",
MTIMEodbc_timestamp_add_month_interval_time_bulk_p1, false, "", args(1,4,
batarg("",timestamp),arg("t",daytime),batarg("s",int),batarg("s",oid))),
+ pattern("batmtime", "odbc_timestamp_add_month_time",
MTIMEodbc_timestamp_add_month_interval_time_bulk_p2, false, "", args(1,4,
batarg("",timestamp),batarg("t",daytime),arg("s",int),batarg("s",oid))),
+ // --
+ command("mtime", "odbc_timestamp_add_msec_date",
MTIMEodbc_timestamp_add_msec_interval_date, false, "", args(1,3,
arg("",timestamp),arg("d", date),arg("ms",lng))),
+ pattern("batmtime", "odbc_timestamp_add_msec_date",
MTIMEodbc_timestamp_add_msec_interval_date_bulk, false, "", args(1,3,
batarg("",timestamp),batarg("d",date),batarg("ms",lng))),
+ pattern("batmtime", "odbc_timestamp_add_msec_date",
MTIMEodbc_timestamp_add_msec_interval_date_bulk_p1, false, "", args(1,3,
batarg("",timestamp),arg("d",date),batarg("ms",lng))),
+ pattern("batmtime", "odbc_timestamp_add_msec_date",
MTIMEodbc_timestamp_add_msec_interval_date_bulk_p2, false, "", args(1,3,
batarg("",timestamp),batarg("d",date),arg("ms",lng))),
+ pattern("batmtime", "odbc_timestamp_add_msec_date",
MTIMEodbc_timestamp_add_msec_interval_date_bulk, false, "", args(1,5,
batarg("",timestamp),batarg("d",date),
batarg("ms",lng),batarg("s1",oid),batarg("s2",oid))),
+ pattern("batmtime", "odbc_timestamp_add_msec_date",
MTIMEodbc_timestamp_add_msec_interval_date_bulk_p1, false, "", args(1,4,
batarg("",timestamp),arg("d", date),batarg("ms",lng),batarg("s",oid))),
+ pattern("batmtime", "odbc_timestamp_add_msec_date",
MTIMEodbc_timestamp_add_msec_interval_date_bulk_p2, false, "", args(1,4,
batarg("",timestamp),batarg("d", date),arg("ms",lng),batarg("s",oid))),
+// end odbc timestampadd corner cases
command("mtime", "time_sub_msec_interval", MTIMEtime_sub_msec_interval,
false, "Subtract seconds from a time", args(1,3,
arg("",daytime),arg("t",daytime),arg("ms",lng))),
pattern("batmtime", "time_sub_msec_interval",
MTIMEtime_sub_msec_interval_bulk, false, "", args(1,3,
batarg("",daytime),batarg("t",daytime),batarg("ms",lng))),
pattern("batmtime", "time_sub_msec_interval",
MTIMEtime_sub_msec_interval_bulk_p1, false, "", args(1,3,
batarg("",daytime),arg("t",daytime),batarg("ms",lng))),
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
@@ -111,6 +111,39 @@ timestamp_add_msec_interval(timestamp *r
return MAL_SUCCEED;
}
+
+static inline str
+odbc_timestamp_add_msec_interval_time(timestamp *ret, daytime t, lng ms)
+{
+ date today = timestamp_date(timestamp_current());
+ timestamp ts = timestamp_create(today, t);
+ if (is_timestamp_nil((*ret = timestamp_add_usec(ts, ms * 1000))))
+ throw(MAL, "mtime.odbc_timestamp_add_msec_interval_time",
SQLSTATE(22003) "overflow in calculation");
+ return MAL_SUCCEED;
+}
+
+
+static inline str
+odbc_timestamp_add_month_interval_time(timestamp *ret, daytime t, int m)
+{
+ date today = timestamp_date(timestamp_current());
+ timestamp ts = timestamp_create(today, t);
+ if (is_timestamp_nil((*ret = timestamp_add_month(ts, m))))
+ throw(MAL, "mtime.odbc_timestamp_add_month_interval_time",
SQLSTATE(22003) "overflow in calculation");
+ return MAL_SUCCEED;
+}
+
+
+static inline str
+odbc_timestamp_add_msec_interval_date(timestamp *ret, date d, lng ms)
+{
+ timestamp ts = timestamp_fromdate(d);
+ if (is_timestamp_nil((*ret = timestamp_add_usec(ts, ms * 1000))))
+ throw(MAL, "mtime.odbc_timestamp_add_msec_interval_date",
SQLSTATE(22003) "overflow in calculation");
+ return MAL_SUCCEED;
+}
+
+
static inline str
date_submonths(date *ret, date d, int m)
{
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
@@ -1348,6 +1348,20 @@ sqltypeinit( sql_allocator *sa)
sql_create_func(sa, "sql_add", "mtime", "time_add_msec_interval",
FALSE, FALSE, SCALE_NONE, 0, TME, 2, TME, SECINT);
sql_create_func(sa, "sql_add", "mtime", "time_add_msec_interval",
FALSE, FALSE, SCALE_NONE, 0, TMETZ, 2, TMETZ, SECINT);
+ // odbc timestampadd
+ sql_create_func(sa, "timestampadd", "mtime",
"timestamp_add_msec_interval", FALSE, FALSE, SCALE_NONE, 0, TMESTAMP, 2,
TMESTAMP, SECINT);
+ sql_create_func(sa, "timestampadd", "mtime",
"timestamp_add_msec_interval", FALSE, FALSE, SCALE_NONE, 0, TMESTAMP, 2,
TMESTAMP, DAYINT);
+ sql_create_func(sa, "timestampadd", "mtime",
"timestamp_add_month_interval", FALSE, FALSE, SCALE_NONE, 0, TMESTAMP, 2,
TMESTAMP, MONINT);
+ sql_create_func(sa, "timestampadd", "mtime",
"timestamp_add_msec_interval", FALSE, FALSE, SCALE_NONE, 0, TMESTAMPTZ, 2,
TMESTAMPTZ, SECINT);
+ sql_create_func(sa, "timestampadd", "mtime",
"timestamp_add_msec_interval", FALSE, FALSE, SCALE_NONE, 0, TMESTAMPTZ, 2,
TMESTAMPTZ, DAYINT);
+ sql_create_func(sa, "timestampadd", "mtime",
"timestamp_add_month_interval", FALSE, FALSE, SCALE_NONE, 0, TMESTAMPTZ, 2,
TMESTAMPTZ, MONINT);
+ // corner cases as described in microsoft odbc timestampadd spec
+ sql_create_func(sa, "timestampadd", "mtime",
"odbc_timestamp_add_msec_time", FALSE, FALSE, SCALE_NONE, 0, TMESTAMP, 2, TME,
DAYINT);
+ sql_create_func(sa, "timestampadd", "mtime",
"odbc_timestamp_add_month_time", FALSE, FALSE, SCALE_NONE, 0, TMESTAMP, 2, TME,
MONINT);
+ sql_create_func(sa, "timestampadd", "mtime",
"odbc_timestamp_add_msec_date", FALSE, FALSE, SCALE_NONE, 0, TMESTAMP, 2, DTE,
SECINT);
+ // odbc timestampdiff
+ // end odbc
+
sql_create_func(sa, "local_timezone", "mtime", "local_timezone", FALSE,
FALSE, SCALE_FIX, 0, SECINT, 0);
sql_create_func(sa, "century", "mtime", "century", FALSE, FALSE,
SCALE_FIX, 0, INT, 1, DTE);
diff --git a/sql/server/sql_parser.y b/sql/server/sql_parser.y
--- a/sql/server/sql_parser.y
+++ b/sql/server/sql_parser.y
@@ -6472,8 +6472,7 @@ odbc_datetime_func:
}
| TIMESTAMPADD '(' odbc_tsi_qualifier ',' intval ',' search_condition ')'
{ dlist *l = L();
- // TODO sql_add or custom func ?
- append_list( l, append_string(L(), sa_strdup(SA, "sql_add")));
+ append_list( l, append_string(L(), sa_strdup(SA,
"timestampadd")));
append_int(l, FALSE); /* ignore distinct */
sql_subtype t;
lng i = 0;
@@ -6482,8 +6481,8 @@ odbc_datetime_func:
$$ = NULL;
YYABORT;
}
+ append_symbol(l, $7);
append_symbol(l, _newAtomNode(atom_int(SA, &t, i)));
- append_symbol(l, $7);
$$ = _symbol_create_list( SQL_BINOP, l );
}
| TIMESTAMPDIFF '(' odbc_tsi_qualifier ',' intval ',' search_condition ')'
diff --git
a/sql/test/odbc-escape-sequences/Tests/time-date-interval-functions.test
b/sql/test/odbc-escape-sequences/Tests/time-date-interval-functions.test
--- a/sql/test/odbc-escape-sequences/Tests/time-date-interval-functions.test
+++ b/sql/test/odbc-escape-sequences/Tests/time-date-interval-functions.test
@@ -107,13 +107,18 @@ select { fn timestampadd(SQL_TSI_HOUR, 1
----
2022-11-14 13:12:12
-# corner case
-query T rowsort
-select { fn timestampadd(SQL_TSI_DAY, 1, time'12:12:12') }
-----
-2022-11-14 13:12:12
-
query T rowsort
select { fn timestampdiff(SQL_TSI_HOUR, 1, timestamp'2022-11-14 12:12:12') }
----
2022-11-14 11:12:12
+
+# corner cases
+query T rowsort
+select sql_sub({fn timestampadd(SQL_TSI_HOUR, 1, time'12:12:12')},
timestampadd(time'12:12:12', interval '1' hour))
+----
+0:00:00
+
+query T rowsort
+select sql_sub({fn timestampadd(SQL_TSI_HOUR, 1, date'2022-12-12')},
timestampadd(date'2022-12-12', interval '1' hour))
+----
+0:00:00
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
