| Bug #63146 | LIKE with CONCAT and REPLACE produces empty resultset - connection encoding | ||
|---|---|---|---|
| Submitted: | 8 Nov 2011 12:25 | Modified: | 12 Apr 2018 5:27 |
| Reporter: | Nick Levett | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Charsets | Severity: | S3 (Non-critical) |
| Version: | 5.5.15, 5.5.17 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | concat, Latin1, like, REPLACE, utf8 | ||
[11 Nov 2011 15:37]
Valeriy Kravchuk
Thank you for the problem report. Verified with 5.5.17 on Mac OS X:
macbook-pro:5.5 openxs$ 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 2
Server version: 5.5.17-debug Source distribution
Copyright (c) 2000, 2011, 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> SHOW VARIABLES LIKE 'character_set%';
+--------------------------+---------------------------------------+
| Variable_name | Value |
+--------------------------+---------------------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /Users/openxs/dbs/5.5/share/charsets/ |
+--------------------------+---------------------------------------+
8 rows in set (0.02 sec)
mysql> create table testbug (client_number char(8));
Query OK, 0 rows affected (0.25 sec)
mysql> insert into testbug values ('01234567');
Query OK, 1 row affected (0.06 sec)
mysql> select client_number from testbug where client_number LIKE replace(concat('%',
-> 'AB01234567', '%'), 'AB', '');
+---------------+
| client_number |
+---------------+
| 01234567 |
+---------------+
1 row in set (0.05 sec)
mysql> select client_number from testbug where client_number LIKE concat('%',
-> replace('AB01234567', 'AB', ''), '%');
Empty set (0.00 sec)
mysql> select concat('%',
-> replace('AB01234567', 'AB', ''), '%');
+---------------------------------------------------+
| concat('%',
replace('AB01234567', 'AB', ''), '%') |
+---------------------------------------------------+
| %01234567% |
+---------------------------------------------------+
1 row in set (0.00 sec)
mysql> select client_number from testbug where client_number LIKE '%01234567%';
+---------------+
| client_number |
+---------------+
| 01234567 |
+---------------+
1 row in set (0.00 sec)
mysql> charset utf8;
Charset changed
mysql> select client_number from testbug where client_number LIKE concat('%', replace('AB01234567', 'AB', ''), '%');
+---------------+
| client_number |
+---------------+
| 01234567 |
+---------------+
1 row in set (0.02 sec)
mysql> show variables like 'character_set%';
+--------------------------+---------------------------------------+
| Variable_name | Value |
+--------------------------+---------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /Users/openxs/dbs/5.5/share/charsets/ |
+--------------------------+---------------------------------------+
8 rows in set (0.00 sec)
[14 Feb 2012 20:18]
Chris Bloom
I can reproduce this bug even without the call to REPLACE. This is based off of the unresolved bug report #8521
mysql> SHOW VARIABLES LIKE 'character_set%';
+--------------------------+--------------------------------------------+
| Variable_name | Value |
+--------------------------+--------------------------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /Applications/MAMP/Library/share/charsets/ |
+--------------------------+--------------------------------------------+
8 rows in set (0.00 sec)
mysql> 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;
Query OK, 0 rows affected (0.06 sec)
mysql> INSERT INTO abc (days) VALUES ('Monday,Tuesday,Wednesday,Thursday,Friday'),
-> ('Monday,Tuesday,Wednesday,Thursday,Friday'),
-> ('Saturday,Sunday'),
-> ('Monday,Tuesday,Wednesday,Thursday,Friday');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM abc WHERE days LIKE concat('%',dayname('2005-02-15 16:33:48'),'%');
Empty set (0.00 sec)
mysql>
mysql> SELECT concat('%',dayname('2005-02-15 16:33:48'),'%');
+------------------------------------------------+
| concat('%',dayname('2005-02-15 16:33:48'),'%') |
+------------------------------------------------+
| %Tuesday% |
+------------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> SELECT * FROM abc WHERE days LIKE '%Tuesday%';
+----+------------------------------------------+
| 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)
mysql> charset utf8;
Charset changed
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.00 sec)
[14 Feb 2012 20:42]
Chris Bloom
FWIW, this is a valid workaround:
mysql> SELECT * FROM abc WHERE days LIKE concat('%',dayname('2005-02-15 16:33:48'),'%');
Empty set (0.11 sec)
mysql> SELECT * FROM abc WHERE days LIKE cast(concat('%',dayname('2005-02-15 16:33:48'),'%') as CHAR CHARACTER SET utf8);
+----+------------------------------------------+
| id | days |
+----+------------------------------------------+
| 1 | Monday,Tuesday,Wednesday,Thursday,Friday |
| 3 | Monday,Tuesday,Wednesday,Thursday,Friday |
| 7 | Monday,Tuesday,Wednesday,Thursday,Friday |
+----+------------------------------------------+
3 rows in set (0.12 sec)
[12 Apr 2018 5:27]
Erlend Dahl
Duplicate of Bug#59140 LIKE concat('%',@a,'%') doesn't match when @a contains latin1 string
Description: I've come across an interesting issue where a query that should work doesn't when using a latin1 client connection. However changing the query around produces the correct result. We're using LIKE CONCAT('%', REPLACE('AB01234567', 'AB', ''), '%') in the WHERE statement which produces an empty resultset, but if you change it so the CONCAT is inside the REPLACE it then works - LIKE REPLACE(CONCAT('%', 'AB01234567', '%'), 'AB', ''). Using the MySQL command line both ways work, it's just when the connection is latin1 it starts to produce weird results. The original query should work when using latin1 connection encoding. PHP 5.3.8/MySQL 5.5.15 using mysql functions. How to repeat: latin1_swedish_ci is the default collation mysql> create table testbug (client_number char(8)); Query OK, 0 rows affected (0.03 sec) mysql> insert into testbug values ('01234567'); Query OK, 1 row affected, 1 warning (0.01 sec) mysql> SHOW VARIABLES LIKE 'character_set%'; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ 8 rows in set (0.00 sec) mysql> select client_number from testbug where client_number LIKE replace(concat('%', 'AB01234567', '%'), 'AB', ''); +---------------+ | client_number | +---------------+ | 01234567 | +---------------+ 1 row in set (0.00 sec) mysql> select client_number from testbug where client_number LIKE concat('%', replace('AB01234567', 'AB', ''), '%'); +---------------+ | client_number | +---------------+ | 01234567 | +---------------+ 1 row in set (0.00 sec) mysql> charset latin1 Charset changed mysql> SHOW VARIABLES LIKE 'character_set%'; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ 8 rows in set (0.00 sec) mysql> select client_number from testbug where client_number LIKE concat('%', replace('AB01234567', 'AB', ''), '%'); Empty set (0.00 sec) mysql> select client_number from testbug where client_number LIKE replace(concat('%', 'AB01234567', '%'), 'AB', ''); +---------------+ | client_number | +---------------+ | 01234567 | +---------------+ 1 row in set (0.00 sec)