Changeset: 23196159a154 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=23196159a154
Modified Files:
sql/common/sql_types.c
sql/server/rel_select.c
sql/test/analytics/Tests/analytics11.sql
sql/test/analytics/Tests/analytics11.stable.err
sql/test/analytics/Tests/analytics11.stable.out
Branch: grouping-analytics
Log Message:
Approve output and small fixes
diffs (300 lines):
diff --git a/sql/common/sql_types.c b/sql/common/sql_types.c
--- a/sql/common/sql_types.c
+++ b/sql/common/sql_types.c
@@ -486,7 +486,7 @@ static sql_subaggr *
scale = member->scale;
}
/* same type as the input */
- if (r->type->eclass == EC_ANY)
+ if (r->type->eclass == EC_ANY && member)
r = member;
res = sql_create_subtype(sa, r->type, digits, scale);
list_append(ares->res, res);
@@ -1535,8 +1535,7 @@ sqltypeinit( sql_allocator *sa)
*t = NULL;
// sql_create_func(sa, "st_pointfromtext", "geom", "st_pointformtext",
OID, NULL, OID, SCALE_FIX);
- /* using ANY for the res type for grouping aggregate gives problems,
however we don't care about the types of it at all */
- sql_create_aggr(sa, "grouping", "sql", "grouping", ANY, TABLE);
+ sql_create_aggr(sa, "grouping", "sql", "grouping", ANY, ANY);
sql_create_aggr(sa, "not_unique", "sql", "not_unique", OID, BIT);
/* well to be precise it does reduce and map */
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
@@ -3857,6 +3857,7 @@ static sql_exp *
int no_nil = 0, group = 0, freevar = 1;
sql_rel *groupby = *rel, *sel = NULL, *gr, *og = NULL;
list *exps = NULL;
+ bool is_grouping = !strcmp(aname, "grouping");
/* find having select */
if (groupby && !is_processed(groupby) && is_sql_having(f)) {
@@ -3997,11 +3998,17 @@ static sql_exp *
gr->l = gl;
if (!e || !exp_subtype(e)) /* we also do not expect parameters
here */
return NULL;
+ if (is_grouping && !exps_find_exp((list*)groupby->r, e)) {
+ const char *cname = exp_name(e);
+ assert(cname && e->type == e_column);
+ return sql_error(sql, 02, SQLSTATE(42000) "GROUPING:
cannot use column %s without specifying it in the GROUP BY clause", cname);
+ }
+
freevar &= exp_has_freevar(e);
list_append(exps, e);
}
- if (!strcmp(aname, "grouping"))
+ if (is_grouping)
a = sql_bind_aggr(sql->sa, s, aname, NULL);
else
a = sql_bind_aggr_(sql->sa, s, aname, exp_types(sql->sa, exps));
diff --git a/sql/test/analytics/Tests/analytics11.sql
b/sql/test/analytics/Tests/analytics11.sql
--- a/sql/test/analytics/Tests/analytics11.sql
+++ b/sql/test/analytics/Tests/analytics11.sql
@@ -36,7 +36,12 @@ GROUP BY GROUPING SETS((Product_Category
SELECT
AVG(GROUPING(Product_Category))
FROM tbl_ProductSales
-GROUP BY GROUPING SETS((Product_Category)) --error, "grouping" not allowed
inside aggregation functions
+GROUP BY GROUPING SETS((Product_Category)); --error, "grouping" not allowed
inside aggregation functions
+
+SELECT
+ GROUPING(1)
+FROM tbl_ProductSales
+GROUP BY Product_Category; --error, "grouping" requires group columns as input
-- GROUPING calls
@@ -46,7 +51,7 @@ FROM tbl_ProductSales
GROUP BY Product_Category;
SELECT
- GROUPING(Product_Category) AS myalias
+ GROUPING(Product_Category) myalias
FROM tbl_ProductSales
GROUP BY Product_Category, Product_Name;
@@ -78,6 +83,16 @@ FROM tbl_ProductSales
GROUP BY ROLLUP((Product_Category, Product_Name, ColID));
SELECT
+ GROUPING(Product_Category, ColID)
+FROM tbl_ProductSales
+GROUP BY ROLLUP((Product_Category, Product_Name, ColID));
+
+SELECT
+ GROUPING(Product_Category, ColID)
+FROM tbl_ProductSales
+GROUP BY CUBE((Product_Category, Product_Name, ColID));
+
+SELECT
GROUPING(Product_Category)
FROM tbl_ProductSales
GROUP BY GROUPING SETS((Product_Category), (Product_Name), (Product_Category,
Product_Name), ())
@@ -89,4 +104,11 @@ FROM tbl_ProductSales
GROUP BY GROUPING SETS((Product_Category), (Product_Name), (Product_Category,
Product_Name), ())
HAVING GROUPING(Product_Category) = 0;
+SELECT
+ GROUPING(Product_Category, ColID)
+FROM tbl_ProductSales
+GROUP BY CUBE((Product_Category, Product_Name, ColID))
+HAVING GROUPING(Product_Category) = 3
+ORDER BY GROUPING(Product_Category);
+
DROP TABLE tbl_ProductSales;
diff --git a/sql/test/analytics/Tests/analytics11.stable.err
b/sql/test/analytics/Tests/analytics11.stable.err
--- a/sql/test/analytics/Tests/analytics11.stable.err
+++ b/sql/test/analytics/Tests/analytics11.stable.err
@@ -38,7 +38,43 @@ MAPI = (monetdb) /var/tmp/mtest-19672/.
QUERY = SELECT
GROUPING(Product_Name)
FROM tbl_ProductSales GROUP BY (); --error, Product_Name it's not a
grouping column
-ERROR = !SELECT: cannot use non GROUP BY column 'product_name' in query
results without an aggregate function
+ERROR = !GROUPING: cannot use column product_name without specifying it in the
GROUP BY clause
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-11452/.s.monetdb.32908
+QUERY = SELECT
+ GROUPING(Product_Name)
+ FROM tbl_ProductSales; --error, same as upper one
+ERROR = !GROUPING: cannot use column product_name without specifying it in the
GROUP BY clause
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-11452/.s.monetdb.32908
+QUERY = SELECT
+ 1
+ FROM tbl_ProductSales
+ GROUP BY GROUPING(Product_Name); --error, "grouping" not allowed
inside GROUP BY
+ERROR = !GROUPING: aggregate function 'grouping' not allowed in GROUP BY clause
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-11452/.s.monetdb.32908
+QUERY = SELECT
+ 1
+ FROM tbl_ProductSales
+ WHERE GROUPING(Product_Category) > 1
+ GROUP BY GROUPING SETS((Product_Category)); --error, "grouping" not
allowed in where clause
+ERROR = !Subquery result missing
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-11452/.s.monetdb.32908
+QUERY = SELECT
+ AVG(GROUPING(Product_Category))
+ FROM tbl_ProductSales
+ GROUP BY GROUPING SETS((Product_Category)); --error, "grouping" not
allowed inside aggregation functions
+ERROR = !SELECT: subquery result missing
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-12109/.s.monetdb.39866
+QUERY = SELECT
+ GROUPING(1)
+ FROM tbl_ProductSales
+ GROUP BY Product_Category; --error, "grouping" requires group columns
as input
+ERROR = !syntax error, unexpected sqlINT in: "select
+ ! grouping(1"
CODE = 42000
# 14:50:27 >
diff --git a/sql/test/analytics/Tests/analytics11.stable.out
b/sql/test/analytics/Tests/analytics11.stable.out
--- a/sql/test/analytics/Tests/analytics11.stable.out
+++ b/sql/test/analytics/Tests/analytics11.stable.out
@@ -42,6 +42,141 @@ stdout of test 'analytics11` in director
% tinyint # type
% 1 # length
[ 1 ]
+#SELECT
+# GROUPING(Product_Category) AS myalias
+#FROM tbl_ProductSales
+#GROUP BY Product_Category;
+% .L2 # table_name
+% myalias # name
+% tinyint # type
+% 1 # length
+[ 0 ]
+[ 0 ]
+#SELECT
+# GROUPING(Product_Category) AS myalias
+#FROM tbl_ProductSales
+#GROUP BY Product_Category, Product_Name;
+% .L2 # table_name
+% myalias # name
+% tinyint # type
+% 1 # length
+[ 0 ]
+[ 0 ]
+[ 0 ]
+[ 0 ]
+#SELECT
+# GROUPING(Product_Name, Product_Category)
+#FROM tbl_ProductSales
+#GROUP BY Product_Category, Product_Name;
+% .L1 # table_name
+% L1 # name
+% tinyint # type
+% 1 # length
+[ 0 ]
+[ 0 ]
+[ 0 ]
+[ 0 ]
+#SELECT
+# GROUPING(Product_Category) AS myalias
+#FROM tbl_ProductSales
+#GROUP BY ROLLUP(Product_Category);
+% .L2 # table_name
+% myalias # name
+% tinyint # type
+% 1 # length
+[ 0 ]
+[ 0 ]
+[ 1 ]
+#SELECT
+# GROUPING(Product_Category) AS myalias
+#FROM tbl_ProductSales
+#GROUP BY Product_Category, ROLLUP(Product_Category);
+% .L2 # table_name
+% myalias # name
+% tinyint # type
+% 1 # length
+[ 0 ]
+[ 0 ]
+[ 0 ]
+[ 0 ]
+#SELECT
+# GROUPING(Product_Category, Product_Name, ColID)
+#FROM tbl_ProductSales
+#GROUP BY ROLLUP(Product_Category, Product_Name, ColID);
+% .L1 # table_name
+% L1 # name
+% tinyint # type
+% 1 # length
+[ 0 ]
+[ 0 ]
+[ 0 ]
+[ 0 ]
+[ 1 ]
+[ 1 ]
+[ 1 ]
+[ 1 ]
+[ 3 ]
+[ 3 ]
+[ 7 ]
+#SELECT
+# GROUPING(Product_Category, Product_Name, ColID)
+#FROM tbl_ProductSales
+#GROUP BY ROLLUP((Product_Category, Product_Name, ColID));
+% .L1 # table_name
+% L1 # name
+% tinyint # type
+% 1 # length
+[ 0 ]
+[ 0 ]
+[ 0 ]
+[ 0 ]
+[ 7 ]
+#SELECT
+# GROUPING(Product_Category, ColID)
+#FROM tbl_ProductSales
+#GROUP BY ROLLUP((Product_Category, Product_Name, ColID));
+% .L1 # table_name
+% L1 # name
+% tinyint # type
+% 1 # length
+[ 0 ]
+[ 0 ]
+[ 0 ]
+[ 0 ]
+[ 3 ]
+#SELECT
+# GROUPING(Product_Category, ColID)
+#FROM tbl_ProductSales
+#GROUP BY CUBE((Product_Category, Product_Name, ColID));
+% .L1 # table_name
+% L1 # name
+% tinyint # type
+% 1 # length
+[ 3 ]
+[ 0 ]
+[ 0 ]
+[ 0 ]
+[ 0 ]
+#SELECT
+# GROUPING(Product_Category)
+#FROM tbl_ProductSales
+#GROUP BY GROUPING SETS((Product_Category), (Product_Name), (Product_Category,
Product_Name), ())
+#ORDER BY GROUPING(Product_Category);
+% .L1 # table_name
+% L1 # name
+% tinyint # type
+% 1 # length
+[ 0 ]
+[ 0 ]
+[ 0 ]
+[ 0 ]
+[ 0 ]
+[ 0 ]
+[ 1 ]
+[ 1 ]
+[ 1 ]
+[ 1 ]
+[ 1 ]
#DROP TABLE tbl_ProductSales;
# 14:50:27 >
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
