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

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.

Thank you for the report.

Verified as described.

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.

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.

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.

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.

Pushed into 5.1.32 (revid:joro@sun.com-20090203090549-gos3v4320vimrzg6) (version source revid:azundris@mysql.com-20090202133730-q7lwopqr10hz69wo) (merge vers: 5.1.32) (pib:6)

Pushed into 6.0.10-alpha (revid:kostja@sun.com-20090204104420-mw1i2u9lum4bxjo6) (version source revid:aelkin@mysql.com-20090202205045-ibm2u4upsznckdmi) (merge vers: 6.0.10-alpha) (pib:6)

Pushed into 5.1.32-ndb-6.3.23 (revid:tomas.ulin@sun.com-20090217131017-6u8qz1edkjfiobef) (version source revid:tomas.ulin@sun.com-20090203133556-9rclp06ol19bmzs4) (merge vers: 5.1.32-ndb-6.3.22) (pib:6)

Pushed into 5.1.32-ndb-6.4.3 (revid:tomas.ulin@sun.com-20090217134419-5ha6xg4dpedrbmau) (version source revid:tomas.ulin@sun.com-20090203133556-9rclp06ol19bmzs4) (merge vers: 5.1.32-ndb-6.3.22) (pib:6)

Pushed into 5.1.32-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090217134216-5699eq74ws4oxa0j) (version source revid:tomas.ulin@sun.com-20090203064601-uyuzm4mielj3fz76) (merge vers: 5.1.32-ndb-6.2.17) (pib:6)

Noted in 5.1.32, 6.0.10 changelogs.

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