Changeset: befa1b10e22b for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/befa1b10e22b
Branch: nilmask
Log Message:
merged with default
diffs (truncated from 3604 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.h b/sql/backends/monet5/sql.h
--- a/sql/backends/monet5/sql.h
+++ b/sql/backends/monet5/sql.h
@@ -282,7 +282,6 @@ extern str SQLflush_log(Client cntxt, Ma
extern str SQLsuspend_log_flushing(Client cntxt, MalBlkPtr mb, MalStkPtr stk,
InstrPtr pci);
extern str SQLresume_log_flushing(Client cntxt, MalBlkPtr mb, MalStkPtr stk,
InstrPtr pci);
extern str SQLhot_snapshot(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr
pci);
-extern str SQLhot_snapshot_wrap(Client cntxt, MalBlkPtr mb, MalStkPtr stk,
InstrPtr pci);
extern str SQLpersist_unlogged(Client cntxt, MalBlkPtr mb, MalStkPtr stk,
InstrPtr pci);
extern str SQLsession_prepared_statements(Client cntxt, MalBlkPtr mb,
MalStkPtr stk, InstrPtr pci);
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
@@ -5179,6 +5179,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)
@@ -5546,7 +5547,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)) {
@@ -5781,6 +5781,38 @@ sql_update_jun2023(Client c, mvc *sql, s
}
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 */
+}
+
+static str
sql_update_default_geom(Client c, mvc *sql, sql_schema *s)
{
sql_subtype tp;
@@ -6278,10 +6310,10 @@ sql_update_default(Client c, mvc *sql, s
"EXTERNAL NAME sql.persist_unlogged;\n"
"CREATE FUNCTION sys.persist_unlogged(sname STRING)\n"
"RETURNS TABLE(\"table\" STRING, \"table_id\" INT,
\"rowcount\" BIGINT)\n"
- "EXTERNAL NAME sql.persist_unlogged(string);\n"
+ "EXTERNAL NAME sql.persist_unlogged;\n"
"CREATE FUNCTION sys.persist_unlogged(sname STRING,
tname STRING)\n"
"RETURNS TABLE(\"table\" STRING, \"table_id\" INT,
\"rowcount\" BIGINT)\n"
- "EXTERNAL NAME sql.persist_unlogged(string, string);\n"
+ "EXTERNAL NAME sql.persist_unlogged;\n"
"GRANT EXECUTE ON FUNCTION sys.persist_unlogged() TO
PUBLIC;\n"
"UPDATE sys.functions SET system = true WHERE system <>
true AND\n"
"name = 'persist_unlogged' AND schema_id = 2000;\n";
@@ -6476,6 +6508,12 @@ SQLupgrades(Client c, mvc *m)
goto handle_error;
}
+ if ((err = sql_update_jun2023_sp3(c, m, s)) != NULL) {
+ TRC_CRITICAL(SQL_PARSER, "%s\n", err);
+ freeException(err);
+ return -1;
+ }
+
return 0;
handle_error:
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
@@ -599,15 +599,15 @@ CREATE VIEW INFORMATION_SCHEMA.COLUMNS A
cast(c."number" +1 AS int) AS ORDINAL_POSITION,
c."default" AS COLUMN_DEFAULT,
cast(sys.ifthenelse(c."null", 'YES', 'NO') AS varchar(3)) AS IS_NULLABLE,
- c."type" AS DATA_TYPE,
+ CASE c."type" WHEN 'day_interval' THEN 'interval day' WHEN 'month_interval'
THEN 'interval month' WHEN 'sec_interval' THEN 'interval second' ELSE c."type"
END AS DATA_TYPE,
cast(sys.ifthenelse(c."type" IN
('varchar','clob','char','json','url','xml'), c."type_digits", NULL) AS int) AS
CHARACTER_MAXIMUM_LENGTH,
cast(sys.ifthenelse(c."type" IN
('varchar','clob','char','json','url','xml'), c."type_digits" * 3, NULL) AS
int) AS CHARACTER_OCTET_LENGTH,
cast(sys.ifthenelse(c."type" IN
('int','smallint','tinyint','bigint','hugeint','float','real','double','decimal','numeric','oid'),
c."type_digits", NULL) AS int) AS NUMERIC_PRECISION,
cast(sys.ifthenelse(c."type" IN
('int','smallint','tinyint','bigint','hugeint','float','real','double','oid'),
2, sys.ifthenelse(c."type" IN ('decimal','numeric'), 10, NULL)) AS int) AS
NUMERIC_PRECISION_RADIX,
cast(sys.ifthenelse(c."type" IN
('int','smallint','tinyint','bigint','hugeint','float','real','double','decimal','numeric','oid'),
c."type_scale", NULL) AS int) AS NUMERIC_SCALE,
cast(sys.ifthenelse(c."type" IN
('date','timestamp','timestamptz','time','timetz'), c."type_scale" -1, NULL) AS
int) AS DATETIME_PRECISION,
- cast(CASE c."type" WHEN 'day_interval' THEN 'interval day' WHEN
'month_interval' THEN 'interval month' WHEN 'sec_interval' THEN 'interval
second' ELSE NULL END AS varchar(40)) AS INTERVAL_TYPE,
- cast(sys.ifthenelse(c."type" IN
('day_interval','month_interval','sec_interval'), c."type_scale" -1, NULL) AS
int) AS INTERVAL_PRECISION,
+ cast(CASE c."type" WHEN 'day_interval' THEN 'interval day' WHEN
'month_interval' THEN (CASE c."type_digits" WHEN 1 THEN 'interval year' WHEN 2
THEN 'interval year to month' WHEN 3 THEN 'interval month' ELSE NULL END) WHEN
'sec_interval' THEN (CASE c."type_digits" WHEN 5 THEN 'interval day to hour'
WHEN 6 THEN 'interval day to minute' WHEN 7 THEN 'interval day to second' WHEN
8 THEN 'interval hour' WHEN 9 THEN 'interval hour to minute' WHEN 10 THEN
'interval hour to second' WHEN 11 THEN 'interval minute' WHEN 12 THEN 'interval
minute to second' WHEN 13 THEN 'interval second' ELSE NULL END) ELSE NULL END
AS varchar(40)) AS INTERVAL_TYPE,
+ cast(CASE c."type" WHEN 'day_interval' THEN 0 WHEN 'month_interval' THEN 0
WHEN 'sec_interval' THEN (sys.ifthenelse(c."type_digits" IN (7, 10, 12, 13),
sys.ifthenelse(c."type_scale" > 0, c."type_scale", 3), 0)) ELSE NULL END AS
int) AS INTERVAL_PRECISION,
cast(NULL AS varchar(1)) AS CHARACTER_SET_CATALOG,
cast(NULL AS varchar(1)) AS CHARACTER_SET_SCHEMA,
cast(sys.ifthenelse(c."type" IN
('varchar','clob','char','json','url','xml'), 'UTF-8', NULL) AS varchar(16)) AS
CHARACTER_SET_NAME,
@@ -654,8 +654,110 @@ CREATE VIEW INFORMATION_SCHEMA.COLUMNS A
LEFT OUTER JOIN sys."comments" cm ON c."id" = cm."id"
ORDER BY s."name", t."name", c."number";
GRANT SELECT ON TABLE INFORMATION_SCHEMA.COLUMNS TO PUBLIC WITH GRANT OPTION;
+CREATE VIEW INFORMATION_SCHEMA.CHECK_CONSTRAINTS AS SELECT
+ cast(NULL AS varchar(1)) AS CONSTRAINT_CATALOG,
+ cast(NULL AS varchar(1024)) AS CONSTRAINT_SCHEMA,
+ cast(NULL AS varchar(1024)) AS CONSTRAINT_NAME,
+ cast(NULL AS varchar(1024)) AS CHECK_CLAUSE
+ WHERE 1=0;
+GRANT SELECT ON TABLE INFORMATION_SCHEMA.CHECK_CONSTRAINTS TO PUBLIC WITH
GRANT OPTION;
+CREATE VIEW INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS SELECT
+ cast(NULL AS varchar(1)) AS CONSTRAINT_CATALOG,
+ s."name" AS CONSTRAINT_SCHEMA,
+ k."name" AS CONSTRAINT_NAME,
+ cast(NULL AS varchar(1)) AS TABLE_CATALOG,
+ s."name" AS TABLE_SCHEMA,
+ t."name" AS TABLE_NAME,
+ cast(CASE k."type" WHEN 0 THEN 'PRIMARY KEY' WHEN 1 THEN 'UNIQUE' WHEN 2
THEN 'FOREIGN KEY' ELSE NULL END AS varchar(16)) AS CONSTRAINT_TYPE,
+ cast('NO' AS varchar(3)) AS IS_DEFERRABLE,
+ cast('NO' AS varchar(3)) AS INITIALLY_DEFERRED,
+ cast('YES' AS varchar(3)) AS ENFORCED,
+ -- MonetDB column extensions
+ t."schema_id" AS schema_id,
+ t."id" AS table_id,
+ k."id" AS key_id,
+ k."type" AS key_type,
+ t."system" AS is_system
+ FROM (SELECT sk."id", sk."table_id", sk."name", sk."type" FROM sys."keys" sk
UNION ALL SELECT tk."id", tk."table_id", tk."name", tk."type" FROM tmp."keys"
tk) k
+ INNER JOIN (SELECT st."id", st."schema_id", st."name", st."system" FROM
sys."_tables" st UNION ALL SELECT tt."id", tt."schema_id", tt."name",
tt."system" FROM tmp."_tables" tt) t ON k."table_id" = t."id"
+ INNER JOIN sys."schemas" s ON t."schema_id" = s."id"
+ ORDER BY s."name", t."name", k."name";
+GRANT SELECT ON TABLE INFORMATION_SCHEMA.TABLE_CONSTRAINTS TO PUBLIC WITH
GRANT OPTION;
+CREATE VIEW INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS SELECT
+ cast(NULL AS varchar(1)) AS CONSTRAINT_CATALOG,
+ s."name" AS CONSTRAINT_SCHEMA,
+ fk."name" AS CONSTRAINT_NAME,
+ cast(NULL AS varchar(1)) AS UNIQUE_CONSTRAINT_CATALOG,
+ uks."name" AS UNIQUE_CONSTRAINT_SCHEMA,
+ uk."name" AS UNIQUE_CONSTRAINT_NAME,
+ cast('FULL' AS varchar(7)) AS MATCH_OPTION,
+ fk."update_action" AS UPDATE_RULE,
+ fk."delete_action" AS DELETE_RULE,
+ -- MonetDB column extensions
+ t."schema_id" AS fk_schema_id,
+ t."id" AS fk_table_id,
+ t."name" AS fk_table_name,
+ fk."id" AS fk_key_id,
+ ukt."schema_id" AS uc_schema_id,
+ uk."table_id" AS uc_table_id,
+ ukt."name" AS uc_table_name,
+ uk."id" AS uc_key_id
+ FROM sys."fkeys" fk
+ INNER JOIN sys."tables" t ON t."id" = fk."table_id"
+ INNER JOIN sys."schemas" s ON s."id" = t."schema_id"
+ LEFT OUTER JOIN sys."keys" uk ON uk."id" = fk."rkey"
+ LEFT OUTER JOIN sys."tables" ukt ON ukt."id" = uk."table_id"
+ LEFT OUTER JOIN sys."schemas" uks ON uks."id" = ukt."schema_id"
+ ORDER BY s."name", t."name", fk."name";
+GRANT SELECT ON TABLE INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS TO PUBLIC
WITH GRANT OPTION;
+CREATE VIEW INFORMATION_SCHEMA.SEQUENCES AS SELECT
+ cast(NULL AS varchar(1)) AS SEQUENCE_CATALOG,
+ s."name" AS SEQUENCE_SCHEMA,
+ sq."name" AS SEQUENCE_NAME,
+ cast('bigint' AS varchar(16)) AS DATA_TYPE,
+ cast(64 AS SMALLINT) AS NUMERIC_PRECISION,
+ cast(2 AS SMALLINT) AS NUMERIC_PRECISION_RADIX,
+ cast(0 AS SMALLINT) AS NUMERIC_SCALE,
+ sq."start" AS START_VALUE,
+ sq."minvalue" AS MINIMUM_VALUE,
+ sq."maxvalue" AS MAXIMUM_VALUE,
+ sq."increment" AS INCREMENT,
+ cast(sys.ifthenelse(sq."cycle", 'YES', 'NO') AS varchar(3)) AS CYCLE_OPTION,
+ cast(NULL AS varchar(16)) AS DECLARED_DATA_TYPE,
+ cast(NULL AS SMALLINT) AS DECLARED_NUMERIC_PRECISION,
+ cast(NULL AS SMALLINT) AS DECLARED_NUMERIC_SCALE,
+ -- MonetDB column extensions
+ sq."schema_id" AS schema_id,
+ sq."id" AS sequence_id,
+ get_value_for(s."name", sq."name") AS current_value,
+ sq."cacheinc" AS cacheinc,
+ cm."remark" AS comments
+ FROM sys."sequences" sq
+ INNER JOIN sys."schemas" s ON sq."schema_id" = s."id"
+ LEFT OUTER JOIN sys."comments" cm ON sq."id" = cm."id"
+ ORDER BY s."name", sq."name";
+GRANT SELECT ON TABLE INFORMATION_SCHEMA.SEQUENCES TO PUBLIC WITH GRANT OPTION;
update sys._tables set system = true where system <> true
and schema_id = (select s.id from sys.schemas s where s.name =
'information_schema')
- and name in ('character_sets','schemata','tables','views','columns');
+ and name in
('character_sets','check_constraints','columns','schemata','sequences','referential_constraints','table_constraints','tables','views');
+Running database upgrade commands:
+CREATE FUNCTION sys.persist_unlogged()
+RETURNS TABLE("table" STRING, "table_id" INT, "rowcount" BIGINT)
+EXTERNAL NAME sql.persist_unlogged;
+CREATE FUNCTION sys.persist_unlogged(sname STRING)
+RETURNS TABLE("table" STRING, "table_id" INT, "rowcount" BIGINT)
+EXTERNAL NAME sql.persist_unlogged;
+CREATE FUNCTION sys.persist_unlogged(sname STRING, tname STRING)
+RETURNS TABLE("table" STRING, "table_id" INT, "rowcount" BIGINT)
+EXTERNAL NAME sql.persist_unlogged;
+GRANT EXECUTE ON FUNCTION sys.persist_unlogged() TO PUBLIC;
+UPDATE sys.functions SET system = true WHERE system <> true AND
+name = 'persist_unlogged' AND schema_id = 2000;
+
+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
@@ -599,15 +599,15 @@ CREATE VIEW INFORMATION_SCHEMA.COLUMNS A
cast(c."number" +1 AS int) AS ORDINAL_POSITION,
c."default" AS COLUMN_DEFAULT,
cast(sys.ifthenelse(c."null", 'YES', 'NO') AS varchar(3)) AS IS_NULLABLE,
- c."type" AS DATA_TYPE,
+ CASE c."type" WHEN 'day_interval' THEN 'interval day' WHEN 'month_interval'
THEN 'interval month' WHEN 'sec_interval' THEN 'interval second' ELSE c."type"
END AS DATA_TYPE,
_______________________________________________
checkin-list mailing list -- checkin-list@monetdb.org
To unsubscribe send an email to checkin-list-le...@monetdb.org
