Created attachment 8345 [details]
sample data

looking at the ODBC logs, it seems OOo chanegs the query to

SELECT `artists`.`name`, `albums`.`name` FROM `albums`, `tracks`,
`artists`GROUP BY `tracks`.`al_id` HAVING  0 = 1

which is not the same as originally saved in the query. So problem 1
here is that OOo incorrectly changes the SQL statement.

Created attachment 8350 [details]
ODBC trace log for displaying the query

with the same data as in the attachment netjuke.sql, when the query

SELECT `artists`.`name`, `albums`.`name` FROM `albums`, `tracks`,
`artists`GROUP BY `tracks`.`al_id` HAVING  0 = 1

is executed in the MySQL console, MySQL draws 100% of the CPU, and
seems to loop. So this is a MySQL problem, which I'm going to report
to their bug list, and not a OOo problem.
I'll submit another issue for the incorect change of the SQL statement.

closing as INVALID, because it's an MySQL bug.
submitted follow-up issue 18040 for the improper translation of the
SQL statement done by OOo.

also submitted issue 18041 for completeness.
closing INVALID.

re-opening.
as justification, lemme paste the mail I got as answer from the mySQL
people (thanks to Alexander Keremidarski for his very quick response
here!):

------------------------------------------------------------------

> Frank,
> 
> Frank Schönheit wrote:
>> Hi,
>> 
>> During using OpenOffice.org with MySQL, it seems a user discovered
a bug
>> in MySQL.
>> The details are described in
>> http://www.openoffice.org/issues/show_bug.cgi?id=18039.
>> 
>> In short: If a sample SELECT statement (attached to the issue above) is
>> executed against MySQL, with the sample data also attached to the above
>> issue, then the CPU usage of the mysdqld-nt process jumps to 100%, and
>> the query doesn't return.
> 
> We will investigate this case, but Query in question is Cartesian
Product between 
> 3 tables!
> 
> mysql> select count(*) from albums;
> +----------+
> | count(*) |
> +----------+
> |      305 |
> +----------+
> 1 row in set (0.01 sec)
> 
> mysql> select count(*) from tracks;
> +----------+
> | count(*) |
> +----------+
> |     3638 |
> +----------+
> 1 row in set (0.00 sec)
> 
> mysql> select count(*) from artists;
> +----------+
> | count(*) |
> +----------+
> |      231 |
> +----------+
> 1 row in set (0.00 sec)
> 
> 
> 305*3638*231 = 256315290
> 
> Query which will provide 256 million rows and which can store them
in some 
> temporary space before sending to client will be always slow.
> 
> I don't agree with you about
> "closing as INVALID, because it's an MySQL bug."
> 
> Interrim result of this Cartesian product consists of 2 columns
CHAR(100) + 1 Byte 
> per column * 256315290 rows = 51519373290 bytes which is almost 48GB
of data. This 
> number is huge by any means. You should not expect your machine to
handle easily 
> this amount of data.
> 
> 
> I see nothing strange. You are just using MySQL in a way it is not
intended to be 
> used. Cartesian Product is almost never meaningfull.
> 
> You can knock down any server with sending huge amount of data request.
> 
> 
> This is very good example of careless SQL usage. No matter if this
query is 
> written by hand or generated by some programs. Processing 3 very
small tables 
> less than 2 Mb in size and very few rows can demand huge amount of
resources.
> 
>> The original bug problem was discovered while using OpenOffice.org to
>> access the MySQL database, but it persists if the MySQL console is used
>> to execute the statement, so this is clearly a MySQL problem.
>> 
>> Any hints anybody?
> 
> Do you agree with me this is not a bug really?
> 
>> Thanks & Ciao
>> Frank
> 
> Best regards
> 
> -- 
>   Are you MySQL certified? -> http://www.mysql.com/certification
>   For technical support contracts, visit
https://order.mysql.com/?ref=msal
>      __  ___     ___ ____  __
>     /  |/  /_ __/ __/ __ \/ /    Mr. Alexander Keremidarski
<salle@mysql.com>
>    / /|_/ / // /\ \/ /_/ / /__   MySQL AB, Full-Time Developer
>   /_/  /_/\_, /___/\___\_\___/   Sofia, Bulgaria
>           <___/  www.mysql.com


------------------------------------------------------------------

Well, being really used to seeing "WHERE 0 = 1" in OOo-generated
statements, I did not really see that we're talking about HAVING here
instead of WHERE. As in HAVING is evaluated after the results have
been retrieved, MySQL tries to allocate all the ~48GB Alexander
mentioned in his mail - which of course takes a while :)

In theory, MySQL could also evaluate the HAVING before retrieving the
data, seeing that it evalutes to FALSE regardless of the data, and
quickly create an empty result set. In practice, checking HAVING
before retrieving data sounds ... unorthodox at least.

Now, the real problem seems to be that OOo creates this invalid
statement from the original statement. This is already covered in bug
18040.

*** This issue has been marked as a duplicate of 18040 ***

closing DUPLICATE

*** Issue 21591 has been marked as a duplicate of this issue. ***

*** Issue 21125 has been marked as a duplicate of this issue. ***

change subcomponent to 'none'