Changeset: 43ad3ab454ad for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=43ad3ab454ad
Added Files:
sql/test/bugs/Tests/rtrim_bug.test
Modified Files:
sql/backends/monet5/rel_bin.c
sql/server/rel_optimizer.c
sql/test/miscellaneous/Tests/simple_plans.stable.out
sql/test/miscellaneous/Tests/simple_selects.test
Branch: default
Log Message:
Merged with Oct2020 and converted tests
diffs (294 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
@@ -2519,8 +2519,6 @@ rel2bin_antijoin(backend *be, sql_rel *r
return stmt_list(be, l);
}
-#define complex_join_expression(e) (exp_has_func(e) && (e)->flag !=
cmp_filter) || (e)->flag == cmp_or || ((e)->f && (e)->anti)
-
static stmt *
rel2bin_semijoin(backend *be, sql_rel *rel, list *refs)
{
@@ -2565,7 +2563,7 @@ rel2bin_semijoin(backend *be, sql_rel *r
int idx = 0, equality_only = 1;
jexps = get_equi_joins_first(sql, jexps,
&equality_only);
- if (!equality_only || list_length(jexps) > 1 ||
complex_join_expression((sql_exp*)jexps->h->data))
+ if (!equality_only || list_length(jexps) > 1 ||
exp_has_func((sql_exp*)jexps->h->data))
left = subrel_project(be, left, refs, rel->l);
right = subrel_project(be, right, refs, rel->r);
@@ -2575,7 +2573,7 @@ rel2bin_semijoin(backend *be, sql_rel *r
stmt *s = NULL;
/* only handle simple joins here */
- if (complex_join_expression(e)) {
+ if ((exp_has_func(e) && e->flag != cmp_filter)
|| e->flag == cmp_or || (e->f && e->anti)) {
if (!join && !list_length(lje)) {
stmt *l = bin_first_column(be,
left);
stmt *r = bin_first_column(be,
right);
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
@@ -1350,16 +1350,16 @@ can_push_func(sql_exp *e, sql_rel *rel,
{
switch(e->type) {
case e_cmp: {
+ int mustl = 0, mustr = 0, mustf = 0;
sql_exp *l = e->l, *r = e->r, *f = e->f;
- int res = 1, lmust = 0;
-
- if (e->flag == cmp_or || e->flag == cmp_in || e->flag ==
cmp_notin || e->flag == cmp_filter)
+
+ if (is_project(rel->op) || e->flag == cmp_or || e->flag ==
cmp_in || e->flag == cmp_notin || e->flag == cmp_filter)
return 0;
- res = can_push_func(l, rel, &lmust) && can_push_func(r, rel,
&lmust) && (!f || can_push_func(f, rel, &lmust));
- if (res && !lmust)
- return 1;
- (*must) |= lmust;
- return res;
+ return ((l->type == e_column || can_push_func(l, rel, &mustl))
&& (*must = mustl)) ||
+ (!f && (r->type == e_column || can_push_func(r,
rel, &mustr)) && (*must = mustr)) ||
+ (f &&
+ (r->type == e_column || can_push_func(r, rel,
&mustr)) &&
+ (f->type == e_column || can_push_func(f, rel, &mustf))
&& (*must = (mustr || mustf)));
}
case e_convert:
return can_push_func(e->l, rel, must);
@@ -1378,8 +1378,7 @@ can_push_func(sql_exp *e, sql_rel *rel,
return res;
}
case e_column:
- /* aliases cannot be bound on the same level, ie same
projection */
- if ((exp_name(e) && !has_label(e)) || (rel &&
!rel_find_exp(rel, e)))
+ if (rel && !rel_find_exp(rel, e))
return 0;
(*must) = 1;
/* fall through */
diff --git a/sql/test/bugs/Tests/rtrim_bug.sql
b/sql/test/bugs/Tests/rtrim_bug.sql
--- a/sql/test/bugs/Tests/rtrim_bug.sql
+++ b/sql/test/bugs/Tests/rtrim_bug.sql
@@ -12,6 +12,24 @@ SELECT length("m") as data_length, "m" a
CREATE VIEW v2 as select "m" from t1 where rtrim(m) in (select rtrim(m) from
sys.t1);
SELECT * FROM v2;
+
+PLAN SELECT length("m") as data_length, "m" as data_value FROM "sys"."v2"
WHERE "m" IS NOT NULL AND length("m") > 1;
+
+set optimizer = 'sequential_pipe';
+create procedure profiler.starttrace() external name profiler."starttrace";
+create procedure profiler.stoptrace() external name profiler.stoptrace;
+
+call profiler."starttrace"();
+SELECT length("m") as data_length, "m" as data_value FROM "sys"."v2" WHERE "m"
IS NOT NULL AND length("m") > 1;
+call profiler.stoptrace();
+
+select count(*) from sys.tracelog() where stmt like '% algebra.crossproduct%';
-- don't do crossjoin
+select count(*) from sys.tracelog() where stmt like '% algebra.join%'; -- do
inner join
+
+drop procedure profiler.starttrace();
+drop procedure profiler.stoptrace();
+set optimizer = 'default_pipe';
+
SELECT length("m") as data_length, "m" as data_value FROM "sys"."v2" WHERE "m"
IS NOT NULL AND length("m") > 1;
-- 2 rows returned !! should be 0 rows as with v1 !!
-- This query produces wrong results!!
diff --git a/sql/test/bugs/Tests/rtrim_bug.stable.out
b/sql/test/bugs/Tests/rtrim_bug.stable.out
--- a/sql/test/bugs/Tests/rtrim_bug.stable.out
+++ b/sql/test/bugs/Tests/rtrim_bug.stable.out
@@ -43,6 +43,48 @@ stdout of test 'rtrim_bug` in directory
% 1 # length
[ "0" ]
[ "2" ]
+#PLAN SELECT length("m") as data_length, "m" as data_value FROM "sys"."v2"
WHERE "m" IS NOT NULL AND length("m") > 1;
+% .plan # table_name
+% rel # name
+% clob # type
+% 112 # length
+project (
+| semijoin (
+| | project (
+| | | select (
+| | | | table("sys"."t1") [ "t1"."m" NOT NULL ] COUNT
+| | | ) [ "sys"."length"("t1"."m" NOT NULL) NOT NULL > int "1" ]
+| | ) [ "t1"."m" NOT NULL, "sys"."rtrim"("t1"."m" NOT NULL) NOT NULL as
"%5"."%5" ],
+| | project (
+| | | project (
+| | | | table("sys"."t1") [ "t1"."m" NOT NULL ] COUNT
+| | | ) [ "sys"."rtrim"("t1"."m" NOT NULL) NOT NULL as "%1"."%1" ]
+| | ) [ "%1"."%1" NOT NULL as "%4"."%4" ]
+| ) [ "%5"."%5" NOT NULL any = "%4"."%4" NOT NULL ]
+) [ "sys"."length"("t1"."m" NOT NULL as "v2"."m") NOT NULL as "data_length",
"t1"."m" NOT NULL as "data_value" ]
+#set optimizer = 'sequential_pipe';
+#create procedure profiler.starttrace() external name profiler."starttrace";
+#create procedure profiler.stoptrace() external name profiler.stoptrace;
+#SELECT length("m") as data_length, "m" as data_value FROM "sys"."v2" WHERE
"m" IS NOT NULL AND length("m") > 1;
+% sys., sys. # table_name
+% data_length, data_value # name
+% int, varchar # type
+% 1, 0 # length
+#select count(*) from sys.tracelog() where stmt like '%
algebra.crossproduct%'; -- don't do crossjoin
+% .%2 # table_name
+% %2 # name
+% bigint # type
+% 1 # length
+[ 0 ]
+#select count(*) from sys.tracelog() where stmt like '% algebra.join%'; -- do
inner join
+% .%2 # table_name
+% %2 # name
+% bigint # type
+% 1 # length
+[ 1 ]
+#drop procedure profiler.starttrace();
+#drop procedure profiler.stoptrace();
+#set optimizer = 'default_pipe';
#SELECT length("m") as data_length, "m" as data_value FROM "sys"."v2" WHERE
"m" IS NOT NULL AND length("m") > 1;
% sys., sys. # table_name
% data_length, data_value # name
diff --git a/sql/test/bugs/Tests/rtrim_bug.test
b/sql/test/bugs/Tests/rtrim_bug.test
new file mode 100644
--- /dev/null
+++ b/sql/test/bugs/Tests/rtrim_bug.test
@@ -0,0 +1,112 @@
+statement ok
+CREATE TABLE t1 (m varchar (1) NOT NULL)
+
+statement ok
+INSERT into t1 values ('0')
+
+statement ok
+INSERT into t1 values ('2')
+
+query T rowsort
+SELECT * FROM sys.t1
+----
+0
+2
+
+query IT rowsort
+SELECT length("m") as data_length, "m" as data_value FROM "sys"."t1" WHERE "m"
IS NOT NULL AND length("m") > 1
+----
+
+statement ok
+CREATE VIEW v1 as select "m" from t1 where m in (select m from sys.t1)
+
+query T rowsort
+SELECT * FROM v1
+----
+0
+2
+
+query IT rowsort
+SELECT length("m") as data_length, "m" as data_value FROM "sys"."v1" WHERE "m"
IS NOT NULL AND length("m") > 1
+----
+
+statement ok
+CREATE VIEW v2 as select "m" from t1 where rtrim(m) in (select rtrim(m) from
sys.t1)
+
+query T rowsort
+SELECT * FROM v2
+----
+0
+2
+
+query T nosort
+PLAN SELECT length("m") as data_length, "m" as data_value FROM "sys"."v2"
WHERE "m" IS NOT NULL AND length("m") > 1
+----
+project (
+| semijoin (
+| | project (
+| | | select (
+| | | | table("sys"."t1") [ "t1"."m" NOT NULL ] COUNT
+| | | ) [ "sys"."length"("t1"."m" NOT NULL) NOT NULL > int "1" ]
+| | ) [ "t1"."m" NOT NULL, "sys"."rtrim"("t1"."m" NOT NULL) NOT NULL as
"%5"."%5" ],
+| | project (
+| | | project (
+| | | | table("sys"."t1") [ "t1"."m" NOT NULL ] COUNT
+| | | ) [ "sys"."rtrim"("t1"."m" NOT NULL) NOT NULL as "%1"."%1" ]
+| | ) [ "%1"."%1" NOT NULL as "%4"."%4" ]
+| ) [ "%5"."%5" NOT NULL any = "%4"."%4" NOT NULL ]
+) [ "sys"."length"("t1"."m" NOT NULL as "v2"."m") NOT NULL as "data_length",
"t1"."m" NOT NULL as "data_value" ]
+
+statement ok
+set optimizer = 'sequential_pipe'
+
+statement ok
+create procedure profiler.starttrace() external name profiler."starttrace"
+
+statement ok
+create procedure profiler.stoptrace() external name profiler.stoptrace
+
+statement ok
+call profiler."starttrace"()
+
+query IT rowsort
+SELECT length("m") as data_length, "m" as data_value FROM "sys"."v2" WHERE "m"
IS NOT NULL AND length("m") > 1
+----
+
+query IT rowsort
+call profiler.stoptrace()
+----
+
+query I rowsort
+select count(*) from sys.tracelog() where stmt like '% algebra.crossproduct%'
+----
+0
+
+query I rowsort
+select count(*) from sys.tracelog() where stmt like '% algebra.join%'
+----
+1
+
+statement ok
+drop procedure profiler.starttrace()
+
+statement ok
+drop procedure profiler.stoptrace()
+
+statement ok
+set optimizer = 'default_pipe'
+
+query IT rowsort
+SELECT length("m") as data_length, "m" as data_value FROM "sys"."v2" WHERE "m"
IS NOT NULL AND length("m") > 1
+----
+
+statement ok
+DROP VIEW v1
+
+statement ok
+DROP VIEW v2
+
+statement ok
+DROP TABLE t1
+
+
diff --git a/sql/test/miscellaneous/Tests/simple_selects.test
b/sql/test/miscellaneous/Tests/simple_selects.test
--- a/sql/test/miscellaneous/Tests/simple_selects.test
+++ b/sql/test/miscellaneous/Tests/simple_selects.test
@@ -676,6 +676,25 @@ statement ok
start transaction
statement ok
+create table t1("kk" int)
+
+statement ok
+create table t2("kk" int)
+
+statement ok
+create table t3("tkey" int)
+
+statement error
+SELECT 1 FROM (((t1 t10 INNER JOIN t2 t20 ON t10."kk" = t20."kk") INNER JOIN
t2 t20 ON t10."kk" = t20."kk")
+INNER JOIN t3 t31 ON t20."kk" = t31."tkey"); --error, multiple references to
relation t20
+
+statement ok
+rollback
+
+statement ok
+start transaction
+
+statement ok
create or replace function ups() returns int begin if null > 1 then return 1;
else return 2; end if; end
query I rowsort
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
