Re: default value returned from sql stmt

On 30 Mar 2012, at 10:22, Richard Huxton wrote:

> 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.

Obviously, UNION needs to sort the results to filter out any duplicate rows, so it would change the order of the results of above query and return the 100-valued row for anum values > 100.

UNION ALL will not do so by default, so it would probably behave as Pavel describes. Until you add an ORDER BY to your query.

A more robust implementation would be:

select anum, 0 from t1 where anum = 4
union all
select 100, 1 limit 1
order by 2;

If you don't want the extra column in your query results, you can wrap the query in another select.

Alban Hertroys

--
The scale of a problem often equals the size of an ego.