There is a problem in libmysqld/libmysql.c in the function fetch_long_with_conversion(), in this fragment:

  case MYSQL_TYPE_TIME:

  case MYSQL_TYPE_DATE:

  case MYSQL_TYPE_TIMESTAMP:

  case MYSQL_TYPE_DATETIME:

  {

    int error;

    value= number_to_datetime(value, 0, (MYSQL_TIME *) buffer, 0, &error);

    *param->error= MY_TEST(error);

    break;

  }

Note, for MYSQL_TYPE_TIME it should call number_to_time() instead of number_to_datetime().

Note, on the contrary, fetch_string_with_conversion() is OK: it correctly calls str_to_time() for MYSQL_TYPE_TIME, while str_to_datetime() for DATE, TIMESTAMP and DATETIME

The attached program demonstrates the problem: it binds expressions of various data types to MYSQL_TYPE_TIME, and it returns the following results:

Program output (wrong)

SELECT TIME'800:33:44' -> TIME = TIME      0000-00-00 800:33:44.000000

SELECT 1010            -> TIME = DATE      2000-10-10 00:00:00.000000

SELECT 20101010        -> TIME = DATE      2010-10-10 00:00:00.000000

SELECT 20101010223344  -> TIME = DATETIME  2010-10-10 22:33:44.000000

SELECT 8003344         -> TIME             mysql_stmt_fetch() failed: error=''

SELECT 8001112         -> TIME             mysql_stmt_fetch() failed: error=''

SELECT 10001112        -> TIME = DATE      1000-11-12 00:00:00.000000

One would expect that data type conversion will happen in the same way with what happens on the server side with CAST(AS TIME).

Server-side conversion with CAST (correct)

SELECT

CAST(TIME'800:33:44' AS TIME),

CAST(1010 AS TIME),

CAST(20101010 AS TIME),

CAST(20101010223344 AS TIME),

CAST(8003344 AS TIME),

CAST(8001112 AS TIME),

CAST(10001112 AS TIME)\G

CAST(TIME'800:33:44' AS TIME): 800:33:44

CAST(1010 AS TIME): 00:10:10

CAST(20101010 AS TIME): 00:00:00

CAST(20101010223344 AS TIME): 22:33:44

CAST(8003344 AS TIME): 800:33:44

CAST(8001112 AS TIME): 800:11:12

CAST(10001112 AS TIME): 00:00:00

However this is not the case.

Observations on totally wrong results in the program output:

• Conversion from 1010 to TIME returns DATETIME '2000-10-10 00:00:00.000000', which looks wrong. The expected result is TIME'00:10:10', i.e what CAST(1010 AS TIME) does.
• Conversion from 8003344 returns an error. The expected result is TIME'800:33:44'.
• Conversion from 8001112 returns an error. The expected result is TIME'800:11:12'.

Observations on less fatal but still unpleasant results in the program output:

• Conversion from 20101010 returns a DATE. The expected result is zero time. No conversion from DATE to TIME happened.
• Conversion from 20101010223344 returns a full datetime. The expected result is TIME'22:33:44'. No conversion from DATETIME to TIME happened.
• Conversion from 10001112 returns a DATE. The expected result is zero time. No conversion from DATE to TIME happened.