MySQL Bugs: #112435: YEAR scalar function does not return YEAR portion of a temporal type in MySQL 8.

Bug #112435	YEAR scalar function does not return YEAR portion of a temporal type in MySQL 8.
Submitted:	22 Sep 2023 13:49	Modified:	1 Nov 2023 23:24
Reporter:	N Campbell	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	8.1	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The YEAR scalar function is no returning expected integer values compared to prior version of MySQL such as 8.0.

EXTRACT function works.

How to repeat:

Using MySQL JDBC driver and a Java application such as SQLSquirrel

select year ( date '2023-01-01' ) from `dbcert`.`tversion`
does not return 2023 when MySQL Server is 8.1.0

same scenario with MySQL 8.0 returns 2023

Using Extract ( YEAR from ... ) returns 2023 in MySQL 8.1 and 8.0 as expected.

Hello N Campbell,

Thank you for the report and feedback.

regards,
Umesh

This is not a bug. What's going on is that the data type of value returned by the function YEAR() was fixed starting from MySQL 8.1.0. While before those values were returned as LONG, they are now returned as YEAR. You can see this by starting your mysql client with the option --column-type-info and compare results. For example, MySQL 8.0.35 vs 8.2.0 yields these results:

MySQL(8.0.35)> select year('2023-05-08');
Field   1:  `year('2023-05-08')`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONGLONG
Collation:  binary (63)
Length:     5
Max_length: 4
Decimals:   0
Flags:      BINARY NUM 

+--------------------+
| year('2023-05-08') |
+--------------------+
|               2023 |
+--------------------+
1 row in set (0,00 sec)

MySQL(8.2.0)> select year('2023-05-08');
Field   1:  `year('2023-05-08')`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       YEAR
Collation:  binary (63)
Length:     4
Max_length: 4
Decimals:   0
Flags:      UNSIGNED BINARY NUM 

+--------------------+
| year('2023-05-08') |
+--------------------+
|               2023 |
+--------------------+
1 row in set (0,00 sec)

Now, the second part of the issue is being caused by Connector/J itself. Since there is no natural mapping between MySQL YEAR type and a Java type, historically Connector/J mapped those to java.sql.Date, by adding day 1 and month 1 to them. Some time later we realized this doesn't work for everybody so we introduced the connection option 'yearIsDateType'. Therefore, set it to "true" (the default) and you get these values converted to Date, as "2023-01-01"; set it to "false" and you get them converted to Java short, as  "2023". This is the expected behavior.

Couple of comments. 

The documentation on YEAR scalar lacks detail. Specifically, it does not state explicitly a YEAR type. 

Returns the year for date, in the range 1000 to 9999, or 0 for the “zero” date. 

https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_year
https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_year

Most applications trying to obtain the year portion of a temporal type invariably would be expecting the integral value not a year type, as EXTRACT will return.

The docs should be improved IMHO to make it clearer re the change, the return type and perhaps suggesting when persons should use EXTRACT etc.