MySQL Bugs: #75289: MySQL recognizes varchar column as numeric in SELECT query

Bug #75289	MySQL recognizes varchar column as numeric in SELECT query
Submitted:	22 Dec 2014 14:20	Modified:	22 Dec 2014 21:13
Reporter:	Stan Mezyk	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	5.6	OS:	Microsoft Windows (SELECT query)
Assigned to:		CPU Architecture:	Any
Tags:	numeric, SELECT, varchar

Description:
In table 't1' I have column 'x' varchar(5) which has values '2','4A','5','5A','5B', etc 
My command: 
Case!: SELECT x FROM t1 WHERE x = '2'; returns 0 rows
Case2: SELECT x FROM t1 WHERE x = 2 returns some rows
Case3: SELECT x FROM t1 WHERE x = '4A' returns 0 rows

Can you help me in this? 

How to repeat:
Create table t1 with column x and values like above & try to execute SELECT query

I get expected results on 5.6.22.  

I wonder if you are running some ANSI sql_mode? What happens it you use doublequotes instead (SELECT X FROM vc WHERE X = "4A";)

-- Peter
-- not an Oracle/MySQL person

Thank you for the bug report.

[15:26:01][miguel-mac:]~ miguel$ mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.6.22 MySQL Community Server (GPL)

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> CREATE TABLE t1 (x VARCHAR(5));
Query OK, 0 rows affected (0.49 sec)

mysql> INSERT INTO t1 values ('2'),('4A'),('5'),('5A'),('5B');
Query OK, 5 rows affected (0.03 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT x FROM t1 WHERE x = '2';
+------+
| x    |
+------+
| 2    |
+------+
1 row in set (0.10 sec)

mysql> SELECT x FROM t1 WHERE x = 2;
+------+
| x    |
+------+
| 2    |
+------+
1 row in set, 3 warnings (0.04 sec)

mysql> SELECT x FROM t1 WHERE x = '4A';
+------+
| x    |
+------+
| 4A   |
+------+
1 row in set (0.00 sec)

mysql>

Hi Peter,
I tried SELECT x from t1 WHERE x = "4A"; - no rows in result
CHARACTER SET : utf8
COLLATE utf8_general_ci
Thx
Stan MEZYK

I created a new table t1 for test with column X and values '2', '4A','3' , '3A" ,..
SELECT X works OK ......
but on my real table does not work OK & I do not know Why?
info: MySQL 5.6.14 Win7 x64
I load my data from Microsoft Access 97 table using Withdata software AccessToMySQL(x86) v2.4
Thx
Stan Mezyk

I check similar column types in other MySQL tables and I have the same problem. I think it is something wrong in recognizing column type in some cases( string vs numeric)
Regards
Stan Mezyk

Can we see output of: 
SHOW CREATE TABLE t1;
SHOW TABLE STATUS like 't1';
EXPLAIN EXTENDED SELECT ...
SHOW WARNINGS;

for one of the affected tables?

Maybe using an index vs not using an index could lead to different results?