bk commit into 4.0 tree (gluh:1.1618)

[gluh]

Below is the list of changes that have just been committed into a local
4.0 repository of gluh. When gluh does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://www.mysql.com/doc/I/n/Installing_source_tree.html

ChangeSet
  1.1618 03/12/07 15:10:21 gluh@stripped +6 -0
  WL#1175: more default_week_formats for iso compatibility
  
  New formats added for 'week()' function and 'default_week_format' option(4 - 7).
  Next formats is supported now:
  *Value* *Meaning*
    `0'     Week starts on Sunday; First Sunday of the year starts week 1.
    	  Week() returns 0-53.
    `1'     Week starts on Monday; Weeks numbered according to ISO 8601:1988.
  	  Week() returns 0-53.
    `2'     Week starts on Sunday; First Sunday of the year starts week 1.
    	  Week() returns 1-53.
    `3'     Week starts on Monday; Weeks numbered according to ISO 8601:1988.
  	  Week() returns 1-53.
    `4'     Week starts on Sunday; Weeks numbered according to ISO 8601:1988.
  	  Week() returns 0-53.
    `5'     Week starts on Monday;  First Monday of the year starts week 1.
    	  Week() returns 0-53.
    `6'     Week starts on Sunday; Weeks numbered according to ISO 8601:1988.
  	  Week() returns 1-53.
    `7'     Week starts on Monday;  First Monday of the year starts week 1.
    	  Week() returns 1-53.

  sql/time.cc
    1.26 03/12/07 15:10:05 gluh@stripped +49 -19
    WL#1175 more default_week_formats for iso compatibility

  sql/mysqld.cc
    1.461 03/12/07 15:10:05 gluh@stripped +1 -1
    WL#1175 more default_week_formats for iso compatibility

  sql/mysql_priv.h
    1.209 03/12/07 15:10:05 gluh@stripped +6 -2
    WL#1175 more default_week_formats for iso compatibility

  sql/item_timefunc.cc
    1.24 03/12/07 15:10:05 gluh@stripped +52 -16
    WL#1175 more default_week_formats for iso compatibility

  mysql-test/t/func_time.test
    1.17 03/12/07 15:10:05 gluh@stripped +12 -0
    Test for 'default_week_format' option and 'week' function

  mysql-test/r/func_time.result
    1.20 03/12/07 15:10:05 gluh@stripped +25 -2
    Test for 'default_week_format' option and 'week' function

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	gluh
# Host:	gluh.mysql.r18.ru
# Root:	/home/gluh/mysql-4.0.defweek

--- 1.23/sql/item_timefunc.cc	Wed Aug  6 18:37:19 2003
+++ 1.24/sql/item_timefunc.cc	Sun Dec  7 15:10:05 2003
@@ -175,27 +175,51 @@
 }
 
 
-/*
-  Returns the week of year.
+uint week_mode(uint mode)
+{
+  uint week_format= (mode & 7);
+  if (!(week_format & WEEK_MONDAY_FIRST))
+    week_format^= WEEK_FIRST_WEEKDAY;
+  return week_format;
+}
 
-  The bits in week_format has the following meaning:
-    0	If not set:	USA format: Sunday is first day of week
-        If set:		ISO format: Monday is first day of week
-    1   If not set:	Week is in range 0-53
-    	If set		Week is in range 1-53.
+/*
+  The bits in week_format(for calc_week() function) has the following meaning:
+   WEEK_MONDAY_FIRST (0)  If not set	Sunday is first day of week
+      		   	  If set	Monday is first day of week
+   WEEK_YEAR (1)	  If not set	Week is in range 0-53
+
+   	Week 0 is returned for the the last week of the previous year (for
+	a date at start of january) In this case one can get 53 for the
+	first week of next year.  This flag ensures that the week is
+	relevant for the given year. Note that this flag is only
+	releveant if WEEK_JANUARY is not set.
+
+			  If set	 Week is in range 1-53.
+
+	In this case one may get week 53 for a date in January (when
+	the week is that last week of previous year) and week 1 for a
+	date in December.
+
+  WEEK_FIRST_WEEKDAY (2)  If not set	Weeks are numbered according
+			   		to ISO 8601:1988
+			  If set	The week that contains the first
+					'first-day-of-week' is week 1.
+	
+	ISO 8601:1988 means that if the week containing January 1 has
+	four or more days in the new year, then it is week 1;
+	Otherwise it is the last week of the previous year, and the
+	next week is week 1.
 */
 
 longlong Item_func_week::val_int()
 {
   uint year;
-  uint week_format;
   TIME ltime;
   if (get_arg0_date(&ltime,0))
     return 0;
-  week_format= (uint) args[1]->val_int();
-  return (longlong) calc_week(&ltime, 
-			      (week_format & 2) != 0,
-			      (week_format & 1) == 0,
+  return (longlong) calc_week(&ltime,
+			      week_mode((uint) args[1]->val_int()),
 			      &year);
 }
 
@@ -206,7 +230,9 @@
   TIME ltime;
   if (get_arg0_date(&ltime,0))
     return 0;
-  week=calc_week(&ltime, 1, (args[1]->val_int() & 1) == 0, &year);
+  week= calc_week(&ltime, 
+		  (week_mode((uint) args[1]->val_int()) | WEEK_YEAR),
+		  &year);
   return week+year*100;
 }
 
