Changeset: f05d3ecc346c for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=f05d3ecc346c
Modified Files:
sql/backends/monet5/sql_statement.c
sql/server/rel_unnest.c
sql/test/BugTracker-2015/Tests/crash.Bug-3736.stable.out
Branch: subquery
Log Message:
fixed move of join expressions (move up)
diffs (190 lines):
diff --git a/sql/backends/monet5/sql_statement.c
b/sql/backends/monet5/sql_statement.c
--- a/sql/backends/monet5/sql_statement.c
+++ b/sql/backends/monet5/sql_statement.c
@@ -1478,9 +1478,11 @@ stmt_uselect(backend *be, stmt *op1, stm
q = pushArgument(mb, q, sub->nr);
q = pushArgument(mb, q, r);
switch (cmptype) {
+ case mark_in:
case cmp_equal:
q = pushStr(mb, q, anti?"!=":"==");
break;
+ case mark_notin:
case cmp_notequal:
q = pushStr(mb, q, anti?"==":"!=");
break;
@@ -1861,8 +1863,8 @@ stmt_join(backend *be, stmt *op1, stmt *
return NULL;
switch (cmptype) {
+ case mark_in:
case cmp_equal:
- case mark_in:
q = newStmt(mb, algebraRef, sjt);
q = pushReturn(mb, q, newTmpVariable(mb, TYPE_any));
q = pushArgument(mb, q, op1->nr);
@@ -1886,8 +1888,8 @@ stmt_join(backend *be, stmt *op1, stmt *
if (q == NULL)
return NULL;
break;
+ case mark_notin:
case cmp_notequal:
- case mark_notin:
q = newStmt(mb, algebraRef, antijoinRef);
q = pushReturn(mb, q, newTmpVariable(mb, TYPE_any));
q = pushArgument(mb, q, op1->nr);
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
@@ -579,10 +579,10 @@ push_up_join(mvc *sql, sql_rel *rel)
set_dependent(j);
n = rel_crossproduct(sql->sa, rel, j, j->op);
j->op = rel->op;
- move_join_exps(sql, n, j);
n->l = rel_project(sql->sa, n->l,
rel_projections(sql, n->l, NULL, 1, 1));
nr = n->r;
nr = n->r = rel_project(sql->sa, n->r,
rel_projections(sql, nr->r, NULL, 1, 1));
+ move_join_exps(sql, n, j);
/* add nr->l exps with labels */
/* create jexps */
if (!n->exps)
@@ -663,7 +663,14 @@ push_up_table(mvc *sql, sql_rel *rel)
/* for now just push d into function */
if (d && need_distinct(d) && tf && is_base(tf->op)) {
- tf->l = rel_dup(d);
+ if (tf->l) {
+ sql_rel *l = tf->l;
+
+ assert(!l->l);
+ l->l = rel_dup(d);
+ } else {
+ tf->l = rel_dup(d);
+ }
return rel;
}
}
@@ -688,7 +695,7 @@ rel_general_unnest(mvc *sql, sql_rel *re
if (is_semi(rel->op)) {
if (rel->op == op_semi)
r->op = op_join;
- move_join_exps(sql, r, rel);
+ move_join_exps(sql, rel, r);
}
set_dependent(r);
r = rel_project(sql->sa, r,
(is_semi(r->op))?sa_list(sql->sa):rel_projections(sql, r->r, NULL, 1, 1));
diff --git a/sql/test/BugTracker-2015/Tests/crash.Bug-3736.stable.out
b/sql/test/BugTracker-2015/Tests/crash.Bug-3736.stable.out
--- a/sql/test/BugTracker-2015/Tests/crash.Bug-3736.stable.out
+++ b/sql/test/BugTracker-2015/Tests/crash.Bug-3736.stable.out
@@ -68,46 +68,74 @@ Ready.
% .plan # table_name
% rel # name
% clob # type
-% 438 # length
+% 314 # length
+REF 1 (2)
+crossproduct (
+| table(sys.open_auctions) [ "open_auctions"."id" NOT NULL HASHCOL as
"o"."id", "open_auctions"."open_auction_id" NOT NULL as "o"."open_auction_id" ]
COUNT ,
+| table(sys.bidder) [ "bidder"."id" NOT NULL HASHCOL as "b"."id",
"bidder"."open_auction_id" NOT NULL as "b"."open_auction_id", "bidder"."date"
NOT NULL as "b"."date", "bidder"."time" NOT NULL as "b"."time",
"bidder"."personref" NOT NULL as "b"."personref", "bidder"."increase" NOT NULL
as "b"."increase" ] COUNT
+) [ ]
+REF 2 (2)
+group by (
+| project (
+| | & REF 1
+| ) [ "o"."open_auction_id" NOT NULL ]
+) [ "o"."open_auction_id" NOT NULL ] [ "o"."open_auction_id" NOT NULL ]
+REF 3 (2)
project (
-| select (
+| join (
+| | & REF 1 ,
+| | project (
+| | | semijoin (
+| | | | project (
+| | | | | crossproduct (
+| | | | | | & REF 2 ,
+| | | | | | table(sys.bidder) [ "bidder"."id" NOT NULL HASHCOL as "b3"."id",
"bidder"."increase" NOT NULL as "b3"."increase" ] COUNT
+| | | | | ) [ ]
+| | | | ) [ "o"."open_auction_id" NOT NULL, "b3"."id" NOT NULL HASHCOL ,
"b3"."increase" NOT NULL ],
+| | | | project (
+| | | | | group by (
+| | | | | | join (
+| | | | | | | table(sys.bidder) [ "bidder"."id" NOT NULL HASHCOL as
"b3a"."id", "bidder"."open_auction_id" NOT NULL as "b3a"."open_auction_id" ]
COUNT ,
+| | | | | | | & REF 2
+| | | | | | ) [ "b3a"."open_auction_id" NOT NULL = "o"."open_auction_id" NOT
NULL ]
+| | | | | ) [ "o"."open_auction_id" NOT NULL ] [ sys.min no nil ("b3a"."id"
NOT NULL HASHCOL ) NOT NULL as "L3"."L3", "o"."open_auction_id" NOT NULL ]
+| | | | ) [ "L3"."L3" NOT NULL, "o"."open_auction_id" NOT NULL as "L25"."L25" ]
+| | | ) [ "b3"."id" NOT NULL HASHCOL = "L3"."L3" NOT NULL,
"o"."open_auction_id" NOT NULL = "L25"."L25" NOT NULL ]
+| | ) [ "b3"."increase" NOT NULL, "o"."open_auction_id" NOT NULL as
"L20"."L20" ]
+| ) [ "o"."open_auction_id" NOT NULL = "L20"."L20" NOT NULL ]
+) [ "o"."open_auction_id" NOT NULL, "b"."id" NOT NULL HASHCOL ,
"b"."open_auction_id" NOT NULL, "b"."date" NOT NULL, "b"."time" NOT NULL,
"b"."personref" NOT NULL, "b"."increase" NOT NULL, "b3"."increase" NOT NULL ]
+REF 4 (2)
+group by (
+| project (
+| | & REF 3
+| ) [ "o"."open_auction_id" NOT NULL ]
+) [ "o"."open_auction_id" NOT NULL ] [ "o"."open_auction_id" NOT NULL ]
+project (
+| join (
| | project (
| | | select (
-| | | | group by (
-| | | | | join (
-| | | | | | table(sys.bidder) [ "bidder"."id" NOT NULL HASHCOL as "b2a"."id",
"bidder"."open_auction_id" NOT NULL as "b2a"."open_auction_id" ] COUNT ,
-| | | | | | project (
-| | | | | | | crossproduct (
-| | | | | | | | project (
-| | | | | | | | | project (
-| | | | | | | | | | select (
-| | | | | | | | | | | group by (
-| | | | | | | | | | | | join (
-| | | | | | | | | | | | | project (
-| | | | | | | | | | | | | | crossproduct (
-| | | | | | | | | | | | | | | project (
-| | | | | | | | | | | | | | | | join (
-| | | | | | | | | | | | | | | | | table(sys.open_auctions) [
"open_auctions"."id" NOT NULL HASHCOL as "o"."id",
"open_auctions"."open_auction_id" NOT NULL as "o"."open_auction_id" ] COUNT ,
-| | | | | | | | | | | | | | | | | table(sys.bidder) [ "bidder"."id" NOT NULL
HASHCOL as "b"."id", "bidder"."open_auction_id" NOT NULL as
"b"."open_auction_id", "bidder"."date" NOT NULL as "b"."date", "bidder"."time"
NOT NULL as "b"."time", "bidder"."personref" NOT NULL as "b"."personref",
"bidder"."increase" NOT NULL as "b"."increase" ] COUNT
-| | | | | | | | | | | | | | | | ) [ "o"."open_auction_id" NOT NULL =
"b"."open_auction_id" NOT NULL ]
-| | | | | | | | | | | | | | | ) [ "o"."id" NOT NULL HASHCOL ,
"o"."open_auction_id" NOT NULL, "b"."id" NOT NULL HASHCOL ,
"b"."open_auction_id" NOT NULL, "b"."date" NOT NULL, "b"."time" NOT NULL,
"b"."personref" NOT NULL, "b"."increase" NOT NULL, sys.identity("o"."id" NOT
NULL) HASHCOL as "L47"."L47" ],
-| | | | | | | | | | | | | | | table(sys.bidder) [ "bidder"."id" NOT NULL
HASHCOL as "b3"."id", "bidder"."increase" NOT NULL as "b3"."increase" ] COUNT
-| | | | | | | | | | | | | | ) [ ]
-| | | | | | | | | | | | | ) [ "L47"."L47" HASHCOL , "o"."id" NOT NULL HASHCOL
, "o"."open_auction_id" NOT NULL, "b"."id" NOT NULL HASHCOL ,
"b"."open_auction_id" NOT NULL, "b"."date" NOT NULL, "b"."time" NOT NULL,
"b"."personref" NOT NULL, "b"."increase" NOT NULL, "b3"."id" NOT NULL HASHCOL ,
"b3"."increase" NOT NULL, sys.identity("o"."id" NOT NULL) HASHCOL as
"L37"."L37" ],
-| | | | | | | | | | | | | table(sys.bidder) [ "bidder"."id" NOT NULL HASHCOL
as "b3a"."id", "bidder"."open_auction_id" NOT NULL as "b3a"."open_auction_id" ]
COUNT
-| | | | | | | | | | | | ) [ "b3a"."open_auction_id" NOT NULL =
"o"."open_auction_id" NOT NULL ]
-| | | | | | | | | | | ) [ "L47"."L47", "L37"."L37" ] [ "L47"."L47" HASHCOL ,
"o"."id" NOT NULL HASHCOL , "o"."open_auction_id" NOT NULL, "b"."id" NOT NULL
HASHCOL , "b"."open_auction_id" NOT NULL, "b"."date" NOT NULL, "b"."time" NOT
NULL, "b"."personref" NOT NULL, "b"."increase" NOT NULL, "b3"."id" NOT NULL
HASHCOL , "b3"."increase" NOT NULL, "L37"."L37" HASHCOL , sys.min no nil
("b3a"."id" NOT NULL HASHCOL ) NOT NULL as "L10"."L10" ]
-| | | | | | | | | | ) [ "b3"."id" NOT NULL HASHCOL = "L10"."L10" NOT NULL ]
-| | | | | | | | | ) [ "o"."id" NOT NULL HASHCOL , "o"."open_auction_id" NOT
NULL, "b"."id" NOT NULL HASHCOL , "b"."open_auction_id" NOT NULL, "b"."date"
NOT NULL, "b"."time" NOT NULL, "b"."personref" NOT NULL, "b"."increase" NOT
NULL, "b3"."increase" NOT NULL as "L12"."L12" ]
-| | | | | | | | ) [ "o"."id" NOT NULL HASHCOL , "o"."open_auction_id" NOT
NULL, "b"."id" NOT NULL HASHCOL , "b"."open_auction_id" NOT NULL, "b"."date"
NOT NULL, "b"."time" NOT NULL, "b"."personref" NOT NULL, "b"."increase" NOT
NULL, "L12"."L12" NOT NULL, sys.identity("o"."id" NOT NULL) HASHCOL as
"L63"."L63" ],
-| | | | | | | | table(sys.bidder) [ "bidder"."id" NOT NULL HASHCOL as
"b2"."id", "bidder"."increase" NOT NULL as "b2"."increase" ] COUNT
-| | | | | | | ) [ ]
-| | | | | | ) [ "L63"."L63" HASHCOL , "o"."id" NOT NULL HASHCOL ,
"o"."open_auction_id" NOT NULL, "b"."id" NOT NULL HASHCOL ,
"b"."open_auction_id" NOT NULL, "b"."date" NOT NULL, "b"."time" NOT NULL,
"b"."personref" NOT NULL, "b"."increase" NOT NULL, "L12"."L12" NOT NULL,
"b2"."id" NOT NULL HASHCOL , "b2"."increase" NOT NULL, sys.identity("o"."id"
NOT NULL) HASHCOL as "L56"."L56" ]
-| | | | | ) [ "b2a"."open_auction_id" NOT NULL = "o"."open_auction_id" NOT
NULL ]
-| | | | ) [ "L63"."L63", "L56"."L56" ] [ "L63"."L63" HASHCOL , "b"."id" NOT
NULL HASHCOL , "b"."open_auction_id" NOT NULL, "b"."date" NOT NULL, "b"."time"
NOT NULL, "b"."personref" NOT NULL, "b"."increase" NOT NULL, "L12"."L12" NOT
NULL, "b2"."id" NOT NULL HASHCOL , "b2"."increase" NOT NULL, "L56"."L56"
HASHCOL , sys.max no nil ("b2a"."id" NOT NULL HASHCOL ) NOT NULL as "L23"."L23"
]
-| | | ) [ "b2"."id" NOT NULL HASHCOL = "L23"."L23" NOT NULL ]
-| | ) [ "L63"."L63" HASHCOL , "b"."id" NOT NULL HASHCOL ,
"b"."open_auction_id" NOT NULL, "b"."date" NOT NULL, "b"."time" NOT NULL,
"b"."personref" NOT NULL, "b"."increase" NOT NULL, "L12"."L12" NOT NULL,
"b2"."id" NOT NULL HASHCOL , "b2"."increase" NOT NULL, "L56"."L56" HASHCOL ,
"L23"."L23" NOT NULL, sys.sql_mul("L12"."L12" NOT NULL, double "2") as
"L70"."L70", "b2"."increase" NOT NULL as "L71"."L71" ]
-| ) [ "L70"."L70" <= "L71"."L71" NOT NULL ]
+| | | | & REF 3
+| | | ) [ "o"."open_auction_id" NOT NULL = "b"."open_auction_id" NOT NULL ]
+| | ) [ "o"."open_auction_id" NOT NULL, "b"."id" NOT NULL HASHCOL ,
"b"."open_auction_id" NOT NULL, "b"."date" NOT NULL, "b"."time" NOT NULL,
"b"."personref" NOT NULL, "b"."increase" NOT NULL, "b3"."increase" NOT NULL,
sys.sql_mul("b3"."increase" NOT NULL, double "2") as "L45"."L45" ],
+| | project (
+| | | semijoin (
+| | | | project (
+| | | | | crossproduct (
+| | | | | | & REF 4 ,
+| | | | | | table(sys.bidder) [ "bidder"."id" NOT NULL HASHCOL as "b2"."id",
"bidder"."increase" NOT NULL as "b2"."increase" ] COUNT
+| | | | | ) [ ]
+| | | | ) [ "o"."open_auction_id" NOT NULL, "b2"."id" NOT NULL HASHCOL ,
"b2"."increase" NOT NULL ],
+| | | | project (
+| | | | | group by (
+| | | | | | join (
+| | | | | | | table(sys.bidder) [ "bidder"."id" NOT NULL HASHCOL as
"b2a"."id", "bidder"."open_auction_id" NOT NULL as "b2a"."open_auction_id" ]
COUNT ,
+| | | | | | | & REF 4
+| | | | | | ) [ "b2a"."open_auction_id" NOT NULL = "o"."open_auction_id" NOT
NULL ]
+| | | | | ) [ "o"."open_auction_id" NOT NULL ] [ sys.max no nil ("b2a"."id"
NOT NULL HASHCOL ) NOT NULL as "L7"."L7", "o"."open_auction_id" NOT NULL ]
+| | | | ) [ "L7"."L7" NOT NULL, "o"."open_auction_id" NOT NULL as "L37"."L37" ]
+| | | ) [ "b2"."id" NOT NULL HASHCOL = "L7"."L7" NOT NULL,
"o"."open_auction_id" NOT NULL = "L37"."L37" NOT NULL ]
+| | ) [ "b2"."increase" NOT NULL, "o"."open_auction_id" NOT NULL as
"L32"."L32", "b2"."increase" NOT NULL as "L46"."L46" ]
+| ) [ "L45"."L45" <= "L46"."L46" NOT NULL, "o"."open_auction_id" NOT NULL =
"L32"."L32" NOT NULL ]
) [ "b"."id" NOT NULL HASHCOL , "b"."open_auction_id" NOT NULL, "b"."date" NOT
NULL, "b"."time" NOT NULL, "b"."personref" NOT NULL, "b"."increase" NOT NULL ]
#Select b.* FROM open_auctions o, bidder b WHERE (select b3.INCREASE from
bidder b3 where b3.id = (select min (b3a.id) from bidder b3a where
b3a.open_auction_id = o.open_auction_id)) * 2 <= (Select b2.INCREASE from
bidder b2 where b2.id = (SELECT MAX (b2a.id) from bidder b2a where
b2a.open_auction_id = o.open_auction_id)) AND o.open_auction_id =
b.open_auction_id order by date, time;
% sys.b, sys.b, sys.b, sys.b, sys.b, sys.b # table_name
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
