[MDEV-7301] Unknown column quoted with backticks in HAVING clause when using function. Created: 2014-12-10  Updated: 2015-03-23  Resolved: 2015-03-23

Status: Closed
Project: MariaDB Server
Component/s: Parser
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

Type: Bug Priority: Major
Reporter: Jan Kopp Assignee: Jan Lindström (Inactive)
Resolution: Fixed Votes: 0
Labels: upstream-fixed
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

Attachments: File patch.diff    

 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)

 Comments   
Comment by Elena Stepanova [ 2015-01-09 ]

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

Comment by Jan Kopp [ 2015-01-09 ]

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

Comment by Elena Stepanova [ 2015-01-10 ]

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.

Comment by Jan Lindström (Inactive) [ 2015-01-13 ]

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.

Comment by Sergei Petrunia [ 2015-02-18 ]

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

Comment by Oleksandr Byelkin [ 2015-03-22 ]

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

Comment by Jan Lindström (Inactive) [ 2015-03-23 ]

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

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

Generated at Thu Feb 08 07:18:33 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.