Changeset: 5f1f6307fc99 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/5f1f6307fc99
Branch: Jun2023
Log Message:
Merge with ts_and_tz_to_str_fix branch.
diffs (truncated from 724 to 300 lines):
diff --git a/monetdb5/modules/atoms/Tests/All b/monetdb5/modules/atoms/Tests/All
--- a/monetdb5/modules/atoms/Tests/All
+++ b/monetdb5/modules/atoms/Tests/All
@@ -38,3 +38,5 @@ startswith
endswith
contains
HAVE_ICONV?asciify
+
+ts_and_tstz_to_str_bug
diff --git a/monetdb5/modules/atoms/Tests/ts_and_tstz_to_str_bug.test
b/monetdb5/modules/atoms/Tests/ts_and_tstz_to_str_bug.test
new file mode 100644
--- /dev/null
+++ b/monetdb5/modules/atoms/Tests/ts_and_tstz_to_str_bug.test
@@ -0,0 +1,16 @@
+statement ok
+CREATE TABLE t2 (dt TIMESTAMP)
+
+statement ok
+INSERT INTO t2 (dt) VALUES('2023-10-11 11:36')
+
+query I
+SELECT
+ levenshtein(sys.timestamp_to_str(cast(dt as timestamp with time zone),
'%Y-%d-%d %H:%M:%S'),
+ sys.timestamp_to_str(dt, '%Y-%d-%d %H:%M:%S'))
+FROM t2
+----
+0
+
+statement ok
+DROP TABLE t2
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
@@ -5164,6 +5164,7 @@ sql_update_jun2023(Client c, mvc *sql, s
char *err = NULL, *buf = GDKmalloc(bufsize);
res_table *output;
BAT *b;
+ sql_subtype t1, t2;
(void) sql;
if (buf == NULL)
@@ -5530,7 +5531,6 @@ sql_update_jun2023(Client c, mvc *sql, s
/* Add new sysadmin procedure calls: stop, pause and resume with two
arguments, first arg is query OID and second the user username that
the query in bound to. */
- sql_subtype t1, t2;
sql_find_subtype(&t1, "bigint", 64, 0);
sql_find_subtype(&t2, "varchar", 0, 0);
if (!sql_bind_func(sql, "sys", "pause", &t1, &t2, F_PROC, true)) {
@@ -5764,6 +5764,38 @@ sql_update_jun2023(Client c, mvc *sql, s
return err; /* usually MAL_SUCCEED */
}
+static str
+sql_update_jun2023_sp3(Client c, mvc *sql, sql_schema *s)
+{
+ (void)s;
+ char *err = NULL;
+ sql_subtype t1, t2;
+
+ sql_find_subtype(&t1, "timestamp", 0, 0);
+ sql_find_subtype(&t2, "varchar", 0, 0);
+
+ if (!sql_bind_func(sql, "sys", "timestamp_to_str", &t1, &t2, F_FUNC,
true)) {
+ sql->session->status = 0;
+ sql->errstr[0] = '\0';
+
+ char *query = GDKmalloc(512);
+ if (query == NULL)
+ throw(SQL, __func__, SQLSTATE(HY013) MAL_MALLOC_FAIL);
+
+ snprintf(query, 512, "CREATE FUNCTION timestamp_to_str(d
TIMESTAMP, format STRING) RETURNS STRING "
+ "EXTERNAL NAME mtime.\"timestamp_to_str\";\n"
+ "GRANT EXECUTE ON FUNCTION
timestamp_to_str(TIMESTAMP, STRING) TO PUBLIC;\n"
+ "UPDATE sys.functions SET system = true WHERE
system <> true AND name = 'timestamp_to_str' "
+ "AND schema_id = 2000 and type = %d;\n",
F_FUNC);
+
+ printf("Running database upgrade commands:\n%s\n", query);
+ err = SQLstatementIntern(c, query, "update", true, false, NULL);
+ GDKfree(query);
+ }
+
+ return err; /* usually MAL_SUCCEED */
+}
+
int
SQLupgrades(Client c, mvc *m)
{
@@ -5971,5 +6003,11 @@ SQLupgrades(Client c, mvc *m)
return -1;
}
+ if ((err = sql_update_jun2023_sp3(c, m, s)) != NULL) {
+ TRC_CRITICAL(SQL_PARSER, "%s\n", err);
+ freeException(err);
+ return -1;
+ }
+
return 0;
}
diff --git a/sql/scripts/13_date.sql b/sql/scripts/13_date.sql
--- a/sql/scripts/13_date.sql
+++ b/sql/scripts/13_date.sql
@@ -21,6 +21,9 @@ create function time_to_str(d time with
create function str_to_timestamp(s string, format string) returns timestamp
with time zone
external name mtime."str_to_timestamp";
+create function timestamp_to_str(d timestamp, format string) returns string
+ external name mtime."timestamp_to_str";
+
create function timestamp_to_str(d timestamp with time zone, format string)
returns string
external name mtime."timestamp_to_str";
@@ -29,4 +32,5 @@ grant execute on function date_to_str to
grant execute on function str_to_time to public;
grant execute on function time_to_str to public;
grant execute on function str_to_timestamp to public;
-grant execute on function timestamp_to_str to public;
+grant execute on function timestamp_to_str(timestamp, string) to public;
+grant execute on function timestamp_to_str(timestamp with time zone, string)
to public;
diff --git
a/sql/test/BugTracker-2019/Tests/duplicates-not-eliminated-long-CASE-stmt.Bug-6697.test
b/sql/test/BugTracker-2019/Tests/duplicates-not-eliminated-long-CASE-stmt.Bug-6697.test
---
a/sql/test/BugTracker-2019/Tests/duplicates-not-eliminated-long-CASE-stmt.Bug-6697.test
+++
b/sql/test/BugTracker-2019/Tests/duplicates-not-eliminated-long-CASE-stmt.Bug-6697.test
@@ -105,7 +105,7 @@ 1
query I rowsort
select count(*) from sys.tracelog() where stmt like '%batcalc.timestamp%'
----
-1
+0
query T rowsort
SELECT sys.timestamp_to_str(case when task0."sys_created_on" >= '1999-10-31
09:00:00' and task0."sys_created_on" < '2000-04-02 10:00:00'
diff --git
a/sql/test/emptydb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
b/sql/test/emptydb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
---
a/sql/test/emptydb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
+++
b/sql/test/emptydb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
@@ -366,3 +366,8 @@ update sys.functions set system = true w
update sys.functions set system = true where system <> true and name in
('startswith', 'endswith', 'contains') and schema_id = 2000 and type = 4;
delete from sys.triggers where name = 'system_update_tables' and table_id =
2067;
+Running database upgrade commands:
+CREATE FUNCTION "timestamp_to_str"(d TIMESTAMP, format STRING) RETURNS STRING
EXTERNAL NAME mtime."timestamp_to_str";
+GRANT EXECUTE ON FUNCTION "timestamp_to_str"(TIMESTAMP, STRING) TO PUBLIC;
+UPDATE sys.functions SET system = true WHERE system <> true AND name =
'timestamp_to_str' AND schema_id = 2000 and type = 1;
+
diff --git a/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out
b/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out
--- a/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out
+++ b/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out
@@ -366,3 +366,8 @@ update sys.functions set system = true w
update sys.functions set system = true where system <> true and name in
('startswith', 'endswith', 'contains') and schema_id = 2000 and type = 4;
delete from sys.triggers where name = 'system_update_tables' and table_id =
2067;
+Running database upgrade commands:
+CREATE FUNCTION "timestamp_to_str"(d TIMESTAMP, format STRING) RETURNS STRING
EXTERNAL NAME mtime."timestamp_to_str";
+GRANT EXECUTE ON FUNCTION "timestamp_to_str"(TIMESTAMP, STRING) TO PUBLIC;
+UPDATE sys.functions SET system = true WHERE system <> true AND name =
'timestamp_to_str' AND schema_id = 2000 and type = 1;
+
diff --git
a/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.int128
b/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.int128
--- a/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.int128
+++ b/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.int128
@@ -435,3 +435,8 @@ update sys.functions set system = true w
update sys.functions set system = true where system <> true and name in
('startswith', 'endswith', 'contains') and schema_id = 2000 and type = 4;
delete from sys.triggers where name = 'system_update_tables' and table_id =
2067;
+Running database upgrade commands:
+CREATE FUNCTION "timestamp_to_str"(d TIMESTAMP, format STRING) RETURNS STRING
EXTERNAL NAME mtime."timestamp_to_str";
+GRANT EXECUTE ON FUNCTION "timestamp_to_str"(TIMESTAMP, STRING) TO PUBLIC;
+UPDATE sys.functions SET system = true WHERE system <> true AND name =
'timestamp_to_str' AND schema_id = 2000 and type = 1;
+
diff --git
a/sql/test/emptydb-previous-upgrade-hge/Tests/upgrade.stable.out.int128
b/sql/test/emptydb-previous-upgrade-hge/Tests/upgrade.stable.out.int128
--- a/sql/test/emptydb-previous-upgrade-hge/Tests/upgrade.stable.out.int128
+++ b/sql/test/emptydb-previous-upgrade-hge/Tests/upgrade.stable.out.int128
@@ -366,3 +366,8 @@ update sys.functions set system = true w
update sys.functions set system = true where system <> true and name in
('startswith', 'endswith', 'contains') and schema_id = 2000 and type = 4;
delete from sys.triggers where name = 'system_update_tables' and table_id =
2067;
+Running database upgrade commands:
+CREATE FUNCTION "timestamp_to_str"(d TIMESTAMP, format STRING) RETURNS STRING
EXTERNAL NAME mtime."timestamp_to_str";
+GRANT EXECUTE ON FUNCTION "timestamp_to_str"(TIMESTAMP, STRING) TO PUBLIC;
+UPDATE sys.functions SET system = true WHERE system <> true AND name =
'timestamp_to_str' AND schema_id = 2000 and type = 1;
+
diff --git a/sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out
b/sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out
--- a/sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out
+++ b/sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out
@@ -366,3 +366,8 @@ update sys.functions set system = true w
update sys.functions set system = true where system <> true and name in
('startswith', 'endswith', 'contains') and schema_id = 2000 and type = 4;
delete from sys.triggers where name = 'system_update_tables' and table_id =
2067;
+Running database upgrade commands:
+CREATE FUNCTION "timestamp_to_str"(d TIMESTAMP, format STRING) RETURNS STRING
EXTERNAL NAME mtime."timestamp_to_str";
+GRANT EXECUTE ON FUNCTION "timestamp_to_str"(TIMESTAMP, STRING) TO PUBLIC;
+UPDATE sys.functions SET system = true WHERE system <> true AND name =
'timestamp_to_str' AND schema_id = 2000 and type = 1;
+
diff --git a/sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out.int128
b/sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out.int128
--- a/sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out.int128
+++ b/sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out.int128
@@ -70,6 +70,330 @@ update sys.functions set system = true w
update sys.functions set system = true where system <> true and name =
'filter' and schema_id = (select id from sys.schemas where name = 'json') and
type = 1;
Running database upgrade commands:
+alter table sys.db_user_info add column max_memory bigint;
+alter table sys.db_user_info add column max_workers int;
+alter table sys.db_user_info add column optimizer varchar(1024);
+alter table sys.db_user_info add column default_role int;
+alter table sys.db_user_info add column password varchar(256);
+update sys.db_user_info u set max_memory = 0, max_workers = 0, optimizer =
'default_pipe', default_role = (select id from sys.auths a where a.name =
u.name);
+-- and copying passwords
+
+Running database upgrade commands:
+drop view sys.dependency_schemas_on_users cascade;
+drop view sys.roles cascade;
+drop view sys.users cascade;
+drop function sys.db_users() cascade;
+CREATE VIEW sys.roles AS SELECT id, name, grantor FROM sys.auths a WHERE
a.name NOT IN (SELECT u.name FROM sys.db_user_info u);
+GRANT SELECT ON sys.roles TO PUBLIC;
+CREATE VIEW sys.users AS SELECT name, fullname, default_schema, schema_path,
max_memory, max_workers, optimizer, default_role FROM sys.db_user_info;
+GRANT SELECT ON sys.users TO PUBLIC;
+CREATE FUNCTION sys.db_users() RETURNS TABLE(name varchar(2048)) RETURN SELECT
name FROM sys.db_user_info;
+CREATE VIEW sys.dependency_schemas_on_users AS
+SELECT s.id AS schema_id, s.name AS schema_name, u.name AS user_name, CAST(6
AS smallint) AS depend_type
+ FROM sys.db_user_info AS u, sys.schemas AS s
+ WHERE u.default_schema = s.id
+ ORDER BY s.name, u.name;
+GRANT SELECT ON sys.dependency_schemas_on_users TO PUBLIC;
+update sys._tables set system = true where name in ('users', 'roles',
'dependency_schemas_on_users') AND schema_id = 2000;
+update sys.functions set system = true where system <> true and name in
('db_users') and schema_id = 2000 and type = 5;
+
+Running database upgrade commands:
+drop function sys.dump_database(boolean) cascade;
+drop procedure sys.dump_table_data() cascade;
+drop procedure sys.dump_table_data(string, string) cascade;
+drop view sys.dump_partition_tables cascade;
+drop view sys.describe_partition_tables cascade;
+drop view sys.dump_sequences cascade;
+drop view sys.dump_start_sequences cascade;
+drop view sys.dump_tables cascade;
+drop view sys.describe_tables cascade;
+drop view sys.dump_create_users cascade;
+drop view sys.dump_functions cascade;
+drop view sys.dump_triggers cascade;
+drop function sys.schema_guard cascade;
+drop function sys.replace_first(string, string, string, string) cascade;
+CREATE FUNCTION sys.schema_guard(sch STRING, nme STRING, stmt STRING) RETURNS
STRING BEGIN
+RETURN
+ SELECT 'SET SCHEMA ' || sys.dq(sch) || '; ' || stmt;
+END;
+CREATE VIEW sys.dump_functions AS
+ SELECT f.o o, sys.schema_guard(f.sch, f.fun, f.def) stmt,
+ f.sch schema_name,
+ f.fun function_name
+ FROM sys.describe_functions f;
+CREATE VIEW sys.dump_triggers AS
+ SELECT sys.schema_guard(sch, tab, def) stmt,
+ sch schema_name,
+ tab table_name,
+ tri trigger_name
+ FROM sys.describe_triggers;
+CREATE VIEW sys.describe_partition_tables AS
+ SELECT
+ m_sch,
+ m_tbl,
+ p_sch,
+ p_tbl,
+ CASE
+ WHEN p_raw_type IS NULL THEN 'READ ONLY'
+ WHEN (p_raw_type = 'VALUES' AND pvalues IS NULL) OR (p_raw_type = 'RANGE' AND
minimum IS NULL AND maximum IS NULL AND with_nulls) THEN 'FOR NULLS'
+ ELSE p_raw_type
+ END AS tpe,
+ pvalues,
+ minimum,
+ maximum,
+ with_nulls
+ FROM
+ (WITH
+ tp("type", table_id) AS
+ (SELECT ifthenelse((table_partitions."type" & 2) = 2, 'VALUES', 'RANGE'),
table_partitions.table_id FROM sys.table_partitions),
+ subq(m_tid, p_mid, "type", m_sch, m_tbl, p_sch, p_tbl) AS
+ (SELECT m_t.id, p_m.id, m_t."type", m_s.name, m_t.name, p_s.name, p_m.name
+ FROM sys.schemas m_s, sys._tables m_t, sys.dependencies d, sys.schemas p_s,
sys._tables p_m
+ WHERE m_t."type" IN (3, 6)
+ AND m_t.schema_id = m_s.id
+ AND m_s.name <> 'tmp'
+ AND m_t.system = FALSE
+ AND m_t.id = d.depend_id
+ AND d.id = p_m.id
+ AND p_m.schema_id = p_s.id
+ ORDER BY m_t.id, p_m.id),
+ vals(id,vals) as
+ (SELECT vp.table_id, GROUP_CONCAT(vp.value, ',') FROM sys.value_partitions vp
GROUP BY vp.table_id)
+ SELECT
+ subq.m_sch,
+ subq.m_tbl,
+ subq.p_sch,
+ subq.p_tbl,
+ tp."type" AS p_raw_type,
+ CASE WHEN tp."type" = 'VALUES'
+ THEN (SELECT vals.vals FROM vals WHERE vals.id = subq.p_mid)
+ ELSE NULL
+ END AS pvalues,
+ CASE WHEN tp."type" = 'RANGE'
+ THEN (SELECT minimum FROM sys.range_partitions rp WHERE rp.table_id =
subq.p_mid)
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