@@ -844,7 +870,10 @@
       case 'u':
       {
 	uint year;
-	sprintf(intbuff,"%02d",calc_week(&l_time, 0, (*ptr) == 'U', &year));
+	sprintf(intbuff,"%02d",
+		calc_week(&l_time, 
+			  ((*ptr) == 'U' ? 
+			   WEEK_FIRST_WEEKDAY : WEEK_MONDAY_FIRST) , &year));
 	str->append(intbuff);
       }
       break;
@@ -852,7 +881,11 @@
       case 'V':
       {
 	uint year;
-	sprintf(intbuff,"%02d",calc_week(&l_time, 1, (*ptr) == 'V', &year));
+	sprintf(intbuff,"%02d",
+		calc_week(&l_time,
+			  ((*ptr) == 'V' ? WEEK_YEAR | WEEK_FIRST_WEEKDAY :
+			   WEEK_YEAR | WEEK_MONDAY_FIRST),
+			  &year));
 	str->append(intbuff);
       }
       break;
@@ -860,7 +893,10 @@
       case 'X':
       {
 	uint year;
-	(void) calc_week(&l_time, 1, (*ptr) == 'X', &year);
+	(void) calc_week(&l_time,
+			 ((*ptr) == 'X' ? WEEK_YEAR | WEEK_FIRST_WEEKDAY :
+			  WEEK_YEAR | WEEK_MONDAY_FIRST),
+			 &year);
 	sprintf(intbuff,"%04d",year);
 	str->append(intbuff);
       }

--- 1.208/sql/mysql_priv.h	Sun Oct 12 00:00:20 2003
+++ 1.209/sql/mysql_priv.h	Sun Dec  7 15:10:05 2003
@@ -247,6 +247,11 @@
 #define tmp_file_prefix "#sql"			/* Prefix for tmp tables */
 #define tmp_file_prefix_length 4
 
+/* Flags for calc_week() function.  */
+#define WEEK_MONDAY_FIRST    1
+#define WEEK_YEAR            2
+#define WEEK_FIRST_WEEKDAY   4
+
 struct st_table;
 class THD;
 
@@ -806,8 +811,7 @@
 void change_double_for_sort(double nr,byte *to);
 int get_quick_record(SQL_SELECT *select);
 int calc_weekday(long daynr,bool sunday_first_day_of_week);
-uint calc_week(TIME *ltime, bool with_year, bool sunday_first_day_of_week,
-	       uint *year);
+uint calc_week(TIME *l_time, uint week_behaviour, uint *year);
 void find_date(char *pos,uint *vek,uint flag);
 TYPELIB *convert_strings_to_array_type(my_string *typelibs, my_string *end);
 TYPELIB *typelib(List<String> &strings);

--- 1.460/sql/mysqld.cc	Thu Oct 30 03:20:37 2003
+++ 1.461/sql/mysqld.cc	Sun Dec  7 15:10:05 2003
@@ -4105,7 +4105,7 @@
     "The default week format used by WEEK() functions.",
     (gptr*) &global_system_variables.default_week_format, 
     (gptr*) &max_system_variables.default_week_format, 
-    0, GET_ULONG, REQUIRED_ARG, 0, 0, 3L, 0, 1, 0},
+    0, GET_ULONG, REQUIRED_ARG, 0, 0, 7L, 0, 1, 0},
   {0, 0, 0, 0, 0, 0, GET_NO_ARG, NO_ARG, 0, 0, 0, 0, 0, 0}
 };
 

--- 1.25/sql/time.cc	Tue May 27 18:40:13 2003
+++ 1.26/sql/time.cc	Sun Dec  7 15:10:05 2003
@@ -175,42 +175,72 @@
     366 : 365;
 }
 
