MySQL Bugs: #4049: Upgrade from 4.1.1 to 4.1.2 makes SELECT LIKE 'xxx%' fail

Bug #4049	Upgrade from 4.1.1 to 4.1.2 makes SELECT LIKE 'xxx%' fail
Submitted:	8 Jun 2004 2:31	Modified:	8 Jun 2004 8:09
Reporter:	Robert Nice	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.2	OS:	Linux (RH 7.3)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Scenario:
After upgrading from 4.1.1 to 4.1.2.
Binary dist on Linux/Redhat 7.3.

SELECT * from Table WHERE Field LIKE 'blahblah%';
returns no rows when
SELECT * from Table WHERE Field LIKE 'blahblah';
does

Whoops!

I'd submit schemas, but it doesn't appear on new tables, only ones previously used with 4.1.0(maybe)/4.1.1.

BUG 2: If something in the table structure has changed, a check table should report an error.

Couldn't find anything about it, but I do understand this is a volatile release that can change at any time.

How to repeat:
mysql> select PurchaseID from Purchases WHERE BillingEmail LIKE 'rnice@fraudscrub.com%';
Empty set (0.00 sec)

mysql> select PurchaseID FROM Purchases WHERE BillingEmail LIKE 'rnice@fraudscrub.com';
+------------+
| PurchaseID |
+------------+
|      57386 |
+------------+

mysql> check table Purchases;
+----------------------+-------+----------+----------+
| Table                | Op    | Msg_type | Msg_text |
+----------------------+-------+----------+----------+
| FraudScrub.Purchases | check | status   | OK       |
+----------------------+-------+----------+----------+
1 row in set (1.17 sec)

mysql> alter table Purchases type=MyISAM;
Query OK, 23274 rows affected, 1 warning (6.28 sec)
Records: 23274  Duplicates: 0  Warnings: 0

mysql> select PurchaseID FROM Purchases WHERE BillingEmail LIKE 'rnice@fraudscrub.com%';
+------------+
| PurchaseID |
+------------+
|      57386 |
+------------+
1 row in set (0.00 sec)

mysql> alter table Purchases type=InnoDB;
Query OK, 23274 rows affected, 1 warning (3.94 sec)
Records: 23274  Duplicates: 0  Warnings: 0

mysql> select PurchaseID FROM Purchases WHERE BillingEmail LIKE 'rnice@fraudscrub.com%';
+------------+
| PurchaseID |
+------------+
|      57386 |
+------------+
1 row in set (0.00 sec)

Suggested fix:
As above, move table between handlers, it all seems to fix itself.

Hi!

What character set you are using?

This is probably a duplicate of Bug #3957, if you are using a non-latin1 character set. Though I do not understand how rebuilding the table does help for you.

What does EXPLAIN SELECT ... say about the execution plan of the query?

The reason for Bug #3957 is that the sorting order of MySQL was changed between 4.1.1 and 4.1.2.

We have a fix pending for Bug #3957.

Regards,

Heikki

Hi!

I think I now understand why rebuilding the table fixes the problem. This bug is, indeed, a duplicate of Bug #3957.

In 4.1.0 and 4.1.1, tables created with the default 'latin1_swedish_ci' charset were created by InnoDB as 'non-default-charset' tables, because InnoDB still thought that the name of the MySQL default charset is called 'latin1', like it was in 4.0. InnoDB-4.1.2 uses my_strnncoll() to do the string comparisons for non-default-charset tables, and the LIKE 'abc%' bug appears when that function is used.

If you create a new table with the default charset latin1_swedish_ci in 4.1.2, then InnoDB treats it as a 'default-charset' table, and does the string comparisons internally, without calling my_strnncoll(). That is why the bug does not appear then!

We will try to release 4.1.3 with the bugfix in a few weeks.

Thank you,

Heikki