Changeset: 575a476e7027 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=575a476e7027
Modified Files:
sql/backends/monet5/rel_bin.c
sql/include/sql_catalog.h
sql/rel.txt
sql/server/rel_dump.c
sql/server/rel_exp.c
sql/server/rel_optimizer.c
sql/test/subquery/Tests/subquery6.stable.err
sql/test/subquery/Tests/subquery6.stable.out
Branch: default
Log Message:
Merged with Jun2020
diffs (truncated from 304 to 300 lines):
diff --git a/sql/backends/monet5/rel_bin.c b/sql/backends/monet5/rel_bin.c
--- a/sql/backends/monet5/rel_bin.c
+++ b/sql/backends/monet5/rel_bin.c
@@ -2192,7 +2192,7 @@ split_join_exps(sql_rel *rel, list *join
int left_reference = 0, right_reference = 0;
/* we can handle thetajoins, rangejoins and filter
joins (like) */
- /* ToDo how about mark_exists/not_exists and atom
expressions? */
+ /* ToDo how about atom expressions? */
if (e->type == e_cmp) {
int flag = e->flag & ~CMP_BETWEEN;
/* check if its a select or join expression, ie
use only expressions of one relation left and of the other right (than join) */
@@ -2200,17 +2200,32 @@ split_join_exps(sql_rel *rel, list *join
/* join or select ? */
sql_exp *l = e->l, *r = e->r, *f = e->f;
- if (l->card != CARD_ATOM) {
- left_reference +=
rel_find_exp(rel->l, l) != NULL;
- right_reference +=
rel_find_exp(rel->r, l) != NULL;
- }
- if (r->card != CARD_ATOM) {
- left_reference +=
rel_find_exp(rel->l, r) != NULL;
- right_reference +=
rel_find_exp(rel->r, r) != NULL;
- }
- if (f && f->card != CARD_ATOM) {
- left_reference +=
rel_find_exp(rel->l, f) != NULL;
- right_reference +=
rel_find_exp(rel->r, f) != NULL;
+ if (f) {
+ int ll = rel_find_exp(rel->l,
l) != NULL;
+ int rl = rel_find_exp(rel->r,
l) != NULL;
+ int lr = rel_find_exp(rel->l,
r) != NULL;
+ int rr = rel_find_exp(rel->r,
r) != NULL;
+ int lf = rel_find_exp(rel->l,
f) != NULL;
+ int rf = rel_find_exp(rel->r,
f) != NULL;
+ int nrcr1 = 0, nrcr2 = 0, nrcl1
= 0, nrcl2 = 0;
+
+ if ((ll && !rl &&
+ ((rr && !lr) || (nrcr1 =
r->card == CARD_ATOM)) &&
+ ((rf && !lf) || (nrcr2 =
f->card == CARD_ATOM)) && (nrcr1+nrcr2) <= 1) ||
+ (rl && !ll &&
+ ((lr && !rr) || (nrcl1 =
r->card == CARD_ATOM)) &&
+ ((lf && !rf) || (nrcl2 =
f->card == CARD_ATOM)) && (nrcl1+nrcl2) <= 1)) {
+ left_reference =
right_reference = 1;
+ }
+ } else {
+ if (l->card != CARD_ATOM) {
+ left_reference +=
rel_find_exp(rel->l, l) != NULL;
+ right_reference +=
rel_find_exp(rel->r, l) != NULL;
+ }
+ if (r->card != CARD_ATOM) {
+ left_reference +=
rel_find_exp(rel->l, r) != NULL;
+ right_reference +=
rel_find_exp(rel->r, r) != NULL;
+ }
}
} else if (flag == cmp_filter) {
list *l = e->l, *r = e->r;
@@ -2242,7 +2257,7 @@ split_join_exps(sql_rel *rel, list *join
}
}
-#define is_priority_exp(e) ((e)->type == e_cmp && (e)->flag == cmp_equal)
+#define is_equi_exp(e) ((e)->flag == cmp_equal || (e)->flag == mark_in ||
(e)->flag == mark_notin)
static list *
get_equi_joins_first(mvc *sql, list *exps, int *equality_only)
@@ -2251,17 +2266,18 @@ get_equi_joins_first(mvc *sql, list *exp
for( node *n = exps->h; n; n = n->next ) {
sql_exp *e = n->data;
- if (is_priority_exp(e)) {
+
+ assert(e->type == e_cmp && e->flag != cmp_in && e->flag !=
cmp_notin && e->flag != cmp_or);
+ if (is_equi_exp(e))
list_append(new_exps, e);
- *equality_only &= (e->flag == cmp_equal);
- }
+ else
+ *equality_only = 0;
}
for( node *n = exps->h; n; n = n->next ) {
sql_exp *e = n->data;
- if (!is_priority_exp(e)) {
+
+ if (!is_equi_exp(e))
list_append(new_exps, e);
- *equality_only &= (e->flag == mark_in || e->flag ==
mark_notin);
- }
}
return new_exps;
}
@@ -2482,9 +2498,7 @@ rel2bin_join(backend *be, sql_rel *rel,
static int
exp_is_mark(sql_exp *e)
{
- if (e->type == e_cmp &&
- (e->flag == mark_in || e->flag == mark_notin ||
- e->flag == mark_exists || e->flag == mark_notexists))
+ if (e->type == e_cmp && (e->flag == mark_in || e->flag == mark_notin))
return 1;
return 0;
}
diff --git a/sql/include/sql_catalog.h b/sql/include/sql_catalog.h
--- a/sql/include/sql_catalog.h
+++ b/sql/include/sql_catalog.h
@@ -164,15 +164,13 @@ typedef enum comp_type {
mark_in = 10, /* mark joins */
mark_notin = 11,
- mark_exists = 12,
- mark_notexists = 13,
/* The followin cmp_* are only used within stmt (not sql_exp) */
- cmp_all = 14, /* special case for crossproducts */
- cmp_project = 15, /* special case for projection joins */
- cmp_joined = 16, /* special case already joined */
- cmp_left = 17, /* special case equi join, keep left
order */
- cmp_left_project = 18 /* last step of outer join */
+ cmp_all = 12, /* special case for crossproducts */
+ cmp_project = 13, /* special case for projection joins */
+ cmp_joined = 14, /* special case already joined */
+ cmp_left = 15, /* special case equi join, keep left
order */
+ cmp_left_project = 16 /* last step of outer join */
} comp_type;
/* for ranges we keep the requirment for symmetric */
diff --git a/sql/rel.txt b/sql/rel.txt
--- a/sql/rel.txt
+++ b/sql/rel.txt
@@ -128,15 +128,13 @@ e_cmp
mark_in = 10, /* mark joins */
mark_notin = 11,
- mark_exists = 12,
- mark_notexists = 13,
/* The followin cmp_* are only used within stmt (not
sql_exp) */
- cmp_all = 14, /* special case for
crossproducts */
- cmp_project = 15, /* special case for
projection joins */
- cmp_joined = 16, /* special case already
joined */
- cmp_left = 17, /* special case equi
join, keep left order */
- cmp_left_project = 18 /* last step of outer join */
+ cmp_all = 12, /* special case for
crossproducts */
+ cmp_project = 13, /* special case for
projection joins */
+ cmp_joined = 14, /* special case already
joined */
+ cmp_left = 15, /* special case equi
join, keep left order */
+ cmp_left_project = 16 /* last step of outer join */
)
e_func
diff --git a/sql/server/rel_dump.c b/sql/server/rel_dump.c
--- a/sql/server/rel_dump.c
+++ b/sql/server/rel_dump.c
@@ -65,8 +65,6 @@ cmp_print(mvc *sql, stream *fout, int cm
case mark_in: r = "any ="; break;
case mark_notin: r = "all <>"; break;
- case mark_exists: r = "exists"; break;
- case mark_notexists: r = "!exists"; break;
case cmp_all:
case cmp_project:
@@ -1235,15 +1233,6 @@ exp_read(mvc *sql, sql_rel *lrel, sql_re
f = mark_notin;
}
break;
- case 'e':
- if (strncmp(r+*pos, "exists", strlen("exists")) == 0) {
- (*pos)+= (int) strlen("exists");
- f = mark_exists;
- } else if (strncmp(r+*pos, "!exists", strlen("!exists")) == 0)
{
- (*pos)+= (int) strlen("!exists");
- f = mark_notexists;
- }
- break;
case 'n':
if (strncmp(r+*pos, "notin", strlen("notin")) == 0) {
(*pos)+= (int) strlen("notin");
diff --git a/sql/server/rel_exp.c b/sql/server/rel_exp.c
--- a/sql/server/rel_exp.c
+++ b/sql/server/rel_exp.c
@@ -92,10 +92,6 @@ negate_compare( comp_type t )
return mark_notin;
case mark_notin:
return mark_in;
- case mark_exists:
- return mark_notexists;
- case mark_notexists:
- return mark_exists;
default:
return t;
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
@@ -453,8 +453,6 @@ exp_count(int *cnt, sql_exp *e)
return 3;
case mark_in:
case mark_notin:
- case mark_exists:
- case mark_notexists:
*cnt += 0;
return 0;
default:
diff --git a/sql/test/subquery/Tests/subquery6.stable.err
b/sql/test/subquery/Tests/subquery6.stable.err
--- a/sql/test/subquery/Tests/subquery6.stable.err
+++ b/sql/test/subquery/Tests/subquery6.stable.err
@@ -31,6 +31,12 @@ MAPI = (monetdb) /var/tmp/mtest-25203/.
QUERY = SELECT (SELECT col1) IN ('not a number') FROM another_t;
ERROR = !conversion of string 'not a number' to type int failed.
CODE = 22018
+MAPI = (monetdb) /var/tmp/mtest-289487/.s.monetdb.36720
+QUERY = SELECT (SELECT t2.col2 FROM another_t t2 WHERE t2.col1 BETWEEN t1.col1
AND t2.col2) FROM another_t t1;
+ERROR = !GDK reported error: mergejoin: more than one match
+MAPI = (monetdb) /var/tmp/mtest-289487/.s.monetdb.36720
+QUERY = SELECT (SELECT t2.col2 FROM another_t t2 WHERE t2.col1 BETWEEN t2.col1
AND t1.col2) FROM another_t t1;
+ERROR = !GDK reported error: mergejoin: more than one match
# 11:45:43 >
# 11:45:43 > "Done."
diff --git a/sql/test/subquery/Tests/subquery6.stable.out
b/sql/test/subquery/Tests/subquery6.stable.out
--- a/sql/test/subquery/Tests/subquery6.stable.out
+++ b/sql/test/subquery/Tests/subquery6.stable.out
@@ -28,8 +28,8 @@ stdout of test 'subquery6` in directory
# WHERE ctr1.ctr_state = ctr2.ctr_state)
# AND col1 = ColID
# AND ctr1.ctr_customer_sk = TotalSales;
-% .%45 # table_name
-% %45 # name
+% .%44 # table_name
+% %44 # name
% tinyint # type
% 1 # length
#SELECT (SELECT (SELECT SUM(col1)) IN (MAX(col2))) FROM another_t;
@@ -45,8 +45,8 @@ stdout of test 'subquery6` in directory
% 5 # length
[ false ]
#SELECT CASE WHEN ColID IS NULL THEN CAST(Product_Category AS INT) ELSE
TotalSales END FROM tbl_ProductSales;
-% sys.%1 # table_name
-% %1 # name
+% sys.%2 # table_name
+% %2 # name
% int # type
% 3 # length
[ 200 ]
@@ -62,9 +62,9 @@ stdout of test 'subquery6` in directory
[ 2 ]
[ 3 ]
[ 4 ]
-#SELECT SUM((SELECT col1)) FROM another_t;
-% sys.%2 # table_name
-% %2 # name
+#SELECT CAST(SUM((SELECT col1)) AS BIGINT) FROM another_t;
+% sys.%3 # table_name
+% %3 # name
% bigint # type
% 4 # length
[ 1234 ]
@@ -75,8 +75,8 @@ stdout of test 'subquery6` in directory
% 4 # length
[ 3702 ]
#SELECT CAST(SUM((SELECT CAST(EXISTS(SELECT col1) AS INT))) AS BIGINT) FROM
another_t;
-% sys.%3 # table_name
-% %3 # name
+% sys.%4 # table_name
+% %4 # name
% bigint # type
% 1 # length
[ 4 ]
@@ -87,8 +87,8 @@ stdout of test 'subquery6` in directory
% 4 # length
[ 3702 ]
#SELECT CAST((SELECT SUM((SELECT col1))) AS BIGINT) FROM another_t;
-% sys.%2 # table_name
-% %2 # name
+% sys.%3 # table_name
+% %3 # name
% bigint # type
% 4 # length
[ 1234 ]
@@ -141,6 +141,24 @@ stdout of test 'subquery6` in directory
[ 4 ]
[ 4 ]
[ 4 ]
+#SELECT (SELECT 1 FROM another_t t1 WHERE t2.product_category LIKE
CAST(t1.col1 AS VARCHAR(32))) FROM tbl_ProductSales t2;
+% .%2 # table_name
+% %2 # name
+% tinyint # type
+% 1 # length
+[ NULL ]
+[ NULL ]
+[ NULL ]
+[ NULL ]
+#SELECT (SELECT t2.col2 FROM another_t t2 WHERE t1.col1 BETWEEN t2.col1 AND
t2.col2) FROM another_t t1;
+% .%1 # table_name
+% %1 # name
+% int # type
+% 4 # length
+[ 2 ]
+[ 22 ]
+[ 222 ]
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
