MySQL Bugs: #35185: SELECT LIKE gives wrong results when ndbcluster engine is used

Bug #35185	SELECT LIKE gives wrong results when ndbcluster engine is used
Submitted:	10 Mar 2008 15:57	Modified:	31 May 2008 10:37
Reporter:	Bogdan Kecman	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S2 (Serious)
Version:	mysql-5.1.23-rc	OS:	Any
Assigned to:	Martin Skold	CPU Architecture:	Any
Tags:	like

Description:
SELECT LIKE gives wrong results when ndbcluster engine is used

How to repeat:
CREATE TABLE `NodeAlias` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`nodeId` int(10) unsigned NOT NULL,
`displayName` varchar(45) DEFAULT NULL,
`aliasKey` varchar(45) DEFAULT NULL,
`objectVersion` int(10) unsigned NOT NULL DEFAULT '0',
`changed` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `NodeAlias_identifies_1_Node` (`nodeId`),
KEY `NodeAlias_KeyIndex` (`aliasKey`)
) engine=myisam;

insert into `NodeAlias` VALUES(null, 2 , '49', '49',  0,'2008-03-07 14:54:59');
insert into `NodeAlias` VALUES(null, 3 , '49' , '49' , 0 , '2008-03-07 14:55:24');
insert into `NodeAlias` VALUES(null, 4 , '49' , '49' , 0 , '2008-03-07 14:55:51');
insert into `NodeAlias` VALUES(null, 5 , '150' , '150' , 0 , '2008-03-10 10:48:30');
insert into `NodeAlias` VALUES(null, 6 , '154' , '154' , 0 , '2008-03-10 10:48:43');
insert into `NodeAlias` VALUES(null, 7 , '158' , '158' , 0 , '2008-03-10 10:48:57');
insert into `NodeAlias` VALUES(null, 8 , '491803%' , '491803%' , 0 , '2008-03-10 12:22:26');

select * from NodeAlias where ('4918031215220' LIKE aliasKey OR aliasKey LIKE '4918031215220');

+----+--------+-------------+----------+---------------+---------------------+
| id | nodeId | displayName | aliasKey | objectVersion | changed             |
+----+--------+-------------+----------+---------------+---------------------+
|  7 |      8 | 491803%     | 491803%  |             0 | 2008-03-10 12:22:26 | 
+----+--------+-------------+----------+---------------+---------------------+
1 row in set (0.02 sec)

alter table NodeAlias engine=ndbcluster;
select * from NodeAlias where ('4918031215220' LIKE aliasKey OR aliasKey LIKE '4918031215220');
Empty set (0.00 sec)

Suggested fix:
n/a

tested on 5.0 branch and works ok, the problem is only in 5.1 branch

mysql> SET engine_condition_pushdown=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from NodeAlias where ('4918031215220' LIKE aliasKey OR aliasKey LIKE '4918031215220');
+----+--------+-------------+----------+---------------+---------------------+
| id | nodeId | displayName | aliasKey | objectVersion | changed             |
+----+--------+-------------+----------+---------------+---------------------+
|  7 |      8 | 491803%     | 491803%  |             0 | 2008-03-10 12:22:26 | 
+----+--------+-------------+----------+---------------+---------------------+
1 row in set (0.00 sec)

so, the workaround is to switch the engine_condition_pushdown off.

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/44170

ChangeSet@1.2572, 2008-03-18 13:25:03+01:00, mskold@mysql.com +4 -0
  bug#35185  SELECT LIKE gives wrong results when ndbcluster engine is used: Disabled condition pushdown of <string> LIKE <field> until this is supported

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/44230

ChangeSet@1.2573, 2008-03-19 15:25:56+01:00, mskold@mysql.com +4 -0
  ha_ndbcluster_cond.h, ha_ndbcluster_cond.cc:
    bug#35185  SELECT LIKE gives wrong results when ndbcluster engine is used: Post-review fixes
  item.h, mysql_com.h:
    Adding max values for enums to support allocating bitmasks of correct size

Documented fix in the 5.1.23-ndb-6.3.11 changelog as follows:

        SELECT ... LIKE ... gave incorrect results when used on NDB tables. As
        part of this fix, condition pushdown of such queries has been disabled;
        re-enabling it is expected to be done as part of a permanent fix for
        this issue.

Left status as Patch Queued pending additional merges.

Fix also noted in the 5.1.23-ndb-6.2.15 changelog.

Closed per yesterday's discussion with Jonas.

Pushed into 6.0.6-alpha  (revid:sp1r-mskold/marty@mysql.com/quadfish.(none)-20080319142556-24424) (version source revid:sp1r-tomas@poseidon.ndb.mysql.com-20080516085603-30848) (pib:5)