Changeset: 7b7af87a5f9e for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/7b7af87a5f9e
Modified Files:
sql/server/rel_rel.c
sql/server/rel_rel.h
sql/server/rel_select.c
sql/server/sql_query.h
sql/test/BugTracker-2013/Tests/cannot_use_columns_after_groupby.Bug-3340.test
sql/test/SQLancer/Tests/All
sql/test/SQLancer/Tests/sqlancer04.test
sql/test/SQLancer/Tests/sqlancer05.test
sql/test/SQLancer/Tests/sqlancer09.test
sql/test/subquery/Tests/subquery6.test
Branch: Jan2022
Log Message:
fixing select col func min(1) cases
diffs (truncated from 318 to 300 lines):
diff --git a/sql/server/rel_rel.c b/sql/server/rel_rel.c
--- a/sql/server/rel_rel.c
+++ b/sql/server/rel_rel.c
@@ -318,7 +318,7 @@ rel_bind_column( mvc *sql, sql_rel *rel,
if (e)
return exp_alias_or_copy(sql, exp_relname(e), cname,
rel, e);
}
- if ((is_simple_project(rel->op) || is_groupby(rel->op)) && rel->l) {
+ if (is_simple_project(rel->op) && rel->l) {
if (!is_processed(rel))
return rel_bind_column(sql, rel->l, cname, f, no_tname);
} else if (is_set(rel->op)) {
@@ -401,7 +401,7 @@ rel_bind_column2( mvc *sql, sql_rel *rel
if (e)
return exp_alias_or_copy(sql, tname, cname, rel, e);
}
- if ((is_simple_project(rel->op) || is_groupby(rel->op)) && rel->l) {
+ if (is_simple_project(rel->op) && rel->l) {
if (!is_processed(rel))
return rel_bind_column2(sql, rel->l, tname, cname, f);
} else if (is_set(rel->op)) {
diff --git a/sql/server/rel_rel.h b/sql/server/rel_rel.h
--- a/sql/server/rel_rel.h
+++ b/sql/server/rel_rel.h
@@ -106,7 +106,7 @@ extern sql_rel *rel_or(mvc *sql, sql_rel
extern sql_rel *rel_add_identity(mvc *sql, sql_rel *rel, sql_exp **exp);
extern sql_rel *rel_add_identity2(mvc *sql, sql_rel *rel, sql_exp **exp);
-extern sql_exp * rel_find_column( sql_allocator *sa, sql_rel *rel, const char
*tname, const char *cname );
+extern sql_exp *rel_find_column( sql_allocator *sa, sql_rel *rel, const char
*tname, const char *cname );
extern int rel_in_rel(sql_rel *super, sql_rel *sub);
extern sql_rel *rel_parent(sql_rel *rel);
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
@@ -27,6 +27,13 @@
#define VALUE_FUNC(f) (f->func->type == F_FUNC || f->func->type == F_FILT)
#define check_card(card,f) ((card == card_none && !f->res) ||
(CARD_VALUE(card) && f->res && VALUE_FUNC(f)) || card == card_loader || (card
== card_relation && f->func->type == F_UNION))
+static void
+query_processed(sql_query *query )
+{
+ query -> last_exp = NULL;
+ query -> last_state = 0;
+}
+
/* return all expressions, with table name == tname */
static list *
rel_table_projections( mvc *sql, sql_rel *rel, char *tname, int level )
@@ -1133,9 +1140,7 @@ rel_column_ref(sql_query *query, sql_rel
if (!exp && inner)
if (!(exp = rel_bind_column(sql, inner, name, f, 0)) &&
sql->session->status == -ERR_AMBIGUOUS)
return NULL;
- if (!exp && inner && is_sql_having(f) && is_select(inner->op))
- inner = inner->l;
- if (!exp && inner && (is_sql_having(f) || is_sql_aggr(f)) &&
is_groupby(inner->op))
+ if (!exp && inner && is_sql_aggr(f) && is_groupby(inner->op))
if (!(exp = rel_bind_column(sql, inner->l, name, f, 0))
&& sql->session->status == -ERR_AMBIGUOUS)
return NULL;
if (!exp && query && query_has_outer(query)) {
@@ -1203,6 +1208,11 @@ rel_column_ref(sql_query *query, sql_rel
if (!exp) /* If no column was found, try a variable or
parameter */
exp = rel_exp_variable_on_scope(sql, NULL, name);
+ if (!exp) {
+ if (inner && !is_sql_aggr(f) && is_groupby(inner->op)
&& inner->l && (exp = rel_bind_column(sql, inner->l, name, f, 0)))
+ return sql_error(sql, ERR_NOTFOUND,
SQLSTATE(42000) "SELECT: cannot use non GROUP BY column '%s' in query results
without an aggregate function", name);
+ }
+
if (!exp)
return sql_error(sql, ERR_NOTFOUND, SQLSTATE(42000)
"SELECT: identifier '%s' unknown", name);
if (exp && inner && inner->card <= CARD_AGGR && exp->card >
CARD_AGGR && (is_sql_sel(f) || is_sql_having(f)) && !is_sql_aggr(f))
@@ -1216,9 +1226,7 @@ rel_column_ref(sql_query *query, sql_rel
if (!exp && rel && inner)
if (!(exp = rel_bind_column2(sql, inner, tname, cname,
f)) && sql->session->status == -ERR_AMBIGUOUS)
return NULL;
- if (!exp && inner && is_sql_having(f) && is_select(inner->op))
- inner = inner->l;
- if (!exp && inner && (is_sql_having(f) || is_sql_aggr(f)) &&
is_groupby(inner->op))
+ if (!exp && inner && is_sql_aggr(f) && is_groupby(inner->op))
if (!(exp = rel_bind_column2(sql, inner->l, tname,
cname, f)) && sql->session->status == -ERR_AMBIGUOUS)
return NULL;
if (!exp && query && query_has_outer(query)) {
@@ -1294,6 +1302,10 @@ rel_column_ref(sql_query *query, sql_rel
exp = exp_param_or_declared(sql->sa,
sa_strdup(sql->sa, var->sname), sa_strdup(sql->sa, var->name), &(var->var.tpe),
0);
}
}
+ if (!exp) {
+ if (inner && !is_sql_aggr(f) && is_groupby(inner->op)
&& inner->l && (exp = rel_bind_column2(sql, inner->l, tname, cname, f)))
+ return sql_error(sql, ERR_NOTFOUND,
SQLSTATE(42000) "SELECT: cannot use non GROUP BY column '%s.%s' in query
results without an aggregate function", tname, cname);
+ }
if (!exp)
return sql_error(sql, ERR_NOTFOUND, SQLSTATE(42S22)
"SELECT: no such column '%s.%s'", tname, cname);
@@ -1304,6 +1316,10 @@ rel_column_ref(sql_query *query, sql_rel
} else if (dlist_length(l) >= 3) {
return sql_error(sql, 02, SQLSTATE(42000) "TODO: column names
of level >= 3");
}
+ if (exp && !exp_is_atom(exp)) {
+ query->last_exp = exp;
+ query->last_state = f;
+ }
return exp;
}
@@ -3626,6 +3642,9 @@ static sql_exp *
}
if (!subquery && groupby && groupby->op != op_groupby) {
/* implicit groupby */
+ if (query->last_exp && !is_sql_aggr(query->last_state)) {
+ return sql_error(sql, ERR_GROUPBY, SQLSTATE(42000)
"SELECT: cannot use non GROUP BY column '%s.%s' in query results without an
aggregate function", exp_relname(query->last_exp), exp_name(query->last_exp));
+ }
res = groupby = rel_groupby(sql, groupby, NULL);
}
if (subquery) {
@@ -5442,6 +5461,7 @@ rel_where_groupby_nodes(sql_query *query
return NULL;
}
}
+ query_processed(query);
if (rel && sn->groupby) {
list *gbe, *sets = NULL;
@@ -5468,6 +5488,7 @@ rel_where_groupby_nodes(sql_query *query
if (rel->op != op_groupby)
rel = rel_groupby(sql, rel, NULL);
}
+ query_processed(query);
return rel;
}
@@ -6016,6 +6037,7 @@ rel_joinquery_(sql_query *query, sql_rel
if (inner && is_outerjoin(inner->op))
set_processed(inner);
set_processed(rel);
+ query_processed(query);
return rel;
}
@@ -6053,6 +6075,9 @@ sql_rel *
rel_subquery(sql_query *query, sql_rel *rel, symbol *sq, exp_kind ek)
{
mvc *sql = query->sql;
+ sql_exp *last_exp = query->last_exp;
+ int last_state = query->last_state;
+ query_processed(query);
int toplevel = 0;
if (!stack_push_frame(sql, NULL))
@@ -6064,7 +6089,8 @@ rel_subquery(sql_query *query, sql_rel *
rel = rel_query(query, rel, sq, toplevel, ek);
stack_pop_frame(sql);
-
+ query->last_exp = last_exp;
+ query->last_state = last_state;
if (rel && ek.type == type_relation && ek.card < card_set && rel->card
>= CARD_AGGR)
return rel_zero_or_one(sql, rel, ek);
return rel;
diff --git a/sql/server/sql_query.h b/sql/server/sql_query.h
--- a/sql/server/sql_query.h
+++ b/sql/server/sql_query.h
@@ -25,6 +25,8 @@ typedef struct stacked_query {
typedef struct sql_query {
mvc *sql;
sql_stack *outer;
+ sql_exp *last_exp;
+ int last_state;
} sql_query;
extern sql_query *query_create(mvc *sql);
diff --git
a/sql/test/BugTracker-2013/Tests/cannot_use_columns_after_groupby.Bug-3340.test
b/sql/test/BugTracker-2013/Tests/cannot_use_columns_after_groupby.Bug-3340.test
---
a/sql/test/BugTracker-2013/Tests/cannot_use_columns_after_groupby.Bug-3340.test
+++
b/sql/test/BugTracker-2013/Tests/cannot_use_columns_after_groupby.Bug-3340.test
@@ -9,9 +9,8 @@ CREATE TABLE "sys"."filer_volumes" (
CONSTRAINT "filer_volumes_filer_volume_pkey" PRIMARY KEY ("filer",
"volume")
)
-query TI rowsort
+statement error 42000!SELECT: cannot use non GROUP BY column
'toc.total_overcapacity' in query results without an aggregate function
select "function", cast((sum(fv.claim_tb) - sum(fv.used_tb)) * 100 /
toc.total_overcapacity as bigint) from sys.filer_volumes as fv, (select
sum(claim_tb) - sum(used_tb) as total_overcapacity from sys.filer_volumes) as
toc group by fv."function" order by fv."function"
-----
statement ok
drop table filer_volumes
diff --git a/sql/test/SQLancer/Tests/All b/sql/test/SQLancer/Tests/All
--- a/sql/test/SQLancer/Tests/All
+++ b/sql/test/SQLancer/Tests/All
@@ -14,7 +14,7 @@ sqlancer13
sqlancer14
sqlancer15
sqlancer16
-KNOWNFAIL?sqlancer17
+sqlancer17
sqlancer18
sqlancer19
sqlancer20
diff --git a/sql/test/SQLancer/Tests/sqlancer04.test
b/sql/test/SQLancer/Tests/sqlancer04.test
--- a/sql/test/SQLancer/Tests/sqlancer04.test
+++ b/sql/test/SQLancer/Tests/sqlancer04.test
@@ -156,10 +156,9 @@ create view v1(c0, c1) as (select distin
statement ok
create view v2(c0) as (select distinct coalesce(abs(interval '-1976292283'
month), cast(greatest('Dnwxjm4btQ9cp&\c''a_', '(y7,{q?][NHU,') as interval
month)) from t1, t0 where ((upper(''))not ilike(lower('''wAg_ z
''PPxXßgrd⍮G'))))
-query T rowsort
+statement error 42000!SELECT: cannot use non GROUP BY column 'v2.c0' in query
results without an aggregate function
select all v2.c0 from v2 right outer join v1 on
(('8*By1q)*Oc<n')like(substr('', 1151312829, 406714197)))
where (case v1.c0 when v1.c0 then interval '104279220' second end) in
(interval '2129103763' second) group by timestamp '1970-01-21 14:05:46'
-----
statement ok
ROLLBACK
@@ -394,10 +393,10 @@ 0.7223969464007746
0.6184216877785851
0.6479886625655562
-statement error 42S22!SELECT: no such column 't1.c1'
+statement error 42000!SELECT: cannot use non GROUP BY column 't1.c1' in query
results without an aggregate function
select max(coalesce(interval '5' month, interval '2' month)) from t1 order by
t1.c1 desc nulls last
-statement error 42S22!SELECT: no such column 't1.c1'
+statement error 42000!SELECT: cannot use non GROUP BY column 't1.c1' in query
results without an aggregate function
select sum(coalesce(coalesce(interval '5' month, interval '3' month), interval
'2' month, coalesce(abs(interval '5' month), interval '2' month,
case timestamp '1970-01-15 22:17:17' when timestamp '1970-01-03 22:17:36' then
interval '5' month else interval '5' month end, interval '3' month), interval
'3' month)) from t1 order by t1.c1 desc nulls last
@@ -641,7 +640,7 @@ case 1108638173 when 1 then 'PS ' when 0
statement ok
create view v40(vc0) as (values ((true) not in (false, false)),
("isauuid"(case 8 when 4 then 'F&' when 0 then '&' end)))
-query I rowsort
+statement error 42000!SELECT: cannot use non GROUP BY column 'v20.vc0' in
query results without an aggregate function
select cast(sum(count) as bigint) from (select cast(greatest(false, true) as
int) as count from v37 full outer join
(select all ((((1074663557)%(0.8367095941704169)))%(scale_up(v20.vc0,
0.25281408194923194))), 0.573499282341099
from v20 where ((((true)or(true)))or(true)) group by 2) as sub0 on
@@ -653,8 +652,6 @@ then 0.782559214696659233290176871378207
then 0.8350239383024128 when '6r' then 932574625 when '[]' then 9 when '' then
5 when 'LnP/Amb9' then 2 end)
from v40, v3 where (0.4906208915598539999081140194903127849102020263671875)
not in (1654919043, 6)) as sub1
on ((trim(v37.vc0))>=(substr(v37.vc0, 9, 8)))) as res
-----
-8
statement ok
ROLLBACK
diff --git a/sql/test/SQLancer/Tests/sqlancer05.test
b/sql/test/SQLancer/Tests/sqlancer05.test
--- a/sql/test/SQLancer/Tests/sqlancer05.test
+++ b/sql/test/SQLancer/Tests/sqlancer05.test
@@ -184,7 +184,7 @@ SELECT count(ALL + (((v0.c0)/(((v0.c0)^(
----
0
-statement error 42S22!SELECT: no such column 'v0.c0'
+statement error 42000!SELECT: cannot use non GROUP BY column 'v0.c0' in query
results without an aggregate function
SELECT count(ALL + (((v0.c0)/(((v0.c0)^(v0.c0)))))) FROM v0 ORDER BY v0.c0 ASC
NULLS LAST, v0.c0 DESC NULLS FIRST, v0.c0 DESC NULLS LAST
statement ok
diff --git a/sql/test/SQLancer/Tests/sqlancer09.test
b/sql/test/SQLancer/Tests/sqlancer09.test
--- a/sql/test/SQLancer/Tests/sqlancer09.test
+++ b/sql/test/SQLancer/Tests/sqlancer09.test
@@ -718,20 +718,13 @@ create or replace view v0(vc0, vc1, vc2)
statement ok
create or replace view v1(vc0, vc1) as (select 0.80, 'a')
-query I rowsort
-select 1 from v0, v1
-inner join (values (88), (3), (59)) as sub0 on true natural join
-(select distinct v0.vc0 from v1, v0 group by v0.vc1) as sub1
-where v0.vc0 = 0.06683239
-----
-
statement ok
create or replace view v0(vc0, vc1, vc2) as (values (nullif(cast(220424338 as
smallint), case 0.04487378302689426501359548637992702424526214599609375 when
126710153 then 0.6929207132873739 when 26968 then 26968.0 when 1679982115 then
29 else 0.8204445662571608 end), cast(sql_min('', '#Ts9.H') as boolean),
0.45182237213592768565462165497592650353908538818359375))
statement ok
create or replace view v1(vc0, vc1) as (select
round(0.80863825841427316998277774473535828292369842529296875,
0.6571010204254646), '䝀i8jPo' where not (false))
-statement error 22003!overflow in conversion of 220424338 to sht.
+statement error 42000!SELECT: cannot use non GROUP BY column 'v0.vc0' in query
results without an aggregate function
select all v0.vc2, greatest(min(all least(-266013300, 864326131)),
-1183805674), greatest(-544857133, + (v0.vc2)) from v0, v1
inner join (values (+ (88)), (greatest(0.8732546822607857,
greatest(1933292715, -525885982))), (case when (-1466474216) in
(-1504372996, 417052418, 94) then least(28288, 1447812316) when cast('' as
boolean) then "second"(time '14:18:59') else
@@ -743,6 +736,24 @@ statement ok
ROLLBACK
statement ok
+START TRANSACTION
+
+statement ok
+create or replace view v0(vc0, vc1, vc2) as (values (3, true, 0.3))
+
+statement ok
+create or replace view v1(vc0, vc1) as (select 0.80, 'a')
+
+statement error 42000!SELECT: cannot use non GROUP BY column 'v0.vc0' in query
results without an aggregate function
+select 1 from v0, v1
+inner join (values (88), (3), (59)) as sub0 on true natural join
+(select distinct v0.vc0 from v1, v0 group by v0.vc1) as sub1
+where v0.vc0 = 0.06683239
+
+statement ok
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
