Changeset: 7158d059e8ac for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=7158d059e8ac
Added Files:
sql/test/BugTracker-2016/Tests/column_name_lost_in_in_operator.Bug-6123.sql
sql/test/BugTracker-2016/Tests/column_name_lost_in_in_operator.Bug-6123.stable.err
sql/test/BugTracker-2016/Tests/column_name_lost_in_in_operator.Bug-6123.stable.out
Modified Files:
sql/server/rel_select.c
sql/test/BugTracker-2016/Tests/All
Branch: Dec2016
Log Message:
fixed bug 6123, ie handle correlated in subqueries in selection better
diffs (truncated from 662 to 300 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
@@ -1989,16 +1989,21 @@ rel_logical_value_exp(mvc *sql, sql_rel
dlist *dl = sc->data.lval;
symbol *lo = dl->h->data.sym;
dnode *n = dl->h->next;
- sql_exp *l = rel_value_exp(sql, rel, lo, f, ek), *r = NULL;
- sql_rel *left = NULL, *right = NULL;
+ sql_rel *left = NULL, *right = NULL, *outer = *rel;
+ sql_exp *l = NULL, *r = NULL;
int needproj = 0, vals_only = 1;
- list *vals = NULL;
-
+ list *vals = NULL, *pexps = NULL;
+
+ if (outer && f == sql_sel && is_project(outer->op) &&
!is_processed(outer) && outer->l && !list_empty(outer->exps)) {
+ needproj = 1;
+ pexps = outer->exps;
+ *rel = outer->l;
+ }
+
+ l = rel_value_exp(sql, rel, lo, f, ek);
if (!l)
return NULL;
-
ek.card = card_set;
-
if (!left)
left = *rel;
@@ -2006,7 +2011,6 @@ rel_logical_value_exp(mvc *sql, sql_rel
needproj = (left != NULL);
left = rel_project_exp(sql->sa, l);
}
-
if (left && is_project(left->op) && list_empty(left->exps))
left = left->l;
@@ -2072,6 +2076,7 @@ rel_logical_value_exp(mvc *sql, sql_rel
e = rel_binop_(sql, e, ne,
NULL, "or", card_value);
}
}
+ *rel = outer;
return e;
}
r = rel_lastexp(sql, right);
@@ -2083,7 +2088,7 @@ rel_logical_value_exp(mvc *sql, sql_rel
e = exp_compare(sql->sa, l, r, cmp_equal );
rel_join_add_exp(sql->sa, left, e);
if (*rel && needproj)
- left = *rel = rel_project(sql->sa, left, NULL);
+ left = *rel = rel_project(sql->sa, left, pexps);
else
*rel = left;
if (sc->token == SQL_NOT_IN)
@@ -2510,9 +2515,6 @@ rel_logical_exp(mvc *sql, sql_rel *rel,
if (!correlated) {
if (right->processed)
right = rel_label(sql, right, 0);
- /*
- right = rel_distinct(right);
- */
}
} else {
return sql_error(sql, 02, "IN: missing inner query");
@@ -2535,13 +2537,10 @@ rel_logical_exp(mvc *sql, sql_rel *rel,
sql_exp *l = n->data;
sql_exp *r = m->data;
- //r = rel_lastexp(sql, right);
- //r = exp_column(sql->sa, exp_relname(r),
exp_name(r), exp_subtype(r), exp_card(r), has_nil(r), is_intern(r));
r = exp_alias_or_copy(sql, exp_relname(r),
exp_name(r), right, r);
if (rel_convert_types(sql, &l, &r, 1,
type_equal) < 0)
return NULL;
e = exp_compare(sql->sa, l, r, cmp_equal );
- //rel_join_add_exp(sql->sa, rel, e);
append(jexps, e);
}
if (correlated && l_is_value) {
diff --git a/sql/test/BugTracker-2016/Tests/All
b/sql/test/BugTracker-2016/Tests/All
--- a/sql/test/BugTracker-2016/Tests/All
+++ b/sql/test/BugTracker-2016/Tests/All
@@ -69,3 +69,4 @@ case-column-when-null-Bug-6124
select-in-from.Bug-6119
select-in-from.Bug-6121
nested-subquery-in-select.Bug-6125
+column_name_lost_in_in_operator.Bug-6123
diff --git
a/sql/test/BugTracker-2016/Tests/column_name_lost_in_in_operator.Bug-6123.sql
b/sql/test/BugTracker-2016/Tests/column_name_lost_in_in_operator.Bug-6123.sql
new file mode 100644
--- /dev/null
+++
b/sql/test/BugTracker-2016/Tests/column_name_lost_in_in_operator.Bug-6123.sql
@@ -0,0 +1,274 @@
+
+/* Relations */
+CREATE TABLE comments(
+ id BIGINT NOT NULL PRIMARY KEY,
+ creationDate TIMESTAMP(3) WITH TIME ZONE NOT NULL,
+ locationIP VARCHAR(40) NOT NULL,
+ browserUsed VARCHAR(40) NOT NULL,
+ content VARCHAR(2000) NOT NULL,
+ length INT NOT NULL,
+ creator BIGINT NOT NULL,
+ place_id BIGINT NOT NULL,
+ replyOfPost BIGINT,
+ replyOfComment BIGINT
+);
+
+CREATE TABLE comment_tags(
+ comment_id BIGINT NOT NULL,
+ tag_id BIGINT NOT NULL,
+ PRIMARY KEY(comment_id, tag_id)
+);
+
+CREATE TABLE forums(
+ id BIGINT NOT NULL PRIMARY KEY,
+ title VARCHAR(80) NOT NULL,
+ creationDate TIMESTAMP(3) WITH TIME ZONE NOT NULL,
+ moderator BIGINT /*though it seems that all generated tuples have always a
moderator */
+);
+
+CREATE TABLE forum_persons(
+ forum_id BIGINT NOT NULL,
+ person_id BIGINT NOT NULL,
+ joinDate TIMESTAMP(3) WITH TIME ZONE NOT NULL,
+ PRIMARY KEY(forum_id, person_id)
+);
+
+CREATE TABLE forum_tags(
+ forum_id BIGINT NOT NULL,
+ tag_id BIGINT NOT NULL,
+ PRIMARY KEY(forum_id, tag_id)
+);
+
+CREATE TABLE friends(
+ src BIGINT NOT NULL,
+ dst BIGINT NOT NULL,
+ creationDate TIMESTAMP(3) WITH TIME ZONE NOT NULL,
+ PRIMARY KEY(src, dst)
+);
+
+CREATE TABLE organisations(
+ id BIGINT NOT NULL PRIMARY KEY,
+ "type" VARCHAR(40) NOT NULL, /* university or company */
+ name VARCHAR(160) NOT NULL,
+ url VARCHAR(2000) NOT NULL,
+ place_id BIGINT NOT NULL
+);
+
+CREATE TABLE persons(
+ id BIGINT NOT NULL PRIMARY KEY,
+ firstName VARCHAR(40) NOT NULL ,
+ lastName VARCHAR(40) NOT NULL,
+ gender VARCHAR(40) NOT NULL,
+ birthDay DATE NOT NULL,
+ creationDate TIMESTAMP(3) WITH TIME ZONE NOT NULL,
+ locationIP VARCHAR(40) NOT NULL,
+ browserUsed VARCHAR(40) NOT NULL,
+ place_id BIGINT NOT NULL
+);
+
+CREATE TABLE person_comments(
+ person_id BIGINT NOT NULL,
+ comment_id BIGINT NOT NULL,
+ creationDate TIMESTAMP(3) WITH TIME ZONE NOT NULL,
+ PRIMARY KEY(person_id, comment_id)
+);
+
+CREATE TABLE person_emails(
+ id BIGINT NOT NULL,
+ email VARCHAR(120) NOT NULL,
+ PRIMARY KEY(id, email)
+);
+
+CREATE TABLE person_languages(
+ id BIGINT NOT NULL,
+ "language" VARCHAR(40) NOT NULL,
+ PRIMARY KEY(id, "language")
+);
+
+CREATE TABLE person_tags(
+ person_id BIGINT NOT NULL,
+ tag_id BIGINT NOT NULL,
+ PRIMARY KEY(person_id, tag_id)
+);
+
+CREATE TABLE person_posts(
+ person_id BIGINT NOT NULL,
+ post_id BIGINT NOT NULL,
+ creationDate TIMESTAMP(3) WITH TIME ZONE NOT NULL,
+ PRIMARY KEY(person_id, post_id)
+);
+
+CREATE TABLE person_studyAt_organisations(
+ person_id BIGINT NOT NULL,
+ organisation_id BIGINT NOT NULL,
+ classYear INT NOT NULL,
+ PRIMARY KEY(person_id, organisation_id)
+);
+
+CREATE TABLE person_workAt_organisations(
+ person_id BIGINT NOT NULL,
+ organisation_id BIGINT NOT NULL,
+ workFrom INT NOT NULL,
+ PRIMARY KEY(person_id, organisation_id)
+);
+
+CREATE TABLE places(
+ id BIGINT NOT NULL PRIMARY KEY,
+ name VARCHAR(200) NOT NULL,
+ url VARCHAR(2000) NOT NULL, /* suspecting this is a left-over for RDF
systems where this field replaces the ID */
+ "type" VARCHAR(40) NOT NULL,
+ isPartOf BIGINT
+);
+
+CREATE TABLE posts(
+ id BIGINT NOT NULL PRIMARY KEY,
+ imageFile VARCHAR(40),
+ creationDate TIMESTAMP(3) WITH TIME ZONE NOT NULL,
+ locationIP VARCHAR(40) NOT NULL,
+ browserUsed VARCHAR(40) NOT NULL,
+ "language" VARCHAR(40),
+ content VARCHAR(2000),
+ length INT NOT NULL,
+ creator BIGINT NOT NULL,
+ forum_id BIGINT NOT NULL,
+ place_id BIGINT NOT NULL
+);
+
+CREATE TABLE post_tags(
+ post_id BIGINT NOT NULL,
+ tag_id BIGINT NOT NULL,
+ PRIMARY KEY(post_id, tag_id)
+);
+
+CREATE TABLE tags(
+ id BIGINT NOT NULL PRIMARY KEY,
+ name VARCHAR(160) NOT NULL,
+ url VARCHAR(2000) NOT NULL /* is this field mandatory ? */
+);
+
+CREATE TABLE tag_tagclasses(
+ tag_id BIGINT NOT NULL,
+ tagclass_id BIGINT NOT NULL,
+ PRIMARY KEY(tag_id, tagclass_id)
+);
+
+CREATE TABLE tagclasses(
+ id BIGINT NOT NULL PRIMARY KEY,
+ name VARCHAR(40) NOT NULL,
+ url VARCHAR(2000) NOT NULL /* is this field mandatory ? */
+);
+
+CREATE TABLE tagclass_inheritance(
+ subclass_id BIGINT NOT NULL,
+ superclass_id BIGINT NOT NULL,
+ PRIMARY KEY(subclass_id, superclass_id)
+);
+
+/* Foreign keys */
+ALTER TABLE comments ADD FOREIGN KEY(creator) REFERENCES persons(id);
+ALTER TABLE comments ADD FOREIGN KEY(place_id) REFERENCES places(id);
+ALTER TABLE comments ADD FOREIGN KEY(replyOfPost) REFERENCES posts(id);
+ALTER TABLE comments ADD FOREIGN KEY(replyOfComment) REFERENCES comments(id);
+ALTER TABLE comment_tags ADD FOREIGN KEY(comment_id) REFERENCES comments(id);
+ALTER TABLE comment_tags ADD FOREIGN KEY(tag_id) REFERENCES tags(id);
+ALTER TABLE forums ADD FOREIGN KEY(moderator) REFERENCES persons(id);
+ALTER TABLE forum_persons ADD FOREIGN KEY(forum_id) REFERENCES forums(id);
+ALTER TABLE forum_persons ADD FOREIGN KEY(person_id) REFERENCES persons(id);
+ALTER TABLE forum_tags ADD FOREIGN KEY(forum_id) REFERENCES forums(id);
+ALTER TABLE forum_tags ADD FOREIGN KEY(tag_id) REFERENCES tags(id);
+ALTER TABLE friends ADD FOREIGN KEY(src) REFERENCES persons(id);
+ALTER TABLE friends ADD FOREIGN KEY(dst) REFERENCES persons(id);
+ALTER TABLE organisations ADD FOREIGN KEY(place_id) REFERENCES places(id);
+ALTER TABLE persons ADD FOREIGN KEY(place_id) REFERENCES places(id);
+ALTER TABLE person_comments ADD FOREIGN KEY(person_id) REFERENCES persons(id);
+ALTER TABLE person_comments ADD FOREIGN KEY(comment_id) REFERENCES
comments(id);
+ALTER TABLE person_emails ADD FOREIGN KEY(id) REFERENCES persons(id);
+ALTER TABLE person_languages ADD FOREIGN KEY(id) REFERENCES persons(id);
+ALTER TABLE person_posts ADD FOREIGN KEY(person_id) REFERENCES persons(id);
+ALTER TABLE person_posts ADD FOREIGN KEY(post_id) REFERENCES posts(id);
+ALTER TABLE person_studyAt_organisations ADD FOREIGN KEY(person_id) REFERENCES
persons(id);
+ALTER TABLE person_studyAt_organisations ADD FOREIGN KEY(organisation_id)
REFERENCES organisations(id);
+ALTER TABLE person_tags ADD FOREIGN KEY(person_id) REFERENCES persons(id);
+ALTER TABLE person_tags ADD FOREIGN KEY(tag_id) REFERENCES tags(id);
+ALTER TABLE person_workAt_organisations ADD FOREIGN KEY(person_id) REFERENCES
persons(id);
+ALTER TABLE person_workAt_organisations ADD FOREIGN KEY(organisation_id)
REFERENCES organisations(id);
+ALTER TABLE places ADD FOREIGN KEY(isPartOf) REFERENCES places(id);
+ALTER TABLE posts ADD FOREIGN KEY(creator) REFERENCES persons(id);
+ALTER TABLE posts ADD FOREIGN KEY(forum_id) REFERENCES forums(id);
+ALTER TABLE posts ADD FOREIGN KEY(place_id) REFERENCES places(id);
+ALTER TABLE post_tags ADD FOREIGN KEY(post_id) REFERENCES posts(id);
+ALTER TABLE post_tags ADD FOREIGN KEY(tag_id) REFERENCES tags(id);
+ALTER TABLE tag_tagclasses ADD FOREIGN KEY(tag_id) REFERENCES tags(id);
+ALTER TABLE tag_tagclasses ADD FOREIGN KEY(tagclass_id) REFERENCES
tagclasses(id);
+ALTER TABLE tagclass_inheritance ADD FOREIGN KEY(subclass_id) REFERENCES
tagclasses(id);
+ALTER TABLE tagclass_inheritance ADD FOREIGN KEY(superclass_id) REFERENCES
tagclasses(id);
+
+WITH
+params AS ( SELECT 19791209310216 AS id),
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
