Bug #42032 Description of YEAR type
Submitted: 11 Jan 2009 19:02 Modified: 12 Jan 2009 20:18
Reporter: Mark Horvath Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: Documentation Severity:S4 (Feature request)
Version: OS:Any
Assigned to: Paul DuBois CPU Architecture:Any

[11 Jan 2009 19:02] Mark Horvath
Description:
In the online documentation for the Y2K problem (http://dev.mysql.com/doc/refman/5.1/en/year.html):

It says: "...because MySQL stores years internally using four digits."

But from the Year data type page: "The YEAR type is a one-byte type".

1 byte can only store values btw 0..255, as noted in the Year page. So the first quote seems to be not correct (correctly: it only displays using 2 or 4 digits).

It should also be noted that while the system is y2k safe, it could have problems with pre-1900 or post-2155 dates, and also the starting year problem still applies even when the four-digit display is used (in the example only the two-digit display is shown).

How to repeat:
See online docs at

http://dev.mysql.com/doc/refman/5.1/en/year.html
or
http://dev.mysql.com/doc/refman/6.0/en/y2k-issues.html
[11 Jan 2009 21:55] Jon Stephens
Run the following statements:

CREATE TABLE d (i INT, y YEAR);

DELIMITER //

CREATE PROCEDURE yp ()
  BEGIN
    DECLARE i INT DEFAULT 0;

    WHILE i < 10000 DO
      INSERT INTO test.d VALUES(i, i);
      SET i = i + 1;
    END WHILE;
  END //

DELIMITER ;

CALL yp();

SELECT * FROM test.d;

Now observe that:

1. For 0 < i < 70: y = 2000 + i.

2. For 70 <= i < 100: y = 1900 + i.

2. For 1900 < i < 2156: y = i.

3. For all other values of i: y = 0000.

From this we can conclude:

1. Column y stores 69 + 30 + 155 + 1 = 255 distinct values. Hence 1 byte.

2. Every value stored in column y is 4 digits wide. (Even if you insert a value that has more than or fewer than 4 digits, the value that you get back has 4 digits.)

So IMO the documentation is correct.

If you repeat the test but define the table using YEAR(2) instead of YEAR or YEAR(4), the results will be *displayed* differently, but IIUC this has nothing to do with how the server represents YEAR values *internally*; in this case, the first 2 digits of the value merely get chopped off before it's sent to the client, correct?

I'm inclined to mark this as !Bug, so I won't verify, but I'd like someone else with better knowledge of the server internals to look it over first.

Set lead but left status as Open for now.
[11 Jan 2009 21:55] Jon Stephens
Also gave the bug a better Synopsis.
[12 Jan 2009 20:18] Paul DuBois
Jon is correct, there is nothing to do here. The mapping between the single byte used for YEAR values and the range of allowable values is special and is what allows 4-digit year values.