Changeset: ac5b509f2038 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=ac5b509f2038
Modified Files:
sql/server/rel_select.c
sql/server/rel_unnest.c
sql/test/subquery/Tests/correlated.stable.err
sql/test/subquery/Tests/subquery2.sql
sql/test/subquery/Tests/subquery3.sql
Branch: sq2
Log Message:
small fixes for rewriting or query
check full result projection expressions for proper cardinality
diffs (200 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
@@ -946,6 +946,39 @@ exps_get_exp(list *exps, int nth)
return NULL;
}
+static sql_rel *
+rel_find_groupby(sql_rel *groupby)
+{
+ if (groupby && !is_processed(groupby) && !is_base(groupby->op)) {
+ while(!is_processed(groupby) && !is_base(groupby->op)) {
+ if (groupby->op == op_groupby || !groupby->l)
+ break;
+ if (groupby->l)
+ groupby = groupby->l;
+ }
+ if (groupby && groupby->op == op_groupby)
+ return groupby;
+ }
+ return NULL;
+}
+
+static int
+is_groupby_col(sql_rel *gb, sql_exp *e)
+{
+ gb = rel_find_groupby(gb);
+
+ if (gb) {
+ if (exp_relname(e)) {
+ if (exp_name(e) && exps_bind_column2(gb->r,
exp_relname(e), exp_name(e)))
+ return 1;
+ } else {
+ if (exp_name(e) && exps_bind_column(gb->r, exp_name(e),
NULL))
+ return 1;
+ }
+ }
+ return 0;
+}
+
static sql_exp *
rel_column_ref(sql_query *query, sql_rel **rel, symbol *column_r, int f)
{
@@ -1004,7 +1037,7 @@ rel_column_ref(sql_query *query, sql_rel
if (exp && is_simple_project(outer->op) &&
!rel_find_exp(outer, exp)) {
exp = rel_project_add_exp(sql, outer,
exp);
}
- if (exp && is_sql_aggr(f) &&
is_sql_aggr(sql_state)) {
+ if (exp && is_sql_aggr(f) &&
is_sql_aggr(sql_state) && !is_groupby_col(outer, exp)) {
return sql_error(sql, 05,
SQLSTATE(42000) "SELECT: aggregate function calls cannot be nested");
}
if (exp)
@@ -1070,7 +1103,7 @@ rel_column_ref(sql_query *query, sql_rel
if (exp && is_simple_project(outer->op) &&
!rel_find_exp(outer, exp)) {
exp = rel_project_add_exp(sql, outer,
exp);
}
- if (exp && is_sql_aggr(f) &&
is_sql_aggr(sql_state)) {
+ if (exp && is_sql_aggr(f) &&
is_sql_aggr(sql_state) && !is_groupby_col(outer, exp)) {
return sql_error(sql, 05,
SQLSTATE(42000) "SELECT: aggregate function calls cannot be nested");
}
if (exp)
@@ -3305,22 +3338,6 @@ rel_check_card(sql_rel *rel, sql_exp *l
return 0;
}
-static sql_rel *
-rel_find_groupby(sql_rel *groupby)
-{
- if (groupby && !is_processed(groupby) && !is_base(groupby->op)) {
- while(!is_processed(groupby) && !is_base(groupby->op)) {
- if (groupby->op == op_groupby || !groupby->l)
- break;
- if (groupby->l)
- groupby = groupby->l;
- }
- if (groupby && groupby->op == op_groupby)
- return groupby;
- }
- return NULL;
-}
-
static sql_exp *
rel_binop(sql_query *query, sql_rel **rel, symbol *se, int f, exp_kind ek)
{
@@ -5446,6 +5463,7 @@ rel_select_exp(sql_query *query, sql_rel
sql_exp *ce = rel_column_exp(query, &inner, n->data.sym,
sql_sel);
if (ce && exp_subtype(ce)) {
+ /*
if (inner && inner->card < ce->card) {
if (exp_name(ce)) {
return sql_error(sql, 05,
SQLSTATE(42000) "SELECT: cannot use non GROUP BY column '%s' in query results
without an aggregate function", exp_name(ce));
@@ -5453,6 +5471,7 @@ rel_select_exp(sql_query *query, sql_rel
return sql_error(sql, 05,
SQLSTATE(42000) "SELECT: cannot use non GROUP BY column in query results
without an aggregate function");
}
}
+ */
pexps = append(pexps, ce);
rel = inner;
continue;
@@ -5471,6 +5490,18 @@ rel_select_exp(sql_query *query, sql_rel
*/
pexps = list_merge(pexps, te, (fdup)NULL);
}
+ if (is_groupby(rel->op) && !sn->groupby) {
+ for (node *n=pexps->h; n; n = n->next) {
+ sql_exp *ce = n->data;
+ if (rel->card < ce->card) {
+ if (exp_name(ce)) {
+ return sql_error(sql, 05,
SQLSTATE(42000) "SELECT: cannot use non GROUP BY column '%s' in query results
without an aggregate function", exp_name(ce));
+ } else {
+ return sql_error(sql, 05,
SQLSTATE(42000) "SELECT: cannot use non GROUP BY column in query results
without an aggregate function");
+ }
+ }
+ }
+ }
rel = rel_project(sql->sa, rel, pexps);
rel = rel_having_limits_nodes(query, rel, sn, ek);
diff --git a/sql/server/rel_unnest.c b/sql/server/rel_unnest.c
--- a/sql/server/rel_unnest.c
+++ b/sql/server/rel_unnest.c
@@ -1483,7 +1483,8 @@ rewrite_or_exp(mvc *sql, sql_rel *rel)
/* rewrite into setop */
sql_rel *l = rel;
sql_rel *r = rel_dup(rel);
-
+ list *exps = rel_projections(sql, rel,
NULL, 1, 1);
+
l = rel_select(sql->sa, l, NULL);
l->exps = e->l;
r = rel_select(sql->sa, r, NULL);
@@ -1492,6 +1493,7 @@ rewrite_or_exp(mvc *sql, sql_rel *rel)
list_remove_node(rel->exps, n); /*
remove or expression */
rel = rel_setop(sql->sa, l, r,
op_union);
rel = rel_distinct(rel);
+ rel->exps = exps;
return rewrite_or_exp(sql, rel);
}
}
diff --git a/sql/test/subquery/Tests/correlated.stable.err
b/sql/test/subquery/Tests/correlated.stable.err
--- a/sql/test/subquery/Tests/correlated.stable.err
+++ b/sql/test/subquery/Tests/correlated.stable.err
@@ -56,7 +56,7 @@ ERROR = !SELECT: cannot use non GROUP BY
CODE = 42000
MAPI = (monetdb) /var/tmp/mtest-8372/.s.monetdb.37437
QUERY = SELECT i+1, (SELECT SUM(i1.i)) FROM integers i1;
-ERROR = !SELECT: cannot use non GROUP BY column 'i' in query results without
an aggregate function
+ERROR = !SELECT: cannot use non GROUP BY column in query results without an
aggregate function
CODE = 42000
MAPI = (monetdb) /var/tmp/mtest-17692/.s.monetdb.32120
QUERY = SELECT i1.i FROM integers i1 INNER JOIN integers i ON SUM(i1.i) =
SUM(i.i); --error, aggregations not allowed in join conditions
diff --git a/sql/test/subquery/Tests/subquery2.sql
b/sql/test/subquery/Tests/subquery2.sql
--- a/sql/test/subquery/Tests/subquery2.sql
+++ b/sql/test/subquery/Tests/subquery2.sql
@@ -122,7 +122,6 @@ GROUP BY col1, col2, col5, col8;
-- False True True True
-/* Wrong output (error) because of CAST (NOT col1 IN (SELECT col2 FROM
another_T GROUP BY col2) AS INTEGER) | CAST (col2 IN (SELECT col2 FROM
another_T GROUP BY col2) AS INTEGER),
SELECT
DISTINCT
NOT col1 * col5 = ALL (SELECT 1 FROM tbl_ProductSales HAVING MAX(col2)
> 2),
@@ -133,7 +132,6 @@ FROM another_T
GROUP BY col1, col2, col5;
-- False False 1 0
-- True False 1 0
-*/
SELECT
SUM(col1) IN (SELECT DISTINCT col2 FROM another_T GROUP BY col2)
@@ -233,11 +231,9 @@ FROM another_T t1;
-- NULL
-- NULL
-/* BROKEN code generation with a constant in a groupby- aggregation
SELECT
CASE WHEN 1 IN (SELECT (SELECT MAX(col7))) THEN 2 ELSE NULL END
FROM another_T t1;
-*/
-- NULL
-- NULL
-- NULL
diff --git a/sql/test/subquery/Tests/subquery3.sql
b/sql/test/subquery/Tests/subquery3.sql
--- a/sql/test/subquery/Tests/subquery3.sql
+++ b/sql/test/subquery/Tests/subquery3.sql
@@ -23,13 +23,11 @@ GROUP BY col1, col2, col5, col8;
-- 226 98568 1143
-- 2226 9874568 11443
-/* Broken bit_or aggr
SELECT
CAST (NOT col1 IN (SELECT col2 FROM another_T GROUP BY col2) AS INTEGER) |
CAST (col2 IN (SELECT col2 FROM another_T GROUP BY col2) AS INTEGER),
CAST (EXISTS (SELECT MAX(col5) * MAX(col4) FROM another_T GROUP BY col5,
col4) AS INTEGER) & CAST (SUM(col4) IN (SELECT DISTINCT col2 FROM another_T
GROUP BY col2) AS INTEGER)
FROM another_T
GROUP BY col1, col2, col5;
-*/
-- 1 0
-- 1 0
-- 1 0
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
