[MDEV-17198] Wrong order when selecting in INFORMATION_SCHEMA.COLUMNS for fields with large ENUMs Created: 2018-09-14  Updated: 2018-09-25  Resolved: 2018-09-25

Status: Closed
Project: MariaDB Server
Component/s: N/A
Affects Version/s: 5.5.56
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Sébastien Zinsius Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

Red Hat Entreprise Linux Workstation 7.5

Attachments: Text File CAF07.log     Text File CAF07_RBCTB.log     PNG File diff.png     PNG File picture327-2.png    
Issue Links:
Relates
relates to MDEV-9226 SHOW COLUMNS returns wrong column ord... Closed

 Description   

For each of my databases the following query is used to get and store a MD5 information identifying the consistency of the database format (more than 150 tables):

SELECT MD5(GROUP_CONCAT(TABLE_NAME,COLUMN_NAME,ORDINAL_POSITION,DATA_TYPE,IS_NULLABLE)) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = 'mydatabase' ORDER BY TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION

This worked for years on RHEL5/6 with MySQL.
Today by migrating on RHEL7/MariaDB I encounter problems with this query.

The result of the query is different following the name of the database.
As soon as it contains an underscore ("_") character the result is systematically false.

Database name MD5
CAF07 6915dc76f78d285f493aaeaacbc3aefb
A 6915dc76f78d285f493aaeaacbc3aefb
CAF07_RBCTB_TST 460c779c0643d557fe40c24ab203630b
CAF07_RBCTB e51c6dd63770733d1931c70c44b44782

When displaying the result of the inner concatenation we can notice that the order to the concatenated strings is not correct when the name of the database contains an underscore:

(see also attached log files)

A flavor of this can already be noticed by performing a simple SELECT query on the COLUMNS table:

The type of the field which causes problem is an ENUM with a high number of elements:

  `Type` enum('EBDefault','EBnoRegenerativeBrake','EBnoEddyCurrentBrake','EBnoMagneticShoeBrake','EBnoEPBrake','EBnoRegenerativeBrakeEddyCurrentBrake','EBnoRegenerativeBrakeEddyCurrentBrakeMagneticShoeBrake','EBnoRegenerativeBrakeEddyCurrentBrakeMagneticShoeBrakeEPBrake','EBnoRegenerativeBrakeEddyCurrentBrakeEPBrake','EBnoRegenerativeBrakeMagneticShoeBrake','EBnoRegenerativeBrakeMagneticShoeBrakeEPBrake','EBnoRegenerativeBrakeEPBrake','EBnoEddyCurrentBrakeMagneticShoeBrake','EBnoEddyCurrentBrakeMagneticShoeBrakeEPBrake','EBnoEddyCurrentBrakeEPBrake','EBnoMagneticShoeBrakeEPBrake','SBDefault','SBnoRegenerativeBrake','SBnoEddyCurrentBrake','SBnoEPBrake','SBnoRegenerativeBrakeEddyCurrentBrake','SBnoRegenerativeBrakeEddyCurrentBrakeEPBrake','SBnoRegenerativeBrakeEPBrake','SBnoEddyCurrentBrakeEPBrake','NormalSBFreightG_Default','NormalSBFreightG_A_SB01','NormalSBFreightG_A_SB12','NormalSB_Default','NormalSB_A_SB01','NormalSB_A_SB12') NOT NULL default 'SBDefault',

When reducing the number of elements of the ENUM (to 2) the problem does no more appear.

It can also be noticed that the results are different following the user connecting to the database server:

  • correct with the root user
  • incorrect with the standard user defined for the application.

The problem seems similar to MDEV-9226.

 Comments   
Comment by Alice Sherepa [ 2018-09-24 ]

Please explain why you consider it is a bug.
I can reproduce unordered results inside GROUP_CONCAT with large enum inside the table, but they are not promised to be ordered,
as ORDER BY belongs to the whole query.
Maybe the query should be like 

SELECT MD5(GROUP_CONCAT(TABLE_NAME,COLUMN_NAME,ORDINAL_POSITION,DATA_TYPE,IS_NULLABLE ORDER BY TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION)) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = 'mydatabase' ;

Comment by Sébastien Zinsius [ 2018-09-25 ]

Dear Alice,

Thanks for your answer.
I'm quite embarrassed: you're completely right!
When using your query having the ORDER BY within the GROUP_CONCAT the result is stable and not depending on the name of the table.
However, it is really strange that the existing query never caused any problem through the years.

Thanks again!

Best regards,
Sébastien

Generated at Thu Feb 08 08:34:39 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.