Skip to content

Commit bc8a1fc

Browse files
jconwayjconway
committed
Hashed crosstab was dying with an SPI_finish error when the source SQL
produced no rows. Now it returns 0 rows instead. Adjusted regression test for this case.
1 parent 7d3b7db commit bc8a1fc

File tree

3 files changed

+39
-15
lines changed

3 files changed

+39
-15
lines changed

‎contrib/tablefunc/expected/tablefunc.out

Lines changed: 19 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -127,7 +127,7 @@ SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass = ''
127127
-- hash based crosstab
128128
--
129129
create table cth(id serial, rowid text, rowdt timestamp, attribute text, val text);
130-
NOTICE: CREATE TABLE will create implicit sequence "cth_id_seq" for "serial" column "cth.id"
130+
NOTICE: CREATE TABLE will create implicit sequence "cth_id_seq" for serial column "cth.id"
131131
insert into cth values(DEFAULT,'test1','01 March 2003','temperature','42');
132132
insert into cth values(DEFAULT,'test1','01 March 2003','test_result','PASS');
133133
-- the next line is intentionally left commented and is therefore a "missing" attribute
@@ -193,6 +193,24 @@ SELECT * FROM crosstab(
193193
'SELECT DISTINCT rowdt, attribute FROM cth ORDER BY 2')
194194
AS c(rowid text, rowdt timestamp, temperature int4, test_result text, test_startdate timestamp, volts float8);
195195
ERROR: provided "categories" SQL must return 1 column of at least one row
196+
-- if source query returns zero rows, get zero rows returned
197+
SELECT * FROM crosstab(
198+
'SELECT rowid, rowdt, attribute, val FROM cth WHERE false ORDER BY 1',
199+
'SELECT DISTINCT attribute FROM cth ORDER BY 1')
200+
AS c(rowid text, rowdt timestamp, temperature text, test_result text, test_startdate text, volts text);
201+
rowid | rowdt | temperature | test_result | test_startdate | volts
202+
-------+-------+-------------+-------------+----------------+-------
203+
(0 rows)
204+
205+
-- if source query returns zero rows, get zero rows returned even if category query generates no rows
206+
SELECT * FROM crosstab(
207+
'SELECT rowid, rowdt, attribute, val FROM cth WHERE false ORDER BY 1',
208+
'SELECT DISTINCT attribute FROM cth WHERE false ORDER BY 1')
209+
AS c(rowid text, rowdt timestamp, temperature text, test_result text, test_startdate text, volts text);
210+
rowid | rowdt | temperature | test_result | test_startdate | volts
211+
-------+-------+-------------+-------------+----------------+-------
212+
(0 rows)
213+
196214
--
197215
-- connectby
198216
--

‎contrib/tablefunc/sql/tablefunc.sql

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -88,6 +88,17 @@ SELECT * FROM crosstab(
8888
'SELECT DISTINCT rowdt, attribute FROM cth ORDER BY 2')
8989
AS c(rowid text, rowdt timestamp, temperature int4, test_result text, test_startdate timestamp, volts float8);
9090

91+
-- if source query returns zero rows, get zero rows returned
92+
SELECT * FROM crosstab(
93+
'SELECT rowid, rowdt, attribute, val FROM cth WHERE false ORDER BY 1',
94+
'SELECT DISTINCT attribute FROM cth ORDER BY 1')
95+
AS c(rowid text, rowdt timestamp, temperature text, test_result text, test_startdate text, volts text);
96+
97+
-- if source query returns zero rows, get zero rows returned even if category query generates no rows
98+
SELECT * FROM crosstab(
99+
'SELECT rowid, rowdt, attribute, val FROM cth WHERE false ORDER BY 1',
100+
'SELECT DISTINCT attribute FROM cth WHERE false ORDER BY 1')
101+
AS c(rowid text, rowdt timestamp, temperature text, test_result text, test_startdate text, volts text);
91102

92103
--
93104
-- connectby

‎contrib/tablefunc/tablefunc.c

Lines changed: 9 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -821,15 +821,6 @@ load_categories_hash(char *cats_sql, MemoryContext per_query_ctx)
821821
MemoryContextSwitchTo(SPIcontext);
822822
}
823823
}
824-
else
825-
{
826-
/* no qualifying tuples */
827-
SPI_finish();
828-
ereport(ERROR,
829-
(errcode(ERRCODE_SYNTAX_ERROR),
830-
errmsg("provided \"categories\" SQL must " \
831-
"return 1 column of at least one row")));
832-
}
833824

834825
if (SPI_finish() != SPI_OK_FINISH)
835826
/* internal error */
@@ -879,6 +870,15 @@ get_crosstab_tuplestore(char *sql,
879870
j;
880871
int result_ncols;
881872

873+
if (num_categories == 0)
874+
{
875+
/* no qualifying category tuples */
876+
ereport(ERROR,
877+
(errcode(ERRCODE_SYNTAX_ERROR),
878+
errmsg("provided \"categories\" SQL must " \
879+
"return 1 column of at least one row")));
880+
}
881+
882882
/*
883883
* The provided SQL query must always return at least three
884884
* columns:
@@ -994,11 +994,6 @@ get_crosstab_tuplestore(char *sql,
994994
MemoryContextSwitchTo(SPIcontext);
995995

996996
}
997-
else
998-
{
999-
/* no qualifying tuples */
1000-
SPI_finish();
1001-
}
1002997

1003998
if (SPI_finish() != SPI_OK_FINISH)
1004999
/* internal error */

0 commit comments

Comments
 (0)