Bug #41084 full-text index added to custom UCA collation not working

• Submitted: 28 Nov 2008 3:00
• Modified: 24 Feb 2009 1:07
• Reporter: Miguel K
• Status: Closed
• Category: MySQL Server: Charsets
• Severity: S3 (Non-critical)
• Version: 5.1.30, 5.1, 6.0 bzr
• OS: Any (Linux, MS Windows XP)
• Assigned to: Alexander Barkov
• CPU Architecture: Any

[28 Nov 2008 3:00] Miguel K

Description:
While adding a full-text index to a custom UCA collation in 5.0 caused MySQL to crash, in 5.1 it doesn't crash, but the index does not work.

Reference for 5.0:
http://bugs.mysql.com/bug.php?id=40897

How to repeat:
CREATE TABLE  test2 (
  `col1` varchar(100) character set utf8 collate utf8_dquote_ci NOT NULL,
  PRIMARY KEY  (`col1`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO test2 (col1) VALUES ('abcd'),('efgh'),('ijkl');
ALTER TABLE test2 ADD FULLTEXT INDEX (`col1`);

-- This gives no results, though it should return one row:
SELECT * FROM test2 where match (col1) against ('abcd');

-- This gives all three rows, when it should only show one:
SELECT * FROM test2 where match (col1) against ('abcd' IN BOOLEAN MODE);

-- Changing the collation to utf8_unicode_ci makes the SELECT statements work as expected.

[28 Nov 2008 10:28] Sveta Smirnova

Thank you for the report.

Verified as described.

[24 Dec 2008 13:43] Alexander Barkov

Reproduced with 5.1 by adding this code into ctype_lmdl.test:

#
# Bug#41084 full-text index added to custom UCA collation not working
#
CREATE TABLE t1 (
  col1 varchar(100) character set utf8 collate utf8_test_ci
);
INSERT INTO t1 (col1) VALUES ('abcd'),('Zeee') ,('efgh'),('ijkl');
ALTER TABLE t1 ADD FULLTEXT INDEX (col1);
SELECT * FROM t1 where match (col1) against ('abcd');
SELECT * FROM t1 where match (col1) against ('abcd' IN BOOLEAN MODE);
DROP TABLE t1;

And the test output is:

+CREATE TABLE t1 (
+col1 varchar(100) character set utf8 collate utf8_test_ci
+);
+INSERT INTO t1 (col1) VALUES ('abcd'),('Zeee') ,('efgh'),('ijkl');
+ALTER TABLE t1 ADD FULLTEXT INDEX (col1);
+SELECT * FROM t1 where match (col1) against ('abcd');
+col1
+SELECT * FROM t1 where match (col1) against ('abcd' IN BOOLEAN MODE);
+col1
+abcd
+Zeee
+efgh
+ijkl
+DROP TABLE t1;

That is the first query didn't return any rows.
The second query returned all rows.

[25 Dec 2008 11:16] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/62323

2750 Alexander Barkov	2008-12-25
      Bug#41084 full-text index added to custom UCA collation not working
      Problem:
      Custom UCA collations didn't set MY_CS_STRNXFRM flag,
      which resulted in "prefix_search" method instead of
      the required "seq_search".
      Fix:
      the missing flag was added.
      
      Upgrade:
      
      - All fulltext indexes with custom UCA collations should be rebuilt.
      
      - Non-fulltext custom UCA indexes should likely be rebuild as well.

[25 Dec 2008 11:36] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/62324

2750 Alexander Barkov	2008-12-25
      Bug#41084 full-text index added to custom UCA collation not working
      Problem:
        Custom UCA collations didn't set the MY_CS_STRNXFRM flag,
        which resulted in "prefix_search" method instead of
        the required "seq_search".
      Problem2: (not metioned in the bug report)
        Custom UCA collations didn't also set the MY_CS_UNICODE flag,
        so an attempt to compare a column with a custom UCA collation
        to another column with a non-Unicode character set led to
        the "illegal mix of collation" error.
      Fix:
        the two missing flags was added into collation initialization.
            
      Upgrade:
            
      - All fulltext indexes with custom UCA collations should be rebuilt.
            
      - Non-fulltext custom UCA indexes should likely be rebuild as well.

[2 Feb 2009 13:35] Bugs System

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/64870

2781 Alexander Barkov	2009-02-02
      Bug#41084 full-text index added to custom UCA collation not working
      
      Problem:
      Custom UCA collations didn't set the MY_CS_STRNXFRM flag,
      which resulted in "prefix_search" method instead of
      the required "seq_search".
      
      Problem2: (not metioned in the bug report)
      Custom UCA collations didn't also set the MY_CS_UNICODE flag,
      so an attempt to compare a column with a custom UCA collation
      to another column with a non-Unicode character set led to
      the "illegal mix of collation" error.
      
      Fix:
      the two missing flags was added into collation initialization.
      
        Upgrade:
      
        - All fulltext indexes with custom UCA collations should be rebuilt.
      
        - Non-fulltext custom UCA indexes should likely be rebuild as well.

[3 Feb 2009 9:40] Bugs System

Pushed into 5.1.32 (revid:[e-mail]-20090203090549-gos3v4320vimrzg6) (version source revid:[e-mail]-20090202133730-q7lwopqr10hz69wo) (merge vers: 5.1.32) (pib:6)

[4 Feb 2009 11:15] Bugs System

Pushed into 6.0.10-alpha (revid:[e-mail]-20090204104420-mw1i2u9lum4bxjo6) (version source revid:[e-mail]-20090202205045-ibm2u4upsznckdmi) (merge vers: 6.0.10-alpha) (pib:6)

[17 Feb 2009 14:56] Bugs System

Pushed into 5.1.32-ndb-6.3.23 (revid:[e-mail]-20090217131017-6u8qz1edkjfiobef) (version source revid:[e-mail]-20090203133556-9rclp06ol19bmzs4) (merge vers: 5.1.32-ndb-6.3.22) (pib:6)

[17 Feb 2009 16:43] Bugs System

Pushed into 5.1.32-ndb-6.4.3 (revid:[e-mail]-20090217134419-5ha6xg4dpedrbmau) (version source revid:[e-mail]-20090203133556-9rclp06ol19bmzs4) (merge vers: 5.1.32-ndb-6.3.22) (pib:6)

[17 Feb 2009 18:19] Bugs System

Pushed into 5.1.32-ndb-6.2.17 (revid:[e-mail]-20090217134216-5699eq74ws4oxa0j) (version source revid:[e-mail]-20090203064601-uyuzm4mielj3fz76) (merge vers: 5.1.32-ndb-6.2.17) (pib:6)

[24 Feb 2009 1:07] Paul DuBois

Noted in 5.1.32, 6.0.10 changelogs.

FULLTEXT indexes did not work for Unicode columns that used a custom
UCA collation.