Changeset: 538ab4144c72 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=538ab4144c72
Modified Files:
sql/server/rel_select.c
sql/test/analytics/Tests/analytics01.sql
sql/test/analytics/Tests/analytics01.stable.err
sql/test/analytics/Tests/analytics01.stable.out
Branch: analytics
Log Message:
Fix for lag and lead analytical functions calls where the 3rd argument must
have the same type as the 1st argument.
diffs (114 lines):
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
@@ -4726,15 +4726,19 @@ rel_rankop(mvc *sql, sql_rel **rel, symb
fargs = sa_list(sql->sa);
if (window_function->token == SQL_RANK) { //rank function call
dlist* dnn = window_function->data.lval->h->next->data.lval;
- bool is_ntile = (strcmp(s->base.name, "sys") == 0 &&
strcmp(aname, "ntile") == 0);
-
- if(!dnn || is_ntile) {
+ bool is_ntile = (strcmp(s->base.name, "sys") == 0 &&
strcmp(aname, "ntile") == 0),
+ is_lag = (strcmp(s->base.name, "sys") == 0 &&
strcmp(aname, "lag") == 0),
+ is_lead = (strcmp(s->base.name, "sys") == 0 &&
strcmp(aname, "lead") == 0);
+ int nfargs = 0;
+
+ if(!dnn || is_ntile) { //pass an input column for analytic
functions that don't require it
sql_rel *lr = p->l;
in = lr->exps->h->data;
in = exp_column(sql->sa, exp_relname(in), exp_name(in),
exp_subtype(in), exp_card(in), has_nil(in), is_intern(in));
if(!in)
return NULL;
append(fargs, in);
+ nfargs++;
}
if(dnn) {
for(dnode *nn = dnn->h ; nn ; nn = nn->next) {
@@ -4743,22 +4747,26 @@ rel_rankop(mvc *sql, sql_rel **rel, symb
in = rel_value_exp2(sql, &p, nn->data.sym, f,
ek, &is_last);
if(!in)
return NULL;
-
- if(is_ntile) { /* ntile only has one argument
and in null case this cast should be done */
+ if(is_ntile && nfargs == 1) { //ntile first
argument null handling case
sql_subtype *empty =
sql_bind_localtype("void");
if(subtype_cmp(&(in->tpe), empty) == 0)
{
sql_subtype *to =
sql_bind_localtype("bte");
in = exp_convert(sql->sa, in,
empty, to);
}
- } else if(is_nth_value && dnn->h && nn ==
dnn->h->next) { /* corner case for nth_value */
+ } else if(is_nth_value && nfargs == 1) {
//nth_value second argument null handling case
sql_subtype *empty =
sql_bind_localtype("void");
if(subtype_cmp(&(in->tpe), empty) == 0)
{
sql_rel *lr = p->l;
sql_exp *ep = lr->exps->h->data;
in = exp_convert(sql->sa, in,
empty, &(ep->tpe));
}
+ } else if((is_lag || is_lead) && nfargs == 2) {
//lag and lead 3rd arg must have same type as 1st arg
+ sql_exp *first = (sql_exp*)
fargs->h->data;
+ if(!(in = rel_check_type(sql,
&first->tpe, in, type_equal)))
+ return NULL;
}
append(fargs, in);
+ nfargs++;
}
}
} else { //aggregation function call
diff --git a/sql/test/analytics/Tests/analytics01.sql
b/sql/test/analytics/Tests/analytics01.sql
--- a/sql/test/analytics/Tests/analytics01.sql
+++ b/sql/test/analytics/Tests/analytics01.sql
@@ -202,6 +202,8 @@ select lead(bb) over (partition by aa or
select lead(bb) over (order by aa) from stressme;
select lead(bb) over (order by aa desc) from stressme;
+select aa, bb, lead(aa, 2, 100) over (partition by bb), lead(aa, 1, '100')
over (partition by bb) from analytics;
+
select nth_value(aa, aa) over () from analytics;
select nth_value(1, aa) over () from analytics;
@@ -212,5 +214,6 @@ select lag(null, aa) over () from analyt
select lag(null, null, aa) over () from analytics; --error
select lead(null, aa) over () from analytics; --error
select lead(null, null, aa) over () from analytics; --error
+select lead(aa, 34, 1000000000000) over (partition by bb) from analytics;
--error
drop table analytics;
diff --git a/sql/test/analytics/Tests/analytics01.stable.err
b/sql/test/analytics/Tests/analytics01.stable.err
--- a/sql/test/analytics/Tests/analytics01.stable.err
+++ b/sql/test/analytics/Tests/analytics01.stable.err
@@ -48,6 +48,10 @@ MAPI = (monetdb) /var/tmp/mtest-1980/.s
QUERY = select lead(null, null, aa) over () from analytics; --error
ERROR = !types int(32,0) and any(0,0) are not equal for column 'aa'
CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-21352/.s.monetdb.32874
+QUERY = select lead(aa, 34, 1000000000000) over (partition by bb) from
analytics; --error
+ERROR = !overflow in conversion of 1000000000000 to int.
+CODE = 22003
# 16:22:53 >
# 16:22:53 > "Done."
diff --git a/sql/test/analytics/Tests/analytics01.stable.out
b/sql/test/analytics/Tests/analytics01.stable.out
--- a/sql/test/analytics/Tests/analytics01.stable.out
+++ b/sql/test/analytics/Tests/analytics01.stable.out
@@ -2461,6 +2461,21 @@ Ready.
[ 2 ]
[ 4 ]
[ NULL ]
+#select aa, bb, lead(aa, 2, 100) over (partition by bb), lead(aa, 1, '100')
over (partition by bb) from analytics;
+% sys.analytics, sys.analytics, sys.L6, sys.L12 # table_name
+% aa, bb, L6, L12 # name
+% int, int, int, int # type
+% 2, 1, 3, 3 # length
+[ 3, 1, 4, 2 ]
+[ 2, 1, 100, 4 ]
+[ 4, 1, 100, 100 ]
+[ NULL, 2, 8, 3 ]
+[ 3, 2, 100, 8 ]
+[ 8, 2, 100, 100 ]
+[ 15, 3, 6, 5 ]
+[ 5, 3, 100, 6 ]
+[ 6, 3, 100, 100 ]
+[ NULL, 4, 100, 100 ]
#select nth_value(aa, aa) over () from analytics;
% sys.L4 # table_name
% L4 # name
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
