| Bug #61064 | SELECT queries using ESCAPE syntax fail | ||
|---|---|---|---|
| Submitted: | 4 May 2011 23:27 | Modified: | 13 Feb 2012 10:32 |
| Reporter: | Gary Teter | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Cluster: Cluster (NDB) storage engine | Severity: | S1 (Critical) |
| Version: | 7.1.10 | OS: | Any |
| Assigned to: | Martin Skold | CPU Architecture: | Any |
[24 May 2011 3:47]
Alexey Kishkin
could you please provide output of: select 'test_data@test.org' like "test|_%" escape '|';
[24 May 2011 17:43]
Gary Teter
mysql> select 'test_data@test.org' like "test|_%" escape '|'; +------------------------------------------------+ | 'test_data@test.org' like "test|_%" escape '|' | +------------------------------------------------+ | 1 | +------------------------------------------------+ 1 row in set (0.00 sec)
[3 Jun 2011 2:28]
Alexey Kishkin
Thank you. What collations/charset do you use for table? (I ask that for check if this bug is duplicate of bug#39808)
[3 Jun 2011 2:36]
Gary Teter
This behavior occurs on tables with charset latin1 (encoding cp1252 West Europe), collation latin1_swedish_ci It also occurs on charset utf8, collation utf8_general_ci
[17 Jan 2012 22:37]
Sveta Smirnova
Thank you for the feedback. Verified as described. This is Cluster issue: problem is not repeatable with other storage engines.
[24 Jan 2012 13:35]
Martin Skold
4819 Martin Skold 2012-01-24
Bug#13604447 61064: SELECT QUERIES USING ESCAPE SYNTAX FAIL: Disable pushdown for LIKE with ESCAPE until we have kernel support
modified:
mysql-test/suite/ndb/r/ndb_condition_pushdown.result
mysql-test/suite/ndb/t/ndb_condition_pushdown.test
sql/ha_ndbcluster_cond.cc
sql/item_cmpfunc.h
=== modified file 'mysql-test/suite/ndb/r/ndb_condition_pushdown.result'
--- a/mysql-test/suite/ndb/r/ndb_condition_pushdown.result 2012-01-19 11:14:35 +0000
+++ b/mysql-test/suite/ndb/r/ndb_condition_pushdown.result 2012-01-24 13:10:16 +0000
@@ -2398,5 +2398,21 @@ a b
3 bbb
4 ccc
drop table t;
+create table escapetest ( emailaddress varchar(255) default null, id int not
+null default '0') engine=ndbcluster;
+insert into escapetest values('test_data@test.org', 1);
+explain select * from escapetest where emailaddress like "test_%";
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE escapetest ALL NULL NULL NULL NULL 2 Using where with pushed condition
+select * from escapetest where emailaddress like "test_%";
+emailaddress id
+test_data@test.org 1
+explain select * from escapetest where emailaddress like "test|_%" escape '|';
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE escapetest ALL NULL NULL NULL NULL 2 Using where
+select * from escapetest where emailaddress like "test|_%" escape '|';
+emailaddress id
+test_data@test.org 1
+drop table escapetest;
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 2012-01-19 11:14:35 +0000
+++ b/mysql-test/suite/ndb/t/ndb_condition_pushdown.test 2012-01-24 13:10:16 +0000
@@ -2423,5 +2423,15 @@ select * from t where b like 'a%';
select * from t where b not like 'a%';
drop table t;
+# Bug #13604447 61064: SELECT QUERIES USING ESCAPE SYNTAX FAIL
+create table escapetest ( emailaddress varchar(255) default null, id int not
+null default '0') engine=ndbcluster;
+insert into escapetest values('test_data@test.org', 1);
+explain select * from escapetest where emailaddress like "test_%";
+select * from escapetest where emailaddress like "test_%";
+explain select * from escapetest where emailaddress like "test|_%" escape '|';
+select * from escapetest where emailaddress like "test|_%" escape '|';
+drop table escapetest;
+
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 2012-01-19 11:14:35 +0000
+++ b/sql/ha_ndbcluster_cond.cc 2012-01-24 13:10:16 +0000
@@ -498,7 +498,14 @@ void ndb_serialize_cond(const Item *item
{
Ndb_expect_stack* expect_next= new Ndb_expect_stack();
DBUG_PRINT("info", ("LIKE_FUNC"));
- curr_cond->ndb_item= new Ndb_item(func_item->functype(),
+
+ if (((Item_func_like *)func_item)->escape_was_used_in_parsing())
+ {
+ DBUG_PRINT("info", ("LIKE expressions with ESCAPE not supported"));
+ context->supported= FALSE;
+ }
+
+ curr_cond->ndb_item= new Ndb_item(func_item->functype(),
func_item);
/*
=== modified file 'sql/item_cmpfunc.h'
--- a/sql/item_cmpfunc.h 2011-06-30 15:55:35 +0000
+++ b/sql/item_cmpfunc.h 2012-01-24 13:10:16 +0000
@@ -1412,6 +1412,7 @@ public:
const char *func_name() const { return "like"; }
bool fix_fields(THD *thd, Item **ref);
void cleanup();
+ bool escape_was_used_in_parsing() { return escape_used_in_parsing; }
};
#ifdef USE_REGEX
[13 Feb 2012 10:32]
Jon Stephens
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
[13 Feb 2012 10:33]
Jon Stephens
Fixed in NDB 7.0+. Documented in the NDB 7.0.31 and 7.1.20 changelogs as follows:
Queries using LIKE ... ESCAPE on NDB tables failed when
pushed down to the data nodes. Such queries are no longer pushed
down, regardless of the value of engine_condition_pushdown.
Closed.
[13 Feb 2012 10:34]
Jon Stephens
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
[13 Feb 2012 10:35]
Jon Stephens
Fixed in NDB 7.0+. Documented in the NDB 7.0.31 and 7.1.20 changelogs as follows:
Queries using LIKE ... ESCAPE on NDB tables failed when
pushed down to the data nodes. Such queries are no longer pushed
down, regardless of the value of engine_condition_pushdown.
Closed.
[13 Feb 2012 10:36]
Jon Stephens
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
[13 Feb 2012 10:37]
Jon Stephens
Fixed in NDB 7.0+. Documented in the NDB 7.0.31 and 7.1.20 changelogs as follows:
Queries using LIKE ... ESCAPE on NDB tables failed when
pushed down to the data nodes. Such queries are no longer pushed
down, regardless of the value of engine_condition_pushdown.
Closed.
[13 Feb 2012 10:53]
Jon Stephens
Fixed in NDB 7.0+. Documented in the NDB 7.0.31 and 7.1.20 changelogs as follows:
Queries using LIKE ... ESCAPE on NDB tables failed when
pushed down to the data nodes. Such queries are no longer pushed
down, regardless of the value of engine_condition_pushdown.
Closed.
[13 Feb 2012 10:55]
Jon Stephens
Fixed in NDB 7.0+. Documented in the NDB 7.0.31 and 7.1.20 changelogs as follows:
Queries using LIKE ... ESCAPE on NDB tables failed when
pushed down to the data nodes. Such queries are no longer pushed
down, regardless of the value of engine_condition_pushdown.
Closed.
[13 Feb 2012 10:57]
Jon Stephens
Fixed in NDB 7.0+. Documented in the NDB 7.0.31 and 7.1.20 changelogs as follows:
Queries using LIKE ... ESCAPE on NDB tables failed when
pushed down to the data nodes. Such queries are no longer pushed
down, regardless of the value of engine_condition_pushdown.
Closed.
Description: SELECT queries using ESCAPE syntax fail How to repeat: create table escapetest ( emailaddress varchar(255) default null, id int not null default '0') engine=ndbcluster; insert into escapetest values('test_data@test.org', 1); Works as expected (1 result): select * from escapetest where emailaddress like "test_%"; No results: select * from escapetest where emailaddress like "test|_%" escape '|';