Apache OpenOffice (AOO) Bugzilla – Issue 21125
"group by" bug
Last modified: 2010-11-11 03:31:09 UTC
- create a new mysql datasource via mysql odbc - open a new query - insert two tables - create a new join - group by id - execute query WHERE sintax are ignored, included relations Example: Code that you can see when switch design view: SELECT `TRelacions`.`IdRelacio`, `TEmail`.`Email` FROM `Atlas`.`TRelacions` `TRelacions`, `Atlas`.`TEmail` `TEmail` WHERE ( `TRelacions`.`IdRelacio` = `TEmail`.`IdRelacio` ) GROUP BY `TRelacions`.`IdRelacio` Code executed in MySQL (than I can see in my log): SELECT `TRelacions`.`IdRelacio`, `TEmail`.`Email` FROM `Atlas`.`TRelacions` `TRelacions`, `Atlas`.`TEmail` `TEmail`GROUP BY `TRelacions`.`IdRelacio` HAVING 0 = 1
Hi lazi, When i execute your query everything is works as expect. The query you found in your log file is OK , because with this query we retrieve the columns which are in your query result, without execute the whole query. So as the result is right for me, I set this issue to 'resolved works for me'. Feel free to reopen this issue, if your are not happy with this. Bye Marc
Hi Marc, Yes, it's true. Query works as expect. But if you have more than 2000 rows in each tables, query are extremly slow and MySQL consume excesive CPU. I think best solution to get the same is use this query: SELECT `TRelacions`.`IdRelacio`, `TEmail`.`Email` FROM `Atlas`.`TRelacions` `TRelacions`, `Atlas`.`TEmail` `TEmail` WHERE 0 = 1 GROUP BY `TRelacions`.`IdRelacio` Use WHERE sintax instead HAVING. This works well in MySQL. Bye Marc. Jordi Llonch
This is a duplicate of issue 18040. The problem is that a HAVING clause is much more expensive than a WHERE clause, since the latter can easily be optimized by the server before any data is retrieved, while the former usually is *not* optimized this way (though it *could*). As a result, a "HAVING 0 = 1" produces heavy load on the server (we had some sample data where it was in the range of several GB), which can even make the system unresponsive (well, at least windows, on Linux it's just a "little bit slow"). I had a discussion about this with a MySQL developer, and the result was that MySQL behaves well (I originally suspected the opposite), and that OOo is wrong in adding this HAVING clause - we should use WHERE instead. The fix for this is covered by issue 18040. The reasons why I do *not* close this one here as duplicate: Marc, I'd be interested in your opinion whether it's worth having this in 1.1.1. Personally, I tend to thinking it *is* worth. In this case, we should port the fix from issue 18040 down to the 1.1 branch, using this bug here.
forgot: the conversation with the MySQL developer can be found (in parts) in issue 18039.
Hi all, with the explanation from fs I close this issue as duplicate. Bye Marc *** This issue has been marked as a duplicate of 18039 ***
close
Hi all, I reopen this issue, because I haven't read the comment from fs. ;-( We will use this issue for fixing it in the 1.1.1 tree. Bye Marc
send to the right developer.
Fixed in CWS dba01pp2.
fs->msc: please verify in CWS dba01pp2
verified in cws
verified
- checked sql statement in cws lock file - in comparison to a 680m12 it is several times faster by running this kind of queries
Hi, this will be fixed in OOo 1.1.1 which will be available soon. I close this issue now as fixed. bye Marc
change subcomponent to 'none'
Created attachment 74141