Re: [I] The `yearIsDateType=false` not effect at ShardingSphere-Proxy + MySQL [shardingsphere]

["terrymanu (via GitHub)" <gi...@apache.org>]

terrymanu commented on issue #28858:
URL: https://github.com/apache/shardingsphere/issues/28858#issuecomment-3642523219

   ##  Problem Understanding
   
     - Using MySQL Connector/J 8 against ShardingSphere-Proxy, selecting a YEAR column returns java.sql.Date even when yearIsDateType=false, instead of Short as expected.
   
   ##  Root Cause
   
     - Proxy reuses backend JDBC metadata columnType for result metadata. The backend reports YEAR as Types.DATE, and ResponsePacketBuilder maps that to MySQL protocol type DATE (0x0a) instead of YEAR (0x0d). The driver therefore never applies the yearIsDateType behavior.
   
   ##  Analysis
   
     - Native MySQL marks YEAR columns as protocol type YEAR (0x0d), which lets Connector/J honor yearIsDateType=false and return Short (official doc for the property: https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-configuration-properties.html (https://
       dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-configuration-properties.html)).
     - In Proxy, MySQLQueryHeaderBuilder passes the JDBC columnType (seen as Types.DATE) to ResponsePacketBuilder; MySQLBinaryColumnType.valueOfJDBCType has no YEAR branch, so it always uses DATE. This protocol mismatch prevents the driver’s YEAR handling.
   
   ##  Conclusion / Fix Suggestion
   
     - This is a Proxy type-mapping gap. When building column definitions, detect YEAR (e.g., via columnTypeName == "YEAR" or an explicit YEAR metadata check) and map it to MySQL protocol type YEAR (0x0d) rather than DATE. That aligns Proxy with native MySQL and lets
       yearIsDateType=false work.
     - Temporary workaround: CAST(t_year AS UNSIGNED) in SQL or use getShort on the client, but the correct fix is the mapping change.
     - We warmly welcome a PR to implement and test this mapping so behavior matches native MySQL. Happy to help review or validate!


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org