From 47662077d61527b3b78704ebfaffedb53f53f904 Mon Sep 17 00:00:00 2001 From: jian he Date: Tue, 3 Dec 2024 22:13:55 +0800 Subject: [PATCH v10 1/1] stats_exts regress tests stats_exts regress tests for virtual generated columns, system columns, columns no less than operator --- src/test/regress/expected/stats_ext.out | 22 ++++++++++++++++++++++ src/test/regress/sql/stats_ext.sql | 14 ++++++++++++++ 2 files changed, 36 insertions(+) diff --git a/src/test/regress/expected/stats_ext.out b/src/test/regress/expected/stats_ext.out index a4c7be487e..6703018317 100644 --- a/src/test/regress/expected/stats_ext.out +++ b/src/test/regress/expected/stats_ext.out @@ -54,6 +54,28 @@ CREATE STATISTICS tst ON (x || 'x'), (x || 'x'), y FROM ext_stats_test; ERROR: duplicate expression in statistics definition CREATE STATISTICS tst (unrecognized) ON x, y FROM ext_stats_test; ERROR: unrecognized statistics kind "unrecognized" +--STATISTICS on virtual generated column not allowed +CREATE TABLE ext_stats_test1 (x int, y int, z int GENERATED ALWAYS AS (x+y) VIRTUAL, w xid); +CREATE STATISTICS tst on z from ext_stats_test1; +ERROR: statistics creation on virtual generated columns is not supported +CREATE STATISTICS tst on (z) from ext_stats_test1; +ERROR: statistics creation on virtual generated columns is not supported +CREATE STATISTICS tst on (z+1) from ext_stats_test1; +ERROR: statistics creation on virtual generated columns is not supported +CREATE STATISTICS tst (ndistinct) ON z from ext_stats_test1; +ERROR: statistics creation on virtual generated columns is not supported +--STATISTICS on system column not allowed +CREATE STATISTICS tst on tableoid from ext_stats_test1; +ERROR: statistics creation on system columns is not supported +CREATE STATISTICS tst on (tableoid) from ext_stats_test1; +ERROR: statistics creation on system columns is not supported +CREATE STATISTICS tst on (tableoid::int+1) from ext_stats_test1; +ERROR: statistics creation on system columns is not supported +CREATE STATISTICS tst (ndistinct) ON xmin from ext_stats_test1; +ERROR: statistics creation on system columns is not supported +--STATISTICS, without a less-than operator not allowed +CREATE STATISTICS tst (ndistinct) ON w from ext_stats_test1; +ERROR: column "w" cannot be used in statistics because its type xid has no default btree operator class -- incorrect expressions CREATE STATISTICS tst ON (y) FROM ext_stats_test; -- single column reference ERROR: extended statistics require at least 2 columns diff --git a/src/test/regress/sql/stats_ext.sql b/src/test/regress/sql/stats_ext.sql index 5c786b16c6..12f02d0d9a 100644 --- a/src/test/regress/sql/stats_ext.sql +++ b/src/test/regress/sql/stats_ext.sql @@ -40,6 +40,20 @@ CREATE STATISTICS tst ON x, x, y, x, x, (x || 'x'), (y + 1), (x || 'x'), (x || ' CREATE STATISTICS tst ON (x || 'x'), (x || 'x'), (y + 1), (x || 'x'), (x || 'x'), (y + 1), (x || 'x'), (x || 'x'), (y + 1) FROM ext_stats_test; CREATE STATISTICS tst ON (x || 'x'), (x || 'x'), y FROM ext_stats_test; CREATE STATISTICS tst (unrecognized) ON x, y FROM ext_stats_test; +--STATISTICS on virtual generated column not allowed +CREATE TABLE ext_stats_test1 (x int, y int, z int GENERATED ALWAYS AS (x+y) VIRTUAL, w xid); +CREATE STATISTICS tst on z from ext_stats_test1; +CREATE STATISTICS tst on (z) from ext_stats_test1; +CREATE STATISTICS tst on (z+1) from ext_stats_test1; +CREATE STATISTICS tst (ndistinct) ON z from ext_stats_test1; +--STATISTICS on system column not allowed +CREATE STATISTICS tst on tableoid from ext_stats_test1; +CREATE STATISTICS tst on (tableoid) from ext_stats_test1; +CREATE STATISTICS tst on (tableoid::int+1) from ext_stats_test1; +CREATE STATISTICS tst (ndistinct) ON xmin from ext_stats_test1; +--STATISTICS, without a less-than operator not allowed +CREATE STATISTICS tst (ndistinct) ON w from ext_stats_test1; + -- incorrect expressions CREATE STATISTICS tst ON (y) FROM ext_stats_test; -- single column reference CREATE STATISTICS tst ON y + z FROM ext_stats_test; -- missing parentheses -- 2.34.1