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:
None 
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
Description:
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 

while 

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

returns one row

and 

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

another problem is that 
when is try
SELECT * FROM `test1`WHERE name =3;
the result contains 2 rows
while 
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:
none
[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.