Changeset: e7f48f867485 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/e7f48f867485
Branch: default
Log Message:
merged
diffs (truncated from 2423 to 300 lines):
diff --git a/clients/odbc/tests/ODBCmetadata.c
b/clients/odbc/tests/ODBCmetadata.c
--- a/clients/odbc/tests/ODBCmetadata.c
+++ b/clients/odbc/tests/ODBCmetadata.c
@@ -503,11 +503,12 @@ main(int argc, char **argv)
(SQLCHAR*)SQL_ALL_TABLE_TYPES, SQL_NTS);
compareResult(stmt, ret, "SQLTables (SQL_ALL_TABLE_TYPES)",
"Resultset with 5 columns\n"
- "Resultset with 10 rows\n"
+ "Resultset with 11 rows\n"
"TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE
REMARKS\n"
"WVARCHAR(1) WVARCHAR(1) WVARCHAR(1) WVARCHAR(25)
WVARCHAR(1)\n"
"NULL NULL NULL GLOBAL TEMPORARY TABLE NULL\n"
"NULL NULL NULL LOCAL TEMPORARY TABLE NULL\n"
+ "NULL NULL NULL LOCAL TEMPORARY VIEW NULL\n"
"NULL NULL NULL MERGE TABLE NULL\n"
"NULL NULL NULL REMOTE TABLE NULL\n"
"NULL NULL NULL REPLICA TABLE NULL\n"
@@ -688,8 +689,8 @@ main(int argc, char **argv)
"Resultset with 2 rows\n"
"TABLE_CAT TABLE_SCHEM TABLE_NAME NON_UNIQUE
INDEX_QUALIFIER INDEX_NAME TYPE ORDINAL_POSITION COLUMN_NAME
ASC_OR_DESC CARDINALITY PAGES FILTER_CONDITION\n"
"WVARCHAR(1) WVARCHAR(1024) WVARCHAR(1024) SMALLINT
WVARCHAR(1) WVARCHAR(1024) SMALLINT SMALLINT WVARCHAR(1024)
WCHAR(1) INTEGER INTEGER WVARCHAR(1)\n"
- "NULL sys table_types 0 NULL
table_types_table_type_id_pkey 2 1 table_type_id NULL 10
NULL NULL\n"
- "NULL sys table_types 0 NULL
table_types_table_type_name_unique 2 1 table_type_name NULL
10 NULL NULL\n");
+ "NULL sys table_types 0 NULL
table_types_table_type_id_pkey 2 1 table_type_id NULL 11
NULL NULL\n"
+ "NULL sys table_types 0 NULL
table_types_table_type_name_unique 2 1 table_type_name NULL
11 NULL NULL\n");
ret = SQLStatistics(stmt, (SQLCHAR*)"", SQL_NTS,
(SQLCHAR*)"sys", SQL_NTS, (SQLCHAR*)"table_types",
SQL_NTS,
@@ -699,8 +700,8 @@ main(int argc, char **argv)
"Resultset with 2 rows\n"
"TABLE_CAT TABLE_SCHEM TABLE_NAME NON_UNIQUE
INDEX_QUALIFIER INDEX_NAME TYPE ORDINAL_POSITION COLUMN_NAME
ASC_OR_DESC CARDINALITY PAGES FILTER_CONDITION\n"
"WVARCHAR(1) WVARCHAR(1024) WVARCHAR(1024) SMALLINT
WVARCHAR(1) WVARCHAR(1024) SMALLINT SMALLINT WVARCHAR(1024)
WCHAR(1) INTEGER INTEGER WVARCHAR(1)\n"
- "NULL sys table_types 0 NULL
table_types_table_type_id_pkey 2 1 table_type_id NULL 10
NULL NULL\n"
- "NULL sys table_types 0 NULL
table_types_table_type_name_unique 2 1 table_type_name NULL
10 NULL NULL\n");
+ "NULL sys table_types 0 NULL
table_types_table_type_id_pkey 2 1 table_type_id NULL 11
NULL NULL\n"
+ "NULL sys table_types 0 NULL
table_types_table_type_name_unique 2 1 table_type_name NULL
11 NULL NULL\n");
ret = SQLTablePrivileges(stmt, (SQLCHAR*)"", SQL_NTS,
(SQLCHAR*)"sys", SQL_NTS, (SQLCHAR*)"table_types",
SQL_NTS);
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
@@ -4755,6 +4755,124 @@ sql_update_mar2025(Client c, mvc *sql, s
return err;
}
+static str
+sql_update_mar2025_sp1(Client c, mvc *sql)
+{
+ size_t bufsize = 65536, pos = 0;
+ char *err = NULL, *buf = GDKmalloc(bufsize);
+ res_table *output = NULL;
+ BAT *b;
+
+ if (buf == NULL)
+ throw(SQL, __func__, SQLSTATE(HY013) MAL_MALLOC_FAIL);
+
+ /* 10_sys_schema_extension.sql */
+ /* if the table type LOCAL TEMPORARY VIEW is
+ * not in the list of table_types, upgrade */
+ pos = snprintf(buf, bufsize, "select table_type_name from
sys.table_types where table_type_name = 'LOCAL TEMPORARY VIEW';\n");
+ assert(pos < bufsize);
+ err = SQLstatementIntern(c, buf, "update", true, false, &output);
+ if (err == MAL_SUCCEED && (b = BBPquickdesc(output->cols[0].b)) &&
BATcount(b) == 0) {
+ pos = snprintf(buf, bufsize,
+ "ALTER TABLE sys.table_types SET READ WRITE;\n"
+ "INSERT INTO sys.table_types VALUES (31, 'LOCAL
TEMPORARY VIEW');\n");
+ assert(pos < bufsize);
+ printf("Running database upgrade commands:\n%s\n", buf);
+ fflush(stdout);
+ err = SQLstatementIntern(c, buf, "update", true, false, NULL);
+ if (err == MAL_SUCCEED) {
+ pos = snprintf(buf, bufsize, "ALTER TABLE
sys.table_types SET READ ONLY;\n");
+ assert(pos < bufsize);
+ printf("Running database upgrade commands:\n%s\n", buf);
+ fflush(stdout);
+ err = SQLstatementIntern(c, buf, "update", true, false,
NULL);
+ }
+
+ /* 91_information_schema.sql */
+ /* correct definitions of views: information_schema.tables and
information_schema.views */
+ if (err == MAL_SUCCEED) {
+ const char query[] =
+ "DROP VIEW information_schema.views CASCADE;\n"
+ "DROP VIEW information_schema.tables CASCADE;\n"
+
+ "CREATE VIEW INFORMATION_SCHEMA.TABLES AS
SELECT\n"
+ " cast(NULL AS varchar(1)) AS TABLE_CATALOG,\n"
+ " s.\"name\" AS TABLE_SCHEMA,\n"
+ " t.\"name\" AS TABLE_NAME,\n"
+ " tt.\"table_type_name\" AS TABLE_TYPE,\n"
+ " cast(NULL AS varchar(1)) AS
SELF_REFERENCING_COLUMN_NAME,\n"
+ " cast(NULL AS varchar(1)) AS
REFERENCE_GENERATION,\n"
+ " cast(NULL AS varchar(1)) AS
USER_DEFINED_TYPE_CATALOG,\n"
+ " cast(NULL AS varchar(1)) AS
USER_DEFINED_TYPE_SCHEMA,\n"
+ " cast(NULL AS varchar(1)) AS
USER_DEFINED_TYPE_NAME,\n"
+ " cast(sys.ifthenelse((t.\"type\" IN (0, 3, 7,
20, 30) AND t.\"access\" IN (0, 2)), 'YES', 'NO') AS varchar(3)) AS
IS_INSERTABLE_INTO,\n"
+ " cast('NO' AS varchar(3)) AS IS_TYPED,\n"
+ " cast((CASE t.\"commit_action\" WHEN 1 THEN
'DELETE' WHEN 2 THEN 'PRESERVE' WHEN 3 THEN 'DROP' ELSE NULL END) AS
varchar(10)) AS COMMIT_ACTION,\n"
+ " -- MonetDB column extensions\n"
+ " t.\"schema_id\" AS schema_id,\n"
+ " t.\"id\" AS table_id,\n"
+ " t.\"type\" AS table_type_id,\n"
+ " st.\"count\" AS row_count,\n"
+ " t.\"system\" AS is_system,\n"
+ " sys.ifthenelse(t.\"type\" IN (1, 11, 21,
31), TRUE, FALSE) AS is_view,\n"
+ " t.\"query\" AS query_def,\n"
+ " cm.\"remark\" AS comments\n"
+ " FROM sys.\"tables\" t\n"
+ " INNER JOIN sys.\"schemas\" s ON
t.\"schema_id\" = s.\"id\"\n"
+ " INNER JOIN sys.\"table_types\" tt ON
t.\"type\" = tt.\"table_type_id\"\n"
+ " LEFT OUTER JOIN sys.\"comments\" cm ON
t.\"id\" = cm.\"id\"\n"
+ " LEFT OUTER JOIN (SELECT DISTINCT \"schema\",
\"table\", \"count\" FROM sys.\"statistics\"()) st ON (s.\"name\" =
st.\"schema\" AND t.\"name\" = st.\"table\")\n"
+ " ORDER BY s.\"name\", t.\"name\";\n"
+ "GRANT SELECT ON TABLE
INFORMATION_SCHEMA.TABLES TO PUBLIC WITH GRANT OPTION;\n"
+
+ "CREATE VIEW INFORMATION_SCHEMA.VIEWS AS
SELECT\n"
+ " cast(NULL AS varchar(1)) AS TABLE_CATALOG,\n"
+ " s.\"name\" AS TABLE_SCHEMA,\n"
+ " t.\"name\" AS TABLE_NAME,\n"
+ " t.\"query\" AS VIEW_DEFINITION,\n"
+ " cast('NONE' AS varchar(10)) AS
CHECK_OPTION,\n"
+ " cast('NO' AS varchar(3)) AS IS_UPDATABLE,\n"
+ " cast('NO' AS varchar(3)) AS
INSERTABLE_INTO,\n"
+ " cast('NO' AS varchar(3)) AS
IS_TRIGGER_UPDATABLE,\n"
+ " cast('NO' AS varchar(3)) AS
IS_TRIGGER_DELETABLE,\n"
+ " cast('NO' AS varchar(3)) AS
IS_TRIGGER_INSERTABLE_INTO,\n"
+ " -- MonetDB column extensions\n"
+ " t.\"schema_id\" AS schema_id,\n"
+ " t.\"id\" AS table_id,\n"
+ " t.\"type\" AS table_type_id,\n"
+ " t.\"system\" AS is_system,\n"
+ " cm.\"remark\" AS comments\n"
+ " FROM sys.\"tables\" t\n"
+ " INNER JOIN sys.\"schemas\" s ON
t.\"schema_id\" = s.\"id\"\n"
+ " LEFT OUTER JOIN sys.\"comments\" cm ON
t.\"id\" = cm.\"id\"\n"
+ " WHERE t.\"type\" IN (1, 11, 21, 31)\n"
+ " ORDER BY s.\"name\", t.\"name\";\n"
+ "GRANT SELECT ON TABLE INFORMATION_SCHEMA.VIEWS
TO PUBLIC WITH GRANT OPTION;\n"
+ "\n"
+ "UPDATE sys._tables SET system = true where
system <> true\n"
+ " and schema_id = (select s.id from sys.schemas
s where s.name = 'information_schema')\n"
+ " and name in ('tables','views');\n";
+
+ sql_schema *infoschema = mvc_bind_schema(sql,
"information_schema");
+ sql_table *t;
+ if ((t = mvc_bind_table(sql, infoschema, "tables")) !=
NULL)
+ t->system = 0; /* make it non-system else the
drop view will fail */
+ if ((t = mvc_bind_table(sql, infoschema, "views")) !=
NULL)
+ t->system = 0;
+ printf("Running database upgrade commands:\n%s\n",
query);
+ fflush(stdout);
+ err = SQLstatementIntern(c, query, "update", true,
false, NULL);
+ }
+ }
+ if (output != NULL) {
+ res_table_destroy(output);
+ output = NULL;
+ }
+
+ return err;
+}
+
+
int
SQLupgrades(Client c, mvc *m)
{
@@ -4823,6 +4941,11 @@ SQLupgrades(Client c, mvc *m)
goto handle_error;
}
+ if ((err = sql_update_mar2025_sp1(c, m)) != NULL) {
+ TRC_CRITICAL(SQL_PARSER, "%s\n", err);
+ goto handle_error;
+ }
+
return 0;
handle_error:
diff --git a/sql/scripts/10_sys_schema_extension.sql
b/sql/scripts/10_sys_schema_extension.sql
--- a/sql/scripts/10_sys_schema_extension.sql
+++ b/sql/scripts/10_sys_schema_extension.sql
@@ -345,7 +345,8 @@ INSERT INTO sys.table_types (table_type_
-- sys._tables.type value depending on values of temporary and
-- commit_action).
(20, 'GLOBAL TEMPORARY TABLE'),
- (30, 'LOCAL TEMPORARY TABLE');
+ (30, 'LOCAL TEMPORARY TABLE'),
+ (31, 'LOCAL TEMPORARY VIEW');
ALTER TABLE sys.table_types SET READ ONLY;
GRANT SELECT ON sys.table_types TO PUBLIC;
diff --git a/sql/scripts/91_information_schema.sql
b/sql/scripts/91_information_schema.sql
--- a/sql/scripts/91_information_schema.sql
+++ b/sql/scripts/91_information_schema.sql
@@ -80,7 +80,7 @@ CREATE VIEW INFORMATION_SCHEMA.TABLES AS
t."type" AS table_type_id,
st."count" AS row_count,
t."system" AS is_system,
- sys.ifthenelse(t."type" IN (1, 11), TRUE, FALSE) AS is_view,
+ sys.ifthenelse(t."type" IN (1, 11, 21, 31), TRUE, FALSE) AS is_view,
t."query" AS query_def,
cm."remark" AS comments
FROM sys."tables" t
@@ -109,13 +109,13 @@ CREATE VIEW INFORMATION_SCHEMA.VIEWS AS
-- MonetDB column extensions
t."schema_id" AS schema_id,
t."id" AS table_id,
- cast(sys.ifthenelse(t."system", t."type" + 10 /* system view */, t."type")
AS smallint) AS table_type_id,
+ t."type" AS table_type_id,
t."system" AS is_system,
cm."remark" AS comments
- FROM sys."_tables" t
+ FROM sys."tables" t
INNER JOIN sys."schemas" s ON t."schema_id" = s."id"
LEFT OUTER JOIN sys."comments" cm ON t."id" = cm."id"
- WHERE t."type" = 1
+ WHERE t."type" IN (1, 11, 21, 31)
ORDER BY s."name", t."name";
GRANT SELECT ON TABLE INFORMATION_SCHEMA.VIEWS TO PUBLIC WITH GRANT OPTION;
diff --git a/sql/server/sql_parser.y b/sql/server/sql_parser.y
--- a/sql/server/sql_parser.y
+++ b/sql/server/sql_parser.y
@@ -2289,6 +2289,17 @@ view_def:
append_int(l, $1);
$$ = _symbol_create_list( SQL_CREATE_VIEW, l );
}
+ | CREATE OR REPLACE opt_temp VIEW qname opt_column_list AS SelectStmt
opt_with_check_option
+ { dlist *l = L();
+ append_int(l, SQL_PERSIST);
+ append_list(l, $6);
+ append_list(l, $7);
+ append_symbol(l, $9);
+ append_int(l, $10);
+ append_int(l, TRUE); /* persistent view */
+ append_int(l, $4);
+ $$ = _symbol_create_list( SQL_CREATE_VIEW, l );
+ }
;
opt_with_check_option:
diff --git
a/sql/test/BugTracker-2025/Tests/7625_missing_temporary_view_types.test
b/sql/test/BugTracker-2025/Tests/7625_missing_temporary_view_types.test
new file mode 100644
--- /dev/null
+++ b/sql/test/BugTracker-2025/Tests/7625_missing_temporary_view_types.test
@@ -0,0 +1,224 @@
+query TITIIIII nosort
+select name, schema_id, query, type, system, commit_action, access, temporary
from sys.tables where not system
+----
+
+query IT
+select * from sys.table_types order by 1
+----
+0
+TABLE
+1
+VIEW
+3
+MERGE TABLE
+5
+REMOTE TABLE
+6
+REPLICA TABLE
+7
+UNLOGGED TABLE
+10
+SYSTEM TABLE
+11
+SYSTEM VIEW
+20
+GLOBAL TEMPORARY TABLE
+30
+LOCAL TEMPORARY TABLE
+31
+LOCAL TEMPORARY VIEW
+
+--DROP VIEW v
+statement ok
+CREATE VIEW v AS select name, schema_id, query, type, system, commit_action,
access, temporary from sys.tables where not system
+
+query TITIIIII nosort
+select * from v order by schema_id, name
+----
+v
+2000
+create view v as select name, schema_id, query, type, system, commit_action,
access, temporary from sys.tables where not system@;
+1
+0
+0
+0
+0
+
+--DROP VIEW tv
+statement ok
+CREATE TEMPORARY VIEW tv AS select name, schema_id, query, type, system,
commit_action, access, temporary from sys.tables where not system
+
+query TITIIIII nosort
+select * from tv order by schema_id, name
+----
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
