Strange LIMIT bug in 4.0.4
am 15.10.2002 16:36:31 von Michael Widenius
Hi!
>>>>> "Saulius" == Saulius Gurklys writes:
Saulius> Hello,
Saulius> I think I've found a LIMIT bug.
Saulius> Testcase is uploaded to ftp://support.mysql.com/pub/mysql/secret/,
Saulius> filename is "bug-w-limit-in-404.zip".
Saulius> In ZIP file you will find 2 SQL files:
Saulius> - data.sql - data for test
Saulius> - test.sql - 2 queries, 1rst without LIMIT (returns result rows), 2nd exactly
Saulius> the same as 1rst, but with LIMIT ( returns no result)
Saulius> Besides you will find a README file with some comments, and "\s" and "show variables"
Saulius> output.
Saulius> How-To-Repeat:
Saulius> 1. use data.sql to create required tables
Saulius> 2. rum mysql
I have now fixed this in the MySQL source tree (it will be in 4.0.5);
The bug was in a simple optimisation that I had accidently introduced in 4.0.3
Here is a patch for this:
===== sql_select.cc 1.197 vs edited =====
*** /tmp/sql_select.cc-1.197-6253 Fri Sep 27 08:23:48 2002
--- edited/sql_select.cc Tue Oct 15 16:21:58 2002
***************
*** 111,117 ****
static uint find_shortest_key(TABLE *table, key_map usable_keys);
static bool test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,
ha_rows select_limit, bool no_changes);
! static int create_sort_index(JOIN_TAB *tab,ORDER *order,ha_rows select_limit);
static int remove_duplicates(JOIN *join,TABLE *entry,List- &fields,
Item *having);
static int remove_dup_with_compare(THD *thd, TABLE *entry, Field **field,
--- 111,118 ----
static uint find_shortest_key(TABLE *table, key_map usable_keys);
static bool test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,
ha_rows select_limit, bool no_changes);
! static int create_sort_index(JOIN_TAB *tab,ORDER *order,ha_rows filesort_limit,
! ha_rows select_limit);
static int remove_duplicates(JOIN *join,TABLE *entry,List- &fields,
Item *having);
static int remove_dup_with_compare(THD *thd, TABLE *entry, Field **field,
***************
*** 207,212 ****
--- 208,214 ----
int error, tmp_error;
bool need_tmp,hidden_group_fields;
bool simple_order,simple_group,no_order, skip_sort_order;
+ ha_rows select_limit;
Item::cond_result cond_value;
SQL_SELECT *select;
DYNAMIC_ARRAY keyuse;
***************
*** 662,668 ****
DBUG_PRINT("info",("Sorting for group"));
thd->proc_info="Sorting for group";
if (create_sort_index(&join.join_tab[join.const_tables],group,
! HA_POS_ERROR) ||
make_sum_func_list(&join,all_fields) ||
alloc_group_fields(&join,group))
goto err;
--- 664,670 ----
DBUG_PRINT("info",("Sorting for group"));
thd->proc_info="Sorting for group";
if (create_sort_index(&join.join_tab[join.const_tables],group,
! HA_POS_ERROR, HA_POS_ERROR) ||
make_sum_func_list(&join,all_fields) ||
alloc_group_fields(&join,group))
goto err;
***************
*** 677,683 ****
DBUG_PRINT("info",("Sorting for order"));
thd->proc_info="Sorting for order";
if (create_sort_index(&join.join_tab[join.const_tables],order,
! HA_POS_ERROR))
goto err; /* purecov: inspected */
order=0;
}
--- 679,685 ----
DBUG_PRINT("info",("Sorting for order"));
thd->proc_info="Sorting for order";
if (create_sort_index(&join.join_tab[join.const_tables],order,
! HA_POS_ERROR, HA_POS_ERROR))
goto err; /* purecov: inspected */
order=0;
}
***************
*** 778,784 ****
if (group)
{
thd->proc_info="Creating sort index";
! if (create_sort_index(join.join_tab,group,HA_POS_ERROR) ||
alloc_group_fields(&join,group))
{
free_tmp_table(thd,tmp_table2); /* purecov: inspected */
--- 780,786 ----
if (group)
{
thd->proc_info="Creating sort index";
! if (create_sort_index(join.join_tab,group,HA_POS_ERROR, HA_POS_ERROR) ||
alloc_group_fields(&join,group))
{
free_tmp_table(thd,tmp_table2); /* purecov: inspected */
***************
*** 872,882 ****
DBUG_EXECUTE("where",print_where(conds,"having after sort"););
}
}
if (create_sort_index(&join.join_tab[join.const_tables],
group ? group : order,
! (having || group ||
! (join.select_options & OPTION_FOUND_ROWS)) ?
! HA_POS_ERROR : thd->select_limit))
goto err; /* purecov: inspected */
}
join.having=having; // Actually a parameter
--- 874,904 ----
DBUG_EXECUTE("where",print_where(conds,"having after sort"););
}
}
+ select_limit= thd->select_limit;
+ if (having || group || (join.select_options & OPTION_FOUND_ROWS))
+ select_limit= HA_POS_ERROR;
+ else
+ {
+ /*
+ We can abort sorting after thd->select_limit rows if we there is no
+ WHERE clause for any tables after the sorted one.
+ */
+ JOIN_TAB *table= &join.join_tab[join.const_tables+1];
+ JOIN_TAB *end_table= &join.join_tab[join.tables];
+ for (; table < end_table ; table++)
+ {
+ if (table->select_cond)
+ {
+ /* We have to sort all rows */
+ select_limit= HA_POS_ERROR;
+ break;
+ }
+ }
+ }
if (create_sort_index(&join.join_tab[join.const_tables],
group ? group : order,
! select_limit,
! thd->select_limit))
goto err; /* purecov: inspected */
}
join.having=having; // Actually a parameter
***************
*** 5639,5645 ****
************************************************************ *****************/
static int
! create_sort_index(JOIN_TAB *tab,ORDER *order,ha_rows select_limit)
{
SORT_FIELD *sortorder;
uint length;
--- 5661,5668 ----
************************************************************ *****************/
static int
! create_sort_index(JOIN_TAB *tab, ORDER *order, ha_rows filesort_limit,
! ha_rows select_limit)
{
SORT_FIELD *sortorder;
uint length;
***************
*** 5684,5690 ****
if (table->tmp_table)
table->file->info(HA_STATUS_VARIABLE); // Get record count
table->found_records=filesort(table,sortorder,length,
! select, 0L, select_limit, &examined_rows);
tab->records=table->found_records; // For SQL_CALC_ROWS
delete select; // filesort did select
tab->select=0;
--- 5707,5713 ----
if (table->tmp_table)
table->file->info(HA_STATUS_VARIABLE); // Get record count
table->found_records=filesort(table,sortorder,length,
! select, 0L, filesort_limit, &examined_rows);
tab->records=table->found_records; // For SQL_CALC_ROWS
delete select; // filesort did select
tab->select=0;
Regards,
Monty
--
For technical support contracts, goto https://order.mysql.com/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Michael Widenius
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO
/_/ /_/\_, /___/\___\_\___/ Helsinki, Finland
<___/ www.mysql.com
------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail bugs-thread12730@lists.mysql.com
To unsubscribe, e-mail
Re: Strange LIMIT bug in 4.0.4
am 16.10.2002 20:57:22 von Sinisa Milivojevic
Saulius Gurklys writes:
> Hello,
> I think I've found a LIMIT bug.
> Testcase is uploaded to ftp://support.mysql.com/pub/mysql/secret/,
> filename is "bug-w-limit-in-404.zip".
>
> In ZIP file you will find 2 SQL files:
> - data.sql - data for test
> - test.sql - 2 queries, 1rst without LIMIT (returns result rows), 2nd exactly
> the same as 1rst, but with LIMIT ( returns no result)
> Besides you will find a README file with some comments, and "\s" and "show variables"
> output.
>
> How-To-Repeat:
> 1. use data.sql to create required tables
> 2. rum mysql
>
> Actual result:
> 1rst query from test.sql returns result rows, while 2nd does not !
>
> Expected: result:
> 1rst query returns some rows, 2nd - returns 1 row exactly the same as first row from 1rst query
>
>
> Regards,
> --
> Saulius Gurklys
>
Hi!
This bug was fixed in the latest push to 4.0 tree.
A fix will come in 4.0.5 release.
--
Regards,
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com
------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail bugs-thread12756@lists.mysql.com
To unsubscribe, e-mail
Re: Strange LIMIT bug in 4.0.4
am 16.10.2002 21:03:37 von Sinisa Milivojevic
Saulius Gurklys writes:
> Hello,
> I think I've found a LIMIT bug.
> Testcase is uploaded to ftp://support.mysql.com/pub/mysql/secret/,
> filename is "bug-w-limit-in-404.zip".
>
> In ZIP file you will find 2 SQL files:
> - data.sql - data for test
> - test.sql - 2 queries, 1rst without LIMIT (returns result rows), 2nd exactly
> the same as 1rst, but with LIMIT ( returns no result)
> Besides you will find a README file with some comments, and "\s" and "show variables"
> output.
>
> How-To-Repeat:
> 1. use data.sql to create required tables
> 2. rum mysql
>
> Actual result:
> 1rst query from test.sql returns result rows, while 2nd does not !
>
> Expected: result:
> 1rst query returns some rows, 2nd - returns 1 row exactly the same as first row from 1rst query
>
>
> Regards,
> --
> Saulius Gurklys
Hi!
This is just to inform you that the above bug is fixed in the last
changes in our BK 4.0 repository.
A fix will come in 4.0.5 version of MySQL.
--
Regards,
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com
------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail bugs-thread12759@lists.mysql.com
To unsubscribe, e-mail