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

Invalid references to sys.columns.id from sys.statistics.column_id #6439

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

Invalid references to sys.columns.id from sys.statistics.column_id #6439

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

Comments

@monetdb-team
Copy link

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

Date: 2017-10-19 15:26:18 +0200
From: Martin van Dinther <<martin.van.dinther>>
To: SQL devs <>
Version: 11.27.5 (Jul2017-SP1)
CC: akkaran046, @njnes

Last updated: 2019-02-12 14:00:43 +0100

Comment 25766

Date: 2017-10-19 15:26:18 +0200
From: Martin van Dinther <<martin.van.dinther>>

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

Analyze schema.table will populate or update rows in table sys.statistics.
However when an analysed table or column is dropped the referencing rows in sys.statistics are not dropped also! This creates invalid Foreign Key references to column ids which do not exist in sys.columns.
The query:
select * from sys.statistics where column_id not in (select id from sys.columns);
will than return rows, which indicates inconsistencies in the data and corresponding problems when joining with sys.columns.

As those rows are invalid (and useless) they should be deleted automatically when a column or table (including all its columns) is dropped.

Reproducible: Always

Steps to Reproduce:

CREATE TABLE sys.abc (a INT, b VARCHAR(10));
INSERT INTO sys.abc VALUES (1, 'one');
INSERT INTO sys.abc VALUES (2, 'two');
SELECT * FROM sys.abc;

DELETE FROM sys.statistics;

ANALYZE sys.abc;
SELECT /* column_id, / type, width, / stamp, / "sample", "count", "unique", nils, minval, maxval, sorted, revsorted FROM sys.statistics;
-- expected 2 rows
SELECT /
column_id, / type, width, / stamp, */ "sample", "count", "unique", nils, minval, maxval, sorted, revsorted FROM sys.statistics where column_id not in (select id from sys.columns);
-- expected 0 rows

ALTER TABLE sys.abc DROP COLUMN b;
SELECT /* column_id, / type, width, / stamp, */ "sample", "count", "unique", nils, minval, maxval, sorted, revsorted FROM sys.statistics where column_id not in (select id from sys.columns);
-- expected 0 rows but found 1 row !

DROP TABLE sys.abc CASCADE;
SELECT /* column_id, / type, width, / stamp, */ "sample", "count", "unique", nils, minval, maxval, sorted, revsorted FROM sys.statistics where column_id not in (select id from sys.columns);
-- expected 0 rows but found 2 rows !

SELECT /* column_id, / type, width, / stamp, */ "sample", "count", "unique", nils, minval, maxval, sorted, revsorted FROM sys.statistics;
-- expected 0 rows but found 2 rows !
DELETE FROM sys.statistics;

Actual Results:

