MySQL Bugs: #64039: In mysql cluster 7.1, LIKE function not work.

Bug #64039	In mysql cluster 7.1, LIKE function not work.
Submitted:	16 Jan 2012 1:15	Modified:	17 Feb 2012 11:15
Reporter:	ws lee	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S1 (Critical)
Version:	7.1.18	OS:	Solaris (5.10)
Assigned to:	Martin Skold	CPU Architecture:	Any

Description:
In mysql cluster 5.1.56-ndb7.1.18 version, LIKE function not work.

How to repeat:
mysql> create table a(id int, name varchar(50)) engine=ndb;
Query OK, 0 rows affected (0.71 sec)

mysql> insert into a values(1,'test1');
Query OK, 1 row affected (0.00 sec)

mysql> select * from a;
+------+-------+
| id   | name  |
+------+-------+
|    1 | test1 |
+------+-------+
1 row in set (0.00 sec)

mysql> select * from a where name like 'test%';
Empty set (0.00 sec)

NOT searched any value.

This is deadly bug. shocking.
and mysql cluster 's seriou reliability problem in RDBMS existence.

Suggested fix:
please bug patch ASAP.

It seems to mysqld bug.
In ndbd(mysql5.1.51-ndb7.1.9a) + mysqld(mysql5.1.56-ndb7.1.18),
LIKE function not work, also.

Also not working for 
mysql-5.1.56 ndb-7.1.17

Please, send the output of:

show create table a\G

from your environment. I wonder if charset is utf8...

Yes it is utf8

Then it's a know bug that was already reported internally. Not yet fixed.

To. Valeriy Kravchuk 
Thanks for reply.

mysql> show create table a;
+-------+----------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                               |
+-------+----------------------------------------------------------------------------------------------------------------------------+
| a     | CREATE TABLE `a` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL
) ENGINE=ndbcluster DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------------------------------+

it's a known bug?
unbelievable.
why downloable serios bug version?
it is irresponsibility.
if LIKE funciton bug exist, can't use for database.

The following patch has been committed for a similar bug,
please verify if it solves the problem:

 4804 Martin Skold	2012-01-19
      Bug#13579318 LIKE SEARCH DOESN'T MATCH ANY ROWS ON A MULTI BYTE CHARSET COLUMN: Passing actual string length to scan filter predicate

    modified:
      mysql-test/suite/ndb/r/ndb_condition_pushdown.result
      mysql-test/suite/ndb/t/ndb_condition_pushdown.test
      sql/ha_ndbcluster_cond.cc
=== modified file 'mysql-test/suite/ndb/r/ndb_condition_pushdown.result'
--- a/mysql-test/suite/ndb/r/ndb_condition_pushdown.result	2011-11-21 13:27:34 +0000
+++ b/mysql-test/suite/ndb/r/ndb_condition_pushdown.result	2012-01-19 11:14:35 +0000
@@ -2387,5 +2387,16 @@ select b from mytab where a like -1 havi
 b
 1
 drop table mytab;
+create table t(a bigint unsigned not null primary key auto_increment, b varchar(100)) character set utf8 engine ndb;
+insert into t (b) values('abc'),('aaa'),('bbb'),('ccc');
+select * from t where b like 'a%';
+a	b
+1	abc
+2	aaa
+select * from t where b not like 'a%';
+a	b
+3	bbb
+4	ccc
+drop table t;
 set engine_condition_pushdown = @old_ecpd;
 DROP TABLE t1,t2,t3,t4,t5;

=== modified file 'mysql-test/suite/ndb/t/ndb_condition_pushdown.test'
--- a/mysql-test/suite/ndb/t/ndb_condition_pushdown.test	2011-11-21 13:27:34 +0000
+++ b/mysql-test/suite/ndb/t/ndb_condition_pushdown.test	2012-01-19 11:14:35 +0000
@@ -2413,6 +2413,15 @@ select b from mytab where a like -1 havi
 
 drop table mytab;
 
