Bug #121352 Prepared statement returns an unexpected WEEK() result after changing default_week_format

• Submitted: 22 Sep 17:01
• Modified: 23 Sep 6:12
• Reporter: Xiaoyuan Xie
• Status: Verified
• Category: MySQL Server: Documentation
• Severity: S2 (Serious)
• Version: 8.0.46, 9.4.11, 9.7.2
• OS: Ubuntu
• CPU Architecture: Any

[22 Sep 17:01] Xiaoyuan Xie

Description:
When WEEK(date) is used without an explicit mode, its behavior depends on the session variable default_week_format.

In this test case, default_week_format is first set to 6, and a statement containing WEEK('2001-02-02') is prepared. The session variable is then restored to DEFAULT, after which @@SESSION.default_week_format is 0.

However, when the previously prepared statement is executed, WEEK('2001-02-02') returns 5 instead of 4. The result therefore does not correspond to the current session value of default_week_format at execution time.

How to repeat:
SET SESSION default_week_format = 6;

PREPARE s FROM
  'SELECT WEEK(''2001-02-02'')';

SET SESSION default_week_format = DEFAULT;

SELECT @@SESSION.default_week_format;
-- 0

EXECUTE s;

Actual Result: 5
Expected Result: 4

[22 Sep 20:30] Roy Lyseng

Security tag has been cleared.

[23 Sep 6:12] Chaithra Marsur Gopala Reddy

Hi Xiaoyuan Xie,

Thank you for the test case. Verified as described.