bash-4.4$ mclient -p41000
Welcome to mclient, the MonetDB/SQL interactive terminal (unreleased)
Database: MonetDB v11.27.8 (unreleased), 'demo'
Type \q to quit, ? for a list of available commands
auto commit mode: on
sql>CREATE TABLE sys.abc (a INT, b VARCHAR(10));
operation successful (11.158ms)
sql>INSERT INTO sys.abc VALUES (1, 'one');
1 affected row (2.768ms)
sql>INSERT INTO sys.abc VALUES (2, 'two');
1 affected row (2.228ms)
sql>SELECT * FROM sys.abc;
+------+------+
| a | b |
+======+======+
| 1 | one |
| 2 | two |
+------+------+
2 tuples (2.361ms)
sql>
sql>DELETE FROM sys.statistics;
0 affected rows (2.348ms)
sql>
sql>ANALYZE sys.abc;
sql>SELECT /* column_id, / type, width, / stamp, / "sample", "count", "unique", nils, minval, maxval, sorted, revsorted FROM sys.statistics;
+---------+-------+--------+-------+--------+------+--------+--------+--------+-----------+
| type | width | sample | count | unique | nils | minval | maxval | sorted | revsorted |
+=========+=======+========+=======+========+======+========+========+========+===========+
| int | 4 | 2 | 2 | 2 | 0 | 1 | 2 | true | false |
| varchar | 1 | 2 | 2 | 2 | 0 | "one" | "two" | true | false |
+---------+-------+--------+-------+--------+------+--------+--------+--------+-----------+
2 tuples (4.628ms)
sql>-- expected 2 rows
sql>SELECT /
column_id, / type, width, / stamp, / "sample", "count", "unique", nils, minval, maxval, sorted, revsorted FROM sys.statistics where column_id not in (select id from sys.columns);
+------+-------+--------+-------+--------+------+--------+--------+--------+-----------+
| type | width | sample | count | unique | nils | minval | maxval | sorted | revsorted |
+======+=======+========+=======+========+======+========+========+========+===========+
+------+-------+--------+-------+--------+------+--------+--------+--------+-----------+
0 tuples (5.592ms)
sql>-- expected 0 rows
sql>
sql>ALTER TABLE sys.abc DROP COLUMN b;
operation successful (2.217ms)
sql>SELECT /
column_id, / type, width, / stamp, / "sample", "count", "unique", nils, minval, maxval, sorted, revsorted FROM sys.statistics where column_id not in (select id from sys.columns);
+---------+-------+--------+-------+--------+------+--------+--------+--------+-----------+
| type | width | sample | count | unique | nils | minval | maxval | sorted | revsorted |
+=========+=======+========+=======+========+======+========+========+========+===========+
| varchar | 1 | 2 | 2 | 2 | 0 | "one" | "two" | true | false |
+---------+-------+--------+-------+--------+------+--------+--------+--------+-----------+
1 tuple (10.743ms)
sql>-- expected 0 rows but found 1 row !
sql>
sql>DROP TABLE sys.abc CASCADE;
operation successful (2.016ms)
sql>SELECT /
column_id, / type, width, / stamp, / "sample", "count", "unique", nils, minval, maxval, sorted, revsorted FROM sys.statistics where column_id not in (select id from sys.columns);
+---------+-------+--------+-------+--------+------+--------+--------+--------+-----------+
| type | width | sample | count | unique | nils | minval | maxval | sorted | revsorted |
+=========+=======+========+=======+========+======+========+========+========+===========+
| int | 4 | 2 | 2 | 2 | 0 | 1 | 2 | true | false |
| varchar | 1 | 2 | 2 | 2 | 0 | "one" | "two" | true | false |
+---------+-------+--------+-------+--------+------+--------+--------+--------+-----------+
2 tuples (5.546ms)
sql>-- expected 0 rows but found 2 rows !
sql>
sql>SELECT /
column_id, / type, width, / stamp, */ "sample", "count", "unique", nils, minval, maxval, sorted, revsorted FROM sys.statistics;
+---------+-------+--------+-------+--------+------+--------+--------+--------+-----------+
| type | width | sample | count | unique | nils | minval | maxval | sorted | revsorted |
+=========+=======+========+=======+========+======+========+========+========+===========+
| int | 4 | 2 | 2 | 2 | 0 | 1 | 2 | true | false |
| varchar | 1 | 2 | 2 | 2 | 0 | "one" | "two" | true | false |
+---------+-------+--------+-------+--------+------+--------+--------+--------+-----------+
2 tuples (2.838ms)
sql>-- expected 0 rows but found 2 rows !
sql>DELETE FROM sys.statistics;
2 affected rows (1.727ms)
sql>

Expected Results:

