MySQL Bugs: #9309: Wrong order in INFORMATION

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

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

I can't repeat this bug(Suse 9.1, compile-pentium-debug, last changeset: 1.1829).

This bug appears if MySQL is compiled using 'compile-pentium' script

Fixed in 5.0.3-beta repo.

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.

Noted in 5.0.4 changelog.

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