Bug #68531 incorrect interpret single digits in time values
Submitted: 1 Mar 2013 2:42 Modified: 4 Mar 2013 14:02
Reporter: tsubasa tanaka (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Charsets Severity:S3 (Non-critical)
Version:5.5.31 OS:Any
Assigned to: CPU Architecture:Any
Tags: sjis

[1 Mar 2013 2:42] tsubasa tanaka
Description:
Single digit value allows time type string, when it uses time part delimiter.
For example, '20:1' means '20:01'.

http://dev.mysql.com/doc/refman/5.6/en/date-and-time-literals.html

But it has incorrect interpret when character_set_connection = sjis, ucs2.

How to repeat:
create table t12 ( t time );
insert into t12 values ('20:00'),('20:01'),('20:02');

set character_set_connection = utf8;
select * from t12 where t > '20:01'; -- 20:02:00 matches.
select * from t12 where t > '20:1';  -- 20:02:00 matches too.

set character_set_connection = sjis;
select * from t12 where t > '20:01'; -- 20:02:00 matches.
select * from t12 where t > '20:1';  -- now rows match.
select * from t12 where t > cast('20:1' as time); -- 20:02:00 matches.
[4 Mar 2013 14:02] Erlend Dahl
Thank you for the bug report. Verified as described on 5.5.31 and 5.6.11. Not relevant for 5.1 since sjis is not supported there.