Changeset: 8d98909b851e for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=8d98909b851e
Added Files:
sql/test/analytics/Tests/analytics11.sql
sql/test/analytics/Tests/analytics11.stable.err
sql/test/analytics/Tests/analytics11.stable.out
Modified Files:
sql/server/rel_optimizer.c
sql/server/rel_select.c
sql/server/sql_parser.y
sql/server/sql_semantic.c
sql/server/sql_tokens.h
sql/test/analytics/Tests/All
Branch: grouping-analytics
Log Message:
Adding GROUPING special function call. It requires a non conventional way of
compiling compared to a function call or aggregate
diffs (truncated from 403 to 300 lines):
diff --git a/sql/server/rel_optimizer.c b/sql/server/rel_optimizer.c
--- a/sql/server/rel_optimizer.c
+++ b/sql/server/rel_optimizer.c
@@ -6291,7 +6291,7 @@ exps_mark_used(sql_allocator *sa, sql_re
nr += e->used;
}
- if (!nr && is_project(rel->op)) /* project atleast one column */
+ if (!nr && is_project(rel->op) && len > 0) /* project at least
one column if exists */
exps[0]->used = 1;
for (i = len-1; i >= 0; i--) {
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
@@ -3848,19 +3848,15 @@ rel_intermediates_add_exp(mvc *sql, sql_
}
}
-static sql_exp *
-_rel_aggr(sql_query *query, sql_rel **rel, int distinct, sql_schema *s, char
*aname, dnode *args, int f)
+
+static sql_rel *
+rel_find_groupby(sql_query *query, sql_rel *groupby, sql_rel **og, sql_rel
**sel, int *group, int f, char* aname)
{
mvc *sql = query->sql;
- exp_kind ek = {type_value, card_column, FALSE};
- sql_subaggr *a = NULL;
- int no_nil = 0, group = 0, freevar = 1;
- sql_rel *groupby = *rel, *sel = NULL, *gr, *og = NULL;
- list *exps = NULL;
/* find having select */
if (groupby && !is_processed(groupby) && is_sql_having(f)) {
- og = groupby;
+ *og = groupby;
while(!is_processed(groupby) && !is_base(groupby->op)) {
if (is_select(groupby->op) || !groupby->l)
break;
@@ -3868,20 +3864,20 @@ static sql_exp *
groupby = groupby->l;
}
if (groupby && is_select(groupby->op) &&
!is_processed(groupby)) {
- group = 1;
- sel = groupby;
+ *group = 1;
+ *sel = groupby;
/* At the end we switch back to the old projection
relation og.
* During the partitioning and ordering we add the
expressions to the intermediate relations. */
}
- if (!sel)
- groupby = og;
- if (sel && sel->l)
- groupby = sel->l;
+ if (!*sel)
+ groupby = *og;
+ if (*sel && (*sel)->l)
+ groupby = (*sel)->l;
}
/* find groupby */
if (groupby && !is_processed(groupby) && !is_base(groupby->op)) {
- og = groupby;
+ *og = groupby;
while(!is_processed(groupby) && !is_base(groupby->op)) {
if (groupby->op == op_groupby || !groupby->l)
break;
@@ -3889,32 +3885,82 @@ static sql_exp *
groupby = groupby->l;
}
if (groupby && groupby->op == op_groupby) {
- group = 1;
+ *group = 1;
/* At the end we switch back to the old projection
relation og.
* During the partitioning and ordering we add the
expressions to the intermediate relations. */
}
- if (!group)
- groupby = og;
+ if (!*group)
+ groupby = *og;
}
if (!groupby) {
char *uaname = GDKmalloc(strlen(aname) + 1);
- sql_exp *e = sql_error(sql, 02, SQLSTATE(42000) "%s: missing
group by",
- uaname ? toUpperCopy(uaname, aname) :
aname);
+ (void) sql_error(sql, 02, SQLSTATE(42000) "%s: missing group
by",
+ uaname ? toUpperCopy(uaname,
aname) : aname);
if (uaname)
GDKfree(uaname);
- return e;
+ return NULL;
} else if(is_sql_groupby(f) || (is_sql_partitionby(f) && groupby->op !=
op_groupby)) {
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);
+ (void) 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 (groupby->op != op_groupby) { /* implicit groupby */
+ return NULL;
+ }
+
+ return groupby;
+}
+
+static sql_exp *
+rel_grouping(sql_query *query, sql_rel **rel, symbol *se, int f)
+{
+ mvc *sql = query->sql;
+ sql_rel *groupby, *sel = NULL, *og = NULL;
+ int group = 0;
+ dlist *l = se->data.lval;
+ list *group_cols;
+
+ (void) group;
+ (void) sel;
+ (void) og;
+
+ if (is_sql_groupby(f) || is_sql_partitionby(f) || is_sql_where(f) ||
is_sql_from(f))
+ return sql_error(sql, 02, SQLSTATE(42000) "SELECT: GROUPING
expression not allowed in %s clause",
+ is_sql_groupby(f) ? "GROUP BY"
: is_sql_partitionby(f) ? "PARTITION BY" : is_sql_where(f) ? "WHERE" : "FROM");
+ if (!(groupby = rel_find_groupby(query, *rel, &og, &sel, &group, f,
"GROUPING")))
+ return NULL;
+ if (groupby->op != op_groupby)
+ return sql_error(sql, 02, SQLSTATE(42000) "SELECT: GROUPING
requires a GROUP BY clause");
+
+ group_cols = (list*) groupby->r;
+
+ for (dnode *dn = l->h; dn; dn = dn->next) {
+ symbol *sym = dn->data.sym;
+ exp_kind ek = {type_value, card_column, FALSE};
+ sql_exp *col = rel_value_exp(query, rel, sym, f, ek);
+ if (!col)
+ return NULL; /* GROUPING(x, ...) columns must be
present in the GROUP BY clause */
+ }
+
+ return exp_atom(sql->sa, atom_int(sql->sa, sql_bind_localtype("int"),
0));
+}
+
+static sql_exp *
+_rel_aggr(sql_query *query, sql_rel **rel, int distinct, sql_schema *s, char
*aname, dnode *args, int f)
+{
+ mvc *sql = query->sql;
+ exp_kind ek = {type_value, card_column, FALSE};
+ sql_subaggr *a = NULL;
+ int no_nil = 0, group = 0, freevar = 1;
+ sql_rel *groupby, *sel = NULL, *gr, *og = NULL;
+ list *exps = NULL;
+
+ if (!(groupby = rel_find_groupby(query, *rel, &og, &sel, &group, f,
aname)))
+ return NULL;
+
+ if (groupby->op != op_groupby) { /* implicit groupby */
sql_rel *np = rel_project2groupby(sql, groupby);
if (*rel == groupby) {
@@ -5990,6 +6036,8 @@ rel_value_exp2(sql_query *query, sql_rel
return exp_atom(sql->sa, atom_dup(sql->sa, an->a));
}
}
+ case SQL_GROUPING:
+ return rel_grouping(query, rel, se, f);
case SQL_NEXT:
return rel_next_value_for(sql, se);
case SQL_CAST:
diff --git a/sql/server/sql_parser.y b/sql/server/sql_parser.y
--- a/sql/server/sql_parser.y
+++ b/sql/server/sql_parser.y
@@ -4253,27 +4253,26 @@ scalar_exp:
value_exp:
atom
- | user { $$ = _symbol_create_list( SQL_COLUMN,
- append_string(L(), sa_strdup(SA, "current_user"))); }
- | CURRENT_ROLE { $$ = _symbol_create_list( SQL_COLUMN,
- append_string(L(), sa_strdup(SA, "current_role"))); }
- | window_function
- | column_ref { $$ = _symbol_create_list( SQL_COLUMN, $1); }
- | var_ref
| aggr_ref
- | func_ref
- | NEXT VALUE FOR qname { $$ = _symbol_create_list( SQL_NEXT, $4); }
- | datetime_funcs
- | string_funcs
| case_exp
| cast_exp
- | XML_value_function
- | param
+ | column_ref { $$ =
_symbol_create_list(SQL_COLUMN, $1); }
+ | CURRENT_ROLE { $$ = _symbol_create_list(SQL_COLUMN, append_string(L(),
sa_strdup(SA, "current_role"))); }
+ | datetime_funcs
+ | func_ref
+ | GROUPING '(' column_ref_commalist ')' { $$ =
_symbol_create_list(SQL_GROUPING, $3); }
+ | NEXT VALUE FOR qname { $$ = _symbol_create_list(SQL_NEXT,
$4); }
| null
- ;
-
-param:
- '?'
+ | param
+ | string_funcs
+ | user { $$ = _symbol_create_list(SQL_COLUMN, append_string(L(),
sa_strdup(SA, "current_user"))); }
+ | var_ref
+ | window_function
+ | XML_value_function
+ ;
+
+param:
+ '?'
{
int nr = (m->params)?list_length(m->params):0;
@@ -6657,6 +6656,7 @@ char *token2string(tokens token)
SQL(GRANT);
SQL(GRANT_ROLES);
SQL(GROUPBY);
+ SQL(GROUPING);
SQL(GROUPING_SETS);
SQL(IDENT);
SQL(IF);
diff --git a/sql/server/sql_semantic.c b/sql/server/sql_semantic.c
--- a/sql/server/sql_semantic.c
+++ b/sql/server/sql_semantic.c
@@ -329,6 +329,21 @@ char *symbol2string(mvc *sql, symbol *se
}
len = snprintf( buf+len, BUFSIZ-len, ")");
} break;
+ case SQL_GROUPING: {
+ dnode *lst = se->data.lval->h;
+ dnode *ops = lst->data.lval->h;
+ len = snprintf( buf+len, BUFSIZ-len, "GROUPING(");
+ for (; ops; ops = ops->next) {
+ char *tmp = symbol2string(sql, ops->data.sym,
expression, err);
+ if (tmp == NULL)
+ return NULL;
+ len = snprintf( buf+len, BUFSIZ-len, "%s%s",
+ tmp,
+ (ops->next)?",":"");
+ _DELETE(tmp);
+ }
+ len = snprintf( buf+len, BUFSIZ-len, ")");
+ } break;
case SQL_BINOP: {
dnode *lst = se->data.lval->h;
char *op = qname_fname(lst->data.lval);
diff --git a/sql/server/sql_tokens.h b/sql/server/sql_tokens.h
--- a/sql/server/sql_tokens.h
+++ b/sql/server/sql_tokens.h
@@ -84,6 +84,7 @@ typedef enum tokens {
SQL_GRANT,
SQL_GRANT_ROLES,
SQL_GROUPBY,
+ SQL_GROUPING,
SQL_GROUPING_SETS,
SQL_IDENT,
SQL_IF,
diff --git a/sql/test/analytics/Tests/All b/sql/test/analytics/Tests/All
--- a/sql/test/analytics/Tests/All
+++ b/sql/test/analytics/Tests/All
@@ -12,3 +12,4 @@ analytics09
# Rollup, Cube tests
analytics10
+analytics11
diff --git a/sql/test/analytics/Tests/analytics11.sql
b/sql/test/analytics/Tests/analytics11.sql
new file mode 100644
--- /dev/null
+++ b/sql/test/analytics/Tests/analytics11.sql
@@ -0,0 +1,21 @@
+CREATE TABLE tbl_ProductSales (ColID int, Product_Category varchar(64),
Product_Name varchar(64), TotalSales int);
+INSERT INTO tbl_ProductSales VALUES (1,'Game','Mobo Game',200),(2,'Game','PKO
Game',400),(3,'Fashion','Shirt',500),(4,'Fashion','Shorts',100);
+
+SELECT 1
+FROM tbl_ProductSales
+GROUP BY Product_Category;
+
+SELECT 1
+FROM tbl_ProductSales
+GROUP BY ();
+
+SELECT
+ GROUPING()
+FROM tbl_ProductSales
+GROUP BY Product_Category; --error, "grouping" requires arguments
+
+SELECT
+ GROUPING(Product_Name)
+FROM tbl_ProductSales GROUP BY (); --error, Product_Name it's not a grouping
column
+
+DROP TABLE tbl_ProductSales;
diff --git a/sql/test/analytics/Tests/analytics11.stable.err
b/sql/test/analytics/Tests/analytics11.stable.err
new file mode 100644
--- /dev/null
+++ b/sql/test/analytics/Tests/analytics11.stable.err
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
