Bug #22343 | boolean fulltext search becomes case-sensitive | ||
---|---|---|---|
Submitted: | 14 Sep 2006 12:55 | Modified: | 25 Sep 2006 13:28 |
Reporter: | [ name withheld ] | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server: FULLTEXT search | Severity: | S3 (Non-critical) |
Version: | 5.0.26-BK, 5.0.22 | OS: | Linux (Linux (Fedora Core 5)) |
Assigned to: | Sergey Vojtovich | CPU Architecture: | Any |
Tags: | boolean, case-sensitive, fulltext |
[14 Sep 2006 12:55]
[ name withheld ]
[14 Sep 2006 15:11]
Valeriy Kravchuk
Thank you for a bug report. Verified just as described, also with 5.0.26-BK. Only one table is needed to demonstrate a problem: mysql> SELECT spots.* FROM spots WHERE MATCH (spots.product,spots.title) aga inst ( 'tobies' IN BOOLEAN MODE); +----+-------------+-----------+----------------+ | id | product | title | postproduction | +----+-------------+-----------+----------------+ | 1 | Tobies Test | Testtitle | 1 | +----+-------------+-----------+----------------+ 1 row in set (0.01 sec) mysql> SELECT spots.* FROM spots WHERE MATCH (spots.product,spots.title, spo ts.postproduction) against ( 'tobies' IN BOOLEAN MODE); Empty set (0.00 sec) mysql> SELECT spots.* FROM spots WHERE MATCH (spots.product,spots.title, spo ts.postproduction) against ( 'Tobies' IN BOOLEAN MODE); +----+-------------+-----------+----------------+ | id | product | title | postproduction | +----+-------------+-----------+----------------+ | 1 | Tobies Test | Testtitle | 1 | +----+-------------+-----------+----------------+ 1 row in set (0.00 sec) mysql> select version(); +--------------+ | version() | +--------------+ | 5.0.26-debug | +--------------+ 1 row in set (0.00 sec) So, looks like adding INT column to MATCH lists affects case-sensitivity for searches. This looks like a bug for me.
[25 Sep 2006 13:28]
Sergei Golubchik
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ When you mix arguments in different character sets in one function, they are all converted to a single common character set. Integer converted to a sting gets a binary character set. As a result the argument of MATCH is converted to a binary character set: mysql> select charset(concat("a","b")); +--------------------------+ | charset(concat("a","b")) | +--------------------------+ | latin1 | +--------------------------+ 1 row in set (0.00 sec) mysql> select charset(concat("a","b",4)); +----------------------------+ | charset(concat("a","b",4)) | +----------------------------+ | binary | +----------------------------+ 1 row in set (0.00 sec) In a binary character set a string is a sequence of bytes, the concept of "letter case" is not applicable: mysql> select upper(concat("a","b")); +------------------------+ | upper(concat("a","b")) | +------------------------+ | AB | +------------------------+ 1 row in set (0.00 sec) mysql> select upper(concat("a","b",4)); +--------------------------+ | upper(concat("a","b",4)) | +--------------------------+ | ab4 | +--------------------------+ 1 row in set (0.00 sec)