Changeset: b9c2b91e0f24 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/b9c2b91e0f24
Modified Files:
clients/Tests/MAL-signatures-hge.test
clients/Tests/MAL-signatures.test
sql/backends/monet5/sql.c
sql/backends/monet5/sql_rank.c
sql/test/analytics/Tests/analytics19.test
Branch: Jan2022
Log Message:
Fixed sum window function bug on interval types (ie make sure the output type
is the same as the input one)
diffs (truncated from 434 to 300 lines):
diff --git a/clients/Tests/MAL-signatures-hge.test
b/clients/Tests/MAL-signatures-hge.test
--- a/clients/Tests/MAL-signatures-hge.test
+++ b/clients/Tests/MAL-signatures-hge.test
@@ -44844,6 +44844,11 @@ SQLsum;
return the sum of groups
batsql
sum
+pattern batsql.sum(X_0:bat[:int], X_1:any, X_2:any, X_3:int, X_4:any,
X_5:any):bat[:int]
+SQLsum;
+return the sum of groups
+batsql
+sum
pattern batsql.sum(X_0:bat[:int], X_1:any, X_2:any, X_3:int, X_4:any,
X_5:any):bat[:lng]
SQLsum;
return the sum of groups
@@ -63799,6 +63804,11 @@ SQLsum;
return the sum of groups
sql
sum
+pattern sql.sum(X_0:int, X_1:bit, X_2:bit, X_3:int, X_4:oid, X_5:oid):int
+SQLsum;
+return the sum of groups
+sql
+sum
pattern sql.sum(X_0:int, X_1:bit, X_2:bit, X_3:int, X_4:oid, X_5:oid):lng
SQLsum;
return the sum of groups
diff --git a/clients/Tests/MAL-signatures.test
b/clients/Tests/MAL-signatures.test
--- a/clients/Tests/MAL-signatures.test
+++ b/clients/Tests/MAL-signatures.test
@@ -32094,6 +32094,11 @@ SQLsum;
return the sum of groups
batsql
sum
+pattern batsql.sum(X_0:bat[:int], X_1:any, X_2:any, X_3:int, X_4:any,
X_5:any):bat[:int]
+SQLsum;
+return the sum of groups
+batsql
+sum
pattern batsql.sum(X_0:bat[:lng], X_1:any, X_2:any, X_3:int, X_4:any,
X_5:any):bat[:lng]
SQLsum;
return the sum of groups
@@ -47224,6 +47229,11 @@ SQLsum;
return the sum of groups
sql
sum
+pattern sql.sum(X_0:int, X_1:bit, X_2:bit, X_3:int, X_4:oid, X_5:oid):int
+SQLsum;
+return the sum of groups
+sql
+sum
pattern sql.sum(X_0:int, X_1:bit, X_2:bit, X_3:int, X_4:oid, X_5:oid):lng
SQLsum;
return the sum of groups
diff --git a/sql/backends/monet5/sql.c b/sql/backends/monet5/sql.c
--- a/sql/backends/monet5/sql.c
+++ b/sql/backends/monet5/sql.c
@@ -5773,6 +5773,9 @@ static mel_func sql_init_funcs[] = {
pattern("batsql", "sum", SQLsum, false, "return the sum of groups", args(1,7,
batarg("",dbl),batarg("b",flt),argany("p",0),argany("o",0),arg("t",int),argany("s",0),argany("e",0))),
pattern("sql", "sum", SQLsum, false, "return the sum of groups", args(1,7,
arg("",dbl),arg("b",dbl),arg("p",bit),arg("o",bit),arg("t",int),arg("s",oid),arg("e",oid))),
pattern("batsql", "sum", SQLsum, false, "return the sum of groups", args(1,7,
batarg("",dbl),batarg("b",dbl),argany("p",0),argany("o",0),arg("t",int),argany("s",0),argany("e",0))),
+ /* sql.sum for month intervals */
+ pattern("sql", "sum", SQLsum, false, "return the sum of groups", args(1,7,
arg("",int),arg("b",int),arg("p",bit),arg("o",bit),arg("t",int),arg("s",oid),arg("e",oid))),
+ pattern("batsql", "sum", SQLsum, false, "return the sum of groups", args(1,7,
batarg("",int),batarg("b",int),argany("p",0),argany("o",0),arg("t",int),argany("s",0),argany("e",0))),
pattern("sql", "prod", SQLprod, false, "return the product of groups",
args(1,7,
arg("",lng),arg("b",bte),arg("p",bit),arg("o",bit),arg("t",int),arg("s",oid),arg("e",oid))),
pattern("batsql", "prod", SQLprod, false, "return the product of groups",
args(1,7,
batarg("",lng),batarg("b",bte),argany("p",0),argany("o",0),arg("t",int),argany("s",0),argany("e",0))),
pattern("sql", "prod", SQLprod, false, "return the product of groups",
args(1,7,
arg("",lng),arg("b",sht),arg("p",bit),arg("o",bit),arg("t",int),arg("s",oid),arg("e",oid))),
diff --git a/sql/backends/monet5/sql_rank.c b/sql/backends/monet5/sql_rank.c
--- a/sql/backends/monet5/sql_rank.c
+++ b/sql/backends/monet5/sql_rank.c
@@ -1364,6 +1364,7 @@ do_analytical_sumprod(Client cntxt, MalB
(void) cntxt;
if (pci->argc != 7)
throw(SQL, op, ILLEGAL_ARGUMENT "%s requires exactly 7
arguments", op);
+ tp2 = getArgType(mb, pci, 0);
tp1 = getArgType(mb, pci, 1);
frame_type = *getArgReference_int(stk, pci, 4);
assert(frame_type >= 0 && frame_type <= 6);
@@ -1375,29 +1376,9 @@ do_analytical_sumprod(Client cntxt, MalB
goto bailout;
}
}
- switch (tp1) {
- case TYPE_bte:
- case TYPE_sht:
- case TYPE_int:
- case TYPE_lng:
-#ifdef HAVE_HGE
- case TYPE_hge:
- tp2 = TYPE_hge;
-#else
- tp2 = TYPE_lng;
-#endif
- break;
- case TYPE_flt:
- tp2 = TYPE_flt;
- break;
- case TYPE_dbl:
- tp2 = TYPE_dbl;
- break;
- default: {
- msg = createException(SQL, op, SQLSTATE(42000) "%s not
available for %s", op, ATOMname(tp1));
- goto bailout;
- }
- }
+ if (isaBatType(tp2))
+ tp2 = getBatType(tp2);
+
if (b) {
res = getArgReference_bat(stk, pci, 0);
if (!(r = COLnew(b->hseqbase, tp2, BATcount(b), TRANSIENT))) {
@@ -1437,8 +1418,68 @@ do_analytical_sumprod(Client cntxt, MalB
ptr in = getArgReference(stk, pci, 1);
int scale = 0;
- switch (tp1) {
+ switch (tp2) {
+ case TYPE_bte:{
+ switch (tp1) {
+ case TYPE_bte:
+ msg = bte_dec2_bte((bte*)res, &scale, (bte*)in);
+ break;
+ default:
+ msg = createException(SQL, op, SQLSTATE(42000)
"type combination (%s(%s)->%s) not supported", op, ATOMname(tp1),
ATOMname(tp2));
+ }
+ break;
+ }
+ case TYPE_sht:{
+ switch (tp1) {
+ case TYPE_bte:
+ msg = bte_dec2_sht((sht*)res, &scale, (bte*)in);
+ break;
+ case TYPE_sht:
+ msg = sht_dec2_sht((sht*)res, &scale, (sht*)in);
+ break;
+ default:
+ msg = createException(SQL, op, SQLSTATE(42000)
"type combination (%s(%s)->%s) not supported", op, ATOMname(tp1),
ATOMname(tp2));
+ }
+ break;
+ }
+ case TYPE_int:{
+ switch (tp1) {
+ case TYPE_bte:
+ msg = bte_dec2_int((int*)res, &scale, (bte*)in);
+ break;
+ case TYPE_sht:
+ msg = sht_dec2_int((int*)res, &scale, (sht*)in);
+ break;
+ case TYPE_int:
+ msg = int_dec2_int((int*)res, &scale, (int*)in);
+ break;
+ default:
+ msg = createException(SQL, op, SQLSTATE(42000)
"type combination (%s(%s)->%s) not supported", op, ATOMname(tp1),
ATOMname(tp2));
+ }
+ break;
+ }
+ case TYPE_lng:{
+ switch (tp1) {
+ case TYPE_bte:
+ msg = bte_dec2_lng((lng*)res, &scale, (bte*)in);
+ break;
+ case TYPE_sht:
+ msg = sht_dec2_lng((lng*)res, &scale, (sht*)in);
+ break;
+ case TYPE_int:
+ msg = int_dec2_lng((lng*)res, &scale, (int*)in);
+ break;
+ case TYPE_lng:
+ msg = lng_dec2_lng((lng*)res, &scale, (lng*)in);
+ break;
+ default:
+ msg = createException(SQL, op, SQLSTATE(42000)
"type combination (%s(%s)->%s) not supported", op, ATOMname(tp1),
ATOMname(tp2));
+ }
+ break;
+ }
#ifdef HAVE_HGE
+ case TYPE_hge:{
+ switch (tp1) {
case TYPE_bte:
msg = bte_dec2_hge((hge*)res, &scale, (bte*)in);
break;
@@ -1452,22 +1493,26 @@ do_analytical_sumprod(Client cntxt, MalB
msg = lng_dec2_hge((hge*)res, &scale, (lng*)in);
break;
case TYPE_hge:
- *(hge*)res = *((hge*)in);
- break;
-#else
- case TYPE_bte:
- msg = bte_dec2_lng((lng*)res, &scale, (bte*)in);
+ msg = hge_dec2_hge((hge*)res, &scale, (hge*)in);
break;
- case TYPE_sht:
- msg = sht_dec2_lng((lng*)res, &scale, (sht*)in);
+ default:
+ msg = createException(SQL, op, SQLSTATE(42000)
"type combination (%s(%s)->%s) not supported", op, ATOMname(tp1),
ATOMname(tp2));
+ }
+ break;
+ }
+#endif
+ case TYPE_flt:{
+ switch (tp1) {
+ case TYPE_flt:
+ *(flt*)res = *((flt*)in);
break;
- case TYPE_int:
- msg = int_dec2_lng((lng*)res, &scale, (int*)in);
- break;
- case TYPE_lng:
- *(lng*)res = *((lng*)in);
- break;
-#endif
+ default:
+ msg = createException(SQL, op, SQLSTATE(42000)
"type combination (%s(%s)->%s) not supported", op, ATOMname(tp1),
ATOMname(tp2));
+ }
+ break;
+ }
+ case TYPE_dbl:{
+ switch (tp1) {
case TYPE_flt: {
flt fp = *((flt*)in);
*(dbl*)res = is_flt_nil(fp) ? dbl_nil : (dbl)
fp;
@@ -1476,7 +1521,12 @@ do_analytical_sumprod(Client cntxt, MalB
*(dbl*)res = *((dbl*)in);
break;
default:
- msg = createException(SQL, op, SQLSTATE(42000)
"%s not available for %s", op, ATOMname(tp1));
+ msg = createException(SQL, op, SQLSTATE(42000)
"type combination (%s(%s)->%s) not supported", op, ATOMname(tp1),
ATOMname(tp2));
+ }
+ break;
+ }
+ default:
+ msg = createException(SQL, op, SQLSTATE(42000) "type
combination (%s(%s)->%s) not supported", op, ATOMname(tp1), ATOMname(tp2));
}
}
diff --git a/sql/test/analytics/Tests/analytics19.test
b/sql/test/analytics/Tests/analytics19.test
--- a/sql/test/analytics/Tests/analytics19.test
+++ b/sql/test/analytics/Tests/analytics19.test
@@ -540,6 +540,194 @@ 3
3
4
+query TTTT nosort
+select sum(aa) over (partition by bb),
+ sum(aa) over (partition by bb order by bb asc),
+ sum(aa) over (partition by bb order by bb desc),
+ sum(aa) over (order by bb desc) from analytics
+----
+NULL
+NULL
+NULL
+NULL
+0:00:26
+0:00:26
+0:00:26
+0:00:26
+0:00:26
+0:00:26
+0:00:26
+0:00:26
+0:00:26
+0:00:26
+0:00:26
+0:00:26
+0:00:11
+0:00:11
+0:00:11
+0:00:37
+0:00:11
+0:00:11
+0:00:11
+0:00:37
+0:00:11
+0:00:11
+0:00:11
+0:00:37
+0:00:09
+0:00:09
+0:00:09
+0:00:46
+0:00:09
+0:00:09
+0:00:09
+0:00:46
+0:00:09
+0:00:09
+0:00:09
+0:00:46
+
+query TTTT nosort
+select sum(bb) over (partition by aa),
+ sum(bb) over (partition by aa order by aa asc),
+ sum(bb) over (partition by aa order by aa desc),
+ sum(bb) over (order by aa desc) from analytics
+----
+3
+3
+3
+3
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
