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

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

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.1.67, 5.2.14, 5.3.12, 5.5(EOL), 10.0(EOL)
    • 5.5.43, 10.0.18
    • Parser
    • 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

            jplindst Jan Lindström (Inactive)
            hans Jan Kopp
            Votes:
            0 Vote for this issue
            Watchers:
            6 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.