3

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.

oudent
  • 33
  • 5
  • Further note that there are enough differences between MySQL 5.x, MySQL 8.0, and MariaDB that you should not assume any consistency -- even of what tables and columns are in `information_schema`, much less in the order of `SELECTs`. Ditto for columns of `EXPLAIN`, sql_modes, etc, etc. – Rick James Aug 29 '21 at 04:01
  • Thanks for the explanation. That is what I tried to tell the higher ups, but then I also got caught up in some of the misinformation they believe is part of the current problem (hence the edit). I believe the actual issue we are currently facing is a LabView timeout (with errors not reported) rather than the columns being sorted incorrectly in the SELECT's, and then the SQL viewer FlySpeed SQL Query showing columns out of order (separate minor issue), misleading people to believe this is a related issue. – oudent Aug 29 '21 at 15:38

1 Answers1

3

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.

Tangentially Perpendicular
  • 5,012
  • 4
  • 11
  • 29