MySQL Bugs: #9324: 4.1: LIKE 'a ' fails to retrieve a row from a TEXT column

Bug #9324	4.1: LIKE 'a ' fails to retrieve a row from a TEXT column
Submitted:	22 Mar 2005 4:26	Modified:	28 Jun 2005 5:11
Reporter:	Heikki Tuuri	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.11-pre	OS:	Linux (Linux)
Assigned to:	Alexander Barkov	CPU Architecture:	Any

Description:
Hi!

heikki@hundin:~/mysql-4.1/client> ./mysql test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 4.1.11-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table t(a tinytext not null, b text, primary key(a(256))) type=myi
sam;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> insert into t values ('a', 'b');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values ('a\0', 'b');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values ('a ', 'b');
Query OK, 1 row affected (0.00 sec)

mysql> select a, length(a) from t where a like 'a ';
Empty set (0.00 sec)

Regards,

Heikki

How to repeat:
See above.

Suggested fix:
N/A

This bug was fixed in 5.0 - LIKE should pad minimal string with zeros.
If we do it in 4.1, then the same test case as above won't work for InnoDB tables.

It's because in 4.1 InnoDB doesn't pad TEXT with spaces for comparison , while MyISAM does.

Sergei,

see the email I sent 4 hours ago. MyISAM-4.1 does not pad TEXT with spaces in a comparison either.

Regards,

Heikki

I don't know how much is my bug related to this one, but it looks similar enough, though I can't exactly see what my bug has to do with padding. So I will post it here, instead of creating a new bug record, because they are most likely related to each other.

mysql> create table test1 (asdf varchar(16));
Query OK, 0 rows affected (0.05 sec)

mysql> create table test2 (asdf varchar(16) primary key);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into test1 values ('a'),('b'),('bb'),('aa');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into test2 values ('a'),('b'),('bb'),('aa');
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from test1;
+------+
| asdf |
+------+
| a    |
| b    |
| bb   |
| aa   |
+------+
4 rows in set (0.00 sec)

mysql> select * from test2;
+------+
| asdf |
+------+
| a    |
| aa   |
| b    |
| bb   |
+------+
4 rows in set (0.00 sec)

mysql> select * from test2 where asdf like 'a%';
Empty set (0.00 sec)

mysql> select * from test2 where asdf like 'b%';
+------+
| asdf |
+------+
| bb   |
+------+
1 row in set (0.00 sec)
       
mysql> select * from test1 where asdf like 'a%';
+------+
| asdf |
+------+
| a    |
| aa   |
+------+
2 rows in set (0.00 sec)

mysql> select * from test1 where asdf like 'b%';
+------+
| asdf |
+------+
| b    |
| bb   |
+------+
2 rows in set (0.00 sec)

So as you can see... if the column is not a primary key, 'like' works exactly as expected. If the column is a primary key, strange things start to happen. If I then create index (unique or non-unique, doesn't matter) on table test1, it displays the same behaviour as test2. MySQL 4.1.11, Linux.

This bug was previously fixed in 5.0.
Wan't fix in 4.1.

Excuse me. Alexander, could you really repeat Jan's bug?
I couldn't repeat it in both 4.1.7-linux and 4.1.12a-win.
I mean, even if the column is a primary key or not, 
'like' works exactly as expected.

I cannot reproduce Jan's bug. Jan, if you still have this problem,
please post as a separate bug. These two problems are not related.
The original bug concerns only TEXT columns.

Sorry for long delay in responding, but I was rather occupied on other issues. In the meantime, I upgraded to 5.0 and problem doesn't occur any more, so it was either fixed between 4.1 and 5.0, or (more probably) it was something on my side - somehow, I think misconfigured character encoding or collation had something to do with it. Anyway, it is fixed now, and because noone else reported something like it, it probably doesn't require further investigation.