Date: 2019-01-10 18:32:58 +0100
From: Martin van Dinther <<martin.van.dinther>>
To: SQL devs <>
Version: 11.31.11 (Aug2018-SP1)

Last updated: 2019-04-30 12:36:01 +0200

Comment 26788

Date: 2019-01-10 18:32:58 +0100
From: Martin van Dinther <<martin.van.dinther>>

User-Agent: Mozilla/5.0 (X11; Fedora; Linux x86_64; rv:64.0) Gecko/20100101 Firefox/64.0
Build Identifier:

The system table sys._tables contains character string data in column "query" whose length (2811) is larger than the allowed max length specification of 2048.
The "query" column in tables sys._tables and tmp._tables needs to be created with a much larger maximum size, for instance 65535, such that large view definitions can be created and stored.

Please do not change the data type to clob. It should remain a varchar.

Reproducible: Always

Steps to Reproduce:

\d sys._tables
\d tmp._tables
select length(query), t.* from sys._tables t where length(query) > 2048;
select length(query), t.* from sys._tables t order by length(query) desc limit 3;
select * from columns where name = 'query' and table_id in (select id from tables where system and query is null);

Actual Results:

sql>\d sys._tables
CREATE TABLE "sys"."_tables" (
"id" INTEGER,
"name" VARCHAR(1024),
"schema_id" INTEGER,
"query" VARCHAR(2048),
"type" SMALLINT,
"system" BOOLEAN,
"commit_action" SMALLINT,
"access" SMALLINT
);
sql>\d tmp._tables
CREATE TABLE "tmp"."_tables" (
"id" INTEGER,
"name" VARCHAR(1024),
"schema_id" INTEGER,
"query" VARCHAR(2048),
"type" SMALLINT,
"system" BOOLEAN,
"commit_action" SMALLINT,
"access" SMALLINT
);
sql>select length(query), t.* from _tables t where length(query) > 2048;
+------+------+------+------+-----------------------------------------------------------------------------------------------------+------+-------+------+------+
| L2 | id | name | sche | query | type | syste | comm | acce |
: : : : ma_i : : : m : it_a : ss :
: : : : d : : : : ctio : :
: : : : : : : : n : :
+======+======+======+======+=====================================================================================================+======+=======+======+======+
| 2811 | 6853 | ids | 2000 | create view sys.ids (id, name, schema_id, table_id, table_name, obj_type, sys_table) as | 1 | true | 0 | 0 |
: : : : : select id, name, cast(null as int) as schema_id, cast(null as int) as table_id, cast(null as varcha : : : : :
: : : : : r(124)) as table_name, 'author' as obj_type, 'sys.auths' as sys_table from sys.auths union all : : : : :
: : : : : select id, name, cast(null as int) as schema_id, cast(null as int) as table_id, cast(null as varcha : : : : :
: : : : : r(124)) as table_name, 'schema', 'sys.schemas' from sys.schemas union all : : : : :
: : : : : select id, name, schema_id, id as table_id, name as table_name, case when type = 1 then 'view' else : : : : :
: : : : : 'table' end, 'sys._tables' from sys._tables union all : : : : :
: : : : : select id, name, schema_id, id as table_id, name as table_name, case when type = 1 then 'view' else : : : : :
: : : : : 'table' end, 'tmp._tables' from tmp._tables union all : : : : :
: : : : : select c.id, c.name, t.schema_id, c.table_id, t.name as table_name, 'column', 'sys._columns' from s : : : : :
: : : : : ys._columns c join sys._tables t on c.table_id = t.id union all : : : : :
: : : : : select c.id, c.name, t.schema_id, c.table_id, t.name as table_name, 'column', 'tmp._columns' from t : : : : :
: : : : : mp._columns c join tmp._tables t on c.table_id = t.id union all : : : : :
: : : : : select k.id, k.name, t.schema_id, k.table_id, t.name as table_name, 'key', 'sys.keys' from sys.keys : : : : :
: : : : : k join sys._tables t on k.table_id = t.id union all : : : : :
: : : : : select k.id, k.name, t.schema_id, k.table_id, t.name as table_name, 'key', 'tmp.keys' from tmp.keys : : : : :
: : : : : k join tmp._tables t on k.table_id = t.id union all : : : : :
: : : : : select i.id, i.name, t.schema_id, i.table_id, t.name as table_name, 'index', 'sys.idxs' from sys.id : : : : :
: : : : : xs i join sys._tables t on i.table_id = t.id union all : : : : :
: : : : : select i.id, i.name, t.schema_id, i.table_id, t.name as table_name, 'index', 'tmp.idxs' from tmp.id : : : : :
: : : : : xs i join tmp._tables t on i.table_id = t.id union all : : : : :
: : : : : select g.id, g.name, t.schema_id, g.table_id, t.name as table_name, 'trigger', 'sys.triggers' from : : : : :
: : : : : sys.triggers g join sys._tables t on g.table_id = t.id union all : : : : :
: : : : : select g.id, g.name, t.schema_id, g.table_id, t.name as table_name, 'trigger', 'tmp.triggers' from : : : : :
: : : : : tmp.triggers g join tmp._tables t on g.table_id = t.id union all : : : : :
: : : : : select id, name, schema_id, cast(null as int) as table_id, cast(null as varchar(124)) as table_name : : : : :
: : : : : , case when type = 2 then 'procedure' else 'function' end, 'sys.functions' from sys.functions union : : : : :
: : : : : all : : : : :
: : : : : select a.id, a.name, f.schema_id, cast(null as int) as table_id, cast(null as varchar(124)) as ta...> : : : :
+------+------+------+------+-----------------------------------------------------------------------------------------------------+------+-------+------+------+
1 tuple !1 field truncated!
sql>select * from columns where name = 'query' and table_id in (select id from tables where system and query is null);
+------+-------+---------+-------------+------------+----------+---------+-------+--------+---------+
| id | name | type | type_digits | type_scale | table_id | default | null | number | storage |
+======+=======+=========+=============+============+==========+=========+=======+========+=========+
| 2071 | query | varchar | 2048 | 0 | 2067 | null | true | 3 | null |
| 2119 | query | varchar | 2048 | 0 | 2115 | null | true | 3 | null |
+------+-------+---------+-------------+------------+----------+---------+-------+--------+---------+
2 tuples

