Changeset: d1af2cca27c8 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=d1af2cca27c8
Modified Files:
sql/server/rel_rel.h
sql/server/rel_select.c
sql/test/analytics/Tests/analytics09.sql
sql/test/analytics/Tests/analytics09.stable.err
sql/test/analytics/Tests/analytics09.stable.out
sql/test/miscellaneous/Tests/groupby_expressions.sql
sql/test/miscellaneous/Tests/groupby_expressions.stable.err
sql/test/miscellaneous/Tests/groupby_expressions.stable.out
Branch: groupby-expressions
Log Message:
Disallow aggregation functions in group by clauses and window functions in
partition by clauses.
diffs (177 lines):
diff --git a/sql/server/rel_rel.h b/sql/server/rel_rel.h
--- a/sql/server/rel_rel.h
+++ b/sql/server/rel_rel.h
@@ -18,6 +18,7 @@
#define sql_having 8
#define sql_orderby 16
#define sql_groupby 32 //ORed
+#define sql_partitionby 64 //ORed
#define is_sql_from(X) ((X & sql_from) == sql_from)
#define is_sql_where(X) ((X & sql_where) == sql_where)
@@ -25,6 +26,7 @@
#define is_sql_having(X) ((X & sql_having) == sql_having)
#define is_sql_orderby(X) ((X & sql_orderby) == sql_orderby)
#define is_sql_groupby(X) ((X & sql_groupby) == sql_groupby)
+#define is_sql_partitionby(X) ((X & sql_partitionby) == sql_partitionby)
#define ERR_AMBIGUOUS 050000
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
@@ -3561,6 +3561,13 @@ static sql_exp *
if (uaname)
GDKfree(uaname);
return e;
+ } else if(is_sql_groupby(f)) {
+ char *uaname = GDKmalloc(strlen(aname) + 1);
+ sql_exp *e = sql_error(sql, 02, SQLSTATE(42000) "%s: aggregate
function '%s' not allowed in GROUP BY clause",
+ uaname ?
toUpperCopy(uaname, aname) : aname, aname);
+ if (uaname)
+ GDKfree(uaname);
+ return e;
}
if (is_sql_having(f) && is_select(groupby->op))
@@ -4704,9 +4711,9 @@ rel_rankop(mvc *sql, sql_rel **rel, symb
supports_frames = (window_function->token != SQL_RANK) || is_nth_value
||
(strcmp(s->base.name, "sys") == 0 &&
((strcmp(aname, "first_value") == 0) || strcmp(aname, "last_value") == 0));
- if (is_sql_where(f) || is_sql_groupby(f) || is_sql_having(f) ||
is_sql_orderby(f)) {
+ if (is_sql_where(f) || is_sql_groupby(f) || is_sql_having(f) ||
is_sql_orderby(f) || is_sql_partitionby(f)) {
char *uaname = GDKmalloc(strlen(aname) + 1);
- const char *clause =
is_sql_where(f)?"WHERE":is_sql_groupby(f)?"GROUP
BY":is_sql_having(f)?"HAVING":"ORDER BY";
+ const char *clause =
is_sql_where(f)?"WHERE":is_sql_groupby(f)?"GROUP
BY":is_sql_having(f)?"HAVING":is_sql_orderby(f)?"ORDER BY":"PARTITION BY";
(void) sql_error(sql, 02, SQLSTATE(42000) "%s: window function
'%s' not allowed in %s clause",
uaname ? toUpperCopy(uaname,
aname) : aname, aname, clause);
if (uaname)
@@ -4826,7 +4833,7 @@ rel_rankop(mvc *sql, sql_rel **rel, symb
/* Partition By */
if (partition_by_clause) {
- gbe = rel_group_by(sql, &pp, partition_by_clause, NULL /*
cannot use (selection) column references, as this result is a selection column
*/, f );
+ gbe = rel_group_by(sql, &pp, partition_by_clause, NULL /*
cannot use (selection) column references, as this result is a selection column
*/, f | sql_partitionby );
if (!gbe)
return NULL;
for(n = gbe->h ; n ; n = n->next) {
@@ -5057,7 +5064,7 @@ rel_value_exp2(mvc *sql, sql_rel **rel,
if (THRhighwater())
return sql_error(sql, 10, SQLSTATE(42000) "SELECT: too many
nested operators");
- if (*rel && (*rel)->card == CARD_AGGR) { //group by expression case,
handle it before
+ if (rel && *rel && (*rel)->card == CARD_AGGR) { //group by expression
case, handle it before
sql_exp *exp = stack_get_groupby_expression(sql, se);
if (sql->errstr[0] != '\0')
return NULL;
diff --git a/sql/test/analytics/Tests/analytics09.sql
b/sql/test/analytics/Tests/analytics09.sql
--- a/sql/test/analytics/Tests/analytics09.sql
+++ b/sql/test/analytics/Tests/analytics09.sql
@@ -53,7 +53,7 @@ select cast(sum(aa) * 100 / sum(sum(aa))
select cast(prod(sum(aa)) * count(1 + aa) / avg(null) over () as bigint) from
analytics; --error, nesting aggregation functions
-select rank() over (partition by case when aa > 5 then aa else aa + 5 end)
from analytics; --TODO we don't support expressions in partition by as well
group by statements
+select rank() over (partition by case when aa > 5 then aa else aa + 5 end)
from analytics;
select rank() over (partition by sum(aa)) from analytics; --TODO we don't
support expressions in partition by as well group by
diff --git a/sql/test/analytics/Tests/analytics09.stable.err
b/sql/test/analytics/Tests/analytics09.stable.err
--- a/sql/test/analytics/Tests/analytics09.stable.err
+++ b/sql/test/analytics/Tests/analytics09.stable.err
@@ -54,13 +54,9 @@ MAPI = (monetdb) /var/tmp/mtest-1389/.s
QUERY = select cast(prod(sum(aa)) * count(1 + aa) / avg(null) over () as
bigint) from analytics; --error, nesting aggregation functions
ERROR = !SELECT: subquery result missing
CODE = 42000
-MAPI = (monetdb) /var/tmp/mtest-1389/.s.monetdb.37694
-QUERY = select rank() over (partition by case when aa > 5 then aa else aa + 5
end) from analytics; --TODO we don't support expressions in partition by as
well group by statements
-ERROR = !syntax error, unexpected CASE in: "select rank() over (partition by
case"
-CODE = 42000
-MAPI = (monetdb) /var/tmp/mtest-1389/.s.monetdb.37694
+MAPI = (monetdb) /var/tmp/mtest-11873/.s.monetdb.31832
QUERY = select rank() over (partition by sum(aa)) from analytics; --TODO we
don't support expressions in partition by as well group by
-ERROR = !syntax error, unexpected '(', expecting ')' in: "select rank() over
(partition by sum("
+ERROR = !Cannot use non GROUP BY column 'aa' in query results without an
aggregate function
CODE = 42000
# 13:13:17 >
diff --git a/sql/test/analytics/Tests/analytics09.stable.out
b/sql/test/analytics/Tests/analytics09.stable.out
--- a/sql/test/analytics/Tests/analytics09.stable.out
+++ b/sql/test/analytics/Tests/analytics09.stable.out
@@ -175,6 +175,21 @@ Ready.
[ 100 ]
[ 100 ]
[ NULL ]
+#select rank() over (partition by case when aa > 5 then aa else aa + 5 end)
from analytics;
+% sys.L4 # table_name
+% L4 # name
+% int # type
+% 1 # length
+[ 1 ]
+[ 1 ]
+[ 1 ]
+[ 1 ]
+[ 1 ]
+[ 1 ]
+[ 1 ]
+[ 1 ]
+[ 1 ]
+[ 1 ]
#drop table analytics;
# 13:13:17 >
diff --git a/sql/test/miscellaneous/Tests/groupby_expressions.sql
b/sql/test/miscellaneous/Tests/groupby_expressions.sql
--- a/sql/test/miscellaneous/Tests/groupby_expressions.sql
+++ b/sql/test/miscellaneous/Tests/groupby_expressions.sql
@@ -21,9 +21,13 @@ select case when "aa" > 1 then "aa" else
select cast(sum("aa"+"bb") as bigint) from "groupings" group by "aa"+"bb";
select cast(sum("aa"+3452) as bigint) from "groupings" group by "aa"+"bb";
+
+select count(*) from "groupings" having count("aa"-54) > 2;
+select count(*) from "groupings" order by count("bb"+1);
rollback;
select "aa"+3452 from "groupings" group by "aa"+"bb"; --error
+select count(*) from "groupings" group by count("aa"); --error
select count(*) from "groupings" group by rank() over (); --error
select count(*) from "groupings" having rank() over (); --error
select count(*) from "groupings" order by rank() over (); --error TODO?
diff --git a/sql/test/miscellaneous/Tests/groupby_expressions.stable.err
b/sql/test/miscellaneous/Tests/groupby_expressions.stable.err
--- a/sql/test/miscellaneous/Tests/groupby_expressions.stable.err
+++ b/sql/test/miscellaneous/Tests/groupby_expressions.stable.err
@@ -34,7 +34,11 @@ MAPI = (monetdb) /var/tmp/mtest-28161/.
QUERY = select "aa"+3452 from "groupings" group by "aa"+"bb"; --error
ERROR = !SELECT: no such aggregate 'sql_add'
CODE = 42000
-MAPI = (monetdb) /var/tmp/mtest-28161/.s.monetdb.36698
+MAPI = (monetdb) /var/tmp/mtest-11615/.s.monetdb.30717
+QUERY = select count(*) from "groupings" group by count("aa"); --error
+ERROR = !COUNT: aggregate function 'count' not allowed in GROUP BY clause
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-11615/.s.monetdb.30717
QUERY = select count(*) from "groupings" group by rank() over (); --error
ERROR = !RANK: window function 'rank' not allowed in GROUP BY clause
CODE = 42000
diff --git a/sql/test/miscellaneous/Tests/groupby_expressions.stable.out
b/sql/test/miscellaneous/Tests/groupby_expressions.stable.out
--- a/sql/test/miscellaneous/Tests/groupby_expressions.stable.out
+++ b/sql/test/miscellaneous/Tests/groupby_expressions.stable.out
@@ -176,6 +176,18 @@ Ready.
% 4 # length
[ 3453 ]
[ 6907 ]
+#select count(*) from "groupings" having count("aa"-54) > 2;
+% .L2 # table_name
+% L2 # name
+% bigint # type
+% 1 # length
+[ 3 ]
+#select count(*) from "groupings" order by count("bb"+1);
+% sys.L3 # table_name
+% L3 # name
+% bigint # type
+% 1 # length
+[ 3 ]
#rollback;
# 14:21:56 >
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
