Using CONVERT(c.val0, SIGNED) Syntax to Resolve Issues with Different Data Types in ID Fields When Joining Two Tables

Translated
1

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

Original topic: 使用 CONVERT ( c.val0, SIGNED ) 语法 解决 两张表的关联字段 id 数据类型不同，关联出错问题

| username: TiDBer_QXegKncF

【TiDB Usage Environment】Production Environment / Testing / Poc
【TiDB Version】5.4
【Reproduction Path】SQL self-join query on the same table
【Encountered Problem: Phenomenon and Impact】
SQL as follows:

SELECT
    a.id,
    (
        SELECT
            IFNULL(sum(c.val2), 0)
        FROM
            fac_app_data_table_field_val c
        WHERE
            c.val0 = a.id
            AND c.table_id = 1969241186793349598
    ) AS r15
FROM
    fac_app_data_table_field_val a
    LEFT JOIN fac_app_data_table_field_val b ON a.id = b.val0
        AND b.table_id = 1969243196838052179
        AND b.school_id = '22'
        AND b.flg = 'Y'
        AND b.source = '1'
    LEFT JOIN fac_app_data_table_field_val e ON a.id = e.val0
        AND e.table_id = 1969241186793349598
        AND e.school_id = '22'
        AND e.flg = 'Y'
        AND e.source = '1'
    LEFT JOIN dictionary_info c ON a.val6 = c.id
        AND c.schoolid = '22'
        AND c.flg = 'Y'
    LEFT JOIN dictionary_info d ON a.val18 = d.id
        AND d.schoolid = '22'
        AND d.flg = 'Y'
    LEFT JOIN user_info ui ON a.val21 = ui.id
        AND ui.schoolid = '22'
        AND ui.flg = 'Y'
WHERE
    a.table_id = 1969243196838052093
    AND a.school_id = '22'
    AND a.flg = 'Y'
    AND a.source = '1'
    AND a.val6 IN ('中标销售合同', '服务销售合同')
    AND IF('blank' = '学校内涵式发展平台', 1 = 1, a.val3 LIKE concat('%', '学校内涵式发展平台', '%'))
GROUP BY
    a.id

Problem: If c.val0 = a.id is modified to CONVERT(c.val0, SIGNED) = a.id, then record 1 can be correct, but record 2 will have an error. If it is changed back to c.val0 = a.id, then record 2 can be correct, but record 1 will have an error.

id is of bigint type, val0 is of varchar type.

【Resource Configuration】Enter TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
【Attachments: Screenshots / Logs / Monitoring】

2
| username: dba远航 | Original post link

The syntax at the end is too complex, and I suspect the parser won’t pass. Try to keep the syntax simple and readable.

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

Can converting with CAST(t1.id AS CHAR) be associated?

4
| username: zhaokede | Original post link

The id is of bigint type and val0 is of varchar type. It definitely needs to be converted to char type, as this is much safer. Since val0 has no restrictions, it may contain very large numbers or characters that cannot be converted, leading to SQL errors.

5
| username: WinterLiu | Original post link

I haven’t used this advanced usage.

6
| username: zhaokede | Original post link

I couldn’t find whether the length range of SIGNED is 4 bytes or 8 bytes.

7
| username: 小龙虾爱大龙虾 | Original post link

The fundamental issue is that there is a problem with your table model design. Implicit conversion may lead to unexpected results. 避免隐式类型转换 | PingCAP 文档中心
Post the data and let’s take a look; your description is unclear.

8
| username: kkpeter | Original post link

Yes, it is best to solve this fundamentally, otherwise, trying to fix one issue with another will eventually lead to bugs.

9
| username: system | Original post link

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