Expected Results:

The "query" column in tables sys._tables and tmp._tables needs to be created with a much larger maximum size, for instance 65535.

Comment 26789

Date: 2019-01-10 19:08:42 +0100
From: Martin van Dinther <<martin.van.dinther>>

Addendum: It is not required to change the tmp._tables.query column max length as it will never be populated with data (create temporary views is not supported), so it can not exceed the current maximum length of 2048.

Comment 26801

Date: 2019-01-17 12:36:07 +0100
From: MonetDB Mercurial Repository <>

Changeset 759bcf8796d8 made by Sjoerd Mullender sjoerd@acm.org in the MonetDB repo, refers to this bug.

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=759bcf8796d8

Changeset description:

Increase maximum length of query column in sys._tables to 1 MiB.
This fixes bug #6676.

Comment 26802

Date: 2019-01-17 12:36:53 +0100
From: @sjoerdmullender

I increased the size to 1 MiB. 64 KiB still seems a bit small.

Comment 26803

Date: 2019-01-17 12:39:07 +0100
From: @sjoerdmullender

(In reply to Martin van Dinther from comment 1)

Addendum: It is not required to change the tmp._tables.query column max
length as it will never be populated with data (create temporary views is
not supported), so it can not exceed the current maximum length of 2048.

It may not be necessary to increase the size for tmp._tables.query, but I did anyway. It's easier since the two are created with the same lines of code.