Bug #9309 Wrong order in INFORMATION_SCHEMA
Submitted: 21 Mar 2005 11:25 Modified: 23 Mar 2005 17:33
Reporter: Hakan Küçükyılmaz Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.3 OS:Linux (SuSE 9.0)
Assigned to: Ramil Kalimullin CPU Architecture:Any

[21 Mar 2005 11:25] Hakan Küçükyılmaz
Description:
USE test;

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (
  a int,
  b int, 
  c int,
  PRIMARY KEY (a, b, c),
  KEY "t1~001" (b, c)
) ENGINE INNODB;

SELECT index_name, table_name, column_name, seq_in_index
  FROM information_schema.statistics
  WHERE table_name = 't1' AND table_schema = 'test'
  ORDER BY index_name, seq_in_index;

Result in 5.0.3 changeset 1.1828:
+------------+------------+-------------+--------------+
| index_name | table_name | column_name | seq_in_index |
+------------+------------+-------------+--------------+
| PRIMARY    | t1         | a           |            1 |
| PRIMARY    | t1         | b           |            2 |
| PRIMARY    | t1         | c           |            3 |
| t1~001     | t1         | b           |            1 |
| t1~001     | t1         | c           |            2 |
+------------+------------+-------------+--------------+

Result in 5.0.3 changeset 1.1829:
+------------+------------+-------------+--------------+
| index_name | table_name | column_name | seq_in_index |
+------------+------------+-------------+--------------+
| PRIMARY    | t1         | b           |            2 |
| PRIMARY    | t1         | c           |            3 |
| PRIMARY    | t1         | a           |            1 |
| t1~001     | t1         | c           |            2 |
| t1~001     | t1         | b           |            1 |
+------------+------------+-------------+--------------+

Notice the wrong order in "seq_in_index".

How to repeat:
sql_mode is:
sql_mode="ANSI_QUOTES,NO_BACKSLASH_ESCAPES,TRADITIONAL,IGNORE_SPACE"

After restarting MySQL server the wrong ordering appears. If I quit mysql-client and restart mysql-client and drop and recreate table "t1" the wrong ordering does not appear - unfortunately not all the time. I was not able to reproduce a pattern when the wrong ordering happens and when not.

The wrong ordering does not appear in 5.0.3 changeset 1.1828
[21 Mar 2005 12:38] Sergei Glukhov
I can't repeat this bug(Suse 9.1, compile-pentium-debug, last changeset: 1.1829).
[21 Mar 2005 14:04] Sergei Glukhov
This bug appears if MySQL is compiled using 'compile-pentium' script
[23 Mar 2005 13:44] Ramil Kalimullin
Fixed in 5.0.3-beta repo.
[23 Mar 2005 15:38] Joerg Bruehe
Comment about version of the fix is wrong: 5.0.3 is already cloned, the changeset mentioned here is not in 5.0.3, so it will go into 5.0.4 only.
[23 Mar 2005 17:33] Paul Dubois
Noted in 5.0.4 changelog.
[23 Mar 2005 23:09] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/23332