MDEV-6996: SET STATEMENT default_week_format = .. has no effect

Change constant with a system variable.

5 files changed, 48 insertions, 3 deletions

diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result
index bf07595bc3a..0007847f22a 100644
--- a/mysql-test/r/func_time.result
+++ b/mysql-test/r/func_time.result
@@ -872,7 +872,7 @@ explain extended select period_add("9602",-12),period_diff(199505,"9404"),from_d
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
-Note	1003	select period_add('9602',-(12)) AS `period_add("9602",-12)`,period_diff(199505,'9404') AS `period_diff(199505,"9404")`,from_days(to_days('960101')) AS `from_days(to_days("960101"))`,dayofmonth('1997-01-02') AS `dayofmonth("1997-01-02")`,month('1997-01-02') AS `month("1997-01-02")`,monthname('1972-03-04') AS `monthname("1972-03-04")`,dayofyear('0000-00-00') AS `dayofyear("0000-00-00")`,hour('1997-03-03 23:03:22') AS `HOUR("1997-03-03 23:03:22")`,minute('23:03:22') AS `MINUTE("23:03:22")`,second(230322) AS `SECOND(230322)`,quarter(980303) AS `QUARTER(980303)`,week('1998-03-03',0) AS `WEEK("1998-03-03")`,yearweek('2000-01-01',1) AS `yearweek("2000-01-01",1)`,week(19950101,1) AS `week(19950101,1)`,year('98-02-03') AS `year("98-02-03")`,(weekday(curdate()) - weekday(now())) AS `weekday(curdate())-weekday(now())`,dayname('1962-03-03') AS `dayname("1962-03-03")`,unix_timestamp() AS `unix_timestamp()`,sec_to_time((time_to_sec('0:30:47') / 6.21)) AS `sec_to_time(time_to_sec("0:30:47")/6.21)`,curtime() AS `curtime()`,utc_time() AS `utc_time()`,curdate() AS `curdate()`,utc_date() AS `utc_date()`,utc_timestamp() AS `utc_timestamp()`,date_format('1997-01-02 03:04:05','%M %W %D %Y %y %m %d %h %i %s %w') AS `date_format("1997-01-02 03:04:05", "%M %W %D %Y %y %m %d %h %i %s %w")`,from_unixtime(unix_timestamp('1994-03-02 10:11:12')) AS `from_unixtime(unix_timestamp("1994-03-02 10:11:12"))`,('1997-12-31 23:59:59' + interval 1 second) AS `"1997-12-31 23:59:59" + INTERVAL 1 SECOND`,('1998-01-01 00:00:00' - interval 1 second) AS `"1998-01-01 00:00:00" - INTERVAL 1 SECOND`,('1997-12-31' + interval 1 day) AS `INTERVAL 1 DAY + "1997-12-31"`,extract(year from '1999-01-02 10:11:12') AS `extract(YEAR FROM "1999-01-02 10:11:12")`,('1997-12-31 23:59:59' + interval 1 second) AS `date_add("1997-12-31 23:59:59",INTERVAL 1 SECOND)`
+Note	1003	select period_add('9602',-(12)) AS `period_add("9602",-12)`,period_diff(199505,'9404') AS `period_diff(199505,"9404")`,from_days(to_days('960101')) AS `from_days(to_days("960101"))`,dayofmonth('1997-01-02') AS `dayofmonth("1997-01-02")`,month('1997-01-02') AS `month("1997-01-02")`,monthname('1972-03-04') AS `monthname("1972-03-04")`,dayofyear('0000-00-00') AS `dayofyear("0000-00-00")`,hour('1997-03-03 23:03:22') AS `HOUR("1997-03-03 23:03:22")`,minute('23:03:22') AS `MINUTE("23:03:22")`,second(230322) AS `SECOND(230322)`,quarter(980303) AS `QUARTER(980303)`,week('1998-03-03',@@default_week_format) AS `WEEK("1998-03-03")`,yearweek('2000-01-01',1) AS `yearweek("2000-01-01",1)`,week(19950101,1) AS `week(19950101,1)`,year('98-02-03') AS `year("98-02-03")`,(weekday(curdate()) - weekday(now())) AS `weekday(curdate())-weekday(now())`,dayname('1962-03-03') AS `dayname("1962-03-03")`,unix_timestamp() AS `unix_timestamp()`,sec_to_time((time_to_sec('0:30:47') / 6.21)) AS `sec_to_time(time_to_sec("0:30:47")/6.21)`,curtime() AS `curtime()`,utc_time() AS `utc_time()`,curdate() AS `curdate()`,utc_date() AS `utc_date()`,utc_timestamp() AS `utc_timestamp()`,date_format('1997-01-02 03:04:05','%M %W %D %Y %y %m %d %h %i %s %w') AS `date_format("1997-01-02 03:04:05", "%M %W %D %Y %y %m %d %h %i %s %w")`,from_unixtime(unix_timestamp('1994-03-02 10:11:12')) AS `from_unixtime(unix_timestamp("1994-03-02 10:11:12"))`,('1997-12-31 23:59:59' + interval 1 second) AS `"1997-12-31 23:59:59" + INTERVAL 1 SECOND`,('1998-01-01 00:00:00' - interval 1 second) AS `"1998-01-01 00:00:00" - INTERVAL 1 SECOND`,('1997-12-31' + interval 1 day) AS `INTERVAL 1 DAY + "1997-12-31"`,extract(year from '1999-01-02 10:11:12') AS `extract(YEAR FROM "1999-01-02 10:11:12")`,('1997-12-31 23:59:59' + interval 1 second) AS `date_add("1997-12-31 23:59:59",INTERVAL 1 SECOND)`
 SET @TMP='2007-08-01 12:22:49';
 CREATE TABLE t1 (d DATETIME);
 INSERT INTO t1 VALUES ('2007-08-01 12:22:59');
diff --git a/mysql-test/r/partition_bug18198.result b/mysql-test/r/partition_bug18198.result
index ee7bf514807..80f11edaaf6 100644
--- a/mysql-test/r/partition_bug18198.result
+++ b/mysql-test/r/partition_bug18198.result
@@ -130,7 +130,8 @@ ERROR HY000: Constant, random or timezone-dependent expressions in (sub)partitio
 create table t1 (col1 datetime)
 partition by range(week(col1))
 (partition p0 values less than (10), partition p1 values less than (30));
-ERROR HY000: This partition function is not allowed
+ERROR 42000: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed near ')
+(partition p0 values less than (10), partition p1 values less than (30))' at line 2
 create table t1 (col1 varchar(25))
 partition by range(cast(col1 as signed))
 (partition p0 values less than (10), partition p1 values less than (30));
diff --git a/mysql-test/r/set_statement.result b/mysql-test/r/set_statement.result
index b7465499a35..6e86e4cb77e 100644
--- a/mysql-test/r/set_statement.result
+++ b/mysql-test/r/set_statement.result
@@ -1078,3 +1078,30 @@ set statement wait_timeout=default for select 1;
 ERROR 42000: The system variable wait_timeout cannot be set in SET STATEMENT.
 set statement interactive_timeout=default for select 1;
 ERROR 42000: The system variable interactive_timeout cannot be set in SET STATEMENT.
+set @save_week_format=@@default_week_format;
+set @@default_week_format=0;
+SET STATEMENT default_week_format = 2 FOR SELECT WEEK('2000-01-01');
+WEEK('2000-01-01')
+52
+create table t1 (a date);
+insert t1 values ('2000-01-01');
+explain extended select week(a) from t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	100.00	
+Warnings:
+Note	1003	select week('2000-01-01',@@default_week_format) AS `week(a)` from dual
+prepare stmt1 from "select week(a) from t1";
+execute stmt1;
+week(a)
+0
+set default_week_format = 2;
+execute stmt1;
+week(a)
+52
+alter table t1 engine=myisam;
+execute stmt1;
+week(a)
+52
+deallocate prepare stmt1;
+drop table t1;
+set @@default_week_format=@save_week_format;
diff --git a/mysql-test/t/partition_bug18198.test b/mysql-test/t/partition_bug18198.test
index 720d483e8ed..75544f58ce8 100644
--- a/mysql-test/t/partition_bug18198.test
+++ b/mysql-test/t/partition_bug18198.test
@@ -163,7 +163,7 @@ create table t1 (col1 date)
 partition by range(unix_timestamp(col1))
 (partition p0 values less than (10), partition p1 values less than (30));
 
--- error ER_PARTITION_FUNCTION_IS_NOT_ALLOWED
+-- error ER_PARSE_ERROR
 create table t1 (col1 datetime)
 partition by range(week(col1))
 (partition p0 values less than (10), partition p1 values less than (30));
diff --git a/mysql-test/t/set_statement.test b/mysql-test/t/set_statement.test
index 8b8431315f4..2c7e74093db 100644
--- a/mysql-test/t/set_statement.test
+++ b/mysql-test/t/set_statement.test
@@ -1033,3 +1033,20 @@ set statement wait_timeout=default for select 1;
 --error ER_SET_STATEMENT_NOT_SUPPORTED
 set statement interactive_timeout=default for select 1;
 
+# MDEV-6996: SET STATEMENT default_week_format = .. has no effect
+set @save_week_format=@@default_week_format;
+set @@default_week_format=0;
+SET STATEMENT default_week_format = 2 FOR SELECT WEEK('2000-01-01');
+create table t1 (a date);
+insert t1 values ('2000-01-01');
+explain extended select week(a) from t1;
+prepare stmt1 from "select week(a) from t1";
+execute stmt1;
+set default_week_format = 2;
+execute stmt1;
+alter table t1 engine=myisam;
+execute stmt1;
+deallocate prepare stmt1;
+drop table t1;
+set @@default_week_format=@save_week_format;
+