Re: Crosstab Problems

Stefan Schwarzer <stefan(dot)schwarzer(at)grid(dot)unep(dot)ch> writes:
> Here is a SQL dump for the table. One can just neglect the JOIN with
> the countries table (which just replaces the country id with the
> country name):
> http://geodata.grid.unep.ch/download/sql_agri_area.sql.zip
> But when re-doing the query now without the JOIN, it works (almost):

OK, after poking at it, it seems that crosstab() isn't prepared for
null rowids. I can reproduce the crash without any data:

contrib_regression=# select * from crosstab(
'SELECT null::text as name, 10 as year, 42 as value', 3)
as ct(name text, year int, value int);
server closed the connection unexpectedly

Backtrace looks like

#0 0xc008774c in ?? () from /usr/lib/libc.1
#1 0x3eb0bc in MemoryContextStrdup (context=0x40167048, string=0x0)
at mcxt.c:662
#2 0xc0a5f2e4 in crosstab (fcinfo=0x7b03b858) at tablefunc.c:539
#3 0x239e24 in ExecMakeTableFunctionResult (funcexpr=0x401615e8,
econtext=0x401611f0, expectedDesc=0x401613a0, returnDesc=0x7b03b7d8)
at execQual.c:1566
#4 0x24d264 in FunctionNext (node=0x40161160) at nodeFunctionscan.c:68
#5 0x23ed8c in ExecScan (node=0x40161160,
accessMtd=0x400170b2 <DINFINITY+3218>) at execScan.c:68
#6 0x24d2c4 in ExecFunctionScan (node=0x40167048) at nodeFunctionscan.c:109

so it's trying to pstrdup a null result from SPI_getvalue.

Obviously it shouldn't crash, but I'm not sure what it *should* do in
this case. Joe?

In the meantime, it appears that you want to not use a LEFT JOIN here,
or else maybe COALESCE(c.name, '') so that a null isn't returned to
crosstab.

regards, tom lane