Executing Join Queries with Special Characters Throws Exception, Works Smoothly in MySQL 8

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

Original topic: 执行关联查询带特殊符号抛出异常,mysql8 顺利执行

| username: TiDBer_DBIXWVOj

[TiDB Usage Environment] Production Environment / Testing / POC
[TiDB Version]
[Reproduction Path] What operations were performed that led to the issue
[Encountered Issue: Issue Phenomenon and Impact]
[Resource Configuration] Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
[Attachments: Screenshots/Logs/Monitoring]

| username: 大飞哥online | Original post link

Your eyes must be tired from looking at this. Could you send the SQL separately?

| username: TiDBer_DBIXWVOj | Original post link

SELECT tnr.org_name AS org_name, tnr.org_id_path AS orgPath, tnr.vehicle_model_id AS vehicleModelId, aa.vin, aa.did, aa.id, aa.CODE, aa.NAME, aa.type, aa.LEVEL, aa.start_lon AS startLon, aa.start_lat AS startLat, aa.address AS address, aa.end_lon AS endLon, aa.end_lat AS endLat, aa.start_time AS startTime, aa.end_time AS endTime 
FROM agri_alarm_nearly_180_days aa 
LEFT JOIN tw_nrv_redundant tnr ON aa.vin = tnr.vin AND tnr.tenant_id = 108 
JOIN (
    SELECT DISTINCT (vin) AS vin 
    FROM (
        SELECT DISTINCT (vin) AS vin 
        FROM tb_vehicle vehicle 
        WHERE vehicle.del_flag = 0 AND vehicle.org_id IN (
            SELECT DISTINCT (t.orgId) 
            FROM (
                SELECT org.id AS orgId 
                FROM tb_org org 
                WHERE org.del_flag = 0 AND org.org_id_path LIKE concat(
                    (SELECT substring_index(org.org_id_path, "#", 1) AS orgId 
                    FROM tb_user tu 
                    INNER JOIN tb_org org ON tu.org_id = org.id AND org.del_flag = 0 
                    INNER JOIN tr_user_role tur ON tu.id = tur.user_id AND tur.del_flag = 0 AND tu.id = 108 
                    INNER JOIN tb_role tr ON tur.role_id = tr.id AND tr.del_flag = 0 AND tr.data_permission_type = 0), 
                    '#%'
                ) AND org.tenant_id = 108 
                UNION ALL 
                SELECT org.id AS orgId 
                FROM tb_org org 
                WHERE org.del_flag = 0 AND org.org_id_path LIKE concat(
                    (SELECT DISTINCT org.org_id_path AS orgIdPath 
                    FROM tb_user tu 
                    INNER JOIN tb_org org ON tu.org_id = org.id AND org.del_flag = 0 
                    INNER JOIN tr_user_role tur ON tu.id = tur.user_id AND tur.del_flag = 0 AND tu.id = 108 
                    INNER JOIN tb_role tr ON tur.role_id = tr.id AND tr.del_flag = 0 AND tr.data_permission_type = 1), 
                    '%'
                ) AND org.tenant_id = 108 
                UNION ALL 
                SELECT tu.org_id AS orgId 
                FROM tb_user tu 
                INNER JOIN tr_user_role tur ON tu.id = tur.user_id AND tur.del_flag = 0 AND tu.id = 108 AND tu.tenant_id = 108 AND tur.tenant_id = 108 
                INNER JOIN tb_role tr ON tur.role_id = tr.id AND tr.del_flag = 0 AND tr.data_permission_type = 2 AND tr.tenant_id = 108 
                UNION ALL 
                SELECT DISTINCT (org.id) AS orgId 
                FROM tb_user tu 
                INNER JOIN tr_user_role tur ON tu.id = tur.user_id AND tur.del_flag = 0 AND tu.id = 108 AND tu.tenant_id = 108 AND tur.tenant_id = 108 
                INNER JOIN tb_role tr ON tur.role_id = tr.id AND tr.del_flag = 0 AND tr.data_permission_type = 3 AND tr.tenant_id = 108 
                INNER JOIN tr_role_org tro ON tr.id = tro.role_id AND tro.del_flag = 0 AND tro.selected_flag = 1 AND tro.tenant_id = 108 
                INNER JOIN tb_org org ON tro.assign_org_id = org.id AND org.del_flag = 0 AND org.tenant_id = 108
            ) t 
            WHERE t.orgId IS NOT NULL
        ) AND vehicle.tenant_id = 108 
        UNION ALL 
        SELECT tv.vin AS vin 
        FROM tb_user tu 
        INNER JOIN tr_user_role tur ON tu.id = tur.user_id AND tur.del_flag = 0 AND tu.id = 108 AND tu.tenant_id = 108 AND tur.tenant_id = 108 
        INNER JOIN tb_role tr ON tur.role_id = tr.id AND tr.del_flag = 0 AND tr.data_permission_type = 4 AND tr.tenant_id = 108 
        INNER JOIN tr_role_vehicle trv ON trv.role_id = tr.id AND trv.del_flag = 0 AND trv.tenant_id = 108 
        INNER JOIN tb_vehicle tv ON trv.assign_vehicle_id = tv.id AND tv.del_flag = 0 AND tv.tenant_id = 108
    ) v 
    WHERE v.vin IS NOT NULL AND v.vin != ''
) dv ON tnr.vin = dv.vin 
WHERE aa.CODE != 'EC00000000O17000101' AND aa.end_time IS NOT NULL AND tnr.org_id_path LIKE concat(108, '%') 
UNION ALL 
SELECT tnr.org_name AS org_name, tnr.org_id_path AS orgPath, tnr.vehicle_model_id AS vehicleModelId, bb.vin, bb.did, bb.id, bb.CODE, bb.NAME, bb.type, bb.LEVEL, bb.start_lon AS startLon, bb.start_lat AS startLat, bb.address AS address, bb.end_lon AS endLon, bb.end_lat AS endLat, bb.start_time AS startTime, bb.end_time AS endTime 
FROM agri_alarm bb 
LEFT JOIN tw_nrv_redundant tnr ON bb.vin = tnr.vin AND tnr.tenant_id = 108 
JOIN (
    SELECT DISTINCT (vin) AS vin 
    FROM (
        SELECT DISTINCT (vin) AS vin 
        FROM tb_vehicle vehicle 
        WHERE vehicle.del_flag = 0 AND vehicle.org_id IN (
            SELECT DISTINCT (t.orgId) 
            FROM (
                SELECT org.id AS orgId 
                FROM tb_org org 
                WHERE org.del_flag = 0 AND org.org_id_path LIKE concat(
                    (SELECT substring_index(org.org_id_path, "#", 1) AS orgId 
                    FROM tb_user tu 
                    INNER JOIN tb_org org ON tu.org_id = org.id AND org.del_flag = 0 
                    INNER JOIN tr_user_role tur ON tu.id = tur.user_id AND tur.del_flag = 0 AND tu.id = 108 
                    INNER JOIN tb_role tr ON tur.role_id = tr.id AND tr.del_flag = 0 AND tr.data_permission_type = 0), 
                    '#%'
                ) AND org.tenant_id = 108 
                UNION ALL 
                SELECT org.id AS orgId 
                FROM tb_org org 
                WHERE org.del_flag = 0 AND org.org_id_path LIKE concat(
                    (SELECT DISTINCT org.org_id_path AS orgIdPath 
                    FROM tb_user tu 
                    INNER JOIN tb_org org ON tu.org_id = org.id AND org.del_flag = 0 
                    INNER JOIN tr_user_role tur ON tu.id = tur.user_id AND tur.del_flag = 0 AND tu.id = 108 
                    INNER JOIN tb_role tr ON tur.role_id = tr.id AND tr.del_flag = 0 AND tr.data_permission_type = 1), 
                    '%'
                ) AND org.tenant_id = 108 
                UNION ALL 
                SELECT tu.org_id AS orgId 
                FROM tb_user tu 
                INNER JOIN tr_user_role tur ON tu.id = tur.user_id AND tur.del_flag = 0 AND tu.id = 108 AND tu.tenant_id = 108 AND tur.tenant_id = 108 
                INNER JOIN tb_role tr ON tur.role_id = tr.id AND tr.del_flag = 0 AND tr.data_permission_type = 2 AND tr.tenant_id = 108 
                UNION ALL 
                SELECT DISTINCT (org.id) AS orgId 
                FROM tb_user tu 
                INNER JOIN tr_user_role tur ON tu.id = tur.user_id AND tur.del_flag = 0 AND tu.id = 108 AND tu.tenant_id = 108 AND tur.tenant_id = 108 
                INNER JOIN tb_role tr ON tur.role_id = tr.id AND tr.del_flag = 0 AND tr.data_permission_type = 3 AND tr.tenant_id = 108 
                INNER JOIN tr_role_org tro ON tr.id = tro.role_id AND tro.del_flag = 0 AND tro.selected_flag = 1 AND tro.tenant_id = 108 
                INNER JOIN tb_org org ON tro.assign_org_id = org.id AND org.del_flag = 0 AND org.tenant_id = 108
            ) t 
            WHERE t.orgId IS NOT NULL
        ) AND vehicle.tenant_id = 108 
        UNION ALL 
        SELECT tv.vin AS vin 
        FROM tb_user tu 
        INNER JOIN tr_user_role tur ON tu.id = tur.user_id AND tur.del_flag = 0 AND tu.id = 108 AND tu.tenant_id = 108 AND tur.tenant_id = 108 
        INNER JOIN tb_role tr ON tur.role_id = tr.id AND tr.del_flag = 0 AND tr.data_permission_type = 4 AND tr.tenant_id = 108 
        INNER JOIN tr_role_vehicle trv ON trv.role_id = tr.id AND trv.del_flag = 0 AND trv.tenant_id = 108 
        INNER JOIN tb_vehicle tv ON trv.assign_vehicle_id = tv.id AND tv.del_flag = 0 AND tv.tenant_id = 108
    ) v 
    WHERE v.vin IS NOT NULL AND v.vin != ''
) dv ON tnr.vin = dv.vin 
WHERE bb.CODE != 'EC00000000O17000101' AND bb.end_time IS NOT NULL AND tnr.org_id_path LIKE concat(108, '%') 
ORDER BY startTime DESC 
LIMIT 50;
| username: Fly-bird | Original post link

