MySQL Bugs: #72595: Unknown column

Bug #72595	Unknown column
Submitted:	9 May 2014 18:54	Modified:	10 May 2014 9:24
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.6.17	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Not sure if 'DML' or 'parser' is correct category.

With recent MySQL 5.6 I experience 'unknown column' errors frequently.

The only special about my configuration is that 'enforce_gtid_consistency' is ON.  But not sure if it matters (it should not).

Below the most simple test case I could find.

How to repeat:
DROP DATABASE IF EXISTS `blah`;
CREATE DATABASE `blah`;
USE `blah`;

CREATE TABLE `a` (
`int` INT(11) DEFAULT NULL
) ENGINE=MYISAM;

INSERT INTO `a` VALUES (1), (2);
UPDATE `a` SET `ìnt`= 5 WHERE `ìnt` = 1;

/*
Error Code: 1054
Unknown column 'ìnt' in 'where clause'
*/

Suggested fix:
No idea. 

Probably a reserved word overlap/conflict on column name `int` (the issue does not occur with column name `p`).  But as it is `backquoted` it should work. 

MySQL 5.6 has so many new reserved words that this kind of conflict will arise frequently with applications wirtten for older MySQL veersions if not resolved.

Thank you for the bug report.

C:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 --debug-info --prompt="mysql 5.6 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.19-debug Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.6 > DROP DATABASE IF EXISTS `blah`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql 5.6 > CREATE DATABASE `blah`;
Query OK, 1 row affected (0.01 sec)

mysql 5.6 > USE `blah`;
Database changed
mysql 5.6 >
mysql 5.6 > CREATE TABLE `a` (
    -> `int` INT(11) DEFAULT NULL
    -> ) ENGINE=MYISAM;
Query OK, 0 rows affected (0.06 sec)

mysql 5.6 >
mysql 5.6 > INSERT INTO `a` VALUES (1), (2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql 5.6 > UPDATE `a` SET `int`= 5 WHERE `int` = 1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql 5.6 >
mysql 5.6 > SELECT * FROM a;
+------+
| int  |
+------+
|    5 |
|    2 |
+------+
2 rows in set (0.00 sec)

Notice that your test case `int` INT(11) DEFAULT NULL the column name does not is accented with grave accent however the update statement does: SET `ìnt`= 5 WHERE `ìnt` = 1. Then I think Unknown column 'ìnt' in 'where clause' is correct.

@Miguel .. you are completely right.

I was testing on a small Win8 tablet and was not able to see the difference.