Bug #78470 When dates are provided as inputs for period_diff, result is not understandable
Submitted: 17 Sep 2015 12:20 Modified: 18 Sep 2015 10:52
Reporter: Su Dylan Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.6.22 OS:Any
Assigned to: MySQL Verification Team CPU Architecture:Any

[17 Sep 2015 12:20] Su Dylan
Description:
Output:

mysql> select period_diff(20101202,20101001);
+--------------------------------+
| period_diff(20101202,20101001) |
+--------------------------------+
|                             25 |
+--------------------------------+
1 row in set (0.00 sec)

mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.22-log |
+------------+
1 row in set (0.00 sec)

Problem:
From the manual, period_diff should accept YYMM and YYYYMM. However, when we provide dates as the input, how should we understand this "25"?

How to repeat:
select period_diff(20101202,20101001);
[17 Sep 2015 13:13] Su Dylan
Another situation is not understandable:
mysql> select period_diff(20101201123456,20101001123456);
+--------------------------------------------+
| period_diff(20101201123456,20101001123456) |
+--------------------------------------------+
|                                   24000000 |
+--------------------------------------------+
1 row in set (0.00 sec)
[18 Sep 2015 10:52] MySQL Verification Team
http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_period-diff

Returns the number of months between periods P1 and P2. P1 and P2 should be in the format YYMM or YYYYMM. Note that the period arguments P1 and P2 are not date values.

select period_diff(20101202,20101001);
select period_diff(20101201123456,20101001123456);

You entered data that's not in the requested format so the result of the function is unclear to you. What you put in is what you get out. Anyhow, the format is understood as YY.*MM so last two digits are month, the ones before are the year and result is in months.

So to understand what is the output of this function:

select period_diff(20101202,20101001); -> difference( year 201012 - month 02, year 201010 - month 01) = 2 years + 1month = 25 months

same for select period_diff(20101201123456,20101001123456);
201012011234-56 to 201010011234-56 is 2000000 years = 24000000 months

so exactly what you got out.

You can argue that 201012011234-56 is invalid date but it's just
201012011234 years + 56 months no law forbid you to write date like that

kind regards
Bogdan Kecman