Bug #69510 In mysql cluster 7.3 GA version, like function not work.
Submitted: 19 Jun 2013 0:57 Modified: 19 Jun 2013 9:37
Reporter: 李 源錫 Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S1 (Critical)
Version:mysql5.6.11-ndb-7.3.2 OS:Any
Assigned to: CPU Architecture:Any

[19 Jun 2013 0:57] 李 源錫
Description:
In mysql cluster 7.3.1 GA version , like function not work.
Also mysql cluster 7.3 GA version is garbage version as mysql 7.2 ???
unbelivable oracle's GA policy.
if like not work, is not GA versio. and is not db. only garbage.
this bug exist in 7.2 version and i reported. and so yours know this bug exits in 7.2
but yours not patched now after 7 months.
7 months.
7 months.
7 months.

How to repeat:
mysql> create table a(a varchar(7)) engine=ndb charset=utf8;

mysql> insert into a values ('abcdefg');

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

mysql> select * from a where a like 'abcdefg%';             
Empty set (0.00 sec)
[19 Jun 2013 1:00] 李 源錫
I missed severity selection.
please change from S3(Non-critical) to S1(Critical).
[19 Jun 2013 9:37] MySQL Verification Team
Hello,

Thank you for the report.
Verified as described.

Thanks,
Umesh
[19 Jun 2013 9:37] MySQL Verification Team
// 7.2.5, 7.2.10/12/13 - all affected
// 7.3.2 affected
// 7.1.20

Looks like below fix never made it to the relevant branches

Bug #64039
BUG 13579318 - LIKE SEARCH DOESN'T MATCH ANY ROWS ON A MULTI BYTE CHARSET COLUM

How to repeat:

# ndb table  - affected
use test;
drop table if exists a;
create table a(id bigint unsigned not null primary key auto_increment,a varchar(7)) character set utf8 engine ndb;
insert into a(a) values ('abcdefg');
select * from a;
select * from a where a like 'abcdefg%';

Workaround: set engine_condition_pushdown=off;

# myisam/innodb - not affected

use test;
drop table if exists a;
create table a(id bigint unsigned not null primary key auto_increment,a varchar(7)) character set utf8 engine myisam;
insert into a(a) values ('abcdefg');
select * from a;
select * from a where a like 'abcdefg%';
[19 Jun 2013 9:48] MySQL Verification Team
Bug #69511 marked as duplicate of this one.
[19 Jun 2013 9:50] MySQL Verification Team
// 7.3.2

mysql> select version();
+----------------------------------------------+
| version()                                    |
+----------------------------------------------+
| 5.6.11-ndb-7.3.2-cluster-commercial-advanced |
+----------------------------------------------+
1 row in set (0.01 sec)

mysql> show global variables like '%push%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| ndb_join_pushdown | ON    |
+-------------------+-------+
1 row in set (0.01 sec)

mysql> \s
--------------
bin/mysql  Ver 14.14 Distrib 5.6.11-ndb-7.3.2, for linux-glibc2.5 (x86_64) using  EditLine wrapper

Connection id:          2
Current database:       test
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.6.11-ndb-7.3.2-cluster-commercial-advanced MySQL Cluster Server - Advanced Edition (Commercial)
Protocol version:       10
Connection:             Localhost via UNIX socket
Insert id:              1
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /tmp/mysql.sock
Uptime:                 16 min 16 sec

Threads: 2  Questions: 75  Slow queries: 0  Opens: 79  Flush tables: 2  Open tables: 1  Queries per second avg: 0.076
--------------

// MyISAM

mysql> select version();
+----------------------------------------------+
| version()                                    |
+----------------------------------------------+
| 5.6.11-ndb-7.3.2-cluster-commercial-advanced |
+----------------------------------------------+

mysql> use test;
Database changed
mysql> drop table if exists a;
Query OK, 0 rows affected (0.25 sec)

mysql> create table a(a varchar(7)) engine=myisam charset=utf8;
Query OK, 0 rows affected (0.04 sec)

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

mysql> select * from a;
+---------+
| a       |
+---------+
| abcdefg |
+---------+
1 row in set (0.01 sec)

mysql> select * from a where a like 'abcdefg%'
    -> ;
+---------+
| a       |
+---------+
| abcdefg |
+---------+
1 row in set (0.00 sec)

// Innodb

mysql> use test;
Database changed
mysql> drop table if exists a;
Query OK, 0 rows affected (0.00 sec)

mysql> create table a(a varchar(7)) engine=myisam charset=utf8;
Query OK, 0 rows affected (0.00 sec)

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

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

mysql> select * from a where a like 'abcdefg%';
+---------+
| a       |
+---------+
| abcdefg |
+---------+
1 row in set (0.00 sec)

// ndb

mysql> drop table if exists a;
Query OK, 0 rows affected (0.00 sec)

mysql> create table a(a varchar(7)) engine=ndbcluster charset=utf8;
Query OK, 0 rows affected (0.91 sec)

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

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

mysql> select * from a where a like 'abcdefg%';
Empty set (0.01 sec)

mysql> select * from a where trim(a) like 'abcdefg%';
+---------+
| a       |
+---------+
| abcdefg |
+---------+
1 row in set (0.01 sec)

mysql> select * from a where upper(a) like 'abcdefg%';
+---------+
| a       |
+---------+
| abcdefg |
+---------+
1 row in set (0.00 sec)

mysql> select * from a where lower(a) like 'abcdefg%';
+---------+
| a       |
+---------+
| abcdefg |
+---------+
1 row in set (0.00 sec)

^^ Works if the column is enclosed within string functions or when set engine_condition_pushdown=off;
[19 Jun 2013 13:56] Martin Sköld
This is fixed in 7.1 and the merge to 7.2 will look as follows:

    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	2012-11-13 10:27:06 +0000
+++ b/mysql-test/suite/ndb/r/ndb_condition_pushdown.result	2013-06-19 13:17:16 +0000
@@ -2420,6 +2420,20 @@ select * from escapetest where emailaddr
 emailaddress	id
 test_data@test.org	1
 drop table escapetest;
+create table a(a varchar(7)) engine=ndb charset=utf8;
+insert into a values ('abcdefg');
+select * from a;
+a
+abcdefg
+explain extended select * from a where a like 'abcdefg%';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	a	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where with pushed condition: (`test`.`a`.`a` like 'abcdefg%')
+Warnings:
+Note	1003	select `test`.`a`.`a` AS `a` from `test`.`a` where (`test`.`a`.`a` like 'abcdefg%')
+select * from a where a like 'abcdefg%';
+a
+abcdefg
+drop table a;
 set @@session.optimizer_switch = @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	2012-05-23 12:43:48 +0000
+++ b/mysql-test/suite/ndb/t/ndb_condition_pushdown.test	2013-06-19 13:17:16 +0000
@@ -2434,6 +2434,20 @@ explain select * from escapetest where e
 select * from escapetest where emailaddress like "test|_%" escape '|';
 drop table escapetest;
 
+# Bug#15923467 LIKE FUNCTION NOT WORK.
+
+create table a(a varchar(7)) engine=ndb charset=utf8;
+
+insert into a values ('abcdefg');
+
+select * from a;                        
+
+explain extended select * from a where a like 'abcdefg%'; 
+
+select * from a where a like 'abcdefg%'; 
+
+drop table a;
+
 set @@session.optimizer_switch = @old_ecpd;
 DROP TABLE t1,t2,t3,t4,t5;
 

=== modified file 'sql/ha_ndbcluster_cond.cc'
--- a/sql/ha_ndbcluster_cond.cc	2013-05-14 15:58:53 +0000
+++ b/sql/ha_ndbcluster_cond.cc	2013-06-19 13:17:16 +0000
@@ -1363,7 +1363,10 @@ ha_ndbcluster_cond::build_scan_filter_pr
         str.set(value->get_val(), val_len, field->get_field_charset());
       else
         field->get_field_val_str(&str);
-      uint32 len= str.length();
+      uint32 len=
+        ((value->is_const_func() || value->is_cached()) && is_string)?
+        str.length():
+        value->pack_length();
       const char *val=
         ((value->is_const_func() || value->is_cached()) && is_string)?
         str.ptr()
@@ -1392,7 +1395,10 @@ ha_ndbcluster_cond::build_scan_filter_pr
         str.set(value->get_val(), val_len, field->get_field_charset());
       else
         field->get_field_val_str(&str);
-      uint32 len= str.length();
+      uint32 len=
+        ((value->is_const_func() || value->is_cached()) && is_string)?
+        str.length():
+        value->pack_length();
       const char *val=
         ((value->is_const_func() || value->is_cached()) && is_string)?
         str.ptr()