In the test cases in TiDB, MySQL and TiDB behave differently, whichever prevails

[coolboydan]

General Question

In the test case util/admin/admin_integration_test.go:59

create table t1 (
		a int             as (JSON_EXTRACT(k,'$.a')),
		c double          as (JSON_EXTRACT(k,'$.c')),
		d decimal(20,10)  as (JSON_EXTRACT(k,'$.d')),
		e char(10)        as (JSON_EXTRACT(k,'$.e')),
		f date            as (JSON_EXTRACT(k,'$.f')),
		g time            as (JSON_EXTRACT(k,'$.g')),
		h datetime        as (JSON_EXTRACT(k,'$.h')),
		i timestamp       as (JSON_EXTRACT(k,'$.i')),
		j year            as (JSON_EXTRACT(k,'$.j')),
		k json);

insert into t1 set k='{"a": 100,"c":1.234,"d":1.2340000000,"e":"abcdefg","f":"2018-09-28","g":"12:59:59","h":"2018-09-28 12:59:59","i":"2018-09-28 16:40:33","j":"2018"}';

In TiDB it can write normally。

When executed in MySQL

mysql> insert into t1 set k='{"a": 100,"c":1.234,"d":1.2340000000,"e":"abcdefg","f":"2018-09-28","g":"12:59:59","h":"2018-09-28 12:59:59","i":"2018-09-28 16:40:33","j":"2018"}';
ERROR 3156 (22018): Invalid JSON value for CAST to DATE/TIME/DATETIME/TIMESTAMP from column json_extract at row 1

Which shall prevail

[xiongjiwei]

maybe same root cause #25329

[YangKeao]

Same problem with #9988. In MySQL:

bool Json_wrapper::coerce_time(MYSQL_TIME *ltime,
                               const char *msgnam) const
{
  switch (type())
  {
  case Json_dom::J_DATETIME:
  case Json_dom::J_DATE:
  case Json_dom::J_TIME:
  case Json_dom::J_TIMESTAMP:
    set_zero_time(ltime, MYSQL_TIMESTAMP_DATETIME);
    get_datetime(ltime);
    return false;
  default:
    push_json_coercion_warning("DATE/TIME/DATETIME/TIMESTAMP",
                               ER_INVALID_JSON_VALUE_FOR_CAST, msgnam);
    return true;
  }
}

We cannot cast a json string value to DATE/TIME/DATETIME/TIMESTAMP.