ports/105679: [PATCH] databases/mysql-server: SELECT DISTINCT sorting bugfix (introduced in 4.1.21)

[Michael Ranner]

>Number:         105679
>Category:       ports
>Synopsis:       [PATCH] databases/mysql-server: SELECT DISTINCT sorting bugfix (introduced in 4.1.21)
>Confidential:   no
>Severity:       non-critical
>Priority:       low
>Responsible:    freebsd-ports-bugs
>State:          open
>Quarter:        
>Keywords:       
>Date-Required:
>Class:          change-request
>Submitter-Id:   current-users
>Arrival-Date:   Sun Nov 19 20:30:19 GMT 2006
>Closed-Date:
>Last-Modified:
>Originator:     Michael Ranner
>Release:        FreeBSD 6.1-RELEASE-p10 i386
>Organization:
>Environment:
System: FreeBSD dwarf.jawa.at 6.1-RELEASE-p10 FreeBSD 6.1-RELEASE-p10 #2: Sat Nov  4 20:03:02 CET
>Description:
MySQL 4.1.21 has introduced an sorting bug in conjunction with SELECT DISTINCT. The attached
patch is from the MySQL project and will be shipped with the future 4.1.22 release.
http://lists.mysql.com/commits/10578

Added file(s):
- files/patch-sql::sql_select.cc

Port maintainer (ale at FreeBSD.org) is cc'd.

Generated with FreeBSD Port Tools 0.77
>How-To-Repeat:
>Fix:

--- mysql-server-4.1.21_1.patch begins here ---
diff -ruN --exclude=CVS /usr/ports/databases/mysql-server/Makefile /usr/ports/databases/mysql41-server.update/Makefile
--- /usr/ports/databases/mysql-server/Makefile	Sun Nov 19 21:22:10 2006
+++ /usr/ports/databases/mysql41-server.update/Makefile	Sun Nov 19 21:20:54 2006
@@ -7,7 +7,7 @@
 
 PORTNAME?=	mysql
 PORTVERSION=	4.1.21
-PORTREVISION?=	0
+PORTREVISION?=	1
 CATEGORIES=	databases
 MASTER_SITES=	${MASTER_SITE_MYSQL}
 MASTER_SITE_SUBDIR=	MySQL-4.1
diff -ruN --exclude=CVS /usr/ports/databases/mysql-server/files/patch-sql::sql_select.cc /usr/ports/databases/mysql41-server.update/files/patch-sql::sql_select.cc
--- /usr/ports/databases/mysql-server/files/patch-sql::sql_select.cc	Thu Jan  1 01:00:00 1970
+++ /usr/ports/databases/mysql41-server.update/files/patch-sql::sql_select.cc	Sun Nov 19 21:20:27 2006
@@ -0,0 +1,76 @@
+--- sql/sql_select.cc.orig	Wed Jul 19 17:10:38 2006
++++ sql/sql_select.cc	Sun Nov 19 21:07:19 2006
+@@ -630,6 +630,36 @@
+     if (!order && org_order)
+       skip_sort_order= 1;
+   }
++  /*
++     Check if we can optimize away GROUP BY/DISTINCT.
++     We can do that if there are no aggregate functions and the
++     fields in DISTINCT clause (if present) and/or columns in GROUP BY
++     (if present) contain direct references to all key parts of
++     an unique index (in whatever order).
++     Note that the unique keys for DISTINCT and GROUP BY should not
++     be the same (as long as they are unique).
++
++     The FROM clause must contain a single non-constant table.
++  */
++  if (tables - const_tables == 1 && (group_list || select_distinct) &&
++      !tmp_table_param.sum_func_count)
++  {
++    if (group_list &&
++       list_contains_unique_index(join_tab[const_tables].table,
++                                 find_field_in_order_list,
++                                 (void *) group_list))
++    {
++      group_list= 0;
++      group= 0;
++    }
++    if (select_distinct &&
++       list_contains_unique_index(join_tab[const_tables].table,
++                                 find_field_in_item_list,
++                                 (void *) &fields_list))
++    {
++      select_distinct= 0;
++    }
++  }
+   if (group_list || tmp_table_param.sum_func_count)
+   {
+     if (! hidden_group_fields && rollup.state == ROLLUP::STATE_NONE)
+@@ -698,36 +728,6 @@
+ 			     &simple_group);
+     if (old_group_list && !group_list)
+       select_distinct= 0;
+-  }
+-  /*
+-     Check if we can optimize away GROUP BY/DISTINCT.
+-     We can do that if there are no aggregate functions and the
+-     fields in DISTINCT clause (if present) and/or columns in GROUP BY
+-     (if present) contain direct references to all key parts of
+-     an unique index (in whatever order).
+-     Note that the unique keys for DISTINCT and GROUP BY should not
+-     be the same (as long as they are unique).
+-
+-     The FROM clause must contain a single non-constant table.
+-  */
+-  if (tables - const_tables == 1 && (group_list || select_distinct) &&
+-      !tmp_table_param.sum_func_count)
+-  {
+-    if (group_list &&
+-       list_contains_unique_index(join_tab[const_tables].table,
+-                                 find_field_in_order_list,
+-                                 (void *) group_list))
+-    {
+-      group_list= 0;
+-      group= 0;
+-    }
+-    if (select_distinct &&
+-       list_contains_unique_index(join_tab[const_tables].table,
+-                                 find_field_in_item_list,
+-                                 (void *) &fields_list))
+-    {
+-      select_distinct= 0;
+-    }
+   }
+   if (!group_list && group)
+   {
--- mysql-server-4.1.21_1.patch ends here ---

>Release-Note:
>Audit-Trail:
>Unformatted: