Bug #7304 SELECT DISTINCT yields unexpected results when query is made editable
Submitted: 15 Dec 2004 0:29 Modified: 15 Dec 2004 8:53
Reporter: W.P. Nijhof Email Updates:
Status: Duplicate Impact on me:
Category:MySQL Query Browser Severity:S3 (Non-critical)
Version:1.1.3 OS:Microsoft Windows (Windows XP SP1)
Assigned to: CPU Architecture:Any

[15 Dec 2004 0:29] W.P. Nijhof
This may not be a bug, but a feature.

Option 'Enforce queries to be editable by adding primary key columns to the select' is turned on by default. While this is a nice feature, it screws up the correct result when you execute a 'SELECT DISTINCT column FROM table' query; you don't get distinct values, you get the same result as if the query were 'SELECT column FROM table'.

How to repeat:
Execute a SELECT DISTINCT query on a table with a primary key running with default options.

Suggested fix:
-disable this option for a SELECT DISTINCT query (it doesn't make a query with GROUP BY editable).
-disable this option by default.
-show a message attending the user to this option when a SELECT DISTINCT query is first run against a table with a primary key.
-add primary key columns to the visible output. This may cause users to look in the options dialog to find out where the extra columns come from.
-rewrite the select statement in the Query Toolbar, for the same reason.
[15 Dec 2004 0:41] W.P. Nijhof
For some reason, I didn't find this bug when I first searched for it. Looks like the first report of this was bug #4619.