Re: Column headings in csv

On 5/23/2020 9:07 PM, Richard Yates wrote:
> On Sat, 23 May 2020 17:19:09 -0700, Richard Yates
> <[email protected]> wrote:
> 
>> 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"?
> 
> Still cannot break it. Did discover that the parentheses around each
> part are necessary.
> 
> (select owner, make, model, color from cars
> where owner='Jerry'
> order by make, model limit 10)
> union
> (select owner, make, model, color from cars
> where owner='Luuk'
> order by color limit 10)
> union
> (select owner, make, model, color from cars
> where owner<>'Luuk' and owner<>'Jerry'
> order by model limit 10)
> 
> Jerry	Audi	A3	red
> Jerry	Audi	TT	silver
> Jerry	Ford	Fiesta	green
> Jerry	Ford	Fiesta	blue
> Jerry	GMC	Savannah	blue
> Jerry	GMC	Sierra	blue
> 
> Luuk	GMC	Yukon	black
> Luuk	Ford	F100	blue
> Luuk	GMC	Savannah	blue
> Luuk	Ford	Mustang	green
> Luuk	Ford	Fiesta	red
> 
> Zor	Audi	A4	white
> TNP	Ford	Edsel	black
> 

So?  A hole in the parser code may allow it to work - and it might even 
work correctly - for now.  But that can change at any time. 
Undocumented behavior is *never* safe.



-- 
==================
Remove the "x" from my email address
Jerry Stuckle
[email protected]
==================