| 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: | |
| 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 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()
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)