Changeset: a992d4d5abfa for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=a992d4d5abfa
Modified Files:
sql/backends/monet5/sql_upgrades.c
sql/scripts/39_analytics.sql
sql/scripts/39_analytics_hge.sql
Branch: statistics-analytics
Log Message:
Several changes.
- Added SQL definitions for the new window functions on huge integers
- Removed stddev_samp, stddev_pop, var_samp and var_pop definitions on date,
time and timestamps because they were not implemented, and also meaningless.
- Added upgrade code for changes so far.
diffs (truncated from 312 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
@@ -2557,7 +2557,6 @@ sql_update_default(Client c, mvc *sql, c
"('STATEMENT'),('TABLE'),('TEMP'),('TEMPORARY'),('TEXT'),('TIME'),('TIMESTAMP'),('TRACE'),('TYPE'),('UNIONJOIN'),"
"('WEEK'),('YEAR'),('ZONE');\n");
- pos += snprintf(buf + pos, bufsize - pos, "commit;\n");
pos += snprintf(buf + pos, bufsize - pos, "set schema \"%s\";\n",
prev_schema);
assert(pos < bufsize);
@@ -2575,6 +2574,166 @@ sql_update_default(Client c, mvc *sql, c
return err; /* usually MAL_SUCCEED */
}
+static str
+sql_update_analytics(Client c, mvc *sql, const char *prev_schema, bool
*systabfixed)
+{
+ size_t bufsize = 8192, pos = 0;
+ char *err = NULL, *buf = GDKmalloc(bufsize);
+
+ if (!*systabfixed &&
+ (err = sql_fix_system_tables(c, sql, prev_schema)) != NULL)
+ return err;
+ *systabfixed = true;
+
+ if (buf == NULL)
+ throw(SQL, __func__, SQLSTATE(HY013) MAL_MALLOC_FAIL);
+
+ /* 39_analytics.sql */
+ pos += snprintf(buf + pos, bufsize - pos,
+ "create window stddev_samp(val TINYINT) returns
DOUBLE\n"
+ " external name \"sql\".\"stdev\";\n"
+ "GRANT EXECUTE ON WINDOW stddev_samp(TINYINT) TO
PUBLIC;\n"
+ "create window stddev_samp(val SMALLINT) returns DOUBLE"
+ " external name \"sql\".\"stdev\";\n"
+ "GRANT EXECUTE ON WINDOW stddev_samp(SMALLINT) TO
PUBLIC;\n"
+ "create window stddev_samp(val INTEGER) returns
DOUBLE\n"
+ " external name \"sql\".\"stdev\";\n"
+ "GRANT EXECUTE ON WINDOW stddev_samp(INTEGER) TO
PUBLIC;\n"
+ "create window stddev_samp(val BIGINT) returns DOUBLE\n"
+ " external name \"sql\".\"stdev\";\n"
+ "GRANT EXECUTE ON WINDOW stddev_samp(BIGINT) TO
PUBLIC;\n"
+ "create window stddev_samp(val REAL) returns DOUBLE\n"
+ " external name \"sql\".\"stdev\";\n"
+ "GRANT EXECUTE ON WINDOW stddev_samp(REAL) TO PUBLIC;\n"
+ "create window stddev_samp(val DOUBLE) returns DOUBLE\n"
+ " external name \"sql\".\"stdev\";\n"
+ "GRANT EXECUTE ON WINDOW stddev_samp(DOUBLE) TO
PUBLIC;\n"
+ "create window stddev_samp(val INTERVAL SECOND) returns
DOUBLE\n"
+ " external name \"sql\".\"stdev\";\n"
+ "GRANT EXECUTE ON WINDOW stddev_samp(INTERVAL SECOND)
TO PUBLIC;\n"
+ "create window stddev_samp(val INTERVAL MONTH) returns
DOUBLE\n"
+ " external name \"sql\".\"stdev\";\n"
+ "GRANT EXECUTE ON WINDOW stddev_samp(INTERVAL MONTH) TO
PUBLIC;\n"
+ "create window stddev_pop(val TINYINT) returns DOUBLE\n"
+ " external name \"sql\".\"stdevp\";\n"
+ "GRANT EXECUTE ON WINDOW stddev_pop(TINYINT) TO
PUBLIC;\n"
+ "create window stddev_pop(val SMALLINT) returns
DOUBLE\n"
+ " external name \"sql\".\"stdevp\";\n"
+ "GRANT EXECUTE ON WINDOW stddev_pop(SMALLINT) TO
PUBLIC;\n"
+ "create window stddev_pop(val INTEGER) returns DOUBLE\n"
+ " external name \"sql\".\"stdevp\";\n"
+ "GRANT EXECUTE ON WINDOW stddev_pop(INTEGER) TO
PUBLIC;\n"
+ "create window stddev_pop(val BIGINT) returns DOUBLE\n"
+ " external name \"sql\".\"stdevp\";\n"
+ "GRANT EXECUTE ON WINDOW stddev_pop(BIGINT) TO
PUBLIC;\n"
+ "create window stddev_pop(val REAL) returns DOUBLE\n"
+ " external name \"sql\".\"stdevp\";\n"
+ "GRANT EXECUTE ON WINDOW stddev_pop(REAL) TO PUBLIC;\n"
+ "create window stddev_pop(val DOUBLE) returns DOUBLE\n"
+ " external name \"sql\".\"stdevp\";\n"
+ "GRANT EXECUTE ON WINDOW stddev_pop(DOUBLE) TO
PUBLIC;\n"
+ "create window stddev_pop(val INTERVAL SECOND) returns
DOUBLE\n"
+ " external name \"sql\".\"stdevp\";\n"
+ "GRANT EXECUTE ON WINDOW stddev_pop(INTERVAL SECOND) TO
PUBLIC;\n"
+ "create window stddev_pop(val INTERVAL MONTH) returns
DOUBLE\n"
+ " external name \"sql\".\"stdevp\";\n"
+ "GRANT EXECUTE ON WINDOW stddev_pop(INTERVAL MONTH) TO
PUBLIC;\n"
+ "create window var_samp(val TINYINT) returns DOUBLE\n"
+ " external name \"sql\".\"variance\";\n"
+ "GRANT EXECUTE ON WINDOW var_samp(TINYINT) TO PUBLIC;\n"
+ "create window var_samp(val SMALLINT) returns DOUBLE\n"
+ " external name \"sql\".\"variance\";\n"
+ "GRANT EXECUTE ON WINDOW var_samp(SMALLINT) TO
PUBLIC;\n"
+ "create window var_samp(val INTEGER) returns DOUBLE\n"
+ " external name \"sql\".\"variance\";\n"
+ "GRANT EXECUTE ON WINDOW var_samp(INTEGER) TO PUBLIC;\n"
+ "create window var_samp(val BIGINT) returns DOUBLE\n"
+ " external name \"sql\".\"variance\";\n"
+ "GRANT EXECUTE ON WINDOW var_samp(BIGINT) TO PUBLIC;\n"
+ "create window var_samp(val REAL) returns DOUBLE\n"
+ " external name \"sql\".\"variance\";\n"
+ "GRANT EXECUTE ON WINDOW var_samp(REAL) TO PUBLIC;\n"
+ "create window var_samp(val DOUBLE) returns DOUBLE\n"
+ " external name \"sql\".\"variance\";\n"
+ "GRANT EXECUTE ON WINDOW var_samp(DOUBLE) TO PUBLIC;\n"
+ "create window var_samp(val INTERVAL SECOND) returns
DOUBLE\n"
+ " external name \"sql\".\"variance\";\n"
+ "GRANT EXECUTE ON WINDOW var_samp(INTERVAL SECOND) TO
PUBLIC;\n"
+ "create window var_samp(val INTERVAL MONTH) returns
DOUBLE\n"
+ " external name \"sql\".\"variance\";\n"
+ "GRANT EXECUTE ON WINDOW var_samp(INTERVAL MONTH) TO
PUBLIC;\n"
+ "create window var_pop(val TINYINT) returns DOUBLE\n"
+ " external name \"sql\".\"variancep\";\n"
+ "GRANT EXECUTE ON WINDOW var_pop(TINYINT) TO PUBLIC;\n"
+ "create window var_pop(val SMALLINT) returns DOUBLE\n"
+ " external name \"sql\".\"variancep\";\n"
+ "GRANT EXECUTE ON WINDOW var_pop(SMALLINT) TO PUBLIC;\n"
+ "create window var_pop(val INTEGER) returns DOUBLE\n"
+ " external name \"sql\".\"variancep\";\n"
+ "GRANT EXECUTE ON WINDOW var_pop(INTEGER) TO PUBLIC;\n"
+ "create window var_pop(val BIGINT) returns DOUBLE\n"
+ " external name \"sql\".\"variancep\";\n"
+ "GRANT EXECUTE ON WINDOW var_pop(BIGINT) TO PUBLIC;\n"
+ "create window var_pop(val REAL) returns DOUBLE\n"
+ " external name \"sql\".\"variancep\";\n"
+ "GRANT EXECUTE ON WINDOW var_pop(REAL) TO PUBLIC;\n"
+ "create window var_pop(val DOUBLE) returns DOUBLE\n"
+ " external name \"sql\".\"variancep\";\n"
+ "GRANT EXECUTE ON WINDOW var_pop(DOUBLE) TO PUBLIC;\n"
+ "create window var_pop(val INTERVAL SECOND) returns
DOUBLE\n"
+ " external name \"sql\".\"variancep\";\n"
+ "GRANT EXECUTE ON WINDOW var_pop(INTERVAL SECOND) TO
PUBLIC;\n"
+ "create window var_pop(val INTERVAL MONTH) returns
DOUBLE\n"
+ " external name \"sql\".\"variancep\";\n"
+ "GRANT EXECUTE ON WINDOW var_pop(INTERVAL MONTH) TO
PUBLIC;\n");
+
+#ifdef HAVE_HGE
+ if (have_hge) {
+ /* 39_analytics_hge.sql */
+ pos += snprintf(buf + pos, bufsize - pos,
+ "create window stddev_samp(val HUGEINT) returns
DOUBLE\n"
+ " external name \"sql\".\"stdev\";\n"
+ "GRANT EXECUTE ON WINDOW stddev_samp(HUGEINT) TO
PUBLIC;\n"
+ "create window stddev_pop(val HUGEINT) returns DOUBLE\n"
+ " external name \"sql\".\"stdevp\";\n"
+ "GRANT EXECUTE ON WINDOW stddev_pop(HUGEINT) TO
PUBLIC;\n"
+ "create window var_samp(val HUGEINT) returns DOUBLE\n"
+ " external name \"sql\".\"variance\";\n"
+ "GRANT EXECUTE ON WINDOW var_samp(HUGEINT) TO PUBLIC;\n"
+ "create window var_pop(val HUGEINT) returns DOUBLE\n"
+ " external name \"sql\".\"variancep\";\n"
+ "GRANT EXECUTE ON WINDOW var_pop(HUGEINT) TO
PUBLIC;\n");
+ }
+#endif
+
+ pos += snprintf(buf + pos, bufsize - pos,
+ "update sys.functions set system = true where name in
('stddev_samp', 'stddev_pop', 'var_samp', 'var_pop')"
+ " and schema_id = (select id from sys.schemas where
name = 'sys');\n");
+
+ pos += snprintf(buf + pos, bufsize - pos,
+ "DROP AGGREGATE stddev_samp(date);\n"
+ "DROP AGGREGATE stddev_samp(time);\n"
+ "DROP AGGREGATE stddev_samp(timestamp);\n"
+ "DROP AGGREGATE stddev_pop(date);\n"
+ "DROP AGGREGATE stddev_pop(time);\n"
+ "DROP AGGREGATE stddev_pop(timestamp);\n"
+ "DROP AGGREGATE var_samp(date);\n"
+ "DROP AGGREGATE var_samp(time);\n"
+ "DROP AGGREGATE var_samp(timestamp);\n"
+ "DROP AGGREGATE var_pop(date);\n"
+ "DROP AGGREGATE var_pop(time);\n"
+ "DROP AGGREGATE var_pop(timestamp);\n");
+
+ pos += snprintf(buf + pos, bufsize - pos, "commit;\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 */
+}
+
int
SQLupgrades(Client c, mvc *m)
{
@@ -2868,6 +3027,14 @@ SQLupgrades(Client c, mvc *m)
}
}
+ if (!res && !sql_find_func_by_name(m->sa, s, "stddev_samp", 1,
F_ANALYTIC)) {
+ if ((err = sql_update_analytics(c, m, prev_schema,
&systabfixed)) != NULL) {
+ fprintf(stderr, "!%s\n", err);
+ freeException(err);
+ res = -1;
+ }
+ }
+
GDKfree(prev_schema);
return res;
}
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,15 +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 DATE) returns DOUBLE
- external name "aggr"."stdev";
-GRANT EXECUTE ON AGGREGATE stddev_samp(DATE) TO PUBLIC;
-create aggregate stddev_samp(val TIME) returns DOUBLE
- external name "aggr"."stdev";
-GRANT EXECUTE ON AGGREGATE stddev_samp(TIME) TO PUBLIC;
-create aggregate stddev_samp(val TIMESTAMP) returns DOUBLE
- external name "aggr"."stdev";
-GRANT EXECUTE ON AGGREGATE stddev_samp(TIMESTAMP) 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;
@@ -85,15 +76,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 DATE) returns DOUBLE
- external name "aggr"."stdevp";
-GRANT EXECUTE ON AGGREGATE stddev_pop(DATE) TO PUBLIC;
-create aggregate stddev_pop(val TIME) returns DOUBLE
- external name "aggr"."stdevp";
-GRANT EXECUTE ON AGGREGATE stddev_pop(TIME) TO PUBLIC;
-create aggregate stddev_pop(val TIMESTAMP) returns DOUBLE
- external name "aggr"."stdevp";
-GRANT EXECUTE ON AGGREGATE stddev_pop(TIMESTAMP) 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;
@@ -147,15 +129,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 DATE) returns DOUBLE
- external name "aggr"."variance";
-GRANT EXECUTE ON AGGREGATE var_samp(DATE) TO PUBLIC;
-create aggregate var_samp(val TIME) returns DOUBLE
- external name "aggr"."variance";
-GRANT EXECUTE ON AGGREGATE var_samp(TIME) TO PUBLIC;
-create aggregate var_samp(val TIMESTAMP) returns DOUBLE
- external name "aggr"."variance";
-GRANT EXECUTE ON AGGREGATE var_samp(TIMESTAMP) 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;
@@ -208,16 +181,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 DATE) returns DOUBLE
- external name "aggr"."variancep";
-GRANT EXECUTE ON AGGREGATE var_pop(DATE) TO PUBLIC;
-create aggregate var_pop(val TIME) returns DOUBLE
- external name "aggr"."variancep";
-GRANT EXECUTE ON AGGREGATE var_pop(TIME) TO PUBLIC;
-create aggregate var_pop(val TIMESTAMP) returns DOUBLE
- external name "aggr"."variancep";
-GRANT EXECUTE ON AGGREGATE var_pop(TIMESTAMP) 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;
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
@@ -7,27 +7,47 @@
create aggregate stddev_samp(val HUGEINT) returns DOUBLE
external name "aggr"."stdev";
GRANT EXECUTE ON AGGREGATE stddev_samp(HUGEINT) TO PUBLIC;
+create window stddev_samp(val HUGEINT) returns DOUBLE
+ external name "sql"."stdev";
+GRANT EXECUTE ON WINDOW stddev_samp(HUGEINT) TO PUBLIC;
+
create aggregate stddev_pop(val HUGEINT) returns DOUBLE
external name "aggr"."stdevp";
GRANT EXECUTE ON AGGREGATE stddev_pop(HUGEINT) TO PUBLIC;
+create window stddev_pop(val HUGEINT) returns DOUBLE
+ external name "sql"."stdevp";
+GRANT EXECUTE ON WINDOW stddev_pop(HUGEINT) TO PUBLIC;
+
create aggregate var_samp(val HUGEINT) returns DOUBLE
external name "aggr"."variance";
GRANT EXECUTE ON AGGREGATE var_samp(HUGEINT) TO PUBLIC;
+create window var_samp(val HUGEINT) returns DOUBLE
+ external name "sql"."variance";
+GRANT EXECUTE ON WINDOW var_samp(HUGEINT) TO PUBLIC;
+
create aggregate var_pop(val HUGEINT) returns DOUBLE
external name "aggr"."variancep";
GRANT EXECUTE ON AGGREGATE var_pop(HUGEINT) TO PUBLIC;
+create window var_pop(val HUGEINT) returns DOUBLE
+ external name "sql"."variancep";
+GRANT EXECUTE ON WINDOW var_pop(HUGEINT) TO PUBLIC;
+
create aggregate median(val HUGEINT) returns HUGEINT
external name "aggr"."median";
GRANT EXECUTE ON AGGREGATE median(HUGEINT) TO PUBLIC;
+
create aggregate quantile(val HUGEINT, q DOUBLE) returns HUGEINT
external name "aggr"."quantile";
GRANT EXECUTE ON AGGREGATE quantile(HUGEINT, DOUBLE) TO PUBLIC;
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
