Changeset: 28e02819984d for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=28e02819984d
Modified Files:
sql/server/rel_select.c
sql/server/sql_semantic.c
sql/test/miscellaneous/Tests/groupby_error.sql
sql/test/miscellaneous/Tests/groupby_error.stable.err
sql/test/miscellaneous/Tests/groupby_error.stable.out
Branch: linear-hashing
Log Message:
Make prepared statements work with the BETWEEN and EXISTS operators
diffs (205 lines):
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
@@ -1827,8 +1827,6 @@ rel_compare(sql_query *query, sql_rel *r
if (!rs2)
return NULL;
}
- if (!rs)
- return NULL;
if (ls->card > rs->card && rs->card == CARD_AGGR && is_sql_having(f))
return sql_error(sql, 05, SQLSTATE(42000) "SELECT: cannot use
non GROUP BY column '%s.%s' in query results without an aggregate function",
exp_relname(ls), exp_name(ls));
if (rs->card > ls->card && ls->card == CARD_AGGR && is_sql_having(f))
@@ -1928,18 +1926,25 @@ static sql_exp*
}
static sql_exp *
-exp_exist(sql_query *query, sql_exp *le, int exists)
+exp_exist(sql_query *query, sql_rel *rel, sql_exp *le, int exists)
{
mvc *sql = query->sql;
sql_subfunc *exists_func = NULL;
-
+ sql_subtype *t;
+
+ if (!exp_name(le))
+ exp_label(sql->sa, le, ++sql->label);
+ if (!exp_subtype(le) && rel_set_type_param(sql,
sql_bind_localtype("bit"), rel, le, 0) < 0) /* workaround */
+ return NULL;
+ t = exp_subtype(le);
+
if (exists)
- exists_func = sql_bind_func(sql->sa, sql->session->schema,
"sql_exists", exp_subtype(le), NULL, F_FUNC);
+ exists_func = sql_bind_func(sql->sa, sql->session->schema,
"sql_exists", t, NULL, F_FUNC);
else
- exists_func = sql_bind_func(sql->sa, sql->session->schema,
"sql_not_exists", exp_subtype(le), NULL, F_FUNC);
+ exists_func = sql_bind_func(sql->sa, sql->session->schema,
"sql_not_exists", t, NULL, F_FUNC);
if (!exists_func)
- return sql_error(sql, 02, SQLSTATE(42000) "exist operator on
type %s missing", exp_subtype(le)->type->sqlname);
+ return sql_error(sql, 02, SQLSTATE(42000) "exist operator on
type %s missing", t->type->sqlname);
return exp_unop(sql->sa, le, exists_func);
}
@@ -1952,7 +1957,7 @@ rel_exists_value_exp(sql_query *query, s
le = rel_value_exp(query, rel, sc->data.sym, f, ek);
if (!le)
return NULL;
- e = exp_exist(query, le, sc->token == SQL_EXISTS);
+ e = exp_exist(query, rel ? *rel : NULL, le, sc->token == SQL_EXISTS);
if (e) {
/* only freevar should have CARD_AGGR */
e->card = CARD_ATOM;
@@ -1975,7 +1980,7 @@ rel_exists_exp(sql_query *query, sql_rel
assert(!is_sql_sel(f));
if (sq) {
sql_exp *e = exp_rel(sql, sq);
- e = exp_exist(query, e, sc->token == SQL_EXISTS);
+ e = exp_exist(query, rel, e, sc->token == SQL_EXISTS);
if (e) {
/* only freevar should have CARD_AGGR */
e->card = CARD_ATOM;
@@ -2531,12 +2536,24 @@ rel_logical_exp(sql_query *query, sql_re
sql_exp *le = rel_value_exp(query, &rel, lo, f, ek);
sql_exp *re1 = rel_value_exp(query, &rel, ro1, f, ek);
sql_exp *re2 = rel_value_exp(query, &rel, ro2, f, ek);
+ sql_subtype *t1, *t2, *t3;
int flag = 0;
assert(sc->data.lval->h->next->type == type_int);
if (!le || !re1 || !re2)
return NULL;
+ t1 = exp_subtype(le);
+ t2 = exp_subtype(re1);
+ t3 = exp_subtype(re2);
+
+ if (!t1 && (t2 || t3) && rel_binop_check_types(sql, rel, le, t2
? re1 : re2, 0) < 0)
+ return NULL;
+ if (!t2 && (t1 || t3) && rel_binop_check_types(sql, rel, le, t1
? le : re2, 0) < 0)
+ return NULL;
+ if (!t3 && (t1 || t2) && rel_binop_check_types(sql, rel, le, t1
? le : re1, 0) < 0)
+ return NULL;
+
if (rel_convert_types(sql, rel, rel, &le, &re1, 1, type_equal)
< 0 ||
rel_convert_types(sql, rel, rel, &le, &re2, 1, type_equal)
< 0)
return NULL;
@@ -2550,9 +2567,8 @@ rel_logical_exp(sql_query *query, sql_re
sql_subfunc *min = sql_bind_func(sql->sa,
sql->session->schema, "sql_min", exp_subtype(re1), exp_subtype(re2), F_FUNC);
sql_subfunc *max = sql_bind_func(sql->sa,
sql->session->schema, "sql_max", exp_subtype(re1), exp_subtype(re2), F_FUNC);
- if (!min || !max) {
+ if (!min || !max)
return sql_error(sql, 02, SQLSTATE(42000) "min
or max operator on types %s %s missing", exp_subtype(re1)->type->sqlname,
exp_subtype(re2)->type->sqlname);
- }
tmp = exp_binop(sql->sa, re1, re2, min);
re2 = exp_binop(sql->sa, re1, re2, max);
re1 = tmp;
@@ -5685,7 +5701,6 @@ rel_joinquery_(sql_query *query, sql_rel
static sql_rel *
rel_joinquery(sql_query *query, sql_rel *rel, symbol *q)
{
-
dnode *n = q->data.lval->h;
symbol *tab_ref1 = n->data.sym;
int natural = n->next->data.i_val;
diff --git a/sql/server/sql_semantic.c b/sql/server/sql_semantic.c
--- a/sql/server/sql_semantic.c
+++ b/sql/server/sql_semantic.c
@@ -192,8 +192,7 @@ qname_catalog(dlist *qname)
if (dlist_length(qname) == 3) {
return qname->h->data.sval;
- }
-
+ }
return NULL;
}
diff --git a/sql/test/miscellaneous/Tests/groupby_error.sql
b/sql/test/miscellaneous/Tests/groupby_error.sql
--- a/sql/test/miscellaneous/Tests/groupby_error.sql
+++ b/sql/test/miscellaneous/Tests/groupby_error.sql
@@ -24,7 +24,15 @@ prepare select col0 from tab0 where (col
prepare select ? < ANY (select max(col0) from tab0) from tab0 t1;
prepare select col0 = ALL (select ? from tab0) from tab0 t1;
+prepare select 1 from tab0 where 1 between ? and ?;
+prepare select 1 from tab0 where ? between 1 and ?;
+prepare select 1 from tab0 where ? between ? and 1;
+
+prepare select EXISTS (SELECT ? FROM tab0) from tab0;
+prepare select EXISTS (SELECT ?,? FROM tab0) from tab0;
+
prepare select col0 from tab0 where (?) in (?); --error
+prepare select ? = ALL (select ? from tab0) from tab0 t1; --error
CREATE TABLE tab1(col0 INTEGER, col1 STRING);
prepare select 1 from tab1 where (col0,col1) in (select ?,? from tab1);
diff --git a/sql/test/miscellaneous/Tests/groupby_error.stable.err
b/sql/test/miscellaneous/Tests/groupby_error.stable.err
--- a/sql/test/miscellaneous/Tests/groupby_error.stable.err
+++ b/sql/test/miscellaneous/Tests/groupby_error.stable.err
@@ -35,6 +35,10 @@ MAPI = (monetdb) /var/tmp/mtest-19682/.
QUERY = prepare select col0 from tab0 where (?) in (?); --error
ERROR = !Cannot have a parameter (?) on both sides of an expression
CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-54771/.s.monetdb.35007
+QUERY = prepare select ? = ALL (select ? from tab0) from tab0 t1; --error
+ERROR = !Cannot have a parameter (?) on both sides of an expression
+CODE = 42000
# 10:56:47 >
# 10:56:47 > "Done."
diff --git a/sql/test/miscellaneous/Tests/groupby_error.stable.out
b/sql/test/miscellaneous/Tests/groupby_error.stable.out
--- a/sql/test/miscellaneous/Tests/groupby_error.stable.out
+++ b/sql/test/miscellaneous/Tests/groupby_error.stable.out
@@ -153,6 +153,50 @@ stdout of test 'groupby_error` in direct
% 7, 2, 1, 0, 2, 2 # length
[ "boolean", 1, 0, "", "%5", "%5" ]
[ "int", 32, 0, NULL, NULL, NULL ]
+#prepare select 1 from tab0 where 1 between ? and ?;
+#prepare select 1 from tab0 where 1 between ? and ?;
+% .prepare, .prepare, .prepare, .prepare, .prepare,
.prepare # table_name
+% type, digits, scale, schema, table, column # name
+% varchar, int, int, str, str, str # type
+% 7, 1, 1, 0, 0, 0 # length
+[ "tinyint", 1, 0, "", "", "" ]
+[ "tinyint", 1, 0, NULL, NULL, NULL ]
+[ "tinyint", 1, 0, NULL, NULL, NULL ]
+#prepare select 1 from tab0 where ? between 1 and ?;
+#prepare select 1 from tab0 where ? between 1 and ?;
+% .prepare, .prepare, .prepare, .prepare, .prepare,
.prepare # table_name
+% type, digits, scale, schema, table, column # name
+% varchar, int, int, str, str, str # type
+% 7, 1, 1, 0, 0, 0 # length
+[ "tinyint", 1, 0, "", "", "" ]
+[ "tinyint", 1, 0, NULL, NULL, NULL ]
+[ "tinyint", 1, 0, NULL, NULL, NULL ]
+#prepare select 1 from tab0 where ? between ? and 1;
+#prepare select 1 from tab0 where ? between ? and 1;
+% .prepare, .prepare, .prepare, .prepare, .prepare,
.prepare # table_name
+% type, digits, scale, schema, table, column # name
+% varchar, int, int, str, str, str # type
+% 7, 1, 1, 0, 0, 0 # length
+[ "tinyint", 1, 0, "", "", "" ]
+[ "tinyint", 1, 0, NULL, NULL, NULL ]
+[ "tinyint", 1, 0, NULL, NULL, NULL ]
+#prepare select EXISTS (SELECT ? FROM tab0) from tab0;
+#prepare select EXISTS (SELECT ? FROM tab0) from tab0;
+% .prepare, .prepare, .prepare, .prepare, .prepare,
.prepare # table_name
+% type, digits, scale, schema, table, column # name
+% varchar, int, int, str, str, str # type
+% 7, 1, 1, 0, 2, 2 # length
+[ "boolean", 1, 0, "", "%2", "%2" ]
+[ "boolean", 1, 0, NULL, NULL, NULL ]
+#prepare select EXISTS (SELECT ?,? FROM tab0) from tab0;
+#prepare select EXISTS (SELECT ?,? FROM tab0) from tab0;
+% .prepare, .prepare, .prepare, .prepare, .prepare,
.prepare # table_name
+% type, digits, scale, schema, table, column # name
+% varchar, int, int, str, str, str # type
+% 7, 1, 1, 0, 2, 2 # length
+[ "boolean", 1, 0, "", "%3", "%3" ]
+[ "boolean", 1, 0, NULL, NULL, NULL ]
+[ "boolean", 1, 0, NULL, NULL, NULL ]
#CREATE TABLE tab1(col0 INTEGER, col1 STRING);
#prepare select 1 from tab1 where (col0,col1) in (select ?,? from tab1);
#prepare select 1 from tab1 where (col0,col1) in (select ?,? from tab1);
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
