Bug #3152 Two conditions in 'where' with 'or' behaves incorrectly
Submitted: 12 Mar 2004 4:34 Modified: 25 Mar 2004 5:07
Reporter: Alex Pyltsov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S2 (Serious)
Version:3.23.58 4.0.19 4.1.2 OS:
Assigned to: Michael Widenius CPU Architecture:Any

[12 Mar 2004 4:34] Alex Pyltsov 
Description:
Table with one column and KEY for this column that has two rows.
CREATE TABLE test (
  text1 varchar(32) default NULL,
  KEY key1 (text1)
) TYPE=MyISAM;
INSERT INTO test VALUES ('teststring');
INSERT INTO test VALUES ('nothing');

When try to query this:
  select * from test where text1='teststring' or text1 like 'teststring_%'

no rows is returned! This problem appeared only if one of condition
  text1 like 'teststring_%'
and another
  text1='teststring'

All of the following query works correctly:
  select * from test where text1='teststring' (1 row)
  select * from test where text1 like 'teststring_%' (0 rows)
or even
  select * from test 
    where text1='teststring' or text1 like 'teststring%' (1 row)

How to repeat:
DROP TABLE IF EXISTS test;
CREATE TABLE test (
  text1 varchar(32) default NULL,
  KEY key1 (text1)
) TYPE=MyISAM;

INSERT INTO test VALUES ('teststring');
INSERT INTO test VALUES ('nothing');

select * from test where text1='teststring' or text1 like 'teststring_%';
[12 Mar 2004 12:58] Dean Ellis 
Verified against 3.23.58, 4.0.19 and 4.1.2 in Linux.  Thank you.

Query returns incorrect results with MyISAM and BDB; correct results for InnoDB and HEAP.
[18 Mar 2004 23:24] Alexey Botchkov 
Simpler query here:
select * from test1 where text1='teststring' or text1 > 'teststring\t';
also returns empty recordset.

The reason of this bug is in the difference in how we compare strings in Field_string::cmp and in ha_key_cmp.
We use same strnncoll, but with different 'length' parameters

In Field_string::cmp we compare whole buffers with ending spaces, so
  "teststr\t" < "teststr "  (because '\t' < ' ')
in ha_key_cmp we compare significant parts of strings only, so
  "teststr\t" > "teststr"   (because of bigger length)

In the initial case with LIKE we got "teststr\0" and "teststr" strings
to compare.
[18 Mar 2004 23:26] Alexey Botchkov 
IMHO Most proper fix here would be adding 'length' parameters to SEL_ARG and
sending these to the strnncoll.
[18 Mar 2004 23:27] Alexey Botchkov 
bk commit - 4.1 tree (hf:1.1793)
[25 Mar 2004 5:07] Michael Widenius 
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 be in 4.1.2
(Can't fix this in earlier versions as this would force a repair of some old tables)