Changeset: 81b96a91de34 for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=81b96a91de34
Modified Files:
sql/rel.txt
sql/src/backends/monet5/vaults/Tests/All
sql/src/server/rel_bin.c
sql/src/server/rel_dump.c
sql/src/server/rel_exp.c
sql/src/server/rel_exp.h
sql/src/server/rel_optimizer.c
sql/src/server/rel_select.c
sql/src/server/sql_psm.c
sql/src/server/sql_statement.c
sql/src/server/sql_statement.h
Branch: default
Log Message:
added 2 new compare types (cmp_in and cmp_notin). This now handles
the simple SQL in ( values ) case. which solves the slow optimization
of (many) cmp_or statements.
diffs (truncated from 872 to 300 lines):
diff -r dfe215052f70 -r 81b96a91de34 sql/rel.txt
--- a/sql/rel.txt Thu Dec 02 10:44:28 2010 +0100
+++ b/sql/rel.txt Fri Dec 03 13:59:15 2010 +0100
@@ -88,7 +88,12 @@
cmp_notlike = 6,
cmp_like = 7,
cmp_all = 8, cross product
- cmp_or = 9 or handling
+ cmp_or = 9, or handling
+ cmp_in = 10, in list handling
+ cmp_notin = 11 not in list handling
+
+ cmp_in/cmp_notin
+ ->r is a list of values
)
e_func
diff -r dfe215052f70 -r 81b96a91de34 sql/src/backends/monet5/vaults/Tests/All
--- a/sql/src/backends/monet5/vaults/Tests/All Thu Dec 02 10:44:28 2010 +0100
+++ b/sql/src/backends/monet5/vaults/Tests/All Fri Dec 03 13:59:15 2010 +0100
@@ -1,1 +1,1 @@
-vault00
+#vault00
diff -r dfe215052f70 -r 81b96a91de34 sql/src/server/rel_bin.c
--- a/sql/src/server/rel_bin.c Thu Dec 02 10:44:28 2010 +0100
+++ b/sql/src/server/rel_bin.c Fri Dec 03 13:59:15 2010 +0100
@@ -175,6 +175,32 @@
}
}
+static stmt *
+handle_in_exps( mvc *sql, sql_exp *ce, list *nl, stmt *left, stmt *right,
group *grp, int in, int use_r)
+{
+ node *n;
+ stmt *s, *c;
+
+ /* create bat append values */
+ s = stmt_temp(sql->sa, exp_subtype(ce));
+ for( n = nl->h; n; n = n->next) {
+ sql_exp *e = n->data;
+ stmt *i = exp_bin(sql, use_r?e->r:e, left, right, grp, NULL);
+
+ s = stmt_append(sql->sa, s, i);
+ }
+ c = exp_bin(sql, ce, left, right, grp, NULL);
+ /*s = stmt_mark_tail(sql->sa, stmt_reverse(sql->sa,
stmt_semijoin(sql->sa, stmt_reverse(sql->sa, c), stmt_reverse(sql->sa, s))),
0);*/
+ /* not really a projection join, therefore make sure left values are
unique !! */
+ c = column(sql->sa, c);
+ if (in)
+ s = stmt_project(sql->sa, c, stmt_reverse(sql->sa,
stmt_unique(sql->sa, s, NULL)));
+ else
+ s = stmt_reverse(sql->sa, stmt_diff(sql->sa,
stmt_reverse(sql->sa, c), stmt_reverse(sql->sa, stmt_unique(sql->sa, s,
NULL))));
+ s = stmt_const(sql->sa, s, NULL);
+ return s;
+}
+
/* For now this only works if all or's are part of the 'IN' */
static stmt *
handle_equality_exps( mvc *sql, list *l, list *r, stmt *left, stmt *right,
group *grp )
@@ -182,7 +208,6 @@
node *n;
sql_exp *ce = NULL;
list *nl = new_exp_list(sql->sa);
- stmt *s = NULL, *c;
get_exps(nl, l);
get_exps(nl, r);
@@ -197,21 +222,7 @@
if (!exp_match(ce, e->l))
return NULL;
}
-
- /* create bat append values */
- s = stmt_temp(sql->sa, exp_subtype(ce));
- for( n = nl->h; n; n = n->next) {
- sql_exp *e = n->data;
- stmt *i = exp_bin(sql, e->r, left, right, grp, NULL);
-
- s = stmt_append(sql->sa, s, i);
- }
- c = exp_bin(sql, ce, left, right, grp, NULL);
- /*s = stmt_mark_tail(sql->sa, stmt_reverse(sql->sa,
stmt_semijoin(sql->sa, stmt_reverse(sql->sa, c), stmt_reverse(sql->sa, s))),
0);*/
- /* not really a projection join, therefore make sure left values are
unique !! */
- s = stmt_project(sql->sa, c, stmt_reverse(sql->sa, stmt_unique(sql->sa,
s, NULL)));
- s = stmt_const(sql->sa, s, NULL);
- return s;
+ return handle_in_exps( sql, ce, nl, left, right, grp, 1, 1);
}
stmt *
@@ -394,6 +405,9 @@
sql_exp *re = e->r, *re2 = e->f;
prop *p;
+ if (e->flag == cmp_in || e->flag == cmp_notin) {
+ return handle_in_exps(sql, e->l, e->r, left, right,
grp, (e->flag == cmp_in), 0);
+ }
if (e->flag == cmp_or) {
list *l = e->l;
node *n;
@@ -1615,6 +1629,9 @@
sel = NULL;
} else {
sel = stmt_join(sql->sa, sel, predicate, cmp_all);
+ predicate = NULL;
+ if (!sub)
+ predicate = sel;
}
}
/* construct relation */
diff -r dfe215052f70 -r 81b96a91de34 sql/src/server/rel_dump.c
--- a/sql/src/server/rel_dump.c Thu Dec 02 10:44:28 2010 +0100
+++ b/sql/src/server/rel_dump.c Fri Dec 03 13:59:15 2010 +0100
@@ -62,6 +62,8 @@
case cmp_ilike: r = "ilike"; break;
case cmp_all: r = "all"; break;
case cmp_or: r = "or"; break;
+ case cmp_in: r = "in"; break;
+ case cmp_notin: r = "notin"; break;
default:
r = "";
}
@@ -141,7 +143,11 @@
alias = 0;
break;
case e_cmp:
- if (e->flag == cmp_or) {
+ if (e->flag == cmp_in || e->flag == cmp_notin) {
+ exp_print(sql, fout, e->l, depth, alias, 1);
+ cmp_print(sql, fout, e->flag );
+ exps_print(sql, fout, e->r, depth, alias, 1);
+ } else if (e->flag == cmp_or) {
exps_print(sql, fout, e->l, depth, alias, 1);
cmp_print(sql, fout, e->flag );
exps_print(sql, fout, e->r, depth, alias, 1);
diff -r dfe215052f70 -r 81b96a91de34 sql/src/server/rel_exp.c
--- a/sql/src/server/rel_exp.c Thu Dec 02 10:44:28 2010 +0100
+++ b/sql/src/server/rel_exp.c Fri Dec 03 13:59:15 2010 +0100
@@ -82,6 +82,20 @@
return e;
}
+sql_exp *
+exp_in(sql_allocator *sa, sql_exp *l, list *r, int cmptype)
+{
+ sql_exp *e = exp_create(sa, e_cmp);
+
+ e->card = l->card;
+ e->l = l;
+ e->r = r;
+ assert( cmptype == cmp_in || cmptype == cmp_notin);
+ e->flag = cmptype;
+ return e;
+}
+
+
static sql_subtype*
dup_subtype(sql_allocator *sa, sql_subtype *st)
{
@@ -431,17 +445,17 @@
if (e1->type != e_cmp || e2->type != e_cmp)
return 0;
- if (e1->flag != cmp_or && e1_r && e1_r->card == 1 &&
- e2->flag != cmp_or && e2_r && e2_r->card == 1)
+ if (!is_complex_exp(e1->flag) && e1_r && e1_r->card == 1 &&
+ !is_complex_exp(e2->flag) && e2_r && e2_r->card == 1)
return exp_match_exp(e1->l, e2->l);
- if (e1->flag != cmp_or && e1_r && e1_r->card == 1 &&
+ if (!is_complex_exp(e1->flag) && e1_r && e1_r->card == 1 &&
e2->flag == cmp_or)
return exp_match_col_exps(e1->l, e2->l) &&
exp_match_col_exps(e1->l, e2->r);
if (e1->flag == cmp_or &&
- e2->flag != cmp_or && e2_r && e2_r->card == 1)
+ !is_complex_exp(e2->flag) && e2_r && e2_r->card == 1)
return exp_match_col_exps(e2->l, e1->l) &&
exp_match_col_exps(e2->l, e1->r);
@@ -502,7 +516,7 @@
if (e1->type == e2->type) {
switch(e1->type) {
case e_cmp:
- if (e1->flag == e2->flag && e1->flag != cmp_or &&
+ if (e1->flag == e2->flag && !is_complex_exp(e1->flag) &&
exp_match_exp(e1->l, e2->l) &&
exp_match_exp(e1->r, e2->r) &&
((!e1->f && !e2->f) || exp_match_exp(e1->f, e2->f)))
@@ -558,7 +572,7 @@
{
sql_exp *l = e->l;
sql_exp *r = e->r;
- if (e->type == e_cmp && e->flag != cmp_or && l && r && r->card >=
CARD_AGGR)
+ if (e->type == e_cmp && !is_complex_exp(e->flag) && l && r && r->card
>= CARD_AGGR)
return 0;
if (e->type == e_cmp && e->flag == cmp_or && e->card >= CARD_AGGR)
if (exps_are_joins(e->l) == 0 && exps_are_joins(e->r) == 0)
@@ -611,7 +625,7 @@
/* only simple compare expressions, ie not or lists
or range expressions (e->f)
*/
- if (e->type == e_cmp && e->flag != cmp_or && e->l && e->r && !e->f &&
e->card >= CARD_AGGR && !complex_select(e))
+ if (e->type == e_cmp && !is_complex_exp(e->flag) && e->l && e->r &&
!e->f && e->card >= CARD_AGGR && !complex_select(e))
return 0;
return -1;
}
@@ -704,7 +718,7 @@
int
exp_is_correlation(sql_exp *e, sql_rel *r )
{
- if (e->type == e_cmp && e->flag != cmp_or) {
+ if (e->type == e_cmp && !is_complex_exp(e->flag)) {
sql_exp *le = rel_find_exp(r->l, e->l);
sql_exp *re = rel_find_exp(r->r, e->r);
diff -r dfe215052f70 -r 81b96a91de34 sql/src/server/rel_exp.h
--- a/sql/src/server/rel_exp.h Thu Dec 02 10:44:28 2010 +0100
+++ b/sql/src/server/rel_exp.h Fri Dec 03 13:59:15 2010 +0100
@@ -25,6 +25,7 @@
extern sql_exp *exp_compare(sql_allocator *sa, sql_exp *l, sql_exp *r, int
cmptype);
extern sql_exp *exp_compare2(sql_allocator *sa, sql_exp *l, sql_exp *r,
sql_exp *h, int cmptype);
extern sql_exp *exp_or(sql_allocator *sa, list *l, list *r);
+extern sql_exp *exp_in(sql_allocator *sa, sql_exp *l, list *r, int cmptype);
#define exp_fromtype(e) ((list*)e->r)->h->data
#define exp_totype(e) ((list*)e->r)->h->next->data
diff -r dfe215052f70 -r 81b96a91de34 sql/src/server/rel_optimizer.c
--- a/sql/src/server/rel_optimizer.c Thu Dec 02 10:44:28 2010 +0100
+++ b/sql/src/server/rel_optimizer.c Fri Dec 03 13:59:15 2010 +0100
@@ -256,7 +256,7 @@
*cnt += 100;
switch(e->type) {
case e_cmp:
- if (e->flag != cmp_or) {
+ if (!is_complex_exp(e->flag)) {
exp_count(cnt, seqnr, e->l);
exp_count(cnt, seqnr, e->r);
}
@@ -283,6 +283,10 @@
case cmp_notilike:
*cnt += 2;
return 2;
+ case cmp_in:
+ case cmp_notin:
+ *cnt += 9;
+ return 9;
case cmp_or: /* prefer union over like */
*cnt += 3;
return 3;
@@ -553,26 +557,15 @@
lr = find_basetable(lr);
rr = find_basetable(rr);
- if (!lr || !rr) {
- list_destroy(lexps);
- list_destroy(rexps);
- list_destroy(eje);
- list_destroy(aaje);
+ if (!lr || !rr)
continue;
- }
l = lr->l;
r = rr->l;
lcols = list_map(lexps, lr, (fmap) &table_colexp);
rcols = list_map(rexps, rr, (fmap) &table_colexp);
- list_destroy(lexps);
- list_destroy(rexps);
if (list_length(lcols) != list_length(rcols)) {
lcols->destroy = NULL;
rcols->destroy = NULL;
- list_destroy(lcols);
- list_destroy(rcols);
- list_destroy(eje);
- list_destroy(aaje);
continue;
}
@@ -602,18 +595,11 @@
}
lcols->destroy = NULL;
rcols->destroy = NULL;
- list_destroy(lcols);
- list_destroy(rcols);
- list_destroy(eje);
- list_destroy(aaje);
}
}
- list_destroy(aje);
/* sort expressions on weighted number of reducing operators */
sdje = order_join_expressions(dje, rels);
- list_destroy(dje);
-
return sdje;
}
@@ -739,7 +725,6 @@
for(n=exps->h; n; n = n->next)
_______________________________________________
Checkin-list mailing list
[email protected]
http://mail.monetdb.org/mailman/listinfo/checkin-list
