Date: 2018-03-22 19:02:19 +0100
From: Martin van Dinther <<martin.van.dinther>>
To: SQL devs <>
Version: 11.27.13 (Jul2017-SP4)
CC: @njnes

Last updated: 2018-03-29 15:39:15 +0200

Comment 26275

Date: 2018-03-22 19:02:19 +0100
From: Martin van Dinther <<martin.van.dinther>>

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

after creation of a temporary table and analysing its columns data (such that sys.statistics is populated) and next dropping the temporary table explicitly, the associated rows in sys.statistics are not automatically dropped also.

Reproducible: Always

Steps to Reproduce:

Run SQL:
DELETE FROM sys.statistics WHERE column_id NOT IN (SELECT id FROM sys.columns);
SELECT COUNT(*) as COUNT FROM sys.statistics WHERE column_id NOT IN (SELECT id FROM sys.columns);
-- should be 0

CREATE TABLE tbl_normal (id int, name varchar(99));
SELECT * FROM tbl_normal;

ANALYZE sys.tbl_normal;
SELECT COUNT(*) as COUNT FROM sys.statistics WHERE column_id NOT IN (SELECT id FROM sys.columns);
-- should be o
SELECT type, width, count, "unique", nils FROM sys.statistics WHERE column_id NOT IN (SELECT id FROM sys.columns);

CREATE TEMP TABLE tbl_temp as SELECT * FROM sys.statistics ON COMMIT PRESERVE ROWS;
SELECT * FROM tbl_temp;
SELECT * FROM tmp.tbl_temp;

SELECT name, type, "system", commit_action, access FROM tmp._tables;
-- shows 1 row
SELECT name, type, type_digits, type_scale, "null", number FROM tmp._columns;
-- shows 12 rows

ANALYZE tmp.tbl_temp;
SELECT COUNT() as COUNT FROM sys.statistics;
SELECT COUNT() as COUNT FROM sys.statistics WHERE column_id NOT IN (SELECT id FROM sys.columns);
-- SELECT * FROM sys.statistics;
SELECT type, width, count, "unique", nils FROM sys.statistics WHERE column_id NOT IN (SELECT id FROM sys.columns);

DROP TABLE tbl_normal;

SELECT COUNT() as COUNT FROM sys.statistics;
SELECT COUNT() as COUNT FROM sys.statistics WHERE column_id NOT IN (SELECT id FROM sys.columns);
SELECT type, width, count, "unique", nils FROM sys.statistics WHERE column_id NOT IN (SELECT id FROM sys.columns);

DROP TABLE tmp.tbl_temp;
DROP TABLE IF EXISTS tmp.tbl_temp;

SELECT * FROM tmp._tables;
SELECT * FROM tmp._columns;

SELECT COUNT() as COUNT FROM sys.statistics;
SELECT COUNT() as COUNT FROM sys.statistics WHERE column_id NOT IN (SELECT id FROM sys.columns);
-- should have been 0
SELECT type, width, count, "unique", nils FROM sys.statistics WHERE column_id NOT IN (SELECT id FROM sys.columns);
-- should have been no rows

Comment 26286

Date: 2018-03-24 15:26:41 +0100
From: @njnes

we should no keep statistics on temporary tables at all.

Comment 26290

Date: 2018-03-25 18:15:16 +0200
From: @njnes

disallowed statistics on temporary tables

Comment 26314

Date: 2018-03-29 15:39:15 +0200
From: @sjoerdmullender

The Mar2018 version has been released.