Обсуждение: BUG #2106: EXPLAIN ANALYZE with SELECT query causes a single backend server process to segfault

The following bug has been logged online:

Bug reference:      2106
Logged by:
Email address:      bugrep@oldanygroup.cz
PostgreSQL version: 8.1.0, 8.2devel
Operating system:   CentOS 4.2 with 2.6.13.4-SMP kernel
Description:        EXPLAIN ANALYZE with SELECT query causes a single
backend server process to segfault
Details:

Hello,

while testing the 8.1.0 version of PostgreSQL (we've been using version
7.4.8 for a long time), the EXPLAIN ANALYZE causes a single backend server
process to segfault after issuing the following query:

SELECT obch_vyrobek.id_vyrobku AS id_vyrobku,
      obch_vyrobek.id_vyrobce AS id_vyrobce,
      obch_vyrobce.nazev AS nazev_vyrobce,
      CASE obch_vyrobek.nazev ILIKE '%aloe%' WHEN true THEN 1 ELSE 0 END AS
match_nazev_vyrobku
FROM obch_vyrobek LEFT JOIN obch_vyrobce USING (id_vyrobce)
WHERE (obch_vyrobek.aktivni AND ((obch_vyrobek.sukl IS NULL AND
obch_vyrobek.v_katalogu) OR (obch_vyrobek.sukl IS NOT NULL) OR
(obch_vyrobek.odhadovana_dostupnost IS NOT NULL))) AND (obch_vyrobek.nazev
ILIKE
'%aloe%' OR obch_vyrobek.popis ILIKE '%aloe%' OR
coalesce(obch_vyrobek.popis_od_vyrobce,obch_vyrobek.popis) ILIKE '%aloe%'
OR
obch_vyrobce.nazev ILIKE '%aloe%')
ORDER BY otc DESC, typ DESC, odhadovana_dostupnost DESC, obr_m DESC,
match_nazev_vyrobku DESC, obch_vyrobek.nazev;

The same happens with version 8.2devel. There are no segfaults with such
EXPLAIN ANALYZE followed by abovementioned query when using version 7.4.8,
even 8.0.4 is ok.

"" <bugrep@oldanygroup.cz> writes:
> Description:        EXPLAIN ANALYZE with SELECT query causes a single
> backend server process to segfault

Could you provide a self-contained test case, please?  Guessing at your
table schemas does not seem a productive use of time.  See
http://www.postgresql.org/docs/8.1/static/bug-reporting.html

            regards, tom lane

On Sat, Dec 10, 2005 at 12:57:13PM -0500, Tom Lane wrote:
> "" <bugrep@oldanygroup.cz> writes:
> > Description:        EXPLAIN ANALYZE with SELECT query causes a single
> > backend server process to segfault
>
> Could you provide a self-contained test case, please?

Here's a test case:

CREATE TABLE foo (x integer);

EXPLAIN
SELECT CASE x = 1 WHEN true THEN 1 ELSE 0 END AS y
FROM foo
ORDER BY y;

I get a segfault in 8.1.1 with EXPLAIN but not for the query alone.
Here's the stack trace:

#0  0x081bfdcf in get_rule_expr (node=0x83ac460, context=0xbfbfd4b0, showimplicit=80 'P') at pg_list.h:82
#1  0x081c0e73 in deparse_expression_pretty (expr=0x83ac460, dpcontext=0x83ac7b0, forceprefix=0 '\0', showimplicit=1
'\001',prettyFlags=32, startIndent=32) 
    at ruleutils.c:1388
#2  0x080ef4a5 in explain_outNode (str=0x83ac648, plan=0x83ac388, planstate=0x83ad1f0, outer_plan=0x0, indent=0,
es=0x83ac638)at explain.c:1160 
#3  0x080ef90d in ExplainOnePlan (queryDesc=0x83ac5f0, stmt=0x835b708, tstate=0x8305b00) at explain.c:282
#4  0x080efca5 in ExplainOneQuery (query=0x83c7580, stmt=0x835b708, tstate=0x8305b00) at explain.c:214
#5  0x080efdcd in ExplainQuery (stmt=0x835b708, dest=0x83c7530) at explain.c:121
#6  0x0818cb2f in PortalRunUtility (portal=0x83ca018, query=0x835b228, dest=0x83c7530, completionTag=0x0) at
pquery.c:987
#7  0x0818ce16 in PortalRun (portal=0x83ca018, count=2147483647, dest=0x835b748, altdest=0x835b748,
completionTag=0xbfbfd7b0"") at pg_list.h:81 
#8  0x08189127 in exec_simple_query (query_string=0x835b018 "EXPLAIN\nSELECT CASE x = 1 WHEN true THEN 1 ELSE 0 END AS
y\nFROMfoo\nORDER BY y;") at postgres.c:1002 
#9  0x0818b693 in PostgresMain (argc=4, argv=0x82e4d98, username=0x82e4d78 "mfuhr") at postgres.c:3168
#10 0x081684e6 in ServerLoop () at postmaster.c:2853
#11 0x08169a01 in PostmasterMain (argc=3, argv=0xbfbfecb8) at postmaster.c:943
#12 0x08133612 in main (argc=3, argv=0xbfbfecb8) at main.c:256

--
Michael Fuhr

Michael Fuhr <mike@fuhr.org> writes:
> On Sat, Dec 10, 2005 at 12:57:13PM -0500, Tom Lane wrote:
>> Could you provide a self-contained test case, please?

> Here's a test case:

Thanks, I'll fix this ... but I'm not certain it is the same problem the
OP is seeing.  The OR join clauses he's got look related to other bugs
we've found in 8.1.

            regards, tom lane

Michael Fuhr <mike@fuhr.org> writes:
> CREATE TABLE foo (x integer);

> EXPLAIN
> SELECT CASE x = 1 WHEN true THEN 1 ELSE 0 END AS y
> FROM foo
> ORDER BY y;

Fixed, but this is a bit too late for 8.1.1.  Meanwhile the easy way to
avoid the bug is to write the CASE in a less obtuse form, like
    CASE WHEN x = 1 THEN 1 ELSE 0 END
or
    CASE x WHEN 1 THEN 1 ELSE 0 END

            regards, tom lane