Bug #6078 like binary test (case sensitive) ineffective for long search strings

• Submitted: 13 Oct 2004 23:49
• Modified: 14 Oct 2004 8:05
• Reporter: Andrew Philpot
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: Ver 13.5 Distrib 4.1.0-alpha
• OS: pc-linux (i686) Red Hat
• CPU Architecture: Any

[13 Oct 2004 23:49] Andrew Philpot

Description:
Given a non case sensitive 'text' column, I understand that I am to use LIKE BINARY '%string%' to match all elements case sensitively that include 'string'.  However, I see a breakdown in the behavior of this mechanism when the size of the search string becomes 4 or larger (but only sometimes).  It always "seems correct" for search strings of 3 or shorter.  There are no indexes on the table.  I didn't find any other reports on the web of this problem, but I can't figure out any way to explain making a query _more specific_ and yet having it return more tuples.

How to repeat:
Mimic attached transcript.

Suggested fix:
Unknown.  If this is a known issue, perhaps it could be documented better.

[13 Oct 2004 23:52] Andrew Philpot

Transcript showing how LIKE '%BIO%' works case-sensitively but LIKE '%BIOL%' is case-insensitive

Attachment: likebinary.txt (text/plain), 2.38 KiB.

[14 Oct 2004 8:05] Sergei Golubchik

Thank you for taking the time to report a problem.  Unfortunately
you are not using a current version of the product your reported a
problem with -- the problem might already be fixed. Please download
a new version from http://www.mysql.com/downloads/

If you are able to reproduce the bug with one of the latest versions,
please change the version on this bug report to the version you
tested and change the status back to "Open".  Again, thank you for
your continued support of MySQL.

Additional info:

The reason was that starting from 4 characters MySQL uses different algorithm for substring search