| Bug #56853 | Wrong result with LIKE '_', PRIMARY KEY on VARCHAR field and NDB storage engine. | ||
|---|---|---|---|
| Submitted: | 18 Sep 2010 21:06 | Modified: | 26 Oct 2010 9:31 |
| Reporter: | Sveta Smirnova | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Cluster: Cluster (NDB) storage engine | Severity: | S3 (Non-critical) |
| Version: | mysql-5.1-telco-7.0 | OS: | Any |
| Assigned to: | Pekka Nousiainen | CPU Architecture: | Any |
| Tags: | 7.0.16 | ||
[18 Sep 2010 21:08]
Sveta Smirnova
Workaround: use UNIQUE key + integer primary key or use CHAR instead of VARCHAR
[20 Oct 2010 14:34]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/121344 3313 Pekka Nousiainen 2010-10-20 bug#56853 a01_patch.diff missing shrink varchar in index bound dist key check
[20 Oct 2010 17:42]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/121384 3314 Pekka Nousiainen 2010-10-20 bug#56853 a01_patch.diff missing shrink varchar in index bound dist key check
[20 Oct 2010 17:49]
Bugs System
Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.20 (revid:pekka@mysql.com-20101020174122-c2qlkamv6x5yqz8e) (version source revid:pekka@mysql.com-20101020174122-c2qlkamv6x5yqz8e) (merge vers: 5.1.51-ndb-7.0.20) (pib:21)
[20 Oct 2010 17:51]
Bugs System
Pushed into mysql-5.1-telco-6.3 5.1.51-ndb-6.3.39 (revid:pekka@mysql.com-20101020173953-e3dnk3042f4i5vaw) (version source revid:pekka@mysql.com-20101020173953-e3dnk3042f4i5vaw) (merge vers: 5.1.51-ndb-6.3.39) (pib:21)
[20 Oct 2010 18:12]
Pekka Nousiainen
The 2 commits are duplicates (to get a clean push..). This is what happened in the bug: mysqld uses 2 length bytes for varchar index bounds. NDB uses 1 byte here (same as for the value). So we convert in various places. The problem was one more missing conversion. For LIKE 'aa_' mysqld sent low and high bounds with 2 little-endian length bytes at beginning (assume latin1): \003 \000 a a \000 \003 \000 a a \377 When NDB compared these as NDB varchars, the 4th bytes \000 and \377 were not compared. So bounds were seen as equal, and since they were on PK, caused the scan to be pruned to a single fragment. Therefore "randomly" missing values (the worse the more fragments). Same bug applies to LIKE % where % matches last char: x varchar(4) primary key ...where x like 'aaa%'
[26 Oct 2010 6:19]
Jonas Oreland
pushed to 6.3.39, 7.0.20, and 7.1.9
[26 Oct 2010 9:31]
Jon Stephens
Documented fix as follows in the NDB-6.3.39, 7.0.20, and 7.1.9 changelogs:
Queries using WHERE varchar_pk_column LIKE 'pattern%' or WHERE
varchar_pk_column LIKE 'pattern_' against an NDB table having a
VARCHAR column as its primary key failed to return all matching
rows.
Closed.
Description: If one of following character sets used query select * from test1 where a like 'aa_'; returns no results, although table has corresponding row: big5 sjis tis620 gbk latin7 binary cp932 How to repeat: Create SQL file: drop table if exists test1, test2; CREATE TABLE test1 ( a varchar(16) NOT NULL, PRIMARY KEY (a) ) ENGINE=ndbcluster; insert into test1 (a) values ('aaa'); select * from test1; select a as 'aaa' from test1 where a='aaa'; select a as 'aa%' from test1 where a like 'aa%'; select a as 'aa_' from test1 where a like 'aa_'; select length(a) from test1; CREATE TABLE test2 ( a char(16) NOT NULL, PRIMARY KEY (a) ) ENGINE=ndbcluster; insert into test2 (a) values ('aaa'); select * from test2; select a as 'aaa' from test2 where a='aaa'; select a as 'aa%' from test2 where a like 'aa%'; select a as 'aa_' from test2 where a like 'aa_'; select length(a) from test2; Start cluster. Run loop through character sets: for i in `$BASEDIR/bin/mysql --host=127.0.0.1 --port=35117 -uroot test -e "select CHARACTER_SET_NAME from information_schema.character_sets;"`; do $BASEDIR/bin/mysql --host=127.0.0.1 --port=35117 -uroot test -e "set names $i; select '$i';drop database test; create database test CHARACTER SET=$i;"; $BASEDIR/bin/mysql --host=127.0.0.1 --port=35117 -uroot --default-character-set=$i test <bug.sql; done >bug.log Examine results in bug.log for table test1 and character sets indicated.