MySQL Bugs: #17421: MySQL Cluster doesn't like LIKE

Bug #17421	MySQL Cluster doesn't like LIKE
Submitted:	15 Feb 2006 11:58	Modified:	30 Nov 2006 15:33
Reporter:	Alexander List	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S2 (Serious)
Version:	5.0.18-max	OS:	Linux (Debian GNU/Linux .31)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Hello!

We just discovered an "undocumented feature" of MySQL cluster.

It seems that filtering with LIKE in combination with OR isn't working reliably...

Note that an identical query works perfectly on MyISAM, but doesnt' yield a result on NDB.

I checked the "known limitations" and couldn't find a matching one - so I think this is either an undocumented feature or, beware, a bug :-)

Alex

How to repeat:
Firstly, a comparison that matches with = should also satisfy LIKE:

mysql> select * from RADONLINE where USERNAME LIKE '%user%' OR FRAMEDIPADDRESS LIKE '2.3.4.5';
Empty set (0.06 sec)

mysql> select * from RADONLINE where USERNAME LIKE '%user%' OR FRAMEDIPADDRESS='2.3.4.5';
+---------------+---------------------------------------------+-----------------+----------------+---------+--------------+-------+------------+--------------------+-----------+
| ACCTSESSIONID | DNIS                                        | FRAMEDIPADDRESS | NASIDENTIFIER  | NASPORT | ORIGUSERNAME | STATE | TIME_STAMP | USERNAME           | del_count |
+---------------+---------------------------------------------+-----------------+----------------+---------+--------------+-------+------------+--------------------+-----------+
| 00ABCDEF      | <calling-party-number> | 2.3.4.5  | 1.2.3.4 |  123456
| NULL         |  NULL | 1140000590 | user@domain |         0 |
+---------------+---------------------------------------------+-----------------+----------------+---------+--------------+-------+------------+--------------------+-----------+
1 row in set (0.08 sec)

Secondly, identical queries should yield identical results for identical columns and not yield unexpected behaviour:

mysql> select * from RADONLINE_test where USERNAME = '' OR FRAMEDIPADDRESS LIKE '2.3.4.5';
+---------------+---------------------------------------------+-----------------+----------------+---------+--------------+-------+------------+--------------------+-----------+
| ACCTSESSIONID | DNIS                                        | FRAMEDIPADDRESS | NASIDENTIFIER  | NASPORT | ORIGUSERNAME | STATE | TIME_STAMP | USERNAME           | del_count |
+---------------+---------------------------------------------+-----------------+----------------+---------+--------------+-------+------------+--------------------+-----------+
| 00ABCDEF      | <calling-party-number>                      | 2.3.4.5         | 1.2.3.4        |  12345  | NULL         |  NULL | 1139996090 | user@domain        |         0 |
+---------------+---------------------------------------------+-----------------+----------------+---------+--------------+-------+------------+--------------------+-----------+
1 row in set (0.00 sec)

mysql> select * from RADONLINE where USERNAME = '' OR FRAMEDIPADDRESS LIKE '2.3.4.5'; 
Empty set (0.08 sec)

mysql> select * from RADONLINE where FRAMEDIPADDRESS LIKE '2.3.4.5';
+---------------+---------------------------------------------+-----------------+----------------+---------+--------------+-------+------------+--------------------+-----------+
| ACCTSESSIONID | DNIS                                        | FRAMEDIPADDRESS | NASIDENTIFIER  | NASPORT | ORIGUSERNAME | STATE | TIME_STAMP | USERNAME           | del_count |
+---------------+---------------------------------------------+-----------------+----------------+---------+--------------+-------+------------+--------------------+-----------+
| 00ABCDEF      | <calling-party-number>                      | 2.3.4.5         | 1.2.3.4        |  123456 | NULL         |  NULL | 1140004191 | user@domain        |         0 |
+---------------+---------------------------------------------+-----------------+----------------+---------+--------------+-------+------------+--------------------+-----------+
1 row in set (1.85 sec)

USERNAME and FRAMEDIPADDRESS are both of type VARCHAR(255).

The table definitions are as follows:

