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