Changeset: 0d2c55c221c2 for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=0d2c55c221c2
Modified Files:
sql/server/rel_optimizer.c
sql/server/rel_rel.c
sql/server/rel_select.c
sql/test/BugTracker-2016/Tests/innerjoin-leftjoin-or.Bug-4049.sql
sql/test/BugTracker-2016/Tests/innerjoin-leftjoin-or.Bug-4049.stable.out
sql/test/BugTracker-2016/Tests/memory-consumption-query-PLAN-25joins.Bug-3972.stable.out
Branch: Jun2016
Log Message:
fixed leftjoin problem / bug 4049
diffs (253 lines):
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
@@ -6647,56 +6647,6 @@ add_nulls(mvc *sql, sql_rel *rel, sql_re
}
}
-static int
-exp_is_select(sql_exp *e, sql_rel *r)
-{
- if (e->type == e_cmp && exp_card(e->r) <= CARD_AGGR &&
- rel_find_exp(r, e->l) && !e->f)
- return 0;
- if (e->type == e_cmp && e->flag == cmp_or) {
- list *l = e->l;
- node *n;
- int ok = 1;
-
- for (n = l->h; n && ok; n = n->next)
- ok &= (exp_is_select(n->data, r) == 0);
- l = e->r;
- for (n = l->h; n && ok; n = n->next)
- ok &= (exp_is_select(n->data, r) == 0);
- if (ok)
- return 0;
- }
- return -1;
-}
-
-static sql_rel *
-rel_split_outerjoin_exps(int *changes, mvc *sql, sql_rel *rel)
-{
- if (rel->op == op_left && list_length(rel->exps)) {
- /* find select expressions on left hand relation */
- list *exps = list_select(rel->exps, rel->l, (fcmp)
&exp_is_select, (fdup)NULL);
- /* if so introduce select operator and move these expressions */
- if (!list_empty(exps)) {
- list_remove_list(rel->exps, exps);
- rel = rel_select(sql->sa, rel, NULL);
- rel->exps = exps;
- (*changes)++;
- }
- }
- if (rel->op == op_right && list_length(rel->exps)) {
- /* find select expressions on right hand relation */
- list *exps = list_select(rel->exps, rel->r, (fcmp)
&exp_is_select, (fdup)NULL);
- /* if so introduce select operator and move these expressions */
- if (!list_empty(exps)) {
- list_remove_list(rel->exps, exps);
- rel = rel_select(sql->sa, rel, NULL);
- rel->exps = exps;
- (*changes)++;
- }
- }
- return rel;
-}
-
static sql_rel *
rel_split_outerjoin(int *changes, mvc *sql, sql_rel *rel)
{
@@ -7910,8 +7860,6 @@ static sql_rel *
gp.cnt[op_left] || gp.cnt[op_right] || gp.cnt[op_full] ||
gp.cnt[op_semi] || gp.cnt[op_anti] ||
gp.cnt[op_select]) {
- if (level <= 0 && 0)
- rel = rewrite(sql, rel, &rel_split_outerjoin_exps,
&changes);
rel = rewrite(sql, rel, &rel_find_range, &changes);
rel = rel_project_reduce_casts(&changes, sql, rel);
rel = rewrite(sql, rel, &rel_reduce_casts, &changes);
diff --git a/sql/server/rel_rel.c b/sql/server/rel_rel.c
--- a/sql/server/rel_rel.c
+++ b/sql/server/rel_rel.c
@@ -1046,6 +1046,8 @@ rel_or(mvc *sql, sql_rel *l, sql_rel *r,
rel_destroy(r);
append(nl, e);
+ if (is_outerjoin(l->op) && is_processed(l))
+ l = rel_select(sql->sa, l, NULL);
l->exps = nl;
return 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
@@ -1406,7 +1406,7 @@ rel_filter(mvc *sql, sql_rel *rel, list
/* push select into the given relation */
return rel_push_select(sql, rel, L, e);
} else { /* join */
- if (is_semi(rel->op) || is_outerjoin(rel->op)) {
+ if (is_semi(rel->op) || (is_outerjoin(rel->op) &&
!is_processed(rel))) {
rel_join_add_exp(sql->sa, rel, e);
return rel;
}
@@ -2020,7 +2020,7 @@ rel_logical_exp(mvc *sql, sql_rel *rel,
lr = rel;
rr = rel_dup(lr);
- if (is_outerjoin(rel->op)) {
+ if (is_outerjoin(rel->op) && !is_processed(rel)) {
int pushdown = sql->pushdown;
exps = rel->exps;
diff --git a/sql/test/BugTracker-2016/Tests/innerjoin-leftjoin-or.Bug-4049.sql
b/sql/test/BugTracker-2016/Tests/innerjoin-leftjoin-or.Bug-4049.sql
--- a/sql/test/BugTracker-2016/Tests/innerjoin-leftjoin-or.Bug-4049.sql
+++ b/sql/test/BugTracker-2016/Tests/innerjoin-leftjoin-or.Bug-4049.sql
@@ -43,7 +43,7 @@ where dd.c = 1
or dd.c = 3
and d1.d = 1
and (((dd.c = 1 or dd.c = 3) and d2.d is null) or ((dd.c = 1 or dd.c = 3)
and d2.d = 2));
--- shows 4 rows instead of 1
+-- shows 4 rows instead of 2
select *
from test_dic1 as dd
diff --git
a/sql/test/BugTracker-2016/Tests/innerjoin-leftjoin-or.Bug-4049.stable.out
b/sql/test/BugTracker-2016/Tests/innerjoin-leftjoin-or.Bug-4049.stable.out
--- a/sql/test/BugTracker-2016/Tests/innerjoin-leftjoin-or.Bug-4049.stable.out
+++ b/sql/test/BugTracker-2016/Tests/innerjoin-leftjoin-or.Bug-4049.stable.out
@@ -128,8 +128,6 @@ Ready.
% 1, 1, 1, 1, 1, 1, 1, 1 # length
[ 1, 1, 1, 2, 1, 1, 3, 2 ]
[ 1, 1, 1, 3, 2, 1, 3, 2 ]
-[ 1, 2, 1, 2, 1, NULL, NULL, NULL ]
-[ 1, 2, 1, 3, 2, NULL, NULL, NULL ]
#select *
# from test_dic1 as dd
#inner join test1 as d1 on d1.a = dd.a and d1.d = 1
diff --git
a/sql/test/BugTracker-2016/Tests/memory-consumption-query-PLAN-25joins.Bug-3972.stable.out
b/sql/test/BugTracker-2016/Tests/memory-consumption-query-PLAN-25joins.Bug-3972.stable.out
---
a/sql/test/BugTracker-2016/Tests/memory-consumption-query-PLAN-25joins.Bug-3972.stable.out
+++
b/sql/test/BugTracker-2016/Tests/memory-consumption-query-PLAN-25joins.Bug-3972.stable.out
@@ -89,10 +89,10 @@ Ready.
% .plan # table_name
% rel # name
% clob # type
-% 2314 # length
+% 2317 # length
top N (
| project (
-| | left outer join (
+| | select (
| | | left outer join (
| | | | left outer join (
| | | | | left outer join (
@@ -117,57 +117,59 @@ top N (
| | | | | | | | | | | | | | | | | | | | | | | | left outer join (
| | | | | | | | | | | | | | | | | | | | | | | | | left outer join (
| | | | | | | | | | | | | | | | | | | | | | | | | | left outer join (
-| | | | | | | | | | | | | | | | | | | | | | | | | | | table(sys.table1) [
table1.t1pkcol NOT NULL HASHCOL , table1.t1cola1, table1.t1cola11,
table1.t1cola12, table1.t1cola82, table1.t1cola91, table1.t1cola101,
table1.t1cola111, table1.t1cola112, table1.t1cola114, table1.t1colb1,
table1.t1colb111, table1.t1colb112, table1.t1colb113, table1.t1colb114,
table1.t1colc91, table1.t1cold1, table1.t1cold111, table1.t1cold112,
table1.t1cold113 ] COUNT ,
-| | | | | | | | | | | | | | | | | | | | | | | | | | | table(sys.table4) [
table4.t4cola1, table4.t4cola2, table4.t4cola111, table4.t4colb111,
table4.t4colb112, table4.t4colb114, table4.t4colb115 ] COUNT
-| | | | | | | | | | | | | | | | | | | | | | | | | | ) [ table1.t1cola111 =
table4.t4cola111 ],
-| | | | | | | | | | | | | | | | | | | | | | | | | | table(sys.table2) [
table2.t2cola1, table2.t2cola10, table2.t2cola81, table2.t2cola82,
table2.t2cola112, table2.t2cola113, table2.t2colc111, table2.t2colc112,
table2.t2colc113, table2.t2colc114, table2.t2colc115 ] COUNT
-| | | | | | | | | | | | | | | | | | | | | | | | | ) [ table1.t1cola112 =
table2.t2cola112 ],
-| | | | | | | | | | | | | | | | | | | | | | | | | table(sys.table5) [
table5.t5cola1, table5.t5cola2, table5.t5cola3, table5.t5cola5,
table5.t5cola81, table5.t5cola113, table5.t5colb113 ] COUNT
-| | | | | | | | | | | | | | | | | | | | | | | | ) [ table5.t5cola113 =
table2.t2cola113 ],
-| | | | | | | | | | | | | | | | | | | | | | | | table(sys.table6) [
table6.t6pkcol NOT NULL HASHCOL as lookup1.t6pkcol ] COUNT
-| | | | | | | | | | | | | | | | | | | | | | | ) [ table1.t1cola114 =
lookup1.t6pkcol NOT NULL HASHCOL ],
-| | | | | | | | | | | | | | | | | | | | | | | table(sys.table2) [
table2.t2colb111 as lookup2.t2colb111 ] COUNT
-| | | | | | | | | | | | | | | | | | | | | | ) [ table1.t1colb111 =
lookup2.t2colb111 ],
-| | | | | | | | | | | | | | | | | | | | | | table(sys.table5) [
table5.t5colb112 as lookup3.t5colb112 ] COUNT
-| | | | | | | | | | | | | | | | | | | | | ) [ table1.t1colb112 =
lookup3.t5colb112 ],
-| | | | | | | | | | | | | | | | | | | | | table(sys.table7) [ table7.t7colb113
as lookup4.t7colb113 ] COUNT
-| | | | | | | | | | | | | | | | | | | | ) [ table1.t1colb113 =
lookup4.t7colb113 ],
-| | | | | | | | | | | | | | | | | | | | table(sys.table8) [ table8.t8colb114
as lookup5.t8colb114 ] COUNT
-| | | | | | | | | | | | | | | | | | | ) [ table1.t1colb114 = lookup5.t8colb114
],
-| | | | | | | | | | | | | | | | | | | table(sys.table6) [ table6.t6pkcol NOT
NULL HASHCOL as lookup11.t6pkcol ] COUNT
-| | | | | | | | | | | | | | | | | | ) [ table4.t4colb111 = lookup11.t6pkcol
NOT NULL HASHCOL ],
-| | | | | | | | | | | | | | | | | | table(sys.table2) [ table2.t2colb112 as
lookup21.t2colb112 ] COUNT
-| | | | | | | | | | | | | | | | | ) [ table4.t4colb112 = lookup21.t2colb112 ],
-| | | | | | | | | | | | | | | | | table(sys.table5) [ table5.t5pkcol NOT NULL
HASHCOL as lookup31.t5pkcol ] COUNT
-| | | | | | | | | | | | | | | | ) [ table5.t5colb113 = lookup31.t5pkcol NOT
NULL HASHCOL ],
-| | | | | | | | | | | | | | | | table(sys.table7) [ table7.t7pkcol NOT NULL
HASHCOL as lookup41.t7pkcol ] COUNT
-| | | | | | | | | | | | | | | ) [ table4.t4colb114 = lookup41.t7pkcol NOT NULL
HASHCOL ],
-| | | | | | | | | | | | | | | table(sys.table8) [ table8.t8pkcol NOT NULL
HASHCOL as lookup51.t8pkcol ] COUNT
-| | | | | | | | | | | | | | ) [ table4.t4colb115 = lookup51.t8pkcol NOT NULL
HASHCOL ],
-| | | | | | | | | | | | | | table(sys.table6) [ table6.t6pkcol NOT NULL
HASHCOL as clookup1.t6pkcol ] COUNT
-| | | | | | | | | | | | | ) [ table2.t2colc111 = clookup1.t6pkcol NOT NULL
HASHCOL ],
-| | | | | | | | | | | | | table(sys.table2) [ table2.t2pkcol NOT NULL HASHCOL
as clookup2.t2pkcol ] COUNT
-| | | | | | | | | | | | ) [ table2.t2colc112 = clookup2.t2pkcol NOT NULL
HASHCOL ],
-| | | | | | | | | | | | table(sys.table5) [ table5.t5pkcol NOT NULL HASHCOL
as clookup3.t5pkcol ] COUNT
-| | | | | | | | | | | ) [ table2.t2colc113 = clookup3.t5pkcol NOT NULL HASHCOL
],
-| | | | | | | | | | | table(sys.table7) [ table7.t7pkcol NOT NULL HASHCOL as
clookup4.t7pkcol ] COUNT
-| | | | | | | | | | ) [ table2.t2colc114 = clookup4.t7pkcol NOT NULL HASHCOL
],
-| | | | | | | | | | table(sys.table8) [ table8.t8pkcol NOT NULL HASHCOL as
clookup5.t8pkcol ] COUNT
-| | | | | | | | | ) [ table2.t2colc115 = clookup5.t8pkcol NOT NULL HASHCOL ],
-| | | | | | | | | table(sys.table9) [ table9.t9pkcol NOT NULL HASHCOL ,
table9.t9cola1, table9.t9cola91, table9.t9cola111 ] COUNT
-| | | | | | | | ) [ table1.t1pkcol NOT NULL HASHCOL = table9.t9cola111 ],
-| | | | | | | | table(sys.table10) [ table10.t10pkcol NOT NULL HASHCOL ,
table10.t10cola1, table10.t10cola91 ] COUNT
-| | | | | | | ) [ table9.t9pkcol NOT NULL HASHCOL = table10.t10pkcol NOT NULL
HASHCOL ],
-| | | | | | | table(sys.table11) [ table11.t11pkcol NOT NULL HASHCOL ,
table11.t11cola91 ] COUNT
-| | | | | | ) [ table9.t9pkcol NOT NULL HASHCOL = table11.t11pkcol NOT NULL
HASHCOL ],
-| | | | | | table(sys.table3) [ table3.t3pkcol NOT NULL HASHCOL as
a1.t3pkcol, table3.t3cola1 as a1.t3cola1 ] COUNT
-| | | | | ) [ a1.t3pkcol NOT NULL HASHCOL = table1.t1cold111 ],
-| | | | | table(sys.table3) [ table3.t3pkcol NOT NULL HASHCOL as a2.t3pkcol ]
COUNT
-| | | | ) [ a2.t3pkcol NOT NULL HASHCOL = table1.t1cold112 ],
-| | | | table(sys.table3) [ table3.t3pkcol NOT NULL HASHCOL as a3.t3pkcol ]
COUNT
-| | | ) [ a3.t3pkcol NOT NULL HASHCOL = table1.t1cold113 ],
-| | | table(sys.table12) [ table12.t12cola1 ] COUNT
-| | ) [ table12.t12cola1 = table1.t1cola1, (((clob[char[table1.t1cold1]] as
table1.t1cold1) FILTER ilike (clob "%a%", clob "")) or
(((clob[char[table1.t1cola1]] as table1.t1cola1) FILTER ilike (clob "%a%", clob
"")) or (((clob[char[table1.t1colb1]] as table1.t1colb1) FILTER ilike (clob
"%a%", clob "")) or (((clob[char[table1.t1cola11]] as table1.t1cola11) FILTER
ilike (clob "%business%", clob "")) or ((table1.t1colc91 >=
timestamp(7)[char(19) "2016-03-21 05:00:00"]) or ((table1.t1cola101 = tinyint
"1") or (((clob[char[table1.t1cola12]] as table1.t1cola12) FILTER ilike (clob
"%Vijay%", clob "")) or (((clob[char[table2.t2cola1]] as table2.t2cola1) !
FILTER ilike (clob "%gmail%", clob ""), (clob[char[table2.t2cola1]] as
table2.t2cola1) ! FILTER ilike (clob "%yahoo%", clob "")) or
(((clob[char[table2.t2cola1]] as table2.t2cola1) FILTER ilike (clob
"%efequitygroup.com%", clob "")) or ((table4.t4cola1 = clob "Customer") or
((sys.isnull(table4.t4cola2) = boolean "false") or ((table2.t2cola
81 >= date[char(10) "2009-08-31"]) or (((table5.t5cola1 = clob "BAT") or
(((clob[char[table5.t5cola2]] as table5.t5cola2) FILTER ilike (clob
"%AUSTRALIA%", clob "")) or ((clob[char[table5.t5cola2]] as table5.t5cola2)
FILTER ilike (clob "%Monet%", clob ""), table5.t5cola3 = clob "Facebook",
table5.t5cola5 = clob "new", table5.t5cola81 > date[char(10) "2015-07-30"])))
or ((table10.t10cola1 != clob "Completed", table9.t9cola1 = clob "Tasks",
table9.t9cola91 >= timestamp(7)[char(19) "2012-01-01 04:32:27"],
table10.t10cola91 <= timestamp(7)[char(19) "2013-01-01 04:32:27"]) or
((table9.t9cola1 = clob "Events", timestamp(7)[char(19) "2012-01-01 04:32:27"]
<= table11.t11cola91 <= timestamp(7)[char(19) "2013-01-01 04:32:27"]) or
(table9.t9cola1 = clob "Calls", timestamp(7)[char(19) "2012-01-01 04:32:27"] <=
table10.t10cola91 <= timestamp(7)[char(19) "2013-01-01
04:32:27"]))))))))))))))), table1.t1cold111 in (bigint "15842000014793046",
bigint "15842000017701488", bigint "15842000000024019",
bigint "15842000000074007", bigint "15842000009358096", bigint
"15842000010487625", bigint "15842000006731919", bigint "15842000002590112",
bigint "15842000000019001", bigint "15842000014923682", bigint
"15842000027547249")) or (table12.t12cola1 in (clob[bigint
"15842000280111951"], clob[bigint "15842000280163015"])) ]
+| | | | | | | | | | | | | | | | | | | | | | | | | | | left outer join (
+| | | | | | | | | | | | | | | | | | | | | | | | | | | | table(sys.table1) [
table1.t1pkcol NOT NULL HASHCOL , table1.t1cola1, table1.t1cola11,
table1.t1cola12, table1.t1cola82, table1.t1cola91, table1.t1cola101,
table1.t1cola111, table1.t1cola112, table1.t1cola114, table1.t1colb1,
table1.t1colb111, table1.t1colb112, table1.t1colb113, table1.t1colb114,
table1.t1colc91, table1.t1cold1, table1.t1cold111, table1.t1cold112,
table1.t1cold113 ] COUNT ,
+| | | | | | | | | | | | | | | | | | | | | | | | | | | | table(sys.table4) [
table4.t4cola1, table4.t4cola2, table4.t4cola111, table4.t4colb111,
table4.t4colb112, table4.t4colb114, table4.t4colb115 ] COUNT
+| | | | | | | | | | | | | | | | | | | | | | | | | | | ) [ table1.t1cola111 =
table4.t4cola111 ],
+| | | | | | | | | | | | | | | | | | | | | | | | | | | table(sys.table2) [
table2.t2cola1, table2.t2cola10, table2.t2cola81, table2.t2cola82,
table2.t2cola112, table2.t2cola113, table2.t2colc111, table2.t2colc112,
table2.t2colc113, table2.t2colc114, table2.t2colc115 ] COUNT
+| | | | | | | | | | | | | | | | | | | | | | | | | | ) [ table1.t1cola112 =
table2.t2cola112 ],
+| | | | | | | | | | | | | | | | | | | | | | | | | | table(sys.table5) [
table5.t5cola1, table5.t5cola2, table5.t5cola3, table5.t5cola5,
table5.t5cola81, table5.t5cola113, table5.t5colb113 ] COUNT
+| | | | | | | | | | | | | | | | | | | | | | | | | ) [ table5.t5cola113 =
table2.t2cola113 ],
+| | | | | | | | | | | | | | | | | | | | | | | | | table(sys.table6) [
table6.t6pkcol NOT NULL HASHCOL as lookup1.t6pkcol ] COUNT
+| | | | | | | | | | | | | | | | | | | | | | | | ) [ table1.t1cola114 =
lookup1.t6pkcol NOT NULL HASHCOL ],
+| | | | | | | | | | | | | | | | | | | | | | | | table(sys.table2) [
table2.t2colb111 as lookup2.t2colb111 ] COUNT
+| | | | | | | | | | | | | | | | | | | | | | | ) [ table1.t1colb111 =
lookup2.t2colb111 ],
+| | | | | | | | | | | | | | | | | | | | | | | table(sys.table5) [
table5.t5colb112 as lookup3.t5colb112 ] COUNT
+| | | | | | | | | | | | | | | | | | | | | | ) [ table1.t1colb112 =
lookup3.t5colb112 ],
+| | | | | | | | | | | | | | | | | | | | | | table(sys.table7) [
table7.t7colb113 as lookup4.t7colb113 ] COUNT
+| | | | | | | | | | | | | | | | | | | | | ) [ table1.t1colb113 =
lookup4.t7colb113 ],
+| | | | | | | | | | | | | | | | | | | | | table(sys.table8) [ table8.t8colb114
as lookup5.t8colb114 ] COUNT
+| | | | | | | | | | | | | | | | | | | | ) [ table1.t1colb114 =
lookup5.t8colb114 ],
+| | | | | | | | | | | | | | | | | | | | table(sys.table6) [ table6.t6pkcol NOT
NULL HASHCOL as lookup11.t6pkcol ] COUNT
+| | | | | | | | | | | | | | | | | | | ) [ table4.t4colb111 = lookup11.t6pkcol
NOT NULL HASHCOL ],
+| | | | | | | | | | | | | | | | | | | table(sys.table2) [ table2.t2colb112 as
lookup21.t2colb112 ] COUNT
+| | | | | | | | | | | | | | | | | | ) [ table4.t4colb112 = lookup21.t2colb112
],
+| | | | | | | | | | | | | | | | | | table(sys.table5) [ table5.t5pkcol NOT
NULL HASHCOL as lookup31.t5pkcol ] COUNT
+| | | | | | | | | | | | | | | | | ) [ table5.t5colb113 = lookup31.t5pkcol NOT
NULL HASHCOL ],
+| | | | | | | | | | | | | | | | | table(sys.table7) [ table7.t7pkcol NOT NULL
HASHCOL as lookup41.t7pkcol ] COUNT
+| | | | | | | | | | | | | | | | ) [ table4.t4colb114 = lookup41.t7pkcol NOT
NULL HASHCOL ],
+| | | | | | | | | | | | | | | | table(sys.table8) [ table8.t8pkcol NOT NULL
HASHCOL as lookup51.t8pkcol ] COUNT
+| | | | | | | | | | | | | | | ) [ table4.t4colb115 = lookup51.t8pkcol NOT NULL
HASHCOL ],
+| | | | | | | | | | | | | | | table(sys.table6) [ table6.t6pkcol NOT NULL
HASHCOL as clookup1.t6pkcol ] COUNT
+| | | | | | | | | | | | | | ) [ table2.t2colc111 = clookup1.t6pkcol NOT NULL
HASHCOL ],
+| | | | | | | | | | | | | | table(sys.table2) [ table2.t2pkcol NOT NULL
HASHCOL as clookup2.t2pkcol ] COUNT
+| | | | | | | | | | | | | ) [ table2.t2colc112 = clookup2.t2pkcol NOT NULL
HASHCOL ],
+| | | | | | | | | | | | | table(sys.table5) [ table5.t5pkcol NOT NULL HASHCOL
as clookup3.t5pkcol ] COUNT
+| | | | | | | | | | | | ) [ table2.t2colc113 = clookup3.t5pkcol NOT NULL
HASHCOL ],
+| | | | | | | | | | | | table(sys.table7) [ table7.t7pkcol NOT NULL HASHCOL
as clookup4.t7pkcol ] COUNT
+| | | | | | | | | | | ) [ table2.t2colc114 = clookup4.t7pkcol NOT NULL HASHCOL
],
+| | | | | | | | | | | table(sys.table8) [ table8.t8pkcol NOT NULL HASHCOL as
clookup5.t8pkcol ] COUNT
+| | | | | | | | | | ) [ table2.t2colc115 = clookup5.t8pkcol NOT NULL HASHCOL
],
+| | | | | | | | | | table(sys.table9) [ table9.t9pkcol NOT NULL HASHCOL ,
table9.t9cola1, table9.t9cola91, table9.t9cola111 ] COUNT
+| | | | | | | | | ) [ table1.t1pkcol NOT NULL HASHCOL = table9.t9cola111 ],
+| | | | | | | | | table(sys.table10) [ table10.t10pkcol NOT NULL HASHCOL ,
table10.t10cola1, table10.t10cola91 ] COUNT
+| | | | | | | | ) [ table9.t9pkcol NOT NULL HASHCOL = table10.t10pkcol NOT
NULL HASHCOL ],
+| | | | | | | | table(sys.table11) [ table11.t11pkcol NOT NULL HASHCOL ,
table11.t11cola91 ] COUNT
+| | | | | | | ) [ table9.t9pkcol NOT NULL HASHCOL = table11.t11pkcol NOT NULL
HASHCOL ],
+| | | | | | | table(sys.table3) [ table3.t3pkcol NOT NULL HASHCOL as
a1.t3pkcol, table3.t3cola1 as a1.t3cola1 ] COUNT
+| | | | | | ) [ a1.t3pkcol NOT NULL HASHCOL = table1.t1cold111 ],
+| | | | | | table(sys.table3) [ table3.t3pkcol NOT NULL HASHCOL as a2.t3pkcol
] COUNT
+| | | | | ) [ a2.t3pkcol NOT NULL HASHCOL = table1.t1cold112 ],
+| | | | | table(sys.table3) [ table3.t3pkcol NOT NULL HASHCOL as a3.t3pkcol ]
COUNT
+| | | | ) [ a3.t3pkcol NOT NULL HASHCOL = table1.t1cold113 ],
+| | | | table(sys.table12) [ table12.t12cola1 ] COUNT
+| | | ) [ table12.t12cola1 = table1.t1cola1 ]
+| | ) [ (((clob[char[table1.t1cold1]] as table1.t1cold1) FILTER ilike (clob
"%a%", clob "")) or (((clob[char[table1.t1cola1]] as table1.t1cola1) FILTER
ilike (clob "%a%", clob "")) or (((clob[char[table1.t1colb1]] as
table1.t1colb1) FILTER ilike (clob "%a%", clob "")) or
(((clob[char[table1.t1cola11]] as table1.t1cola11) FILTER ilike (clob
"%business%", clob "")) or ((table1.t1colc91 >= timestamp(7)[char(19)
"2016-03-21 05:00:00"]) or ((table1.t1cola101 = tinyint "1") or
(((clob[char[table1.t1cola12]] as table1.t1cola12) FILTER ilike (clob
"%Vijay%", clob "")) or (((clob[char[table2.t2cola1]] as table2.t2cola1) !
FILTER ilike (clob "%gmail%", clob ""), (clob[char[table2.t2cola1]] as
table2.t2cola1) ! FILTER ilike (clob "%yahoo%", clob "")) or
(((clob[char[table2.t2cola1]] as table2.t2cola1) FILTER ilike (clob
"%efequitygroup.com%", clob "")) or ((table4.t4cola1 = clob "Customer") or
((sys.isnull(table4.t4cola2) = boolean "false") or ((table2.t2cola81 >=
date[char(10) "2009-08-31"])
or (((table5.t5cola1 = clob "BAT") or (((clob[char[table5.t5cola2]] as
table5.t5cola2) FILTER ilike (clob "%AUSTRALIA%", clob "")) or
((clob[char[table5.t5cola2]] as table5.t5cola2) FILTER ilike (clob "%Monet%",
clob ""), table5.t5cola3 = clob "Facebook", table5.t5cola5 = clob "new",
table5.t5cola81 > date[char(10) "2015-07-30"]))) or ((table10.t10cola1 != clob
"Completed", table9.t9cola1 = clob "Tasks", table9.t9cola91 >=
timestamp(7)[char(19) "2012-01-01 04:32:27"], table10.t10cola91 <=
timestamp(7)[char(19) "2013-01-01 04:32:27"]) or ((table9.t9cola1 = clob
"Events", table11.t11cola91 >= timestamp(7)[char(19) "2012-01-01 04:32:27"],
table11.t11cola91 <= timestamp(7)[char(19) "2013-01-01 04:32:27"]) or
(table9.t9cola1 = clob "Calls", table10.t10cola91 >= timestamp(7)[char(19)
"2012-01-01 04:32:27"], table10.t10cola91 <= timestamp(7)[char(19) "2013-01-01
04:32:27"]))))))))))))))), table1.t1cold111 in (bigint "15842000014793046",
bigint "15842000017701488", bigint "15842000000024019
", bigint "15842000000074007", bigint "15842000009358096", bigint
"15842000010487625", bigint "15842000006731919", bigint "15842000002590112",
bigint "15842000000019001", bigint "15842000014923682", bigint
"15842000027547249")) or (table12.t12cola1 in (clob[bigint
"15842000280111951"], clob[bigint "15842000280163015"])) ]
| ) [ table1.t1pkcol NOT NULL HASHCOL , table1.t1cola82, table2.t2cola10,
table1.t1cola1, table1.t1cola91, a1.t3cola1 ] [ table2.t2cola82 ]
) [ wrd "10", wrd "0" ]
#ROLLBACK;
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
