Changeset: 66b3debbea80 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/66b3debbea80
Added Files:
sql/test/BugTracker-2025/Tests/7717-check-with-window.test
Modified Files:
sql/server/rel_rel.h
sql/server/rel_schema.c
sql/server/rel_select.c
sql/test/BugTracker-2025/Tests/7714-crash-in-window-with-subquery.test
sql/test/BugTracker-2025/Tests/All
Branch: default
Log Message:
disallow window functions in check constraints
solved issues #7714 and #7717
diffs (77 lines):
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
@@ -37,6 +37,7 @@
#define sql_merge (1 << 17) //ORed
#define sql_no_subquery (1 << 18) //ORed
#define sql_qualify (1 << 19) //ORed
+#define sql_check (1 << 20) //ORed
#define is_sql_from(X) ((X & sql_from) == sql_from)
#define is_sql_where(X) ((X & sql_where) == sql_where)
@@ -58,6 +59,7 @@
#define is_sql_merge(X) ((X & sql_merge) == sql_merge)
#define is_sql_no_subquery(X) ((X & sql_no_subquery) == sql_no_subquery)
#define is_sql_qualify(X) ((X & sql_qualify) == sql_qualify)
+#define is_sql_check(X) ((X & sql_check) == sql_check)
#define is_anyequal_func(sf) (strcmp((sf)->func->base.name, "sql_anyequal") ==
0 || strcmp((sf)->func->base.name, "sql_not_anyequal") == 0)
#define is_anyequal(sf) (strcmp((sf)->func->base.name, "sql_anyequal") == 0)
diff --git a/sql/server/rel_schema.c b/sql/server/rel_schema.c
--- a/sql/server/rel_schema.c
+++ b/sql/server/rel_schema.c
@@ -413,7 +413,7 @@ create_check_plan(sql_query *query, symb
sql_rel *rel = rel_basetable(sql, t, t->base.name);
if (!stack_push_frame(sql, NULL))
return sql_error(sql, 02, SQLSTATE(HY013) MAL_MALLOC_FAIL);
- sql_exp *e = rel_logical_value_exp(query, &rel,
s->data.lval->h->data.sym, sql_sel | sql_no_subquery, ek);
+ sql_exp *e = rel_logical_value_exp(query, &rel,
s->data.lval->h->data.sym, sql_sel | sql_no_subquery | sql_check, ek);
stack_pop_frame(sql);
if (!e || !rel || !is_basetable(rel->op))
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
@@ -4872,11 +4872,12 @@ rel_rankop(sql_query *query, sql_rel **r
rank = true;
supports_frames = (!rank || is_value);
- if (is_sql_update_set(f) || is_sql_psm(f) || is_sql_values(f) ||
is_sql_join(f) || is_sql_where(f) || is_sql_groupby(f) || is_sql_having(f) ||
is_psm_call(f) || is_sql_from(f)) {
+ if (is_sql_update_set(f) || is_sql_psm(f) || is_sql_values(f) ||
is_sql_join(f) || is_sql_where(f) || is_sql_groupby(f) || is_sql_having(f) ||
is_psm_call(f) || is_sql_from(f) || is_sql_check(f)) {
char *uaname = SA_NEW_ARRAY(sql->ta, char, strlen(aname) + 1);
const char *clause = is_sql_update_set(f)||is_sql_psm(f)?"in
SET, WHILE, IF, ELSE, CASE, WHEN, RETURN, ANALYZE clauses (use
subquery)":is_sql_values(f)?"on an unique value":
is_sql_join(f)?"in
JOIN conditions":is_sql_where(f)?"in WHERE clause":is_sql_groupby(f)?"in GROUP
BY clause":
- is_psm_call(f)?"in
CALL":is_sql_from(f)?"in functions in FROM":"in HAVING clause";
+ is_psm_call(f)?"in
CALL":is_sql_from(f)?"in functions in FROM":
+ is_sql_check(f)?"in
check constraints":"in HAVING clause";
return sql_error(sql, 02, SQLSTATE(42000) "%s: window function
'%s' not allowed %s", toUpperCopy(uaname, aname), aname, clause);
} else if (is_sql_aggr(f)) {
char *uaname = SA_NEW_ARRAY(sql->ta, char, strlen(aname) + 1);
diff --git
a/sql/test/BugTracker-2025/Tests/7714-crash-in-window-with-subquery.test
b/sql/test/BugTracker-2025/Tests/7714-crash-in-window-with-subquery.test
--- a/sql/test/BugTracker-2025/Tests/7714-crash-in-window-with-subquery.test
+++ b/sql/test/BugTracker-2025/Tests/7714-crash-in-window-with-subquery.test
@@ -1,5 +1,5 @@
-statement error 42000!SELECT: no such window function 'dense_rank'(any)
+statement error 42000!DENSE_RANK: window function 'dense_rank' not allowed in
check constraints
CREATE TABLE v0 ( v1 INT , v2 VARCHAR ( 37 ) NOT NULL CHECK ( CAST (
DENSE_RANK ( v1 ) OVER ( PARTITION BY ( CASE WHEN v2 > 8 THEN 'x' ELSE 'x' END
) ORDER BY v2 ) AS INTEGER ) ) )
-statement ok
+statement error 42000!COUNT: window function 'count' not allowed in check
constraints
CREATE TABLE v1 ( v1 INT , v2 VARCHAR ( 37 ) NOT NULL CHECK ( CAST ( count (
v1 ) OVER ( PARTITION BY ( CASE WHEN v2 > 8 THEN 'x' ELSE 'x' END ) ORDER BY v2
) AS INTEGER ) ) )
diff --git a/sql/test/BugTracker-2025/Tests/7717-check-with-window.test
b/sql/test/BugTracker-2025/Tests/7717-check-with-window.test
new file mode 100644
--- /dev/null
+++ b/sql/test/BugTracker-2025/Tests/7717-check-with-window.test
@@ -0,0 +1,2 @@
+statement error 42000!STDDEV_SAMP: window function 'stddev_samp' not allowed
in check constraints
+CREATE TABLE v0 ( v1 INT , v2 VARCHAR ( 77 ) NOT NULL CHECK ( CAST (
STDDEV_SAMP ( v1 / -1 ) OVER ( ORDER BY 15933342.000000 DESC ) AS INTEGER )))
diff --git a/sql/test/BugTracker-2025/Tests/All
b/sql/test/BugTracker-2025/Tests/All
--- a/sql/test/BugTracker-2025/Tests/All
+++ b/sql/test/BugTracker-2025/Tests/All
@@ -61,5 +61,6 @@ 7713-crash-groupby-cse
7714-crash-in-window-with-subquery
7715-anti-like
7716-antijoin-not-exists-null
+7717-check-with-window
7730-generate-series-month-int
7732-table-exp-issue
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