CREATE TABLE `RADONLINE` (
  `ACCTSESSIONID` varchar(30) default NULL,
  `DNIS` varchar(255) default NULL,
  `FRAMEDIPADDRESS` varchar(22) default NULL,
  `NASIDENTIFIER` varchar(50) NOT NULL default '',
  `NASPORT` int(11) NOT NULL default '0',
  `ORIGUSERNAME` varchar(50) default NULL,
  `STATE` int(11) default NULL,
  `TIME_STAMP` int(11) default NULL,
  `USERNAME` varchar(50) NOT NULL default '',
  `del_count` tinyint(3) unsigned NOT NULL default '0',
  UNIQUE KEY `RADONLINE_pk` (`NASIDENTIFIER`,`NASPORT`),
  KEY `RADONLINE_ui1` (`USERNAME`),
  KEY `nassess` (`NASIDENTIFIER`,`ACCTSESSIONID`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PACK_KEYS=1

CREATE TABLE `RADONLINE_test` (
  `ACCTSESSIONID` varchar(30) default NULL,
  `DNIS` varchar(255) default NULL,
  `FRAMEDIPADDRESS` varchar(22) default NULL,
  `NASIDENTIFIER` varchar(50) NOT NULL default '',
  `NASPORT` int(11) NOT NULL default '0',
  `ORIGUSERNAME` varchar(50) default NULL,
  `STATE` int(11) default NULL,
  `TIME_STAMP` int(11) default NULL,
  `USERNAME` varchar(50) NOT NULL default '',
  `del_count` tinyint(3) unsigned NOT NULL default '0',
  UNIQUE KEY `RADONLINE_pk` (`NASIDENTIFIER`,`NASPORT`),
  KEY `RADONLINE_ui1` (`USERNAME`),
  KEY `nassess` (`NASIDENTIFIER`,`ACCTSESSIONID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=1

Suggested fix:
Expose expected behaviour.

Hi,

i was not able to reproduce this so far, 
could you provide us with the following for further testing?

- your cluster configuration file
- EXPLAIN output for all queries
- a full dump of your RADONLINE table

I'm sorry but I cannot give you a dump of the whole RADONLINE table as
it contains confidential information.

However, I can give you a dump of a test table that has identical
structure and contains fake data if you want.

Reproducing the problem is simpler tho:

mysql> create table test2 (c1 VARCHAR(255), c2 VARCHAR(255))
ENGINE=ndbcluster;
Query OK, 0 rows affected (0.30 sec)

mysql> insert into test2 values('foo','bar');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test2;
+------+------+
| c1   | c2   |
+------+------+
| foo  | bar  |
+------+------+
1 row in set (0.04 sec)

mysql> EXPLAIN select * from test2;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len |
ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | test2 | ALL  | NULL          | NULL | NULL    |
NULL |  100 |       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.01 sec)

mysql> select * from test2 where c1='foo' or c2='';
+------+------+
| c1   | c2   |
+------+------+
| foo  | bar  |
+------+------+
1 row in set (0.04 sec)

mysql> EXPLAIN select * from test2 where c1='foo' or c2='';
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len |
ref  | rows | Extra                             |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------+
|  1 | SIMPLE      | test2 | ALL  | NULL          | NULL | NULL    |
NULL |  100 | Using where with pushed condition |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------+
1 row in set (0.00 sec)

mysql> select * from test2 where c1 like 'foo' or c2='';
Empty set (0.01 sec)

mysql> EXPLAIN select * from test2 where c1 like 'foo' or c2='';
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len |
ref  | rows | Extra                             |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------+
|  1 | SIMPLE      | test2 | ALL  | NULL          | NULL | NULL    |
NULL |  100 | Using where with pushed condition |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------+
1 row in set (0.01 sec)

mysql> select * from test2 where c1 like 'foo';
+------+------+
| c1   | c2   |
+------+------+
| foo  | bar  |
+------+------+
1 row in set (0.01 sec)

mysql> EXPLAIN select * from test2 where c1 like 'foo';
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len |
ref  | rows | Extra                             |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------+
|  1 | SIMPLE      | test2 | ALL  | NULL          | NULL | NULL    |
NULL |  100 | Using where with pushed condition |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------+
1 row in set (0.04 sec)
-----snap-----

Here's the dump of test2

-----snip-----
-- MySQL dump 10.10
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version       5.0.18-max-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `test2`
--

DROP TABLE IF EXISTS `test2`;
CREATE TABLE `test2` (
  `c1` varchar(255) default NULL,
  `c2` varchar(255) default NULL
) ENGINE=ndbcluster DEFAULT CHARSET=latin1;

--
-- Dumping data for table `test2`
--

/*!40000 ALTER TABLE `test2` DISABLE KEYS */;
LOCK TABLES `test2` WRITE;
INSERT INTO `test2` VALUES ('foo','bar');
UNLOCK TABLES;
/*!40000 ALTER TABLE `test2` ENABLE KEYS */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-----snap-----

I wonder if you have any news wrt this bug...

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

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 bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented bugfix in 4.1.20/5.0.22/5.1.10 changelogs. Closed.

According to

http://dev.mysql.com/doc/refman/5.0/en/news-5-0-23.html

this issue has not been fixed in 5.0.22, but in 5.0.23.

I will test this next week and report if the fix works.

Awaiting test result

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".