Bug #30732 Really long query can't handle use of GROUP function
Submitted: 30 Aug 2007 19:03 Modified: 30 Sep 2007 19:09
Reporter: Steve Marsh Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Compiling Severity:S2 (Serious)
Version:5.0.32 OS:Microsoft Windows (XP Professional)
Assigned to: CPU Architecture:Any
Tags: 1111, big, error, GROUP, huge, invalid, long, query, UNION

[30 Aug 2007 19:03] Steve Marsh
Description:
I am uncertain whether this is a compiler issue or not, but as best I can guess, I think it would be.

When running a query that incorporates the GROUP function, if it is too long, then I get the error: "#1111 - Invalid use of group function"

There is, in fact, no error with the use of the GROUP function.  The way my query would work is I have multiple databases that I am uniting with the UNION ALL command.  We have a huge pile of databases, one per client to keep tables from becoming too enormous.  But if I am examining too many databases at once, I get that #1111 error.

To test it, I created one small query that made use of the GROUP function.  It worked fine.  Then I added a UNION ALL and repeated the exact same query.  This worked fine as well.  Then I repeated this process many times over and it worked fine as well.  But, once the query reaches somewhere between 72,327 and 72,907 bytes or above, the "#1111 - Invalid use of group function" error is returned.

So for some reason, mySQL doesn't seem to be able to handle really huge queries that incorporate the GROUP function (I've rewritten the query in many different ways using the GROUP function and once I hit that 70K, it dies and gives me that error)

I have not gone over my memory limit for mySQL either, by the way.  Even if I had, I wouldn't be getting the #1111 error.  Or shouldn't at least.  Besides which, I made a query without the GROUP function and made it 200K in size, in the same manner with UNION ALL's between the statements, and it worked fine.  It took a really long time, but it worked fine.

How to repeat:
So here's a sample query I used:
___________________________________________________
SELECT DISTINCT machines.id AS computer_id,
                system2.id AS mem_id,
                machines.name,
                system2.vendor,
                system2.mem_size
FROM (SELECT machine_id,
             MAX( scan_id ) AS 'latest'
      FROM database1.system2
      GROUP BY machine_id
     )latest_scans
     LEFT JOIN database1.system2 ON latest_scans.latest = system2.scan_id
          AND latest_scans.machine_id = system2.machine_id
     LEFT JOIN database1.machines ON latest_scans.machine_id = machines.id
WHERE machines.last_scanned >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY))
___________________________________________________

I repeat this query with UNION ALL's between them, and once I hit around 70K, it gives the error.

i.e:

above query
UNION ALL
above query
UNION ALL
above query
UNION ALL
...

You can probably make any old query using GROUP and UNION ALL's and you should get the same result.

Suggested fix:
I can only imagine that mySQL ceases to know how to parse the GROUP function after a certain number of characters.  Maybe it's too complex for it, I don't know.

I really have no suggestions unless I were able to look at the code myself.
[30 Aug 2007 19:09] Valeriy Kravchuk
Thank you for a problem report. Please, try to repeat with a newer version, 5.0.45 (MySQL binaries), and inform about the results.
[30 Sep 2007 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".