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 checkin-list@monetdb.org https://www.monetdb.org/mailman/listinfo/checkin-list