Bug #20311 quote() not working right
Submitted: 7 Jun 2006 7:58 Modified: 7 Jun 2006 11:54
Reporter: Anca Dogaru Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server Severity:S3 (Non-critical)
Version:MySQL - 4.0.24 or 5.0.21 OS:Linux (rhel)
Assigned to: CPU Architecture:Any

[7 Jun 2006 7:58] Anca Dogaru
using quote() inside of query returns no rows even there are matcing ones

How to repeat:
given the following table

-- Table structure for table `test1`

CREATE TABLE `test1` (
  `id` int(11) NOT NULL default '0',
  `name` varchar(32) NOT NULL default '',
  `email` varchar(32) NOT NULL default '',
  PRIMARY KEY  (`id`)
) TYPE=InnoDB;

-- Dumping data for table `test1`

INSERT INTO `test1` VALUES (0, '1', '1');
INSERT INTO `test1` VALUES (1, 'test', 'test@test.tst');
INSERT INTO `test1` VALUES (3, '3', '3');
INSERT INTO `test1` VALUES (4, '0003*03', '4');

when you issue a 

SELECT * FROM `test1` WHERE name=quote('1')

returns an empty result set 


SELECT * FROM `test1` WHERE name='1';

returns one row


select quote('1');
returns '1'...

another problem is that 
when is try
SELECT * FROM `test1`WHERE name =3;
the result contains 2 rows
SELECT * FROM `test1`WHERE name =quote(3);
returns no row

am i missing something?

for my problem i found a work around using cast() instead of quote 
SELECT * FROM `test1`WHERE name =cast(3 as char); 
but i still think this is a bug...

Suggested fix:
[7 Jun 2006 11:54] Tonci Grgin
Hi Anca. Thanks for your problem report.
According to manual http://dev.mysql.com/doc/refman/5.0/en/string-functions.html (under CONCAT) and http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html CAST gives the functionality you need. Quote function has different purpose so this is not a bug.