Incompatible with MySQL 8.0 about the CAST string to JSON

[lonng]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?
   If possible, provide a recipe for reproducing the error.

➜  ~ tidiff 'select cast("2018-01-02 01:01:01.111111" as json)'
MySQL(127.0.0.1:3306)> select cast("2018-01-02 01:01:01.111111" as json)
Error 3141: Invalid JSON text in argument 1 to function cast_as_json: "The document root must not be followed by other values." at position 4.

TiDB(127.0.0.1:4000)> select cast("2018-01-02 01:01:01.111111" as json)
+--------------------------------------------+
| cast("2018-01-02 01:01:01.111111" as json) |
+--------------------------------------------+
| 2018                                       |
+--------------------------------------------+
1 row in set (0.003 sec)

2. What did you expect to see?

The error should be produced.

3. What did you see instead?

Returns an unexpected result.

4. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?

TiDB(127.0.0.1:4000)> select tidb_version()
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                                                            |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v2.1.8-24-g7b36cdfb6-dirty
Git Commit Hash: 7b36cdfb6e8a4c279fc360ed79cf1c499b553688
Git Branch: failpoint-2.1
UTC Build Time: 2019-04-30 08:26:17
GoVersion: go version go1.12 darwin/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.003 sec)

[wjhuang2016]

Now， the master：

mysql> select cast("2018-01-02 01:01:01.111111" as json);
ERROR 3140 (22032): Invalid JSON text: The document root must not be followed by other values.

[ghost]

I have verified this against master. TiDB now correctly returns an error with the correct code. It is only the message that differs:

select cast("2018-01-02 01:01:01.111111" as json);

..

mysql> select cast("2018-01-02 01:01:01.111111" as json);
ERROR 3140 (22032): Invalid JSON text: The document root must not be followed by other values.

mysql> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-771-gca41972fb
Edition: Community
Git Commit Hash: ca41972fbac068c8a5de107d9075f09ac68842ac
Git Branch: master
UTC Build Time: 2020-07-14 02:41:21
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)


vs MySQL:

mysql [localhost:5731] {msandbox} (test) > select cast("2018-01-02 01:01:01.111111" as json);
ERROR 3141 (22032): Invalid JSON text in argument 1 to function cast_as_json: "The document root must not be followed by other values." at position 4.

[lonng]

@nullnotnil Then, can we close this issue?

[ghost]

I agree. Message parity is less important here because it gets very specific about the json parser.

We can re-open if it is discovered applications depend on it.

[HimanshuChugh2]

i am saving json value which includes base64 text of 3mn characters, i am getting the below error while inserting in mysql.

The document root must not be followed by other values." at position 2725979 in value for column 'create_content.data'.

this is how my table looks like

the base64 text is an image.

how can this error be resolved ?

[ghost]

@HimanshuChugh2 can you provide an example INSERT statement which fails? I would recommend creating a new issue, but I would be happy to take a look at it for you.

[HimanshuChugh2]

Hey buddy @nullnotnil , thanks for your response, i have created a new issue, please take a look and help me,
Thank you

issue link: #18922 @nullnotnil