ddl: column type change will use default value as converted one for a invalid old json string

[AilinKid]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

create table t(a json);
insert into t values("null");
alter table t modify column a int unsigned default 1910128469

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

MySQL

ERROR 1366 (HY000): Incorrect integer value: 'null' for column 'a' at row 1

3. What did you see instead (Required)

TiDB

Query OK, 0 rows affected (2.74 sec)

mysql> select * from t;
+------+
| a    |
+------+
|    0 |
+------+
1 row in set (0.01 sec)

4. What is your TiDB version? (Required)

master

[sylzd]

/assign

[sylzd]

Ref: https://dev.mysql.com/doc/refman/5.7/en/json-attribute-functions.html

JSON_TYPE() returns NULL if the argument is NULL:
JSON_VALID(val) Returns 0 or 1 to indicate whether a value is valid JSON. Returns NULL if the argument is NULL.

that's weird. I think it's a bug both exits in mysql and tidb., maybe we should not adapt it until mysql fix it. In my opinion, It's the same thing if we insert values('null') and insert values(null);

But the result of 'null' value is conflicted.

mysql> select json_valid('null'), json_valid(null), json_valid('"null"'), json_type('null'), json_type(null), json_type('"null"');
+--------------------+------------------+----------------------+-------------------+-----------------+---------------------+
| json_valid('null') | json_valid(null) | json_valid('"null"') | json_type('null') | json_type(null) | json_type('"null"') |
+--------------------+------------------+----------------------+-------------------+-----------------+---------------------+
|                  1 |             NULL |                    1 | NULL              | NULL            | STRING              |
+--------------------+------------------+----------------------+-------------------+-----------------+---------------------+
1 row in set (0.00 sec)

mysql>select isnull('null'), isnull(null), isnull('"null"');
+----------------+--------------+------------------+
| isnull('null') | isnull(null) | isnull('"null"') |
+----------------+--------------+------------------+
|              0 |            1 |                0 |
+----------------+--------------+------------------+
1 row in set (0.00 sec)

Given all that, I think we can keep it until it becomes more clear. @AilinKid @djshow832

[djshow832]

I think it still makes sense for MySQL.
There is a difference between insert values('null') and insert values(null). 'null' is a JSON literal null, but null is a NULL type.

mysql> create table t(j json);
Query OK, 0 rows affected (0.07 sec)
mysql> insert t value('null'), (NULL);

Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select isnull(j) from t;
+-----------+
| isnull(j) |
+-----------+
|         0 |
|         1 |
+-----------+
2 rows in set (0.00 sec)

json_valid('null'), json_valid(null), json_type('null'), and json_type(null) also make sense. 'null' and null are both valid, json_type can only return several specific types and only NULL suits the result.

MySQL and TiDB act the same for the above statements, but here is the difference:

In MySQL:

mysql> select cast(j as unsigned) from t;
+---------------------+
| cast(j as unsigned) |
+---------------------+
|                   0 |
|                NULL |
+---------------------+
2 rows in set, 1 warning (0.01 sec)

mysql> show warnings;
+---------+------+---------------------------------------------------------------+
| Level   | Code | Message                                                       |
+---------+------+---------------------------------------------------------------+
| Warning | 3156 | Invalid JSON value for CAST to INTEGER from column j at row 1 |
+---------+------+---------------------------------------------------------------+
1 row in set (0.00 sec)

In TiDB:

mysql> select cast(j as unsigned) from t;
+---------------------+
| cast(j as unsigned) |
+---------------------+
|                   0 |
|                NULL |
+---------------------+
2 rows in set (0.01 sec)

TiDB casts the value 'null' to unsigned without error but MySQL actually fails.

[github-actions]

Please check whether the issue should be labeled with 'affects-x.y' or 'fixes-x.y.z', and then remove 'needs-more-info' label.