Bug Related to the Combination of 【CASE WHEN】 and 【JSON_EXTRACT】

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 关于【CASE WHEN】与【JSON_EXTRACT】结合使用的bug

| username: Peng

[TiDB Usage Environment] Production Environment
[TiDB Version] v6.5
[Reproduction Path] Execute the following SQL, and the type of ‘a’ in the returned result is problematic. It should return 0, but the value retrieved by Java is [48]. The returned values of ‘b’, ‘c’, and ‘d’ are fine. This SQL executes without issues in MySQL, but only has problems when executed in TiDB.

[Encountered Problem: Phenomenon and Impact]
See screenshot for details
TiDB Issue Screenshot.zip (225.5 KB)
Problematic SQL attachment:
Problematic SQL.sql (822 bytes)

[Reproduction Path 2] After multiple verifications, it is unrelated to IFNULL. See the “Problematic SQL2” attachment for details. This SQL executes without issues in the MySQL environment, but only has problems when executed in TiDB.
Problematic SQL2.sql (590 bytes)

| username: tidb菜鸟一只 | Original post link

I didn’t quite understand. Isn’t the type returned by case when generally determined by the type of your then clause?

| username: Peng | Original post link

You can take a look at the comments in my SQL. There is only an issue when using a certain table as a condition after “case when,” which means there is a problem with “a.” However, “b,” “c,” and “d” are all fine.

| username: tidb菜鸟一只 | Original post link

What is the type of the field JSON_EXTRACT(‘{}’, ‘$.KEY’)?

| username: Peng | Original post link

Executing select JSON_EXTRACT('{}', '$.KEY'); alone returns a type of longtext.

I tried a few more times, and it is not related to IFNULL. In the following SQL, the type of e has an issue, but f does not.

select
    t.v,

    CASE
        WHEN t.v > 2 THEN 1
        ELSE JSON_EXTRACT('{"KEY":0}', '$.KEY')
    END AS e,

    CASE
        WHEN 1 > 2 THEN 1
        ELSE JSON_EXTRACT('{"KEY":0}', '$.KEY')
    END AS f

from (select 1 as v) t;
| username: yiduoyunQ | Original post link

The results of testing TiDB v6.5.3 and MySQL 5.7.42 are consistent.

| username: Peng | Original post link

Thank you for the reply.

If we only look at the values returned by the SQL, they are indeed all 0. However, if we use a visual client tool to check the SQL execution results, we can see that the return types are different. The return types of ‘a’ in “problematic SQL” and ‘e’ in “problematic SQL2” are incorrect, causing Java to retrieve values with square brackets [48] (which is a byte array). On the other hand, the returned values of b, c, d, f, and g are normal, all being 0.

This issue only occurs in TiDB 6.5, while the same SQL executed in MySQL does not have this problem. When Java connects to TiDB, the results for ‘a’ and ‘e’ are [48], while b, c, d, f, and g are all 0. When Java connects to MySQL, the results for a~g are all 0.

| username: tidb菜鸟一只 | Original post link

I understand your point. In TiDB, for the same case when else JSON function statement, if the condition in the when clause involves a table field, the returned character type is different from when a constant is used. This is indeed an issue. I tested it on TiDB 5.4.3 and encountered the same problem.

| username: Peng | Original post link

Yes, indeed, there is a problem.

| username: Peng | Original post link

How can I provide feedback on this issue to the official development team?

| username: Miracle | Original post link

You can open an issue on GitHub.

| username: tidb菜鸟一只 | Original post link

You can raise an issue on GitHub or provide feedback in the forum.

| username: Billmay表妹 | Original post link

I’ll directly move this post to the product defect category~

| username: Peng | Original post link

Thank you for your attention. I just submitted some feedback: 【CASE WHEN】与【JSON_EXTRACT】结合使用的bug - TiDB 的问答社区

| username: Billmay表妹 | Original post link

Got it, thanks for your hard work!

| username: Peng | Original post link

I provided feedback, but it’s been a day and there’s still no response. :sob:

| username: aytrack | Original post link

May I know the specific version of TiDB? I have tried all versions from 6.5.0 to 6.5.5. In mysql --column-type-info, the data types for a, c, d, e, and f are all displayed as LONG_BLOB, but when queried through DataGrip, their data types are displayed as LONG_TEXT.
Additionally, could you check which version of the mysql-java-connector driver your visualization tool is using? Please try a few different versions to see if it works correctly.

