Date: 2014-07-25 13:07:23 +0200
From: Martin van Dinther <<martin.van.dinther>>
To: SQL devs <>
Version: 11.17.17 (Jan2014-SP2)
CC: @njnes

Last updated: 2014-10-31 14:13:53 +0100

Comment 19930

Date: 2014-07-25 13:07:23 +0200
From: Martin van Dinther <<martin.van.dinther>>

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

sys.tables is defined as view:
SELECT * FROM (SELECT p., 0 AS "temporary" FROM "sys"."_tables" AS p UNION ALL SELECT t., 1 AS "temporary" FROM "tmp"."_tables" AS t) AS tables where tables.type <> 2;

sys.columns is defined as view:
SELECT * FROM (SELECT p.* FROM "sys"."_columns" AS p UNION ALL SELECT t.* FROM "tmp"."_columns" AS t) AS columns;

The view definition of sys.columns needs to be corrected to exclude rows where table_id does NOT exist in sys.tables.

Reproducible: Always

Steps to Reproduce:

1. start mserver5 ( MonetDB 5 server v11.17.17 "Jan2014-SP2", database demo)
2. start mclient
3. Enter SQL query:
   SELECT id, name, schema_id, type from sys._tables WHERE id IN (SELECT table_id FROM sys.columns WHERE table_id NOT IN (SELECT id from sys.tables));

Actual Results:

sql>SELECT id, name, schema_id, type from sys._tables WHERE id IN (SELECT table_id FROM sys.columns WHERE table_id NOT IN (SELECT id from sys.tables));
+------+--------------------------------------+-----------+------+
| id | name | schema_id | type |
+======+======================================+===========+======+
| 5098 | env | 2000 | 2 |
| 5103 | var | 2000 | 2 |
| 5111 | db_users | 2000 | 2 |
| 5270 | querylog_catalog | 2000 | 2 |
| 5281 | querylog_calls | 2000 | 2 |
| 5343 | tracelog | 2000 | 2 |
| 5419 | dependencies_schemas_on_users | 2000 | 2 |
| 5426 | dependencies_owners_on_schemas | 2000 | 2 |
| 5433 | dependencies_tables_on_views | 2000 | 2 |
| 5440 | dependencies_tables_on_indexes | 2000 | 2 |
| 5447 | dependencies_tables_on_triggers | 2000 | 2 |
| 5454 | dependencies_tables_on_foreignkeys | 2000 | 2 |
| 5461 | dependencies_tables_on_functions | 2000 | 2 |
| 5468 | dependencies_columns_on_views | 2000 | 2 |
| 5475 | dependencies_columns_on_keys | 2000 | 2 |
| 5482 | dependencies_columns_on_indexes | 2000 | 2 |
| 5489 | dependencies_columns_on_functions | 2000 | 2 |
| 5496 | dependencies_columns_on_triggers | 2000 | 2 |
| 5503 | dependencies_views_on_functions | 2000 | 2 |
| 5510 | dependencies_views_on_triggers | 2000 | 2 |
| 5517 | dependencies_functions_on_functions | 2000 | 2 |
| 5524 | dependencies_functions_os_triggers | 2000 | 2 |
| 5531 | dependencies_keys_on_foreignkeys | 2000 | 2 |
| 5542 | sessions | 2000 | 2 |
| 5619 | optimizer_stats | 2000 | 2 |
| 5625 | querycache | 2000 | 2 |
| 5634 | optimizers | 2000 | 2 |
| 5646 | environment | 2000 | 2 |
| 5656 | bbp | 2000 | 2 |
| 5676 | queue | 2000 | 2 |
| 6215 | storage | 2000 | 2 |
| 6274 | storagemodel | 2000 | 2 |
+------+--------------------------------------+-----------+------+
32 tuples (4.136ms)

Expected Results:

Expected 0 tuples returned.
sys.columns should contain only columns of tables existing in sys.tables else we have a referential data integrity problem.

Comment 19943

Date: 2014-08-03 14:12:17 +0200
From: @njnes

the table returning types used to be stored in the _tables table. This is changed in the default branch, ie will be fixed in the next feature release.

Comment 20343

Date: 2014-10-31 14:13:53 +0100
From: @sjoerdmullender

Oct2014 has been released.