MySQL Bugs: #15691: wrong select result if primary key contains a blob column

Bug #15691	wrong select result if primary key contains a blob column
Submitted:	12 Dec 2005 19:13	Modified:	22 Jun 2006 20:27
Reporter:	Jan Kuehl	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S1 (Critical)
Version:	4.1.15/5.0 BK	OS:	Microsoft Windows (Windows XP/Linux)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
If I execute the queries below the result for 4.1.15 is:
subSymbolId   superSymbolId 
1307184        1307190 
1307186        1307190

but the right result is (4.1.8, 4.1.10 tested) 
subSymbolId   superSymbolId 
1307184	        1307190
1307186	        1307190
1307188	        1307190

If I remove superClassName(32) from the primary key of table tmpInh, the rigth result is returned.

How to repeat:
DROP TABLE IF EXISTS tmpGVSpecial;
DROP TABLE IF EXISTS tmpInh;

CREATE TABLE IF NOT EXISTS tmpGVSpecial( 
 id INT(4) NOT NULL, PRIMARY KEY(id) )
;

INSERT IGNORE INTO tmpGVSpecial VALUES(1307101),(1307151),(1307184),(1307186),(1307188),(1307190)
;

CREATE TABLE IF NOT EXISTS tmpInh( 
  superSymbolId		INT	NOT NULL, 
  subSymbolId			INT	NOT NULL, 
  superClassName		TINYBLOB	NOT NULL, 
  PRIMARY KEY(superSymbolId, subSymbolId, superClassName(32)))
;

INSERT INTO tmpInh VALUES 
(-1,	1307101,	"Object"),
(-1,	1307151,	"Object"),
(-1,	1307184,	"Object"),
(-1,	1307186,	"Object"),
(-1,	1307188,	"Object"),
(1307190,	1307184,	"IService"),
(1307190,	1307186,	"IService"),
(1307190,	1307188,	"IService")
;

flush tables;
flush hosts;

SELECT i.subSymbolId, i.superSymbolId, 4, 1 
FROM tmpGVSpecial=tmp1, tmpGVSpecial=tmp2, tmpInh= i 
WHERE i.superSymbolId=tmp1.id  
AND i.subSymbolId=tmp2.id 

Suggested fix:
no idea

I forget - we use MyISAM-Tables

miguel@hegel:~/dbs/4.1> bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.17-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE IF NOT EXISTS tmpGVSpecial( 
    ->  id INT(4) NOT NULL, PRIMARY KEY(id) )
    -> ;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT IGNORE INTO tmpGVSpecial
    -> VALUES(1307101),(1307151),(1307184),(1307186),(1307188),(1307190)
    -> ;
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0
mysql> CREATE TABLE IF NOT EXISTS tmpInh( 
    ->   superSymbolId INT NOT NULL, 
    ->   subSymbolId INT NOT NULL, 
    ->   superClassName TINYBLOB NOT NULL, 
    ->   PRIMARY KEY(superSymbolId, subSymbolId, superClassName(32)))
    -> ;
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO tmpInh VALUES 
    -> (-1,1307101,"Object"),
    -> (-1,1307151,"Object"),
    -> (-1,1307184,"Object"),
    -> (-1,1307186,"Object"),
    -> (-1,1307188,"Object"),
    -> (1307190,1307184,"IService"),
    -> (1307190,1307186,"IService"),
    -> (1307190,1307188,"IService")
    -> ;
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> SELECT i.subSymbolId, i.superSymbolId, 4, 1 
    -> FROM tmpGVSpecial=tmp1, tmpGVSpecial=tmp2, tmpInh= i 
    -> WHERE i.superSymbolId=tmp1.id  
    -> AND i.subSymbolId=tmp2.id;
+-------------+---------------+---+---+
| subSymbolId | superSymbolId | 4 | 1 |
+-------------+---------------+---+---+
|     1307184 |       1307190 | 4 | 1 |
|     1307186 |       1307190 | 4 | 1 |
+-------------+---------------+---+---+
2 rows in set (0.01 sec)

