Changeset: 19aae50627ec for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=19aae50627ec
Added Files:
sql/test/BugTracker-2019/Tests/date_trunc_bulk.sql
sql/test/BugTracker-2019/Tests/date_trunc_bulk.stable.err
sql/test/BugTracker-2019/Tests/date_trunc_bulk.stable.out
Modified Files:
sql/backends/monet5/sql_datetrunc.c
sql/backends/monet5/sql_upgrades.c
sql/test/BugTracker-2019/Tests/All
sql/test/BugTracker-2019/Tests/date_trunc.stable.err
sql/test/BugTracker-2019/Tests/date_trunc.stable.out
Branch: Apr2019
Log Message:
Added missing vectorized versions, tests and include upgrade code.
(grafted from 4e221d75283511fe55ace37f9b76d5a3eb134f2b)
diffs (truncated from 473 to 300 lines):
diff --git a/sql/backends/monet5/sql_datetrunc.c
b/sql/backends/monet5/sql_datetrunc.c
--- a/sql/backends/monet5/sql_datetrunc.c
+++ b/sql/backends/monet5/sql_datetrunc.c
@@ -50,6 +50,7 @@ bat_date_trunc(bat *res, const str *scal
char *msg = NULL;
lng nils = 0;
timestamp ts;
+ int dow, y, m, d, one = 1;
if ( truncate_check(scale) == 0)
throw(SQL, "batcalc.truncate_timestamp", SQLSTATE(HY005)
"Improper directive ");
@@ -83,14 +84,98 @@ bat_date_trunc(bat *res, const str *scal
ts = bt[lo];
ts.msecs = 0;
dt[lo] = ts;
- } }
+ } }
+
+ if ( strcmp(*scale, "week") == 0){
+ for( ; lo < hi; lo++)
+ if (is_timestamp_nil(bt[lo])) {
+ dt[lo] = *timestamp_nil;
+ } else {
+ ts = bt[lo];
+ ts.msecs = 0;
+ MTIMEdate_extract_ymd(&y, &m, &d, &ts.days);
+ MTIMEdate_extract_dayofweek(&dow, &ts.days);
+ d = d - dow - 1;
+ MTIMEdate_create(&ts.days, &y, &m, &d);
+ dt[lo] = ts;
+ } }
+
+ if ( strcmp(*scale, "month") == 0){
+ for( ; lo < hi; lo++)
+ if (is_timestamp_nil(bt[lo])) {
+ dt[lo] = *timestamp_nil;
+ } else {
+ ts = bt[lo];
+ ts.msecs = 0;
+ MTIMEdate_extract_ymd(&y, &m, &d, &ts.days);
+ MTIMEdate_create(&ts.days, &y, &m, &one);
+ dt[lo] = ts;
+ } }
+
+ if ( strcmp(*scale, "quarter") == 0){
+ for( ; lo < hi; lo++)
+ if (is_timestamp_nil(bt[lo])) {
+ dt[lo] = *timestamp_nil;
+ } else {
+ ts = bt[lo];
+ ts.msecs = 0;
+ MTIMEdate_extract_ymd(&y, &m, &d, &ts.days);
+ m = m/4 + 1;
+ MTIMEdate_create(&ts.days, &y, &one, &one);
+ dt[lo] = ts;
+ } }
- // week
- // month
- // quarter
- // decade
- // century
- // millenium
+ if ( strcmp(*scale, "year") == 0){
+ for( ; lo < hi; lo++)
+ if (is_timestamp_nil(bt[lo])) {
+ dt[lo] = *timestamp_nil;
+ } else {
+ ts = bt[lo];
+ ts.msecs = 0;
+ MTIMEdate_extract_ymd(&y, &m, &d, &ts.days);
+ MTIMEdate_create(&ts.days, &y, &one, &one);
+ dt[lo] = ts;
+ } }
+
+ if ( strcmp(*scale, "decade") == 0){
+ for( ; lo < hi; lo++)
+ if (is_timestamp_nil(bt[lo])) {
+ dt[lo] = *timestamp_nil;
+ } else {
+ ts = bt[lo];
+ ts.msecs = 0;
+ MTIMEdate_extract_ymd(&y, &m, &d, &ts.days);
+ y = (y /10 ) *10;
+ MTIMEdate_create(&ts.days, &y, &one, &one);
+ dt[lo] = ts;
+ } }
+
+ if ( strcmp(*scale, "century") == 0){
+ for( ; lo < hi; lo++)
+ if (is_timestamp_nil(bt[lo])) {
+ dt[lo] = *timestamp_nil;
+ } else {
+ ts = bt[lo];
+ ts.msecs = 0;
+ MTIMEdate_extract_ymd(&y, &m, &d, &ts.days);
+ y = (y /100 ) *100;
+ MTIMEdate_create(&ts.days, &y, &one, &one);
+ dt[lo] = ts;
+ } }
+
+ if ( strcmp(*scale, "millenium") == 0){
+ for( ; lo < hi; lo++)
+ if (is_timestamp_nil(bt[lo])) {
+ dt[lo] = *timestamp_nil;
+ } else {
+ ts = bt[lo];
+ ts.msecs = 0;
+ MTIMEdate_extract_ymd(&y, &m, &d, &ts.days);
+ y = (y /1000 ) *1000;
+ MTIMEdate_create(&ts.days, &y, &one, &one);
+ dt[lo] = ts;
+ } }
+
if( nils){
bn->tnonil = false;
bn->tnil = true;
diff --git a/sql/backends/monet5/sql_upgrades.c
b/sql/backends/monet5/sql_upgrades.c
--- a/sql/backends/monet5/sql_upgrades.c
+++ b/sql/backends/monet5/sql_upgrades.c
@@ -1539,6 +1539,14 @@ sql_update_apr2019(Client c, mvc *sql)
pos += snprintf(buf + pos, bufsize - pos, "set schema sys;\n");
+ /* 17_temporal.sql */
+
+ pos += snprintf(buf + pos, bufsize - pos,
+ "drop function sys.date_trunc;\n"
+ "create function sys.date_trunc(txt string, t
timestamp)\n"
+ "returns timestamp\n"
+ "external name sql.date_trunc;\n");
+
/* 26_sysmon.sql */
pos += snprintf(buf + pos, bufsize - pos,
"grant execute on function sys.queue to public;\n"
diff --git a/sql/test/BugTracker-2019/Tests/All
b/sql/test/BugTracker-2019/Tests/All
--- a/sql/test/BugTracker-2019/Tests/All
+++ b/sql/test/BugTracker-2019/Tests/All
@@ -1,5 +1,6 @@
copy-into-from-stdin-empty-line.Bug-6669
date_trunc
+date_trunc_bulk
subselect-contradiction.Bug-6683
insert-replica-table.Bug-6684
subselect-count.Bug-6686
diff --git a/sql/test/BugTracker-2019/Tests/date_trunc.stable.err
b/sql/test/BugTracker-2019/Tests/date_trunc.stable.err
--- a/sql/test/BugTracker-2019/Tests/date_trunc.stable.err
+++ b/sql/test/BugTracker-2019/Tests/date_trunc.stable.err
@@ -28,12 +28,8 @@ stderr of test 'date_trunc` in directory
# 08:07:24 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-23934" "--port=33395"
# 08:07:24 >
-MAPI = (monetdb) /var/tmp/mtest-24278/.s.monetdb.39992
-QUERY = select date_trunc('millennium', timestamp '2019-02-17
02:08:12.345678');
-
-CODE = HY001
-# 08:07:24 >
-# 08:07:24 > "Done."
-# 08:07:24 >
+# 21:04:00 >
+# 21:04:00 > "Done."
+# 21:04:00 >
diff --git a/sql/test/BugTracker-2019/Tests/date_trunc.stable.out
b/sql/test/BugTracker-2019/Tests/date_trunc.stable.out
--- a/sql/test/BugTracker-2019/Tests/date_trunc.stable.out
+++ b/sql/test/BugTracker-2019/Tests/date_trunc.stable.out
@@ -29,68 +29,80 @@ Ready.
% L2 # name
% timestamp # type
% 26 # length
-[ 2019-02-17 02:08:12.345678 ]
-#select date_trunc('milliseconds', timestamp '2019-02-17 02:08:12.345678');
+[ 2119-02-17 02:08:12.346000 ]
+#select date_trunc('microseconds', timestamp '2119-02-17 02:08:12.345678');
% .L2 # table_name
% L2 # name
% timestamp # type
% 26 # length
-[ 2019-02-17 02:08:12.345000 ]
-#select date_trunc('second', timestamp '2019-02-17 02:08:12.345678');
+[ 2119-02-17 02:08:12.346000 ]
+#select date_trunc('milliseconds', timestamp '2119-02-17 02:08:12.345678');
% .L2 # table_name
% L2 # name
% timestamp # type
% 26 # length
-[ 2019-02-17 02:08:12.000000 ]
-#select date_trunc('minute', timestamp '2019-02-17 02:08:12.345678');
+[ 2119-02-17 02:08:12.346000 ]
+#select date_trunc('second', timestamp '2119-02-17 02:08:12.345678');
% .L2 # table_name
% L2 # name
% timestamp # type
% 26 # length
-[ 2019-02-17 02:08:00.000000 ]
-#select date_trunc('hour', timestamp '2019-02-17 02:08:12.345678');
+[ 2119-02-17 02:08:12.000000 ]
+#select date_trunc('minute', timestamp '2119-02-17 02:08:12.345678');
% .L2 # table_name
% L2 # name
% timestamp # type
% 26 # length
-[ 2019-02-17 02:00:00.000000 ]
-#select date_trunc('day', timestamp '2019-02-17 02:08:12.345678');
+[ 2119-02-17 02:08:00.000000 ]
+#select date_trunc('hour', timestamp '2119-02-17 02:08:12.345678');
+% .L2 # table_name
+% L2 # name
+% timestamp # type
+% 26 # length
+[ 2119-02-17 02:00:00.000000 ]
+#select date_trunc('day', timestamp '2119-02-17 02:08:12.345678');
% .L2 # table_name
% L2 # name
% timestamp # type
% 26 # length
-[ 2019-02-17 00:00:00.000000 ]
-#select date_trunc('week', timestamp '2019-02-17 02:08:12.345678');
+[ 2119-02-17 00:00:00.000000 ]
+#select date_trunc('week', timestamp '2119-02-17 02:08:12.345678');
% .L2 # table_name
% L2 # name
% timestamp # type
% 26 # length
-[ 2019-02-11 00:00:00.000000 ]
-#select date_trunc('month', timestamp '2019-02-17 02:08:12.345678');
+[ 2119-02-11 00:00:00.000000 ]
+#select date_trunc('month', timestamp '2119-02-17 02:08:12.345678');
+% .L2 # table_name
+% L2 # name
+% timestamp # type
+% 26 # length
+[ 2119-02-01 00:00:00.000000 ]
+#select date_trunc('quarter', timestamp '2119-02-17 02:08:12.345678');
% .L2 # table_name
% L2 # name
% timestamp # type
% 26 # length
-[ 2019-02-01 00:00:00.000000 ]
-#select date_trunc('quarter', timestamp '2019-02-17 02:08:12.345678');
+[ 2119-01-01 00:00:00.000000 ]
+#select date_trunc('year', timestamp '2119-02-17 02:08:12.345678');
% .L2 # table_name
% L2 # name
% timestamp # type
% 26 # length
-[ 2019-01-01 00:00:00.000000 ]
-#select date_trunc('year', timestamp '2019-02-17 02:08:12.345678');
+[ 2119-01-01 00:00:00.000000 ]
+#select date_trunc('decade', timestamp '2119-02-17 02:08:12.345678');
% .L2 # table_name
% L2 # name
% timestamp # type
% 26 # length
-[ 2019-01-01 00:00:00.000000 ]
-#select date_trunc('decade', timestamp '2019-02-17 02:08:12.345678');
+[ 2110-01-01 00:00:00.000000 ]
+#select date_trunc('century', timestamp '2119-02-17 02:08:12.345678');
% .L2 # table_name
% L2 # name
% timestamp # type
% 26 # length
-[ 2010-01-01 00:00:00.000000 ]
-#select date_trunc('century', timestamp '2019-02-17 02:08:12.345678');
+[ 2101-01-01 00:00:00.000000 ]
+#select date_trunc('millenium', timestamp '2119-02-17 02:08:12.345678');
% .L2 # table_name
% L2 # name
% timestamp # type
diff --git a/sql/test/BugTracker-2019/Tests/date_trunc_bulk.sql
b/sql/test/BugTracker-2019/Tests/date_trunc_bulk.sql
new file mode 100644
--- /dev/null
+++ b/sql/test/BugTracker-2019/Tests/date_trunc_bulk.sql
@@ -0,0 +1,21 @@
+start transaction;
+create table dt_tmp( t timestamp);
+insert into dt_tmp values (timestamp '2119-02-17 02:08:12.345678'), (null);
+
+select * from dt_tmp;
+
+select date_trunc('microseconds', t) from dt_tmp;
+select date_trunc('milliseconds', t) from dt_tmp;
+select date_trunc('second', t) from dt_tmp;
+select date_trunc('minute', t) from dt_tmp;
+select date_trunc('hour', t) from dt_tmp;
+
+select date_trunc('day', t) from dt_tmp;
+select date_trunc('week', t) from dt_tmp;
+select date_trunc('month', t) from dt_tmp;
+select date_trunc('quarter', t) from dt_tmp;
+select date_trunc('year', t) from dt_tmp;
+select date_trunc('decade', t) from dt_tmp;
+select date_trunc('century', t) from dt_tmp;
+select date_trunc('millenium', t) from dt_tmp;
+rollback;
diff --git a/sql/test/BugTracker-2019/Tests/date_trunc_bulk.stable.err
b/sql/test/BugTracker-2019/Tests/date_trunc_bulk.stable.err
new file mode 100644
--- /dev/null
+++ b/sql/test/BugTracker-2019/Tests/date_trunc_bulk.stable.err
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
