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)