执行关联查询带特殊符号抛出异常，mysql8 顺利执行

TiDBer_DBIXWVOj · 2023 年10 月 20 日 03:19

【 TiDB 使用环境】生产环境 /测试/ Poc
【 TiDB 版本】
【复现路径】做过哪些操作出现的问题
【遇到的问题：问题现象及影响】
【资源配置】进入到 TiDB Dashboard -集群信息 (Cluster Info) -主机(Hosts) 截图此页面
【附件：截图/日志/监控】

大飞哥online · 2023 年10 月 20 日 03:21

你这看的眼都花了，sql单独发下吧

TiDBer_DBIXWVOj · 2023 年10 月 20 日 03:26

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
my:root@192.168.11.181:4000=> 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_i
my:root@192.168.11.181:4000=> 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;

Fly-bird · 2023 年10 月 20 日 03:26

不支持关联查询吧

大飞哥online · 2023 年10 月 20 日 03:29

带上转义符呢

TiDBer_DBIXWVOj · 2023 年10 月 20 日 03:50

我用这种不行呢，同样错误 “#”

大飞哥online · 2023 年10 月 20 日 04:01

那就是优化编译器那规则没处理好了，可以去提个优化吧

TiDBer_DBIXWVOj · 2023 年10 月 20 日 04:02

好的，谢谢

大飞哥online · 2023 年10 月 20 日 04:03

扣出来包含 # 号一段的 sql 是不是也报错，
还是用在 join 里面，可以尝试下，确定问题位置

TiDBer_DBIXWVOj · 2023 年10 月 20 日 04:17

对，刚测试dbeaver都能查，mysql命令行能查。

大飞哥online · 2023 年10 月 20 日 04:49

你之前报错，用的啥客户端

zhanggame1 · 2023 年10 月 20 日 05:19

#作为列名吗

随缘天空 · 2023 年10 月 20 日 05:39

可能原因及解决方法

语法差异：TiDB 和 MySQL 8 在某些语法细节上可能存在差异。请确保查询语句符合 TiDB 的语法规范，例如正确使用引号、括号和关键字等。特殊字符要转义
字符集和排序规则：TiDB 和 MySQL 8 默认的字符集和排序规则可能不同，这可能导致在处理特定字符或排序时的差异。请确保在 TiDB 中使用与 MySQL 8 相同的字符集和排序规则。
数据类型不兼容：TiDB 和 MySQL 8 可能对某些数据类型的处理方式不同，例如日期时间类型或字符串类型。请确保查询中使用的数据类型在 TiDB 中是兼容的。

TiDBer_DBIXWVOj · 2023 年10 月 20 日 05:46

Linux 控制台 tiup client

TiDBer_DBIXWVOj · 2023 年10 月 20 日 05:47

谢谢，我这边按这个思路去看看。

WalterWj · 2023 年10 月 20 日 06:02

MySQL 报错么？或者试试单引号。

Billmay表妹 · 2023 年10 月 20 日 06:10

hi 你最近回复的几个问题都是用 gpt 生成的吗？

tidb菜鸟一只 · 2023 年10 月 20 日 07:08

你简化一下，找到具体是哪个字符报错

TiDBer_DBIXWVOj · 2023 年10 月 21 日 01:50

不报错，其实能执行，应该是tidb 会话框问题.

Kongdom · 2023 年10 月 21 日 05:34

看这格式，确实有点像AI生成的