select
    t.v,

    # The type of a is incorrect (longblob) when using a table condition after CASE WHEN
    CASE
        WHEN t.v > 2 THEN 1
        ELSE IFNULL(JSON_EXTRACT('{}', '$.KEY'), 0)
        END AS a,

    # Adding CONVERT, everything else is the same as a, the type of b is correct (decimal)
    CASE
        WHEN t.v > 2 THEN 1
        ELSE IFNULL(CONVERT(JSON_EXTRACT('{}', '$.KEY'), unsigned ), 0)
        END AS b,

    # Not using a table condition after CASE WHEN, everything else is the same as a, the type of c is correct (longtext)
    CASE
        WHEN 1 > 2 THEN 1
        ELSE IFNULL(JSON_EXTRACT('{}', '$.KEY'), 0)
        END AS c,

    # Only keeping IFNULL JSON_EXTRACT, the type of d is correct (longtext)
    IFNULL(JSON_EXTRACT('{}', '$.KEY'), 0) AS d,

    # Using a table condition after CASE WHEN, the type of e is incorrect (longblob)
    CASE
        WHEN t.v > 2 THEN 1
        ELSE JSON_EXTRACT('{"KEY": 0}', '$.KEY')
        END AS e,

    # Changing CASE WHEN to IF, everything else is the same as e, the type of f is correct (longtext)
    IF(t.v > 2, 1, JSON_EXTRACT('{"KEY": 0}', '$.KEY'))
            AS f,

    # Not using a table condition after CASE WHEN, everything else is the same as e, the type of g is correct (text)
    CASE
        WHEN 1 > 2 THEN 1
        ELSE JSON_EXTRACT('{"KEY": 0}', '$.KEY')
        END AS g

from (select 1 as v) t
;
Field   1:  `v`
Catalog:    `def`
Database:   `test`
Table:      `t`
Org_table:  ``
Type:       LONGLONG
Collation:  binary (63)
Length:     1
Max_length: 1
Decimals:   0
Flags:      NOT_NULL BINARY NUM

Field   2:  `a`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONG_BLOB
Collation:  utf8mb4_bin (46)
Length:     67108864
Max_length: 1
Decimals:   31
Flags:      BINARY

Field   3:  `b`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       NEWDECIMAL
Collation:  binary (63)
Length:     24
Max_length: 1
Decimals:   0
Flags:      BINARY NUM

Field   4:  `c`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONG_BLOB
Collation:  utf8mb4_bin (46)
Length:     67108864
Max_length: 1
Decimals:   31
Flags:      NOT_NULL

Field   5:  `d`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONG_BLOB
Collation:  utf8mb4_bin (46)
Length:     67108864
Max_length: 1
Decimals:   31
Flags:      NOT_NULL

Field   6:  `e`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONG_BLOB
Collation:  utf8mb4_bin (46)
Length:     67108864
Max_length: 1
Decimals:   31
Flags:      BINARY

Field   7:  `f`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONG_BLOB
Collation:  utf8mb4_bin (46)
Length:     67108864
Max_length: 1
Decimals:   31
Flags:

Field   8:  `g`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  utf8mb4_bin (46)
Length:     67108864
Max_length: 1
Decimals:   31
Flags:


+---+------+------+---+---+------+------+------+
| v | a    | b    | c | d | e    | f    | g    |
+---+------+------+---+---+------+------+------+
| 1 | 0    |    0 | 0 | 0 | 0    | 0    | 0    |
+---+------+------+---+---+------+------+------+
1 row in set (0.00 sec)
| username: Peng | Original post link

The TiDB version is v6.5.2, and the mysql-connector-java driver version is 5.1.47. After changing the driver to 8.0.25, the types of a and e that had issues before have become longtext. However, in our Java project, the mysql-connector-java driver version is 5.1.44. After connecting to TiDB and executing SQL, the types of a and e retrieved by Java are byte, whereas there is no issue when executing the same SQL with MySQL.
So the problem lies in the fact that under the same environment, there is no issue with MySQL, but there is an issue with TiDB.

| username: aytrack | Original post link

Opened an issue: case when with json_extract not compatible with mysql-connector-java driver 5.1 · Issue #48004 · pingcap/tidb · GitHub. Follow up here.

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.