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

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

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

          I assume "Works well on MySQL 5.5" is a typo, you meant 5.6?

          elenst Elena Stepanova added a comment - I assume "Works well on MySQL 5.5" is a typo, you meant 5.6?
          hans Jan Kopp added a comment -

          Both versions ... tested on 5.5, 5.6 (just for sure) as I mentioned.

          hans Jan Kopp added a comment - Both versions ... tested on 5.5, 5.6 (just for sure) as I mentioned.
          elenst Elena Stepanova added a comment - - edited

          It's a bit strange because it is reproducible on the current MySQL 5.5 tree and on older versions as well; but it's unimportant really, I was just trying to clarify some details.

          It used to be an upstream bug which was fixed in 5.6.11 by the revision below, but is still present in all MariaDB versions.

                  revno: 4740.1.4
                  revision-id: guilhem.bichot@oracle.com-20130207144106-zd4opujo00osvsnu
                  parent: guilhem.bichot@oracle.com-20130207160851-a7zbsnawgbt1qpu7
                  committer: Guilhem Bichot <guilhem.bichot@oracle.com>
                  branch nick: 5.6
                  timestamp: Thu 2013-02-07 15:41:06 +0100
                  message:
                    fix for
                    Bug#16165981 VIEWS: CRASHING IN ITEM_REF::FIX_FIELDS
                    Bug#16221433 MYSQL REJECTS QUERY DUE TO BAD RESOLUTION OF NAMES IN HAVING; VIEW UNREADABLE
                    See comment of sql_view.cc.

          Test case (exactly the same as in the description, just put together for more convenient copy-paste):

          CREATE TABLE `articles` (
            `id` int(11) NOT NULL,
            `title` varchar(45) DEFAULT NULL,
            PRIMARY KEY (`id`)
          );
          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%';
           
          SELECT `id`, SHA1(`title`) AS `column_1`
          FROM `articles`
          HAVING UPPER(column_1) LIKE '5%';
           
          SELECT `id`, SHA1(`title`) AS `column_1`
          FROM `articles`
          HAVING UPPER(`column_1`) LIKE '5%';

          The bug is that the last SELECT fails with ER_BAD_FIELD_ERROR.

          elenst Elena Stepanova added a comment - - edited It's a bit strange because it is reproducible on the current MySQL 5.5 tree and on older versions as well; but it's unimportant really, I was just trying to clarify some details. It used to be an upstream bug which was fixed in 5.6.11 by the revision below, but is still present in all MariaDB versions. revno: 4740.1.4 revision-id: guilhem.bichot@oracle.com-20130207144106-zd4opujo00osvsnu parent: guilhem.bichot@oracle.com-20130207160851-a7zbsnawgbt1qpu7 committer: Guilhem Bichot <guilhem.bichot@oracle.com> branch nick: 5.6 timestamp: Thu 2013-02-07 15:41:06 +0100 message: fix for Bug#16165981 VIEWS: CRASHING IN ITEM_REF::FIX_FIELDS Bug#16221433 MYSQL REJECTS QUERY DUE TO BAD RESOLUTION OF NAMES IN HAVING; VIEW UNREADABLE See comment of sql_view.cc. Test case (exactly the same as in the description, just put together for more convenient copy-paste): CREATE TABLE `articles` ( `id` int (11) NOT NULL , `title` varchar (45) DEFAULT NULL , PRIMARY KEY (`id`) ); 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%' ;   SELECT `id`, SHA1(`title`) AS `column_1` FROM `articles` HAVING UPPER (column_1) LIKE '5%' ;   SELECT `id`, SHA1(`title`) AS `column_1` FROM `articles` HAVING UPPER (`column_1`) LIKE '5%' ; The bug is that the last SELECT fails with ER_BAD_FIELD_ERROR.

          revno: 4552
          committer: Jan Lindström <jplindst@mariadb.org>
          branch nick: 10.0-bugs
          timestamp: Tue 2015-01-13 20:38:17 +0200
          message:
          MDEV-7301: Unknown column quoted with backticks in HAVING clause
          when using function.

          Merged upstream fix to Bug#16221433 MYSQL REJECTS QUERY DUE TO BAD
          RESOLUTION OF NAMES IN HAVING; VIEW UNREADABLE
          authored by Guilhem Bichot <guilhem.bichot@oracle.com>.

          See attached patch, for some reason my bzr commits trigger or my emails to commits list do not work.

          jplindst Jan Lindström (Inactive) added a comment - revno: 4552 committer: Jan Lindström <jplindst@mariadb.org> branch nick: 10.0-bugs timestamp: Tue 2015-01-13 20:38:17 +0200 message: MDEV-7301 : Unknown column quoted with backticks in HAVING clause when using function. Merged upstream fix to Bug#16221433 MYSQL REJECTS QUERY DUE TO BAD RESOLUTION OF NAMES IN HAVING; VIEW UNREADABLE authored by Guilhem Bichot <guilhem.bichot@oracle.com>. See attached patch, for some reason my bzr commits trigger or my emails to commits list do not work.

          sanja has better knowledge of VIEWs and Item_refs. Sanja, please review.

          psergei Sergei Petrunia added a comment - sanja has better knowledge of VIEWs and Item_refs. Sanja, please review.

          1) why it is not 5.5?
          2) The fix itself is change in sql_yacc.yy (1 line) what is everything else about?

          sanja Oleksandr Byelkin added a comment - 1) why it is not 5.5? 2) The fix itself is change in sql_yacc.yy (1 line) what is everything else about?

          1) Patch moved to 5.5
          2) Removed everything else as not needed

          http://lists.askmonty.org/pipermail/commits/2015-March/007659.html

          jplindst Jan Lindström (Inactive) added a comment - - edited 1) Patch moved to 5.5 2) Removed everything else as not needed http://lists.askmonty.org/pipermail/commits/2015-March/007659.html

          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.