Changeset: 6baf5e734cab for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/6baf5e734cab
Modified Files:
sql/server/rel_select.c
sql/test/SQLancer/Tests/sqlancer18.test
Branch: Jan2022
Log Message:
fix crashes in sqlancer18, ie wrong combination of group by and use of
ungrouped columns
diffs (72 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
@@ -1156,6 +1156,8 @@ rel_column_ref(sql_query *query, sql_rel
if (exp)
break;
}
+ if (exp && exp->card != CARD_AGGR &&
is_groupby(outer->op) && !is_sql_aggr(f) && rel_find_exp(outer->l, exp))
+ return sql_error(sql, ERR_GROUPBY,
SQLSTATE(42000) "SELECT: cannot use non GROUP BY column '%s' in query results
without an aggregate function", name);
if (exp && outer && outer->card <= CARD_AGGR &&
exp->card > CARD_AGGR && !is_sql_aggr(f))
return sql_error(sql, ERR_GROUPBY,
SQLSTATE(42000) "SELECT: cannot use non GROUP BY column '%s' in query results
without an aggregate function", name);
if (exp && outer && !is_sql_aggr(f)) {
@@ -1237,6 +1239,8 @@ rel_column_ref(sql_query *query, sql_rel
if (exp)
break;
}
+ if (exp && exp->card != CARD_AGGR &&
is_groupby(outer->op) && !is_sql_aggr(f) && rel_find_exp(outer->l, exp))
+ return sql_error(sql, ERR_GROUPBY,
SQLSTATE(42000) "SELECT: cannot use non GROUP BY column '%s.%s' in query
results without an aggregate function", tname, cname);
if (exp && outer && outer->card <= CARD_AGGR &&
exp->card > CARD_AGGR && !is_sql_aggr(f))
return sql_error(sql, ERR_GROUPBY,
SQLSTATE(42000) "SELECT: cannot use non GROUP BY column '%s.%s' in query
results without an aggregate function", tname, cname);
if (exp && outer && !is_sql_aggr(f)) {
@@ -2271,6 +2275,7 @@ rel_logical_value_exp(sql_query *query,
symbol *ro = n->next->next->data.sym;
char *compare_op = n->next->data.sval;
int quantifier = 0;
+ int grouped = 0;
sql_exp *rs = NULL, *ls;
comp_type cmp_type = compare_str2type(compare_op);
@@ -2304,6 +2309,7 @@ rel_logical_value_exp(sql_query *query,
compare_op = "=";
}
+ grouped = (rel && (*rel) && is_groupby((*rel)->op));
ls = rel_value_exp(query, rel, lo, f|sql_farg, ek);
if (!ls)
return NULL;
@@ -2314,6 +2320,13 @@ rel_logical_value_exp(sql_query *query,
if (!rs)
return NULL;
+ if (!grouped && rel && (*rel) && is_groupby((*rel)->op) &&
!rel_find_exp(*rel, ls) && !is_freevar(ls) && (!exp_is_rel(ls))) {
+ if (exp_name(ls) && exp_relname(ls))
+ return sql_error(sql, 02, SQLSTATE(42000)
"SELECT: cannot use non GROUP BY column '%s.%s' in query results without an
aggregate function", exp_relname(ls), exp_name(ls));
+ else
+ return sql_error(sql, 02, SQLSTATE(42000)
"SELECT: subquery uses ungrouped column");
+ }
+
if (!exp_is_rel(ls) && !exp_is_rel(rs) && ls->card < rs->card) {
sql_exp *swap = ls; /* has to swap parameters like in
the rel_logical_exp case */
ls = rs;
diff --git a/sql/test/SQLancer/Tests/sqlancer18.test
b/sql/test/SQLancer/Tests/sqlancer18.test
--- a/sql/test/SQLancer/Tests/sqlancer18.test
+++ b/sql/test/SQLancer/Tests/sqlancer18.test
@@ -348,7 +348,7 @@ select greatest(1, (select (select vy.vc
statement ok
DROP VIEW vy
-statement error 42000!SELECT: subquery uses ungrouped column "vz.c0" from
outer query
+statement error 42000!SELECT: cannot use non GROUP BY column 'vz.c0' in query
results without an aggregate function
SELECT 1 FROM (select 0,1) vz(c0,c1) GROUP BY c1 HAVING sum(1) < ANY(SELECT
vz.c0 FROM t1)
statement ok
@@ -683,7 +683,7 @@ SELECT 1 FROM v100 WHERE (SELECT 1 HAVIN
statement error 42000!SELECT: cannot use non GROUP BY column 'vx.vc0' in query
results without an aggregate function
SELECT vx.vc0 < ANY(SELECT sum(vx.vc0) FROM tx) FROM (select 240 FROM tx)
vx(vc0)
-statement error 42000!SELECT: subquery uses ungrouped column "vx.vc0" from
outer query
+statement error 42000!SELECT: cannot use non GROUP BY column 'vx.vc0' in query
results without an aggregate function
SELECT sum(vx.vc0) < ANY(SELECT vx.vc0 FROM tx) FROM (select 240 FROM tx)
vx(vc0)
#these are right
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
