Update of /cvsroot/monetdb/sql/src/server
In directory 23jxhf1.ch3.sourceforge.com:/tmp/cvs-serv1066/src/server
Modified Files:
rel_bin.mx rel_dump.mx rel_exp.mx rel_optimizer.mx
rel_semantic.mx sql_parser.mx sql_rel2bin.mx sql_scan.mx
Log Message:
the optimizer now recognizes range joins and selects
U sql_rel2bin.mx
Index: sql_rel2bin.mx
===================================================================
RCS file: /cvsroot/monetdb/sql/src/server/sql_rel2bin.mx,v
retrieving revision 1.132
retrieving revision 1.133
diff -u -d -r1.132 -r1.133
--- sql_rel2bin.mx 13 Mar 2009 10:24:55 -0000 1.132
+++ sql_rel2bin.mx 8 Jun 2009 04:43:11 -0000 1.133
@@ -552,8 +552,8 @@
/* pairwise (re-)combine single-sided range selects to
double-sided range selects */
/*
* 0) a> x && a< y => x< a< y
- * 1) a> x && a<=y => x< a<=y
- * 2) a>=x && a< y => x<=a< y
+ * 1) a>=x && a< y => x<=a< y
+ * 2) a> x && a<=y => x< a<=y
* 3) a>=x && a<=y => x<=a<=y
*/
for (flg = 0; flg <= 3; flg++) {
@@ -565,13 +565,13 @@
cr = cmp_lt;
break;
case 1:
- cl = cmp_gt;
- cr = cmp_lte;
- break;
- case 2:
cl = cmp_gte;
cr = cmp_lt;
break;
+ case 2:
+ cl = cmp_gt;
+ cr = cmp_lte;
+ break;
case 3:
cl = cmp_gte;
cr = cmp_lte;
U rel_bin.mx
Index: rel_bin.mx
===================================================================
RCS file: /cvsroot/monetdb/sql/src/server/rel_bin.mx,v
retrieving revision 1.78
retrieving revision 1.79
diff -u -d -r1.78 -r1.79
--- rel_bin.mx 18 May 2009 07:18:14 -0000 1.78
+++ rel_bin.mx 8 Jun 2009 04:43:10 -0000 1.79
@@ -555,10 +555,12 @@
l =
stmt_const(bin_first_column(swapped?right:left), l);
if (r->nrcols == 0)
r =
stmt_const(bin_first_column(swapped?left:right), r);
- if (swapped) {
- s = stmt_join(r, stmt_reverse(l),
swap_compare((comp_type)e->flag));
- } else if (r2) {
+ if (r2) {
s = stmt_join2(l, r, r2, (comp_type)e->flag);
+ if (swapped)
+ s = stmt_reverse(s);
+ } else if (swapped) {
+ s = stmt_join(r, stmt_reverse(l),
swap_compare((comp_type)e->flag));
} else {
s = stmt_join(l, stmt_reverse(r),
(comp_type)e->flag);
}
@@ -566,14 +568,22 @@
if (r2) {
if (l->nrcols == 0 && r->nrcols == 0 &&
r2->nrcols == 0) {
sql_subtype *bt =
sql_bind_localtype("bit");
- sql_subfunc *f =
sql_bind_func(sql->session->schema, compare_func(range2compare(e->flag&3)),
tail_type(l), tail_type(r));
+ sql_subfunc *lf =
sql_bind_func(sql->session->schema, compare_func(range2lcompare(e->flag)),
tail_type(l), tail_type(r));
+ sql_subfunc *rf =
sql_bind_func(sql->session->schema, compare_func(range2rcompare(e->flag)),
tail_type(l), tail_type(r));
sql_subfunc *a =
sql_bind_func(sql->session->schema, "and", bt, bt);
- assert(f && a);
+ assert(lf && rf && a);
+ l = stmt_dup(l);
s = stmt_binop(
- stmt_binop(r, stmt_dup(l), f),
- stmt_binop(l, r2,
sql_dup_func(f)), a);
- } else
+ stmt_binop(l, r, lf),
+ stmt_binop(l, r2, rf), a);
+ } else if (l->nrcols > 0 && r->nrcols > 0 &&
r2->nrcols > 0) {
+ l = stmt_dup(l);
+ s = stmt_semijoin(
+ stmt_uselect(l, r,
range2lcompare(e->flag)),
+ stmt_uselect(l, r2,
range2rcompare(e->flag)));
+ } else {
s = stmt_uselect2(l, r, r2,
(comp_type)e->flag);
+ }
} else {
/* value compare or select */
if (l->nrcols == 0 && r->nrcols == 0) {
U rel_optimizer.mx
Index: rel_optimizer.mx
===================================================================
RCS file: /cvsroot/monetdb/sql/src/server/rel_optimizer.mx,v
retrieving revision 1.61
retrieving revision 1.62
diff -u -d -r1.61 -r1.62
--- rel_optimizer.mx 4 Jun 2009 20:27:54 -0000 1.61
+++ rel_optimizer.mx 8 Jun 2009 04:43:11 -0000 1.62
@@ -2196,8 +2196,11 @@
break;
case op_topn:
- if (proj)
+ if (proj) {
rel = rel ->l;
+ rel_mark_used(sql, rel, proj);
+ break;
+ }
case op_project:
case op_groupby:
if (proj && rel->l) {
@@ -2596,9 +2599,8 @@
/*sql_exp *lf = f->l;*/
sql_exp *rf = f->r;
- assert(f->type == e_cmp && e->flag != cmp_or);
- if (rf->card == CARD_ATOM && e->flag <
cmp_equal) {
- /*printf("possible candidate\n");*/
+ if (rf->card == CARD_ATOM && f->flag <
cmp_equal) {
+ printf("possible candidate\n");
}
}
}
@@ -2621,6 +2623,119 @@
return rel;
}
+static list *
+exp_merge_range(list *exps)
+{
+ node *n, *m;
+ for (n=exps->h; n && n->next; n = n->next) {
+ sql_exp *e = n->data;
+ sql_exp *le = e->l;
+ sql_exp *re = e->r;
+
+ /* only look for gt, gte, lte, lt */
+ if (e->type == e_cmp && e->flag < cmp_equal &&
+ re->card == CARD_ATOM) {
+ for (m=n->next; m; m = m->next) {
+ sql_exp *f = m->data;
+ sql_exp *lf = f->l;
+ sql_exp *rf = f->r;
+
+ if (f->type == e_cmp && f->flag < cmp_equal &&
+ rf->card == CARD_ATOM &&
+ exp_match_exp(le, lf)) {
+ sql_exp *ne;
+ int swap = 0, lt = 0, gt = 0;
+ /* for now only c1 <[=] x <[=] c2 */
+
+ swap = lt = (e->flag == cmp_lt ||
e->flag == cmp_lte);
+ gt = !lt;
+
+ if (gt &&
+ (f->flag == cmp_gt ||
+ f->flag == cmp_gte))
+ continue;
+ if (lt &&
+ (f->flag == cmp_lt ||
+ f->flag == cmp_lte))
+ continue;
+ if (!swap)
+ ne = exp_compare2(exp_dup(le),
exp_dup(re), exp_dup(rf), compare2range(e->flag, f->flag));
+ else
+ ne = exp_compare2(exp_dup(le),
exp_dup(rf), exp_dup(re), compare2range(f->flag, e->flag));
+
+ list_remove_data(exps, e);
+ list_remove_data(exps, f);
+ list_append(exps, ne);
+ return exp_merge_range(exps);
+ }
+ }
+ } else if (e->type == e_cmp && e->flag < cmp_equal &&
+ re->card > CARD_ATOM) {
+ for (m=n->next; m; m = m->next) {
+ sql_exp *f = m->data;
+ sql_exp *lf = f->l;
+ sql_exp *rf = f->r;
+
+ if (f->type == e_cmp && f->flag < cmp_equal &&
+ rf->card > CARD_ATOM) {
+ sql_exp *ne;
+ int swap = 0, lt = 0, gt = 0;
+ int ef = e->flag, ff = f->flag;
+
+ /* is left swapped ? */
+ if (exp_match_exp(re, lf)) {
+ sql_exp *t = re;
+
+ re = le;
+ le = t;
+ ef = swap_compare(ef);
+ }
+ /* is right swapped ? */
+ if (exp_match_exp(le, rf)) {
+ sql_exp *t = rf;
+
+ rf = lf;
+ lf = t;
+ ff = swap_compare(ff);
+ }
+
+ if (!exp_match_exp(le, lf))
+ continue;
+
+ /* for now only c1 <[=] x <[=] c2 */
+ swap = lt = (ef == cmp_lt || ef ==
cmp_lte);
+ gt = !lt;
+
+ if (gt && (ff == cmp_gt || ff ==
cmp_gte))
+ continue;
+ if (lt && (ff == cmp_lt || ff ==
cmp_lte))
+ continue;
+ if (!swap)
+ ne = exp_compare2(exp_dup(le),
exp_dup(re), exp_dup(rf), compare2range(ef, ff));
+ else
+ ne = exp_compare2(exp_dup(le),
exp_dup(rf), exp_dup(re), compare2range(ff, ef));
+
+ list_remove_data(exps, e);
+ list_remove_data(exps, f);
+ list_append(exps, ne);
+ return exp_merge_range(exps);
+ }
+ }
+ }
+ }
+ return exps;
+}
+
+static sql_rel *
+rel_find_range(int *changes, mvc *sql, sql_rel *rel)
+{
+ (void)sql;
+ *changes = 0;
+ if ((is_join(rel->op) || rel->op == op_select) && rel->exps &&
list_length(rel->exps)>1)
+ rel->exps = exp_merge_range(rel->exps);
+ return rel;
+}
+
static int
is_identity( sql_exp *e, sql_rel *r)
{
@@ -2814,11 +2929,14 @@
if (gp.cnt[op_join] || gp.cnt[op_left])
rel = rewrite(sql, rel, &rel_join_order);
- /* Todo rel_find_range_exps (and then remove from later code) */
-
- if (gp.cnt[op_select])
+ if (gp.cnt[op_select])
rel = rewrite(sql, rel, &rel_select_order);
+ if (gp.cnt[op_join] ||
+ gp.cnt[op_left] || gp.cnt[op_right] || gp.cnt[op_full] ||
+ gp.cnt[op_select])
+ rel = rewrite(sql, rel, &rel_find_range);
+
if (gp.cnt[op_select])
rel = rewrite(sql, rel, &rel_select_use_index);
U rel_dump.mx
Index: rel_dump.mx
===================================================================
RCS file: /cvsroot/monetdb/sql/src/server/rel_dump.mx,v
retrieving revision 1.37
retrieving revision 1.38
diff -u -d -r1.37 -r1.38
--- rel_dump.mx 13 Mar 2009 21:17:15 -0000 1.37
+++ rel_dump.mx 8 Jun 2009 04:43:10 -0000 1.38
@@ -145,16 +145,23 @@
exps_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->f) {
+ exp_print(sql, fout, e->r, depth+1, 0, 0);
+ if (is_anti(e))
+ stream_printf(fout, " ! ");
+ cmp_print(sql, fout,
swap_compare(range2lcompare(e->flag)) );
+ exp_print(sql, fout, e->l, depth+1, 0, 0);
+ if (is_anti(e))
+ stream_printf(fout, " ! ");
+ cmp_print(sql, fout, range2rcompare(e->flag) );
+ exp_print(sql, fout, e->f, depth+1, 0, 0);
} else {
exp_print(sql, fout, e->l, depth+1, 0, 0);
if (is_anti(e))
stream_printf(fout, " ! ");
cmp_print(sql, fout, e->flag );
+
exp_print(sql, fout, e->r, depth+1, 0, 0);
- if (e->f) {
- stream_printf(fout, ", ");
- exp_print(sql, fout, e->f, depth+1, 0, 0);
- }
}
break;
default:
U rel_semantic.mx
Index: rel_semantic.mx
===================================================================
RCS file: /cvsroot/monetdb/sql/src/server/rel_semantic.mx,v
retrieving revision 1.36
retrieving revision 1.37
diff -u -d -r1.36 -r1.37
--- rel_semantic.mx 22 Jan 2009 21:58:06 -0000 1.36
+++ rel_semantic.mx 8 Jun 2009 04:43:11 -0000 1.37
@@ -33,7 +33,9 @@
extern sql_rel *rel_parse(mvc *m, char *query, char emode);
extern comp_type swap_compare( comp_type t );
-extern comp_type range2compare( int r );
+extern comp_type range2lcompare( int r );
+extern comp_type range2rcompare( int r );
+extern int compare2range( comp_type l, comp_type r );
#endif /*_REL_SEMANTIC_H_*/
@@ -74,22 +76,43 @@
}
comp_type
-range2compare( int r )
+range2lcompare( int r )
{
- switch(r) {
- case 0:
- return cmp_gt;
- case 1:
+ if (r&1) {
return cmp_gte;
- case 2:
+ } else {
+ return cmp_gt;
+ }
+}
+
+comp_type
+range2rcompare( int r )
+{
+ if (r&2) {
return cmp_lte;
- case 3:
+ } else {
return cmp_lt;
- default:
- return cmp_gte;
}
}
+int
+compare2range( comp_type l, comp_type r )
+{
+ if (l == cmp_gt) {
+ if (r == cmp_lt)
+ return 0;
+ else if (r == cmp_lte)
+ return 2;
+ } else if (l == cmp_gte) {
+ if (r == cmp_lt)
+ return 1;
+ else if (r == cmp_lte)
+ return 3;
+ }
+ return -1;
+}
+
+
sql_rel *
rel_parse(mvc *m, char *query, char emode)
{
U rel_exp.mx
Index: rel_exp.mx
===================================================================
RCS file: /cvsroot/monetdb/sql/src/server/rel_exp.mx,v
retrieving revision 1.31
retrieving revision 1.32
diff -u -d -r1.31 -r1.32
--- rel_exp.mx 9 Apr 2009 14:34:14 -0000 1.31
+++ rel_exp.mx 8 Jun 2009 04:43:10 -0000 1.32
@@ -262,7 +262,8 @@
return e;
}
-sql_exp * exp_atom( atom *a)
+sql_exp *
+exp_atom( atom *a)
{
sql_exp *e = exp_create(e_atom);
e->card = CARD_ATOM;
U sql_scan.mx
Index: sql_scan.mx
===================================================================
RCS file: /cvsroot/monetdb/sql/src/server/sql_scan.mx,v
retrieving revision 1.148
retrieving revision 1.149
diff -u -d -r1.148 -r1.149
--- sql_scan.mx 5 Jun 2009 08:48:27 -0000 1.148
+++ sql_scan.mx 8 Jun 2009 04:43:11 -0000 1.149
@@ -316,7 +316,6 @@
keywords_insert("EXPLAIN", SQL_EXPLAIN);
keywords_insert("PLAN", SQL_PLAN);
- keywords_insert("RECORD", SQL_RECORD);
keywords_insert("DEBUG", SQL_DEBUG);
keywords_insert("TRACE", SQL_TRACE);
keywords_insert("PREPARE", PREPARE);
U sql_parser.mx
Index: sql_parser.mx
===================================================================
RCS file: /cvsroot/monetdb/sql/src/server/sql_parser.mx,v
retrieving revision 1.309
retrieving revision 1.310
diff -u -d -r1.309 -r1.310
--- sql_parser.mx 5 Jun 2009 08:48:23 -0000 1.309
+++ sql_parser.mx 8 Jun 2009 04:43:11 -0000 1.310
@@ -691,7 +691,7 @@
%token CHECK CONSTRAINT CREATE
%token TYPE PROCEDURE FUNCTION AGGREGATE RETURNS EXTERNAL sqlNAME DECLARE
%token CALL LANGUAGE
-%token SQL_EXPLAIN SQL_PLAN SQL_RECORD SQL_DEBUG SQL_TRACE PREPARE EXECUTE
+%token SQL_EXPLAIN SQL_PLAN SQL_DEBUG SQL_TRACE PREPARE EXECUTE
%token DEFAULT DISTINCT DROP
%token FOREIGN
%token ENCRYPTED UNENCRYPTED PASSWORD GRANT REVOKE ROLE ADMIN INTO
@@ -4420,7 +4420,6 @@
| PREPARE { $$ = sa_strdup(SA, "prepare"); }
| EXECUTE { $$ = sa_strdup(SA, "execute"); }
| SQL_EXPLAIN { $$ = sa_strdup(SA, "explain"); }
-| SQL_RECORD { $$ = sa_strdup(SA, "record"); }
| SQL_DEBUG { $$ = sa_strdup(SA, "debug"); }
| SQL_TRACE { $$ = sa_strdup(SA, "trace"); }
| AUTO_COMMIT { $$ = sa_strdup(SA, "auto_commit"); }
------------------------------------------------------------------------------
OpenSolaris 2009.06 is a cutting edge operating system for enterprises
looking to deploy the next generation of Solaris that includes the latest
innovations from Sun and the OpenSource community. Download a copy and
enjoy capabilities such as Networking, Storage and Virtualization.
Go to: http://p.sf.net/sfu/opensolaris-get
_______________________________________________
Monetdb-sql-checkins mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/monetdb-sql-checkins