mysql> alter table tmpInh DROP PRIMARY KEY;                       
Query OK, 8 rows affected (0.01 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> alter table tmpInh add PRIMARY KEY(superSymbolId, subSymbolId);
Query OK, 8 rows affected (0.01 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> SELECT i.subSymbolId, i.superSymbolId, 4, 1 
    -> FROM tmpGVSpecial=tmp1, tmpGVSpecial=tmp2, tmpInh= i 
    -> WHERE i.superSymbolId=tmp1.id  
    -> AND i.subSymbolId=tmp2.id;
+-------------+---------------+---+---+
| subSymbolId | superSymbolId | 4 | 1 |
+-------------+---------------+---+---+
|     1307184 |       1307190 | 4 | 1 |
|     1307186 |       1307190 | 4 | 1 |
|     1307188 |       1307190 | 4 | 1 |
+-------------+---------------+---+---+
3 rows in set (0.00 sec)

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.18-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE IF NOT EXISTS tmpGVSpecial( 
    ->  id INT(4) NOT NULL, PRIMARY KEY(id) )
    -> ;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT IGNORE INTO tmpGVSpecial
    -> VALUES(1307101),(1307151),(1307184),(1307186),(1307188),(1307190)
    -> ;
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE IF NOT EXISTS tmpInh( 
    ->   superSymbolId INT NOT NULL, 
    ->   subSymbolId INT NOT NULL, 
    ->   superClassName TINYBLOB NOT NULL, 
    ->   PRIMARY KEY(superSymbolId, subSymbolId, superClassName(32)))
    -> ;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO tmpInh VALUES 
    -> (-1,1307101,"Object"),
    -> (-1,1307151,"Object"),
    -> (-1,1307184,"Object"),
    -> (-1,1307186,"Object"),
    -> (-1,1307188,"Object"),
    -> (1307190,1307184,"IService"),
    -> (1307190,1307186,"IService"),
    -> (1307190,1307188,"IService")
    -> ;
Query OK, 8 rows affected (0.02 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> SELECT i.subSymbolId, i.superSymbolId, 4, 1 
    -> FROM tmpGVSpecial=tmp1, tmpGVSpecial=tmp2, tmpInh= i 
    -> WHERE i.superSymbolId=tmp1.id  
    -> AND i.subSymbolId=tmp2.id;
+-------------+---------------+---+---+
| subSymbolId | superSymbolId | 4 | 1 |
+-------------+---------------+---+---+
|     1307184 |       1307190 | 4 | 1 |
|     1307186 |       1307190 | 4 | 1 |
+-------------+---------------+---+---+
2 rows in set (0.02 sec)

mysql> alter table tmpInh DROP PRIMARY KEY;
Query OK, 8 rows affected (0.01 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> alter table tmpInh add PRIMARY KEY(superSymbolId, subSymbolId);
Query OK, 8 rows affected (0.01 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> SELECT i.subSymbolId, i.superSymbolId, 4, 1 
    -> FROM tmpGVSpecial=tmp1, tmpGVSpecial=tmp2, tmpInh= i 
    -> WHERE i.superSymbolId=tmp1.id  
    -> AND i.subSymbolId=tmp2.id;
+-------------+---------------+---+---+
| subSymbolId | superSymbolId | 4 | 1 |
+-------------+---------------+---+---+
|     1307184 |       1307190 | 4 | 1 |
|     1307186 |       1307190 | 4 | 1 |
|     1307188 |       1307190 | 4 | 1 |
+-------------+---------------+---+---+
3 rows in set (0.01 sec)

mysql> 

miguel@hegel:~/dbs/4.0> bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.27-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE IF NOT EXISTS tmpGVSpecial( 
    ->  id INT(4) NOT NULL, PRIMARY KEY(id) )
    -> ;
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT IGNORE INTO tmpGVSpecial
    -> VALUES(1307101),(1307151),(1307184),(1307186),(1307188),(1307190)
    -> ;
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE IF NOT EXISTS tmpInh( 
    ->   superSymbolId INT NOT NULL, 
    ->   subSymbolId INT NOT NULL, 
    ->   superClassName TINYBLOB NOT NULL, 
    ->   PRIMARY KEY(superSymbolId, subSymbolId, superClassName(32)))
    -> ;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO tmpInh VALUES 
    -> (-1,1307101,"Object"),
    -> (-1,1307151,"Object"),
    -> (-1,1307184,"Object"),
    -> (-1,1307186,"Object"),
    -> (-1,1307188,"Object"),
    -> (1307190,1307184,"IService"),
    -> (1307190,1307186,"IService"),
    -> (1307190,1307188,"IService")
    -> ;
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> SELECT i.subSymbolId, i.superSymbolId, 4, 1 
    -> FROM tmpGVSpecial=tmp1, tmpGVSpecial=tmp2, tmpInh= i 
    -> WHERE i.superSymbolId=tmp1.id  
    -> AND i.subSymbolId=tmp2.id;
+-------------+---------------+---+---+
| subSymbolId | superSymbolId | 4 | 1 |
+-------------+---------------+---+---+
|     1307184 |       1307190 | 4 | 1 |
|     1307186 |       1307190 | 4 | 1 |
|     1307188 |       1307190 | 4 | 1 |
+-------------+---------------+---+---+
3 rows in set (0.02 sec)

Wasn't able to repeat with current 5.0 BK using both original and simplified test cases. That is SELECT returns 3 rows. This problem was likely fixed with patch for BUG#14980.