Bug #12367 Strange behavior of select statement when like expr. is used on prim.key
Submitted: 4 Aug 2005 8:58 Modified: 3 Jul 2009 13:35
Reporter: Wolfgang Schmiedek Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.7 OS:Linux (Linux SLES9)
Assigned to: CPU Architecture:Any

[4 Aug 2005 8:58] Wolfgang Schmiedek
Description:
MySQL is returning 0 rows when it should return some,in a select statement when the select statement has some like conditions.
e.g.
select * from mytable where myfield like 'A%';...returns 100 rows
select * from mytable where myfield like 'A%' and myfield not like 'B%' ...return 0 rows, but should be 100;

This happens only if myfield is the primary key.

Using the Expression (myfield like 'A%' and myfield not like 'B%' ) is not very senseful, but if a select statement is built by a program based on user input it could happen.

 

How to repeat:
create table test (item char(10) primary key);
insert into test values ('100'),('200'),('300');
select count(*) from test where item not like '1%' and item like '2%';

In Mysql 3.23.x count(*) return 1, which is the correct value.

In MySql 5.0.7-beta count(*) return 0, which is wrong.

Suggested fix:
MySQL should return the right number of rows, even if a sql-statement contains not very senseful expressions.
[4 Aug 2005 9:17] Valeriy Kravchuk
Thank you for taking the time to report a problem.  Unfortunately
you are not using a current version of the product your reported a
problem with -- the problem might already be fixed. Please download
a new version from http://www.mysql.com/downloads/

If you are able to reproduce the bug with one of the latest versions,
please change the version on this bug report to the version you
tested and change the status back to "Open".  Again, thank you for
your continued support of MySQL.

Additional info:

I tried to repeat your test case in 5.0.10 and 5.0.11 (not yet released), on both Windows and Linux, on MyISAM and InnoDB tables. There is no such problem is newer versions:

mysql> select version();
+-----------------+
| version()       |
+-----------------+
| 5.0.10a-beta-nt |
+-----------------+
1 row in set (0.47 sec)

mysql> use test;
Database changed
mysql> show tables;
Empty set (0.13 sec)

mysql> create table test (item char(10) primary key);
Query OK, 0 rows affected (0.67 sec)

mysql> insert into test values ('100'),('200'),('300');
Query OK, 3 rows affected (0.10 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> show create table test;
+-------+-----------------------------------------------------------------------
-------------------------------------------+
| Table | Create Table
                                           |
+-------+-----------------------------------------------------------------------
-------------------------------------------+
| test  | CREATE TABLE `test` (
  `item` char(10) NOT NULL,
  PRIMARY KEY  (`item`)
) ENGINE=InnoDB DEFAULT CHARSET=cp1251 |
+-------+-----------------------------------------------------------------------
-------------------------------------------+
1 row in set (0.10 sec)

mysql> select count(*) from test where item not like '1%' and item like '2%';
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.05 sec)

mysql> create table test2 (item char(10) primary key) engine=MyISAM;
Query OK, 0 rows affected (0.08 sec)

mysql> insert into test2 values ('100'),('200'),('300');
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select count(*) from test2 where item not like '1%' and item like '2%';
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.01 sec)
[3 Jul 2009 13:20] Mina Demian
Retested this bug on 5.1.36 - still same error - should return rows on primary key but none returned instead
[3 Jul 2009 13:35] Valeriy Kravchuk
Sorry, I still can not repeat this. Look:

valeriy-kravchuks-macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.37-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table test (item char(10) primary key);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into test values ('100'),('200'),('300');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select count(*) from test where item not like '1%' and item like '2%';
+----------+
| count(*) |
+----------+
|        1 | 
+----------+
1 row in set (0.00 sec)

mysql> show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `item` char(10) NOT NULL,
  PRIMARY KEY (`item`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> alter table test engine=InnoDB;
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select count(*) from test where item not like '1%' and item like '2%';
+----------+
| count(*) |
+----------+
|        1 | 
+----------+
1 row in set (0.00 sec)

If you have another repeatable test case, please, send it.