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: | |
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
[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.