Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

SQL catalog table sys.columns lists columns for table ids which do not exist in sys.tables #3522

Closed
monetdb-team opened this issue Nov 30, 2020 · 0 comments

Comments

@monetdb-team
Copy link

@monetdb-team monetdb-team commented Nov 30, 2020

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant