MySQL Bugs: #63603: Like clause in char and varchar data-type fields return no-results

Bug #63603	Like clause in char and varchar data-type fields return no-results
Submitted:	5 Dec 2011 14:14	Modified:	16 Feb 2012 17:35
Reporter:	Marco Sperandio	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S1 (Critical)
Version:	7.1.17 - 7.1.18	OS:	Linux
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	like clause no-result, mysql-cluster 7.1.17 - 7.1.18 like clause

Description:
Dear tech support,

some days ago I made an upgrade from mysql cluster 7.1.15a to mysql cluster 7.1.17, prior of this upgrade our software used the "like" clause in various modes, but after the upgrade the "like" cluse seems to stop work properly with char and varchar data types.

In fact some queries that prior to upgrade returned values as expected, after the upgrade did not return values.

Obviously we made no change in the query construction, so the software remained the same used in 7.1.15a. 

How to repeat:
I repeated the bug in various systems, the same process in 7.1.15a work without problems, not in 7.1.17.

So this is how i reproduced the bug:

create database prova;

use prova

Then i've created this table, with INT, CHAR and VARCHAR data-type fields:  

CREATE TABLE `test` (`id` int(2) NOT NULL DEFAULT '1', `country` char(2) NOT NULL DEFAULT '', `city` varchar(255) NOT NULL DEFAULT '', PRIMARY KEY (`id`)) ENGINE=ndbcluster DEFAULT CHARSET=utf8;

This is the result:
show create table test;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `id` int(2) NOT NULL DEFAULT '1',
  `country` char(2) NOT NULL DEFAULT '',
  `city` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=ndbcluster DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Now I have inserted datas with this syntax:
INSERT INTO test (id, country, city) VALUES (1, 'IT', 'Padova');
INSERT INTO test (id, country, city) VALUES (2, 'US', 'New York');
INSERT INTO test (id, country, city) VALUES (3, 'DE', 'Amburg');

A check for correct data in table:
select * from test;
+----+---------+----------+
| id | country | city     |
+----+---------+----------+
|  1 | IT      | Padova   |
|  2 | US      | New York |
|  3 | DE      | Amburg   |
+----+---------+----------+
3 rows in set (0.00 sec)

Now, this query correctly show the data i looked for, in CHAR field without using LIKE clause:
SELECT * FROM `test` WHERE `country` = 'IT';
+----+---------+--------+
| id | country | city   |
+----+---------+--------+
|  1 | IT      | Padova |
+----+---------+--------+
1 row in set (0.00 sec)

The same query with "like" clause applied on CHAR field:
SELECT * FROM `test` where`country` like 'IT';
Empty set (0.00 sec)

But the same query syntax applied on INT field:
SELECT * FROM `test` where `id` like '1';
+----+---------+--------+
| id | country | city   |
+----+---------+--------+
|  1 | IT      | Padova |
+----+---------+--------+
1 row in set (0.00 sec)

Same thing with varchar datatype fields:

query ok in VARCHAR field without using LIKE clause:
SELECT * FROM `test` WHERE `city` = 'Amburg';
+----+---------+--------+
| id | country | city   |
+----+---------+--------+
|  3 | DE      | Amburg |
+----+---------+--------+
1 row in set (0.00 sec)

The same query with "like" clause in VARCHAR field:
SELECT * FROM `test` where`city` like 'Amburg';
Empty set (0.00 sec)

It seems that the "LIKE" clause don't work at all, after i saw this strange fact i search for similar bug and found Bug #56853, but the bug-enabling condition was different than this one.

Now we have rollbacked our cluster to 7.1.15a because a lot of code stop to work.

Thank You in advance for all Your responses and suggestions.

Best Regards

Marco Sperandio

Italy

Dear tech support,

I forgot to say that I used this systems for my cluster architecture:

RHEL 5.4 for SQL nodes
RHEL 5.6 for Data nodes

I have installed this RPMs:

rhel5.x86_64

Thank You in advance

Best regards

Marco Sperandio

I meet the same bug. I found that if the charset of the table is something other than utf8, by using convert function the like clause can work. But if the charset of the table is utf8 then the like clause doesn't work. Example:

mysql> CREATE TABLE `test`.`gbktest` (
  `UserAccount` CHAR(4) DEFAULT NULL,
  `UserName` CHAR(10) DEFAULT NULL
) ENGINE=NDBCLUSTER DEFAULT CHARSET=gbk;

mysql> CREATE TABLE `test`.`utf8test` (
  `UserAccount` CHAR(4) DEFAULT NULL,
  `UserName` CHAR(10) DEFAULT NULL
) ENGINE=NDBCLUSTER DEFAULT CHARSET=utf8;

mysql> insert  into `gbktest`(`UserAccount`,`UserName`) values ('0000','aaaa'),('1111','bbbb');
mysql> insert  into `utf8test`(`UserAccount`,`UserName`) values ('1111','aaaa'),('0000','bbbb');

mysql> SELECT * FROM gbktest WHERE username LIKE "%a%";
Empty set (0.09 sec)

mysql> SELECT * FROM gbktest WHERE username LIKE convert("%a%" using utf8);
+-------------+----------+
| UserAccount | UserName |
+-------------+----------+
| 1111        | aaaa     |
+-------------+----------+
1 row in set (0.06 sec)

mysql> SELECT * FROM utf8test WHERE username LIKE "%a%";
Empty set (0.06 sec)

mysql> SELECT * FROM utf8test WHERE username LIKE convert("%a%" using utf8);
Empty set (0.08 sec)

Dear tech support,

The problem still persist in MySQL Cluster 7.1.18

Just to make this clear, is it happening on all OS's? 
I see a report for Sun Solaris and one(this) for Linux.

Hello,

I don't know if the bug is present on other operating systems, I use MySQL Cluster only on linux systems.

P.S. The bug is still present in 7.1.19, released on 30/01/2012

regards

Hi,

This is a duplicate of http://bugs.mysql.com/bug.php?id=64039.
This bug is fixed in upcoming 7.1.20 and 7.2.5.

Closing this as duplicate

/Jonas