Changeset: 6b4e76ced517 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=6b4e76ced517
Modified Files:
sql/server/rel_select.c
sql/test/BugTracker-2015/Tests/project_rewrite.Bug-3693.sql
sql/test/subquery/Tests/subquery5.sql
sql/test/subquery/Tests/subquery5.stable.err
Branch: Jun2020
Log Message:
Small bugfix, disallow relations with the same name on the FROM clause. It
leads into ambiguous cases
diffs (93 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
@@ -5628,10 +5628,11 @@ rel_query(sql_query *query, sql_rel *rel
if (sn->from) { /* keep variable list with tables and names */
dlist *fl = sn->from->data.lval;
- dnode *n = NULL;
sql_rel *fnd = NULL;
-
- for (n = fl->h; n ; n = n->next) {
+ list *names = new_exp_list(sql->sa);
+
+ for (dnode *n = fl->h; n ; n = n->next) {
+ char *nrame = NULL;
int lateral = check_is_lateral(n->data.sym);
/* just used current expression */
@@ -5647,6 +5648,14 @@ rel_query(sql_query *query, sql_rel *rel
}
if (!fnd)
break;
+ if ((nrame = (char*) rel_name(fnd))) {
+ if (list_find(names, nrame, (fcmp) &strcmp)) {
+ if (res)
+ rel_destroy(res);
+ return sql_error(sql, 01,
SQLSTATE(42000) "SELECT: relation name \"%s\" specified more than once", nrame);
+ } else
+ list_append(names, nrame);
+ }
if (res) {
res = rel_crossproduct(sql->sa, res, fnd,
op_join);
if (lateral)
@@ -5918,6 +5927,9 @@ rel_crossquery(sql_query *query, sql_rel
if (!t1 || !t2)
return NULL;
+ if (rel_name(t1) && rel_name(t2) && strcmp(rel_name(t1), rel_name(t2))
== 0)
+ return sql_error(sql, 02, SQLSTATE(42000) "SELECT: '%s' on both
sides of the CROSS JOIN expression", rel_name(t1));
+
return rel_crossproduct(sql->sa, t1, t2, op_join);
}
diff --git a/sql/test/BugTracker-2015/Tests/project_rewrite.Bug-3693.sql
b/sql/test/BugTracker-2015/Tests/project_rewrite.Bug-3693.sql
--- a/sql/test/BugTracker-2015/Tests/project_rewrite.Bug-3693.sql
+++ b/sql/test/BugTracker-2015/Tests/project_rewrite.Bug-3693.sql
@@ -57,7 +57,7 @@ CREATE VIEW output_1427727864562__386719
CREATE VIEW output_1427727864562_7461402036329501989 AS SELECT a2 AS a1, a4 AS
a2, prob AS prob FROM output_1427727864562__3867191803197065991;
-CREATE VIEW output_1427727864562_664384226664078002 AS SELECT
output_1427727864562_7461402036329501989.a1 AS a1,
output_1427727864562_7461402036329501989.a2 AS a2,
output_1427727864562_7461402036329501989.a1 AS a3,
output_1427727864562_7461402036329501989.a2 AS a4,
output_1427727864562_7461402036329501989.prob *
output_1427727864562_7461402036329501989.prob AS prob FROM
output_1427727864562_7461402036329501989,output_1427727864562_7461402036329501989
WHERE output_1427727864562_7461402036329501989.a2 =
output_1427727864562_7461402036329501989.a2;
+CREATE VIEW output_1427727864562_664384226664078002 AS SELECT
output_1427727864562_7461402036329501989.a1 AS a1,
output_1427727864562_7461402036329501989.a2 AS a2,
output_1427727864562_7461402036329501989.a1 AS a3,
output_1427727864562_7461402036329501989.a2 AS a4,
output_1427727864562_7461402036329501989.prob *
output_1427727864562_7461402036329501989.prob AS prob FROM
output_1427727864562_7461402036329501989,output_1427727864562_7461402036329501989
myalias WHERE output_1427727864562_7461402036329501989.a2 =
output_1427727864562_7461402036329501989.a2;
CREATE VIEW output_1427727864562_8055303038742365054 AS SELECT a1 AS a1, a3 AS
a2, sum(prob) AS prob FROM output_1427727864562_664384226664078002 GROUP BY a1,
a3;
diff --git a/sql/test/subquery/Tests/subquery5.sql
b/sql/test/subquery/Tests/subquery5.sql
--- a/sql/test/subquery/Tests/subquery5.sql
+++ b/sql/test/subquery/Tests/subquery5.sql
@@ -139,6 +139,12 @@ SELECT (VALUES(col1, col2)) FROM another
SELECT (VALUES(col1), (col2)) FROM another_t; --error, more than one row
returned by a subquery used as an expression
+SELECT integers.i FROM (VALUES(4),(5),(6),(8)) AS integers(i), integers;
--error table integers specified more than once
+
+SELECT integers.i FROM integers, (VALUES(4)) AS myt(i), (SELECT 1) AS
integers(i); --error table integers specified more than once
+
+SELECT 1 FROM integers CROSS JOIN integers; --error table integers specified
more than once
+
SELECT * FROM integers i1 LEFT OUTER JOIN integers i2 ON i2.i = ANY(SELECT
SUM(i2.i + i3.i) FROM integers i3) = NOT EXISTS(SELECT MIN(i1.i) OVER ());
-- 1 3
-- 1 2
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
@@ -118,6 +118,18 @@ MAPI = (monetdb) /var/tmp/mtest-577635/
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
+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
+MAPI = (monetdb) /var/tmp/mtest-146231/.s.monetdb.30793
+QUERY = SELECT integers.i FROM integers, (VALUES(4)) AS myt(i), (SELECT 1) AS
integers(i); --error table integers specified more than once
+ERROR = !SELECT: relation name "integers" specified more than once
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-146231/.s.monetdb.30793
+QUERY = SELECT 1 FROM integers CROSS JOIN integers; --error table integers
specified more than once
+ERROR = !SELECT: 'integers' on both sides of the CROSS JOIN expression
+CODE = 42000
# 08:41:39 >
# 08:41:39 > "Done."
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
