[HACKERS] Postgresql crash (signal 11). keywords: distinct, subselect, union

[Magnus Naeslund(f)]

I just wanted to check if this has been fixed in any recent v8.1.x
release, since I'm using v8.1.0 now.

Backtrace:

Program received signal SIGSEGV, Segmentation fault.
0x08152448 in qual_is_pushdown_safe ()
(gdb) bt
#0  0x08152448 in qual_is_pushdown_safe ()
#1  0x08151e47 in set_subquery_pathlist ()
#2  0x08151a3c in set_base_rel_pathlists ()
#3  0x08151960 in make_one_rel ()
#4  0x0815dcaf in query_planner ()
#5  0x0815ea19 in grouping_planner ()
#6  0x0815e2e4 in subquery_planner ()
#7  0x0815dfaa in planner ()
#8  0x08197b7c in pg_plan_query ()
#9  0x08197c39 in pg_plan_queries ()
#10 0x08197e3d in exec_simple_query ()
#11 0x0819a6fe in PostgresMain ()
#12 0x08176356 in BackendRun ()
#13 0x08175c77 in BackendStartup ()
#14 0x08173ee2 in ServerLoop ()
#15 0x08173723 in PostmasterMain ()
#16 0x08139f90 in main ()
#17 0x400dc14f in __libc_start_main () from /lib/libc.so.6


The crashing query is below, if I remove the not is null test it
doesn't crash.

How to reproduce:

create table snicker_whatever(
id SERIAL primary key
);

create table snicker (
idSERIAL primary key,
name_singular text not null unique,
name_plural   text not null unique
);

create table snicker_group (
id  SERIAL primary key,
title   varchar(64) not null,
snicker_id  integer not null references snicker_whatever(id)
);

create table snicker_group_mapping (
id SERIAL primary key,
snicker_group_id   integer not null references snicker_group(id),
snicker_id integer references snicker(id)
);


SELECT DISTINCT
*
FROM
(
SELECT
vtgm.snicker_id
FROM snicker_group_mapping vtgm
WHERE exists
(
SELECT
*
FROM snicker_group vtg
WHERE vtgm.snicker_group_id = vtg.id
AND lower(vtg.title) ~* 'test'
)
UNION
SELECT
snicker.id
FROM snicker
WHERE lower(snicker.name_singular) ~* 'test'
OR lower(snicker.name_plural) ~* 'test'
) AS vt_id
WHERE vt_id is not null;

Regards,
Magnus

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] Postgresql crash (signal 11). keywords: distinct, subselect, union

[Tom Lane]

Magnus Naeslund(f) [EMAIL PROTECTED] writes:
 SELECT DISTINCT
 *
 FROM
 (
 SELECT
 vtgm.snicker_id
 FROM snicker_group_mapping vtgm
 WHERE exists
 (
 SELECT
 *
 FROM snicker_group vtg
 WHERE vtgm.snicker_group_id = vtg.id
 AND lower(vtg.title) ~* 'test'
 )
 UNION
 SELECT
 snicker.id
 FROM snicker
 WHERE lower(snicker.name_singular) ~* 'test'
 OR lower(snicker.name_plural) ~* 'test'
 ) AS vt_id
 WHERE vt_id is not null;

While the crash is certainly a bug, the answer is going to be don't do
that.  Testing a whole record for null-ness is not meaningful.

regards, tom lane

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [HACKERS] Postgresql crash (signal 11). keywords: distinct, subselect, union

[Tom Lane]

Magnus Naeslund(f) [EMAIL PROTECTED] writes:
 I just wanted to check if this has been fixed in any recent v8.1.x
 release, since I'm using v8.1.0 now.

Here's the fix if you need it.

regards, tom lane

Index: allpaths.c
===
RCS file: /cvsroot/pgsql/src/backend/optimizer/path/allpaths.c,v
retrieving revision 1.137.2.1
diff -c -r1.137.2.1 allpaths.c
*** allpaths.c  22 Nov 2005 18:23:10 -  1.137.2.1
--- allpaths.c  13 Feb 2006 16:07:30 -
***
*** 793,803 
   * it will work correctly: sublinks will already have been transformed into
   * subplans in the qual, but not in the subquery).
   *
!  * 2. The qual must not refer to any subquery output columns that were
   * found to have inconsistent types across a set operation tree by
   * subquery_is_pushdown_safe().
   *
!  * 3. If the subquery uses DISTINCT ON, we must not push down any quals that
   * refer to non-DISTINCT output columns, because that could change the set
   * of rows returned.  This condition is vacuous for DISTINCT, because then
   * there are no non-DISTINCT output columns, but unfortunately it's fairly
--- 793,806 
   * it will work correctly: sublinks will already have been transformed into
   * subplans in the qual, but not in the subquery).
   *
!  * 2. The qual must not refer to the whole-row output of the subquery
!  * (since there is no easy way to name that within the subquery itself).
!  *
!  * 3. The qual must not refer to any subquery output columns that were
   * found to have inconsistent types across a set operation tree by
   * subquery_is_pushdown_safe().
   *
!  * 4. If the subquery uses DISTINCT ON, we must not push down any quals that
   * refer to non-DISTINCT output columns, because that could change the set
   * of rows returned.  This condition is vacuous for DISTINCT, because then
   * there are no non-DISTINCT output columns, but unfortunately it's fairly
***
*** 805,811 
   * parsetree representation.  It's cheaper to just make sure all the Vars
   * in the qual refer to DISTINCT columns.
   *
!  * 4. We must not push down any quals that refer to subselect outputs that
   * return sets, else we'd introduce functions-returning-sets into the
   * subquery's WHERE/HAVING quals.
   */
--- 808,814 
   * parsetree representation.  It's cheaper to just make sure all the Vars
   * in the qual refer to DISTINCT columns.
   *
!  * 5. We must not push down any quals that refer to subselect outputs that
   * return sets, else we'd introduce functions-returning-sets into the
   * subquery's WHERE/HAVING quals.
   */
***
*** 834,839 
--- 837,849 
  
Assert(var-varno == rti);
  
+   /* Check point 2 */
+   if (var-varattno == 0)
+   {
+   safe = false;
+   break;
+   }
+ 
/*
 * We use a bitmapset to avoid testing the same attno more than 
once.
 * (NB: this only works because subquery outputs can't have 
negative
***
*** 843,849 
continue;
tested = bms_add_member(tested, var-varattno);
  
!   /* Check point 2 */
if (differentTypes[var-varattno])
{
safe = false;
--- 853,859 
continue;
tested = bms_add_member(tested, var-varattno);
  
!   /* Check point 3 */
if (differentTypes[var-varattno])
{
safe = false;
***
*** 855,861 
Assert(tle != NULL);
Assert(!tle-resjunk);
  
!   /* If subquery uses DISTINCT or DISTINCT ON, check point 3 */
if (subquery-distinctClause != NIL 
!targetIsInSortList(tle, subquery-distinctClause))
{
--- 865,871 
Assert(tle != NULL);
Assert(!tle-resjunk);
  
!   /* If subquery uses DISTINCT or DISTINCT ON, check point 4 */
if (subquery-distinctClause != NIL 
!targetIsInSortList(tle, subquery-distinctClause))
{
***
*** 864,870 
break;
}
  
!   /* Refuse functions returning sets (point 4) */
if (expression_returns_set((Node *) tle-expr))
{
safe = false;
--- 874,880 
break;
}
  
!   /* Refuse functions returning sets (point 5) */
if (expression_returns_set((Node *) tle-expr))
{
safe = false;

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings