MySQL yearweek() vs week() returning different result

Question

This simple query does not seem to be outputting the correct results. Given that the mode is the same (1) for all the functions shouldn't "week("2018-12-31", 1)" show a value of 1 rather than 53?

SELECT yearweek("2018-12-31", 1), yearweek("2019-01-02", 1),
week("2018-12-31", 1), week("2019-01-02", 1)    

which outputs the following.

'201901','201901','53','1'

Is there a default dev more that I need to set somewhere?

Answer 1

Have a look at this example from WEEK function description:

mysql> SELECT YEAR('2000-01-01'), WEEK('2000-01-01',0);
        -> 2000, 0

One might argue that WEEK() should return 52 because the given date actually occurs in the 52nd week of 1999. WEEK() returns 0 instead so that the return value is “the week number in the given year.”

The above seem to suggest that all modes having 0-53 range would return the week number relative to the year of input date (it'll return 0 if the date falls in the last week of the previous year).

So if first day of year = Monday and first week of year = having 4 or more days this year, then 2018-12-31 belongs to 53rd week of 2018 -and- 1st week of 2019, and the mode parameter determines the return value:

SELECT WEEK('2018-12-31', 1); -- 53
SELECT WEEK('2018-12-31', 3); -- 1

The YEARWEEK function is unambiguous (the result includes the year) so above does not apply.