Changeset: 73aaa298ce5e for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=73aaa298ce5e
Modified Files:
clients/mapiclient/mhelp.c
sql/backends/monet5/sql_upgrades.c
sql/test/Users/Tests/columnRights.stable.err
sql/test/Users/Tests/columnRights.stable.out
sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.int128
sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.powerpc64.int128
sql/test/emptydb-upgrade/Tests/upgrade.stable.out.int128
sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out.int128
sql/test/testdb-upgrade/Tests/upgrade.stable.out.int128
Branch: default
Log Message:
Merge with Nov2019
diffs (283 lines):
diff --git a/clients/mapiclient/mhelp.c b/clients/mapiclient/mhelp.c
--- a/clients/mapiclient/mhelp.c
+++ b/clients/mapiclient/mhelp.c
@@ -295,7 +295,7 @@ SQLhelp sqlhelp1[] = {
NULL},
{"EXTRACT",
"Built-in function",
- "EXTRACT '(' { YEAR | MONTH | DAY | HOUR | MINUTE | SECOND } FROM
scalar_expression ')'",
+ "EXTRACT '(' { YEAR | MONTH | DAY | HOUR | MINUTE | SECOND | CENTURY |
DECADE | QUARTER | WEEK | DOW | DOY } FROM scalar_expression ')'",
NULL,
NULL},
{"DECLARE",
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
@@ -216,25 +216,31 @@ sql_update_hugeint(Client c, mvc *sql, c
/* 39_analytics_hge.sql */
pos += snprintf(buf + pos, bufsize - pos,
"create aggregate stddev_samp(val HUGEINT) returns
DOUBLE\n"
- "\texternal name \"aggr\".\"stdev\";\n"
+ " external name \"aggr\".\"stdev\";\n"
"GRANT EXECUTE ON AGGREGATE stddev_samp(HUGEINT) TO
PUBLIC;\n"
"create aggregate stddev_pop(val HUGEINT) returns
DOUBLE\n"
- "\texternal name \"aggr\".\"stdevp\";\n"
+ " external name \"aggr\".\"stdevp\";\n"
"GRANT EXECUTE ON AGGREGATE stddev_pop(HUGEINT) TO
PUBLIC;\n"
"create aggregate var_samp(val HUGEINT) returns
DOUBLE\n"
- "\texternal name \"aggr\".\"variance\";\n"
+ " external name \"aggr\".\"variance\";\n"
"GRANT EXECUTE ON AGGREGATE var_samp(HUGEINT) TO
PUBLIC;\n"
"create aggregate var_pop(val HUGEINT) returns DOUBLE\n"
- "\texternal name \"aggr\".\"variancep\";\n"
+ " external name \"aggr\".\"variancep\";\n"
"GRANT EXECUTE ON AGGREGATE var_pop(HUGEINT) TO
PUBLIC;\n"
"create aggregate median(val HUGEINT) returns HUGEINT\n"
- "\texternal name \"aggr\".\"median\";\n"
+ " external name \"aggr\".\"median\";\n"
"GRANT EXECUTE ON AGGREGATE median(HUGEINT) TO
PUBLIC;\n"
"create aggregate quantile(val HUGEINT, q DOUBLE)
returns HUGEINT\n"
- "\texternal name \"aggr\".\"quantile\";\n"
+ " external name \"aggr\".\"quantile\";\n"
"GRANT EXECUTE ON AGGREGATE quantile(HUGEINT, DOUBLE)
TO PUBLIC;\n"
+ "create aggregate median_avg(val HUGEINT) returns
DOUBLE\n"
+ " external name \"aggr\".\"median_avg\";\n"
+ "GRANT EXECUTE ON AGGREGATE median_avg(HUGEINT) TO
PUBLIC;\n"
+ "create aggregate quantile_avg(val HUGEINT, q DOUBLE)
returns DOUBLE\n"
+ " external name \"aggr\".\"quantile_avg\";\n"
+ "GRANT EXECUTE ON AGGREGATE quantile_avg(HUGEINT,
DOUBLE) TO PUBLIC;\n"
"create aggregate corr(e1 HUGEINT, e2 HUGEINT) returns
DOUBLE\n"
- "\texternal name \"aggr\".\"corr\";\n"
+ " external name \"aggr\".\"corr\";\n"
"GRANT EXECUTE ON AGGREGATE corr(HUGEINT, HUGEINT) TO
PUBLIC;\n");
/* 40_json_hge.sql */
@@ -244,7 +250,7 @@ sql_update_hugeint(Client c, mvc *sql, c
"GRANT EXECUTE ON FUNCTION json.filter(json, hugeint)
TO PUBLIC;\n");
pos += snprintf(buf + pos, bufsize - pos,
- "update sys.functions set system = true where name in
('fuse', 'generate_series', 'stddev_samp', 'stddev_pop', 'var_samp', 'var_pop',
'median', 'quantile', 'corr') and schema_id = (select id from sys.schemas where
name = 'sys');\n"
+ "update sys.functions set system = true where name in
('fuse', 'generate_series', 'stddev_samp', 'stddev_pop', 'var_samp', 'var_pop',
'median', 'median_avg', 'quantile', 'quantile_avg', 'corr') and schema_id =
(select id from sys.schemas where name = 'sys');\n"
"update sys.functions set system = true where name =
'filter' and schema_id = (select id from sys.schemas where name = 'json');\n");
pos += snprintf(buf + pos, bufsize - pos, "set schema \"%s\";\n",
prev_schema);
@@ -2306,6 +2312,45 @@ sql_update_nov2019(Client c, mvc *sql, c
return err; /* usually MAL_SUCCEED */
}
+#ifdef HAVE_HGE
+static str
+sql_update_nov2019_sp1_hugeint(Client c, mvc *sql, const char *prev_schema,
bool *systabfixed)
+{
+ size_t bufsize = 1024, pos = 0;
+ char *buf, *err;
+
+ if (!*systabfixed &&
+ (err = sql_fix_system_tables(c, sql, prev_schema)) != NULL)
+ return err;
+ *systabfixed = true;
+
+ if ((buf = GDKmalloc(bufsize)) == NULL)
+ throw(SQL, "sql_update_hugeint", SQLSTATE(HY001)
MAL_MALLOC_FAIL);
+
+ pos += snprintf(buf + pos, bufsize - pos, "set schema \"sys\";\n");
+
+ /* 39_analytics_hge.sql */
+ pos += snprintf(buf + pos, bufsize - pos,
+ "create aggregate median_avg(val HUGEINT) returns
DOUBLE\n"
+ " external name \"aggr\".\"median_avg\";\n"
+ "GRANT EXECUTE ON AGGREGATE median_avg(HUGEINT) TO
PUBLIC;\n"
+ "create aggregate quantile_avg(val HUGEINT, q DOUBLE)
returns DOUBLE\n"
+ " external name \"aggr\".\"quantile_avg\";\n"
+ "GRANT EXECUTE ON AGGREGATE quantile_avg(HUGEINT,
DOUBLE) TO PUBLIC;\n");
+
+ pos += snprintf(buf + pos, bufsize - pos,
+ "update sys.functions set system = true where name in
('median_avg', 'quantile_avg') and schema_id = (select id from sys.schemas
where name = 'sys');\n");
+
+ pos += snprintf(buf + pos, bufsize - pos, "set schema \"%s\";\n",
prev_schema);
+ assert(pos < bufsize);
+
+ printf("Running database upgrade commands:\n%s\n", buf);
+ err = SQLstatementIntern(c, &buf, "update", true, false, NULL);
+ GDKfree(buf);
+ return err; /* usually MAL_SUCCEED */
+}
+#endif
+
static str
sql_update_default(Client c, mvc *sql, const char *prev_schema)
{
@@ -2757,6 +2802,19 @@ SQLupgrades(Client c, mvc *m)
}
}
+#ifdef HAVE_HGE
+ if (!res && have_hge) {
+ sql_find_subtype(&tp, "hugeint", 0, 0);
+ if (!sql_bind_aggr(m->sa, s, "median_avg", &tp)) {
+ if ((err = sql_update_nov2019_sp1_hugeint(c, m,
prev_schema, &systabfixed)) != NULL) {
+ fprintf(stderr, "!%s\n", err);
+ freeException(err);
+ res = -1;
+ }
+ }
+ }
+#endif
+
if (!res && !sql_bind_func(m->sa, s, "suspend_log_flushing", NULL,
NULL, F_PROC)) {
if ((err = sql_update_default(c, m, prev_schema)) != NULL) {
fprintf(stderr, "!%s\n", err);
diff --git a/sql/test/Users/Tests/columnRights.stable.err
b/sql/test/Users/Tests/columnRights.stable.err
--- a/sql/test/Users/Tests/columnRights.stable.err
+++ b/sql/test/Users/Tests/columnRights.stable.err
@@ -30,13 +30,9 @@ stderr of test 'columnRights` in directo
# 11:31:52 > "/usr/bin/python2" "columnRights.SQL.py" "columnRights"
# 11:31:52 >
-MAPI = (alice) /var/tmp/mtest-9087/.s.monetdb.35899
-QUERY = SELECT price FROM orders;
-ERROR = !SELECT: access denied for alice to table 'library.orders'
-CODE = 42000
-MAPI = (alice) /var/tmp/mtest-30274/.s.monetdb.37685
+MAPI = (alice) /var/tmp/mtest-190499/.s.monetdb.35303
QUERY = SELECT name FROM orders; --insufficient rights
-ERROR = !SELECT: access denied for alice to table 'library.orders'
+ERROR = !SELECT: identifier 'name' unknown
CODE = 42000
MAPI = (alice) /var/tmp/mtest-30274/.s.monetdb.37685
QUERY = UPDATE orders SET price = 0; --insufficient rights
diff --git a/sql/test/Users/Tests/columnRights.stable.out
b/sql/test/Users/Tests/columnRights.stable.out
--- a/sql/test/Users/Tests/columnRights.stable.out
+++ b/sql/test/Users/Tests/columnRights.stable.out
@@ -29,6 +29,11 @@ stdout of test 'columnRights` in directo
#GRANT SELECT (price) ON library.orders TO alice;
#GRANT UPDATE (name) ON library.orders TO alice;
+#SELECT price FROM orders;
+% library.orders # table_name
+% price # name
+% int # type
+% 1 # length
#UPDATE orders SET name = 'book title goes here';
[ 0 ]
diff --git a/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.int128
b/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.int128
--- a/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.int128
+++ b/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.int128
@@ -5926,13 +5926,19 @@ GRANT EXECUTE ON AGGREGATE median(HUGEIN
create aggregate quantile(val HUGEINT, q DOUBLE) returns HUGEINT
external name "aggr"."quantile";
GRANT EXECUTE ON AGGREGATE quantile(HUGEINT, 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 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 corr(e1 HUGEINT, e2 HUGEINT) returns DOUBLE
external name "aggr"."corr";
GRANT EXECUTE ON AGGREGATE corr(HUGEINT, HUGEINT) TO PUBLIC;
create function json.filter(js json, name hugeint)
returns json external name json.filter;
GRANT EXECUTE ON FUNCTION json.filter(json, hugeint) TO PUBLIC;
-update sys.functions set system = true where name in ('fuse',
'generate_series', 'stddev_samp', 'stddev_pop', 'var_samp', 'var_pop',
'median', 'quantile', 'corr') and schema_id = (select id from sys.schemas where
name = 'sys');
+update sys.functions set system = true where name in ('fuse',
'generate_series', 'stddev_samp', 'stddev_pop', 'var_samp', 'var_pop',
'median', 'median_avg', 'quantile', 'quantile_avg', 'corr') and schema_id =
(select id from sys.schemas where name = 'sys');
update sys.functions set system = true where name = 'filter' and schema_id =
(select id from sys.schemas where name = 'json');
set schema "sys";
diff --git
a/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.powerpc64.int128
b/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.powerpc64.int128
--- a/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.powerpc64.int128
+++ b/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.powerpc64.int128
@@ -5926,13 +5926,19 @@ GRANT EXECUTE ON AGGREGATE median(HUGEIN
create aggregate quantile(val HUGEINT, q DOUBLE) returns HUGEINT
external name "aggr"."quantile";
GRANT EXECUTE ON AGGREGATE quantile(HUGEINT, 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 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 corr(e1 HUGEINT, e2 HUGEINT) returns DOUBLE
external name "aggr"."corr";
GRANT EXECUTE ON AGGREGATE corr(HUGEINT, HUGEINT) TO PUBLIC;
create function json.filter(js json, name hugeint)
returns json external name json.filter;
GRANT EXECUTE ON FUNCTION json.filter(json, hugeint) TO PUBLIC;
-update sys.functions set system = true where name in ('fuse',
'generate_series', 'stddev_samp', 'stddev_pop', 'var_samp', 'var_pop',
'median', 'quantile', 'corr') and schema_id = (select id from sys.schemas where
name = 'sys');
+update sys.functions set system = true where name in ('fuse',
'generate_series', 'stddev_samp', 'stddev_pop', 'var_samp', 'var_pop',
'median', 'median_avg', 'quantile', 'quantile_avg', 'corr') and schema_id =
(select id from sys.schemas where name = 'sys');
update sys.functions set system = true where name = 'filter' and schema_id =
(select id from sys.schemas where name = 'json');
set schema "sys";
diff --git a/sql/test/emptydb-upgrade/Tests/upgrade.stable.out.int128
b/sql/test/emptydb-upgrade/Tests/upgrade.stable.out.int128
--- a/sql/test/emptydb-upgrade/Tests/upgrade.stable.out.int128
+++ b/sql/test/emptydb-upgrade/Tests/upgrade.stable.out.int128
@@ -5926,13 +5926,19 @@ GRANT EXECUTE ON AGGREGATE median(HUGEIN
create aggregate quantile(val HUGEINT, q DOUBLE) returns HUGEINT
external name "aggr"."quantile";
GRANT EXECUTE ON AGGREGATE quantile(HUGEINT, 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 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 corr(e1 HUGEINT, e2 HUGEINT) returns DOUBLE
external name "aggr"."corr";
GRANT EXECUTE ON AGGREGATE corr(HUGEINT, HUGEINT) TO PUBLIC;
create function json.filter(js json, name hugeint)
returns json external name json.filter;
GRANT EXECUTE ON FUNCTION json.filter(json, hugeint) TO PUBLIC;
-update sys.functions set system = true where name in ('fuse',
'generate_series', 'stddev_samp', 'stddev_pop', 'var_samp', 'var_pop',
'median', 'quantile', 'corr') and schema_id = (select id from sys.schemas where
name = 'sys');
+update sys.functions set system = true where name in ('fuse',
'generate_series', 'stddev_samp', 'stddev_pop', 'var_samp', 'var_pop',
'median', 'median_avg', 'quantile', 'quantile_avg', 'corr') and schema_id =
(select id from sys.schemas where name = 'sys');
update sys.functions set system = true where name = 'filter' and schema_id =
(select id from sys.schemas where name = 'json');
set schema "sys";
diff --git a/sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out.int128
b/sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out.int128
--- a/sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out.int128
+++ b/sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out.int128
@@ -5925,13 +5925,19 @@ GRANT EXECUTE ON AGGREGATE median(HUGEIN
create aggregate quantile(val HUGEINT, q DOUBLE) returns HUGEINT
external name "aggr"."quantile";
GRANT EXECUTE ON AGGREGATE quantile(HUGEINT, 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 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 corr(e1 HUGEINT, e2 HUGEINT) returns DOUBLE
external name "aggr"."corr";
GRANT EXECUTE ON AGGREGATE corr(HUGEINT, HUGEINT) TO PUBLIC;
create function json.filter(js json, name hugeint)
returns json external name json.filter;
GRANT EXECUTE ON FUNCTION json.filter(json, hugeint) TO PUBLIC;
-update sys.functions set system = true where name in ('fuse',
'generate_series', 'stddev_samp', 'stddev_pop', 'var_samp', 'var_pop',
'median', 'quantile', 'corr') and schema_id = (select id from sys.schemas where
name = 'sys');
+update sys.functions set system = true where name in ('fuse',
'generate_series', 'stddev_samp', 'stddev_pop', 'var_samp', 'var_pop',
'median', 'median_avg', 'quantile', 'quantile_avg', 'corr') and schema_id =
(select id from sys.schemas where name = 'sys');
update sys.functions set system = true where name = 'filter' and schema_id =
(select id from sys.schemas where name = 'json');
set schema "sys";
diff --git a/sql/test/testdb-upgrade/Tests/upgrade.stable.out.int128
b/sql/test/testdb-upgrade/Tests/upgrade.stable.out.int128
--- a/sql/test/testdb-upgrade/Tests/upgrade.stable.out.int128
+++ b/sql/test/testdb-upgrade/Tests/upgrade.stable.out.int128
@@ -5926,13 +5926,19 @@ GRANT EXECUTE ON AGGREGATE median(HUGEIN
create aggregate quantile(val HUGEINT, q DOUBLE) returns HUGEINT
external name "aggr"."quantile";
GRANT EXECUTE ON AGGREGATE quantile(HUGEINT, 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 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 corr(e1 HUGEINT, e2 HUGEINT) returns DOUBLE
external name "aggr"."corr";
GRANT EXECUTE ON AGGREGATE corr(HUGEINT, HUGEINT) TO PUBLIC;
create function json.filter(js json, name hugeint)
returns json external name json.filter;
GRANT EXECUTE ON FUNCTION json.filter(json, hugeint) TO PUBLIC;
-update sys.functions set system = true where name in ('fuse',
'generate_series', 'stddev_samp', 'stddev_pop', 'var_samp', 'var_pop',
'median', 'quantile', 'corr') and schema_id = (select id from sys.schemas where
name = 'sys');
+update sys.functions set system = true where name in ('fuse',
'generate_series', 'stddev_samp', 'stddev_pop', 'var_samp', 'var_pop',
'median', 'median_avg', 'quantile', 'quantile_avg', 'corr') and schema_id =
(select id from sys.schemas where name = 'sys');
update sys.functions set system = true where name = 'filter' and schema_id =
(select id from sys.schemas where name = 'json');
set schema "sys";
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
