MySQL Bugs: #59581: Equivalence of sql_modes not reflected in sql

Bug #59581	Equivalence of sql_modes not reflected in sql_mode returned.
Submitted:	18 Jan 2011 12:36	Modified:	20 Jan 2011 11:39
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.1.54 - likely any 5.x	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html

'TRADITIONAL' sql_mode is described as "quivalent to STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER".

Then I find the below output inconsistent.

How to repeat:
SET SQL_MODE = 'TRADITIONAL';
SHOW VARIABLES LIKE 'sql_mode';
--STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER

SET SQL_MODE = 'STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER';
SHOW VARIABLES LIKE 'sql_mode'; 
-- STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER
(note: substring 'traditional' does not occur here)

Suggested fix:
I think 'traditional' should be included in output from last statement as well.

I realize that docs use the term 'equivalent' - and not 'identical'. So with this is mind it is not a documentation bug. Though not quite clear in what respects 'equivalent' means 'identical' and in what respects 'not identical'

This probably affects all 'compound modes aggregated from simple modes'.  It would useful to be able parse for a 'compound mode' as a substring ('traditional', 'ANSI' or whatever) in order to - in a simple way - to check compliance with a 'compound mode'.

Please at least consider this as a feature request!

(I hope my terms 'simple mode' and 'compound mode' are understandable!)

.. and a less random output order would also be nice BTW. 
(alphabetically, 'compound modes' first or whatever).

Thank you for the report.

Verified as described.

In 5.6.2 TRADITIONAL also contains NO_ENGINE_SUBSTITUTION (==mode is changing), so, probably, this should be left as is.