-/* Calculate week.  If 'with_year' is not set, then return a week 0-53, where
-   0 means that it's the last week of the previous year.
-   If 'with_year' is set then the week will always be in the range 1-53 and
-   the year out parameter will contain the year for the week */
 
-uint calc_week(TIME *l_time, bool with_year, bool sunday_first_day_of_week,
-	       uint *year)
+/*
+  The bits in week_format has the following meaning:
+   WEEK_MONDAY_FIRST (0)  If not set	Sunday is first day of week
+      		   	  If set	Monday is first day of week
+   WEEK_YEAR (1)	  If not set	Week is in range 0-53
+
+   	Week 0 is returned for the the last week of the previous year (for
+	a date at start of january) In this case one can get 53 for the
+	first week of next year.  This flag ensures that the week is
+	relevant for the given year. Note that this flag is only
+	releveant if WEEK_JANUARY is not set.
+
+			  If set	 Week is in range 1-53.
+
+	In this case one may get week 53 for a date in January (when
+	the week is that last week of previous year) and week 1 for a
+	date in December.
+
+  WEEK_FIRST_WEEKDAY (2)  If not set	Weeks are numbered according
+			   		to ISO 8601:1988
+			  If set	The week that contains the first
+					'first-day-of-week' is week 1.
+	
+	ISO 8601:1988 means that if the week containing January 1 has
+	four or more days in the new year, then it is week 1;
+	Otherwise it is the last week of the previous year, and the
+	next week is week 1.
+*/
+
+uint calc_week(TIME *l_time, uint week_behaviour, uint *year)
 {
   uint days;
   ulong daynr=calc_daynr(l_time->year,l_time->month,l_time->day);
   ulong first_daynr=calc_daynr(l_time->year,1,1);
-  uint weekday=calc_weekday(first_daynr,sunday_first_day_of_week);
+  bool monday_first= test(week_behaviour & WEEK_MONDAY_FIRST);
+  bool week_year= test(week_behaviour & WEEK_YEAR);
+  bool first_weekday= test(week_behaviour & WEEK_FIRST_WEEKDAY);
+
+  uint weekday=calc_weekday(first_daynr, !monday_first);
   *year=l_time->year;
-  if (l_time->month == 1 && l_time->day <= 7-weekday &&
-      ((!sunday_first_day_of_week && weekday >= 4) ||
-       (sunday_first_day_of_week && weekday != 0)))
+
+  if (l_time->month == 1 && l_time->day <= 7-weekday)
   {
-    /* Last week of the previous year */
-    if (!with_year)
+    if (!week_year && 
+	(first_weekday && weekday != 0 ||
+	 !first_weekday && weekday >= 4))
       return 0;
-    with_year=0;				// Don't check the week again
+    week_year= 1;
     (*year)--;
     first_daynr-= (days=calc_days_in_year(*year));
     weekday= (weekday + 53*7- days) % 7;
   }
-  if ((sunday_first_day_of_week && weekday != 0) ||
-      (!sunday_first_day_of_week && weekday >= 4))
+
+  if ((first_weekday && weekday != 0) ||
+      (!first_weekday && weekday >= 4))
     days= daynr - (first_daynr+ (7-weekday));
   else
     days= daynr - (first_daynr - weekday);
-  if (with_year && days >= 52*7)
+
+  if (week_year && days >= 52*7)
   {
-    /* Check if we are on the first week of the next year (or week 53) */
     weekday= (weekday + calc_days_in_year(*year)) % 7;
-    if (weekday < 4)
-    {					// We are at first week on next year
+    if (!first_weekday && weekday < 4 ||
+	first_weekday && weekday == 0)
+    {
       (*year)++;
       return 1;
     }

--- 1.19/mysql-test/r/func_time.result	Thu Jun 19 14:02:17 2003
+++ 1.20/mysql-test/r/func_time.result	Sun Dec  7 15:10:05 2003
@@ -95,11 +95,34 @@
 52	53	52	52
 select week(20001231,2),week(20001231,3);
 week(20001231,2)	week(20001231,3)
-1	52
+53	52
+select week(19981231,0) as '0', week(19981231,1) as '1', week(19981231,2) as '2',
week(19981231,3) as '3', week(19981231,4) as '4', week(19981231,5) as '5',
week(19981231,6) as '6', week(19981231,7) as '7';
+0	1	2	3	4	5	6	7
[phone]	52
+select week(20000101,0) as '0', week(20000101,1) as '1', week(20000101,2) as '2',
week(20000101,3) as '3', week(20000101,4) as '4', week(20000101,5) as '5',
week(20000101,6) as '6', week(20000101,7) as '7';
+0	1	2	3	4	5	6	7
+0	0	52	52	0	0	52	52
+select week(20000106,0) as '0', week(20000106,1) as '1', week(20000106,2) as '2',
week(20000106,3) as '3', week(20000106,4) as '4', week(20000106,5) as '5',
week(20000106,6) as '6', week(20000106,7) as '7';
+0	1	2	3	4	5	6	7
[phone]
+select week(20001231,0) as '0', week(20001231,1) as '1', week(20001231,2) as '2',
week(20001231,3) as '3', week(20001231,4) as '4', week(20001231,5) as '5',
week(20001231,6) as '6', week(20001231,7) as '7';
+0	1	2	3	4	5	6	7
[phone]
+select week(20010101,0) as '0', week(20010101,1) as '1', week(20010101,2) as '2',
week(20010101,3) as '3', week(20010101,4) as '4', week(20010101,5) as '5',
week(20010101,6) as '6', week(20010101,7) as '7';
+0	1	2	3	4	5	6	7
+0	1	53	1	1	1	1	1
+select yearweek(20001231,0), yearweek(20001231,1), yearweek(20001231,2),
yearweek(20001231,3), yearweek(20001231,4), yearweek(20001231,5), yearweek(20001231,6),
yearweek(20001231,7);
+yearweek(20001231,0)	yearweek(20001231,1)	yearweek(20001231,2)	yearweek(20001231,3)	yearweek(20001231,4)	yearweek(20001231,5)	yearweek(20001231,6)	yearweek(20001231,7)
[phone]	200053	200052	200101	200052	200101	200052
+set default_week_format = 6;
+select week(20001231), week(20001231,6);
+week(20001231)	week(20001231,6)
+1	1
+set default_week_format = 0;
 set default_week_format = 2;
 select week(20001231),week(20001231,2),week(20001231,0);
 week(20001231)	week(20001231,2)	week(20001231,0)
-1	1	53
+53	53	53
 set default_week_format = 0;
 select date_format('1998-12-31','%x-%v'),date_format('1999-01-01','%x-%v');
 date_format('1998-12-31','%x-%v')	date_format('1999-01-01','%x-%v')

--- 1.16/mysql-test/t/func_time.test	Thu Jun 19 14:02:17 2003
+++ 1.17/mysql-test/t/func_time.test	Sun Dec  7 15:10:05 2003
@@ -39,6 +39,18 @@
 select week(19981231,2), week(19981231,3), week(20000101,2), week(20000101,3);
 select week(20001231,2),week(20001231,3);
 
+select week(19981231,0) as '0', week(19981231,1) as '1', week(19981231,2) as '2',
week(19981231,3) as '3', week(19981231,4) as '4', week(19981231,5) as '5',
week(19981231,6) as '6', week(19981231,7) as '7';
+select week(20000101,0) as '0', week(20000101,1) as '1', week(20000101,2) as '2',
week(20000101,3) as '3', week(20000101,4) as '4', week(20000101,5) as '5',
week(20000101,6) as '6', week(20000101,7) as '7';
+select week(20000106,0) as '0', week(20000106,1) as '1', week(20000106,2) as '2',
week(20000106,3) as '3', week(20000106,4) as '4', week(20000106,5) as '5',
week(20000106,6) as '6', week(20000106,7) as '7';
+select week(20001231,0) as '0', week(20001231,1) as '1', week(20001231,2) as '2',
week(20001231,3) as '3', week(20001231,4) as '4', week(20001231,5) as '5',
week(20001231,6) as '6', week(20001231,7) as '7';
+select week(20010101,0) as '0', week(20010101,1) as '1', week(20010101,2) as '2',
week(20010101,3) as '3', week(20010101,4) as '4', week(20010101,5) as '5',
week(20010101,6) as '6', week(20010101,7) as '7';
+
+select yearweek(20001231,0), yearweek(20001231,1), yearweek(20001231,2),
yearweek(20001231,3), yearweek(20001231,4), yearweek(20001231,5), yearweek(20001231,6),
yearweek(20001231,7);
+
+set default_week_format = 6;
+select week(20001231), week(20001231,6);
+set default_week_format = 0;
+
 set default_week_format = 2;
 select week(20001231),week(20001231,2),week(20001231,0);
 set default_week_format = 0;