Changeset: 9a20823adaa8 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=9a20823adaa8
Modified Files:
sql/server/rel_propagate.c
sql/server/rel_select.c
sql/server/rel_unnest.c
sql/server/rel_updates.c
sql/test/subquery/Tests/subquery3.stable.err
sql/test/subquery/Tests/subquery3.stable.out
sql/test/subquery/Tests/subquery5.stable.err
sql/test/subquery/Tests/subquery5.stable.out
Branch: Jun2020
Log Message:
small fixes for UNION, INTERSECT, EXCEPT in project/subqueries
handling values with correlation earlier
diffs (263 lines):
diff --git a/sql/server/rel_propagate.c b/sql/server/rel_propagate.c
--- a/sql/server/rel_propagate.c
+++ b/sql/server/rel_propagate.c
@@ -81,7 +81,7 @@ rel_generate_anti_insert_expression(mvc
*anti_rel = rel_project(sql->sa, *anti_rel, l);
inserts = (*anti_rel)->l;
- if (inserts->op != op_project && inserts->op != op_basetable &&
inserts->op != op_table)
+ if (inserts->op != op_project && inserts->op != op_union &&
inserts->op != op_basetable && inserts->op != op_table)
inserts = inserts->l;
for (node *n = t->columns.set->h, *m = inserts->exps->h; n &&
m; n = n->next, m = m->next) {
sql_column *col = n->data;
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
@@ -1759,7 +1759,7 @@ rel_compare_exp_(sql_query *query, sql_r
else
return sql_error(sql, ERR_GROUPBY, SQLSTATE(42000)
"SELECT: cannot use non GROUP BY column in query results without an aggregate
function");
}
- if (rs->card <= CARD_ATOM && (exp_is_atom(rs) || exp_has_freevar(sql,
rs)) &&
+ if (rs->card <= CARD_ATOM && (exp_is_atom(rs) || exp_has_freevar(sql,
rs) || exp_has_freevar(sql, ls)) &&
(!rs2 || (rs2->card <= CARD_ATOM && (exp_is_atom(rs2) ||
exp_has_freevar(sql, rs2))))) {
if ((ls->card == rs->card && !rs2) || rel->processed) /* bin
compare op */
return rel_select(sql->sa, rel, e);
@@ -1886,7 +1886,7 @@ rel_compare(sql_query *query, sql_rel *r
ls = rel_value_exp(query, &rel, lo, f, ek);
if (!ls)
return NULL;
- if (ls && rel && exp_has_freevar(sql, ls) && (is_sql_sel(f) ||
is_sql_having(f)))
+ if (ls && rel && exp_has_freevar(sql, ls) && is_sql_sel(f))
ls = rel_project_add_exp(sql, rel, ls);
if (quantifier)
ek.card = card_set;
@@ -2448,7 +2448,7 @@ rel_logical_value_exp(sql_query *query,
return NULL;
if (ek.card <= card_set && is_project(sq->op) &&
list_length(sq->exps) > 1)
return sql_error(sql, 02, SQLSTATE(42000) "SELECT:
subquery must return only one column");
- if (ek.card < card_set && sq->card >= CARD_MULTI &&
(is_sql_sel(f) | is_sql_where(f)) && rel && (!*rel || is_basetable((*rel)->op)
|| is_simple_project((*rel)->op)))
+ if (ek.card < card_set && sq->card >= CARD_MULTI &&
(is_sql_sel(f) | is_sql_having(f) | ( is_sql_where(f) && rel && (!*rel ||
is_basetable((*rel)->op) || is_simple_project((*rel)->op)))))
sq = rel_zero_or_one(sql, sq, ek);
return exp_rel(sql, sq);
}
@@ -3509,7 +3509,10 @@ static sql_exp *
return sql_error(sql, 05, SQLSTATE(42000)
"SELECT: aggregate functions not allowed in functions in FROM");
if (card > CARD_AGGR) { /* used an expression before on
the non grouped relation */
sql_exp *lu = query_outer_last_used(query,
all_freevar-1);
- return sql_error(sql, ERR_GROUPBY,
SQLSTATE(42000) "SELECT: subquery uses ungrouped column \"%s.%s\" from outer
query", exp_relname(lu), exp_name(lu));
+ if (lu->type == e_column)
+ return sql_error(sql, ERR_GROUPBY,
SQLSTATE(42000) "SELECT: subquery uses ungrouped column \"%s.%s\" from outer
query", (char*)lu->l, (char*)lu->r);
+ else
+ return sql_error(sql, ERR_GROUPBY,
SQLSTATE(42000) "SELECT: subquery uses ungrouped column \"%s.%s\" from outer
query", exp_relname(lu), exp_name(lu));
}
if (is_outer(groupby))
return sql_error(sql, ERR_GROUPBY,
SQLSTATE(42000) "SELECT: subquery uses ungrouped column from outer query");
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
@@ -2887,6 +2887,42 @@ rewrite_complex(mvc *sql, sql_rel *rel,
return e;
}
+/* rewrite project [ [multi values], [multi values2] , .. [] ] -> union ) */
+static sql_rel *
+rewrite_values(mvc *sql, sql_rel *rel, int *changes)
+{
+ (void)changes;
+ if (!is_simple_project(rel->op) || list_empty(rel->exps))
+ return rel;
+ sql_exp *e = rel->exps->h->data;
+
+ if (!is_values(e) || list_length(exp_get_values(e))<=1 ||
!rel_has_freevar(sql, rel))
+ return rel;
+
+ list *exps = sa_list(sql->sa);
+ sql_rel *cur = NULL;
+ list *vals = exp_get_values(e);
+ for(int i = 0; i<list_length(vals); i++) {
+ sql_rel *nrel = rel_project(sql->sa, NULL, sa_list(sql->sa));
+ for(node *n = rel->exps->h; n; n = n->next) {
+ sql_exp *e = n->data;
+ if (i == 0)
+ append(exps, exp_ref(sql, e));
+ list *vals = exp_get_values(e);
+ sql_exp *v = list_fetch(vals, i);
+ append(nrel->exps, v);
+ rel_set_exps(nrel, nrel->exps);
+ }
+ if (cur) {
+ nrel = rel_setop(sql->sa, cur, nrel, op_union);
+ rel_set_exps(nrel, exps);
+ }
+ cur = nrel;
+ }
+ rel = cur;
+ return rel;
+}
+
sql_rel *
rel_unnest(mvc *sql, sql_rel *rel)
{
@@ -2903,9 +2939,12 @@ rel_unnest(mvc *sql, sql_rel *rel)
rel = rel_exp_visitor_bottomup(sql, rel, &rewrite_rank, &changes);
rel = rel_visitor_bottomup(sql, rel, &rewrite_outer2inner_union,
&changes);
+ // remove empty project/groupby !
+ rel = rel_visitor_bottomup(sql, rel, &rewrite_empty_project, &changes);
rel = rel_exp_visitor_bottomup(sql, rel, &rewrite_complex, &changes);
rel = rel_exp_visitor_bottomup(sql, rel, &rewrite_ifthenelse,
&changes); /* add isnull handling */
+ rel = rel_visitor_bottomup(sql, rel, &rewrite_values, &changes);
rel = rel_exp_visitor_bottomup(sql, rel, &rewrite_exp_rel, &changes);
rel = rel_visitor_bottomup(sql, rel, &rewrite_join2semi, &changes);
/* where possible convert anyequal functions into marks */
rel = rel_visitor_bottomup(sql, rel, &rewrite_compare_exp, &changes);
/* only allow for e_cmp in selects and handling */
@@ -2919,6 +2958,7 @@ rel_unnest(mvc *sql, sql_rel *rel)
rel = rel_visitor_bottomup(sql, rel, &rewrite_remove_xp, &changes);
/* remove crossproducts with project [ atom ] */
rel = rel_visitor_bottomup(sql, rel, &rewrite_groupings, &changes);
/* transform group combinations into union of group relations */
rel = rel_visitor_bottomup(sql, rel, &rewrite_empty_project, &changes);
+ // needed again!
rel = rel_exp_visitor_bottomup(sql, rel, &exp_reset_card, &changes);
return rel;
}
diff --git a/sql/server/rel_updates.c b/sql/server/rel_updates.c
--- a/sql/server/rel_updates.c
+++ b/sql/server/rel_updates.c
@@ -503,14 +503,8 @@ insert_generate_inserts(sql_query *query
sql_exp *ins =
insert_value(query, c, &r, n->data.sym, action);
if (!ins)
return NULL;
- if (r && inner)
- inner =
rel_crossproduct(sql->sa, inner, r, op_join);
- else if (r)
- inner = r;
- if (inner && !exp_name(ins) &&
!exp_is_atom(ins)) {
+ if (!exp_name(ins))
exp_label(sql->sa, ins,
++sql->label);
- ins = exp_ref(sql, ins);
- }
list_append(vals_list, ins);
}
} else {
diff --git a/sql/test/subquery/Tests/subquery3.stable.err
b/sql/test/subquery/Tests/subquery3.stable.err
--- a/sql/test/subquery/Tests/subquery3.stable.err
+++ b/sql/test/subquery/Tests/subquery3.stable.err
@@ -325,14 +325,14 @@ MAPI = (monetdb) /var/tmp/mtest-596453/
QUERY = SELECT
(SELECT i2.i FROM (VALUES (i1.i), (i1.i)) as i2(i))
FROM integers i1; --error, more than one row returned by a subquery
used as an expression
-ERROR = !Cardinality violation, scalar value expected
-CODE = 21000
-MAPI = (monetdb) /var/tmp/mtest-600940/.s.monetdb.32137
+ERROR = !zero_or_one: cardinality violation, scalar expression expected
+CODE = M0M29
+MAPI = (monetdb) /var/tmp/mtest-2650031/.s.monetdb.39763
QUERY = SELECT
(SELECT i2.i FROM (VALUES (i1.i, i1.i), (i1.i, i1.i)) as
i2(i,j))
FROM integers i1; --error, more than one row returned by a subquery
used as an expression
-ERROR = !Cardinality violation, scalar value expected
-CODE = 21000
+ERROR = !zero_or_one: cardinality violation, scalar expression expected
+CODE = M0M29
# 17:40:41 >
# 17:40:41 > "Done."
diff --git a/sql/test/subquery/Tests/subquery3.stable.out
b/sql/test/subquery/Tests/subquery3.stable.out
--- a/sql/test/subquery/Tests/subquery3.stable.out
+++ b/sql/test/subquery/Tests/subquery3.stable.out
@@ -36,7 +36,7 @@ stdout of test 'subquery3` in directory
#FROM another_T t1
#GROUP BY t1.col6, t1.col7;
% . # table_name
-% %36 # name
+% %33 # name
% boolean # type
% 5 # length
[ false ]
@@ -63,7 +63,7 @@ stdout of test 'subquery3` in directory
#FROM another_T
#GROUP BY col1, col2, col5;
% ., . # table_name
-% %53, %54 # name
+% %55, %56 # name
% int, int # type
% 1, 1 # length
[ 1, 0 ]
@@ -193,8 +193,8 @@ stdout of test 'subquery3` in directory
# NOT SUM(t1.col2) * MIN(t1.col6 + t1.col6 - t1.col6 * t1.col6) NOT IN
(SELECT MAX(t2.col6) FROM another_T t2 GROUP BY t1.col6 HAVING t1.col7 +
MIN(t2.col8) < MAX(t2.col7 - t1.col6))
#FROM another_T t1
#GROUP BY t1.col7, t1.col6;
-% .%24 # table_name
-% %24 # name
+% .%23 # table_name
+% %23 # name
% boolean # type
% 5 # length
[ false ]
diff --git a/sql/test/subquery/Tests/subquery5.stable.err
b/sql/test/subquery/Tests/subquery5.stable.err
--- a/sql/test/subquery/Tests/subquery5.stable.err
+++ b/sql/test/subquery/Tests/subquery5.stable.err
@@ -116,9 +116,9 @@ ERROR = !SELECT: subquery must return on
CODE = 42000
MAPI = (monetdb) /var/tmp/mtest-577635/.s.monetdb.34590
QUERY = SELECT (VALUES(col1), (col2)) FROM another_t; --error, more than one
row returned by a subquery used as an expression
-ERROR = !Cardinality violation, scalar value expected
-CODE = 21000
-MAPI = (monetdb) /var/tmp/mtest-146231/.s.monetdb.30793
+ERROR = !zero_or_one: cardinality violation, scalar expression expected
+CODE = M0M29
+MAPI = (monetdb) /var/tmp/mtest-2671726/.s.monetdb.31336
QUERY = SELECT integers.i FROM (VALUES(4),(5),(6),(8)) AS integers(i),
integers; --error table integers specified more than once
ERROR = !SELECT: relation name "integers" specified more than once
CODE = 42000
@@ -170,6 +170,18 @@ MAPI = (monetdb) /var/tmp/mtest-1772822
QUERY = SELECT CORR((SELECT i1.i FROM integers i2), (SELECT SUM(i1.i + i2.i)
FROM integers i2)) FROM integers i1;
ERROR = !zero_or_one: cardinality violation, scalar expression expected
CODE = M0M29
+MAPI = (monetdb) /var/tmp/mtest-2671726/.s.monetdb.31336
+QUERY = SELECT (VALUES(SUM(i1.i)) UNION VALUES(AVG(i1.i))) FROM integers i1;
+ERROR = !zero_or_one: cardinality violation, scalar expression expected
+CODE = M0M29
+MAPI = (monetdb) /var/tmp/mtest-2671726/.s.monetdb.31336
+QUERY = SELECT ((SELECT SUM(i1.i)) UNION ALL (SELECT AVG(i1.i))) FROM integers
i1;
+ERROR = !zero_or_one: cardinality violation, scalar expression expected
+CODE = M0M29
+MAPI = (monetdb) /var/tmp/mtest-2671726/.s.monetdb.31336
+QUERY = SELECT ((SELECT i1.i NOT IN (SELECT i1.i)) UNION (SELECT SUM(i1.i) IN
(SELECT i1.i))) FROM integers i1;
+ERROR = !SELECT: subquery uses ungrouped column "i1.i" from outer query
+CODE = 42000
# 08:41:39 >
# 08:41:39 > "Done."
diff --git a/sql/test/subquery/Tests/subquery5.stable.out
b/sql/test/subquery/Tests/subquery5.stable.out
--- a/sql/test/subquery/Tests/subquery5.stable.out
+++ b/sql/test/subquery/Tests/subquery5.stable.out
@@ -281,6 +281,29 @@ stdout of test 'subquery5` in directory
% 5 # length
[ true ]
[ true ]
+#SELECT (VALUES (SUM(i1.i)),(AVG(i1.i)) INTERSECT VALUES(AVG(i1.i))) FROM
integers i1;
+% .%17 # table_name
+% %17 # name
+% double # type
+% 24 # length
+[ 2 ]
+#SELECT SUM(i1.i) FROM integers i1 HAVING (VALUES(SUM(i1.i)),(AVG(i1.i))
INTERSECT VALUES(AVG(i1.i))) > 0;
+% .%1 # table_name
+% %1 # name
+% hugeint # type
+% 1 # length
+[ 6 ]
+#SELECT MAX(i1.i) FROM integers i1 HAVING (VALUES((AVG(i1.i))) EXCEPT
VALUES(AVG(i1.i))) <> 0;
+% .%1 # table_name
+% %1 # name
+% int # type
+% 1 # length
+#SELECT (SELECT 6 EXCEPT (SELECT SUM(i1.i))) IN (SELECT 1) FROM integers i1;
-- OPTmergetableImplementation: !ERROR: Mergetable bailout on group input reuse
in group statement
+% .%24 # table_name
+% %24 # name
+% boolean # type
+% 5 # length
+[ NULL ]
#DROP FUNCTION evilfunction(INT);
#DROP TABLE tbl_ProductSales;
#DROP TABLE another_T;
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
