Changeset: 61dbcc73e179 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=61dbcc73e179
Modified Files:
sql/backends/monet5/sql_upgrades.c
sql/scripts/39_analytics.sql
sql/test/sys-schema/Tests/systemfunctions.stable.out
sql/test/sys-schema/Tests/systemfunctions.stable.out.int128
Branch: typing
Log Message:
Cleaning my own mess. The statistical functions on intervals should't exist
except for median and quantile. Added missing versions of median and quantile
for day interval
diffs (truncated from 566 to 300 lines):
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
@@ -2333,7 +2333,7 @@ sql_update_oscar(Client c, mvc *sql, con
static str
sql_update_default(Client c, mvc *sql, const char *prev_schema, bool
*systabfixed)
{
- size_t bufsize = 3000, pos = 0;
+ size_t bufsize = 4096, pos = 0;
char *buf, *err;
sql_schema *s = mvc_bind_schema(sql, "sys");
sql_table *t;
@@ -2381,6 +2381,45 @@ sql_update_default(Client c, mvc *sql, c
"UPDATE sys._tables SET
system = true WHERE name = 'var_values' AND schema_id = (SELECT id FROM
sys.schemas WHERE name = 'sys');\n"
"GRANT SELECT ON
sys.var_values TO PUBLIC;\n");
+ /* WARNING this upgrade is related to the typing
branch, which I hope it will be merged into default before the next feature
release */
+ /* 39_analytics.sql */
+ pos += snprintf(buf + pos, bufsize - pos,
+ "DROP AGGREGATE
stddev_samp(INTERVAL SECOND);\n"
+ "DROP AGGREGATE
stddev_samp(INTERVAL MONTH);\n"
+ "DROP WINDOW
stddev_samp(INTERVAL SECOND);\n"
+ "DROP WINDOW
stddev_samp(INTERVAL MONTH);\n"
+ "DROP AGGREGATE
stddev_pop(INTERVAL SECOND);\n"
+ "DROP AGGREGATE
stddev_pop(INTERVAL MONTH);\n"
+ "DROP WINDOW
stddev_pop(INTERVAL SECOND);\n"
+ "DROP WINDOW
stddev_pop(INTERVAL MONTH);\n"
+ "DROP AGGREGATE
var_samp(INTERVAL SECOND);\n"
+ "DROP AGGREGATE
var_samp(INTERVAL MONTH);\n"
+ "DROP WINDOW
var_samp(INTERVAL SECOND);\n"
+ "DROP WINDOW
var_samp(INTERVAL MONTH);\n"
+ "DROP AGGREGATE
var_pop(INTERVAL SECOND);\n"
+ "DROP AGGREGATE
var_pop(INTERVAL MONTH);\n"
+ "DROP WINDOW
var_pop(INTERVAL SECOND);\n"
+ "DROP WINDOW
var_pop(INTERVAL MONTH);\n"
+ "DROP AGGREGATE
covar_samp(INTERVAL SECOND,INTERVAL SECOND);\n"
+ "DROP AGGREGATE
covar_samp(INTERVAL MONTH,INTERVAL MONTH);\n"
+ "DROP WINDOW
covar_samp(INTERVAL SECOND,INTERVAL SECOND);\n"
+ "DROP WINDOW
covar_samp(INTERVAL MONTH,INTERVAL MONTH);\n"
+ "DROP AGGREGATE
covar_pop(INTERVAL SECOND,INTERVAL SECOND);\n"
+ "DROP AGGREGATE
covar_pop(INTERVAL MONTH,INTERVAL MONTH);\n"
+ "DROP WINDOW
covar_pop(INTERVAL SECOND,INTERVAL SECOND);\n"
+ "DROP WINDOW
covar_pop(INTERVAL MONTH,INTERVAL MONTH);\n"
+ "DROP AGGREGATE
corr(INTERVAL SECOND,INTERVAL SECOND);\n"
+ "DROP AGGREGATE
corr(INTERVAL MONTH,INTERVAL MONTH);\n"
+ "DROP WINDOW
corr(INTERVAL SECOND,INTERVAL SECOND);\n"
+ "DROP WINDOW
corr(INTERVAL MONTH,INTERVAL MONTH);\n"
+ "\n"
+ "create aggregate
median(val INTERVAL DAY) returns INTERVAL DAY\n"
+ " external name
\"aggr\".\"median\";\n"
+ "GRANT EXECUTE ON
AGGREGATE median(INTERVAL DAY) TO PUBLIC;\n"
+ "create aggregate
quantile(val INTERVAL DAY, q DOUBLE) returns INTERVAL DAY\n"
+ " external name
\"aggr\".\"quantile\";\n"
+ "GRANT EXECUTE ON
AGGREGATE quantile(INTERVAL DAY, DOUBLE) TO PUBLIC;\n");
+
pos += snprintf(buf + pos, bufsize - pos, "set schema
\"%s\";\n", prev_schema);
assert(pos < bufsize);
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
@@ -23,13 +23,6 @@ create aggregate stddev_samp(val DOUBLE)
external name "aggr"."stdev";
GRANT EXECUTE ON AGGREGATE stddev_samp(DOUBLE) TO PUBLIC;
-create aggregate stddev_samp(val INTERVAL SECOND) returns DOUBLE
- external name "aggr"."stdev";
-GRANT EXECUTE ON AGGREGATE stddev_samp(INTERVAL SECOND) TO PUBLIC;
-create aggregate stddev_samp(val INTERVAL MONTH) returns DOUBLE
- external name "aggr"."stdev";
-GRANT EXECUTE ON AGGREGATE stddev_samp(INTERVAL MONTH) TO PUBLIC;
-
create window stddev_samp(val TINYINT) returns DOUBLE
external name "sql"."stdev";
GRANT EXECUTE ON WINDOW stddev_samp(TINYINT) TO PUBLIC;
@@ -49,13 +42,6 @@ create window stddev_samp(val DOUBLE) re
external name "sql"."stdev";
GRANT EXECUTE ON WINDOW stddev_samp(DOUBLE) TO PUBLIC;
-create window stddev_samp(val INTERVAL SECOND) returns DOUBLE
- external name "sql"."stdev";
-GRANT EXECUTE ON WINDOW stddev_samp(INTERVAL SECOND) TO PUBLIC;
-create window stddev_samp(val INTERVAL MONTH) returns DOUBLE
- external name "sql"."stdev";
-GRANT EXECUTE ON WINDOW stddev_samp(INTERVAL MONTH) TO PUBLIC;
-
create aggregate stddev_pop(val TINYINT) returns DOUBLE
external name "aggr"."stdevp";
@@ -76,13 +62,6 @@ create aggregate stddev_pop(val DOUBLE)
external name "aggr"."stdevp";
GRANT EXECUTE ON AGGREGATE stddev_pop(DOUBLE) TO PUBLIC;
-create aggregate stddev_pop(val INTERVAL SECOND) returns DOUBLE
- external name "aggr"."stdevp";
-GRANT EXECUTE ON AGGREGATE stddev_pop(INTERVAL SECOND) TO PUBLIC;
-create aggregate stddev_pop(val INTERVAL MONTH) returns DOUBLE
- external name "aggr"."stdevp";
-GRANT EXECUTE ON AGGREGATE stddev_pop(INTERVAL MONTH) TO PUBLIC;
-
create window stddev_pop(val TINYINT) returns DOUBLE
external name "sql"."stdevp";
GRANT EXECUTE ON WINDOW stddev_pop(TINYINT) TO PUBLIC;
@@ -102,13 +81,6 @@ create window stddev_pop(val DOUBLE) ret
external name "sql"."stdevp";
GRANT EXECUTE ON WINDOW stddev_pop(DOUBLE) TO PUBLIC;
-create window stddev_pop(val INTERVAL SECOND) returns DOUBLE
- external name "sql"."stdevp";
-GRANT EXECUTE ON WINDOW stddev_pop(INTERVAL SECOND) TO PUBLIC;
-create window stddev_pop(val INTERVAL MONTH) returns DOUBLE
- external name "sql"."stdevp";
-GRANT EXECUTE ON WINDOW stddev_pop(INTERVAL MONTH) TO PUBLIC;
-
create aggregate var_samp(val TINYINT) returns DOUBLE
external name "aggr"."variance";
@@ -129,13 +101,6 @@ create aggregate var_samp(val DOUBLE) re
external name "aggr"."variance";
GRANT EXECUTE ON AGGREGATE var_samp(DOUBLE) TO PUBLIC;
-create aggregate var_samp(val INTERVAL SECOND) returns DOUBLE
- external name "aggr"."variance";
-GRANT EXECUTE ON AGGREGATE var_samp(INTERVAL SECOND) TO PUBLIC;
-create aggregate var_samp(val INTERVAL MONTH) returns DOUBLE
- external name "aggr"."variance";
-GRANT EXECUTE ON AGGREGATE var_samp(INTERVAL MONTH) TO PUBLIC;
-
create window var_samp(val TINYINT) returns DOUBLE
external name "sql"."variance";
GRANT EXECUTE ON WINDOW var_samp(TINYINT) TO PUBLIC;
@@ -155,13 +120,6 @@ create window var_samp(val DOUBLE) retur
external name "sql"."variance";
GRANT EXECUTE ON WINDOW var_samp(DOUBLE) TO PUBLIC;
-create window var_samp(val INTERVAL SECOND) returns DOUBLE
- external name "sql"."variance";
-GRANT EXECUTE ON WINDOW var_samp(INTERVAL SECOND) TO PUBLIC;
-create window var_samp(val INTERVAL MONTH) returns DOUBLE
- external name "sql"."variance";
-GRANT EXECUTE ON WINDOW var_samp(INTERVAL MONTH) TO PUBLIC;
-
create aggregate var_pop(val TINYINT) returns DOUBLE
external name "aggr"."variancep";
@@ -181,12 +139,6 @@ GRANT EXECUTE ON AGGREGATE var_pop(REAL)
create aggregate var_pop(val DOUBLE) returns DOUBLE
external name "aggr"."variancep";
GRANT EXECUTE ON AGGREGATE var_pop(DOUBLE) TO PUBLIC;
-create aggregate var_pop(val INTERVAL SECOND) returns DOUBLE
- external name "aggr"."variancep";
-GRANT EXECUTE ON AGGREGATE var_pop(INTERVAL SECOND) TO PUBLIC;
-create aggregate var_pop(val INTERVAL MONTH) returns DOUBLE
- external name "aggr"."variancep";
-GRANT EXECUTE ON AGGREGATE var_pop(INTERVAL MONTH) TO PUBLIC;
create window var_pop(val TINYINT) returns DOUBLE
external name "sql"."variancep";
@@ -207,13 +159,6 @@ create window var_pop(val DOUBLE) return
external name "sql"."variancep";
GRANT EXECUTE ON WINDOW var_pop(DOUBLE) TO PUBLIC;
-create window var_pop(val INTERVAL SECOND) returns DOUBLE
- external name "sql"."variancep";
-GRANT EXECUTE ON WINDOW var_pop(INTERVAL SECOND) TO PUBLIC;
-create window var_pop(val INTERVAL MONTH) returns DOUBLE
- external name "sql"."variancep";
-GRANT EXECUTE ON WINDOW var_pop(INTERVAL MONTH) TO PUBLIC;
-
create aggregate covar_samp(e1 TINYINT, e2 TINYINT) returns DOUBLE
external name "aggr"."covariance";
@@ -234,13 +179,6 @@ create aggregate covar_samp(e1 DOUBLE, e
external name "aggr"."covariance";
GRANT EXECUTE ON AGGREGATE covar_samp(DOUBLE, DOUBLE) TO PUBLIC;
-create aggregate covar_samp(e1 INTERVAL SECOND, e2 INTERVAL SECOND) returns
DOUBLE
- external name "aggr"."covariance";
-GRANT EXECUTE ON AGGREGATE covar_samp(INTERVAL SECOND, INTERVAL SECOND) TO
PUBLIC;
-create aggregate covar_samp(e1 INTERVAL MONTH, e2 INTERVAL MONTH) returns
DOUBLE
- external name "aggr"."covariance";
-GRANT EXECUTE ON AGGREGATE covar_samp(INTERVAL MONTH, INTERVAL MONTH) TO
PUBLIC;
-
create window covar_samp(e1 TINYINT, e2 TINYINT) returns DOUBLE
external name "sql"."covariance";
GRANT EXECUTE ON WINDOW covar_samp(TINYINT, TINYINT) TO PUBLIC;
@@ -260,13 +198,6 @@ create window covar_samp(e1 DOUBLE, e2 D
external name "sql"."covariance";
GRANT EXECUTE ON WINDOW covar_samp(DOUBLE, DOUBLE) TO PUBLIC;
-create window covar_samp(e1 INTERVAL SECOND, e2 INTERVAL SECOND) returns DOUBLE
- external name "sql"."covariance";
-GRANT EXECUTE ON WINDOW covar_samp(INTERVAL SECOND, INTERVAL SECOND) TO PUBLIC;
-create window covar_samp(e1 INTERVAL MONTH, e2 INTERVAL MONTH) returns DOUBLE
- external name "sql"."covariance";
-GRANT EXECUTE ON WINDOW covar_samp(INTERVAL MONTH, INTERVAL MONTH) TO PUBLIC;
-
create aggregate covar_pop(e1 TINYINT, e2 TINYINT) returns DOUBLE
external name "aggr"."covariancep";
@@ -287,13 +218,6 @@ create aggregate covar_pop(e1 DOUBLE, e2
external name "aggr"."covariancep";
GRANT EXECUTE ON AGGREGATE covar_pop(DOUBLE, DOUBLE) TO PUBLIC;
-create aggregate covar_pop(e1 INTERVAL SECOND, e2 INTERVAL SECOND) returns
DOUBLE
- external name "aggr"."covariancep";
-GRANT EXECUTE ON AGGREGATE covar_pop(INTERVAL SECOND, INTERVAL SECOND) TO
PUBLIC;
-create aggregate covar_pop(e1 INTERVAL MONTH, e2 INTERVAL MONTH) returns DOUBLE
- external name "aggr"."covariancep";
-GRANT EXECUTE ON AGGREGATE covar_pop(INTERVAL MONTH, INTERVAL MONTH) TO PUBLIC;
-
create window covar_pop(e1 TINYINT, e2 TINYINT) returns DOUBLE
external name "sql"."covariancep";
GRANT EXECUTE ON WINDOW covar_pop(TINYINT, TINYINT) TO PUBLIC;
@@ -313,13 +237,6 @@ create window covar_pop(e1 DOUBLE, e2 DO
external name "sql"."covariancep";
GRANT EXECUTE ON WINDOW covar_pop(DOUBLE, DOUBLE) TO PUBLIC;
-create window covar_pop(e1 INTERVAL SECOND, e2 INTERVAL SECOND) returns DOUBLE
- external name "sql"."covariancep";
-GRANT EXECUTE ON WINDOW covar_pop(INTERVAL SECOND, INTERVAL SECOND) TO PUBLIC;
-create window covar_pop(e1 INTERVAL MONTH, e2 INTERVAL MONTH) returns DOUBLE
- external name "sql"."covariancep";
-GRANT EXECUTE ON WINDOW covar_pop(INTERVAL MONTH, INTERVAL MONTH) TO PUBLIC;
-
create aggregate median(val TINYINT) returns TINYINT
external name "aggr"."median";
@@ -355,6 +272,9 @@ GRANT EXECUTE ON AGGREGATE median(TIMEST
create aggregate median(val INTERVAL SECOND) returns INTERVAL SECOND
external name "aggr"."median";
GRANT EXECUTE ON AGGREGATE median(INTERVAL SECOND) TO PUBLIC;
+create aggregate median(val INTERVAL DAY) returns INTERVAL DAY
+ external name "aggr"."median";
+GRANT EXECUTE ON AGGREGATE median(INTERVAL DAY) TO PUBLIC;
create aggregate median(val INTERVAL MONTH) returns INTERVAL MONTH
external name "aggr"."median";
GRANT EXECUTE ON AGGREGATE median(INTERVAL MONTH) TO PUBLIC;
@@ -394,6 +314,9 @@ GRANT EXECUTE ON AGGREGATE quantile(TIME
create aggregate quantile(val INTERVAL SECOND, q DOUBLE) returns INTERVAL
SECOND
external name "aggr"."quantile";
GRANT EXECUTE ON AGGREGATE quantile(INTERVAL SECOND, DOUBLE) TO PUBLIC;
+create aggregate quantile(val INTERVAL DAY, q DOUBLE) returns INTERVAL DAY
+ external name "aggr"."quantile";
+GRANT EXECUTE ON AGGREGATE quantile(INTERVAL DAY, DOUBLE) TO PUBLIC;
create aggregate quantile(val INTERVAL MONTH, q DOUBLE) returns INTERVAL MONTH
external name "aggr"."quantile";
GRANT EXECUTE ON AGGREGATE quantile(INTERVAL MONTH, DOUBLE) TO PUBLIC;
@@ -464,13 +387,6 @@ create aggregate corr(e1 DOUBLE, e2 DOUB
external name "aggr"."corr";
GRANT EXECUTE ON AGGREGATE corr(DOUBLE, DOUBLE) TO PUBLIC;
-create aggregate corr(e1 INTERVAL SECOND, e2 INTERVAL SECOND) returns DOUBLE
- external name "aggr"."corr";
-GRANT EXECUTE ON AGGREGATE corr(INTERVAL SECOND, INTERVAL SECOND) TO PUBLIC;
-create aggregate corr(e1 INTERVAL MONTH, e2 INTERVAL MONTH) returns DOUBLE
- external name "aggr"."corr";
-GRANT EXECUTE ON AGGREGATE corr(INTERVAL MONTH, INTERVAL MONTH) TO PUBLIC;
-
create window corr(e1 TINYINT, e2 TINYINT) returns DOUBLE
external name "sql"."corr";
GRANT EXECUTE ON WINDOW corr(TINYINT, TINYINT) TO PUBLIC;
@@ -489,10 +405,3 @@ GRANT EXECUTE ON WINDOW corr(REAL, REAL)
create window corr(e1 DOUBLE, e2 DOUBLE) returns DOUBLE
external name "sql"."corr";
GRANT EXECUTE ON WINDOW corr(DOUBLE, DOUBLE) TO PUBLIC;
-
-create window corr(e1 INTERVAL SECOND, e2 INTERVAL SECOND) returns DOUBLE
- external name "sql"."corr";
-GRANT EXECUTE ON WINDOW corr(INTERVAL SECOND, INTERVAL SECOND) TO PUBLIC;
-create window corr(e1 INTERVAL MONTH, e2 INTERVAL MONTH) returns DOUBLE
- external name "sql"."corr";
-GRANT EXECUTE ON WINDOW corr(INTERVAL MONTH, INTERVAL MONTH) TO PUBLIC;
diff --git a/sql/test/sys-schema/Tests/systemfunctions.stable.out
b/sql/test/sys-schema/Tests/systemfunctions.stable.out
--- a/sql/test/sys-schema/Tests/systemfunctions.stable.out
+++ b/sql/test/sys-schema/Tests/systemfunctions.stable.out
@@ -476,12 +476,6 @@ stdout of test 'systemfunctions` in dire
[ "sys", "corr", 0, "double", "create aggregate corr(e1
double, e2 double) returns double\nexternal name \"aggr\".\"corr\";" ]
[ "sys", "corr", 1, "double", "" ]
[ "sys", "corr", 2, "double", "" ]
-[ "sys", "corr", 0, "double", "create aggregate corr(e1
interval second, e2 interval second) returns double\nexternal name
\"aggr\".\"corr\";" ]
-[ "sys", "corr", 1, "sec_interval", "" ]
-[ "sys", "corr", 2, "sec_interval", "" ]
-[ "sys", "corr", 0, "double", "create aggregate corr(e1
interval month, e2 interval month) returns double\nexternal name
\"aggr\".\"corr\";" ]
-[ "sys", "corr", 1, "month_interval", "" ]
-[ "sys", "corr", 2, "month_interval", "" ]
[ "sys", "corr", 0, "double", "create window corr(e1 tinyint,
e2 tinyint) returns double\nexternal name \"sql\".\"corr\";" ]
[ "sys", "corr", 1, "tinyint", "" ]
[ "sys", "corr", 2, "tinyint", "" ]
@@ -542,12 +536,6 @@ stdout of test 'systemfunctions` in dire
[ "sys", "covar_pop", 0, "double", "create aggregate
covar_pop(e1 double, e2 double) returns double\nexternal name
\"aggr\".\"covariancep\";" ]
[ "sys", "covar_pop", 1, "double", "" ]
[ "sys", "covar_pop", 2, "double", "" ]
-[ "sys", "covar_pop", 0, "double", "create aggregate
covar_pop(e1 interval second, e2 interval second) returns double\nexternal name
\"aggr\".\"covariancep\";" ]
-[ "sys", "covar_pop", 1, "sec_interval", "" ]
-[ "sys", "covar_pop", 2, "sec_interval", "" ]
-[ "sys", "covar_pop", 0, "double", "create aggregate
covar_pop(e1 interval month, e2 interval month) returns double\nexternal name
\"aggr\".\"covariancep\";" ]
-[ "sys", "covar_pop", 1, "month_interval", "" ]
-[ "sys", "covar_pop", 2, "month_interval", "" ]
[ "sys", "covar_pop", 0, "double", "create window
covar_pop(e1 tinyint, e2 tinyint) returns double\nexternal name
\"sql\".\"covariancep\";" ]
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
