Bug #11196 Query using '=' and 'LIKE' simultaneously giving strange results

• Submitted: 9 Jun 2005 10:19
• Modified: 9 Jun 2005 19:30
• Reporter: Piyush Singal
• Status: Can't repeat
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.1.x
• OS: Linux (Linux)
• CPU Architecture: Any

[9 Jun 2005 10:19] Piyush Singal

Description:
The following mysql statements were executed in the order. Three charachter searching is ok, but fails if length is made more.

Also, note that Phsical is just a typo (reproducable with' Physical').

mysql> create table TEST ( name varchar(255) default NULL, myKey varchar(255) NOT NULL default '', PRIMARY KEY (myKey) ) Type=MyISAM;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> INSERT INTO TEST (name, myKey) VALUES ('jack', 'TOR.1.PhsicalLayer.1.PhysicalNode.1');
Query OK, 1 row affected (0.00 sec)

mysql> select * from TEST;
+------+-------------------------------------+
| name | myKey                               |
+------+-------------------------------------+
| jack | TOR.1.PhsicalLayer.1.PhysicalNode.1 |
+------+-------------------------------------+
1 row in set (0.00 sec)

mysql> select * from TEST where myKey = 'TOR.1.PhsicalLayer.1.PhysicalNode.1';
+------+-------------------------------------+
| name | myKey                               |
+------+-------------------------------------+
| jack | TOR.1.PhsicalLayer.1.PhysicalNode.1 |
+------+-------------------------------------+
1 row in set (0.00 sec)

mysql> select * from TEST where myKey like '%PhsicalLayer%';
+------+-------------------------------------+
| name | myKey                               |
+------+-------------------------------------+
| jack | TOR.1.PhsicalLayer.1.PhysicalNode.1 |
+------+-------------------------------------+
1 row in set (0.00 sec)

mysql> select * from TEST where myKey = 'TOR.1.PhsicalLayer.1.PhysicalNode.1' AND myKey like '%PhsicalLayer%';
Empty set (0.00 sec)

mysql> select * from TEST where myKey = 'TOR.1.PhsicalLayer.1.PhysicalNode.1' AND myKey like '%Phs%';
+------+-------------------------------------+
| name | myKey                               |
+------+-------------------------------------+
| jack | TOR.1.PhsicalLayer.1.PhysicalNode.1 |
+------+-------------------------------------+
1 row in set (0.00 sec)

mysql> select * from TEST where myKey = 'TOR.1.PhsicalLayer.1.PhysicalNode.1' AND myKey like '%Node%';
Empty set (0.00 sec)

mysql> select * from TEST where myKey = 'TOR.1.PhsicalLayer.1.PhysicalNode.1' AND myKey like '%Nod%';
+------+-------------------------------------+
| name | myKey                               |
+------+-------------------------------------+
| jack | TOR.1.PhsicalLayer.1.PhysicalNode.1 |
+------+-------------------------------------+

How to repeat:
Written above

[9 Jun 2005 19:29] Jorge del Conde

Thanks for your bug report.  I was unable to reproduce this bug in 4.1.13 from bk.

mysql> select * from TEST;
+------+-------------------------------------+
| name | myKey                               |
+------+-------------------------------------+
| jack | TOR.1.PhsicalLayer.1.PhysicalNode.1 |
+------+-------------------------------------+
1 row in set (0.01 sec)

mysql> select * from TEST where myKey = 'TOR.1.PhsicalLayer.1.PhysicalNode.1';
+------+-------------------------------------+
| name | myKey                               |
+------+-------------------------------------+
| jack | TOR.1.PhsicalLayer.1.PhysicalNode.1 |
+------+-------------------------------------+
1 row in set (0.01 sec)

mysql> select * from TEST where myKey like '%PhsicalLayer%';
+------+-------------------------------------+
| name | myKey                               |
+------+-------------------------------------+
| jack | TOR.1.PhsicalLayer.1.PhysicalNode.1 |
+------+-------------------------------------+
1 row in set (0.00 sec)

mysql> select * from TEST where myKey = 'TOR.1.PhsicalLayer.1.PhysicalNode.1'
    -> AND myKey like '%Phs%';
+------+-------------------------------------+
| name | myKey                               |
+------+-------------------------------------+
| jack | TOR.1.PhsicalLayer.1.PhysicalNode.1 |
+------+-------------------------------------+
1 row in set (0.00 sec)

mysql> select * from TEST where myKey = 'TOR.1.PhsicalLayer.1.PhysicalNode.1'
    -> AND myKey like '%PhsicalLayer%';
+------+-------------------------------------+
| name | myKey                               |
+------+-------------------------------------+
| jack | TOR.1.PhsicalLayer.1.PhysicalNode.1 |
+------+-------------------------------------+
1 row in set (0.00 sec)

mysql> select * from TEST where myKey = 'TOR.1.PhsicalLayer.1.PhysicalNode.1'
    -> AND myKey like '%Node%';
+------+-------------------------------------+
| name | myKey                               |
+------+-------------------------------------+
| jack | TOR.1.PhsicalLayer.1.PhysicalNode.1 |
+------+-------------------------------------+
1 row in set (0.00 sec)

mysql> select * from TEST where myKey = 'TOR.1.PhsicalLayer.1.PhysicalNode.1'
    -> AND myKey like '%Nod%';
+------+-------------------------------------+
| name | myKey                               |
+------+-------------------------------------+
| jack | TOR.1.PhsicalLayer.1.PhysicalNode.1 |
+------+-------------------------------------+
1 row in set (0.00 sec)

mysql>

[10 Jun 2005 8:58] Piyush Singal

Sorry for the confusion. I had tried on (4.1.7 and 4.1.10a).