Changeset: 89a9ebc92eda for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=89a9ebc92eda
Added Files:
sql/test/BugTracker-2012/Tests/order_by_on_column_exp.Bug-3193.sql
sql/test/BugTracker-2012/Tests/order_by_on_column_exp.Bug-3193.stable.err
sql/test/BugTracker-2012/Tests/order_by_on_column_exp.Bug-3193.stable.out
sql/test/BugTracker-2012/Tests/rewrite_like_into_likesubselect.Bug-3179.sql
sql/test/BugTracker-2012/Tests/rewrite_like_into_likesubselect.Bug-3179.stable.err
sql/test/BugTracker-2012/Tests/rewrite_like_into_likesubselect.Bug-3179.stable.out
sql/test/BugTracker-2012/Tests/table_function_with_column_subselects.Bug-3172.sql
sql/test/BugTracker-2012/Tests/table_function_with_column_subselects.Bug-3172.stable.err
sql/test/BugTracker-2012/Tests/table_function_with_column_subselects.Bug-3172.stable.out
Modified Files:
monetdb5/optimizer/opt_prelude.c
monetdb5/optimizer/opt_prelude.h
monetdb5/optimizer/opt_pushselect.c
monetdb5/optimizer/opt_reorder.c
monetdb5/optimizer/opt_support.c
monetdb5/optimizer/opt_support.h
sql/backends/monet5/sql_gencode.c
sql/common/sql_types.c
sql/include/sql_relation.h
sql/server/rel_bin.c
sql/server/rel_dump.c
sql/server/rel_exp.c
sql/server/rel_exp.h
sql/server/rel_optimizer.c
sql/server/rel_select.c
sql/server/sql_parser.y
sql/server/sql_statement.h
sql/test/BugTracker-2012/Tests/All
sql/test/BugTracker-2012/Tests/predicate_select.Bug-3090.stable.err
sql/test/BugTracker-2012/Tests/predicate_select.Bug-3090.stable.out
sql/test/Tests/marcin1.stable.out
sql/test/leaks/Tests/check0.stable.out
sql/test/leaks/Tests/check1.stable.out
sql/test/leaks/Tests/check2.stable.out
sql/test/leaks/Tests/check3.stable.out
sql/test/leaks/Tests/check4.stable.out
sql/test/leaks/Tests/check5.stable.out
Branch: default
Log Message:
added tests for Bugs 3193, 3179 and 3172
Fixed bug 3191, we now allow the order by column expressions to refer to
the lower level projection. This is very limited support voor order by with
expressions. Aliases is still preferred.
Fixed bug 3179 (or feature request). We now rewrite batstr.*like + subselect
into
*likeselect. Also some cleanup of pushselect optimizer and mergetable related
helper functions in opt_support.c
Fixed bug 3172. We still don't allow multi row input in table functions. But
we now don't crash on it anymore. Still work on mal.multiplex needed (requires
multiple outputs)!
diffs (truncated from 2258 to 300 lines):
diff --git a/monetdb5/optimizer/opt_prelude.c b/monetdb5/optimizer/opt_prelude.c
--- a/monetdb5/optimizer/opt_prelude.c
+++ b/monetdb5/optimizer/opt_prelude.c
@@ -127,6 +127,9 @@ str ilikeselectRef;
str likeuselectRef;
str ilikeuselectRef;
str likeRef;
+str ilikeRef;
+str not_likeRef;
+str not_ilikeRef;
str listRef;
str lockRef;
str lookupRef;
@@ -237,6 +240,7 @@ str updateRef;
str subselectRef;
str thetasubselectRef;
str likesubselectRef;
+str ilikesubselectRef;
str uselectRef;
str userRef;
str vectorRef;
@@ -379,6 +383,9 @@ void optimizerInit(void){
ilikeuselectRef = putName("ilike_uselect",13);
listRef = putName("list",4);
likeRef = putName("like",4);
+ ilikeRef = putName("ilike",5);
+ not_likeRef = putName("not_like",8);
+ not_ilikeRef = putName("not_ilike",9);
lockRef = putName("lock",4);
lookupRef = putName("lookup",6);
malRef = putName("mal", 3);
@@ -488,6 +495,7 @@ void optimizerInit(void){
subselectRef = putName("subselect",9);
thetasubselectRef = putName("thetasubselect",14);
likesubselectRef = putName("likesubselect",13);
+ ilikesubselectRef = putName("ilikesubselect",14);
uselectRef = putName("uselect",7);
vectorRef = putName("vector",6);
antiuselectRef = putName("antiuselect",11);
diff --git a/monetdb5/optimizer/opt_prelude.h b/monetdb5/optimizer/opt_prelude.h
--- a/monetdb5/optimizer/opt_prelude.h
+++ b/monetdb5/optimizer/opt_prelude.h
@@ -127,6 +127,9 @@ opt_export str likeuselectRef;
opt_export str ilikeuselectRef;
opt_export str listRef;
opt_export str likeRef;
+opt_export str ilikeRef;
+opt_export str not_likeRef;
+opt_export str not_ilikeRef;
opt_export str lockRef;
opt_export str lookupRef;
opt_export str malRef;
@@ -236,6 +239,7 @@ opt_export str updateRef;
opt_export str subselectRef;
opt_export str thetasubselectRef;
opt_export str likesubselectRef;
+opt_export str ilikesubselectRef;
opt_export str uselectRef;
opt_export str userRef;
opt_export str vectorRef;
diff --git a/monetdb5/optimizer/opt_pushselect.c
b/monetdb5/optimizer/opt_pushselect.c
--- a/monetdb5/optimizer/opt_pushselect.c
+++ b/monetdb5/optimizer/opt_pushselect.c
@@ -102,7 +102,7 @@ subselect_find_subselect( subselect_t *s
int
OPTpushselectImplementation(Client cntxt, MalBlkPtr mb, MalStkPtr stk,
InstrPtr pci)
{
- int i, j, limit, slimit, actions=0, *vars, push_down_delta = 0, nr_topn
= 0;
+ int i, j, limit, slimit, actions=0, *vars, push_down_delta = 0, nr_topn
= 0, nr_likes = 0;
InstrPtr p, *old;
subselect_t subselects;
@@ -135,6 +135,9 @@ OPTpushselectImplementation(Client cntxt
if (getModuleId(p) == algebraRef && getFunctionId(p) ==
sliceRef)
nr_topn++;
+ if (isLikeOp(p))
+ nr_likes++;
+
if (getModuleId(p) == sqlRef && getFunctionId(p) == deltaRef)
push_down_delta++;
@@ -156,8 +159,7 @@ OPTpushselectImplementation(Client cntxt
}
}
}
- if (getModuleId(p) == algebraRef && p->retc == 1 &&
- (getFunctionId(p) == subselectRef || getFunctionId(p) ==
thetasubselectRef || getFunctionId(p) == likesubselectRef) &&
+ if (isSubSelect(p) && p->retc == 1 &&
/* no cand list */ getArgType(mb, p, 2) !=
newBatType(TYPE_oid, TYPE_oid)) {
int i1 = getArg(p, 1), tid = 0;
InstrPtr q = old[vars[i1]];
@@ -171,10 +173,10 @@ OPTpushselectImplementation(Client cntxt
if (getModuleId(s) == sqlRef &&
getFunctionId(s) == tidRef)
tid = getArg(q, 1);
break;
- } else if (getModuleId(q) == batcalcRef &&
q->argc >= 2 && isaBatType(getArgType(mb, q, 1))) {
+ } else if (isMapOp(q) && q->argc >= 2 &&
isaBatType(getArgType(mb, q, 1))) {
int i1 = getArg(q, 1);
q = old[vars[i1]];
- } else if (getModuleId(q) == batcalcRef &&
q->argc >= 3 && isaBatType(getArgType(mb, q, 2))) {
+ } else if (isMapOp(q) && q->argc >= 3 &&
isaBatType(getArgType(mb, q, 2))) {
int i2 = getArg(q, 2);
q = old[vars[i2]];
} else {
@@ -188,7 +190,7 @@ OPTpushselectImplementation(Client cntxt
}
}
- if ((!subselects.nr && !nr_topn) || newMalBlkStmt(mb, mb->ssize+20) <0
) {
+ if ((!subselects.nr && !nr_topn && !nr_likes) || newMalBlkStmt(mb,
mb->ssize+20) <0 ) {
GDKfree(vars);
return 0;
}
@@ -197,11 +199,40 @@ OPTpushselectImplementation(Client cntxt
for (i = 1; i < limit; i++) {
p = old[i];
+ /* rewrite batstr.like + subselect -> likesubselect */
+ if (getModuleId(p) == algebraRef && p->retc == 1 &&
getFunctionId(p) == subselectRef) {
+ int var = getArg(p, 1);
+ InstrPtr q = mb->stmt[vars[var]]; /* BEWARE: the
optimizer may not add or remove statements ! */
+
+ if (isLikeOp(q)) { /* TODO check if getArg(p, 3) value
== TRUE */
+ InstrPtr r = newInstruction(mb, ASSIGNsymbol);
+ int has_cand = (getArgType(mb, p, 2) ==
newBatType(TYPE_oid, TYPE_oid));
+ int a, anti = (getFunctionId(q)[0] == 'n'),
ignore_case = (getFunctionId(q)[anti?4:0] == 'i');
+
+ setModuleId(r, algebraRef);
+ setFunctionId(r, likesubselectRef);
+ getArg(r,0) = getArg(p,0);
+ r = pushArgument(mb, r, getArg(q, 1));
+ if (has_cand)
+ r = pushArgument(mb, r, getArg(p, 2));
+ for(a = 2; a<q->argc; a++)
+ r = pushArgument(mb, r, getArg(q, a));
+ if (r->argc < (4+has_cand))
+ r = pushStr(mb, r, ""); /* default esc
*/
+ if (r->argc < (5+has_cand))
+ r = pushBit(mb, r, ignore_case);
+ if (r->argc < (6+has_cand))
+ r = pushBit(mb, r, anti);
+ freeInstruction(p);
+ p = r;
+ actions++;
+ }
+ }
+
/* inject table ids into subselect
* s = subselect(c, C1..) => subselect(c, t, C1..)
*/
- if (getModuleId(p) == algebraRef && p->retc == 1 &&
- (getFunctionId(p) == subselectRef || getFunctionId(p) ==
thetasubselectRef || getFunctionId(p) == likesubselectRef)) {
+ if (isSubSelect(p) && p->retc == 1) {
int tid = 0;
if ((tid = subselect_find_tids(&subselects, getArg(p,
0))) >= 0) {
@@ -304,8 +335,7 @@ OPTpushselectImplementation(Client cntxt
* nu = subselect(uvl, C1..)
* s = subdelta(nc, uid, nu, ni);
*/
- if (getModuleId(p) == algebraRef && p->retc == 1 &&
- (getFunctionId(p) == subselectRef || getFunctionId(p) ==
thetasubselectRef || getFunctionId(p) == likesubselectRef)) {
+ if (isSubSelect(p) && p->retc == 1) {
int var = getArg(p, 1);
InstrPtr q = old[vars[var]];
diff --git a/monetdb5/optimizer/opt_reorder.c b/monetdb5/optimizer/opt_reorder.c
--- a/monetdb5/optimizer/opt_reorder.c
+++ b/monetdb5/optimizer/opt_reorder.c
@@ -202,8 +202,7 @@ OPTbreadthfirst(Client cntxt, MalBlkPtr
old[pc] = 0;
pushInstruction(mb, p);
}
- if (getFunctionId(p) == leftjoinRef || getFunctionId(p) == newRef ||
- getFunctionId(p) == deriveRef || getFunctionId(p) ==
mirrorRef)
+ if (getFunctionId(p) == leftjoinRef || getModuleId(p) == groupRef)
for (i = 0; i< dep[pc]->used; i++)
OPTbreadthfirst(cntxt, mb, uselist[dep[pc]->pos+i],
max, old, dep, uselist);
}
diff --git a/monetdb5/optimizer/opt_support.c b/monetdb5/optimizer/opt_support.c
--- a/monetdb5/optimizer/opt_support.c
+++ b/monetdb5/optimizer/opt_support.c
@@ -828,6 +828,14 @@ int isMapOp(InstrPtr p){
(getModuleId(p)== mkeyRef);
}
+int isLikeOp(InstrPtr p){
+ return (getModuleId(p) == batstrRef &&
+ (getFunctionId(p) == likeRef ||
+ getFunctionId(p) == not_likeRef ||
+ getFunctionId(p) == ilikeRef ||
+ getFunctionId(p) == not_ilikeRef));
+}
+
int isTopn(InstrPtr p){
return ((getModuleId(p) == pqueueRef &&
(getFunctionId(p) == topn_minRef ||
@@ -887,6 +895,15 @@ int isFragmentGroup2(InstrPtr p){
);
}
+int isSubSelect(InstrPtr p)
+{
+ return (getModuleId(p)== algebraRef && (
+ getFunctionId(p)== subselectRef ||
+ getFunctionId(p)== thetasubselectRef ||
+ getFunctionId(p)== likesubselectRef ||
+ getFunctionId(p)== ilikesubselectRef));
+}
+
int isFragmentGroup(InstrPtr p){
return
(getModuleId(p)== pcreRef && (
@@ -901,25 +918,15 @@ int isFragmentGroup(InstrPtr p){
getFunctionId(p)== selectNotNilRef ||
getFunctionId(p)== uselectRef ||
getFunctionId(p)== antiuselectRef ||
- getFunctionId(p)== thetauselectRef ||
- getFunctionId(p)== subselectRef ||
- getFunctionId(p)== thetasubselectRef ||
- getFunctionId(p)== likesubselectRef
+ getFunctionId(p)== thetauselectRef
)) ||
+ isSubSelect(p) ||
(getModuleId(p)== batRef && (
getFunctionId(p)== mirrorRef
)
);
}
-int isSubSelect(InstrPtr p)
-{
- return (getModuleId(p)== algebraRef && (
- getFunctionId(p)== subselectRef ||
- getFunctionId(p)== thetasubselectRef ||
- getFunctionId(p)== likesubselectRef ));
-}
-
/*
* Some optimizers are interdependent (e.g. mitosis and octopus), which
* requires inspection of the pipeline attached to a MAL block.
diff --git a/monetdb5/optimizer/opt_support.h b/monetdb5/optimizer/opt_support.h
--- a/monetdb5/optimizer/opt_support.h
+++ b/monetdb5/optimizer/opt_support.h
@@ -119,6 +119,7 @@ opt_export int isFragmentGroup2(InstrPtr
opt_export int isDelta(InstrPtr q);
opt_export int isMatJoinOp(InstrPtr q);
opt_export int isMapOp(InstrPtr q);
+opt_export int isLikeOp(InstrPtr q);
opt_export int isTopn(InstrPtr q);
opt_export int isSlice(InstrPtr q);
opt_export int isOrderby(InstrPtr q);
diff --git a/sql/backends/monet5/sql_gencode.c
b/sql/backends/monet5/sql_gencode.c
--- a/sql/backends/monet5/sql_gencode.c
+++ b/sql/backends/monet5/sql_gencode.c
@@ -1043,6 +1043,7 @@ _dumpstmt(backend *sql, MalBlkPtr mb, st
int l = _dumpstmt(sql, mb, s->op1);
int r = s->op2?_dumpstmt(sql, mb, s->op2):-1;
int sub = -1;
+ int anti = is_anti(s);
if (s->op3)
sub = _dumpstmt(sql, mb, s->op3);
@@ -1053,7 +1054,7 @@ _dumpstmt(backend *sql, MalBlkPtr mb, st
int k;
int op3 = -1;
- switch (s->flag) {
+ switch (get_cmp(s)) {
case cmp_equal:
op = "=";
break;
@@ -1080,6 +1081,7 @@ _dumpstmt(backend *sql, MalBlkPtr mb, st
op = sql_func_imp(s->op4.funcval->func);
mod =
sql_func_mod(s->op4.funcval->func);
+ assert(anti == 0);
r = p2->nr;
if (s->op2->op4.lval->h->next) {
p3 =
s->op2->op4.lval->h->next->data;
@@ -1118,7 +1120,7 @@ _dumpstmt(backend *sql, MalBlkPtr mb, st
if (s->flag != cmp_equal && s->flag !=
cmp_notequal)
cmd = "thetasubselect";
- if (s->flag == cmp_filter) {
+ if (get_cmp(s) == cmp_filter) {
node *n;
char *mod, *fimp;
@@ -1136,7 +1138,7 @@ _dumpstmt(backend *sql, MalBlkPtr mb, st
q = pushArgument(mb, q, op->nr);
}
- q = pushBit(mb, q, FALSE);
+ q = pushBit(mb, q, anti);
s->nr = getDestVar(q);
break;
}
@@ -1552,7 +1554,8 @@ _dumpstmt(backend *sql, MalBlkPtr mb, st
_______________________________________________
checkin-list mailing list
[email protected]
http://mail.monetdb.org/mailman/listinfo/checkin-list
