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(¤t_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!
