From 7fba23c28e9e7558f54c52dd9407ce50f75b0e1d Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Tue, 5 Mar 2024 16:37:35 -0500 Subject: [PATCH v1 1/2] Add more test coverage for contrib/tablefunc. Add test cases that exercise all the error reports in the module, excluding those that are for failures elsewhere such as SPI errors. --- contrib/tablefunc/expected/tablefunc.out | 63 ++++++++++++++++++++++++ contrib/tablefunc/sql/tablefunc.sql | 42 ++++++++++++++++ 2 files changed, 105 insertions(+) diff --git a/contrib/tablefunc/expected/tablefunc.out b/contrib/tablefunc/expected/tablefunc.out index 464c210f42..0c4e114aba 100644 --- a/contrib/tablefunc/expected/tablefunc.out +++ b/contrib/tablefunc/expected/tablefunc.out @@ -145,6 +145,21 @@ SELECT * FROM crosstab_out('SELECT rowid, attribute, val FROM ct where rowclass | val9 | val10 | val11 (3 rows) +-- check error reporting +SELECT * FROM crosstab('SELECT rowid, val FROM ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') ORDER BY 1,2;') + AS ct(row_name text, category_1 text, category_2 text); +ERROR: invalid source data SQL statement +DETAIL: The provided SQL must return 3 columns: rowid, category, and values. +SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') ORDER BY 1,2;') + AS ct(row_name text); +ERROR: return and sql tuple descriptions are incompatible +SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') ORDER BY 1,2;') + AS ct(row_name int, category_1 text, category_2 text); +ERROR: invalid return type +DETAIL: SQL rowid datatype does not match return rowid datatype. +SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') ORDER BY 1,2;') + AS ct(row_name text, category_1 text, category_2 int); +ERROR: return and sql tuple descriptions are incompatible -- -- hash based crosstab -- @@ -223,6 +238,12 @@ SELECT * FROM crosstab( 'SELECT DISTINCT rowdt, attribute FROM cth ORDER BY 2') AS c(rowid text, rowdt timestamp, temperature int4, test_result text, test_startdate timestamp, volts float8); ERROR: provided "categories" SQL must return 1 column of at least one row +-- if category query generates a NULL value, get expected error +SELECT * FROM crosstab( + 'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1', + 'SELECT NULL::text') +AS c(rowid text, rowdt timestamp, temperature int4, test_result text, test_startdate timestamp, volts float8); +ERROR: provided "categories" SQL must not return NULL values -- if source query returns zero rows, get zero rows returned SELECT * FROM crosstab( 'SELECT rowid, rowdt, attribute, val FROM cth WHERE false ORDER BY 1', @@ -241,6 +262,25 @@ AS c(rowid text, rowdt timestamp, temperature text, test_result text, test_start -------+-------+-------------+-------------+----------------+------- (0 rows) +-- check errors with inappropriate input rowtype +SELECT * FROM crosstab( + 'SELECT rowid, attribute FROM cth ORDER BY 1', + 'SELECT DISTINCT attribute FROM cth ORDER BY 1') +AS c(rowid text, temperature text, test_result text, test_startdate text, volts text); +ERROR: invalid source data SQL statement +DETAIL: The provided SQL must return 3 columns; rowid, category, and values. +SELECT * FROM crosstab( + 'SELECT rowid, rowdt, rowdt, attribute, val FROM cth ORDER BY 1', + 'SELECT DISTINCT attribute FROM cth ORDER BY 1') +AS c(rowid text, rowdt timestamp, temperature int4, test_result text, test_startdate timestamp, volts float8); +ERROR: invalid return type +DETAIL: Query-specified return tuple has 6 columns but crosstab returns 7. +-- check errors with inappropriate result rowtype +SELECT * FROM crosstab( + 'SELECT rowid, attribute, val FROM cth ORDER BY 1', + 'SELECT DISTINCT attribute FROM cth ORDER BY 1') +AS c(rowid text); +ERROR: query-specified return tuple and crosstab function are not compatible -- check it works with a named result rowtype create type my_crosstab_result as ( rowid text, rowdt timestamp, @@ -387,6 +427,29 @@ DETAIL: First two columns must be the same type. SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0, '~') AS t(keyid float8, parent_keyid float8, level int, branch text); ERROR: invalid return type DETAIL: SQL key field type double precision does not match return key field type integer. +SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0, '~') AS t(keyid int, parent_keyid float8, level int, branch text); +ERROR: invalid return type +DETAIL: First two columns must be the same type. +-- check other rowtype mismatch cases +SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0) AS t(keyid int, parent_keyid int, level int, branch text); +ERROR: invalid return type +DETAIL: Query-specified return tuple has wrong number of columns. +SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0, '~') AS t(keyid int, parent_keyid int, level int); +ERROR: invalid return type +DETAIL: Query-specified return tuple has wrong number of columns. +SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0) AS t(keyid int, parent_keyid text, level int); +ERROR: invalid return type +DETAIL: First two columns must be the same type. +SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0, '~') AS t(keyid int, parent_keyid int, level float, branch float); +ERROR: invalid return type +DETAIL: Third column must be type integer. +SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0, '~') AS t(keyid int, parent_keyid int, level int, branch float); +ERROR: invalid return type +DETAIL: Fourth column must be type text. +SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~') AS t(keyid text, parent_keyid text, level int, branch text, pos text); +ERROR: query-specified return tuple not valid for Connectby: fifth column must be type integer +SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'pos', 'row2', 0) AS t(keyid text, parent_keyid text, level int, pos text); +ERROR: query-specified return tuple not valid for Connectby: fourth column must be type integer -- tests for values using custom queries -- query with one column - failed SELECT * FROM connectby('connectby_int', '1; --', 'parent_keyid', '2', 0) AS t(keyid int, parent_keyid int, level int); diff --git a/contrib/tablefunc/sql/tablefunc.sql b/contrib/tablefunc/sql/tablefunc.sql index 02e8a98c73..0fb8e40de2 100644 --- a/contrib/tablefunc/sql/tablefunc.sql +++ b/contrib/tablefunc/sql/tablefunc.sql @@ -44,6 +44,16 @@ LANGUAGE C STABLE STRICT; SELECT * FROM crosstab_out('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' ORDER BY 1,2;'); +-- check error reporting +SELECT * FROM crosstab('SELECT rowid, val FROM ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') ORDER BY 1,2;') + AS ct(row_name text, category_1 text, category_2 text); +SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') ORDER BY 1,2;') + AS ct(row_name text); +SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') ORDER BY 1,2;') + AS ct(row_name int, category_1 text, category_2 text); +SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') ORDER BY 1,2;') + AS ct(row_name text, category_1 text, category_2 int); + -- -- hash based crosstab -- @@ -99,6 +109,12 @@ SELECT * FROM crosstab( 'SELECT DISTINCT rowdt, attribute FROM cth ORDER BY 2') AS c(rowid text, rowdt timestamp, temperature int4, test_result text, test_startdate timestamp, volts float8); +-- if category query generates a NULL value, get expected error +SELECT * FROM crosstab( + 'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1', + 'SELECT NULL::text') +AS c(rowid text, rowdt timestamp, temperature int4, test_result text, test_startdate timestamp, volts float8); + -- if source query returns zero rows, get zero rows returned SELECT * FROM crosstab( 'SELECT rowid, rowdt, attribute, val FROM cth WHERE false ORDER BY 1', @@ -111,6 +127,22 @@ SELECT * FROM crosstab( 'SELECT DISTINCT attribute FROM cth WHERE false ORDER BY 1') AS c(rowid text, rowdt timestamp, temperature text, test_result text, test_startdate text, volts text); +-- check errors with inappropriate input rowtype +SELECT * FROM crosstab( + 'SELECT rowid, attribute FROM cth ORDER BY 1', + 'SELECT DISTINCT attribute FROM cth ORDER BY 1') +AS c(rowid text, temperature text, test_result text, test_startdate text, volts text); +SELECT * FROM crosstab( + 'SELECT rowid, rowdt, rowdt, attribute, val FROM cth ORDER BY 1', + 'SELECT DISTINCT attribute FROM cth ORDER BY 1') +AS c(rowid text, rowdt timestamp, temperature int4, test_result text, test_startdate timestamp, volts float8); + +-- check errors with inappropriate result rowtype +SELECT * FROM crosstab( + 'SELECT rowid, attribute, val FROM cth ORDER BY 1', + 'SELECT DISTINCT attribute FROM cth ORDER BY 1') +AS c(rowid text); + -- check it works with a named result rowtype create type my_crosstab_result as ( @@ -186,6 +218,16 @@ SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0, '~') A -- should fail as key field datatype should match return datatype SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0, '~') AS t(keyid float8, parent_keyid float8, level int, branch text); +SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0, '~') AS t(keyid int, parent_keyid float8, level int, branch text); + +-- check other rowtype mismatch cases +SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0) AS t(keyid int, parent_keyid int, level int, branch text); +SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0, '~') AS t(keyid int, parent_keyid int, level int); +SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0) AS t(keyid int, parent_keyid text, level int); +SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0, '~') AS t(keyid int, parent_keyid int, level float, branch float); +SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0, '~') AS t(keyid int, parent_keyid int, level int, branch float); +SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~') AS t(keyid text, parent_keyid text, level int, branch text, pos text); +SELECT * FROM connectby('connectby_text', 'keyid', 'parent_keyid', 'pos', 'row2', 0) AS t(keyid text, parent_keyid text, level int, pos text); -- tests for values using custom queries -- query with one column - failed -- 2.39.3