List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:December 27 1999 3:33pm
Subject:bug in nested sorts (detailed)
View as plain text  
Hi!

I was scanning old mails to check if we have any unsolved problems and
come across the following:

>>>>> "localuser" == localuser  <localuser@stripped>
> writes:

>> Description:
localuser> Okay, first a dump of about 100 rows:

<cut>

localuser> I want the output sorted first by bgdate, and then by myidnt,
localuser> but when I try the obvious:

localuser> select daily.myidnt, bgdate, obdate, obvalu from begin, daily where
> begin.myidnt = daily.myidnt order by bgdate, daily.myidnt;

localuser> I get output sorted by myidnt only (completely ignoring
localuser> the values of bgdate):

<cut>

The problem is that MySQL tried to do an optimzation on ORDER BY that
is used by a constant reference.  For example in the following query:

elect daily.myidnt, bgdate, obdate, obvalu from begin,
daily where begin.myidnt = daily.myidnt order by daily.myidnt, bgdate;

one can remove bgdate from the order by without affecting the result..

Here is a fix for this for MySQL 3.22:  (MySQL 3.23 doesn't have this
problem):  This patch will be included in MySQL 3.22.28:


*** /my/monty/master/mysql-3.22.26a/sql/sql_select.cc	Fri Sep  3 00:39:51 1999
--- ./sql_select.cc	Mon Dec 27 17:08:34 1999
***************
*** 2034,2067 ****
    if (tab->cached_eq_ref_table)			// If cached
      return tab->eq_ref_table;
    tab->cached_eq_ref_table=1;
!   for (;;)
!   {
!     if (tab->type == JT_CONST)
!       return (tab->eq_ref_table=1);		// We can skip this /* purecov: inspected */
!     if (tab->type != JT_EQ_REF)
!       return (tab->eq_ref_table=0);		// We must use this
!     REF_FIELD *ref_field=tab->table->reginfo.ref_field;
!     REF_FIELD *end=ref_field+tab->table->reginfo.ref_fields;
!     for (; ref_field != end ; ref_field++)
!     {
!       if (ref_field->field->table)
!       {						// Not a const ref
! 	ORDER *order;
! 	for (order=start_order ; order ; order=order->next)
! 	{
! 	  if (order->item[0]->type() == Item::FIELD_ITEM &&
! 	      ((Item_field*) order->item[0])->field == ref_field->field)
! 	    break;
! 	}
! 	if (order)
! 	  break;				// Used in ORDER BY
! 	if (!eq_ref_table(join,start_order,
! 			  join->map2table[ref_field->field->table->tablenr]))
! 	  return (tab->eq_ref_table=0);
        }
      }
-     return tab->eq_ref_table=1;
    }
  }
  
  
--- 2038,2087 ----
    if (tab->cached_eq_ref_table)			// If cached
      return tab->eq_ref_table;
    tab->cached_eq_ref_table=1;
!   if (tab->type == JT_CONST)
!     return (tab->eq_ref_table=1);		// We can skip this /* purecov: inspected */
!   if (tab->type != JT_EQ_REF)
!     return (tab->eq_ref_table=0);		// We must use this
! 
!   REF_FIELD *ref_field=tab->table->reginfo.ref_field;
!   REF_FIELD *end=ref_field+tab->table->reginfo.ref_fields;
!   uint found=0;
!   table_map map=tab->table->map;
! 
!   for (; ref_field != end ; ref_field++)
!   {
!     if (ref_field->field->table)
!     {						// Not a const ref
!       ORDER *order;
!       for (order=start_order ; order ; order=order->next)
!       {
! 	if (order->item[0]->type() == Item::FIELD_ITEM &&
! 	    ((Item_field*) order->item[0])->field == ref_field->field)
! 	  break;
        }
+       /* Reference field is used in order by */
+       if (order)
+       {
+ 	found++;
+ 	order->used|=map;
+       }
+       if (!eq_ref_table(join,start_order,
+ 			join->map2table[ref_field->field->table->tablenr]))
+ 	return (tab->eq_ref_table=0);
      }
    }
+   /* Check that there was no reference to table before sort order */
+   for ( ; found && start_order ; start_order=start_order->next)
+   {
+     if (start_order->used & map)
+     {
+       found--;
+       continue;
+     }
+     if (start_order->item[0]->used_tables() & map)
+       return (tab->eq_ref_table=0);
+   }
+   return tab->eq_ref_table=1;
  }
  
  
***************
*** 2087,2099 ****
  static ORDER *
  remove_const(JOIN *join,ORDER *first_order,bool *simple_order)
  {
    ORDER *order,**prev_ptr;
    table_map first_table= (table_map) 1L << join->const_tables;
    table_map not_const_tables= ~(first_table-1L);
    table_map ref;
    prev_ptr= &first_order;
!   *simple_order= (join->tables != join->const_tables &&
! 		  join->join_tab[join->const_tables].on_expr ? 0 : 1);
  
    /* NOTE: A variable of not_const_tables ^ first_table; breaks gcc 2.7 */
    DBUG_ENTER("remove_const");
--- 2107,2121 ----
  static ORDER *
  remove_const(JOIN *join,ORDER *first_order,bool *simple_order)
  {
+   if (join->tables == join->const_tables)
+     return 0;					// No need to sort
+ 
    ORDER *order,**prev_ptr;
    table_map first_table= (table_map) 1L << join->const_tables;
    table_map not_const_tables= ~(first_table-1L);
    table_map ref;
    prev_ptr= &first_order;
!   *simple_order= join->join_tab[join->const_tables].on_expr ? 0 : 1;
  
    /* NOTE: A variable of not_const_tables ^ first_table; breaks gcc 2.7 */
    DBUG_ENTER("remove_const");
*** /my/monty/master/mysql-3.22.26a/sql/sql_parse.cc	Mon Aug 30 04:15:31 1999
--- ./sql_parse.cc	Mon Dec 27 17:24:40 1999
***************
*** 1610,1615 ****
--- 1609,1615 ----
    *item_ptr= item;
    order->item=item_ptr;
    order->free_me=0;
+   order->used=0;
    link_in_list(&current_thd->proc_list,(byte*) order,(byte**)
&order->next);
    return 0;
  }
--- 1622,1627 ----
*** /my/monty/master/mysql-3.22.26a/sql/table.h	Fri Sep  3 00:39:51 1999
--- ./table.h	Mon Dec 27 17:10:07 1999
***************
*** 31,36 ****
--- 31,37 ----
  
    Field  *field;			/* If tmp-table group */
    char	 *buff;				/* If tmp-table group */
+   table_map used;
  } ORDER;
  
  typedef struct st_grant_info

Regards
Monty

PS: Starting next year we will create a new mailing list:
    bugs@stripped ;  To this list we will only accept bug reports,
    posted with mysqlbug and with a repeatable example. All MySQL
    developers will subscribe and read this list! This will ensure that
    all posted bugs will get solved quickly!
Thread
bug in nested sorts (detailed)localuser30 Nov
  • Re: bug in nested sorts (detailed)André Bjärby30 Nov
    • Re: bug in nested sorts (detailed)localuser1 Dec
  • bug in nested sorts (detailed)Michael Widenius27 Dec