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

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 (
id SERIAL 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

"Magnus Naeslund(f)" <mag(at)fbab(dot)net> 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

Tom Lane wrote:
> "Magnus Naeslund(f)" <mag(at)fbab(dot)net> 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.
>

Yep, my "workaround" (or bugfix) was to push that null test infront of
the exists. Also I might not need the surrounding distinct either,
doesn't union make the result distinct?

So if I would like to do the test after the union, I should add "AS xxx"
on both union queries and then "vt_id.xxx is not null", right ?

Regards,
Magnus

"Magnus Naeslund(f)" <mag(at)fbab(dot)net> 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 -0000 1.137.2.1
--- allpaths.c 13 Feb 2006 16:07:30 -0000
***************
*** 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;