MySQL Bugs: #4661: Query written in Query Browser doesn't match what's executed

Bug #4661	Query written in Query Browser doesn't match what's executed
Submitted:	21 Jul 2004 0:11	Modified:	21 Jul 2004 10:45
Reporter:	Scott Fleck	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Query Browser	Severity:	S1 (Critical)
Version:	1.0.3 Alpha	OS:	Windows (XP)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
I type the following query and execute:
select distinct entry_pnt from xlog2004_07;

show processlist indicates that the following query is running:
| Id    | User      | Host                        | db          | Command     | Time  | State                                                          | Info                                                              |
| 95672 | scottf    | scottf.dtn.com:2859         | scottf      | Query       |     4 | Copying to tmp table                                           | SELECT distinct entry_pnt, curr_pchan, trans_num FROM xlog2004_07|

I saw it happen in previous versions of query browser, but wasn't able to catch it in the processlist.  The query returns the entry_pnt field for every row of the table (over 2 million) instead of the 60 or 70 that are truly distinct by that field.  For what its worth the primary key is trans_num, curr_pchan (reverse of the order that they magically show up in the query).  entry_pnt is the first part of another index.

How to repeat:
Requires a very large test set of data to see it execute on the server.  I can zip and provide if someone wants to download.

Suggested fix:
Don't alter the query entered.

Thanks for reporting, but this issue has already been reported (http://bugs.mysql.com/bug.php?id=4619) and fixed in the source repository and will be in the next release.

What we do is to add the PK fields to the select if the user does a select from a single table. This is useful if a user does SELECT username FROM onlineuser and wants to edit the username. This would not be possible without selecting the PK also.

We forgot to detect the DISTINCED keyword, therefore the result was not as expected.

We will make this feature optional so it will not cause problems anymore.