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

[18 Jan 2011 12:36] Peter Laursen
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!)
[18 Jan 2011 12:45] Peter Laursen
.. and a less random output order would also be nice BTW. 
(alphabetically, 'compound modes' first or whatever).
[20 Jan 2011 11:39] Sveta Smirnova
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.