MySQL Bugs: #3449: Limit Problem

Bug #3449	Limit Problem
Submitted:	12 Apr 2004 17:04	Modified:	27 Apr 2004 15:53
Reporter:	Allann Jones	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.1.1-alpha	OS:	GNU/Linux x86
Assigned to:	Dean Ellis	CPU Architecture:	Any

Description:
Limit problem:

When I execute "Instruction 1" it's returns one row as result idependently of the number of rows that I'm limiting in the query. But when I remove the limit instruction like in "Instruction 2" it's returns very many rows (8545) that are the correct.

------------

Thank you.

Allann J.

How to repeat:
Instruction 1:
--------------

select * from zaurus.dictionary where ortography like 'A%' limit 50

Instruction 2:
--------------

select * from zaurus.dictionary where ortography like 'A%'

Table definition:
-----------------

CREATE TABLE IF NOT EXISTS zaurus.dictionary (
  dictionary_id integer unsigned PRIMARY KEY AUTO_INCREMENT,
  ortography varchar(255) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  frequency integer unsigned NOT NULL DEFAULT 0,
  pos_id integer unsigned,
  language_id integer unsigned,
  area_id integer unsigned,
  etymology_id integer unsigned,
  derived bool DEFAULT 0,
  description text,
  register_dt datetime,
  status char(1) NOT NULL DEFAULT 'A',
  
  INDEX ix_zaurus_dictionary_01 (language_id),
  INDEX ix_zaurus_dictionary_02 (area_id),
  INDEX ix_zaurus_dictionary_03 (etymology_id),
  INDEX ix_zaurus_dictionary_04 (pos_id)

) TYPE=InnoDB;

I cannot repeat this using the current 4.1.2 source tree.  It is possible that this is a bug that has already been corrected, but in order to verify that we would need actual data which demonstrates the issue with your provided table/queries.