Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitbc8a1fc

Browse files
committed
Hashed crosstab was dying with an SPI_finish error when the source SQL
produced no rows. Now it returns 0 rows instead. Adjusted regressiontest for this case.
1 parent7d3b7db commitbc8a1fc

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(rowidtext, rowdttimestamp, temperature int4, test_resulttext, test_startdatetimestamp, 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(rowidtext, rowdttimestamp, temperaturetext, test_resulttext, test_startdatetext, voltstext);
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(rowidtext, rowdttimestamp, temperaturetext, test_resulttext, test_startdatetext, voltstext);
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
intresult_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)
[8]ページ先頭
©2009-2025 Movatter.jp