Search code examples
mysqlinnodblabviewinformation-schema

INFORMATION_SCHEMA.COLUMNS sorted alphabetically initially, then a few days later sorted by ORDINAL_POSITION

After MySQL 8.0.25 (InnoDB engine on Windows) upgrade from MariaDB (10.1.26, and I don't think we were using InnoDB engine on Linux), every time our DB server restarts, we run into this issue:

SELECT * INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = {insert table name here};

returns the column names alphabetically, though the values in the ORDINAL_POSITION column are correct.

SELECT * FROM {insert table name here} WHERE 1=0;

This returns columns ordered alphabetically rather than by ORDINAL_POSITION in some clients (LabView queries seem to be mostly affected by this).

After a few days, everything goes back to normal, with SELECT statements always ordering columns by ORDINAL_POSITION, and INFORMATION_SCHEMA.COLUMNS being sorted by ORDINAL_POSITION as expected.

Unfortunately, due to some of our programs depending on column order being consistent in SQL query results, this has been causing a lot of trouble, and we seem to be going in circles trying to figure it out.

Any thoughts as to what would cause this delayed sorting of INFORMATION_SCHEMA.COLUMNS, and why some clients would return the column order based on the order of INFORMATION_SCHEMA.COLUMNS rather than by ORDINAL_POSITION?

Any known server/DB settings to help resolve this?

Thanks, and apologies, this is way out of my knowledge base, so this is not a great post for content or question style.

======= UPDATE/EDIT =======

Apologies, I think I accidentally lumped in a different issue/behavior that was due to a separate thing that was already fixed. The column order in the SELECT queries may not have been incorrect this time, as that was a different issue that was previously resolved, and was due to a configuration issue during the upgrade.

Essentially the issue seems to be due to the assumption that the INFORMATION_SCHEMA.COLUMNS view would always have the same sort order, and I think @Tangenially Perpendicular's answer verifies that cannot be assumed.

Side note, it looks like FlySpeed SQL Query (issue submitted) was also assuming this, as did Heidi SQL (fixed) at one point.

Solution

  • In SQL order is not an inherent property of a data set. The wording that appears in the SQL-92 spec is

    If an <order by clause> is not specified, then the ordering of the rows ... is implementation-dependent.

    You'll find that in some form in a number of places.

    There's no server-wide or database-wide option to set it. You need to specify the required order in your query. Your example above becomes

    SELECT * from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = <your table name> order by ORDINAL_POSITION;

    You'll need to update your queries.