MySQL Bugs: #72297: Column name ìd` causes problems in SELECT when quoted

Bug #72297	Column name ìd` causes problems in SELECT when quoted
Submitted:	9 Apr 2014 20:25	Modified:	9 Apr 2014 20:48
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.1.73	OS:	Windows (7/64)
Assigned to:		CPU Architecture:	Any

Description:
SELECT VERSION();
/*
version()         
------------------
5.1.73-community  
*/

DROP DATABASE IF EXISTS bug72296;
CREATE DATABASE bug72296;
USE bug72296;

CREATE TABLE IF NOT EXISTS `passwords` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `typepassword` ENUM('Bills','Shopping','Personal','Email') NOT NULL DEFAULT 'Personal',
  `sitename` VARCHAR(100) NOT NULL,
  `url` ENUM('Y','N') NOT NULL DEFAULT 'Y',
  `owner` ENUM('Dom','Steve','Household') NOT NULL DEFAULT 'Steve',
  `username` VARCHAR(100) DEFAULT NULL,
  `email` VARCHAR(50) DEFAULT NULL,
  `password` VARCHAR(100) DEFAULT NULL,
  `comments` TEXT,
  `incomingserver` VARCHAR(100) DEFAULT NULL,
  `outgoingserver` VARCHAR(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `sitename_2` (`sitename`,`owner`),
  UNIQUE KEY `passwordid_2` (`id`),
  KEY `passwordid` (`id`)
) ENGINE=MYISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=169 ;

SHOW TABLES FROM bug72296;
/*
Tables_in_bug72296  
--------------------
passwords           
*/

SHOW COLUMNS FROM passwords;
/*
Field           Type                                         Null    Key     Default   Extra           
--------------  -------------------------------------------  ------  ------  --------  ----------------
id              int(11)                                      NO      PRI     (NULL)    auto_increment  
typepassword    enum('Bills','Shopping','Personal','Email')  NO              Personal                  
sitename        varchar(100)                                 NO      MUL     (NULL)                    
url             enum('Y','N')                                NO              Y                         
owner           enum('Dom','Steve','Household')              NO              Steve                     
username        varchar(100)                                 YES             (NULL)                    
email           varchar(50)                                  YES             (NULL)                    
password        varchar(100)                                 YES             (NULL)                    
comments        text                                         YES             (NULL)                    
incomingserver  varchar(100)                                 YES             (NULL)                    
outgoingserver  varchar(100)                                 YES             (NULL)                    
*/

SELECT `ìd` FROM `passwords`;
-- Error Code: 1054
-- Unknown column 'ìd' in 'field list'

SELECT id FROM passwords;
-- empty set (expected result)

SELECT `owner` FROM passwords
-- empty set (expected result)

How to repeat:
see above. 

Test case is derived from bug72296.

Suggested fix:
Quoting should be valid!

I should add that I am using a utf8 client (SQLyog) that specifies utf8 as the character set for the connection in mysql_options().

I believe it would be reproducible in Workbench as well?

In command line client in ANSI mode it works as expected.

mysql> select `id` from `passwords`;
Empty set (0.00 sec)

mysql> show variables like 'char%';
+--------------------------+----------------------------------------------------
-----+
| Variable_name            | Value
     |
+--------------------------+----------------------------------------------------
-----+
| character_set_client     | utf8
     |
| character_set_connection | utf8
     |
| character_set_database   | utf8
     |
| character_set_filesystem | binary
     |
| character_set_results    | utf8
     |
| character_set_server     | utf8
     |
| character_set_system     | utf8
     |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 5.1\share\chars
ets\ |
+--------------------------+----------------------------------------------------
-----+
8 rows in set (0.00 sec)

mysql>

Hmmm .. same with latest 5.0, 5.5 and 5.6 too!

Should I go to sleep? I cannot see what I am missing (if I am).

Closing as not a bug. Retyping the statememnt solved the problem.  Some non-printable character could have been in the Scintilla editor control/buffer.