Changeset: f1b3e33b1168 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/f1b3e33b1168
Modified Files:
sql/backends/monet5/rel_bin.c
sql/include/sql_catalog.h
sql/server/rel_schema.c
sql/server/sql_parser.y
sql/server/sql_partition.c
sql/server/sql_tokens.h
Branch: literal_features
Log Message:
SQL23: introduce UNIQUE NULLS NOT DISTINCT constraint
diffs (138 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
@@ -4643,7 +4643,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 {
@@ -4668,7 +4670,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);
@@ -4732,12 +4734,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);
}
@@ -4844,7 +4846,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
@@ -524,8 +524,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
@@ -366,8 +366,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;
@@ -828,8 +829,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/sql_parser.y b/sql/server/sql_parser.y
--- a/sql/server/sql_parser.y
+++ b/sql/server/sql_parser.y
@@ -2144,6 +2144,7 @@ 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 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
@@ -2160,6 +2161,8 @@ column_constraint_type:
table_constraint_type:
UNIQUE column_commalist_parens
{ $$ = _symbol_create_list( SQL_UNIQUE, $2); }
+ | 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
@@ -7246,6 +7249,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
@@ -54,7 +54,7 @@ sql_partition_validate_key(mvc *sql, sql
{
if (k->type != fkey) {
const char *keys = (k->type == pkey) ? "primary" : "unique";
- assert(k->type == pkey || k->type == ukey);
+ 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
@@ -153,6 +153,7 @@ typedef enum tokens {
SQL_TYPE,
SQL_UNION,
SQL_UNIQUE,
+ SQL_UNIQUE_NULLS_NOT_DISTINCT,
SQL_UNOP,
SQL_UPDATE,
SQL_USING,
_______________________________________________
checkin-list mailing list -- checkin-list@monetdb.org
To unsubscribe send an email to checkin-list-le...@monetdb.org
