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

COLLATE keyword doesn't work in PREPARE query

Details

    Description

      COLLATE keyword doesn't work in PREPARE query.
      Example :

      CREATE TABLE IF NOT EXISTS `tt` (
          `test` varchar(500) COLLATE utf8mb4_unicode_ci NOT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
       
      SELECT * FROM `tt` WHERE `test` LIKE  'jj' COLLATE utf8mb4_unicode_ci 
      

      Text work well

      	PREPARE stmt FROM 'SELECT * FROM `tt` WHERE `test` LIKE  ? COLLATE utf8mb4_unicode_ci'
      

      will throw an error

      Erreur SQL (1253) : COLLATION 'utf8mb4_unicode_ci' is not valid for CHARACTER SET 'binary'"
      

      In PREPARE, collate is always considering character set as binary.

      Attachments

        Issue Links

          Activity

            Same on MySQL 5.7.

            More precisely, it's not just "in prepare", it's "in prepare for placeholders".
            I fully expect it to turn out to be intentional or at least inevitable, but I will leave it to bar to decide and comment on it.

            elenst Elena Stepanova added a comment - Same on MySQL 5.7. More precisely, it's not just "in prepare", it's "in prepare for placeholders". I fully expect it to turn out to be intentional or at least inevitable, but I will leave it to bar to decide and comment on it.

            Looks like a bug.

            bar Alexander Barkov added a comment - Looks like a bug.

            Hi ralf.gebhardt@mariadb.com, I think a few hours should be enough.

            bar Alexander Barkov added a comment - Hi ralf.gebhardt@mariadb.com , I think a few hours should be enough.

            There is a simple workaround:

            PREPARE stmt FROM 'SELECT * FROM `tt` WHERE _utf8mb4`test` COLLATE utf8mb4_unicode_ci' LIKE  ?
            

            bar Alexander Barkov added a comment - There is a simple workaround: PREPARE stmt FROM 'SELECT * FROM `tt` WHERE _utf8mb4`test` COLLATE utf8mb4_unicode_ci' LIKE ?

            This bug was fixed starting from 10.11 only, because there was a major refactoring in the code handling the COLLATE clause.

            Fixing in an earlier version would need a different patch. It's not feasible to spend much time on a new patch as there is a simple workaround.

            bar Alexander Barkov added a comment - This bug was fixed starting from 10.11 only, because there was a major refactoring in the code handling the COLLATE clause. Fixing in an earlier version would need a different patch. It's not feasible to spend much time on a new patch as there is a simple workaround.

            People

              bar Alexander Barkov
              diego dupin Diego Dupin
              Votes:
              0 Vote for this issue
              Watchers:
              7 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.