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

"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;