Changeset: 6211e231de43 for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=6211e231de43
Added Files:
sql/test/BugTracker-2012/Tests/case_evaluated_too_early.Bug-3186.sql
sql/test/BugTracker-2012/Tests/case_evaluated_too_early.Bug-3186.stable.err
sql/test/BugTracker-2012/Tests/case_evaluated_too_early.Bug-3186.stable.out
sql/test/BugTracker-2012/Tests/recursive_optimizer.Bug-3191.sql
sql/test/BugTracker-2012/Tests/recursive_optimizer.Bug-3191.stable.err
sql/test/BugTracker-2012/Tests/recursive_optimizer.Bug-3191.stable.out
Modified Files:
sql/benchmarks/viam/Tests/viam.sql
sql/benchmarks/viam/Tests/viam.stable.out
sql/server/rel_optimizer.c
sql/test/BugTracker-2012/Tests/All
Branch: default
Log Message:
Merge with Oct2012 branch.
diffs (truncated from 501 to 300 lines):
diff --git a/sql/benchmarks/viam/Tests/viam.sql
b/sql/benchmarks/viam/Tests/viam.sql
--- a/sql/benchmarks/viam/Tests/viam.sql
+++ b/sql/benchmarks/viam/Tests/viam.sql
@@ -5,4 +5,5 @@ select s1.framenumber, avg(c.zanger_c)
and s2.video_id = v.video_id
and v.media_uri = '20050407_mocky.mpg'
and s2.framenumber between s1.framenumber and (s1.framenumber + 124)
- group by s1.framenumber;
+ group by s1.framenumber
+ order by s1.framenumber;
diff --git a/sql/benchmarks/viam/Tests/viam.stable.out
b/sql/benchmarks/viam/Tests/viam.stable.out
--- a/sql/benchmarks/viam/Tests/viam.stable.out
+++ b/sql/benchmarks/viam/Tests/viam.stable.out
@@ -29,6 +29,15 @@ Ready.
% int, double # type
% 2, 24 # length
[ 0, 0.3069530136 ]
+[ 1, 0.3113380553 ]
+[ 2, 0.3158491548 ]
+[ 3, 0.3202903953 ]
+[ 4, 0.3248978475 ]
+[ 5, 0.3292224557 ]
+[ 6, 0.3330400058 ]
+[ 7, 0.3366659278 ]
+[ 8, 0.3393862282 ]
+[ 9, 0.3415804093 ]
[ 10, 0.3421598259 ]
[ 11, 0.3414150065 ]
[ 12, 0.3382639558 ]
@@ -39,29 +48,12 @@ Ready.
[ 17, 0.3240110451 ]
[ 18, 0.3202647068 ]
[ 19, 0.3168082588 ]
-[ 1, 0.3113380553 ]
[ 20, 0.3158073029 ]
[ 21, 0.3154659088 ]
[ 22, 0.3139201927 ]
[ 23, 0.3134433635 ]
[ 24, 0.3130393499 ]
-[ 2, 0.3158491548 ]
-[ 3, 0.3202903953 ]
-[ 4, 0.3248978475 ]
-[ 5, 0.3292224557 ]
-[ 6, 0.3330400058 ]
-[ 7, 0.3366659278 ]
-[ 8, 0.3393862282 ]
-[ 9, 0.3415804093 ]
[ 25, 0.3135247271 ]
-[ 35, 0.3000286778 ]
-[ 36, 0.296002269 ]
-[ 37, 0.290913483 ]
-[ 38, 0.2835143166 ]
-[ 39, 0.2787728265 ]
-[ 40, 0.2731191759 ]
-[ 41, 0.2711542817 ]
-[ 42, 0.2679928431 ]
[ 26, 0.3128866098 ]
[ 27, 0.3136826694 ]
[ 28, 0.3143216616 ]
@@ -71,6 +63,14 @@ Ready.
[ 32, 0.3090121901 ]
[ 33, 0.3060122738 ]
[ 34, 0.3028020651 ]
+[ 35, 0.3000286778 ]
+[ 36, 0.296002269 ]
+[ 37, 0.290913483 ]
+[ 38, 0.2835143166 ]
+[ 39, 0.2787728265 ]
+[ 40, 0.2731191759 ]
+[ 41, 0.2711542817 ]
+[ 42, 0.2679928431 ]
[ 43, 0.2675193019 ]
[ 44, 0.2673059053 ]
[ 45, 0.2655931324 ]
@@ -79,6 +79,11 @@ Ready.
[ 48, 0.2567394975 ]
[ 49, 0.2519025657 ]
[ 50, 0.2493345457 ]
+[ 51, 0.2496920232 ]
+[ 52, 0.2486809716 ]
+[ 53, 0.2519123037 ]
+[ 54, 0.2604823805 ]
+[ 55, 0.271042367 ]
[ 60, 0.2834349046 ]
[ 61, 0.2985857118 ]
[ 62, 0.3154686896 ]
@@ -89,16 +94,11 @@ Ready.
[ 67, 0.2983151227 ]
[ 68, 0.3048942813 ]
[ 69, 0.315040494 ]
-[ 51, 0.2496920232 ]
[ 70, 0.3288443923 ]
[ 71, 0.3404737413 ]
[ 72, 0.3572909931 ]
[ 73, 0.3603464961 ]
[ 74, 0.3620469868 ]
-[ 52, 0.2486809716 ]
-[ 53, 0.2519123037 ]
-[ 54, 0.2604823805 ]
-[ 55, 0.271042367 ]
# 19:17:39 >
# 19:17:39 > Done.
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
@@ -173,6 +173,7 @@ exp_find_column_( sql_rel *rel, sql_exp
return NULL;
}
+/* find column for the select/join expression */
static sql_column *
sjexp_col(sql_exp *e, sql_rel *r)
{
@@ -728,7 +729,7 @@ order_joins(mvc *sql, list *rels, list *
/* complex expressions may touch multiple base tables
* Should be pushed up to extra selection.
* */
- if (cje->type != e_cmp || !is_complex_exp(cje->flag) /*||
+ if (cje->type != e_cmp || !is_complex_exp(cje->flag) ||
!find_prop(cje->p, PROP_HASHCOL) /*||
(cje->type == e_cmp && cje->f == NULL)*/) {
l = find_one_rel(rels, cje->l);
r = find_one_rel(rels, cje->r);
@@ -2051,12 +2052,24 @@ exp_case_fixup( mvc *sql, sql_exp *e )
a1 = sql_div_fixup(sql, a1, cond, 0);
} else if (a1->type == e_func && a1->l) {
a1->l = exps_case_fixup(sql, a1->l, cond, 0);
+ } else if (a1->type == e_convert) {
+ sql_exp *l = a1->l;
+ sql_subfunc *f = l->f;
+
+ if (l->type == e_func && !f->func->s &&
!strcmp(f->func->base.name, "sql_div"))
+ a1->l = sql_div_fixup(sql, l, cond, 0);
}
if (a2->type == e_func && !a2f->func->s &&
!strcmp(a2f->func->base.name, "sql_div")) {
a2 = sql_div_fixup(sql, a2, cond, 1);
} else if (a2->type == e_func && a2->l) {
a2->l = exps_case_fixup(sql, a2->l, cond, 1);
+ } else if (a2->type == e_convert) {
+ sql_exp *l = a2->l;
+ sql_subfunc *f = l->f;
+
+ if (l->type == e_func && !f->func->s &&
!strcmp(f->func->base.name, "sql_div"))
+ a2->l = sql_div_fixup(sql, l, cond, 1);
}
nne = exp_op3(sql->sa, cond, a1, a2, ne->f);
exp_setname(sql->sa, nne, ne->rname, ne->name );
@@ -2688,6 +2701,10 @@ rel_push_select_down(int *changes, mvc *
if (rel_is_ref(rel))
return rel;
+ /* don't make changes for empty selects */
+ if (is_select(rel->op) && (!rel->exps || list_length(rel->exps) == 0))
+ return rel;
+
/* merge 2 selects */
r = rel->l;
if (is_select(rel->op) && r && is_select(r->op) && !(rel_is_ref(r))) {
@@ -4668,6 +4685,7 @@ find_index(sql_allocator *sa, sql_rel *r
/* Depending on the index type we should (in the rel_bin) generate
more code, ie for spatial index add post filter etc, for hash
compute hash value and use index */
+
if (sub->exps && rel->exps)
for(n = sub->exps->h; n; n = n->next) {
prop *p;
@@ -4688,6 +4706,8 @@ find_index(sql_allocator *sa, sql_rel *r
/* now we obtain the columns, move into
sql_column_kc_cmp! */
cols = list_map(exps, sub, (fmap) &sjexp_col);
+ /* TODO check that at most 2 relations are involved */
+
/* Match the index columns with the expression columns.
TODO, Allow partial matches ! */
if (list_match(cols, i->columns, cmp) == 0) {
@@ -4736,20 +4756,34 @@ rel_use_index(int *changes, mvc *sql, sq
if (i) {
prop *p;
node *n;
+ int single_table = 1;
+ sql_exp *re = NULL;
- /* add PROP_HASHCOL to all column exps */
- for( n = exps->h; n; n = n->next) {
+ for( n = exps->h; n && single_table; n = n->next) {
sql_exp *e = n->data;
-
- /* swapped ? */
+ sql_exp *nre = e->r;
+
if (is_join(rel->op) &&
- ((left && !rel_find_exp(rel->l, e->l))
||
- (!left && !rel_find_exp(rel->r,
e->l))))
- n->data = e = exp_compare(sql->sa,
e->r, e->l, cmp_equal);
- p = find_prop(e->p, PROP_HASHCOL);
- if (!p)
- e->p = p = prop_create(sql->sa,
PROP_HASHCOL, e->p);
- p->value = i;
+ ((left && !rel_find_exp(rel->l, e->l))
||
+ (!left && !rel_find_exp(rel->r,
e->l))))
+ nre = e->l;
+ single_table = (re && !exps_match_col_exps(nre,
re));
+ re = nre;
+ }
+ if (single_table) { /* add PROP_HASHCOL to all column
exps */
+ for( n = exps->h; n; n = n->next) {
+ sql_exp *e = n->data;
+
+ /* swapped ? */
+ if (is_join(rel->op) &&
+ ((left && !rel_find_exp(rel->l,
e->l)) ||
+ (!left && !rel_find_exp(rel->r,
e->l))))
+ n->data = e =
exp_compare(sql->sa, e->r, e->l, cmp_equal);
+ p = find_prop(e->p, PROP_HASHCOL);
+ if (!p)
+ e->p = p = prop_create(sql->sa,
PROP_HASHCOL, e->p);
+ p->value = i;
+ }
}
/* add the remaining exps to the new exp list */
if (list_length(rel->exps) > list_length(exps)) {
@@ -5597,8 +5631,10 @@ _rel_optimizer(mvc *sql, sql_rel *rel, i
rel = rewrite(sql, rel, &rel_merge_table_rewrite, &changes);
- if (changes && level > 10)
+ if (changes && level > 10) {
assert(0);
+ return rel;
+ }
if (changes)
return _rel_optimizer(sql, rel, ++level);
diff --git a/sql/test/BugTracker-2012/Tests/All
b/sql/test/BugTracker-2012/Tests/All
--- a/sql/test/BugTracker-2012/Tests/All
+++ b/sql/test/BugTracker-2012/Tests/All
@@ -54,3 +54,6 @@ many-columns-truncated.Bug-3161
aggregate_incorrect_precision_scale.Bug-3182
timestamp-diff.Bug-3190
string-insert-default-null-crash.Bug-3168
+case_evaluated_too_early.Bug-3186
+recursive_optimizer.Bug-3191
+join_over_multitable_using_index.Bug-3181
diff --git
a/sql/test/BugTracker-2012/Tests/case_evaluated_too_early.Bug-3186.sql
b/sql/test/BugTracker-2012/Tests/case_evaluated_too_early.Bug-3186.sql
new file mode 100644
--- /dev/null
+++ b/sql/test/BugTracker-2012/Tests/case_evaluated_too_early.Bug-3186.sql
@@ -0,0 +1,2 @@
+SELECT CASE WHEN TRUE THEN -10 ELSE 0/0 END;
+SELECT coalesce (CASE WHEN TRUE THEN -10. end, case when false then 0/0 END);
diff --git
a/sql/test/BugTracker-2012/Tests/case_evaluated_too_early.Bug-3186.stable.err
b/sql/test/BugTracker-2012/Tests/case_evaluated_too_early.Bug-3186.stable.err
new file mode 100644
--- /dev/null
+++
b/sql/test/BugTracker-2012/Tests/case_evaluated_too_early.Bug-3186.stable.err
@@ -0,0 +1,36 @@
+stderr of test 'case_evaluated_too_early.Bug-3186` in directory
'test/BugTracker-2012` itself:
+
+
+# 17:10:19 >
+# 17:10:19 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set"
"gdk_dbfarm=/home/niels/scratch/rc-clean/Linux-x86_64/var/MonetDB" "--set"
"mapi_open=true" "--set" "mapi_port=35816" "--set" "monet_prompt=" "--trace"
"--forcemito" "--set" "mal_listing=2" "--dbname=mTests_test_BugTracker-2012"
"--set" "mal_listing=0"
+# 17:10:19 >
+
+# builtin opt gdk_dbname = demo
+# builtin opt gdk_dbfarm =
/home/niels/scratch/rc-clean/Linux-x86_64/var/monetdb5/dbfarm
+# builtin opt gdk_debug = 0
+# builtin opt gdk_vmtrim = yes
+# builtin opt monet_prompt = >
+# builtin opt monet_daemon = no
+# builtin opt mapi_port = 50000
+# builtin opt mapi_open = false
+# builtin opt mapi_autosense = false
+# builtin opt sql_optimizer = default_pipe
+# builtin opt sql_debug = 0
+# cmdline opt gdk_nr_threads = 0
+# cmdline opt gdk_dbfarm =
/home/niels/scratch/rc-clean/Linux-x86_64/var/MonetDB
+# cmdline opt mapi_open = true
+# cmdline opt mapi_port = 35816
+# cmdline opt monet_prompt =
+# cmdline opt mal_listing = 2
+# cmdline opt gdk_dbname = mTests_test_BugTracker-2012
+# cmdline opt mal_listing = 0
+
+# 17:10:20 >
+# 17:10:20 > "mclient" "-lsql" "-ftest" "-Eutf-8" "-i" "-e" "--host=niels"
"--port=35816"
+# 17:10:20 >
+
+
+# 17:10:20 >
+# 17:10:20 > "Done."
+# 17:10:20 >
+
diff --git
a/sql/test/BugTracker-2012/Tests/case_evaluated_too_early.Bug-3186.stable.out
b/sql/test/BugTracker-2012/Tests/case_evaluated_too_early.Bug-3186.stable.out
new file mode 100644
--- /dev/null
+++
b/sql/test/BugTracker-2012/Tests/case_evaluated_too_early.Bug-3186.stable.out
@@ -0,0 +1,43 @@
+stdout of test 'case_evaluated_too_early.Bug-3186` in directory
'test/BugTracker-2012` itself:
+
+
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
http://mail.monetdb.org/mailman/listinfo/checkin-list
