Bug #21763 Wrong query results after index creation

• Submitted: 21 Aug 2006 20:42
• Modified: 21 Aug 2006 22:20
• Reporter: Cristian Gafton
• Status: Closed
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: 5.0.24
• OS: Linux (Linux)
• CPU Architecture: Any

[21 Aug 2006 20:42] Cristian Gafton

Description:
MySQL returns wrong data set results after an index is created on a table. The attached data.sql.gz contains the data dump, while the query.sql contains the query that causes the issue.

The index in question is:
CREATE UNIQUE INDEX ffFlavorBaseFlagIdx on ffFlavor(base, flag);

Before the index creation, the query count returns 8954 rows (correct result). After the index is created, the same query returns only 5076 rows.

How to repeat:
mysql> \. data.sql
[...]

mysql> \. query.sql
+----------+
| COUNT(*) |
+----------+
|     8954 |
+----------+
1 row in set (4.30 sec)

mysql> CREATE UNIQUE INDEX ffFlavorBaseFlagIdx on ffFlavor(base, flag);
Query OK, 39 rows affected (0.03 sec)
Records: 39  Duplicates: 0  Warnings: 0

mysql> \. query.sql
+----------+
| COUNT(*) |
+----------+
|     5076 |
+----------+
1 row in set (1.89 sec)

[21 Aug 2006 20:43] Cristian Gafton

dataset for the test case

Attachment: data.sql.gz (application/x-gzip, text), 53.03 KiB.

[21 Aug 2006 20:43] Cristian Gafton

Test Query

Attachment: query.sql (text/x-sql), 910 bytes.

[21 Aug 2006 22:20] MySQL Verification Team

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.25-debug | 
+--------------+
1 row in set (0.00 sec)

mysql> source /home/miguel/a/query.sql
+----------+
| COUNT(*) |
+----------+
|     8954 | 
+----------+
1 row in set (24.76 sec)

mysql> CREATE UNIQUE INDEX ffflavorbaseFlagIdx on ffflavor(base, flag);
Query OK, 39 rows affected (0.05 sec)
Records: 39  Duplicates: 0  Warnings: 0

mysql> source /home/miguel/a/query.sql
+----------+
| COUNT(*) |
+----------+
|     8954 | 
+----------+
1 row in set (13.28 sec)

mysql>