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

Unknown column quoted with backticks in HAVING clause when using function.

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 5.1.67, 5.2.14, 5.3.12, 5.5, 10.0
    • Fix Version/s: 5.5.43, 10.0.18
    • Component/s: Parser
    • Labels:
    • Environment:
      12.04.5 LTS (GNU/Linux 3.13.0-40-generic x86_64) - replacement of MySQL 5.6
      14.04.1 LTS (GNU/Linux 3.13.0-40-generic x86_64) - clean install

      Description

      Error occurs when using backticks in HAVING clause with function (e.g. UPPER, CONCAT, DATE_FORMAT, etc...).

      Views are afected by this behavior - forcing backticks.

      How to simulate

      CREATE TABLE `articles` (
        `id` int(11) NOT NULL,
        `title` varchar(45) DEFAULT NULL,
        PRIMARY KEY (`id`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

      INSERT INTO `articles` VALUES ('1', 'Judge delays Oscar Pistorius ruling');
      INSERT INTO `articles` VALUES ('2', 'Warner dedicates century to Hughes');
      INSERT INTO `articles` VALUES ('3', 'Torture report: Battle lines being drawn');

      SELECT `id`, SHA1(`title`) AS `column_1`
      FROM `articles`
      HAVING `column_1` LIKE '5%';
       
      -- 1 row(s) returned

      SELECT `id`, SHA1(`title`) AS `column_1`
      FROM `articles`
      HAVING UPPER(column_1) LIKE '5%';
       
      -- 1 row(s) returned

      SELECT `id`, SHA1(`title`) AS `column_1`
      FROM `articles`
      HAVING UPPER(`column_1`) LIKE '5%';
       
      -- Error Code: 1054. Unknown column 'column_1' in 'having clause'

      Works well on:

      • MySQL 5.5 - clean install,
      • MySQL 5.6 - upgrade from MySQL 5.5
      • MySQL 5.6 - clean install (sql_mode=NO_ENGINE_SUBSTITUTION)

      Throws an error code with unknown column:

      • MariaDB 10.0.15 - clean install
      • MariaDB 10.0.15 - replacement of MySQL 5.6 (sql_mode=NO_ENGINE_SUBSTITUTION)

        Attachments

          Activity

            People

            • Assignee:
              jplindst Jan Lindström
              Reporter:
              hans Jan Kopp
            • Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: