Fix Bug #35261: date_format test fails if new variables LIKE '%e_format' are added

Use SELECT FROM INFORMATION_SCHEMA instead of SHOW VARIABLES LIKE to restrict
values correctly.

2 files changed, 38 insertions, 22 deletions

diff --git a/mysql-test/r/date_formats.result b/mysql-test/r/date_formats.result
index 99ae6d85fee..7e185daa668 100644
--- a/mysql-test/r/date_formats.result
+++ b/mysql-test/r/date_formats.result
@@ -1,14 +1,20 @@
 drop table if exists t1;
-SHOW GLOBAL VARIABLES LIKE "%e_format";
-Variable_name	Value
-date_format	%d.%m.%Y
-datetime_format	%Y-%m-%d %H:%i:%s
-time_format	%H.%i.%s
-SHOW SESSION VARIABLES LIKE "%e_format";
-Variable_name	Value
-date_format	%d.%m.%Y
-datetime_format	%Y-%m-%d %H:%i:%s
-time_format	%H.%i.%s
+SELECT variable_name, variable_value
+FROM information_schema.global_variables
+WHERE variable_name IN ('date_format', 'datetime_format', 'time_format')
+ORDER BY variable_name;
+variable_name	variable_value
+DATETIME_FORMAT	%Y-%m-%d %H:%i:%s
+DATE_FORMAT	%d.%m.%Y
+TIME_FORMAT	%H.%i.%s
+SELECT variable_name, variable_value
+FROM information_schema.session_variables
+WHERE variable_name IN ('date_format', 'datetime_format', 'time_format')
+ORDER BY variable_name;
+variable_name	variable_value
+DATETIME_FORMAT	%Y-%m-%d %H:%i:%s
+DATE_FORMAT	%d.%m.%Y
+TIME_FORMAT	%H.%i.%s
 SET time_format='%H%i%s';
 SET time_format='%H:%i:%s.%f';
 SET time_format='%h-%i-%s.%f%p';
@@ -26,11 +32,14 @@ set datetime_format= '%H:%i:%s %Y-%m-%d';
 set datetime_format= '%H:%i:%s.%f %m-%d-%Y';
 set datetime_format= '%h:%i:%s %p %Y-%m-%d';
 set datetime_format= '%h:%i:%s.%f %p %Y-%m-%d';
-SHOW SESSION VARIABLES LIKE "%e_format";
-Variable_name	Value
-date_format	%m-%d-%Y
-datetime_format	%h:%i:%s.%f %p %Y-%m-%d
-time_format	%h:%i:%s%p
+SELECT variable_name, variable_value
+FROM information_schema.session_variables
+WHERE variable_name IN ('date_format', 'datetime_format', 'time_format')
+ORDER BY variable_name;
+variable_name	variable_value
+DATETIME_FORMAT	%h:%i:%s.%f %p %Y-%m-%d
+DATE_FORMAT	%m-%d-%Y
+TIME_FORMAT	%h:%i:%s%p
 SET time_format='%h:%i:%s';
 ERROR 42000: Variable 'time_format' can't be set to the value of '%h:%i:%s'
 SET time_format='%H %i:%s';
diff --git a/mysql-test/t/date_formats.test b/mysql-test/t/date_formats.test
index e474fac8a2a..e5dc7ffa53e 100644
--- a/mysql-test/t/date_formats.test
+++ b/mysql-test/t/date_formats.test
@@ -6,10 +6,15 @@
 drop table if exists t1;
 --enable_warnings
 
---replace_result ROW <format> STATEMENT <format> MIXED <format>
-SHOW GLOBAL VARIABLES LIKE "%e_format";
---replace_result ROW <format> STATEMENT <format> MIXED <format>
-SHOW SESSION VARIABLES LIKE "%e_format";
+SELECT variable_name, variable_value
+FROM information_schema.global_variables
+WHERE variable_name IN ('date_format', 'datetime_format', 'time_format')
+ORDER BY variable_name;
+
+SELECT variable_name, variable_value
+FROM information_schema.session_variables
+WHERE variable_name IN ('date_format', 'datetime_format', 'time_format')
+ORDER BY variable_name;
 
 #
 # Test setting a lot of different formats to see which formats are accepted and
@@ -36,8 +41,10 @@ set datetime_format= '%H:%i:%s.%f %m-%d-%Y';
 set datetime_format= '%h:%i:%s %p %Y-%m-%d';
 set datetime_format= '%h:%i:%s.%f %p %Y-%m-%d';
 
---replace_result ROW <format> STATEMENT <format> MIXED <format>
-SHOW SESSION VARIABLES LIKE "%e_format";
+SELECT variable_name, variable_value
+FROM information_schema.session_variables
+WHERE variable_name IN ('date_format', 'datetime_format', 'time_format')
+ORDER BY variable_name;
 
 --error 1231
 SET time_format='%h:%i:%s';
@@ -121,7 +128,7 @@ SET datetime_format=default;
 # Test of str_to_date
 #
 
-# PS doesn't support fraction of a seconds
+# PS doesn't support fractions of a second
 --disable_ps_protocol
 select str_to_date(concat('15-01-2001',' 2:59:58.999'),
 	           concat('%d-%m-%Y',' ','%H:%i:%s.%f'));