Changeset: 2153348bd11a for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=2153348bd11a
Modified Files:
monetdb5/optimizer/opt_mergetable.c
sql/server/rel_optimizer.c
sql/server/rel_select.c
sql/server/rel_updates.c
sql/test/BugTracker-2015/Tests/crash.Bug-3736.stable.err
sql/test/BugTracker-2015/Tests/crash.Bug-3736.stable.out
sql/test/BugTracker-2015/Tests/date_comparison_incorrect_results.Bug-3834.stable.out
sql/test/BugTracker-2015/Tests/update_with_without_parenthesis.Bug-3838.stable.out
Branch: Jul2015
Log Message:
fix bug 3838, ie handle outer joins with updates
diffs (truncated from 332 to 300 lines):
diff --git a/monetdb5/optimizer/opt_mergetable.c
b/monetdb5/optimizer/opt_mergetable.c
--- a/monetdb5/optimizer/opt_mergetable.c
+++ b/monetdb5/optimizer/opt_mergetable.c
@@ -847,6 +847,7 @@ mat_group_project(MalBlkPtr mb, InstrPtr
getArg(q,1) = getArg(mat[e].mi,k);
getArg(q,2) = getArg(mat[a].mi,k);
pushInstruction(mb,q);
+ setPartnr(mb, getArg(mat[a].mi,k), getArg(q,0), k);
/* pack the result into a mat */
ai1 = pushArgument(mb,ai1,getArg(q,0));
@@ -1122,6 +1123,9 @@ mat_group_new(MalBlkPtr mb, InstrPtr p,
getArg(q, 2) = newTmpVariable(mb, tp2);
getArg(q, 3) = getArg(ml->v[b].mi, i);
pushInstruction(mb, q);
+ setPartnr(mb, getArg(ml->v[b].mi,i), getArg(q,0), i);
+ setPartnr(mb, getArg(ml->v[b].mi,i), getArg(q,1), i);
+ setPartnr(mb, getArg(ml->v[b].mi,i), getArg(q,2), i);
/* add result to mats */
r0 = pushArgument(mb,r0,getArg(q,0));
@@ -1132,9 +1136,9 @@ mat_group_new(MalBlkPtr mb, InstrPtr p,
setModuleId(r, algebraRef);
setFunctionId(r, leftfetchjoinRef);
getArg(r, 0) = newTmpVariable(mb, atp);
-
r = pushArgument(mb, r, getArg(q,1));
r = pushArgument(mb, r, getArg(ml->v[b].mi,i));
+ setPartnr(mb, getArg(ml->v[b].mi,i), getArg(r,0), i);
pushInstruction(mb,r);
attr = pushArgument(mb, attr, getArg(r, 0));
@@ -1206,6 +1210,9 @@ mat_group_derive(MalBlkPtr mb, InstrPtr
getArg(q,3) = getArg(ml->v[b].mi,i);
getArg(q,4) = getArg(ml->v[g].mi,i);
pushInstruction(mb,q);
+ setPartnr(mb, getArg(ml->v[b].mi,i), getArg(q,0), i);
+ setPartnr(mb, getArg(ml->v[b].mi,i), getArg(q,1), i);
+ setPartnr(mb, getArg(ml->v[b].mi,i), getArg(q,2), i);
/* add result to mats */
r0 = pushArgument(mb,r0,getArg(q,0));
@@ -1216,9 +1223,9 @@ mat_group_derive(MalBlkPtr mb, InstrPtr
setModuleId(r, algebraRef);
setFunctionId(r, leftfetchjoinRef);
getArg(r, 0) = newTmpVariable(mb, atp);
-
r = pushArgument(mb, r, getArg(q,1));
r = pushArgument(mb, r, getArg(ml->v[b].mi,i));
+ setPartnr(mb, getArg(ml->v[b].mi,i), getArg(r,0), i);
pushInstruction(mb,r);
attr = pushArgument(mb, attr, getArg(r, 0));
diff --git a/sql/server/rel_optimizer.c b/sql/server/rel_optimizer.c
--- a/sql/server/rel_optimizer.c
+++ b/sql/server/rel_optimizer.c
@@ -7094,6 +7094,20 @@ rel_add_identity(mvc *sql, sql_rel *rel,
return rel;
}
+static int
+exps_from_rel( list *exps, sql_rel *rel )
+{
+ node *n;
+
+ if (!rel || !exps)
+ return 0;
+ for (n=exps->h; n; n=n->next) {
+ sql_exp *e = n->data;
+ if (rel_find_exp(rel, e) )
+ return 1;
+ }
+ return 0;
+}
/* push down apply until its gone */
static sql_rel *
@@ -7145,6 +7159,31 @@ rel_apply_rewrite(int *changes, mvc *sql
(*changes)++;
return nrel;
}
+ if (rel->flag == APPLY_LOJ && r->op == op_project &&
exps_from_rel(r->exps, rel->l)) {
+ sql_exp *ident, *le = NULL;
+ sql_rel *nrel = rel_add_identity(sql, l, &ident), *arel = rel;
+
+ rel->l = nrel;
+ ident = exp_column(sql->sa, exp_relname(ident),
exp_name(ident), exp_subtype(ident), ident->card, has_nil(ident),
is_intern(ident));
+
+ rel = rel_project(sql->sa, rel, rel_projections(sql, rel, NULL,
1, 1));
+
+ /* look up the identity column and label these */
+ le = exps_bind_column2(rel->exps, exp_relname(ident),
exp_name(ident));
+ exp_label(sql->sa, le, ++sql->label);
+ /* zap rel->exps name hash tables as we changed names */
+ rel->exps->ht = NULL;
+ le = exp_column(sql->sa, exp_relname(le), exp_name(le),
exp_subtype(le), le->card, has_nil(le), is_intern(le));
+
+ nrel = rel_crossproduct(sql->sa, rel_dup(nrel), rel, op_left);
+ nrel->exps = new_exp_list(sql->sa);
+ le = exp_compare(sql->sa, ident, le, cmp_equal);
+ append(nrel->exps, le);
+
+ arel->flag = APPLY_JOIN;
+ (*changes)++;
+ return nrel;
+ }
/* table function (TODO should output any input cols) */
if (r->op == op_table && r->l) {
/*
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
@@ -354,9 +354,12 @@ rel_projections(mvc *sql, sql_rel *rel,
case op_left:
case op_right:
case op_full:
+ case op_apply:
exps = rel_projections(sql, rel->l, tname, settname, intern );
- rexps = rel_projections(sql, rel->r, tname, settname, intern );
- exps = list_merge( exps, rexps, (fdup)NULL);
+ if (rel->op != op_apply || (rel->flag == APPLY_LOJ ||
rel->flag == APPLY_JOIN)) {
+ rexps = rel_projections(sql, rel->r, tname, settname,
intern );
+ exps = list_merge( exps, rexps, (fdup)NULL);
+ }
return exps;
case op_groupby:
case op_project:
@@ -393,7 +396,6 @@ rel_projections(mvc *sql, sql_rel *rel,
}
return exps;
case op_ddl:
- case op_apply:
case op_semi:
case op_anti:
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
@@ -12,6 +12,9 @@
#include "rel_select.h"
#include "rel_exp.h"
#include "sql_privileges.h"
+#include "rel_optimizer.h"
+#include "rel_dump.h"
+#include "sql_symbol.h"
static sql_exp *
insert_value(mvc *sql, sql_column *c, sql_rel **r, symbol *s)
@@ -855,6 +858,52 @@ update_table(mvc *sql, dlist *qname, dli
dnode *n;
char *rname = NULL;
+#if 0
+ dlist *selection = dlist_create(sql->sa);
+ dlist *from_list = dlist_create(sql->sa);
+ symbol *sym;
+ sql_rel *sq;
+
+ dlist_append_list(sql->sa, from_list, qname);
+ dlist_append_symbol(sql->sa, from_list, NULL);
+ sym = symbol_create_list(sql->sa, SQL_NAME, from_list);
+ from_list = dlist_create(sql->sa);
+ dlist_append_symbol(sql->sa, from_list, sym);
+
+ {
+ dlist *l = dlist_create(sql->sa);
+
+
+ dlist_append_string(sql->sa, l, tname);
+ dlist_append_string(sql->sa, l, TID);
+ sym = symbol_create_list(sql->sa, SQL_COLUMN,
l);
+
+ l = dlist_create(sql->sa);
+ dlist_append_symbol(sql->sa, l, sym);
+ dlist_append_string(sql->sa, l, TID);
+ dlist_append_symbol(sql->sa, selection,
+ symbol_create_list(sql->sa, SQL_COLUMN, l));
+ }
+ for (n = assignmentlist->h; n; n = n->next) {
+ dlist *assignment = n->data.sym->data.lval, *l;
+ int single = (assignment->h->next->type ==
type_string);
+ symbol *a = assignment->h->data.sym;
+
+ l = dlist_create(sql->sa);
+ dlist_append_symbol(sql->sa, l, a);
+ dlist_append_string(sql->sa, l,
(single)?assignment->h->next->data.sval:NULL);
+ a = symbol_create_list(sql->sa, SQL_COLUMN, l);
+ dlist_append_symbol(sql->sa, selection, a);
+ }
+
+ sym = newSelectNode(sql->sa, 0, selection, NULL,
symbol_create_list(sql->sa, SQL_FROM, from_list), opt_where, NULL, NULL, NULL,
NULL, NULL, NULL, NULL);
+ sq = rel_selects(sql, sym);
+ if (sq)
+ sq = rel_optimizer(sql, sq);
+ rel_print(sql,sq,0);
+ }
+#endif
+
if (opt_where) {
int status = sql->session->status;
@@ -909,7 +958,7 @@ update_table(mvc *sql, dlist *qname, dli
if (single) {
v = rel_value_exp(sql, &r, a,
sql_sel, ek);
} else if (!rel_val && r) {
- r = rel_subquery(sql, r, a, ek,
APPLY_JOIN);
+ r = rel_subquery(sql, r, a, ek,
APPLY_LOJ);
if (r) {
list *val_exps =
rel_projections(sql, r->r, NULL, 0, 1);
@@ -930,7 +979,7 @@ update_table(mvc *sql, dlist *qname, dli
rel_val = rel_project(sql->sa,
rel_val, rel_projections(sql, rel_val, NULL, 0, 1));
rel_project_add_exp(sql,
rel_val, v);
}
- r = rel_crossproduct(sql->sa, r,
rel_val, op_join);
+ r = rel_crossproduct(sql->sa, r,
rel_val, op_left);
if (single)
v = exp_column(sql->sa, NULL,
exp_name(v), exp_subtype(v), v->card, has_nil(v), is_intern(v));
}
diff --git a/sql/test/BugTracker-2015/Tests/crash.Bug-3736.stable.err
b/sql/test/BugTracker-2015/Tests/crash.Bug-3736.stable.err
--- a/sql/test/BugTracker-2015/Tests/crash.Bug-3736.stable.err
+++ b/sql/test/BugTracker-2015/Tests/crash.Bug-3736.stable.err
@@ -30,7 +30,7 @@ stderr of test 'crash.Bug-3736` in direc
# 10:50:37 > "mclient" "-lsql" "-ftest" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-30636" "--port=38510"
# 10:50:37 >
-MAPI = (monetdb) /var/tmp/mtest-6362/.s.monetdb.36855
+MAPI = (monetdb) /var/tmp/mtest-15304/.s.monetdb.34590
QUERY = Select b.* FROM open_auctions o, b bidder 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;
ERROR = !SELECT: no such table 'b'
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
@@ -100,11 +100,11 @@ project (
| | | | | | | | ) [ 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 L2.L2,
sys.identity(o.id NOT NULL HASHCOL ) HASHCOL as L12.L12 ],
| | | | | | | | table(sys.bidder) [ bidder.id NOT NULL as L5.id,
bidder.increase NOT NULL as L5.increase ] COUNT
| | | | | | | ) [ ]
-| | | | | | ) [ L2.L2 NOT NULL, L12.L12 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,
L5.id NOT NULL, L5.increase NOT NULL, sys.identity(o.id NOT NULL HASHCOL )
HASHCOL as L11.L11 ]
+| | | | | | ) [ L12.L12 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, L2.L2 NOT NULL,
L5.id NOT NULL, L5.increase NOT NULL, sys.identity(o.id NOT NULL HASHCOL )
HASHCOL as L11.L11 ]
| | | | | ) [ L6.open_auction_id NOT NULL = o.open_auction_id NOT NULL ]
-| | | | ) [ L11.L11, L12.L12 ] [ L2.L2 NOT NULL, L12.L12 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, L5.id NOT NULL, L5.increase NOT
NULL, L11.L11 HASHCOL , sys.max no nil (L6.id NOT NULL HASHCOL as b2a.id) NOT
NULL as L3.L3 ]
+| | | | ) [ L11.L11, L12.L12 ] [ L12.L12 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, L2.L2 NOT NULL, L5.id NOT NULL, L5.increase NOT
NULL, L11.L11 HASHCOL , sys.max no nil (L6.id NOT NULL HASHCOL as b2a.id) NOT
NULL as L3.L3 ]
| | | ) [ L5.id NOT NULL = L3.L3 NOT NULL ]
-| | ) [ L2.L2 NOT NULL, L12.L12 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, L5.id NOT NULL, L5.increase NOT NULL, L11.L11
HASHCOL , L3.L3 NOT NULL, sys.sql_mul(L2.L2 NOT NULL, double "2.000000") as
L13.L13, L5.increase NOT NULL as L14.L14 ]
+| | ) [ L12.L12 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,
L2.L2 NOT NULL, L5.id NOT NULL, L5.increase NOT NULL, L11.L11 HASHCOL , L3.L3
NOT NULL, sys.sql_mul(L2.L2 NOT NULL, double "2.000000") as L13.L13,
L5.increase NOT NULL as L14.L14 ]
| ) [ L13.L13 <= L14.L14 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;
diff --git
a/sql/test/BugTracker-2015/Tests/date_comparison_incorrect_results.Bug-3834.stable.out
b/sql/test/BugTracker-2015/Tests/date_comparison_incorrect_results.Bug-3834.stable.out
---
a/sql/test/BugTracker-2015/Tests/date_comparison_incorrect_results.Bug-3834.stable.out
+++
b/sql/test/BugTracker-2015/Tests/date_comparison_incorrect_results.Bug-3834.stable.out
@@ -86,13 +86,13 @@ Ready.
# WHERE datepoint_wrong.mydate >= B.startdate
# AND datepoint_wrong.mydate <= B.enddate
#);
-[ 1 ]
+[ 2 ]
#SELECT * FROM datepoint_wrong;
% sys.datepoint_wrong, sys.datepoint_wrong, sys.datepoint_wrong # table_name
% mydate, insiderange, rangename # name
% date, boolean, char # type
% 10, 5, 8 # length
-[ 2012-05-09, false, NULL ]
+[ 2012-05-09, NULL, NULL ]
[ 2012-03-09, true, "A" ]
#CREATE TABLE datepoint_correct1 (mydate DATE, insiderange BOOLEAN DEFAULT
FALSE, rangename CHAR(8));
#INSERT INTO datepoint_correct1 (mydate) VALUES ('2012-05-09');
@@ -120,15 +120,15 @@ Ready.
# WHERE datepoint_correct1.mydate >= B.startdate
# AND datepoint_correct1.mydate <= B.enddate
#);
-[ 1 ]
+[ 3 ]
#SELECT * FROM datepoint_correct1;
% sys.datepoint_correct1, sys.datepoint_correct1, sys.datepoint_correct1
# table_name
% mydate, insiderange, rangename # name
% date, boolean, char # type
% 10, 5, 8 # length
-[ 2012-05-09, false, NULL ]
+[ 2012-05-09, NULL, NULL ]
[ 2012-03-09, true, "A" ]
-[ 2012-04-09, false, NULL ]
+[ 2012-04-09, NULL, NULL ]
#CREATE TABLE datepoint_correct2 (mydate DATE, insiderange BOOLEAN DEFAULT
FALSE, rangename CHAR(8));
#INSERT INTO datepoint_correct2 (mydate) VALUES ('2012-03-09');
[ 1 ]
@@ -153,14 +153,14 @@ Ready.
# WHERE datepoint_correct2.mydate >= B.startdate
# AND datepoint_correct2.mydate <= B.enddate
#);
-[ 1 ]
+[ 2 ]
#SELECT * FROM datepoint_correct2;
% sys.datepoint_correct2, sys.datepoint_correct2, sys.datepoint_correct2
# table_name
% mydate, insiderange, rangename # name
% date, boolean, char # type
% 10, 5, 8 # length
[ 2012-03-09, true, "A" ]
-[ 2012-05-09, false, NULL ]
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
