MySQL Bugs: #5451: GROUP BY DESC not working

Bug #5451	GROUP BY DESC not working
Submitted:	7 Sep 2004 15:22	Modified:	13 Sep 2004 11:05
Reporter:	jason Engel	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S1 (Critical)
Version:	4.0.16-nt	OS:	Windows (Win 2k sp4)
Assigned to:		CPU Architecture:	Any

Description:
THE DESC modifier is not working. This was a previously reported bug that was meant to of being fixed. But does not seem to be fixed in the latest production version of MySql running on Win 2k.

How to repeat:
CREATE TABLE WITH DATA:

CREATE TABLE `tbl_news` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `link` bigint(20) NOT NULL default '0',
  PRIMARY KEY  (`id`),
) TYPE=MyISAM;

INSERT INTO tbl_news VALUES("1", "1094566877");
INSERT INTO tbl_news VALUES("2, "1094566877");

THEN TO GET ERROR:

SELECT id FROM tbl_news group by link DESC

Should return 2, but returns 1

Suggested fix:
Implement previously implemented patch onto current version for MySql running on win2k

I don't know which other bug you are refering to, but
i can't see any fault in the result as you didn't have 'id' in the GROUP BY clause.
This is not standard SQL but MySQL allows this (unless you run in ANSI mode)
and behaves as documented in

  MySQL extends the use of GROUP BY so that you can use columns or 
  calculations in the SELECT list that don't appear in the GROUP BY clause. 
  This stands for any possible value for this group. You can use this to get 
  better performance by avoiding sorting and grouping on unnecessary items. 
  For example, you don't need to group on customer.name in the following query:

  mysql> SELECT order.custid, customer.name, MAX(payments)
      ->        FROM order,customer
      ->        WHERE order.custid = customer.custid
      ->        GROUP BY order.custid;

  In standard SQL, you would have to add customer.name to the GROUP BY
  clause. In MySQL, the name is redundant if you don't run in ANSI mode.

  Do not use this feature if the columns you omit from the GROUP BY part 
  are not unique in the group! You will get unpredictable results.

As your 'id' values obviously differ for a single 'link' value you get just that:
unpredictable 'id' values ordered desc by 'link' (although it is hard to see a 
sorting order for 'link' if there is only one value).

Forgot to insert the documentation URL i pasted from in my
previous reply, it is 

  http://dev.mysql.com/doc/mysql/en/GROUP-BY-hidden-fields.html

Okay, i've thought of the problem some more. What I have is:

TABLE2 --------------
   id : link : title
1) 1 : alpha : title1
2) 2 : alpha : title2
3) 3 : alpha : title3
4) 4 : alpha : title4
----------------------

select max(id), title from table2 group by link

returns

 max(id) : title
1)      4 : title1

WHY DOES IT NOT RETURN (pulls hair out!)

 max(id) : title
1)      4 : title4

from reading the documentation i thought

select id, title from table2 group by link DESC

should return

    id     : title
1) 4     : title4

but returns
    id    : title
1) 1     : title1

As stated above (docuemtation quote):

  Do not use this feature if the columns you omit from the GROUP BY part 
  are not unique in the group! You will get unpredictable results.

This applies here as your 'title' column values are not unique for the 
link='alpha' group.

I am still looking for a solution to this exact problem. My specific problem with screenshots can be seen here: http://forum.nucleuscms.org/viewtopic.php?p=76562#76562

I am having trouble getting distinct results from multiple tables, as GROUP BY always groups my results backwards, returning the oldest result available.

What is the correct way to get my desired result.

Thank you.