Re: Column headings in csv

On Sat, 23 May 2020 19:11:00 -0400, Jerry Stuckle
<[email protected]> wrote:

>On 5/23/2020 2:14 PM, Richard Yates wrote:
>> On Sat, 23 May 2020 17:04:43 +0200, Luuk <[email protected]> wrote:
>> 
>>> On 23-5-2020 15:49, Richard Yates wrote:
>>>> On Sat, 23 May 2020 10:13:00 +0200, Luuk <[email protected]> wrote:
>>>>
>>>>> On 23-5-2020 01:47, Richard Yates wrote:
>>>>>> On Sun, 02 Feb 2020 13:57:48 +0100, Kees Nuyt <[email protected]>
>>>>>> wrote:
>>>>>>
>>>>>
>>>>>>> Yeah, you can only ORDER the result of the UNION, not on of its
>>>>>>> parts.
>>>>>>
>>>>>> Actually you can if you order each part and then set a limit. Recently
>>>>>> I found this out and am using this (in a php application).
>>>>>>
>>>>>> (Distribution sites are each assigned to one of several districts. The
>>>>>> query pulls out the ones from one of the districts to list first at
>>>>>> the top of a dropdown menu, and then lists the remaining ones.)
>>>>>>
>>>>>> $distsitesq="
>>>>>> (select distsite, name from distsites
>>>>>> where ID_district=$ID_district
>>>>>> order by name limit 999) union
>>>>>> (select distsite, name from distsites
>>>>>> where ID_district<>$ID_district
>>>>>> order by name limit 999)";
>>>>>>
>>>>>> 999 is chosen because in this example it is far larger than the
>>>>>> distsites table will ever be. Setting the limit enforces the ordering
>>>>>> of each part.
>>>>>>
>>>>> This is not right, it might give look like the correct result, but it is
>>>>> not guaranteed the correct result.
>>>>
>>>> It does produce the correct result in my application. How would it not
>>>> produce that result?
>>>
>>> https://dev.mysql.com/doc/refman/8.0/en/union.html
>>>
>>> "Use of ORDER BY for individual SELECT statements implies nothing about
>>> the order in which the rows appear in the final result because UNION by
>>> default produces an unordered set of rows. "
>> 
>> Yes, I understand what the manual says but quoting the manual does not
>> answer my question. I would point out two things:
>> 
>> 1. Without the LIMITs in the two parts, an error is indeed thrown by
>> mysql, but with the limits it passes, suggesting that mysql thinks
>> that the query is proper.
>> 
>> 2. The manual describes the "default" behavior. It seems that the
>> default may be overridden in some circumstances, such as the query I
>> used. That is even the definition of "default". The manual does not
>> say "always produces an unordered set"
>> 
>> Nevertheless, the query that I used works. Since you said that the
>> correct result was not guaranteed, I was asking if you knew in which
>> circumstances it would not work, or reasons that it would not work.
>> 
>
>I agree with Luuk - this isn't a good construct.  As for it "working" - 
>I suspect it's just a hole in the SQL parser code.  Adding LIMIT to a 
>clause should not change the validity of ORDER.

I am not at all claiming that it is better or even good. And I have
substituted in Luuk's suggestion (thanks again!) which also showed me
a clever technique that I can probably use in other places. 

But wouldn't a "hole in the parser code" just account for it not
throwing an error and not explain why it really does work? I tried
scrambling the order of the table just to see if the fact that it
worked was just an artifact of a particular order, but it still works.
No matter what I do I cannot make it not work. 

So perhaps, as you say, adding a LIMIT to a clause (in a union) SHOULD
not change the validity of ORDER in the clause in some hypothetical
moral domain, but it seems that it actually does do that in unions. 

Why not call this an undocumented feature rather than a "hole"?