From 1ff3f420d470fae46759e948a20e9550af012816 Mon Sep 17 00:00:00 2001 From: Alvaro Herrera Date: Fri, 8 Apr 2016 23:42:24 -0300 Subject: [PATCH] Move \crosstabview regression tests to a separate file It cannot run in the same parallel group as misc, because it creates a table which is unpredictably visible in that test. Per buildfarm member crake. --- doc/src/sgml/ref/psql-ref.sgml | 2 +- src/test/regress/expected/psql.out | 185 -------------------- src/test/regress/expected/psql_crosstab.out | 185 ++++++++++++++++++++ src/test/regress/parallel_schedule | 2 +- src/test/regress/serial_schedule | 1 + src/test/regress/sql/psql.sql | 99 ----------- src/test/regress/sql/psql_crosstab.sql | 98 +++++++++++ 7 files changed, 286 insertions(+), 286 deletions(-) create mode 100644 src/test/regress/expected/psql_crosstab.out create mode 100644 src/test/regress/sql/psql_crosstab.sql diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml index 3f72f5ffe0..1f07956d42 100644 --- a/doc/src/sgml/ref/psql-ref.sgml +++ b/doc/src/sgml/ref/psql-ref.sgml @@ -1009,7 +1009,7 @@ testdb=> colD is the output column to project into the grid. If this is not specified and there are exactly three columns in the result set, - the column that isn't + the column that isn't colV nor colH is displayed; if there are more columns, an error is thrown. diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out index 9e98e9d62f..017b79ea9c 100644 --- a/src/test/regress/expected/psql.out +++ b/src/test/regress/expected/psql.out @@ -2714,188 +2714,3 @@ NOTICE: foo CONTEXT: PL/pgSQL function inline_code_block line 3 at RAISE ERROR: bar CONTEXT: PL/pgSQL function inline_code_block line 4 at RAISE --- --- \crosstabview --- -CREATE TABLE ctv_data (v, h, c, i, d) AS -VALUES - ('v1','h2','foo', 3, '2015-04-01'::date), - ('v2','h1','bar', 3, '2015-01-02'), - ('v1','h0','baz', NULL, '2015-07-12'), - ('v0','h4','qux', 4, '2015-07-15'), - ('v0','h4','dbl', -3, '2014-12-15'), - ('v0',NULL,'qux', 5, '2014-07-15'), - ('v1','h2','quux',7, '2015-04-04'); --- running \crosstabview after query uses query in buffer -SELECT v, EXTRACT(year FROM d), count(*) - FROM ctv_data - GROUP BY 1, 2 - ORDER BY 1, 2; - v | date_part | count -----+-----------+------- - v0 | 2014 | 2 - v0 | 2015 | 1 - v1 | 2015 | 3 - v2 | 2015 | 1 -(4 rows) - --- basic usage with 3 columns - \crosstabview - v | 2014 | 2015 -----+------+------ - v0 | 2 | 1 - v1 | | 3 - v2 | | 1 -(3 rows) - --- ordered months in horizontal header, quoted column name -SELECT v, to_char(d, 'Mon') AS "month name", EXTRACT(month FROM d) AS num, - count(*) FROM ctv_data GROUP BY 1,2,3 ORDER BY 1 - \crosstabview v "month name":num 4 - v | Jan | Apr | Jul | Dec -----+-----+-----+-----+----- - v0 | | | 2 | 1 - v1 | | 2 | 1 | - v2 | 1 | | | -(3 rows) - --- ordered months in vertical header, ordered years in horizontal header -SELECT EXTRACT(year FROM d) AS year, to_char(d,'Mon') AS "month name", - EXTRACT(month FROM d) AS month, - format('sum=%s avg=%s', sum(i), avg(i)::numeric(2,1)) - FROM ctv_data - GROUP BY EXTRACT(year FROM d), to_char(d,'Mon'), EXTRACT(month FROM d) -ORDER BY month -\crosstabview "month name" year:year format - month name | 2014 | 2015 -------------+-----------------+---------------- - Jan | | sum=3 avg=3.0 - Apr | | sum=10 avg=5.0 - Jul | sum=5 avg=5.0 | sum=4 avg=4.0 - Dec | sum=-3 avg=-3.0 | -(4 rows) - --- combine contents vertically into the same cell (V/H duplicates) -SELECT v, h, string_agg(c, E'\n') FROM ctv_data GROUP BY v, h ORDER BY 1,2,3 - \crosstabview 1 2 3 - v | h4 | | h0 | h2 | h1 -----+-----+-----+-----+------+----- - v0 | qux+| qux | | | - | dbl | | | | - v1 | | | baz | foo +| - | | | | quux | - v2 | | | | | bar -(3 rows) - --- horizontal ASC order from window function -SELECT v,h, string_agg(c, E'\n') AS c, row_number() OVER(ORDER BY h) AS r -FROM ctv_data GROUP BY v, h ORDER BY 1,3,2 - \crosstabview v h:r c - v | h0 | h1 | h2 | h4 | -----+-----+-----+------+-----+----- - v0 | | | | qux+| qux - | | | | dbl | - v1 | baz | | foo +| | - | | | quux | | - v2 | | bar | | | -(3 rows) - --- horizontal DESC order from window function -SELECT v, h, string_agg(c, E'\n') AS c, row_number() OVER(ORDER BY h DESC) AS r -FROM ctv_data GROUP BY v, h ORDER BY 1,3,2 - \crosstabview v h:r c - v | | h4 | h2 | h1 | h0 -----+-----+-----+------+-----+----- - v0 | qux | qux+| | | - | | dbl | | | - v1 | | | foo +| | baz - | | | quux | | - v2 | | | | bar | -(3 rows) - --- horizontal ASC order from window function, NULLs pushed rightmost -SELECT v,h, string_agg(c, E'\n') AS c, row_number() OVER(ORDER BY h NULLS LAST) AS r -FROM ctv_data GROUP BY v, h ORDER BY 1,3,2 - \crosstabview v h:r c - v | h0 | h1 | h2 | h4 | -----+-----+-----+------+-----+----- - v0 | | | | qux+| qux - | | | | dbl | - v1 | baz | | foo +| | - | | | quux | | - v2 | | bar | | | -(3 rows) - --- only null, no column name, 2 columns: error -SELECT null,null \crosstabview -The query must return at least two columns to be shown in crosstab --- only null, no column name, 3 columns: works -SELECT null,null,null \crosstabview - ?column? | -----------+-- - | -(1 row) - --- null display -\pset null '#null#' -SELECT v,h, string_agg(i::text, E'\n') AS i FROM ctv_data -GROUP BY v, h ORDER BY h,v - \crosstabview v h i - v | h0 | h1 | h2 | h4 | #null# -----+--------+----+----+----+-------- - v1 | #null# | | 3 +| | - | | | 7 | | - v2 | | 3 | | | - v0 | | | | 4 +| 5 - | | | | -3 | -(3 rows) - -\pset null '' --- refer to columns by position -SELECT v,h,string_agg(i::text, E'\n'), string_agg(c, E'\n') -FROM ctv_data GROUP BY v, h ORDER BY h,v - \crosstabview 2 1 4 - h | v1 | v2 | v0 -----+------+-----+----- - h0 | baz | | - h1 | | bar | - h2 | foo +| | - | quux | | - h4 | | | qux+ - | | | dbl - | | | qux -(5 rows) - --- refer to columns by positions and names mixed -SELECT v,h, string_agg(i::text, E'\n') AS i, string_agg(c, E'\n') AS c -FROM ctv_data GROUP BY v, h ORDER BY h,v - \crosstabview 1 "h" 4 - v | h0 | h1 | h2 | h4 | -----+-----+-----+------+-----+----- - v1 | baz | | foo +| | - | | | quux | | - v2 | | bar | | | - v0 | | | | qux+| qux - | | | | dbl | -(3 rows) - --- error: bad column name -SELECT v,h,c,i FROM ctv_data - \crosstabview v h j -Invalid column name: j --- error: bad column number -SELECT v,h,i,c FROM ctv_data - \crosstabview 2 1 5 -Invalid column number: 5 --- error: same H and V columns -SELECT v,h,i,c FROM ctv_data - \crosstabview 2 h 4 -The same column cannot be used for both vertical and horizontal headers --- error: too many columns -SELECT a,a,1 FROM generate_series(1,3000) AS a - \crosstabview -Maximum number of columns (1600) exceeded --- error: only one column -SELECT 1 \crosstabview -The query must return at least two columns to be shown in crosstab -DROP TABLE ctv_data; diff --git a/src/test/regress/expected/psql_crosstab.out b/src/test/regress/expected/psql_crosstab.out new file mode 100644 index 0000000000..c87c2fcca2 --- /dev/null +++ b/src/test/regress/expected/psql_crosstab.out @@ -0,0 +1,185 @@ +-- +-- \crosstabview +-- +CREATE TABLE ctv_data (v, h, c, i, d) AS +VALUES + ('v1','h2','foo', 3, '2015-04-01'::date), + ('v2','h1','bar', 3, '2015-01-02'), + ('v1','h0','baz', NULL, '2015-07-12'), + ('v0','h4','qux', 4, '2015-07-15'), + ('v0','h4','dbl', -3, '2014-12-15'), + ('v0',NULL,'qux', 5, '2014-07-15'), + ('v1','h2','quux',7, '2015-04-04'); +-- running \crosstabview after query uses query in buffer +SELECT v, EXTRACT(year FROM d), count(*) + FROM ctv_data + GROUP BY 1, 2 + ORDER BY 1, 2; + v | date_part | count +----+-----------+------- + v0 | 2014 | 2 + v0 | 2015 | 1 + v1 | 2015 | 3 + v2 | 2015 | 1 +(4 rows) + +-- basic usage with 3 columns + \crosstabview + v | 2014 | 2015 +----+------+------ + v0 | 2 | 1 + v1 | | 3 + v2 | | 1 +(3 rows) + +-- ordered months in horizontal header, quoted column name +SELECT v, to_char(d, 'Mon') AS "month name", EXTRACT(month FROM d) AS num, + count(*) FROM ctv_data GROUP BY 1,2,3 ORDER BY 1 + \crosstabview v "month name":num 4 + v | Jan | Apr | Jul | Dec +----+-----+-----+-----+----- + v0 | | | 2 | 1 + v1 | | 2 | 1 | + v2 | 1 | | | +(3 rows) + +-- ordered months in vertical header, ordered years in horizontal header +SELECT EXTRACT(year FROM d) AS year, to_char(d,'Mon') AS "month name", + EXTRACT(month FROM d) AS month, + format('sum=%s avg=%s', sum(i), avg(i)::numeric(2,1)) + FROM ctv_data + GROUP BY EXTRACT(year FROM d), to_char(d,'Mon'), EXTRACT(month FROM d) +ORDER BY month +\crosstabview "month name" year:year format + month name | 2014 | 2015 +------------+-----------------+---------------- + Jan | | sum=3 avg=3.0 + Apr | | sum=10 avg=5.0 + Jul | sum=5 avg=5.0 | sum=4 avg=4.0 + Dec | sum=-3 avg=-3.0 | +(4 rows) + +-- combine contents vertically into the same cell (V/H duplicates) +SELECT v, h, string_agg(c, E'\n') FROM ctv_data GROUP BY v, h ORDER BY 1,2,3 + \crosstabview 1 2 3 + v | h4 | | h0 | h2 | h1 +----+-----+-----+-----+------+----- + v0 | qux+| qux | | | + | dbl | | | | + v1 | | | baz | foo +| + | | | | quux | + v2 | | | | | bar +(3 rows) + +-- horizontal ASC order from window function +SELECT v,h, string_agg(c, E'\n') AS c, row_number() OVER(ORDER BY h) AS r +FROM ctv_data GROUP BY v, h ORDER BY 1,3,2 + \crosstabview v h:r c + v | h0 | h1 | h2 | h4 | +----+-----+-----+------+-----+----- + v0 | | | | qux+| qux + | | | | dbl | + v1 | baz | | foo +| | + | | | quux | | + v2 | | bar | | | +(3 rows) + +-- horizontal DESC order from window function +SELECT v, h, string_agg(c, E'\n') AS c, row_number() OVER(ORDER BY h DESC) AS r +FROM ctv_data GROUP BY v, h ORDER BY 1,3,2 + \crosstabview v h:r c + v | | h4 | h2 | h1 | h0 +----+-----+-----+------+-----+----- + v0 | qux | qux+| | | + | | dbl | | | + v1 | | | foo +| | baz + | | | quux | | + v2 | | | | bar | +(3 rows) + +-- horizontal ASC order from window function, NULLs pushed rightmost +SELECT v,h, string_agg(c, E'\n') AS c, row_number() OVER(ORDER BY h NULLS LAST) AS r +FROM ctv_data GROUP BY v, h ORDER BY 1,3,2 + \crosstabview v h:r c + v | h0 | h1 | h2 | h4 | +----+-----+-----+------+-----+----- + v0 | | | | qux+| qux + | | | | dbl | + v1 | baz | | foo +| | + | | | quux | | + v2 | | bar | | | +(3 rows) + +-- only null, no column name, 2 columns: error +SELECT null,null \crosstabview +The query must return at least two columns to be shown in crosstab +-- only null, no column name, 3 columns: works +SELECT null,null,null \crosstabview + ?column? | +----------+-- + | +(1 row) + +-- null display +\pset null '#null#' +SELECT v,h, string_agg(i::text, E'\n') AS i FROM ctv_data +GROUP BY v, h ORDER BY h,v + \crosstabview v h i + v | h0 | h1 | h2 | h4 | #null# +----+--------+----+----+----+-------- + v1 | #null# | | 3 +| | + | | | 7 | | + v2 | | 3 | | | + v0 | | | | 4 +| 5 + | | | | -3 | +(3 rows) + +\pset null '' +-- refer to columns by position +SELECT v,h,string_agg(i::text, E'\n'), string_agg(c, E'\n') +FROM ctv_data GROUP BY v, h ORDER BY h,v + \crosstabview 2 1 4 + h | v1 | v2 | v0 +----+------+-----+----- + h0 | baz | | + h1 | | bar | + h2 | foo +| | + | quux | | + h4 | | | qux+ + | | | dbl + | | | qux +(5 rows) + +-- refer to columns by positions and names mixed +SELECT v,h, string_agg(i::text, E'\n') AS i, string_agg(c, E'\n') AS c +FROM ctv_data GROUP BY v, h ORDER BY h,v + \crosstabview 1 "h" 4 + v | h0 | h1 | h2 | h4 | +----+-----+-----+------+-----+----- + v1 | baz | | foo +| | + | | | quux | | + v2 | | bar | | | + v0 | | | | qux+| qux + | | | | dbl | +(3 rows) + +-- error: bad column name +SELECT v,h,c,i FROM ctv_data + \crosstabview v h j +Invalid column name: j +-- error: bad column number +SELECT v,h,i,c FROM ctv_data + \crosstabview 2 1 5 +Invalid column number: 5 +-- error: same H and V columns +SELECT v,h,i,c FROM ctv_data + \crosstabview 2 h 4 +The same column cannot be used for both vertical and horizontal headers +-- error: too many columns +SELECT a,a,1 FROM generate_series(1,3000) AS a + \crosstabview +Maximum number of columns (1600) exceeded +-- error: only one column +SELECT 1 \crosstabview +The query must return at least two columns to be shown in crosstab +DROP TABLE ctv_data; diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 6c1f21bb62..c03f6352b7 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -92,7 +92,7 @@ test: brin gin gist spgist privileges init_privs security_label collate matview test: alter_generic alter_operator misc psql async dbsize misc_functions # rules cannot run concurrently with any test that creates a view -test: rules +test: rules psql_crosstab # ---------- # Another group of parallel tests diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule index 8269c524dc..e25660cdd6 100644 --- a/src/test/regress/serial_schedule +++ b/src/test/regress/serial_schedule @@ -124,6 +124,7 @@ test: async test: dbsize test: misc_functions test: rules +test: psql_crosstab test: select_views test: portals_p2 test: foreign_key diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql index 2e332afc96..4dc0745f1d 100644 --- a/src/test/regress/sql/psql.sql +++ b/src/test/regress/sql/psql.sql @@ -379,102 +379,3 @@ begin raise notice 'foo'; raise exception 'bar'; end $$; - --- --- \crosstabview --- - -CREATE TABLE ctv_data (v, h, c, i, d) AS -VALUES - ('v1','h2','foo', 3, '2015-04-01'::date), - ('v2','h1','bar', 3, '2015-01-02'), - ('v1','h0','baz', NULL, '2015-07-12'), - ('v0','h4','qux', 4, '2015-07-15'), - ('v0','h4','dbl', -3, '2014-12-15'), - ('v0',NULL,'qux', 5, '2014-07-15'), - ('v1','h2','quux',7, '2015-04-04'); - --- running \crosstabview after query uses query in buffer -SELECT v, EXTRACT(year FROM d), count(*) - FROM ctv_data - GROUP BY 1, 2 - ORDER BY 1, 2; --- basic usage with 3 columns - \crosstabview - --- ordered months in horizontal header, quoted column name -SELECT v, to_char(d, 'Mon') AS "month name", EXTRACT(month FROM d) AS num, - count(*) FROM ctv_data GROUP BY 1,2,3 ORDER BY 1 - \crosstabview v "month name":num 4 - --- ordered months in vertical header, ordered years in horizontal header -SELECT EXTRACT(year FROM d) AS year, to_char(d,'Mon') AS "month name", - EXTRACT(month FROM d) AS month, - format('sum=%s avg=%s', sum(i), avg(i)::numeric(2,1)) - FROM ctv_data - GROUP BY EXTRACT(year FROM d), to_char(d,'Mon'), EXTRACT(month FROM d) -ORDER BY month -\crosstabview "month name" year:year format - --- combine contents vertically into the same cell (V/H duplicates) -SELECT v, h, string_agg(c, E'\n') FROM ctv_data GROUP BY v, h ORDER BY 1,2,3 - \crosstabview 1 2 3 - --- horizontal ASC order from window function -SELECT v,h, string_agg(c, E'\n') AS c, row_number() OVER(ORDER BY h) AS r -FROM ctv_data GROUP BY v, h ORDER BY 1,3,2 - \crosstabview v h:r c - --- horizontal DESC order from window function -SELECT v, h, string_agg(c, E'\n') AS c, row_number() OVER(ORDER BY h DESC) AS r -FROM ctv_data GROUP BY v, h ORDER BY 1,3,2 - \crosstabview v h:r c - --- horizontal ASC order from window function, NULLs pushed rightmost -SELECT v,h, string_agg(c, E'\n') AS c, row_number() OVER(ORDER BY h NULLS LAST) AS r -FROM ctv_data GROUP BY v, h ORDER BY 1,3,2 - \crosstabview v h:r c - --- only null, no column name, 2 columns: error -SELECT null,null \crosstabview - --- only null, no column name, 3 columns: works -SELECT null,null,null \crosstabview - --- null display -\pset null '#null#' -SELECT v,h, string_agg(i::text, E'\n') AS i FROM ctv_data -GROUP BY v, h ORDER BY h,v - \crosstabview v h i -\pset null '' - --- refer to columns by position -SELECT v,h,string_agg(i::text, E'\n'), string_agg(c, E'\n') -FROM ctv_data GROUP BY v, h ORDER BY h,v - \crosstabview 2 1 4 - --- refer to columns by positions and names mixed -SELECT v,h, string_agg(i::text, E'\n') AS i, string_agg(c, E'\n') AS c -FROM ctv_data GROUP BY v, h ORDER BY h,v - \crosstabview 1 "h" 4 - --- error: bad column name -SELECT v,h,c,i FROM ctv_data - \crosstabview v h j - --- error: bad column number -SELECT v,h,i,c FROM ctv_data - \crosstabview 2 1 5 - --- error: same H and V columns -SELECT v,h,i,c FROM ctv_data - \crosstabview 2 h 4 - --- error: too many columns -SELECT a,a,1 FROM generate_series(1,3000) AS a - \crosstabview - --- error: only one column -SELECT 1 \crosstabview - -DROP TABLE ctv_data; diff --git a/src/test/regress/sql/psql_crosstab.sql b/src/test/regress/sql/psql_crosstab.sql new file mode 100644 index 0000000000..e602676df1 --- /dev/null +++ b/src/test/regress/sql/psql_crosstab.sql @@ -0,0 +1,98 @@ +-- +-- \crosstabview +-- + +CREATE TABLE ctv_data (v, h, c, i, d) AS +VALUES + ('v1','h2','foo', 3, '2015-04-01'::date), + ('v2','h1','bar', 3, '2015-01-02'), + ('v1','h0','baz', NULL, '2015-07-12'), + ('v0','h4','qux', 4, '2015-07-15'), + ('v0','h4','dbl', -3, '2014-12-15'), + ('v0',NULL,'qux', 5, '2014-07-15'), + ('v1','h2','quux',7, '2015-04-04'); + +-- running \crosstabview after query uses query in buffer +SELECT v, EXTRACT(year FROM d), count(*) + FROM ctv_data + GROUP BY 1, 2 + ORDER BY 1, 2; +-- basic usage with 3 columns + \crosstabview + +-- ordered months in horizontal header, quoted column name +SELECT v, to_char(d, 'Mon') AS "month name", EXTRACT(month FROM d) AS num, + count(*) FROM ctv_data GROUP BY 1,2,3 ORDER BY 1 + \crosstabview v "month name":num 4 + +-- ordered months in vertical header, ordered years in horizontal header +SELECT EXTRACT(year FROM d) AS year, to_char(d,'Mon') AS "month name", + EXTRACT(month FROM d) AS month, + format('sum=%s avg=%s', sum(i), avg(i)::numeric(2,1)) + FROM ctv_data + GROUP BY EXTRACT(year FROM d), to_char(d,'Mon'), EXTRACT(month FROM d) +ORDER BY month +\crosstabview "month name" year:year format + +-- combine contents vertically into the same cell (V/H duplicates) +SELECT v, h, string_agg(c, E'\n') FROM ctv_data GROUP BY v, h ORDER BY 1,2,3 + \crosstabview 1 2 3 + +-- horizontal ASC order from window function +SELECT v,h, string_agg(c, E'\n') AS c, row_number() OVER(ORDER BY h) AS r +FROM ctv_data GROUP BY v, h ORDER BY 1,3,2 + \crosstabview v h:r c + +-- horizontal DESC order from window function +SELECT v, h, string_agg(c, E'\n') AS c, row_number() OVER(ORDER BY h DESC) AS r +FROM ctv_data GROUP BY v, h ORDER BY 1,3,2 + \crosstabview v h:r c + +-- horizontal ASC order from window function, NULLs pushed rightmost +SELECT v,h, string_agg(c, E'\n') AS c, row_number() OVER(ORDER BY h NULLS LAST) AS r +FROM ctv_data GROUP BY v, h ORDER BY 1,3,2 + \crosstabview v h:r c + +-- only null, no column name, 2 columns: error +SELECT null,null \crosstabview + +-- only null, no column name, 3 columns: works +SELECT null,null,null \crosstabview + +-- null display +\pset null '#null#' +SELECT v,h, string_agg(i::text, E'\n') AS i FROM ctv_data +GROUP BY v, h ORDER BY h,v + \crosstabview v h i +\pset null '' + +-- refer to columns by position +SELECT v,h,string_agg(i::text, E'\n'), string_agg(c, E'\n') +FROM ctv_data GROUP BY v, h ORDER BY h,v + \crosstabview 2 1 4 + +-- refer to columns by positions and names mixed +SELECT v,h, string_agg(i::text, E'\n') AS i, string_agg(c, E'\n') AS c +FROM ctv_data GROUP BY v, h ORDER BY h,v + \crosstabview 1 "h" 4 + +-- error: bad column name +SELECT v,h,c,i FROM ctv_data + \crosstabview v h j + +-- error: bad column number +SELECT v,h,i,c FROM ctv_data + \crosstabview 2 1 5 + +-- error: same H and V columns +SELECT v,h,i,c FROM ctv_data + \crosstabview 2 h 4 + +-- error: too many columns +SELECT a,a,1 FROM generate_series(1,3000) AS a + \crosstabview + +-- error: only one column +SELECT 1 \crosstabview + +DROP TABLE ctv_data; -- 2.40.0