MySQL Bugs: #2147: LIMIT 1 INCORECT

Bug #2147	LIMIT 1 INCORECT
Submitted:	18 Dec 2003 1:11	Modified:	22 Dec 2003 5:12
Reporter:	Sergey Sokolov
Status:	Closed
Category:	Server	Severity:	S2 (Serious)
Version:	4.1.1	OS:	Microsoft Windows (WINDOWS XP)
Assigned to:	Sinisa Milivojevic	Target Version:

Description:
IN MySQL 4.1.1 Alpha ON Windows XP, Incorect work sql-query with "ORDER BY LIMIT 1"

P.S. Sory, my english very bag.

How to repeat:
TABLE:

CREATE TABLE test (
  id smallint(6) unsigned NOT NULL default '0',
  menu tinyint(4) NOT NULL default '0',
  KEY id (id),
  KEY menu (menu)
) TYPE=MyISAM;

INSERT INTO test
VALUES (11384, 2),(11392, 2);

Incorect work sql-query:

SELECT id
FROM test
WHERE id <11984 AND menu =2
ORDER BY id DESC 
LIMIT 1

I can verify this on Linux with 4.1.1 and 4.1.2.
The result is:
ERROR 2013 (HY000): Lost connection to MySQL server during query

In other words, it's a crashing bug, so I've raised the severity
to serious.

WIthout the LIMIT clause, or with LIMIT 2, the query doesn't
crash.

Yes it crashes also on XP. Below the call stack:

mysqld.exe!ptr_compare(unsigned int * compare_length=0x045eef04, unsigned char * *
a=0x036f73f4, unsigned char * * b=0x036f73f4)  Line 62 + 0x3	C
mysqld.exe!queue_insert(st_queue * queue=0x045eeed4, unsigned char * element=0x036f73e8) 
Line 182 + 0x31	C
mysqld.exe!merge_buffers(st_sort_param * param=0x045ef058, st_io_cache *
from_file=0x045ef0ac, st_io_cache * to_file=0x036fc518, unsigned char *
sort_buffer=0x036f7190, st_buffpek * lastbuff=0x036f73e8, st_buffpek * Fb=0x036f73e8,
st_buffpek * Tb=0x036f73e8, int flag=1)  Line 810 + 0xd	C++
mysqld.exe!merge_index(st_sort_param * param=0x045ef058, unsigned char *
sort_buffer=0x036f7190, st_buffpek * buffpek=0x036f73e8, unsigned int maxbuffer=0,
st_io_cache * tempfile=0x045ef0ac, st_io_cache * outfile=0x036fc518)  Line 983 + 0x2b	C++
mysqld.exe!filesort(THD * thd=0x036ef040, st_table * table=0x036fb968, st_sort_field *
sortorder=0x036fe978, unsigned int s_length=1, SQL_SELECT * select=0x036fe708, unsigned
long max_rows=1, unsigned long * examined_rows=0x045ef198)  Line 225 + 0x2f	C++
mysqld.exe!create_sort_index(THD * thd=0x036ef040, JOIN * join=0x036fd558, st_order *
order=0x036fd500, unsigned long filesort_limit=1, unsigned long select_limit=1)  Line 7176
+ 0x21	C++
mysqld.exe!JOIN::exec()  Line 1470 + 0x73	C++
mysqld.exe!mysql_select(THD * thd=0x036ef040, Item * * * rref_pointer_array=0x036ef498,
st_table_list * tables=0x036fd110, unsigned int wild_num=0, List<Item> & fields={...},
Item * conds=0x036fd418, unsigned int og_num=1, st_order * order=0x036fd500, st_order *
group=0x00000000, Item * having=0x00000000, st_order * proc_param=0x00000000, unsigned
long select_options=42224128, select_result * result=0x036fd548, st_select_lex_unit *
unit=0x036ef2c4, st_select_lex * select_lex=0x036ef3b0)  Line 1603	C++
mysqld.exe!handle_select(THD * thd=0x036ef040, st_lex * lex=0x036ef2b8, select_result *
result=0x036fd548)  Line 193 + 0x89	C++
mysqld.exe!mysql_execute_command(THD * thd=0x036ef040)  Line 1916 + 0x11	C++
mysqld.exe!mysql_parse(THD * thd=0x036ef040, char * inBuf=0x036fd020, unsigned int
length=73)  Line 3927 + 0x9	C++
mysqld.exe!dispatch_command(enum_server_command command=COM_QUERY, THD * thd=0x036ef040,
char * packet=0x036f0739, unsigned int packet_length=74)  Line 1387 + 0x1d	C++
mysqld.exe!do_command(THD * thd=0x036ef040)  Line 1217 + 0x31	C++
mysqld.exe!handle_one_connection(void * arg=0x036ef040)  Line 979 + 0x9	C++
mysqld.exe!pthread_start(void * param=0x036fca90)  Line 63 + 0x7	C
mysqld.exe!_threadstart(void * ptd=0x036fbdb8)  Line 173 + 0xd	C
kernel32.dll!77e6d33b()

I can't repeat it with 4.1.2

Paul and Miguel, please tell me what am I doing wrong ??

Test case :
drop table if exists t1;
CREATE TABLE t1 (  id smallint(6) unsigned NOT NULL default '0',  menu tinyint(4) NOT NULL
default '0',  KEY id (id),  KEY menu (menu)) TYPE=MyISAM;
INSERT INTO t1 VALUES (11384, 2),(11392, 2);
SELECT id FROM t1 WHERE id <11984 AND menu =2 ORDER BY id DESC LIMIT 1 ;
drop table if exists t1;

Result from 4.1.2:
[/mnt/work/mysql-4.1]$ ./client/mysql bug < tmp/dump 
id
49417

It doesn't appear to happen on every system.
Sinisa, I did a copy and paste of your test case:

No crash:
MySQL 4.1.2 on Mac OS X 10.3.2 (Panther)

Crash:

MySQL 4.1.2 on Red Hat 7.0
MySQL 4.1.2 on Red Hat 9.0

What kind of system did you use for the test?

I used  my  own  Linux   ..... ;o)

kernel 2.4.23, glibc 2.2.5 (unpatched)
gcc  3.3.2

May be I login  to your machine  and see  for  myself   ??

Testing on XP and Suse 9.0 I got either a crash or wrong query
result.

2Sinisa Milivojevic
>>Result from 4.1.2:
>>[/mnt/work/mysql-4.1]$ ./client/mysql bug < tmp/dump 
>>id
>>49417

49417 - not corect resulte. In table not present id = 49417

Quite correct, but I get this after last BK pull:

[/mnt/work/mysql-4.1]$ ./client/mysql bug < tmp/dump 
id
11392

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Fix will come in 4.1.2.

This wa a  rather complicated issue.