Re: Postgresql crash (signal 11). keywords: distinct, subselect, - Mailing list pgsql-hackers

From Magnus Naeslund(f)
Subject Re: Postgresql crash (signal 11). keywords: distinct, subselect,
Date
Msg-id 43F0A84E.6030206@fbab.net
Whole thread Raw
In response to Re: Postgresql crash (signal 11). keywords: distinct, subselect, union  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-hackers
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



pgsql-hackers by date:

Previous
From: Andrew Dunstan
Date:
Subject: Re: Why don't we allow DNS names in pg_hba.conf?
Next
From: mark@mark.mielke.cc
Date:
Subject: Re: Why don't we allow DNS names in pg_hba.conf?