Changeset: 7d3c2df56b66 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/7d3c2df56b66
Modified Files:
sql/backends/monet5/rel_bin.c
sql/server/rel_rel.c
sql/server/rel_select.c
sql/test/BugTracker-2022/Tests/All
sql/test/SQLancer/Tests/All
sql/test/subquery/Tests/subquery6.test
Branch: default
Log Message:
Merged with Jan2022
diffs (truncated from 389 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
@@ -4221,10 +4221,6 @@ sql_insert_check_null(backend *be, sql_t
stmt *s = i;
char *msg = NULL;
- /* foreach column add predicate */
- if (add_column_predicate(be, c) != LOG_OK)
- return sql_error(sql, 10, SQLSTATE(HY013)
MAL_MALLOC_FAIL);
-
if (!(s->key && s->nrcols == 0)) {
s = stmt_selectnil(be, column(be, i));
s = stmt_aggr(be, s, NULL, NULL, cnt, 1, 0, 1);
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
@@ -319,7 +319,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)) {
@@ -407,7 +407,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/BugTracker-2022/Tests/All
b/sql/test/BugTracker-2022/Tests/All
--- a/sql/test/BugTracker-2022/Tests/All
+++ b/sql/test/BugTracker-2022/Tests/All
@@ -1,2 +1,3 @@
date-calculations.Bug-7227
transaction-conflict.Bug-7228
+insert-not-null.Bug-7232
diff --git a/sql/test/BugTracker-2022/Tests/insert-not-null.Bug-7232.test
b/sql/test/BugTracker-2022/Tests/insert-not-null.Bug-7232.test
new file mode 100644
--- /dev/null
+++ b/sql/test/BugTracker-2022/Tests/insert-not-null.Bug-7232.test
@@ -0,0 +1,45 @@
+@connection(id=1, username=monetdb, password=monetdb)
+statement ok
+CREATE TABLE test(k int NOT NULL)
+
+@connection(id=1)
+statement ok
+START TRANSACTION
+
+@connection(id=2, username=monetdb, password=monetdb)
+statement ok
+START TRANSACTION
+
+@connection(id=1)
+statement ok
+INSERT INTO test VALUES (1)
+
+@connection(id=2)
+statement ok
+INSERT INTO test VALUES (2)
+
+@connection(id=1)
+statement ok
+COMMIT
+
+@connection(id=2)
+statement ok
+COMMIT
+
+@connection(id=1)
+query T
+SELECT k FROM test
+----
+1
+2
+
+@connection(id=2)
+query T
+SELECT k FROM test
+----
+1
+2
+
+@connection(id=1)
+statement ok
+DROP TABLE test
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)))
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
