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

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(EOL), 10.0(EOL)
Fix Version/s: 5.5.43, 10.0.18
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)

Attachments

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

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

Activity

Ascending order - Click to sort in descending order

Jan Kopp created issue - 2014-12-10 09:27

Elena Stepanova made changes - 2014-12-11 18:55

Field	Original Value	New Value
Labels	mariadb	upstream-fixed

Elena Stepanova made changes - 2014-12-11 23:47

Assignee

Elena Stepanova [ elenst ]

Sergei Golubchik made changes - 2015-01-05 22:38

Fix Version/s

10.0 [ 16000 ]

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?

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?

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

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

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

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.

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.

Elena Stepanova made changes - 2015-01-10 14:41

Affects Version/s		5.3.12 [ 12000 ]
Affects Version/s		5.2.14 [ 12101 ]
Affects Version/s		5.1.67 [ 12100 ]
Affects Version/s		10.0 [ 16000 ]
Affects Version/s		5.5 [ 15800 ]
Affects Version/s	10.0.15 [ 17300 ]
Assignee	Elena Stepanova [ elenst ]

Jan Lindström (Inactive) made changes - 2015-01-13 16:34

Assignee

Jan Lindström [ jplindst ]

Jan Lindström (Inactive) made changes - 2015-01-13 16:52

Status

Open [ 1 ]

In Progress [ 3 ]

Jan Lindström (Inactive) made changes - 2015-01-13 20:47

Attachment

patch.diff [ 36701 ]

Jan Lindström (Inactive) 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.

Jan Lindström (Inactive) 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.

Jan Lindström (Inactive) made changes - 2015-01-13 20:54

Assignee	Jan Lindström [ jplindst ]	Sergei Golubchik [ serg ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Jan Lindström (Inactive) made changes - 2015-01-13 21:53

Attachment

patch.diff [ 36701 ]

Jan Lindström (Inactive) made changes - 2015-01-13 21:53

Attachment

patch.diff [ 36702 ]

Sergei Golubchik made changes - 2015-01-16 12:03

Assignee

Sergei Golubchik [ serg ]

Sergei Petrunia [ psergey ]

Sergei Petrunia made changes - 2015-02-18 13:59

Assignee

Sergei Petrunia [ psergey ]

Oleksandr Byelkin [ sanja ]

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

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

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

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?

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?

Oleksandr Byelkin made changes - 2015-03-22 15:18

Status

In Review [ 10002 ]

Stalled [ 10000 ]

Oleksandr Byelkin made changes - 2015-03-22 18:54

Assignee

Oleksandr Byelkin [ sanja ]

Jan Lindström [ jplindst ]

Jan Lindström (Inactive) made changes - 2015-03-23 09:47

Fix Version/s		10.0.18 [ 18702 ]
Fix Version/s		5.5.43 [ 18601 ]
Fix Version/s	10.0 [ 16000 ]

Jan Lindström (Inactive) made changes - 2015-03-23 09:47

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Jan Lindström (Inactive) 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

Jan Lindström (Inactive) 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

Jan Lindström (Inactive) made changes - 2015-03-23 16:51

Component/s		Parser [ 10201 ]
Resolution		Fixed [ 1 ]
Status	In Progress [ 3 ]	Closed [ 6 ]

Rasmus Johansson (Inactive) made changes - 2015-05-18 17:51

Workflow

MariaDB v2 [ 58922 ]

MariaDB v3 [ 67390 ]

Sergei Golubchik made changes - 2021-12-06 21:40

Workflow

MariaDB v3 [ 67390 ]

MariaDB v4 [ 148609 ]

People

Assignee:: Jan Lindström (Inactive)

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

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Attachments

Activity

People

Dates

Git Integration