List:General Discussion« Previous MessageNext Message »
From:Machiel Richards Date:November 8 2013 11:39am
Subject:Re: Mysql 5.1 union with group by for results
View as plain text  
Hi Jesper

     I was just discussing this with the development manager now and the 
following was noted.

         - The query was written for mysql 4.0 originally and it seems 
that in version 5.0 they had enabled some legacy support stuff ( I am 
not too familiar with this as it is before my mysql time ;-) ).

         - I have now explained to them what the problem is and they 
will be working with the developers to rewrite all these queries.

Regards
Machiel.





On 08/11/2013 13:27, Jesper Wisborg Krogh wrote:
> Hi Machiel,
>
> On 8/11/2013 20:04, Machiel Richards wrote:
>> Good day all
>>
>>        I am hoping someone can assist me in the following.
>>
>>         One of our servers were running mysql 5.0 still and as part 
>> of a phased upgrade route we have upgraded to version 5.1.
>>
>>         However since the upgrade, the query below gives us an error 
>> stating that the syntax is incorrect and I simply cant seem to find 
>> out what is actually wrong as all tests and changes have been giving 
>> us the same.
>>
>>         I have tried many suggestions from the net but to no avail.
>>
>>         The query is as follows:
>
> Using a shorter but equivalent query, you have:
>
>    (SELECT t.id, t.name, SUM(val) FROM t1 t)
>    UNION
>    (SELECT t.id, t.name, SUM(val) FROM t2 t)
>    GROUP BY t.id, t.name;
>
> That does not work in 5.0 either (at least in 5.0.96):
>
>    ERROR 1064 (42000): You have an error in your SQL syntax; check the
>    manual that corresponds to your MySQL server version for the right
>    syntax to use near 'GROUP BY t.id, t.name' at line 4
>
>
> The issue is that you are trying grouping the entire UNION result, but 
> at that point there is no SELECT any longer - there is just the result 
> set. You are also referencing tables that exists inside each of the 
> SELECT statements, but at the time the GROUP BY is reached, there are 
> no tables. Note that as written the two SELECT parts will also give 
> non-deterministic results as you have an aggregate function but no 
> GROUP BY, so the values of id and val1 will be "random".
>
> What you probably want instead is either:
>
>    (SELECT t.id, t.name, SUM(val) FROM t1 t GROUP BY t.id, t.name)
>    UNION
>    (SELECT t.id, t.name, SUM(val) FROM t2 t GROUP BY t.id, t.name);
>
> or
>
>    SELECT a.id, a.name, SUM(val)
>       FROM (
>             (SELECT t.id, t.name, t.val FROM t1 t)
>            UNION
>             (SELECT t.id, t.name, t.val FROM t2 t)
>            ) a
>      GROUP BY a.id, a.name;
>
>
> On a side note:
>
>>
>>      AND SUBSTRING(t.Day,1,7) >= '2013-08'
>>      AND SUBSTRING(t.Day,1,7) <= '2013-11')
>
> Assuming t.Day is a date, datetime, or timestamp column, you can 
> rewrite that WHERE clause to something like (depending on the exact 
> data type):
>
>    t.Day BETWEEN '2013-08-01 00:00:00' AND '2013-11-30 23:59:59'
>
> or
>
>    t.Day >= '2013-08-01 00:00:00' AND t.Day < '2013-12-01 00:00:00'
>
>
> That way you will be able to use an index for that condition.
>
> Best regards,
> Jesper Krogh
> MySQL Support
>

Thread
Mysql 5.1 union with group by for resultsMachiel Richards8 Nov
  • Re: Mysql 5.1 union with group by for resultsJesper Wisborg Krogh8 Nov
    • Re: Mysql 5.1 union with group by for resultsMachiel Richards8 Nov
Re: Mysql 5.1 union with group by for resultsJohan De Meersman8 Nov
  • Re: Mysql 5.1 union with group by for resultsMachiel Richards8 Nov