bash-4.4$ mclient -p41000
Welcome to mclient, the MonetDB/SQL interactive terminal (unreleased)
Database: MonetDB v11.27.8 (unreleased), 'demo'
Type \q to quit, ? for a list of available commands
auto commit mode: on
sql>CREATE TABLE sys.abc (a INT, b VARCHAR(10));
operation successful (11.158ms)
sql>INSERT INTO sys.abc VALUES (1, 'one');
1 affected row (2.768ms)
sql>INSERT INTO sys.abc VALUES (2, 'two');
1 affected row (2.228ms)
sql>SELECT * FROM sys.abc;
+------+------+
| a | b |
+======+======+
| 1 | one |
| 2 | two |
+------+------+
2 tuples (2.361ms)
sql>
sql>DELETE FROM sys.statistics;
0 affected rows (2.348ms)
sql>
sql>ANALYZE sys.abc;
sql>SELECT /* column_id, / type, width, / stamp, / "sample", "count", "unique", nils, minval, maxval, sorted, revsorted FROM sys.statistics;
+---------+-------+--------+-------+--------+------+--------+--------+--------+-----------+
| type | width | sample | count | unique | nils | minval | maxval | sorted | revsorted |
+=========+=======+========+=======+========+======+========+========+========+===========+
| int | 4 | 2 | 2 | 2 | 0 | 1 | 2 | true | false |
| varchar | 1 | 2 | 2 | 2 | 0 | "one" | "two" | true | false |
+---------+-------+--------+-------+--------+------+--------+--------+--------+-----------+
2 tuples (4.628ms)
sql>-- expected 2 rows
sql>SELECT /
column_id, / type, width, / stamp, / "sample", "count", "unique", nils, minval, maxval, sorted, revsorted FROM sys.statistics where column_id not in (select id from sys.columns);
+------+-------+--------+-------+--------+------+--------+--------+--------+-----------+
| type | width | sample | count | unique | nils | minval | maxval | sorted | revsorted |
+======+=======+========+=======+========+======+========+========+========+===========+
+------+-------+--------+-------+--------+------+--------+--------+--------+-----------+
0 tuples (5.592ms)
sql>-- expected 0 rows
sql>
sql>ALTER TABLE sys.abc DROP COLUMN b;
operation successful (2.217ms)
sql>SELECT /
column_id, / type, width, / stamp, / "sample", "count", "unique", nils, minval, maxval, sorted, revsorted FROM sys.statistics where column_id not in (select id from sys.columns);
+------+-------+--------+-------+--------+------+--------+--------+--------+-----------+
| type | width | sample | count | unique | nils | minval | maxval | sorted | revsorted |
+======+=======+========+=======+========+======+========+========+========+===========+
+------+-------+--------+-------+--------+------+--------+--------+--------+-----------+
0 tuples (5.592ms)
sql>
sql>DROP TABLE sys.abc CASCADE;
operation successful (2.016ms)
sql>SELECT /
column_id, / type, width, / stamp, / "sample", "count", "unique", nils, minval, maxval, sorted, revsorted FROM sys.statistics where column_id not in (select id from sys.columns);
+------+-------+--------+-------+--------+------+--------+--------+--------+-----------+
| type | width | sample | count | unique | nils | minval | maxval | sorted | revsorted |
+======+=======+========+=======+========+======+========+========+========+===========+
+------+-------+--------+-------+--------+------+--------+--------+--------+-----------+
0 tuples (5.592ms)
sql>
sql>SELECT /
column_id, / type, width, / stamp, */ "sample", "count", "unique", nils, minval, maxval, sorted, revsorted FROM sys.statistics;
+------+-------+--------+-------+--------+------+--------+--------+--------+-----------+
| type | width | sample | count | unique | nils | minval | maxval | sorted | revsorted |
+======+=======+========+=======+========+======+========+========+========+===========+
+------+-------+--------+-------+--------+------+--------+--------+--------+-----------+
0 tuples (5.592ms)
sql>DELETE FROM sys.statistics;
0 affected rows (1.727ms)
sql>

Comment 25914

Date: 2017-11-26 17:53:49 +0100
From: @njnes

added code to drop the rows from the statistics table (after column/table drops)

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