| Bug #8521 | Using like with concat produces unusual results | ||
|---|---|---|---|
| Submitted: | 15 Feb 2005 17:13 | Modified: | 21 Feb 2005 11:56 |
| Reporter: | Clint | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | mysql-standard-4.1.8-pc-linux-i686 | OS: | Linux (RedHat 3.0) |
| Assigned to: | CPU Architecture: | Any | |
[21 Feb 2005 11:56]
MySQL Verification Team
Hi,
Thank you for the report, but I wasn't able reproduce it with version 4.1.10:
mysql> select * from abc where days like concat('%',dayname('2005-02-15
'> 16:33:48'),'%');
+----+------------------------------------------+
| id | days |
+----+------------------------------------------+
| 1 | Monday,Tuesday,Wednesday,Thursday,Friday |
| 2 | Monday,Tuesday,Wednesday,Thursday,Friday |
| 4 | Monday,Tuesday,Wednesday,Thursday,Friday |
+----+------------------------------------------+
3 rows in set (0.01 sec)
Description: When I use like and concat together it appears to produce undesired results. How to repeat: I have the following table structure: Create Table: CREATE TABLE `abc` ( `id` bigint(20) unsigned NOT NULL auto_increment, `days` varchar(255) NOT NULL default '', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 With the following records: +----+------------------------------------------+ | id | days | +----+------------------------------------------+ | 1 | Monday,Tuesday,Wednesday,Thursday,Friday | | 2 | Monday,Tuesday,Wednesday,Thursday,Friday | | 3 | Saturday,Sunday | | 4 | Monday,Tuesday,Wednesday,Thursday,Friday | +----+------------------------------------------+ The following query produces undesired results, sometimes it will produce 1 record, and sometimes it will produce 0 records: select * from abc where days like concat('%',dayname('2005-02-15 16:33:48'),'%'); Empty set (0.00 sec) However if I use a cast it produces the correct results: select * from abc where days like cast(concat('%',dayname('2005-02-15 16:33:48'),'%') as char); +----+------------------------------------------+ | id | days | +----+------------------------------------------+ | 1 | Monday,Tuesday,Wednesday,Thursday,Friday | | 2 | Monday,Tuesday,Wednesday,Thursday,Friday | | 4 | Monday,Tuesday,Wednesday,Thursday,Friday | +----+------------------------------------------+ 3 rows in set (0.00 sec)