[MDEV-7301] Unknown column quoted with backticks in HAVING clause when using function. - JIRA

Details

Type: Bug
Status: Closed
Priority: Major
Resolution: Fixed
Affects Version/s: 5.1.67, 5.2.14, 5.3.12, 10.0, 5.5
Fix Version/s: 10.0.18, 5.5.43
Component/s: Parser
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

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)

Gliffy Diagrams

Attachments

Options
- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

Attachments

patch.diff
7 kB
2015-01-13 21:53

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Elena Stepanova added a comment - 2015-01-09 23:30

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

Show

Elena Stepanova added a comment - 2015-01-09 23:30 I assume "Works well on MySQL 5.5" is a typo, you meant 5.6?

Hide

Permalink

Jan Kopp added a comment - 2015-01-09 23:36

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

Show

Jan Kopp added a comment - 2015-01-09 23:36 Both versions ... tested on 5.5, 5.6 (just for sure) as I mentioned.

Hide

Permalink

Elena Stepanova added a comment - 2015-01-10 14:41 - 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.

Show

Elena Stepanova added a comment - 2015-01-10 14:41 - 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.

Hide

Permalink

Jan Lindström added a comment - 2015-01-13 20:54

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.

Show

Jan Lindström added a comment - 2015-01-13 20:54 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.

Hide

Permalink

Sergei Petrunia added a comment - 2015-02-18 14:00

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

Show

Sergei Petrunia added a comment - 2015-02-18 14:00 Oleksandr Byelkin has better knowledge of VIEWs and Item_refs. Sanja, please review.

Hide

Permalink

Oleksandr Byelkin added a comment - 2015-03-22 15:17

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

Show

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

Hide

Permalink

Jan Lindström added a comment - 2015-03-23 09:48 - edited

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

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

Show

Jan Lindström added a comment - 2015-03-23 09:48 - 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

Assignee:

Jan Lindström

Reporter:

Jan Kopp

Votes:

0 Vote for this issue

Watchers:

6 Start watching this issue

Dates

Created:

2014-12-10 09:27

Updated:

2015-03-23 16:51

Resolved:

2015-03-23 16:51

Agile

View on Board