Changeset: 7766259b9ca7 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=7766259b9ca7
Modified Files:
monetdb5/modules/atoms/mtime.c
sql/common/sql_types.c
sql/test/BugTracker-2015/Tests/timestamp_convert.Bug-3815.stable.out
sql/test/BugTracker-2016/Tests/date_calculation_and_comparison_wrong_results.Bug-3940.stable.out
sql/test/BugTracker-2017/Tests/date-arithmetic.Bug-6415.stable.out
sql/test/pg_regress/Tests/date.stable.out
sql/test/sys-schema/Tests/systemfunctions.stable.out
sql/test/sys-schema/Tests/systemfunctions.stable.out.int128
Branch: typing
Log Message:
Added missing functions for the new day interval type. Also I finally fixed the
diff function for dates. As the day interval is the number of milliseconds on
the backend, it has to multiply to the number of milliseconds on a day
diffs (truncated from 361 to 300 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
@@ -304,7 +304,7 @@ static inline lng
date_diff_imp(const date d1, const date d2)
{
int diff = date_diff(d1, d2);
- return is_int_nil(diff) ? lng_nil : (lng) diff;
+ return is_int_nil(diff) ? lng_nil : (lng) diff * (lng) (24*60*60*1000);
}
func2(MTIMEdate_diff, MTIMEdate_diff_bulk, "diff", date, date, lng,
date_diff_imp, func2_noexcept, \
DEC_VAR_R, DEC_VAR_R, DEC_VAR_R, DEC_VAR_R, DEC_VAR_R, INIT_VAR,
INIT_VAR, INIT_VAR, GET_NEXT_VAR, GET_NEXT_VAR, APPEND_VAR)
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
@@ -1725,20 +1725,26 @@ sqltypeinit( sql_allocator *sa)
sql_create_func(sa, "sql_sub", "mtime", "diff", FALSE, FALSE,
SCALE_NONE, 0, SECINT, 2, TMESTAMPTZ, TMESTAMPTZ);
sql_create_func(sa, "sql_sub", "mtime", "diff", FALSE, FALSE,
SCALE_FIX, 0, SECINT, 2, TMESTAMP, TMESTAMP);
+ sql_create_func(sa, "sql_sub", "mtime", "date_sub_msec_interval",
FALSE, FALSE, SCALE_NONE, 0, DTE, 2, DTE, SECINT);
sql_create_func(sa, "sql_sub", "mtime", "date_sub_msec_interval",
FALSE, FALSE, SCALE_NONE, 0, DTE, 2, DTE, DAYINT);
sql_create_func(sa, "sql_sub", "mtime", "date_sub_month_interval",
FALSE, FALSE, SCALE_NONE, 0, DTE, 2, DTE, MONINT);
sql_create_func(sa, "sql_sub", "mtime", "time_sub_msec_interval",
FALSE, FALSE, SCALE_NONE, 0, TME, 2, TME, SECINT);
sql_create_func(sa, "sql_sub", "mtime", "time_sub_msec_interval",
FALSE, FALSE, SCALE_NONE, 0, TMETZ, 2, TMETZ, SECINT);
sql_create_func(sa, "sql_sub", "mtime", "timestamp_sub_msec_interval",
FALSE, FALSE, SCALE_NONE, 0, TMESTAMP, 2, TMESTAMP, SECINT);
+ sql_create_func(sa, "sql_sub", "mtime", "timestamp_sub_msec_interval",
FALSE, FALSE, SCALE_NONE, 0, TMESTAMP, 2, TMESTAMP, DAYINT);
sql_create_func(sa, "sql_sub", "mtime", "timestamp_sub_month_interval",
FALSE, FALSE, SCALE_NONE, 0, TMESTAMP, 2, TMESTAMP, MONINT);
sql_create_func(sa, "sql_sub", "mtime", "timestamp_sub_msec_interval",
FALSE, FALSE, SCALE_NONE, 0, TMESTAMPTZ, 2, TMESTAMPTZ, SECINT);
+ sql_create_func(sa, "sql_sub", "mtime", "timestamp_sub_msec_interval",
FALSE, FALSE, SCALE_NONE, 0, TMESTAMPTZ, 2, TMESTAMPTZ, DAYINT);
sql_create_func(sa, "sql_sub", "mtime", "timestamp_sub_month_interval",
FALSE, FALSE, SCALE_NONE, 0, TMESTAMPTZ, 2, TMESTAMPTZ, MONINT);
+ sql_create_func(sa, "sql_add", "mtime", "date_add_msec_interval",
FALSE, FALSE, SCALE_NONE, 0, DTE, 2, DTE, SECINT);
sql_create_func(sa, "sql_add", "mtime", "date_add_msec_interval",
FALSE, FALSE, SCALE_NONE, 0, DTE, 2, DTE, DAYINT);
sql_create_func(sa, "sql_add", "mtime", "addmonths", FALSE, FALSE,
SCALE_NONE, 0, DTE, 2, DTE, MONINT);
sql_create_func(sa, "sql_add", "mtime", "timestamp_add_msec_interval",
FALSE, FALSE, SCALE_NONE, 0, TMESTAMP, 2, TMESTAMP, SECINT);
+ sql_create_func(sa, "sql_add", "mtime", "timestamp_add_msec_interval",
FALSE, FALSE, SCALE_NONE, 0, TMESTAMP, 2, TMESTAMP, DAYINT);
sql_create_func(sa, "sql_add", "mtime", "timestamp_add_month_interval",
FALSE, FALSE, SCALE_NONE, 0, TMESTAMP, 2, TMESTAMP, MONINT);
sql_create_func(sa, "sql_add", "mtime", "timestamp_add_msec_interval",
FALSE, FALSE, SCALE_NONE, 0, TMESTAMPTZ, 2, TMESTAMPTZ, SECINT);
+ sql_create_func(sa, "sql_add", "mtime", "timestamp_add_msec_interval",
FALSE, FALSE, SCALE_NONE, 0, TMESTAMPTZ, 2, TMESTAMPTZ, DAYINT);
sql_create_func(sa, "sql_add", "mtime", "timestamp_add_month_interval",
FALSE, FALSE, SCALE_NONE, 0, TMESTAMPTZ, 2, TMESTAMPTZ, MONINT);
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);
@@ -1787,7 +1793,6 @@ sqltypeinit( sql_allocator *sa)
sql_create_func(sa, "year", "mtime", "year", FALSE, FALSE, SCALE_NONE,
0, INT, 1, MONINT);
sql_create_func(sa, "month", "mtime", "month", FALSE, FALSE,
SCALE_NONE, 0, INT, 1, MONINT);
sql_create_func(sa, "day", "mtime", "day", FALSE, FALSE, SCALE_NONE, 0,
LNG, 1, DAYINT);
- sql_create_func(sa, "day", "mtime", "day", FALSE, FALSE, SCALE_NONE, 0,
LNG, 1, SECINT);
sql_create_func(sa, "hour", "mtime", "hours", FALSE, FALSE, SCALE_NONE,
0, INT, 1, SECINT);
sql_create_func(sa, "minute", "mtime", "minutes", FALSE, FALSE,
SCALE_NONE, 0, INT, 1, SECINT);
sql_create_func(sa, "second", "mtime", "seconds", FALSE, FALSE,
SCALE_NONE, 0, INT, 1, SECINT);
diff --git
a/sql/test/BugTracker-2015/Tests/timestamp_convert.Bug-3815.stable.out
b/sql/test/BugTracker-2015/Tests/timestamp_convert.Bug-3815.stable.out
--- a/sql/test/BugTracker-2015/Tests/timestamp_convert.Bug-3815.stable.out
+++ b/sql/test/BugTracker-2015/Tests/timestamp_convert.Bug-3815.stable.out
@@ -23,6 +23,12 @@ stdout of test 'timestamp_convert.Bug-38
% sec_interval # type
% 5 # length
[ 1.000 ]
+#SELECT date '2015-03-01' - (date '2015-02-01' - date '2015-01-01');
+% .%2 # table_name
+% %2 # name
+% date # type
+% 10 # length
+[ 2015-01-29 ]
# 19:04:38 >
# 19:04:38 > "Done."
diff --git
a/sql/test/BugTracker-2016/Tests/date_calculation_and_comparison_wrong_results.Bug-3940.stable.out
b/sql/test/BugTracker-2016/Tests/date_calculation_and_comparison_wrong_results.Bug-3940.stable.out
---
a/sql/test/BugTracker-2016/Tests/date_calculation_and_comparison_wrong_results.Bug-3940.stable.out
+++
b/sql/test/BugTracker-2016/Tests/date_calculation_and_comparison_wrong_results.Bug-3940.stable.out
@@ -67,14 +67,14 @@ stdout of test 'date_calculation_and_com
[ 1 ]
#INSERT INTO foo VALUES ('2016-01-01', '2016-01-31');
[ 1 ]
-#SELECT (dat2-dat1), (dat2-dat1) < 10 FROM foo;
-% sys., sys. # table_name
+#SELECT (dat2-dat1), (dat2-dat1) < interval '10' day FROM foo;
+% sys.%1, sys.%2 # table_name
% %1, %2 # name
% day_interval, boolean # type
-% 5, 5 # length
-[ 0.001, true ]
+% 11, 5 # length
+[ 86400.000, true ]
[ 0.000, true ]
-[ 0.030, true ]
+[ 2592000.000, false ]
#DROP TABLE foo;
# 23:40:42 >
diff --git a/sql/test/BugTracker-2017/Tests/date-arithmetic.Bug-6415.stable.out
b/sql/test/BugTracker-2017/Tests/date-arithmetic.Bug-6415.stable.out
--- a/sql/test/BugTracker-2017/Tests/date-arithmetic.Bug-6415.stable.out
+++ b/sql/test/BugTracker-2017/Tests/date-arithmetic.Bug-6415.stable.out
@@ -9,8 +9,8 @@ stdout of test 'date-arithmetic.Bug-6415
% .%2, .%3 # table_name
% %2, %3 # name
% day_interval, day_interval # type
-% 5, 5 # length
-[ 0.009, 0.009 ]
+% 10, 10 # length
+[ 777600.000, 777600.000 ]
#select cast('2017-10-01' as date) + interval '9' day, date '2017-10-01' +
interval '9' day;
% .%2, .%3 # table_name
% %2, %3 # name
diff --git a/sql/test/pg_regress/Tests/date.stable.out
b/sql/test/pg_regress/Tests/date.stable.out
--- a/sql/test/pg_regress/Tests/date.stable.out
+++ b/sql/test/pg_regress/Tests/date.stable.out
@@ -674,43 +674,43 @@ stdout of test 'date` in directory 'sql/
% sys.date_tbl, sys.%1 # table_name
% f1, %1 # name
% date, day_interval # type
-% 10, 7 # length
-[ 1957-04-09, -16.318 ]
-[ 1957-06-13, -16.253 ]
-[ 1996-02-28, -2.114 ]
-[ 1996-02-29, -2.113 ]
-[ 1996-03-01, -2.112 ]
-[ 1996-03-02, -2.111 ]
-[ 1997-02-28, -1.748 ]
-[ 1997-03-01, -1.747 ]
-[ 1997-03-02, -1.746 ]
-[ 2000-04-01, -0.620 ]
-[ 2000-04-02, -0.619 ]
-[ 2000-04-03, -0.618 ]
-[ 2038-04-08, 13.266 ]
-[ 2039-04-09, 13.632 ]
-[ 2040-04-10, 13.999 ]
+% 10, 15 # length
+[ 1957-04-09, -1409875200.000 ]
+[ 1957-06-13, -1404259200.000 ]
+[ 1996-02-28, -182649600.000 ]
+[ 1996-02-29, -182563200.000 ]
+[ 1996-03-01, -182476800.000 ]
+[ 1996-03-02, -182390400.000 ]
+[ 1997-02-28, -151027200.000 ]
+[ 1997-03-01, -150940800.000 ]
+[ 1997-03-02, -150854400.000 ]
+[ 2000-04-01, -53568000.000 ]
+[ 2000-04-02, -53481600.000 ]
+[ 2000-04-03, -53395200.000 ]
+[ 2038-04-08, 1146182400.000 ]
+[ 2039-04-09, 1177804800.000 ]
+[ 2040-04-10, 1209513600.000 ]
[ NULL, NULL ]
#SELECT f1, sql_sub(cast('2001-12-12' as date), f1) FROM DATE_TBL;
% sys.date_tbl, .%1 # table_name
% f1, %1 # name
% date, day_interval # type
-% 10, 7 # length
-[ 1957-04-09, 16.318 ]
-[ 1957-06-13, 16.253 ]
-[ 1996-02-28, 2.114 ]
-[ 1996-02-29, 2.113 ]
-[ 1996-03-01, 2.112 ]
-[ 1996-03-02, 2.111 ]
-[ 1997-02-28, 1.748 ]
-[ 1997-03-01, 1.747 ]
-[ 1997-03-02, 1.746 ]
-[ 2000-04-01, 0.620 ]
-[ 2000-04-02, 0.619 ]
-[ 2000-04-03, 0.618 ]
-[ 2038-04-08, -13.266 ]
-[ 2039-04-09, -13.632 ]
-[ 2040-04-10, -13.999 ]
+% 10, 15 # length
+[ 1957-04-09, 1409875200.000 ]
+[ 1957-06-13, 1404259200.000 ]
+[ 1996-02-28, 182649600.000 ]
+[ 1996-02-29, 182563200.000 ]
+[ 1996-03-01, 182476800.000 ]
+[ 1996-03-02, 182390400.000 ]
+[ 1997-02-28, 151027200.000 ]
+[ 1997-03-01, 150940800.000 ]
+[ 1997-03-02, 150854400.000 ]
+[ 2000-04-01, 53568000.000 ]
+[ 2000-04-02, 53481600.000 ]
+[ 2000-04-03, 53395200.000 ]
+[ 2038-04-08, -1146182400.000 ]
+[ 2039-04-09, -1177804800.000 ]
+[ 2040-04-10, -1209513600.000 ]
[ NULL, NULL ]
#SELECT date 'January 8, 1999';
% .%2 # table_name
@@ -1058,22 +1058,22 @@ stdout of test 'date` in directory 'sql/
% sys. # table_name
% "Days From 2K" # name
% day_interval # type
-% 7 # length
-[ -15.607 ]
-[ -15.542 ]
-[ -1.403 ]
-[ -1.402 ]
-[ -1.401 ]
-[ -1.400 ]
-[ -1.037 ]
-[ -1.036 ]
-[ -1.035 ]
-[ 0.091 ]
-[ 0.092 ]
-[ 0.093 ]
-[ 13.977 ]
-[ 14.343 ]
-[ 14.710 ]
+% 15 # length
+[ -1348444800.000 ]
+[ -1342828800.000 ]
+[ -121219200.000 ]
+[ -121132800.000 ]
+[ -121046400.000 ]
+[ -120960000.000 ]
+[ -89596800.000 ]
+[ -89510400.000 ]
+[ -89424000.000 ]
+[ 7862400.000 ]
+[ 7948800.000 ]
+[ 8035200.000 ]
+[ 1207612800.000 ]
+[ 1239235200.000 ]
+[ 1270944000.000 ]
[ NULL ]
#SELECT EXTRACT(CENTURY FROM DATE '0101-12-31 BC'); -- -2
% .%2 # table_name
diff --git a/sql/test/sys-schema/Tests/systemfunctions.stable.out
b/sql/test/sys-schema/Tests/systemfunctions.stable.out
--- a/sql/test/sys-schema/Tests/systemfunctions.stable.out
+++ b/sql/test/sys-schema/Tests/systemfunctions.stable.out
@@ -639,8 +639,6 @@ stdout of test 'systemfunctions` in dire
[ "sys", "day", 1, "timestamptz", "" ]
[ "sys", "day", 0, "bigint", "day" ]
[ "sys", "day", 1, "day_interval", "" ]
-[ "sys", "day", 0, "bigint", "day" ]
-[ "sys", "day", 1, "sec_interval", "" ]
[ "sys", "dayofmonth", 0, "int", "day" ]
[ "sys", "dayofmonth", 1, "date", "" ]
[ "sys", "dayofweek", 0, "int", "dayofweek" ]
@@ -3718,6 +3716,9 @@ stdout of test 'systemfunctions` in dire
[ "sys", "sql_add", 2, "double", "" ]
[ "sys", "sql_add", 0, "date", "date_add_msec_interval"
]
[ "sys", "sql_add", 1, "date", "" ]
+[ "sys", "sql_add", 2, "sec_interval", "" ]
+[ "sys", "sql_add", 0, "date", "date_add_msec_interval"
]
+[ "sys", "sql_add", 1, "date", "" ]
[ "sys", "sql_add", 2, "day_interval", "" ]
[ "sys", "sql_add", 0, "date", "addmonths" ]
[ "sys", "sql_add", 1, "date", "" ]
@@ -3725,12 +3726,18 @@ stdout of test 'systemfunctions` in dire
[ "sys", "sql_add", 0, "timestamp",
"timestamp_add_msec_interval" ]
[ "sys", "sql_add", 1, "timestamp", "" ]
[ "sys", "sql_add", 2, "sec_interval", "" ]
+[ "sys", "sql_add", 0, "timestamp",
"timestamp_add_msec_interval" ]
+[ "sys", "sql_add", 1, "timestamp", "" ]
+[ "sys", "sql_add", 2, "day_interval", "" ]
[ "sys", "sql_add", 0, "timestamp",
"timestamp_add_month_interval" ]
[ "sys", "sql_add", 1, "timestamp", "" ]
[ "sys", "sql_add", 2, "month_interval", "" ]
[ "sys", "sql_add", 0, "timestamptz",
"timestamp_add_msec_interval" ]
[ "sys", "sql_add", 1, "timestamptz", "" ]
[ "sys", "sql_add", 2, "sec_interval", "" ]
+[ "sys", "sql_add", 0, "timestamptz",
"timestamp_add_msec_interval" ]
+[ "sys", "sql_add", 1, "timestamptz", "" ]
+[ "sys", "sql_add", 2, "day_interval", "" ]
[ "sys", "sql_add", 0, "timestamptz",
"timestamp_add_month_interval" ]
[ "sys", "sql_add", 1, "timestamptz", "" ]
[ "sys", "sql_add", 2, "month_interval", "" ]
@@ -4305,6 +4312,9 @@ stdout of test 'systemfunctions` in dire
[ "sys", "sql_sub", 2, "timestamp", "" ]
[ "sys", "sql_sub", 0, "date", "date_sub_msec_interval"
]
[ "sys", "sql_sub", 1, "date", "" ]
+[ "sys", "sql_sub", 2, "sec_interval", "" ]
+[ "sys", "sql_sub", 0, "date", "date_sub_msec_interval"
]
+[ "sys", "sql_sub", 1, "date", "" ]
[ "sys", "sql_sub", 2, "day_interval", "" ]
[ "sys", "sql_sub", 0, "date", "date_sub_month_interval"
]
[ "sys", "sql_sub", 1, "date", "" ]
@@ -4318,12 +4328,18 @@ stdout of test 'systemfunctions` in dire
[ "sys", "sql_sub", 0, "timestamp",
"timestamp_sub_msec_interval" ]
[ "sys", "sql_sub", 1, "timestamp", "" ]
[ "sys", "sql_sub", 2, "sec_interval", "" ]
+[ "sys", "sql_sub", 0, "timestamp",
"timestamp_sub_msec_interval" ]
+[ "sys", "sql_sub", 1, "timestamp", "" ]
+[ "sys", "sql_sub", 2, "day_interval", "" ]
[ "sys", "sql_sub", 0, "timestamp",
"timestamp_sub_month_interval" ]
[ "sys", "sql_sub", 1, "timestamp", "" ]
[ "sys", "sql_sub", 2, "month_interval", "" ]
[ "sys", "sql_sub", 0, "timestamptz",
"timestamp_sub_msec_interval" ]
[ "sys", "sql_sub", 1, "timestamptz", "" ]
[ "sys", "sql_sub", 2, "sec_interval", "" ]
+[ "sys", "sql_sub", 0, "timestamptz",
"timestamp_sub_msec_interval" ]
+[ "sys", "sql_sub", 1, "timestamptz", "" ]
+[ "sys", "sql_sub", 2, "day_interval", "" ]
[ "sys", "sql_sub", 0, "timestamptz",
"timestamp_sub_month_interval" ]
[ "sys", "sql_sub", 1, "timestamptz", "" ]
[ "sys", "sql_sub", 2, "month_interval", "" ]
diff --git a/sql/test/sys-schema/Tests/systemfunctions.stable.out.int128
b/sql/test/sys-schema/Tests/systemfunctions.stable.out.int128
--- a/sql/test/sys-schema/Tests/systemfunctions.stable.out.int128
+++ b/sql/test/sys-schema/Tests/systemfunctions.stable.out.int128
@@ -694,8 +694,6 @@ stdout of test 'systemfunctions` in dire
[ "sys", "day", 1, "timestamptz", "" ]
[ "sys", "day", 0, "bigint", "day" ]
[ "sys", "day", 1, "day_interval", "" ]
-[ "sys", "day", 0, "bigint", "day" ]
-[ "sys", "day", 1, "sec_interval", "" ]
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
