Re: default value returned from sql stmt

2012/3/30 Richard Huxton <dev(at)archonet(dot)com>:
> On 30/03/12 08:46, Pavel Stehule wrote:
>>
>> 2012/3/30 Richard Huxton<dev(at)archonet(dot)com>:
>>>
>>> On 29/03/12 23:28, Pavel Stehule wrote:
>>>>
>>>>
>>>> select anum from t1 where anum = 4
>>>> union all select 100 limit 1;
>>>
>>>
>>>
>>> I'm not sure the ordering here is guaranteed by the standard though, is
>>> it?
>>> You could end up with the 4 being discarded.
>>
>>
>> A order is random for only "UNION", "UNION ALL" should to respect
>> order.  But I didn't check it in standard.
>
>
> Let's put it this way - a quick bit of googling can't find anything that
> says the order *is* guaranteed, and (almost?) no other operations do so by
> default.
>

yes, it should to work in pg, but it should not work else where.

secure solution is

SELECT x FROM (SELECT * FROM (SELECT 1, x FROM tab WHERE x = 10 LIMIT
1) s1 UNION ALL SELECT 2, -1000 ORDER BY 1 LIMIT 1) s2;

Regards

Pavel Stehule

>
> --
>  Richard Huxton
>  Archonet Ltd