Changeset: f7406735f6d8 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/f7406735f6d8
Added Files:
sql/backends/monet5/generator/Tests/generator05.test
Modified Files:
sql/backends/monet5/generator/90_generator.sql
sql/backends/monet5/generator/Tests/All
sql/backends/monet5/generator/generator.c
Branch: default
Log Message:
add generate_series for dates, ie steps of 1 or more days or months.
diffs (truncated from 670 to 300 lines):
diff --git a/sql/backends/monet5/generator/90_generator.sql
b/sql/backends/monet5/generator/90_generator.sql
--- a/sql/backends/monet5/generator/90_generator.sql
+++ b/sql/backends/monet5/generator/90_generator.sql
@@ -54,6 +54,14 @@ create function sys.generate_series(firs
returns table (value decimal(10,2))
external name generator.series;
+create function sys.generate_series(first date, "limit" date, stepsize
interval month)
+returns table (value date)
+external name generator.series;
+
+create function sys.generate_series(first date, "limit" date, stepsize
interval day)
+returns table (value date)
+external name generator.series;
+
create function sys.generate_series(first timestamp, "limit" timestamp,
stepsize interval second)
returns table (value timestamp)
external name generator.series;
diff --git a/sql/backends/monet5/generator/Tests/All
b/sql/backends/monet5/generator/Tests/All
--- a/sql/backends/monet5/generator/Tests/All
+++ b/sql/backends/monet5/generator/Tests/All
@@ -3,6 +3,7 @@ generator01
generator02
generator03
generator04
+generator05
rangejoin
diff --git a/sql/backends/monet5/generator/Tests/generator05.test
b/sql/backends/monet5/generator/Tests/generator05.test
new file mode 100644
--- /dev/null
+++ b/sql/backends/monet5/generator/Tests/generator05.test
@@ -0,0 +1,24 @@
+query T nosort
+select * from generate_series(date '2023-01-01',date '2024-01-01', interval
'1' month) where value < date '2023-06-06'
+----
+2023-01-01
+2023-02-01
+2023-03-01
+2023-04-01
+2023-05-01
+
+query T nosort
+select * from generate_series(date '2023-01-01',date '2024-01-01', interval
'15' day) where value < date '2023-06-06'
+----
+2023-01-01
+2023-01-16
+2023-01-31
+2023-02-15
+2023-03-02
+2023-03-17
+2023-04-01
+2023-04-16
+2023-05-01
+2023-05-16
+2023-05-31
+
diff --git a/sql/backends/monet5/generator/generator.c
b/sql/backends/monet5/generator/generator.c
--- a/sql/backends/monet5/generator/generator.c
+++ b/sql/backends/monet5/generator/generator.c
@@ -12,7 +12,7 @@
/*
* (c) Martin Kersten, Sjoerd Mullender
- * Series generating module for integer, decimal, real, double and timestamps.
+ * Series generating module for integer, decimal, real, double, date and
timestamps.
*/
#include "monetdb_config.h"
@@ -51,11 +51,21 @@ VLTgenerator_noop(Client cntxt, MalBlkPt
case TYPE_flt: VLTnoop(flt); break;
case TYPE_dbl: VLTnoop(dbl); break;
default:
- if (tpe == TYPE_timestamp){
+ if (tpe == TYPE_date) {
+ /* with date, step is of SQL type "interval month or
day",
+ * i.e., MAL / C type "int" or "lng" */
+ int steptpe = pci->argc==4 ? getArgType(mb,pci,3) : 0;
+ if (steptpe == TYPE_int)
+ VLTnoop(int);
+ else /* default interval days */
+ VLTnoop(lng);
+ } else if (tpe == TYPE_timestamp) {
/* with timestamp, step is of SQL type "interval
seconds",
* i.e., MAL / C type "lng" */
VLTnoop(lng);
- } else throw(MAL,"generator.noop", SQLSTATE(42000) "unknown
data type %d", getArgType(mb,pci,1));
+ } else {
+ throw(MAL,"generator.noop", SQLSTATE(42000) "unknown
data type %d", getArgType(mb,pci,1));
+ }
}
if( zeroerror)
throw(MAL,"generator.noop", SQLSTATE(42000) "Zero step size not
allowed");
@@ -204,7 +214,77 @@ VLTgenerator_table_(BAT **result, Client
VLTmaterialize_flt(dbl);
break;
default:
- if (tpe == TYPE_timestamp) {
+ if (tpe == TYPE_date && pci->argc == 3)
+ throw(MAL,"generator.table", SQLSTATE(42000) "Date step
missing");
+ if (tpe == TYPE_date && getArgType(mb, pci, 3) == TYPE_int) {
/* months */
+ date *v,f,l;
+ int s;
+ ValRecord ret;
+ if (VARcalccmp(&ret, &stk->stk[pci->argv[1]],
+ &stk->stk[pci->argv[2]]) != GDK_SUCCEED)
+ throw(MAL, "generator.table",
+ SQLSTATE(42000) "Illegal generator
expression range");
+ f = *getArgReference_TYPE(stk, pci, 1, date);
+ l = *getArgReference_TYPE(stk, pci, 2, date);
+ s = *getArgReference_int(stk, pci, 3);
+ if (s == 0 ||
+ (s > 0 && ret.val.btval > 0) ||
+ (s < 0 && ret.val.btval < 0) ||
+ is_date_nil(f) || is_date_nil(l))
+ throw(MAL, "generator.table",
+ SQLSTATE(42000) "Illegal generator
range");
+ n = (BUN) (date_diff(l, f) / (s *28)); /* n maybe too
large now */
+ bn = COLnew(0, TYPE_date, n + 1, TRANSIENT);
+ if (bn == NULL)
+ throw(MAL, "generator.table", SQLSTATE(HY013)
MAL_MALLOC_FAIL);
+ v = (date *) Tloc(bn, 0);
+ for (c = 0; c < n && f < l; c++) {
+ *v++ = f;
+ f = date_add_month(f, s);
+ if (is_date_nil(f)) {
+ BBPreclaim(bn);
+ throw(MAL, "generator.table",
SQLSTATE(22003) "overflow in calculation");
+ }
+ }
+ n = c;
+ bn->tsorted = s > 0 || n <= 1;
+ bn->trevsorted = s < 0 || n <= 1;
+ } else if (tpe == TYPE_date) { /* days */
+ date *v,f,l;
+ lng s;
+ ValRecord ret;
+ if (VARcalccmp(&ret, &stk->stk[pci->argv[1]],
+ &stk->stk[pci->argv[2]]) != GDK_SUCCEED)
+ throw(MAL, "generator.table",
+ SQLSTATE(42000) "Illegal generator
expression range");
+ f = *getArgReference_TYPE(stk, pci, 1, date);
+ l = *getArgReference_TYPE(stk, pci, 2, date);
+ s = *getArgReference_lng(stk, pci, 3);
+ if (s == 0 ||
+ (s > 0 && ret.val.btval > 0) ||
+ (s < 0 && ret.val.btval < 0) ||
+ is_date_nil(f) || is_date_nil(l))
+ throw(MAL, "generator.table",
+ SQLSTATE(42000) "Illegal generator
range");
+ s /= 24*60*60*1000;
+ /* check if s is really in nr of days or usecs */
+ n = (BUN) (date_diff(l, f) / s) + 1; /* n maybe too
large now */
+ bn = COLnew(0, TYPE_date, n + 1, TRANSIENT);
+ if (bn == NULL)
+ throw(MAL, "generator.table", SQLSTATE(HY013)
MAL_MALLOC_FAIL);
+ v = (date *) Tloc(bn, 0);
+ for (c = 0; c < n && f < l; c++) {
+ *v++ = f;
+ f = date_add_day(f, s);
+ if (is_date_nil(f)) {
+ BBPreclaim(bn);
+ throw(MAL, "generator.table",
SQLSTATE(22003) "overflow in calculation");
+ }
+ }
+ n = c;
+ bn->tsorted = s > 0 || n <= 1;
+ bn->trevsorted = s < 0 || n <= 1;
+ } else if (tpe == TYPE_timestamp) {
timestamp *v,f,l;
lng s;
ValRecord ret;
@@ -421,7 +501,158 @@ VLTgenerator_subselect(Client cntxt, Mal
case TYPE_flt: calculate_range(flt, dbl); break;
case TYPE_dbl: calculate_range(dbl, dbl); break;
default:
- if( tpe == TYPE_timestamp){
+ if (p->argc == 3) {
+ BBPreclaim(cand);
+ throw(MAL,"generator.table", SQLSTATE(42000) "Date step
missing");
+ }
+ if (tpe == TYPE_date && getArgType(mb, p, 3) == TYPE_int) { /*
months */
+ date tsf,tsl;
+ date tlow,thgh;
+ int tss;
+ oid *ol;
+
+ tsf = *getArgReference_TYPE(stk, p, 1, date);
+ tsl = *getArgReference_TYPE(stk, p, 2, date);
+ tss = *getArgReference_int(stk, p, 3);
+ if ( tss == 0 ||
+ is_date_nil(tsf) || is_date_nil(tsl) ||
+ (tss > 0 && tsf > tsl ) ||
+ (tss < 0 && tsf < tsl )
+ ) {
+ BBPreclaim(cand);
+ throw(MAL, "generator.select", SQLSTATE(42000)
"Illegal generator range");
+ }
+
+ tlow = *getArgReference_TYPE(stk,pci,i, date);
+ thgh = *getArgReference_TYPE(stk,pci,i+1, date);
+
+ if (!is_date_nil(tlow) && tlow == thgh)
+ hi = li;
+ if( hi && !is_date_nil(thgh)) {
+ thgh = date_add_month(thgh, 1);
+ if (is_date_nil(thgh)) {
+ BBPreclaim(cand);
+ throw(MAL, "generator.select",
SQLSTATE(22003) "overflow in calculation");
+ }
+ }
+ if( !li && !is_date_nil(tlow)) {
+ tlow = date_add_month(tlow, 1);
+ if (is_date_nil(tlow)) {
+ BBPreclaim(cand);
+ throw(MAL, "generator.select",
SQLSTATE(22003) "overflow in calculation");
+ }
+ }
+
+ o2 = (BUN) (date_diff(tsl, tsf) / (tss*28));
+ bn = COLnew(0, TYPE_oid, o2 + 1, TRANSIENT);
+ if (bn == NULL) {
+ BBPreclaim(cand);
+ throw(MAL, "generator.select", SQLSTATE(HY013)
MAL_MALLOC_FAIL);
+ }
+
+ // simply enumerate the sequence and filter it by
predicate and candidate list
+ ol = (oid *) Tloc(bn, 0);
+ for (o1=0; o1 <= o2; o1++) {
+ if(((is_date_nil(tlow) || tsf >= tlow) &&
+ (is_date_nil(thgh) || tsf < thgh)) != anti
){
+ /* could be improved when no candidate
list is available into a void/void BAT */
+ if( cand == NULL ||
canditer_contains(&ci, o1)) {
+ *ol++ = o1;
+ n++;
+ }
+ }
+ tsf = date_add_month(tsf, tss);
+ if (is_date_nil(tsf)) {
+ BBPreclaim(cand);
+ BBPreclaim(bn);
+ throw(MAL, "generator.select",
SQLSTATE(22003) "overflow in calculation");
+ }
+ }
+ BBPreclaim(cand);
+ BATsetcount(bn, n);
+ bn->tsorted = true;
+ bn->trevsorted = BATcount(bn) <= 1;
+ bn->tkey = true;
+ bn->tnil = false;
+ bn->tnonil = true;
+ * getArgReference_bat(stk, pci, 0) = bn->batCacheid;
+ BBPkeepref(bn);
+ return MAL_SUCCEED;
+ } else if (tpe == TYPE_date) { /* days */
+ date tsf,tsl;
+ date tlow,thgh;
+ lng tss;
+ oid *ol;
+
+ tsf = *getArgReference_TYPE(stk, p, 1, date);
+ tsl = *getArgReference_TYPE(stk, p, 2, date);
+ tss = *getArgReference_lng(stk, p, 3);
+ if ( tss == 0 ||
+ is_date_nil(tsf) || is_date_nil(tsl) ||
+ (tss > 0 && tsf > tsl ) ||
+ (tss < 0 && tsf < tsl )
+ ) {
+ BBPreclaim(cand);
+ throw(MAL, "generator.select", SQLSTATE(42000)
"Illegal generator range");
+ }
+ tss /= 24*60*60*1000;
+
+ tlow = *getArgReference_TYPE(stk,pci,i, date);
+ thgh = *getArgReference_TYPE(stk,pci,i+1, date);
+
+ if (!is_date_nil(tlow) && tlow == thgh)
+ hi = li;
+ if( hi && !is_date_nil(thgh)) {
+ thgh = date_add_month(thgh, 1);
+ if (is_date_nil(thgh)) {
+ BBPreclaim(cand);
+ throw(MAL, "generator.select",
SQLSTATE(22003) "overflow in calculation");
+ }
+ }
+ if( !li && !is_date_nil(tlow)) {
+ tlow = date_add_month(tlow, 1);
+ if (is_date_nil(tlow)) {
+ BBPreclaim(cand);
+ throw(MAL, "generator.select",
SQLSTATE(22003) "overflow in calculation");
+ }
+ }
+
+ o2 = (BUN) (date_diff(tsl, tsf) / tss) + 1;
+ bn = COLnew(0, TYPE_oid, o2 + 1, TRANSIENT);
+ if (bn == NULL) {
+ BBPreclaim(cand);
+ throw(MAL, "generator.select", SQLSTATE(HY013)
MAL_MALLOC_FAIL);
+ }
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
