Changeset: 22f1a37ba08a for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=22f1a37ba08a
Added Files:
sql/test/miscellaneous/Tests/groupby_expressions.sql
sql/test/miscellaneous/Tests/groupby_expressions.stable.err
sql/test/miscellaneous/Tests/groupby_expressions.stable.out
Modified Files:
clients/mapiclient/mhelp.c
sql/backends/monet5/Tests/rapi05.stable.out
sql/backends/monet5/rel_bin.c
sql/backends/monet5/sql_scenario.c
sql/server/rel_rel.c
sql/server/rel_rel.h
sql/server/rel_schema.c
sql/server/rel_select.c
sql/server/rel_updates.c
sql/server/sql_mvc.c
sql/server/sql_mvc.h
sql/server/sql_parser.y
sql/server/sql_qc.c
sql/server/sql_qc.h
sql/server/sql_semantic.c
sql/server/sql_symbol.c
sql/server/sql_symbol.h
sql/server/sql_tokens.h
sql/test/BugTracker-2016/Tests/groupby_on_column_expression.Bug-3832.stable.err
sql/test/BugTracker-2016/Tests/groupby_on_column_expression.Bug-3832.stable.out
sql/test/BugTracker-2017/Tests/sqlitelogictest-aggregation-distinct-coalesce.Bug-6431.stable.err
sql/test/analytics/Tests/analytics09.sql
sql/test/analytics/Tests/analytics09.stable.err
sql/test/analytics/Tests/analytics09.stable.out
sql/test/miscellaneous/Tests/All
Branch: default
Log Message:
Merged groupby-expressions branch into default.
diffs (truncated from 2067 to 300 lines):
diff --git a/clients/mapiclient/mhelp.c b/clients/mapiclient/mhelp.c
--- a/clients/mapiclient/mhelp.c
+++ b/clients/mapiclient/mhelp.c
@@ -405,7 +405,7 @@ SQLhelp sqlhelp[] = {
"[ FROM from_item [',' ...] ]\n"
"[ WINDOW window_definition [',' ...] ]\n"
"[ WHERE condition ]\n"
- "[ GROUP BY grouping_element ',', ...] ]\n"
+ "[ GROUP BY expression ',', ...] ]\n"
"[ HAVING condition [',' ...] ]\n"
"[ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] [ CORRESPONDING ]
select ]\n"
"[ ORDER BY expression [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [','
...] ]\n"
diff --git a/sql/backends/monet5/Tests/rapi05.stable.out
b/sql/backends/monet5/Tests/rapi05.stable.out
--- a/sql/backends/monet5/Tests/rapi05.stable.out
+++ b/sql/backends/monet5/Tests/rapi05.stable.out
@@ -32,7 +32,7 @@ Ready.
#language R {kmeans(arg1,arg2)$cluster};
#SELECT cluster FROM (SELECT MIN(x) AS minx, MAX(x) AS maxx, kmeans(x,5) AS
cluster FROM xdata
# GROUP BY cluster) as cdata ORDER BY cluster;
-% sys.cdata # table_name
+% sys.L12 # table_name
% cluster # name
% int # type
% 1 # length
diff --git a/sql/backends/monet5/rel_bin.c b/sql/backends/monet5/rel_bin.c
--- a/sql/backends/monet5/rel_bin.c
+++ b/sql/backends/monet5/rel_bin.c
@@ -1190,7 +1190,7 @@ rel_parse_value(backend *be, char *query
/* get out the single value as we don't want an enclosing projection! */
if (m->sym->token == SQL_SELECT) {
SelectNode *sn = (SelectNode *)m->sym;
- if (sn->selection->h->data.sym->token == SQL_COLUMN) {
+ if (sn->selection->h->data.sym->token == SQL_COLUMN ||
sn->selection->h->data.sym->token == SQL_IDENT) {
int is_last = 0;
sql_rel *rel = NULL;
sql_exp *e = rel_value_exp2(m, &rel,
sn->selection->h->data.sym->data.lval->h->data.sym, sql_sel, ek, &is_last);
diff --git a/sql/backends/monet5/sql_scenario.c
b/sql/backends/monet5/sql_scenario.c
--- a/sql/backends/monet5/sql_scenario.c
+++ b/sql/backends/monet5/sql_scenario.c
@@ -1190,7 +1190,7 @@ SQLparser(Client c)
goto finalize;
}
scanner_query_processed(&(m->scanner));
- } else if (caching(m) && cachable(m, NULL) && m->emode != m_prepare &&
(be->q = qc_match(m->qc, m->sym, m->args, m->argc, m->scanner.key ^
m->session->schema->base.id)) != NULL) {
+ } else if (caching(m) && cachable(m, NULL) && m->emode != m_prepare &&
(be->q = qc_match(m->qc, m, m->sym, m->args, m->argc, m->scanner.key ^
m->session->schema->base.id)) != NULL) {
/* query template was found in the query cache */
scanner_query_processed(&(m->scanner));
m->no_mitosis = be->q->no_mitosis;
diff --git a/sql/server/rel_rel.c b/sql/server/rel_rel.c
--- a/sql/server/rel_rel.c
+++ b/sql/server/rel_rel.c
@@ -265,7 +265,7 @@ rel_bind_column( mvc *sql, sql_rel *rel,
{
sql_rel *p = NULL;
- if (f == sql_sel && rel && is_project(rel->op) && !is_processed(rel))
+ if (is_sql_sel(f) && rel && is_project(rel->op) && !is_processed(rel))
rel = rel->l;
if (!rel || (rel = rel_bind_column_(sql, &p, rel, cname)) == NULL)
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
@@ -12,11 +12,21 @@
#include "sql_relation.h"
#include "sql_mvc.h"
-#define sql_from 0
-#define sql_where 1
-#define sql_sel 2
-#define sql_having 3
-#define sql_orderby 4
+#define sql_from 1
+#define sql_where 2
+#define sql_sel 4
+#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)
+#define is_sql_sel(X) ((X & sql_sel) == sql_sel)
+#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_schema.c b/sql/server/rel_schema.c
--- a/sql/server/rel_schema.c
+++ b/sql/server/rel_schema.c
@@ -430,7 +430,7 @@ column_option(
symbol *sym = s->data.sym;
char *err = NULL, *r;
- if (sym->token == SQL_COLUMN) {
+ if (sym->token == SQL_COLUMN || sym->token == SQL_IDENT) {
sql_exp *e = rel_logical_value_exp(sql, NULL, sym,
sql_sel);
if (e && is_atom(e->type)) {
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
@@ -1054,7 +1054,7 @@ rel_column_ref(mvc *sql, sql_rel **rel,
sql_exp *exp = NULL;
dlist *l = NULL;
- assert(column_r->token == SQL_COLUMN && column_r->type == type_list);
+ assert((column_r->token == SQL_COLUMN || column_r->token == SQL_IDENT)
&& column_r->type == type_list);
l = column_r->data.lval;
if (dlist_length(l) == 1 && l->h->type == type_int) {
@@ -1102,7 +1102,7 @@ rel_column_ref(mvc *sql, sql_rel **rel,
return rel_var_ref(sql, name, 0);
}
if (!exp && !var) {
- if (rel && *rel && (*rel)->card <= CARD_AGGR && f ==
sql_sel) {
+ if (rel && *rel && (*rel)->card <= CARD_AGGR &&
is_sql_sel(f)) {
sql_rel *gb = *rel;
while(gb->l && !is_groupby(gb->op))
@@ -1110,7 +1110,7 @@ rel_column_ref(mvc *sql, sql_rel **rel,
if (gb && gb->l && rel_bind_column(sql, gb->l,
name, f))
return sql_error(sql, 02,
SQLSTATE(42000) "SELECT: cannot use non GROUP BY column '%s' in query results
without an aggregate function", name);
}
- if (f == sql_having)
+ if (is_sql_having(f))
return sql_error(sql, 02, SQLSTATE(42000)
"SELECT: cannot use non GROUP BY column '%s' in query results without an
aggregate function", name);
return sql_error(sql, 02, SQLSTATE(42000) "SELECT:
identifier '%s' unknown", name);
}
@@ -1136,7 +1136,7 @@ rel_column_ref(mvc *sql, sql_rel **rel,
}
}
if (!exp) {
- if (rel && *rel && (*rel)->card == CARD_AGGR && f ==
sql_sel) {
+ if (rel && *rel && (*rel)->card == CARD_AGGR &&
is_sql_sel(f)) {
sql_rel *gb = *rel;
while(gb->l && !is_groupby(gb->op) &&
is_project(gb->op))
@@ -1144,7 +1144,7 @@ rel_column_ref(mvc *sql, sql_rel **rel,
if (gb && is_groupby(gb->op) && gb->l &&
rel_bind_column2(sql, gb->l, tname, cname, f))
return sql_error(sql, 02,
SQLSTATE(42000) "SELECT: cannot use non GROUP BY column '%s.%s' in query
results without an aggregate function", tname, cname);
}
- if (f == sql_having)
+ if (is_sql_having(f))
return sql_error(sql, 02, SQLSTATE(42S22)
"SELECT: cannot use non GROUP BY column '%s.%s' in query results without an
aggregate function", tname, cname);
return sql_error(sql, 02, SQLSTATE(42S22) "SELECT: no
such column '%s.%s'", tname, cname);
}
@@ -1783,14 +1783,14 @@ rel_compare(mvc *sql, sql_rel *rel, symb
/* reset error */
sql->session->status = 0;
sql->errstr[0] = 0;
- r = rel_subquery(sql, rel, ro, ek, f ==
sql_sel?APPLY_LOJ:APPLY_JOIN);
+ r = rel_subquery(sql, rel, ro, ek,
is_sql_sel(f)?APPLY_LOJ:APPLY_JOIN);
/* get inner queries result value, ie
get last expression of r */
if (r) {
rs = rel_lastexp(sql, r);
- if (f == sql_sel && r->card > CARD_ATOM &&
quantifier != 1) {
+ if (is_sql_sel(f) && r->card > CARD_ATOM &&
quantifier != 1) {
sql_subaggr *zero_or_one =
sql_bind_aggr(sql->sa, sql->session->schema, compare_aggr_op(compare_op,
quantifier), exp_subtype(rs));
rs = exp_aggr1(sql->sa, rs,
zero_or_one, 0, 0, CARD_ATOM, 0);
@@ -2017,7 +2017,7 @@ rel_logical_value_exp(mvc *sql, sql_rel
sql->session->status = 0;
sql->errstr[0] = 0;
if (!r)
- r = rel_subquery(sql, *rel, ro, ek, f
== sql_sel?APPLY_LOJ:APPLY_JOIN);
+ r = rel_subquery(sql, *rel, ro, ek,
is_sql_sel(f)?APPLY_LOJ:APPLY_JOIN);
/* get inner queries result value, ie
get last expression of r */
@@ -2025,7 +2025,7 @@ rel_logical_value_exp(mvc *sql, sql_rel
rs = rel_lastexp(sql, r);
*rel = r;
e = exp_compare(sql->sa, ls, rs,
compare_str2type(compare_op));
- if (f != sql_sel)
+ if (!is_sql_sel(f))
return e;
/* For selection we need to convert
back into Boolean */
@@ -2040,7 +2040,7 @@ rel_logical_value_exp(mvc *sql, sql_rel
if (!l) {
l = *rel = rel_project(sql->sa, NULL,
new_exp_list(sql->sa));
ls = rel_project_add_exp(sql, l, ls);
- } else if (f == sql_sel) { /* allways add left
side in case of selections phase */
+ } else if (is_sql_sel(f)) { /* allways add left
side in case of selections phase */
if (!l->processed) { /* add all
expressions to the project */
l->exps = list_merge(l->exps,
rel_projections(sql, l->l, NULL, 1, 1), (fdup)NULL);
l->exps =
list_distinct(l->exps, (fcmp)exp_equal, (fdup)NULL);
@@ -2075,7 +2075,7 @@ rel_logical_value_exp(mvc *sql, sql_rel
int needproj = 0, vals_only = 1, is_new = 0;
list *vals = NULL, *pexps = NULL;
- if (outer && f == sql_sel && is_project(outer->op) &&
!is_processed(outer) && !list_empty(outer->exps)) {
+ if (outer && is_sql_sel(f) && is_project(outer->op) &&
!is_processed(outer) && !list_empty(outer->exps)) {
needproj = 1;
pexps = outer->exps;
if (!outer->l) { /* list of constants */
@@ -2096,7 +2096,7 @@ rel_logical_value_exp(mvc *sql, sql_rel
}
}
- if (!left || (!left->l && f == sql_sel &&
list_empty(left->exps))) {
+ if (!left || (!left->l && is_sql_sel(f) &&
list_empty(left->exps))) {
needproj = (left != NULL);
left = rel_project_exp(sql->sa, l);
is_new = 1;
@@ -2144,7 +2144,7 @@ rel_logical_value_exp(mvc *sql, sql_rel
return NULL;
}
}
- if (r && z && is_project(z->op) && z->l
&& f == sql_sel) {
+ if (r && z && is_project(z->op) && z->l
&& is_sql_sel(f)) {
sql_rel *gp = z->l;
r = rel_project_add_exp(sql, z,
r);
reset_processed(gp);
@@ -2224,7 +2224,7 @@ rel_logical_value_exp(mvc *sql, sql_rel
reset_processed(left);
} else
*rel = left;
- if (f == sql_sel) {
+ if (is_sql_sel(f)) {
e = rel_unop_(sql, r, NULL, "isnull",
card_value);
if (sc->token == SQL_IN)
e = rel_unop_(sql, e, NULL, "not",
card_value);
@@ -2378,6 +2378,7 @@ rel_logical_value_exp(mvc *sql, sql_rel
return exp_atom(sql->sa, atom_dup(sql->sa, an->a));
}
}
+ case SQL_IDENT:
case SQL_COLUMN:
return rel_column_ref(sql, rel, sc, f);
case SQL_UNION:
@@ -3013,6 +3014,7 @@ rel_logical_exp(mvc *sql, sql_rel *rel,
sql_exp *e = exp_atom(sql->sa, atom_dup(sql->sa, an->a));
return rel_select(sql->sa, rel, e);
}
+ case SQL_IDENT:
case SQL_COLUMN: {
sql_rel *or = rel;
sql_exp *e = rel_column_ref(sql, &rel, sc, f);
@@ -3140,6 +3142,8 @@ rel_unop(mvc *sql, sql_rel **rel, symbol
e = rel_value_exp(sql, rel, l->next->data.sym, fs, iek);
if (!e) {
if (!f && *rel && (*rel)->card == CARD_AGGR) {
+ if (is_sql_having(fs) || is_sql_orderby(fs))
+ return NULL;
/* reset error */
sql->session->status = 0;
sql->errstr[0] = '\0';
@@ -3433,6 +3437,8 @@ rel_binop(mvc *sql, sql_rel **rel, symbo
if (!l || !r)
sf = find_func(sql, s, fname, 2, F_AGGR, NULL);
if (!sf && (!l || !r) && *rel && (*rel)->card == CARD_AGGR) {
+ if (is_sql_having(f) || is_sql_orderby(f))
+ return NULL;
/* reset error */
sql->session->status = 0;
sql->errstr[0] = '\0';
@@ -3523,6 +3529,8 @@ rel_nop(mvc *sql, sql_rel **rel, symbol
/* first try aggregate */
f = find_func(sql, s, fname, nr_args, F_AGGR, NULL);
if (!f && err && *rel && (*rel)->card == CARD_AGGR) {
+ if (is_sql_having(fs) || is_sql_orderby(fs))
+ return NULL;
/* reset error */
sql->session->status = 0;
sql->errstr[0] = '\0';
@@ -3558,24 +3566,32 @@ static sql_exp *
if (uaname)
GDKfree(uaname);
return e;
- }
-
- if (f == sql_having && is_select(groupby->op))
+ } else if(is_sql_groupby(f) || is_sql_partitionby(f)) {
+ const char *clause = is_sql_groupby(f) ? "GROUP BY":"PARTITION
BY";
+ char *uaname = GDKmalloc(strlen(aname) + 1);
+ sql_exp *e = sql_error(sql, 02, SQLSTATE(42000) "%s: aggregate
function '%s' not allowed in %s clause",
+ uaname ?
toUpperCopy(uaname, aname) : aname, aname, clause);
+ if (uaname)
+ GDKfree(uaname);
+ return e;
+ }
+
+ if (is_sql_having(f) && is_select(groupby->op))
groupby = groupby->l;
if (groupby->l && groupby->op == op_project) {
sql_rel *r = groupby->l;
- if (!is_processed(r) && f == sql_sel && r->op == op_project) {
+ if (!is_processed(r) && is_sql_sel(f) && r->op == op_project) {
project = r;
r = r->l;
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
