Thread: 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 notnull unique ); create table snicker_group ( id SERIAL primary key, title varchar(64) not null, snicker_id integernot null references snicker_whatever(id) ); create table snicker_group_mapping ( id SERIAL primary key, snicker_group_id integer not null referencessnicker_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@fbab.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@fbab.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@fbab.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,
butnot in the subquery). *
! * 2. The qual must not refer to any subquery output columns that were * found to have inconsistent types across a
setoperation 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,
becausethat could change the set * of rows returned. This condition is vacuous for DISTINCT, because then * there
areno 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,
butnot 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
setoperation 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,
becausethat could change the set * of rows returned. This condition is vacuous for DISTINCT, because then * there
areno 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
DISTINCTcolumns. *
! * 4. We must not push down any quals that refer to subselect outputs that * return sets, else we'd introduce
functions-returning-setsinto 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
DISTINCTcolumns. *
! * 5. We must not push down any quals that refer to subselect outputs that * return sets, else we'd introduce
functions-returning-setsinto 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
worksbecause 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;