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: | |
Category: | MySQL Server: MyISAM storage engine | Severity: | S1 (Critical) |
Version: | 4.1.15/5.0 BK | OS: | Windows (Windows XP/Linux) |
Assigned to: | Assigned Account | CPU Architecture: | Any |
[12 Dec 2005 19:13]
Jan Kuehl
[12 Dec 2005 19:16]
Jan Kuehl
I forget - we use MyISAM-Tables
[12 Dec 2005 19:50]
MySQL Verification Team
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)
[22 Jun 2006 20:27]
Sergey Vojtovich
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.