+# Bug #13579318 LIKE SEARCH DOESN'T MATCH ANY ROWS ON A MULTI BYTE CHARSET COLUMN
+create table t(a bigint unsigned not null primary key auto_increment, b varchar(100)) character set utf8 engine ndb;
+
+insert into t (b) values('abc'),('aaa'),('bbb'),('ccc');
+--sorted_result
+select * from t where b like 'a%';
+--sorted_result
+select * from t where b not like 'a%';
+drop table t;
+
 set engine_condition_pushdown = @old_ecpd;
 DROP TABLE t1,t2,t3,t4,t5;
-

=== modified file 'sql/ha_ndbcluster_cond.cc'
--- a/sql/ha_ndbcluster_cond.cc	2011-11-21 13:27:34 +0000
+++ b/sql/ha_ndbcluster_cond.cc	2012-01-19 11:14:35 +0000
@@ -1211,13 +1211,14 @@ ha_ndbcluster_cond::build_scan_filter_pr
       if (!value || !field) break;
       bool is_string= (value->qualification.value_type == Item::STRING_ITEM);
       // Save value in right format for the field type
-      uint32 len= value->save_in_field(field);
+      uint32 val_len= value->save_in_field(field);
       char buff[MAX_FIELD_WIDTH];
       String str(buff,sizeof(buff),field->get_field_charset());
-      if (len > field->get_field()->field_length)
-        str.set(value->get_val(), len, field->get_field_charset());
+      if (val_len > field->get_field()->field_length)
+        str.set(value->get_val(), val_len, field->get_field_charset());
       else
         field->get_field_val_str(&str);
+      uint32 len= str.length();
       const char *val=
         (value->is_const_func() && is_string)?
         str.ptr()
@@ -1239,13 +1240,14 @@ ha_ndbcluster_cond::build_scan_filter_pr
       if (!value || !field) break;
       bool is_string= (value->qualification.value_type == Item::STRING_ITEM);
       // Save value in right format for the field type
-      uint32 len= value->save_in_field(field);
+      uint32 val_len= value->save_in_field(field);
       char buff[MAX_FIELD_WIDTH];
       String str(buff,sizeof(buff),field->get_field_charset());
-      if (len > field->get_field()->field_length)
-        str.set(value->get_val(), len, field->get_field_charset());
+      if (val_len > field->get_field()->field_length)
+        str.set(value->get_val(), val_len, field->get_field_charset());
       else
         field->get_field_val_str(&str);
+      uint32 len= str.length();
       const char *val=
         (value->is_const_func() && is_string)?
         str.ptr()

Good Morning,

The bug is still present in version 7.1.19, another evidence in this bug report, dated 05 Dec 2011:

http://bugs.mysql.com/bug.php?id=63603

Regards

Marco Sperandio

Is this OS agnostic bug? 
Cheers
Claudio

BUG #64351: http://bugs.mysql.com/bug.php?id=64351
Still present in NDB Cluster 7.2.4. How this was supposed to be released?

this bug has been fixed in upcoming 7.1.20 and 7.2.5

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

This is fixed in NDB 7.1+ by the fix for BUG#13579318. Documented in the NDB 7.1.20 and 7.25 changelogs as follows:

        A SELECT from an NDB table using LIKE with a multibyte column
        (such as utf8) did not return the correct result when
        engine_condition_pushdown was enabled.

Closed.

We encountered similar condition on a 7.2.12 installation.

SELECT * FROM `devices` WHERE `ip` LIKE '%a.b.c.d%'; does not work
SELECT * FROM `devices` WHERE `ip` LIKE '%a.b.c.d'; does not work
SELECT * FROM `devices` WHERE `ip` LIKE 'a.b.c.d%'; works properly

Changing `ip` column to latin1 makes all three queries working. Changing it back to utf8 makes LIKE failing again. Disabling condition pushdown makes queries working.