Changeset: de7d55f07cb0 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/de7d55f07cb0
Branch: ascii-flag
Log Message:
Merge with default branch.
diffs (truncated from 2888 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/backends/monet5/sql_upgrades.c
b/sql/backends/monet5/sql_upgrades.c
--- a/sql/backends/monet5/sql_upgrades.c
+++ b/sql/backends/monet5/sql_upgrades.c
@@ -6625,6 +6625,78 @@ sql_update_default(Client c, mvc *sql, s
"update sys.args set type_digits = 63 where
type = 'bigint' and type_digits <> 63;\n"
"update sys.args set type_digits = 127 where
type = 'hugeint' and type_digits <> 127;\n"
"update sys.args set type = 'varchar' where
type in ('clob', 'char');\n"
+ "drop aggregate median(decimal);\n"
+ "drop aggregate median_avg(decimal);\n"
+ "drop aggregate quantile(decimal, double);\n"
+ "drop aggregate quantile_avg(decimal,
double);\n"
+ "create aggregate median(val DECIMAL(2))
returns DECIMAL(2)\n"
+ " external name \"aggr\".\"median\";\n"
+ "GRANT EXECUTE ON AGGREGATE median(DECIMAL(2))
TO PUBLIC;\n"
+ "create aggregate median(val DECIMAL(4))
returns DECIMAL(4)\n"
+ " external name \"aggr\".\"median\";\n"
+ "GRANT EXECUTE ON AGGREGATE median(DECIMAL(4))
TO PUBLIC;\n"
+ "create aggregate median(val DECIMAL(9))
returns DECIMAL(9)\n"
+ " external name \"aggr\".\"median\";\n"
+ "GRANT EXECUTE ON AGGREGATE median(DECIMAL(9))
TO PUBLIC;\n"
+ "create aggregate median(val DECIMAL(18))
returns DECIMAL(18)\n"
+ " external name \"aggr\".\"median\";\n"
+ "GRANT EXECUTE ON AGGREGATE median(DECIMAL(18))
TO PUBLIC;\n"
+#ifdef HAVE_HGE
+ "create aggregate median(val DECIMAL(38))
returns DECIMAL(38)\n"
+ " external name \"aggr\".\"median\";\n"
+ "GRANT EXECUTE ON AGGREGATE median(DECIMAL(38))
TO PUBLIC;\n"
+#endif
+ "create aggregate median_avg(val DECIMAL(2))
returns DOUBLE\n"
+ " external name \"aggr\".\"median_avg\";\n"
+ "GRANT EXECUTE ON AGGREGATE
median_avg(DECIMAL(2)) TO PUBLIC;\n"
+ "create aggregate median_avg(val DECIMAL(4))
returns DOUBLE\n"
+ " external name \"aggr\".\"median_avg\";\n"
+ "GRANT EXECUTE ON AGGREGATE
median_avg(DECIMAL(4)) TO PUBLIC;\n"
+ "create aggregate median_avg(val DECIMAL(9))
returns DOUBLE\n"
+ " external name \"aggr\".\"median_avg\";\n"
+ "GRANT EXECUTE ON AGGREGATE
median_avg(DECIMAL(9)) TO PUBLIC;\n"
+ "create aggregate median_avg(val DECIMAL(18))
returns DOUBLE\n"
+ " external name \"aggr\".\"median_avg\";\n"
+ "GRANT EXECUTE ON AGGREGATE
median_avg(DECIMAL(18)) TO PUBLIC;\n"
+#ifdef HAVE_HGE
+ "create aggregate median_avg(val DECIMAL(38))
returns DOUBLE\n"
+ " external name \"aggr\".\"median_avg\";\n"
+ "GRANT EXECUTE ON AGGREGATE
median_avg(DECIMAL(38)) TO PUBLIC;\n"
+#endif
+ "create aggregate quantile(val DECIMAL(2), q
DOUBLE) returns DECIMAL(2)\n"
+ " external name \"aggr\".\"quantile\";\n"
+ "GRANT EXECUTE ON AGGREGATE
quantile(DECIMAL(2), DOUBLE) TO PUBLIC;\n"
+ "create aggregate quantile(val DECIMAL(4), q
DOUBLE) returns DECIMAL(4)\n"
+ " external name \"aggr\".\"quantile\";\n"
+ "GRANT EXECUTE ON AGGREGATE
quantile(DECIMAL(4), DOUBLE) TO PUBLIC;\n"
+ "create aggregate quantile(val DECIMAL(9), q
DOUBLE) returns DECIMAL(9)\n"
+ " external name \"aggr\".\"quantile\";\n"
+ "GRANT EXECUTE ON AGGREGATE
quantile(DECIMAL(9), DOUBLE) TO PUBLIC;\n"
+ "create aggregate quantile(val DECIMAL(18), q
DOUBLE) returns DECIMAL(18)\n"
+ " external name \"aggr\".\"quantile\";\n"
+ "GRANT EXECUTE ON AGGREGATE
quantile(DECIMAL(18), DOUBLE) TO PUBLIC;\n"
+#ifdef HAVE_HGE
+ "create aggregate quantile(val DECIMAL(38), q
DOUBLE) returns DECIMAL(38)\n"
+ " external name \"aggr\".\"quantile\";\n"
+ "GRANT EXECUTE ON AGGREGATE
quantile(DECIMAL(38), DOUBLE) TO PUBLIC;\n"
+#endif
+ "create aggregate quantile_avg(val DECIMAL(2),
q DOUBLE) returns DOUBLE\n"
+ " external name \"aggr\".\"quantile_avg\";\n"
+ "GRANT EXECUTE ON AGGREGATE
quantile_avg(DECIMAL(2), DOUBLE) TO PUBLIC;\n"
+ "create aggregate quantile_avg(val DECIMAL(4),
q DOUBLE) returns DOUBLE\n"
+ " external name \"aggr\".\"quantile_avg\";\n"
+ "GRANT EXECUTE ON AGGREGATE
quantile_avg(DECIMAL(4), DOUBLE) TO PUBLIC;\n"
+ "create aggregate quantile_avg(val DECIMAL(9),
q DOUBLE) returns DOUBLE\n"
+ " external name \"aggr\".\"quantile_avg\";\n"
+ "GRANT EXECUTE ON AGGREGATE
quantile_avg(DECIMAL(9), DOUBLE) TO PUBLIC;\n"
+ "create aggregate quantile_avg(val DECIMAL(18),
q DOUBLE) returns DOUBLE\n"
+ " external name \"aggr\".\"quantile_avg\";\n"
+ "GRANT EXECUTE ON AGGREGATE
quantile_avg(DECIMAL(18), DOUBLE) TO PUBLIC;\n"
+#ifdef HAVE_HGE
+ "create aggregate quantile_avg(val DECIMAL(38),
q DOUBLE) returns DOUBLE\n"
+ " external name \"aggr\".\"quantile_avg\";\n"
+ "GRANT EXECUTE ON AGGREGATE
quantile_avg(DECIMAL(38), DOUBLE) TO PUBLIC;\n"
+#endif
"drop function if exists sys.time_to_str(time
with time zone, string) cascade;\n"
"drop function if exists
sys.timestamp_to_str(timestamp with time zone, string) cascade;\n"
"create function time_to_str(d time, format
string) returns string\n"
@@ -6636,7 +6708,7 @@ sql_update_default(Client c, mvc *sql, s
"grant execute on function time_to_str(time,
string) to public;\n"
"grant execute on function time_to_str(time
with time zone, string) to public;\n"
"grant execute on function
timestamp_to_str(timestamp with time zone, string) to public;\n"
- "update sys.functions set system = true where
not system and schema_id = 2000 and name in ('time_to_str',
'timestamp_to_str');\n"
+ "update sys.functions set system = true where
not system and schema_id = 2000 and name in ('time_to_str', 'timestamp_to_str',
'median', 'median_avg', 'quantile', 'quantile_avg');\n"
"drop function if exists
sys.dump_database(boolean) cascade;\n"
"drop view sys.dump_comments;\n"
"drop view sys.dump_tables;\n"
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/scripts/39_analytics.sql b/sql/scripts/39_analytics.sql
--- a/sql/scripts/39_analytics.sql
+++ b/sql/scripts/39_analytics.sql
@@ -254,9 +254,18 @@ GRANT EXECUTE ON AGGREGATE median(INTEGE
create aggregate median(val BIGINT) returns BIGINT
external name "aggr"."median";
GRANT EXECUTE ON AGGREGATE median(BIGINT) TO PUBLIC;
-create aggregate median(val DECIMAL) returns DECIMAL
+create aggregate median(val DECIMAL(2)) returns DECIMAL(2)
+ external name "aggr"."median";
+GRANT EXECUTE ON AGGREGATE median(DECIMAL(2)) TO PUBLIC;
+create aggregate median(val DECIMAL(4)) returns DECIMAL(4)
external name "aggr"."median";
-GRANT EXECUTE ON AGGREGATE median(DECIMAL) TO PUBLIC;
+GRANT EXECUTE ON AGGREGATE median(DECIMAL(4)) TO PUBLIC;
+create aggregate median(val DECIMAL(9)) returns DECIMAL(9)
+ external name "aggr"."median";
+GRANT EXECUTE ON AGGREGATE median(DECIMAL(9)) TO PUBLIC;
+create aggregate median(val DECIMAL(18)) returns DECIMAL(18)
+ external name "aggr"."median";
+GRANT EXECUTE ON AGGREGATE median(DECIMAL(18)) TO PUBLIC;
create aggregate median(val REAL) returns REAL
external name "aggr"."median";
GRANT EXECUTE ON AGGREGATE median(REAL) TO PUBLIC;
@@ -296,9 +305,18 @@ GRANT EXECUTE ON AGGREGATE quantile(INTE
create aggregate quantile(val BIGINT, q DOUBLE) returns BIGINT
external name "aggr"."quantile";
GRANT EXECUTE ON AGGREGATE quantile(BIGINT, DOUBLE) TO PUBLIC;
-create aggregate quantile(val DECIMAL, q DOUBLE) returns DECIMAL
+create aggregate quantile(val DECIMAL(2), q DOUBLE) returns DECIMAL(2)
+ external name "aggr"."quantile";
+GRANT EXECUTE ON AGGREGATE quantile(DECIMAL(2), DOUBLE) TO PUBLIC;
+create aggregate quantile(val DECIMAL(4), q DOUBLE) returns DECIMAL(4)
external name "aggr"."quantile";
-GRANT EXECUTE ON AGGREGATE quantile(DECIMAL, DOUBLE) TO PUBLIC;
+GRANT EXECUTE ON AGGREGATE quantile(DECIMAL(4), DOUBLE) TO PUBLIC;
+create aggregate quantile(val DECIMAL(9), q DOUBLE) returns DECIMAL(9)
+ external name "aggr"."quantile";
+GRANT EXECUTE ON AGGREGATE quantile(DECIMAL(9), DOUBLE) TO PUBLIC;
+create aggregate quantile(val DECIMAL(18), q DOUBLE) returns DECIMAL(18)
+ external name "aggr"."quantile";
+GRANT EXECUTE ON AGGREGATE quantile(DECIMAL(18), DOUBLE) TO PUBLIC;
create aggregate quantile(val REAL, q DOUBLE) returns REAL
external name "aggr"."quantile";
GRANT EXECUTE ON AGGREGATE quantile(REAL, DOUBLE) TO PUBLIC;
@@ -338,9 +356,18 @@ GRANT EXECUTE ON AGGREGATE median_avg(IN
create aggregate median_avg(val BIGINT) returns DOUBLE
external name "aggr"."median_avg";
GRANT EXECUTE ON AGGREGATE median_avg(BIGINT) TO PUBLIC;
-create aggregate median_avg(val DECIMAL) returns DOUBLE
+create aggregate median_avg(val DECIMAL(2)) returns DOUBLE
+ external name "aggr"."median_avg";
+GRANT EXECUTE ON AGGREGATE median_avg(DECIMAL(2)) TO PUBLIC;
+create aggregate median_avg(val DECIMAL(4)) returns DOUBLE
external name "aggr"."median_avg";
-GRANT EXECUTE ON AGGREGATE median_avg(DECIMAL) TO PUBLIC;
+GRANT EXECUTE ON AGGREGATE median_avg(DECIMAL(4)) TO PUBLIC;
+create aggregate median_avg(val DECIMAL(9)) returns DOUBLE
+ external name "aggr"."median_avg";
+GRANT EXECUTE ON AGGREGATE median_avg(DECIMAL(9)) TO PUBLIC;
+create aggregate median_avg(val DECIMAL(18)) returns DOUBLE
+ external name "aggr"."median_avg";
+GRANT EXECUTE ON AGGREGATE median_avg(DECIMAL(18)) TO PUBLIC;
create aggregate median_avg(val REAL) returns DOUBLE
external name "aggr"."median_avg";
GRANT EXECUTE ON AGGREGATE median_avg(REAL) TO PUBLIC;
@@ -361,9 +388,18 @@ GRANT EXECUTE ON AGGREGATE quantile_avg(
create aggregate quantile_avg(val BIGINT, q DOUBLE) returns DOUBLE
external name "aggr"."quantile_avg";
GRANT EXECUTE ON AGGREGATE quantile_avg(BIGINT, DOUBLE) TO PUBLIC;
-create aggregate quantile_avg(val DECIMAL, q DOUBLE) returns DOUBLE
+create aggregate quantile_avg(val DECIMAL(2), q DOUBLE) returns DOUBLE
+ external name "aggr"."quantile_avg";
+GRANT EXECUTE ON AGGREGATE quantile_avg(DECIMAL(2), DOUBLE) TO PUBLIC;
+create aggregate quantile_avg(val DECIMAL(4), q DOUBLE) returns DOUBLE
external name "aggr"."quantile_avg";
-GRANT EXECUTE ON AGGREGATE quantile_avg(DECIMAL, DOUBLE) TO PUBLIC;
+GRANT EXECUTE ON AGGREGATE quantile_avg(DECIMAL(4), DOUBLE) TO PUBLIC;
+create aggregate quantile_avg(val DECIMAL(9), q DOUBLE) returns DOUBLE
+ external name "aggr"."quantile_avg";
+GRANT EXECUTE ON AGGREGATE quantile_avg(DECIMAL(9), DOUBLE) TO PUBLIC;
+create aggregate quantile_avg(val DECIMAL(18), q DOUBLE) returns DOUBLE
+ external name "aggr"."quantile_avg";
+GRANT EXECUTE ON AGGREGATE quantile_avg(DECIMAL(18), DOUBLE) TO PUBLIC;
create aggregate quantile_avg(val REAL, q DOUBLE) returns DOUBLE
external name "aggr"."quantile_avg";
GRANT EXECUTE ON AGGREGATE quantile_avg(REAL, DOUBLE) TO PUBLIC;
diff --git a/sql/scripts/39_analytics_hge.sql b/sql/scripts/39_analytics_hge.sql
--- a/sql/scripts/39_analytics_hge.sql
+++ b/sql/scripts/39_analytics_hge.sql
@@ -54,18 +54,34 @@ create aggregate median(val HUGEINT) ret
external name "aggr"."median";
GRANT EXECUTE ON AGGREGATE median(HUGEINT) TO PUBLIC;
+create aggregate median(val DECIMAL(38)) returns DECIMAL(38)
+ external name "aggr"."median";
+GRANT EXECUTE ON AGGREGATE median(DECIMAL(38)) TO PUBLIC;
+
create aggregate quantile(val HUGEINT, q DOUBLE) returns HUGEINT
external name "aggr"."quantile";
GRANT EXECUTE ON AGGREGATE quantile(HUGEINT, DOUBLE) TO PUBLIC;
+create aggregate quantile(val DECIMAL(38), q DOUBLE) returns DECIMAL(38)
+ external name "aggr"."quantile";
+GRANT EXECUTE ON AGGREGATE quantile(DECIMAL(38), DOUBLE) TO PUBLIC;
+
create aggregate median_avg(val HUGEINT) returns DOUBLE
external name "aggr"."median_avg";
GRANT EXECUTE ON AGGREGATE median_avg(HUGEINT) TO PUBLIC;
+create aggregate median_avg(val DECIMAL(38)) returns DOUBLE
+ external name "aggr"."median_avg";
+GRANT EXECUTE ON AGGREGATE median_avg(DECIMAL(38)) TO PUBLIC;
+
create aggregate quantile_avg(val HUGEINT, q DOUBLE) returns DOUBLE
external name "aggr"."quantile_avg";
GRANT EXECUTE ON AGGREGATE quantile_avg(HUGEINT, DOUBLE) TO PUBLIC;
+create aggregate quantile_avg(val DECIMAL(38), q DOUBLE) returns DOUBLE
+ external name "aggr"."quantile_avg";
+GRANT EXECUTE ON AGGREGATE quantile_avg(DECIMAL(38), DOUBLE) TO PUBLIC;
+
create aggregate corr(e1 HUGEINT, e2 HUGEINT) returns DOUBLE
external name "aggr"."corr";
GRANT EXECUTE ON AGGREGATE corr(HUGEINT, HUGEINT) TO PUBLIC;
diff --git a/sql/server/rel_exp.c b/sql/server/rel_exp.c
--- a/sql/server/rel_exp.c
+++ b/sql/server/rel_exp.c
@@ -1849,7 +1849,7 @@ exp_two_sided_bound_cmp_exp_is_false(sql
sql_exp* h = e->f;
assert (v && l && h);
_______________________________________________
checkin-list mailing list -- checkin-list@monetdb.org
To unsubscribe send an email to checkin-list-le...@monetdb.org
