Changeset: 638bb98f4199 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=638bb98f4199
Modified Files:
sql/backends/monet5/rel_bin.c
sql/test/SQLancer/Tests/sqlancer01.sql
sql/test/SQLancer/Tests/sqlancer01.stable.out
sql/test/miscellaneous/Tests/simple_plans.sql
sql/test/miscellaneous/Tests/simple_plans.stable.out
Branch: Jun2020
Log Message:
Fix for SQLancer crash, ie make the split between join and select expressions
more evident. I added some explain tests to test crossproducts are not
generated on "regular" queries
diffs (truncated from 481 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
@@ -2210,59 +2210,55 @@ rel2bin_join(backend *be, sql_rel *rel,
if (!list_empty(rel->exps)) {
for( en = rel->exps->h, i=0; en; en = en->next, i++) {
sql_exp *e = en->data;
+ int left_reference = 0, right_reference = 0;
/* we can handle thetajoins, rangejoins and
filter joins (like) */
/* ToDo how about in/notin, mark_in/notin,
mark_exists/not_exists and 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) */
- if (flag < cmp_filter && !e->f) { /*
theta join */
+ if (flag < cmp_filter) { /* theta and
range joins */
/* join or select ? */
- if ((rel_find_exp(rel->l, e->l)
&& !rel_find_exp(rel->r, e->l) &&
- rel_find_exp(rel->r, e->r)
&& !rel_find_exp(rel->l, e->r)) ||
- (rel_find_exp(rel->r, e->l)
&& !rel_find_exp(rel->l, e->l) &&
- rel_find_exp(rel->l, e->r)
&& !rel_find_exp(rel->r, e->r))) {
- append(jexps, e);
- continue;
+ 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;
}
- } else if (flag < cmp_filter && e->f) {
/* range */
- int nrcr1 = 0, nrcr2 = 0, nrcl1
= 0, nrcl2 = 0;
- if ((rel_find_exp(rel->l, e->l)
&& !rel_find_exp(rel->r, e->l) &&
- ((rel_find_exp(rel->r, e->r)
&& !rel_find_exp(rel->l, e->r)) || (nrcr1 = ((sql_exp*)e->r)->card ==
CARD_ATOM)) &&
- ((rel_find_exp(rel->r, e->f)
&& !rel_find_exp(rel->l, e->f)) || (nrcr2 = ((sql_exp*)e->f)->card ==
CARD_ATOM)) && (nrcr1+nrcr2) <= 1) ||
- (rel_find_exp(rel->r, e->l)
&& !rel_find_exp(rel->l, e->l) &&
- ((rel_find_exp(rel->l, e->r)
&& !rel_find_exp(rel->r, e->r)) || (nrcl1 = ((sql_exp*)e->r)->card ==
CARD_ATOM)) &&
- ((rel_find_exp(rel->l, e->f)
&& !rel_find_exp(rel->r, e->f)) || (nrcl2 = ((sql_exp*)e->f)->card ==
CARD_ATOM)) && (nrcl1+nrcl2) <= 1)) {
- append(jexps, e);
- continue;
+ 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;
}
} else if (flag == cmp_filter &&
!e->anti) {
- int nrcl = 0, nrcr = 0;
- bool fll = true, flr = true,
frl = true, frr = true;
list *l = e->l, *r = e->r;
for (node *n = l->h ; n ; n =
n->next) {
sql_exp *ee = n->data;
- fll &=
rel_find_exp(rel->l, ee) != NULL;
- frl &=
rel_find_exp(rel->r, ee) != NULL;
- nrcl += ee->card ==
CARD_ATOM;
+ if (ee->card !=
CARD_ATOM) {
+ left_reference
+= rel_find_exp(rel->l, ee) != NULL;
+ right_reference
+= rel_find_exp(rel->r, ee) != NULL;
+ }
}
for (node *n = r->h ; n ; n =
n->next) {
sql_exp *ee = n->data;
- flr &=
rel_find_exp(rel->l, ee) != NULL;
- frr &=
rel_find_exp(rel->r, ee) != NULL;
- nrcr += ee->card ==
CARD_ATOM;
- }
- if (!((fll && flr) || (frl &&
frr)) &&
- nrcl < list_length(l) &&
nrcr < list_length(r)) {
- append(jexps, e);
- continue;
+ if (ee->card !=
CARD_ATOM) {
+ left_reference
+= rel_find_exp(rel->l, ee) != NULL;
+ right_reference
+= rel_find_exp(rel->r, ee) != NULL;
+ }
}
}
}
- append(sexps, e);
+ if (left_reference && right_reference) {
+ append(jexps, e);
+ } else {
+ append(sexps, e);
+ }
}
}
diff --git a/sql/test/SQLancer/Tests/sqlancer01.sql
b/sql/test/SQLancer/Tests/sqlancer01.sql
--- a/sql/test/SQLancer/Tests/sqlancer01.sql
+++ b/sql/test/SQLancer/Tests/sqlancer01.sql
@@ -298,7 +298,8 @@ START TRANSACTION; -- Bug 6906
CREATE TABLE t0("c0" DOUBLE PRECISION, "c1" VARCHAR(496));
create view v0(c0, c1) as (select all t0.c0, r'epfNW⟚榢tptPbC{5{ZW}6,R' from
t0) with check option;
select 1 from v0 full outer join t0 on (cast(('a') in ('a') as string) ilike
v0.c0);
-select sum(all + (cast(t0.c0 as int))) from v0 full outer join t0 on
((cast((cast(v0.c1 as boolean)) not in (true, ((t0.c0)=(t0.c0)),
cast(1745166981 as boolean)) as string))ilike(v0.c0));
+select 1 from v0 full outer join t0 on cast((v0.c1) in (1) as string) like
v0.c0;
+select cast(sum(all + (cast(t0.c0 as int))) as bigint) from v0 full outer join
t0 on ((cast((cast(v0.c1 as boolean)) not in (true, ((t0.c0)=(t0.c0)),
cast(1745166981 as boolean)) as string))ilike(v0.c0));
ROLLBACK;
DROP TABLE tbl_ProductSales;
diff --git a/sql/test/SQLancer/Tests/sqlancer01.stable.out
b/sql/test/SQLancer/Tests/sqlancer01.stable.out
--- a/sql/test/SQLancer/Tests/sqlancer01.stable.out
+++ b/sql/test/SQLancer/Tests/sqlancer01.stable.out
@@ -588,6 +588,17 @@ stdout of test 'sqlancer01` in directory
% %11 # name
% tinyint # type
% 1 # length
+#select 1 from v0 full outer join t0 on cast((v0.c1) in (1) as string) like
v0.c0;
+% .%6 # table_name
+% %6 # name
+% tinyint # type
+% 1 # length
+#select cast(sum(all + (cast(t0.c0 as int))) as bigint) from v0 full outer
join t0 on ((cast((cast(v0.c1 as boolean)) not in (true, ((t0.c0)=(t0.c0)),
cast(1745166981 as boolean)) as string))ilike(v0.c0));
+% .%14 # table_name
+% %14 # name
+% bigint # type
+% 1 # length
+[ NULL ]
#ROLLBACK;
#DROP TABLE tbl_ProductSales;
#DROP TABLE another_T;
diff --git a/sql/test/miscellaneous/Tests/simple_plans.sql
b/sql/test/miscellaneous/Tests/simple_plans.sql
--- a/sql/test/miscellaneous/Tests/simple_plans.sql
+++ b/sql/test/miscellaneous/Tests/simple_plans.sql
@@ -15,4 +15,15 @@ insert into myx values ('1aea00e5db6e081
select * from myx where x in ('1aea00e5db6e0810b554fde31d961966') or y is not
null;
select * from myx where x in ('1aea00e5db6e0810b554fde31d961966') or y is null;
+CREATE TABLE tbl_ProductSales (ColID int, Product_Category varchar(64),
Product_Name varchar(64), TotalSales int);
+INSERT INTO tbl_ProductSales VALUES (1,'Game','Mobo Game',200),(2,'Game','PKO
Game',400),(3,'Fashion','Shirt',500),(4,'Fashion','Shorts',100);
+CREATE TABLE another_T (col1 INT, col2 INT, col3 INT, col4 INT, col5 INT, col6
INT, col7 INT, col8 INT);
+INSERT INTO another_T VALUES (1,2,3,4,5,6,7,8), (11,22,33,44,55,66,77,88),
(111,222,333,444,555,666,777,888), (1111,2222,3333,4444,5555,6666,7777,8888);
+
+-- make sure the following explains don't show crossproducts!
+EXPLAIN SELECT 1 FROM another_t t1 INNER JOIN another_t t2 ON t1.col1 BETWEEN
t2.col1 AND t2.col1;
+EXPLAIN SELECT 1 FROM another_t t1 INNER JOIN another_t t2 ON t1.col1 BETWEEN
t2.col1 - 1 AND t2.col1 + 1;
+EXPLAIN SELECT 1 FROM another_t t1 INNER JOIN another_t t2 ON t1.col1 BETWEEN
t2.col1 AND 2;
+EXPLAIN SELECT 1 FROM tbl_productsales t1 INNER JOIN tbl_productsales t2 ON
t1.product_category LIKE t2.product_category;
+
rollback;
diff --git a/sql/test/miscellaneous/Tests/simple_plans.stable.out
b/sql/test/miscellaneous/Tests/simple_plans.stable.out
--- a/sql/test/miscellaneous/Tests/simple_plans.stable.out
+++ b/sql/test/miscellaneous/Tests/simple_plans.stable.out
@@ -72,6 +72,334 @@ project (
% x, y # name
% uuid, uuid # type
% 36, 36 # length
+#CREATE TABLE tbl_ProductSales (ColID int, Product_Category varchar(64),
Product_Name varchar(64), TotalSales int);
+#INSERT INTO tbl_ProductSales VALUES (1,'Game','Mobo Game',200),(2,'Game','PKO
Game',400),(3,'Fashion','Shirt',500),(4,'Fashion','Shorts',100);
+[ 4 ]
+#CREATE TABLE another_T (col1 INT, col2 INT, col3 INT, col4 INT, col5 INT,
col6 INT, col7 INT, col8 INT);
+#INSERT INTO another_T VALUES (1,2,3,4,5,6,7,8), (11,22,33,44,55,66,77,88),
(111,222,333,444,555,666,777,888), (1111,2222,3333,4444,5555,6666,7777,8888);
+[ 4 ]
+#EXPLAIN SELECT 1 FROM another_t t1 INNER JOIN another_t t2 ON t1.col1 BETWEEN
t2.col1 AND t2.col1;
+% .explain # table_name
+% mal # name
+% clob # type
+% 178 # length
+function user.s20_0():void;
+ X_2:void := querylog.define("explain select 1 from another_t t1 inner join
another_t t2 on t1.col1 between t2.col1 and t2.col1;":str, "default_pipe":str,
23:int);
+barrier X_136:bit := language.dataflow();
+ X_32:bat[:str] := bat.pack(".%3":str);
+ X_33:bat[:str] := bat.pack("%3":str);
+ X_34:bat[:str] := bat.pack("tinyint":str);
+ X_35:bat[:int] := bat.pack(1:int);
+ X_36:bat[:int] := bat.pack(0:int);
+ X_5:int := sql.mvc();
+ C_82:bat[:oid] := sql.tid(X_5:int, "sys":str, "another_t":str, 0:int,
4:int);
+ X_93:bat[:int] := sql.bind(X_5:int, "sys":str, "another_t":str,
"col1":str, 0:int, 0:int, 4:int);
+ X_98:bat[:int] := algebra.projection(C_82:bat[:oid], X_93:bat[:int]);
+ C_13:bat[:oid] := sql.tid(X_5:int, "sys":str, "another_t":str);
+ X_15:bat[:int] := sql.bind(X_5:int, "sys":str, "another_t":str,
"col1":str, 0:int);
+ X_17:bat[:int] := algebra.projection(C_13:bat[:oid], X_15:bat[:int]);
+ (X_102:bat[:oid], X_103:bat[:oid]) := algebra.rangejoin(X_98:bat[:int],
X_17:bat[:int], X_17:bat[:int], nil:BAT, nil:BAT, true:bit, true:bit,
false:bit, false:bit, nil:lng);
+ X_110:bat[:int] := algebra.projection(X_102:bat[:oid], X_98:bat[:int]);
+ X_118:bat[:bte] := algebra.project(X_110:bat[:int], 1:bte);
+ C_84:bat[:oid] := sql.tid(X_5:int, "sys":str, "another_t":str, 1:int,
4:int);
+ X_94:bat[:int] := sql.bind(X_5:int, "sys":str, "another_t":str,
"col1":str, 0:int, 1:int, 4:int);
+ X_99:bat[:int] := algebra.projection(C_84:bat[:oid], X_94:bat[:int]);
+ (X_104:bat[:oid], X_105:bat[:oid]) := algebra.rangejoin(X_99:bat[:int],
X_17:bat[:int], X_17:bat[:int], nil:BAT, nil:BAT, true:bit, true:bit,
false:bit, false:bit, nil:lng);
+ X_111:bat[:int] := algebra.projection(X_104:bat[:oid], X_99:bat[:int]);
+ X_119:bat[:bte] := algebra.project(X_111:bat[:int], 1:bte);
+ C_86:bat[:oid] := sql.tid(X_5:int, "sys":str, "another_t":str, 2:int,
4:int);
+ X_95:bat[:int] := sql.bind(X_5:int, "sys":str, "another_t":str,
"col1":str, 0:int, 2:int, 4:int);
+ X_100:bat[:int] := algebra.projection(C_86:bat[:oid], X_95:bat[:int]);
+ (X_106:bat[:oid], X_107:bat[:oid]) := algebra.rangejoin(X_100:bat[:int],
X_17:bat[:int], X_17:bat[:int], nil:BAT, nil:BAT, true:bit, true:bit,
false:bit, false:bit, nil:lng);
+ X_112:bat[:int] := algebra.projection(X_106:bat[:oid], X_100:bat[:int]);
+ X_120:bat[:bte] := algebra.project(X_112:bat[:int], 1:bte);
+ C_88:bat[:oid] := sql.tid(X_5:int, "sys":str, "another_t":str, 3:int,
4:int);
+ X_96:bat[:int] := sql.bind(X_5:int, "sys":str, "another_t":str,
"col1":str, 0:int, 3:int, 4:int);
+ X_101:bat[:int] := algebra.projection(C_88:bat[:oid], X_96:bat[:int]);
+ (X_108:bat[:oid], X_109:bat[:oid]) := algebra.rangejoin(X_101:bat[:int],
X_17:bat[:int], X_17:bat[:int], nil:BAT, nil:BAT, true:bit, true:bit,
false:bit, false:bit, nil:lng);
+ X_113:bat[:int] := algebra.projection(X_108:bat[:oid], X_101:bat[:int]);
+ X_121:bat[:bte] := algebra.project(X_113:bat[:int], 1:bte);
+ X_130:bat[:bte] := mat.packIncrement(X_118:bat[:bte], 4:int);
+ X_132:bat[:bte] := mat.packIncrement(X_130:bat[:bte], X_119:bat[:bte]);
+ X_133:bat[:bte] := mat.packIncrement(X_132:bat[:bte], X_120:bat[:bte]);
+ X_30:bat[:bte] := mat.packIncrement(X_133:bat[:bte], X_121:bat[:bte]);
+ language.pass(X_98:bat[:int]);
+ language.pass(X_99:bat[:int]);
+ language.pass(X_100:bat[:int]);
+ language.pass(X_17:bat[:int]);
+ language.pass(X_101:bat[:int]);
+exit X_136:bit;
+ sql.resultSet(X_32:bat[:str], X_33:bat[:str], X_34:bat[:str],
X_35:bat[:int], X_36:bat[:int], X_30:bat[:bte]);
+end user.s20_0;
+#inline actions= 0 time=0 usec
+#remap actions= 0 time=1 usec
+#costmodel actions= 1 time=1 usec
+#coercion actions= 0 time=1 usec
+#aliases actions= 1 time=6 usec
+#evaluate actions= 0 time=5 usec
+#emptybind actions= 0 time=0 usec
+#pushselect actions= 0 time=11 usec
+#aliases actions= 0 time=1 usec
+#mitosis actions=4 time=28 usec
+#mergetable actions= 5 time=65 usec
+#deadcode actions= 9 time=15 usec
+#aliases actions= 0 time=0 usec
+#constants actions= 5 time=7 usec
+#commonTerms actions= 0 time=12 usec
+#projectionpath actions= 0 time=6 usec
+#deadcode actions= 0 time=10 usec
+#reorder actions= 1 time=31 usec
+#matpack actions= 1 time=11 usec
+#dataflow actions= 1 time=28 usec
+#multiplex actions= 0 time=2 usec
+#profiler actions= 1 time=2 usec
+#candidates actions= 1 time=2 usec
+#deadcode actions= 0 time=12 usec
+#postfix actions= 0 time=9 usec
+#wlc actions= 0 time=0 usec
+#garbagecollector actions= 1 time=46 usec
+#total actions=29 time=407 usec
+#EXPLAIN SELECT 1 FROM another_t t1 INNER JOIN another_t t2 ON t1.col1 BETWEEN
t2.col1 - 1 AND t2.col1 + 1;
+% .explain # table_name
+% mal # name
+% clob # type
+% 178 # length
+function user.s22_0():void;
+ X_4:void := querylog.define("explain select 1 from another_t t1 inner join
another_t t2 on t1.col1 between t2.col1 - 1 and t2.col1 + 1;":str,
"default_pipe":str, 35:int);
+barrier X_174:bit := language.dataflow();
+ X_49:bat[:str] := bat.pack(".%12":str);
+ X_50:bat[:str] := bat.pack("%12":str);
+ X_51:bat[:str] := bat.pack("tinyint":str);
+ X_52:bat[:int] := bat.pack(1:int);
+ X_53:bat[:int] := bat.pack(0:int);
+ X_7:int := sql.mvc();
+ C_104:bat[:oid] := sql.tid(X_7:int, "sys":str, "another_t":str, 0:int,
4:int);
+ X_115:bat[:int] := sql.bind(X_7:int, "sys":str, "another_t":str,
"col1":str, 0:int, 0:int, 4:int);
+ X_120:bat[:int] := algebra.projection(C_104:bat[:oid], X_115:bat[:int]);
+ X_124:bat[:lng] := batcalc.lng(X_120:bat[:int]);
+ C_17:bat[:oid] := sql.tid(X_7:int, "sys":str, "another_t":str);
+ X_19:bat[:int] := sql.bind(X_7:int, "sys":str, "another_t":str,
"col1":str, 0:int);
+ X_20:bat[:int] := algebra.projection(C_17:bat[:oid], X_19:bat[:int]);
+ X_21:bat[:lng] := batcalc.lng(X_20:bat[:int]);
+ X_24:bat[:lng] := batcalc.-(X_21:bat[:lng], 1:lng, nil:BAT);
+ X_30:bat[:lng] := batcalc.+(X_21:bat[:lng], 1:lng, nil:BAT);
+ (X_128:bat[:oid], X_129:bat[:oid]) := algebra.rangejoin(X_124:bat[:lng],
X_24:bat[:lng], X_30:bat[:lng], nil:BAT, nil:BAT, true:bit, true:bit,
false:bit, false:bit, nil:lng);
+ X_136:bat[:int] := algebra.projection(X_128:bat[:oid], X_120:bat[:int]);
+ X_156:bat[:bte] := algebra.project(X_136:bat[:int], 1:bte);
+ C_106:bat[:oid] := sql.tid(X_7:int, "sys":str, "another_t":str, 1:int,
4:int);
+ X_116:bat[:int] := sql.bind(X_7:int, "sys":str, "another_t":str,
"col1":str, 0:int, 1:int, 4:int);
+ X_121:bat[:int] := algebra.projection(C_106:bat[:oid], X_116:bat[:int]);
+ X_125:bat[:lng] := batcalc.lng(X_121:bat[:int]);
+ (X_130:bat[:oid], X_131:bat[:oid]) := algebra.rangejoin(X_125:bat[:lng],
X_24:bat[:lng], X_30:bat[:lng], nil:BAT, nil:BAT, true:bit, true:bit,
false:bit, false:bit, nil:lng);
+ X_137:bat[:int] := algebra.projection(X_130:bat[:oid], X_121:bat[:int]);
+ X_157:bat[:bte] := algebra.project(X_137:bat[:int], 1:bte);
+ C_108:bat[:oid] := sql.tid(X_7:int, "sys":str, "another_t":str, 2:int,
4:int);
+ X_117:bat[:int] := sql.bind(X_7:int, "sys":str, "another_t":str,
"col1":str, 0:int, 2:int, 4:int);
+ X_122:bat[:int] := algebra.projection(C_108:bat[:oid], X_117:bat[:int]);
+ X_126:bat[:lng] := batcalc.lng(X_122:bat[:int]);
+ (X_132:bat[:oid], X_133:bat[:oid]) := algebra.rangejoin(X_126:bat[:lng],
X_24:bat[:lng], X_30:bat[:lng], nil:BAT, nil:BAT, true:bit, true:bit,
false:bit, false:bit, nil:lng);
+ X_138:bat[:int] := algebra.projection(X_132:bat[:oid], X_122:bat[:int]);
+ X_158:bat[:bte] := algebra.project(X_138:bat[:int], 1:bte);
+ C_110:bat[:oid] := sql.tid(X_7:int, "sys":str, "another_t":str, 3:int,
4:int);
+ X_118:bat[:int] := sql.bind(X_7:int, "sys":str, "another_t":str,
"col1":str, 0:int, 3:int, 4:int);
+ X_123:bat[:int] := algebra.projection(C_110:bat[:oid], X_118:bat[:int]);
+ X_127:bat[:lng] := batcalc.lng(X_123:bat[:int]);
+ (X_134:bat[:oid], X_135:bat[:oid]) := algebra.rangejoin(X_127:bat[:lng],
X_24:bat[:lng], X_30:bat[:lng], nil:BAT, nil:BAT, true:bit, true:bit,
false:bit, false:bit, nil:lng);
+ X_139:bat[:int] := algebra.projection(X_134:bat[:oid], X_123:bat[:int]);
+ X_159:bat[:bte] := algebra.project(X_139:bat[:int], 1:bte);
+ X_168:bat[:bte] := mat.packIncrement(X_156:bat[:bte], 4:int);
+ X_170:bat[:bte] := mat.packIncrement(X_168:bat[:bte], X_157:bat[:bte]);
+ X_171:bat[:bte] := mat.packIncrement(X_170:bat[:bte], X_158:bat[:bte]);
+ X_47:bat[:bte] := mat.packIncrement(X_171:bat[:bte], X_159:bat[:bte]);
+ language.pass(X_21:bat[:lng]);
+ language.pass(X_120:bat[:int]);
+ language.pass(X_121:bat[:int]);
+ language.pass(X_122:bat[:int]);
+ language.pass(X_24:bat[:lng]);
+ language.pass(X_30:bat[:lng]);
+ language.pass(X_123:bat[:int]);
+exit X_174:bit;
+ sql.resultSet(X_49:bat[:str], X_50:bat[:str], X_51:bat[:str],
X_52:bat[:int], X_53:bat[:int], X_47:bat[:bte]);
+end user.s22_0;
+#inline actions= 0 time=2 usec
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
