dml: insert constant into json column should report some errors

[AilinKid]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE `tbl_1` (
  `col_5` json NOT NULL,
  `col_25` json DEFAULT NULL,
  `col_7` json DEFAULT NULL,
  `col_8` json NOT NULL,
  `col_9` json NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin 

insert into tbl_1 values(1, "\"a\"" ,false  , 1 ,77);

MySQL8.0

ERROR 3140 (22032): Invalid JSON text: "not a JSON text, may need CAST" at position 0 in value for column 'tbl_1.col_5'.

TiDB

mysql> insert into tbl_1 values(1, "\"a\"" ,false  , 1 ,77);
Query OK, 1 row affected (0.00 sec)

2. What did you expect to see? (Required)

should it be the same?

3. What did you see instead (Required)

as you see above

4. What is your TiDB version? (Required)

master

[bb7133]

I changed the SIG to 'sig/execution' since it is related to JSON.

[windtalker]

In MySQL, when the target type is not the same as the input type, MySQL usually will implicitly convert the input to target type(both for insert into table and function call), but for Json type, it explicitly defines that only some types can be implicit convert to Json. In TiDB, it allows implicitly convert between all types. I think it is kind of unreasonable for MySQL's special restriction for Json type, I'm not sure if we need to follow MySQL's behavior @zanmato1984 ?

[zanmato1984]

In MySQL, when the target type is not the same as the input type, MySQL usually will implicitly convert the input to target type(both for insert into table and function call), but for Json type, it explicitly defines that only some types can be implicit convert to Json. In TiDB, it allows implicitly convert between all types. I think it is kind of unreasonable for MySQL's special restriction for Json type, I'm not sure if we need to follow MySQL's behavior @zanmato1984 ?

Yeah this makes sense. Besides, json is not fully GA in TiDB, so I don't think we'll cover as much details as this issue. I'm closing it temporarily until we have done more investigation.