Changeset: f8f6fb426ed6 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=f8f6fb426ed6
Modified Files:
gdk/gdk_analytic.c
gdk/gdk_analytic.h
sql/backends/monet5/sql_rank.c
sql/server/rel_select.c
sql/test/analytics/Tests/analytics00.sql
sql/test/analytics/Tests/analytics00.stable.out
Branch: analytics
Log Message:
When applying an aggregation function on window with no specification: over (),
calculate the aggregate over the entire column.
Also fixed the query: select count(*) over ()
diffs (truncated from 458 to 300 lines):
diff --git a/gdk/gdk_analytic.c b/gdk/gdk_analytic.c
--- a/gdk/gdk_analytic.c
+++ b/gdk/gdk_analytic.c
@@ -957,7 +957,7 @@ finish:
has_nils = true; \
for (;rb < rp; rb++) \
*rb = curval; \
- } else if (o) { /* single value, ie no partitions */ \
+ } else if (o || force_order) { \
for(; rp<end; rp++, bp++) { \
if(!is_##TPE##_nil(*bp)) { \
if(is_##TPE##_nil(curval))
\
@@ -990,7 +990,7 @@ finish:
#define ANALYTICAL_LIMIT(OP, IMP, SIGN_OP)
\
gdk_return
\
-GDKanalytical##OP(BAT *r, BAT *b, BAT *p, BAT *o, int tpe)
\
+GDKanalytical##OP(BAT *r, BAT *b, BAT *p, BAT *o, bit force_order, int tpe)
\
{
\
int (*atomcmp)(const void *, const void *);
\
const void* restrict nil;
\
@@ -1053,7 +1053,7 @@ GDKanalytical##OP(BAT *r, BAT *b, BAT *p
if ((gdk_res = BUNappend(r, curval,
false)) != GDK_SUCCEED) \
goto finish;
\
}
\
- } else if (o) { /* single value, ie no partitions */
\
+ } else if (o || force_order) {
\
for(i=0; i<cnt; i++) {
\
void *next = BUNtail(bpi, i);
\
if((*atomcmp)(next, nil) != 0)
{ \
@@ -1316,7 +1316,7 @@ GDKanalyticalcount(BAT *r, BAT *b, BAT *
has_nils = true;
\
for (;rb < rp; rb++) \
*rb = curval;
\
- } else if (o) { /* single value, ie no partitions */ \
+ } else if (o || force_order) { \
for(; rp<end; rp++, bp++) { \
if(!is_##TPE1##_nil(*bp)) {
\
if(is_##TPE2##_nil(curval))
\
@@ -1379,7 +1379,7 @@ GDKanalyticalcount(BAT *r, BAT *b, BAT *
has_nils = true;
\
for (;rb < rp; rb++)
\
*rb = curval;
\
- } else if (o) { /* single value, ie no partitions */ \
+ } else if (o || force_order) { \
TPE2 *end = rb + cnt;
\
if(dofsum(bp, 0, 0, cnt, rb, 1, TYPE_##TPE1,
TYPE_##TPE2, \
NULL, NULL, NULL, 0, 0, true, false,
true, \
@@ -1406,7 +1406,7 @@ GDKanalyticalcount(BAT *r, BAT *b, BAT *
} while(0);
gdk_return
-GDKanalyticalsum(BAT *r, BAT *b, BAT *p, BAT *o, int tp1, int tp2)
+GDKanalyticalsum(BAT *r, BAT *b, BAT *p, BAT *o, bit force_order, int tp1, int
tp2)
{
bool has_nils = false;
BUN i, j, cnt = BATcount(b), nils = 0;
@@ -1572,7 +1572,7 @@ finish:
has_nils = true;
\
for (;rb < rp; rb++) \
*rb = curval;
\
- } else if (o) { /* single value, ie no partitions */ \
+ } else if (o || force_order) { \
for(; rp<end; rp++, bp++) { \
if(!is_##TPE1##_nil(*bp)) {
\
if(is_##TPE2##_nil(curval))
\
@@ -1631,7 +1631,7 @@ finish:
has_nils = true; \
for (;rb < rp; rb++) \
*rb = curval; \
- } else if (o) { /* single value, ie no partitions */ \
+ } else if (o || force_order) { \
for(; rp<end; rp++, bp++) { \
if(!is_##TPE1##_nil(*bp)) { \
if(is_##TPE2##_nil(curval))
\
@@ -1699,7 +1699,7 @@ finish:
has_nils = true;
\
for (;rb < rp; rb++) \
*rb = curval;
\
- } else if (o) { /* single value, ie no partitions */ \
+ } else if (o || force_order) { \
for(; rp<end; rp++, bp++) { \
if(!is_##TPE1##_nil(*bp)) {
\
if(is_##TPE2##_nil(curval))
\
@@ -1726,7 +1726,7 @@ finish:
} while(0);
gdk_return
-GDKanalyticalprod(BAT *r, BAT *b, BAT *p, BAT *o, int tp1, int tp2)
+GDKanalyticalprod(BAT *r, BAT *b, BAT *p, BAT *o, bit force_order, int tp1,
int tp2)
{
bool has_nils = false;
BUN cnt = BATcount(b), nils = 0;
@@ -1940,7 +1940,7 @@ avg_overflow##TPE:
calc_done##TPE: \
for (;rb < rp; rb++)
\
*rb = curval;
\
- } else if (o) { /* single value, ie no partitions */ \
+ } else if (o || force_order) { \
for(; rp<end; rp++, bp++) {
\
if (!is_##TPE##_nil(*bp)) {
\
ADD_WITH_CHECK(TPE, *bp, lng_hge, sum,
lng_hge, \
@@ -2021,7 +2021,7 @@ single_calc_done##TPE:
has_nils = has_nils || (n == 0); \
for (;rb < rp; rb++) \
*rb = curval; \
- } else if (o) { /* single value, ie no partitions */ \
+ } else if (o || force_order) { \
for(; rp<end; rp++, bp++) { \
if (!is_##TPE##_nil(*bp)) \
AVERAGE_ITER_FLOAT(TPE, *bp, a, n);
\
@@ -2044,7 +2044,7 @@ single_calc_done##TPE:
} while(0);
gdk_return
-GDKanalyticalavg(BAT *r, BAT *b, BAT *p, BAT *o, int tpe)
+GDKanalyticalavg(BAT *r, BAT *b, BAT *p, BAT *o, bit force_order, int tpe)
{
bool has_nils = false;
BUN cnt = BATcount(b), nils = 0, n = 0;
diff --git a/gdk/gdk_analytic.h b/gdk/gdk_analytic.h
--- a/gdk/gdk_analytic.h
+++ b/gdk/gdk_analytic.h
@@ -23,11 +23,11 @@ gdk_export gdk_return GDKanalyticallast(
gdk_export gdk_return GDKanalyticalnthvalue(BAT *r, BAT *b, BAT *p, BAT *o,
BUN nth, int tpe);
gdk_export gdk_return GDKanalyticallag(BAT *r, BAT *b, BAT *p, BAT *o, BUN
lag, const void* restrict default_value, int tpe);
gdk_export gdk_return GDKanalyticallead(BAT *r, BAT *b, BAT *p, BAT *o, BUN
lead, const void* restrict default_value, int tpe);
-gdk_export gdk_return GDKanalyticalmin(BAT *r, BAT *b, BAT *p, BAT *o, int
tpe);
-gdk_export gdk_return GDKanalyticalmax(BAT *r, BAT *b, BAT *p, BAT *o, int
tpe);
+gdk_export gdk_return GDKanalyticalmin(BAT *r, BAT *b, BAT *p, BAT *o, bit
force_order, int tpe);
+gdk_export gdk_return GDKanalyticalmax(BAT *r, BAT *b, BAT *p, BAT *o, bit
force_order, int tpe);
gdk_export gdk_return GDKanalyticalcount(BAT *r, BAT *b, BAT *p, BAT *o, const
bit* restrict ignore_nils, int tpe);
-gdk_export gdk_return GDKanalyticalsum(BAT *r, BAT *b, BAT *p, BAT *o, int
tp1, int tp2);
-gdk_export gdk_return GDKanalyticalprod(BAT *r, BAT *b, BAT *p, BAT *o, int
tp1, int tp2);
-gdk_export gdk_return GDKanalyticalavg(BAT *r, BAT *b, BAT *p, BAT *o, int
tpe);
+gdk_export gdk_return GDKanalyticalsum(BAT *r, BAT *b, BAT *p, BAT *o, bit
force_order, int tp1, int tp2);
+gdk_export gdk_return GDKanalyticalprod(BAT *r, BAT *b, BAT *p, BAT *o, bit
force_order, int tp1, int tp2);
+gdk_export gdk_return GDKanalyticalavg(BAT *r, BAT *b, BAT *p, BAT *o, bit
force_order, int tpe);
#endif //_GDK_ANALYTIC_H_
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
@@ -571,8 +571,8 @@ SQLntile(Client cntxt, MalBlkPtr mb, Mal
#undef NTILE_VALUE_SINGLE_IMP
static str
-SQLanalytics_args(BAT **r, BAT **b, BAT **p, BAT **o, Client cntxt, MalBlkPtr
mb, MalStkPtr stk, InstrPtr pci,
- int rtype, const str mod, const str err)
+SQLanalytics_args(BAT **r, BAT **b, BAT **p, BAT **o, bit *force_order, Client
cntxt, MalBlkPtr mb, MalStkPtr stk,
+ InstrPtr pci, int rtype, const str mod, const
str err)
{
*r = *b = *p = *o = NULL;
@@ -609,6 +609,8 @@ SQLanalytics_args(BAT **r, BAT **b, BAT
if (*p) BBPunfix((*p)->batCacheid);
throw(SQL, mod, SQLSTATE(HY005) "Cannot access column
descriptor");
}
+ } else {
+ *force_order = *getArgReference_bit(stk, pci, 3);
}
return MAL_SUCCEED;
}
@@ -942,10 +944,11 @@ SQLlead(Client cntxt, MalBlkPtr mb, MalS
/* we will keep the ordering bat here although is not needed, but maybe later
with varied sized windows */
static str
SQLanalytical_func(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr pci,
const str op, const str err,
- gdk_return (*func)(BAT *, BAT *, BAT *, BAT
*, int))
+ gdk_return (*func)(BAT *, BAT *, BAT *, BAT
*, bit, int))
{
BAT *r, *b, *p, *o;
- str msg = SQLanalytics_args(&r, &b, &p, &o, cntxt, mb, stk, pci, 0, op,
err);
+ bit force_order = 0;
+ str msg = SQLanalytics_args(&r, &b, &p, &o, &force_order, cntxt, mb,
stk, pci, 0, op, err);
int tpe = getArgType(mb, pci, 1);
int unit = *getArgReference_int(stk, pci, 4);
int start = *getArgReference_int(stk, pci, 5);
@@ -966,7 +969,7 @@ SQLanalytical_func(Client cntxt, MalBlkP
if (b) {
bat *res = getArgReference_bat(stk, pci, 0);
- gdk_res = func(r, b, p, o, tpe);
+ gdk_res = func(r, b, p, o, force_order, tpe);
BBPunfix(b->batCacheid);
if (p) BBPunfix(p->batCacheid);
if (o) BBPunfix(o->batCacheid);
@@ -1075,10 +1078,11 @@ SQLcount(Client cntxt, MalBlkPtr mb, Mal
static str
do_analytical_sumprod(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr pci,
- gdk_return (*func)(BAT *, BAT *, BAT
*, BAT *, int, int), const str op, const str err)
+ gdk_return (*func)(BAT *, BAT *, BAT
*, BAT *, bit, int, int), const str op, const str err)
{
BAT *r = NULL, *b = NULL, *p = NULL, *o = NULL;
int tp1, tp2, unit, start, end, excl;
+ bit force_order = 0;
gdk_return gdk_res;
str msg = MAL_SUCCEED;
@@ -1094,9 +1098,8 @@ do_analytical_sumprod(Client cntxt, MalB
end = *getArgReference_int(stk, pci, 6);
excl = *getArgReference_int(stk, pci, 7);
- if (isaBatType(tp1))
+ if (isaBatType(tp1)) {
tp1 = getBatType(tp1);
- if (isaBatType(getArgType(mb, pci, 1))) {
b = BATdescriptor(*getArgReference_bat(stk, pci, 1));
if (!b)
throw(SQL, op, SQLSTATE(HY005) "Cannot access column
descriptor");
@@ -1125,8 +1128,7 @@ do_analytical_sumprod(Client cntxt, MalB
}
}
if (b) {
- BUN cnt = BATcount(b);
- voidresultBAT(r, tp2, cnt, b, op);
+ voidresultBAT(r, tp2, BATcount(b), b, op);
}
if (isaBatType(getArgType(mb, pci, 2))) {
p = BATdescriptor(*getArgReference_bat(stk, pci, 2));
@@ -1144,6 +1146,8 @@ do_analytical_sumprod(Client cntxt, MalB
if (p) BBPunfix(p->batCacheid);
throw(SQL, op, SQLSTATE(HY005) "Cannot access column
descriptor");
}
+ } else {
+ force_order = *getArgReference_bit(stk, pci, 3);
}
if (unit != 0 || excl != 0)
@@ -1154,7 +1158,7 @@ do_analytical_sumprod(Client cntxt, MalB
if (b) {
bat *res = getArgReference_bat(stk, pci, 0);
- gdk_res = func(r, b, p, o, tp1, tp2);
+ gdk_res = func(r, b, p, o, force_order, tp1, tp2);
BBPunfix(b->batCacheid);
if (p) BBPunfix(p->batCacheid);
if (o) BBPunfix(o->batCacheid);
@@ -1232,7 +1236,8 @@ str
SQLavg(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr pci)
{
BAT *r, *b, *p, *o;
- str msg = SQLanalytics_args(&r, &b, &p, &o, cntxt, mb, stk, pci,
TYPE_dbl, "sql.avg",
+ bit force_order = 0;
+ str msg = SQLanalytics_args(&r, &b, &p, &o, &force_order, cntxt, mb,
stk, pci, TYPE_dbl, "sql.avg",
SQLSTATE(42000)
"avg(:any_1,:bit,:bit)");
int tpe = getArgType(mb, pci, 1);
int unit = *getArgReference_int(stk, pci, 4);
@@ -1254,7 +1259,7 @@ SQLavg(Client cntxt, MalBlkPtr mb, MalSt
if (b) {
bat *res = getArgReference_bat(stk, pci, 0);
- gdk_res = GDKanalyticalavg(r, b, p, o, tpe);
+ gdk_res = GDKanalyticalavg(r, b, p, o, force_order, tpe);
BBPunfix(b->batCacheid);
if (p) BBPunfix(p->batCacheid);
if (o) BBPunfix(o->batCacheid);
diff --git a/sql/server/rel_select.c b/sql/server/rel_select.c
--- a/sql/server/rel_select.c
+++ b/sql/server/rel_select.c
@@ -4577,10 +4577,15 @@ rel_rankop(mvc *sql, sql_rel **rel, symb
if (n) {
if (!n->next->data.sym) { /* count(*) */
- e = p->exps->h->data;
- e = exp_column(sql->sa, exp_relname(e),
exp_name(e), exp_subtype(e), exp_card(e), has_nil(e), is_intern(e));
+ if(!p->exps->h) { //no from clause, use a
constant as the expression to project
+ e = exp_atom_lng(sql->sa, 0);
+ append(p->exps, e);
+ } else {
+ e = p->exps->h->data;
+ e = exp_column(sql->sa, exp_relname(e),
exp_name(e), exp_subtype(e), exp_card(e), has_nil(e), is_intern(e));
+ }
append(fargs, e);
- append(fargs, exp_atom_bool(sql->sa, 0));
+ append(fargs, exp_atom_bool(sql->sa, 0));
//don't ignore nills
} else {
is_last = 0;
exp_kind ek1 = {type_value, card_column, FALSE};
@@ -4588,10 +4593,11 @@ rel_rankop(mvc *sql, sql_rel **rel, symb
distinct = n->data.i_val;
/*
* all aggregations implemented in a window
have 1 and only 1 argument only, so for now no further
+ * checking is needed
*/
append(fargs, rel_value_exp2(sql, &p,
n->next->data.sym, f, ek1, &is_last));
if(strcmp(s->base.name, "sys") == 0 &&
strcmp(aname, "count") == 0)
- append(fargs, exp_atom_bool(sql->sa,
1));
+ append(fargs, exp_atom_bool(sql->sa,
1)); //ignore nills
}
}
}
@@ -4640,6 +4646,9 @@ rel_rankop(mvc *sql, sql_rel **rel, symb
append(args, e);
oe = exp_op(sql->sa, args, df);
}
+ } else if(aggr && !gbe && !obe) {
+ /* when there is no partition and no order by we calculate the
aggregate over the entire column */
+ oe = exp_atom_bool(sql->sa, 1);
} else {
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