It doesn’t support join queries, right?

| username: 大飞哥online | Original post link

Add escape characters then.

| username: TiDBer_DBIXWVOj | Original post link

I can’t use this method, the same error “#”.

| username: 大飞哥online | Original post link

That means the optimization compiler rules were not handled well. You can suggest an optimization.

| username: TiDBer_DBIXWVOj | Original post link

Okay, thank you.

| username: 大飞哥online | Original post link

Is extracting a segment of SQL that contains the # symbol also causing an error, or can it be used in a join? You can try it out to determine the problem location.

| username: TiDBer_DBIXWVOj | Original post link

Yes, I just tested it, and both DBeaver and the MySQL command line can query it.

| username: 大飞哥online | Original post link

What client were you using when you encountered the error before?

| username: zhanggame1 | Original post link

As a column name?

| username: 随缘天空 | Original post link

Possible Causes and Solutions

  1. Syntax Differences: TiDB and MySQL 8 may have differences in certain syntax details. Ensure that the query statements conform to TiDB’s syntax specifications, such as the correct use of quotes, parentheses, and keywords. Special characters need to be escaped.

  2. Character Set and Collation: The default character set and collation in TiDB and MySQL 8 may differ, which can lead to discrepancies when handling specific characters or sorting. Ensure that the same character set and collation used in MySQL 8 are applied in TiDB.

  3. Data Type Incompatibility: TiDB and MySQL 8 may handle certain data types differently, such as datetime types or string types. Ensure that the data types used in the queries are compatible with TiDB.

| username: TiDBer_DBIXWVOj | Original post link

Linux console tiup client

| username: TiDBer_DBIXWVOj | Original post link

Thank you, I will look into it following this approach.

| username: WalterWj | Original post link

Does MySQL report an error? Or try using single quotes. :thinking:

| username: Billmay表妹 | Original post link

Hi, were the last few questions you replied to generated using GPT?

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

Simplify it and find out exactly which character is causing the error.

| username: TiDBer_DBIXWVOj | Original post link

No error reported, it can actually execute, it should be a TiDB session issue.

| username: Kongdom | Original post link

:joy: Looking at this format, it does seem a bit like it was generated by AI.