Changeset: 98f0077d3b10 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/98f0077d3b10
Branch: default
Log Message:
merge with literal_features
diffs (truncated from 474 to 300 lines):
diff --git a/sql/ChangeLog b/sql/ChangeLog
--- a/sql/ChangeLog
+++ b/sql/ChangeLog
@@ -1,3 +1,12 @@
# ChangeLog file for sql
# This file is updated with Maddlog
+* Wed Mar 6 2024 Yunus Koning <yunus.kon...@monetdbsolutions.com>
+- SQL2023 feature: Introduce UNIQUE NULLS [NOT] DISTINCT syntax which
+ allows for NULLS to be treated as unique, i.e. a column with this
+ contraint can have one NULL value at most.
+
+- SQL2023 feature: Allow project and ORDER BY expressions on
+ UNIQUE constrained columns when the primary key column is
+ used in a GROUP BY expression.
+
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
@@ -4654,7 +4654,9 @@ insert_check_ukey(backend *be, list *ins
stmt_add_column_predicate(be, c->c);
col = stmt_col(be, c->c, dels, dels->partition);
- if ((k->type == ukey) && stmt_has_null(col)) {
+ if (k->type == unndkey)
+ s = stmt_uselect(be, col, cs,
cmp_equal, s, 0, 1);
+ else if ((k->type == ukey) &&
stmt_has_null(col)) {
stmt *nn = stmt_selectnonil(be, col, s);
s = stmt_uselect(be, col, cs,
cmp_equal, nn, 0, 0);
} else {
@@ -4679,7 +4681,7 @@ insert_check_ukey(backend *be, list *ins
list_append(lje, col);
list_append(rje, cs);
}
- s = releqjoin(be, lje, rje, NULL, 1 /* hash used */, 0,
0);
+ s = releqjoin(be, lje, rje, NULL, 1 /* hash used */, 0,
k->type == unndkey? 1: 0);
s = stmt_result(be, s, 0);
}
s = stmt_binop(be, stmt_aggr(be, s, NULL, NULL, cnt, 1, 0, 1),
stmt_atom_lng(be, 0), NULL, ne);
@@ -4743,12 +4745,12 @@ insert_check_ukey(backend *be, list *ins
s = stmt_project(be, nn, s);
}
if (h->nrcols) {
- s = stmt_join(be, s, h, 0, cmp_equal, 0, 0, false);
+ s = stmt_join(be, s, h, 0, cmp_equal, 0, k->type ==
unndkey? 1: 0, false);
/* s should be empty */
s = stmt_result(be, s, 0);
s = stmt_aggr(be, s, NULL, NULL, cnt, 1, 0, 1);
} else {
- s = stmt_uselect(be, s, h, cmp_equal, NULL, 0, 0);
+ s = stmt_uselect(be, s, h, cmp_equal, NULL, 0, k->type
== unndkey? 1: 0);
/* s should be empty */
s = stmt_aggr(be, s, NULL, NULL, cnt, 1, 0, 1);
}
@@ -4855,7 +4857,7 @@ sql_insert_key(backend *be, list *insert
* insert values
* insert fkey/pkey index
*/
- if (k->type == pkey || k->type == ukey) {
+ if (k->type == pkey || k->type == ukey || k->type == unndkey) {
return insert_check_ukey(be, inserts, k, idx_inserts);
} else { /* foreign keys */
return insert_check_fkey(be, inserts, k, idx_inserts, pin);
diff --git a/sql/include/sql_catalog.h b/sql/include/sql_catalog.h
--- a/sql/include/sql_catalog.h
+++ b/sql/include/sql_catalog.h
@@ -523,8 +523,9 @@ typedef struct sql_subfunc {
typedef enum key_type {
pkey,
- ukey,
- fkey
+ ukey, /* default behavior is that NULLS are distinct, e.g. there can be
multiple null values in a column with regular UNIQUE constraint */
+ fkey,
+ unndkey /* NULLS are not distinct, i.e. NULLS act as regular values for
uniqueness checks */
} key_type;
typedef struct sql_kc {
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
@@ -327,6 +327,9 @@ column_constraint_name(mvc *sql, symbol
case SQL_UNIQUE:
suffix = "unique";
break;
+ case SQL_UNIQUE_NULLS_NOT_DISTINCT:
+ suffix = "nndunique";
+ break;
case SQL_PRIMARY_KEY:
suffix = "pkey";
break;
@@ -368,8 +371,9 @@ column_constraint_type(mvc *sql, const c
}
switch (s->token) {
case SQL_UNIQUE:
+ case SQL_UNIQUE_NULLS_NOT_DISTINCT:
case SQL_PRIMARY_KEY: {
- key_type kt = (s->token == SQL_UNIQUE) ? ukey : pkey;
+ key_type kt = (s->token == SQL_UNIQUE) ? ukey : (s->token ==
SQL_UNIQUE_NULLS_NOT_DISTINCT) ? unndkey : pkey;
sql_key *k;
const char *ns = name;
@@ -832,8 +836,9 @@ table_constraint_type(mvc *sql, const ch
switch (s->token) {
case SQL_UNIQUE:
+ case SQL_UNIQUE_NULLS_NOT_DISTINCT:
case SQL_PRIMARY_KEY: {
- key_type kt = (s->token == SQL_PRIMARY_KEY ? pkey : ukey);
+ key_type kt = (s->token == SQL_PRIMARY_KEY ? pkey : s->token ==
SQL_UNIQUE ? ukey : unndkey);
dnode *nms = s->data.lval->h;
sql_key *k;
const char *ns = name;
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
@@ -1219,6 +1219,70 @@ set_dependent_( sql_rel *r)
set_dependent(r);
}
+static
+sql_rel* find_union(visitor *v, sql_rel *rel) {
+ if (rel->op == op_union)
+ v->data = rel;
+ return rel;
+}
+
+static inline
+bool group_by_pk_project_uk_cond(mvc* sql, sql_rel* inner, sql_exp* exp,const
char* sname, const char* tname) {
+ sql_table* t = find_table_or_view_on_scope(sql, NULL, sname, tname,
"SELECT", false);
+ bool allow = false;
+ if (t) {
+ sql_idx* pki = NULL;
+ list *ukil = sa_list(sql->sa);
+
+ for (node * n = ol_first_node(t->idxs); n; n = n->next) {
+ sql_idx *i = n->data;
+ switch (i->key->type) {
+ case pkey:
+ pki = i;
+ continue;
+ case ukey:
+ case unndkey:
+ list_append(ukil, i);
+ continue;
+ default:
+ continue;
+ }
+ }
+ if (pki && pki->columns->cnt == 1 && ((list*) inner->r)->cnt
== 1) {
+ /* for now only check simple case where primary key and
group by expression is a single column*/
+ sql_exp* gbe = ((list*) inner->r)->h->data;
+ assert(gbe->type == e_column);
+ sql_column* pkc = ((sql_kc *)pki->columns->h->data)->c;
+ if (strcmp(gbe->alias.name, pkc->base.name) == 0) {
+ node *n;
+ for (n = ukil->h; n; n = n->next){
+ sql_idx* uki = n->data;
+ if (uki->columns->cnt == 1) {
+ /* for now only check simple
case where unique key is a single column*/
+ sql_column* ukc = ((sql_kc
*)uki->columns->h->data)->c;
+ if (strcmp(exp->alias.name,
ukc->base.name) == 0) {
+ allow = true;
+ break;
+ }
+ }
+ }
+ }
+ }
+
+ if (allow) {
+ /* sufficiency condition: abort if relation contains
union subrelation
+ * because it may break functional dependency between pk
and uk */
+ visitor v = {.sql=sql};
+ rel_visitor_topdown(&v, inner, &find_union);
+ if (v.data)
+ allow = false;
+ }
+ }
+
+ return allow;
+
+}
+
static sql_exp *
rel_column_ref(sql_query *query, sql_rel **rel, symbol *column_r, int f)
{
@@ -1414,8 +1478,18 @@ rel_column_ref(sql_query *query, sql_rel
}
}
if (!exp) {
- if (inner && !is_sql_aggr(f) && is_groupby(inner->op)
&& inner->l && (exp = rel_bind_column3(sql, inner->l, sname, 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 (inner && !is_sql_aggr(f) && is_groupby(inner->op)
&& inner->l && (exp = rel_bind_column3(sql, inner->l, sname, tname, cname, f)))
{
+ if (group_by_pk_project_uk_cond(sql, inner,
exp, sname, tname)) {
+ /* SQL23 feature: very special case
where primary key is used in GROUP BY expression and
+ * unique key is in the project list or
ORDER BY clause */
+ sql->session->status = 0;
+ sql->errstr[0] = 0;
+ exp->card = CARD_AGGR;
+ list_append(inner->exps, exp);
+ }
+ else
+ 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)
diff --git a/sql/server/sql_parser.y b/sql/server/sql_parser.y
--- a/sql/server/sql_parser.y
+++ b/sql/server/sql_parser.y
@@ -2143,6 +2143,8 @@ column_constraint_type:
NOT sqlNULL { $$ = _symbol_create( SQL_NOT_NULL, NULL); }
| sqlNULL { $$ = _symbol_create( SQL_NULL, NULL); }
| UNIQUE { $$ = _symbol_create( SQL_UNIQUE, NULL ); }
+ | UNIQUE NULLS DISTINCT { $$ = _symbol_create( SQL_UNIQUE, NULL ); }
+ | UNIQUE NULLS NOT DISTINCT { $$ = _symbol_create(
SQL_UNIQUE_NULLS_NOT_DISTINCT, NULL ); }
| PRIMARY KEY { $$ = _symbol_create( SQL_PRIMARY_KEY, NULL ); }
| REFERENCES qname opt_column_list opt_match opt_ref_action
@@ -2159,6 +2161,10 @@ column_constraint_type:
table_constraint_type:
UNIQUE column_commalist_parens
{ $$ = _symbol_create_list( SQL_UNIQUE, $2); }
+ | UNIQUE NULLS DISTINCT column_commalist_parens
+ { $$ = _symbol_create_list( SQL_UNIQUE, $4); }
+ | UNIQUE NULLS NOT DISTINCT column_commalist_parens
+ { $$ = _symbol_create_list(
SQL_UNIQUE_NULLS_NOT_DISTINCT, $5); }
| PRIMARY KEY column_commalist_parens
{ $$ = _symbol_create_list( SQL_PRIMARY_KEY, $3); }
| FOREIGN KEY column_commalist_parens
@@ -7202,6 +7208,7 @@ char *token2string(tokens token)
SQL(TYPE);
SQL(UNION);
SQL(UNIQUE);
+ SQL(UNIQUE_NULLS_NOT_DISTINCT);
SQL(UNOP);
SQL(UPDATE);
SQL(USING);
diff --git a/sql/server/sql_partition.c b/sql/server/sql_partition.c
--- a/sql/server/sql_partition.c
+++ b/sql/server/sql_partition.c
@@ -55,8 +55,8 @@ str
sql_partition_validate_key(mvc *sql, sql_table *nt, sql_key *k, const char* op)
{
if (k->type != fkey) {
- const char *keys = (k->type == pkey) ? "primary" : "unique";
- assert(k->type == pkey || k->type == ukey);
+ const char *keys = (k->type == pkey) ? "primary" : k->type ==
unndkey ? "nndunique" : "unique";
+ assert(k->type == pkey || k->type == ukey || k->type ==
unndkey);
if (isPartitionedByColumnTable(nt)) {
assert(nt->part.pcol);
diff --git a/sql/server/sql_tokens.h b/sql/server/sql_tokens.h
--- a/sql/server/sql_tokens.h
+++ b/sql/server/sql_tokens.h
@@ -155,6 +155,7 @@ typedef enum tokens {
SQL_TYPE,
SQL_UNION,
SQL_UNIQUE,
+ SQL_UNIQUE_NULLS_NOT_DISTINCT,
SQL_UNOP,
SQL_UPDATE,
SQL_USING,
diff --git a/sql/test/2023/Tests/All b/sql/test/2023/Tests/All
--- a/sql/test/2023/Tests/All
+++ b/sql/test/2023/Tests/All
@@ -1,3 +1,4 @@
literals
btrim
any_value
+unique_nulls_distinct
diff --git a/sql/test/2023/Tests/unique_nulls_distinct.test
b/sql/test/2023/Tests/unique_nulls_distinct.test
new file mode 100644
--- /dev/null
+++ b/sql/test/2023/Tests/unique_nulls_distinct.test
@@ -0,0 +1,123 @@
+
+statement ok
+CREATE TABLE und1 (i1 int, i2 int, UNIQUE (i1, i2))
+
+statement ok
+CREATE TABLE und2 (i1 int, i2 int, UNIQUE NULLS DISTINCT (i1, i2))
+
+statement ok
+CREATE TABLE unnd1 (i1 int, i2 int, UNIQUE NULLS NOT DISTINCT (i1, i2))
+
+statement ok
+INSERT INTO und1 VALUES (NULL, 10)
+
+statement ok
+INSERT INTO und1 VALUES (NULL, 10), (NULL, 10)
+
+statement ok
+INSERT INTO und1 VALUES (20, 10)
+
+statement error
+INSERT INTO und1 VALUES (20, 10)
+
+statement error
_______________________________________________
checkin-list mailing list -- checkin-list@monetdb.org
To unsubscribe send an email to checkin-list-le...@monetdb.org
