Changeset: dc3c6de75f10 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=dc3c6de75f10
Modified Files:
sql/server/rel_exp.c
sql/server/rel_exp.h
sql/server/rel_select.c
sql/test/SQLancer/Tests/sqlancer11.sql
sql/test/SQLancer/Tests/sqlancer11.stable.err
sql/test/SQLancer/Tests/sqlancer11.stable.out
Branch: Oct2020
Log Message:
Improved relation expression detected (it may be converted). Also improved
uncorrelated subquery detection for like and range expressions
diffs (209 lines):
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
@@ -228,26 +228,6 @@ exp_or(sql_allocator *sa, list *l, list
return e;
}
-static int /* if the quantifier has to be upcasted, ignore the upper
conversion for the cardinalilty */
-quantifier_has_rel(sql_exp *e)
-{
- if (!e)
- return 0;
- switch(e->type){
- case e_convert:
- return quantifier_has_rel(e->l);
- case e_psm:
- return exp_is_rel(e);
- case e_atom:
- case e_column:
- case e_func:
- case e_aggr:
- case e_cmp:
- return 0;
- }
- return 0;
-}
-
sql_exp *
exp_in(sql_allocator *sa, sql_exp *l, list *r, int cmptype)
{
@@ -261,7 +241,7 @@ exp_in(sql_allocator *sa, sql_exp *l, li
for (node *n = r->h; n ; n = n->next) {
sql_exp *next = n->data;
- if (!quantifier_has_rel(next) && exps_card < next->card)
+ if (!exp_is_rel(next) && exps_card < next->card)
exps_card = next->card;
}
e->card = MAX(l->card, exps_card);
@@ -296,10 +276,10 @@ exp_in_func(mvc *sql, sql_exp *le, sql_e
for (node *n = ((list*)vals->f)->h ; n ; n = n->next) {
sql_exp *next = n->data;
- if (!quantifier_has_rel(next) && exps_card <
next->card)
+ if (!exp_is_rel(next) && exps_card < next->card)
exps_card = next->card;
}
- } else if (!quantifier_has_rel(vals))
+ } else if (!exp_is_rel(vals))
exps_card = vals->card;
e->card = MAX(le->card, exps_card);
@@ -320,7 +300,7 @@ exp_compare_func(mvc *sql, sql_exp *le,
if (e) {
e->flag = quantifier;
/* At ANY and ALL operators, the cardinality on the right side
is ignored if it is a sub-relation */
- e->card = quantifier && quantifier_has_rel(re) ? le->card :
MAX(le->card, re->card);
+ e->card = quantifier && exp_is_rel(re) ? le->card :
MAX(le->card, re->card);
if (!has_nil(le) && !has_nil(re))
set_has_no_nil(e);
}
@@ -1911,7 +1891,28 @@ exp_is_null(sql_exp *e )
int
exp_is_rel( sql_exp *e )
{
- return (e && e->type == e_psm && e->flag == PSM_REL && e->l);
+ if (e) {
+ switch(e->type){
+ case e_convert:
+ return exp_is_rel(e->l);
+ case e_psm:
+ return e->flag == PSM_REL && e->l;
+ default:
+ return 0;
+ }
+ }
+ return 0;
+}
+
+int
+exps_one_is_rel(list *exps)
+{
+ if (list_empty(exps))
+ return 0;
+ for(node *n = exps->h ; n ; n = n->next)
+ if (exp_is_rel(n->data))
+ return 1;
+ return 0;
}
int
diff --git a/sql/server/rel_exp.h b/sql/server/rel_exp.h
--- a/sql/server/rel_exp.h
+++ b/sql/server/rel_exp.h
@@ -151,6 +151,7 @@ extern int exp_is_zero(sql_exp *e);
extern int exp_is_not_null(sql_exp *e);
extern int exp_is_null(sql_exp *e);
extern int exp_is_rel(sql_exp *e);
+extern int exps_one_is_rel(list *exps);
extern int exp_has_rel(sql_exp *e);
extern int exps_have_rel_exp(list *exps);
extern int exps_have_func(list *exps);
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
@@ -1493,6 +1493,8 @@ rel_filter(mvc *sql, sql_rel *rel, list
return sql_error(sql, 02, SQLSTATE(42000) "SELECT: no such
FILTER function '%s'", filter_op);
e = exp_filter(sql->sa, l, r, f, anti);
+ if (exps_one_is_rel(l) || exps_one_is_rel(r)) /* uncorrelated subquery
case */
+ return rel_select(sql->sa, rel, e);
/* atom or row => select */
if (exps_card(l) > rel->card) {
sql_exp *ls = l->h->data;
@@ -1557,7 +1559,7 @@ rel_compare_exp_(sql_query *query, sql_r
mvc *sql = query->sql;
sql_exp *e = NULL;
- if (quantifier || exp_is_rel(ls) || exp_is_rel(rs)) {
+ if (quantifier || exp_is_rel(ls) || exp_is_rel(rs) || (rs2 &&
exp_is_rel(rs2))) {
if (rs2) {
e = exp_compare2(sql->sa, ls, rs, rs2, type);
if (anti)
diff --git a/sql/test/SQLancer/Tests/sqlancer11.sql
b/sql/test/SQLancer/Tests/sqlancer11.sql
--- a/sql/test/SQLancer/Tests/sqlancer11.sql
+++ b/sql/test/SQLancer/Tests/sqlancer11.sql
@@ -22,7 +22,6 @@ insert into t1(c0) values(r']BW扗}FUp'), (cast((values
(greatest(r'Aᨐ', r'_'))) as string(616))), (r'');
DROP TABLE t1;
DROP TABLE t0;
-START TRANSACTION;
CREATE TABLE "sys"."t0" ("c0" BOOLEAN,"c1" DECIMAL(14,3));
COPY 7 RECORDS INTO "sys"."t0" FROM stdin USING DELIMITERS E'\t',E'\n','"';
false 0.458
@@ -46,5 +45,11 @@ NULL 0.374
true NULL
true NULL
-select 1 from t1, t0 where cast(t1.c1 as clob) not like ((select 'A' from t0,
t1) except all (select 'B' from t0));
-ROLLBACK;
+select 1 from t1, t0 where cast(t1.c1 as clob) not like ((select 'A' from t0,
t1) except all (select 'B' from t0)); --error, more than one row returned
+select 1 from t1, t0 where (select 1 from t1) like cast(t1.c1 as clob);
--error, more than one row returned
+select 1 from t1, t0 where cast(t1.c1 as clob) between 'b' and ((select 'A'
from t0)); --error, more than one row returned
+select 1 from t1, t0 where ((select 'A' from t0)) between cast(t1.c1 as clob)
and 'a'; --error, more than one row returned
+select 1 from t1, t0 where cast(t1.c1 as clob) between ((select 1 from t0))
and 'c'; --error, more than one row returned
+
+drop table t0;
+drop table t1;
diff --git a/sql/test/SQLancer/Tests/sqlancer11.stable.err
b/sql/test/SQLancer/Tests/sqlancer11.stable.err
--- a/sql/test/SQLancer/Tests/sqlancer11.stable.err
+++ b/sql/test/SQLancer/Tests/sqlancer11.stable.err
@@ -13,6 +13,21 @@ MAPI = (monetdb) /var/tmp/mtest-364410/
QUERY = insert into t1(c0) values(r']BW扗}FUp'), (cast((values (greatest(r'Aᨐ',
r'_'))) as string(616))), (r'');
ERROR = !INSERT INTO: PRIMARY KEY constraint 't1.t1_c0_pkey' violated
CODE = 40002
+MAPI = (monetdb) /var/tmp/mtest-98741/.s.monetdb.35644
+QUERY = select 1 from t1, t0 where cast(t1.c1 as clob) not like ((select 'A'
from t0, t1) except all (select 'B' from t0)); --error, more than one row
returned
+ERROR = !GDK reported error: BATsubcross: more than one match
+MAPI = (monetdb) /var/tmp/mtest-163002/.s.monetdb.38407
+QUERY = select 1 from t1, t0 where (select 1 from t1) like cast(t1.c1 as
clob); --error, more than one row returned
+ERROR = !GDK reported error: BATsubcross: more than one match
+MAPI = (monetdb) /var/tmp/mtest-163002/.s.monetdb.38407
+QUERY = select 1 from t1, t0 where cast(t1.c1 as clob) between 'b' and
((select 'A' from t0)); --error, more than one row returned
+ERROR = !GDK reported error: BATsubcross: more than one match
+MAPI = (monetdb) /var/tmp/mtest-98741/.s.monetdb.35644
+QUERY = select 1 from t1, t0 where ((select 'A' from t0)) between cast(t1.c1
as clob) and 'a'; --error, more than one row returned
+ERROR = !GDK reported error: BATsubcross: more than one match
+MAPI = (monetdb) /var/tmp/mtest-163002/.s.monetdb.38407
+QUERY = select 1 from t1, t0 where cast(t1.c1 as clob) between ((select 1 from
t0)) and 'c'; --error, more than one row returned
+ERROR = !GDK reported error: BATsubcross: more than one match
# 13:59:46 >
# 13:59:46 > "Done."
diff --git a/sql/test/SQLancer/Tests/sqlancer11.stable.out
b/sql/test/SQLancer/Tests/sqlancer11.stable.out
--- a/sql/test/SQLancer/Tests/sqlancer11.stable.out
+++ b/sql/test/SQLancer/Tests/sqlancer11.stable.out
@@ -32,6 +32,32 @@ stdout of test 'sqlancer11` in directory
#CREATE TABLE "sys"."t1" ("c0" CHAR(375) NOT NULL, CONSTRAINT "t1_c0_pkey"
PRIMARY KEY ("c0"), CONSTRAINT "t1_c0_fkey" FOREIGN KEY ("c0") REFERENCES
"sys"."t0" ("c1"));
#insert into t1 values ('');
[ 1 ]
+#DROP TABLE t1;
+#DROP TABLE t0;
+#START TRANSACTION;
+#CREATE TABLE "sys"."t0" ("c0" BOOLEAN,"c1" DECIMAL(14,3));
+#COPY 7 RECORDS INTO "sys"."t0" FROM stdin USING DELIMITERS E'\t',E'\n','"';
+#false 0.458
+#true 4.112
+#false 0.201
+#false 0.347
+#true 0.420
+#false 0.127
+#false 0.502
+[ 7 ]
+#CREATE TABLE "sys"."t1" ("c0" BOOLEAN,"c1" DECIMAL(14,3));
+#COPY 10 RECORDS INTO "sys"."t1" FROM stdin USING DELIMITERS E'\t',E'\n','"';
+#NULL 0.000
+#false 0.187
+#false 0.000
+#false NULL
+#false NULL
+#true NULL
+#NULL 0.325
+#NULL 0.374
+#true NULL
+#true NULL
+[ 10 ]
#ROLLBACK;
# 13:59:46 >
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
