Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-6146

Can't mix (latin1_swedish_ci,NUMERIC) and (utf8_unicode_ci,IMPLICIT) for MATCH

        Details

        • Type: Bug
        • Status: Closed
        • Priority: Major
        • Resolution: Fixed
        • Affects Version/s: 5.5.36, 10.0.10
        • Fix Version/s: 5.5.38, 10.0.11
        • Component/s: None
        • Labels:
        • Environment:
          Debian

          Description

          Shouldn't it be able to mix numbers and strings in a match query?

          CREATE TABLE `sitelog` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `added` datetime NOT NULL,
  `txt` text COLLATE utf8_unicode_ci NOT NULL,
  `uid` int(11) NOT NULL,
  `id2` int(11) NOT NULL,
  `id2_type` char(1) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `uid` (`uid`),
  KEY `id2` (`id2`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

select txt, unix_timestamp(added), uid, id2, id2_type    from sitelog    where match (id2, uid, txt) against ('' in boolean mode); 

          ERROR 1270 (HY000): Illegal mix of collations (latin1_swedish_ci,NUMERIC), (latin1_swedish_ci,NUMERIC), (utf8_unicode_ci,IMPLICIT) for operation 'match'

            Gliffy Diagrams

              Attachments

                Activity

                Ascending order - Click to sort in descending order
                Hide
                Permalink
                elenst Elena Stepanova added a comment -

                Thank you for the report.

                Reproducible on MySQL 5.5-5.7, MariaDB 5.5, 10.0.
                Not reproducible on MariaDB 5.1-5.3, MySQL 5.1.

                I haven't found in documentation any explanation why it shouldn't work.
                Alexander Barkov,
                Could you please take a look?

                Show
                elenst Elena Stepanova added a comment - Thank you for the report. Reproducible on MySQL 5.5-5.7, MariaDB 5.5, 10.0. Not reproducible on MariaDB 5.1-5.3, MySQL 5.1. I haven't found in documentation any explanation why it shouldn't work. Alexander Barkov , Could you please take a look?
                Hide
                Permalink
                bar Alexander Barkov added a comment -

                Looks like a bug.

                Show
                bar Alexander Barkov added a comment - Looks like a bug.
                Hide
                Permalink
                bar Alexander Barkov added a comment -

                Pushed into 5.5.

                Show
                bar Alexander Barkov added a comment - Pushed into 5.5.
                Hide
                Permalink
                serg Sergei Golubchik added a comment -

                This broke the fulltext test in the --ps-protocol mode.
                Reopening.

                Show
                serg Sergei Golubchik added a comment - This broke the fulltext test in the --ps-protocol mode. Reopening.
                Hide
                Permalink
                bar Alexander Barkov added a comment -

                Pushed an additional patch fixing the "mtr --ps fulltext" failure.

                Show
                bar Alexander Barkov added a comment - Pushed an additional patch fixing the "mtr --ps fulltext" failure.

                  People

                  • Assignee:
                    bar Alexander Barkov
                    Reporter:
                    XTF Olaf van der Spek
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    4 Start watching this issue

                    Dates

                    • Created:
                      Updated:
                      Resolved: