MySQL Bugs: #21455: Hex represenations of strings in WHERE clause does not work for 64bit MySQL

Bug #21455	Hex represenations of strings in WHERE clause does not work for 64bit MySQL
Submitted:	4 Aug 2006 20:04	Modified:	7 Aug 2006 16:03
Reporter:	Aaron Warner	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.0.15 standard	OS:	Linux (Linux 2.4.18-e.58smp)
Assigned to:		CPU Architecture:	Any

Description:
Using hex representaion for strings in the WHERE clause of SQL statements fails in 64bit MySQL.  I have seen this on our own installation as well as customer's. 

How to repeat:
create table test_tab(col1 varchar(10), col2 decimal);

insert into test_tab values('UCLA', 1996);

insert into test_tab values('USC', 2003);

mysql> select * from test_tab;
+------+------+
| col1 | col2 |
+------+------+
| UCLA | 1996 |
| USC  | 2003 |
+------+------+
2 rows in set (0.00 sec)

Hex version which fails:

update test_tab set col2=2010 where col1=555343;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

String version which works:

mysql> update test_tab set col2=2010 where col1='USC';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test_tab;
+------+------+
| col1 | col2 |
+------+------+
| UCLA | 1996 |
| USC  | 2010 |
+------+------+
2 rows in set (0.00 sec)

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

miguel@hegel:~/dbs/4.1> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.22-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table test_tab(col1 varchar(10), col2 decimal);
Query OK, 0 rows affected (0.04 sec)

mysql> insert into test_tab values('UCLA', 1996);
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_tab values('USC', 2003);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test_tab;
+------+------+
| col1 | col2 |
+------+------+
| UCLA | 1996 |
| USC  | 2003 |
+------+------+
2 rows in set (0.02 sec)

mysql> update test_tab set col2=2010 where col1=555343;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> select x'555343';
+-----------+
| x'555343' |
+-----------+
| USC       |
+-----------+
1 row in set (0.00 sec)

mysql> update test_tab set col2=2010 where col1=x'555343';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test_tab;
+------+------+
| col1 | col2 |
+------+------+
| UCLA | 1996 |
| USC  | 2010 |
+------+------+
2 rows in set (0.00 sec)

mysql>