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: | |
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
[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.