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