JSON查询bug

tidb版本5.0

查询SQL:

SELECT t1.ware_code AS `t1.ware_code`, t1.ware_name AS `t1.ware_name`, t2.generic_name AS `t1.generic_name`, t3.group_class_code AS `t3.group_class_code`, t3.group_class_name AS `t3.group_class_name`
	, ct1.class_code AS `ct1.class_code`, ct1.class_name AS `ct1.class_name`, ct2.class_code AS `ct2.class_code`, ct2.class_name AS `ct2.class_name`, ct3.class_code AS `ct3.class_code`
	, ct3.class_name AS `ct3.class_name`, ct4.class_code AS `ct4.class_code`, ct4.class_name AS `ct4.class_name`
FROM h3_ware.t_ware_group_base_info t1
	LEFT JOIN (
		SELECT modify_time, ware_platform_id, class_id, class_group_id, class_code
			, maintain_class_type, is_enable, is_delete, class_id -> '$[0]' AS classid1
			, class_id -> '$[1]' AS classid2, class_id -> '$[2]' AS classid3
			, class_id -> '$[3]' AS classid4
		FROM h3_ware.t_ware_group_class_info
		WHERE group_id = 100120512
		UNION ALL
		SELECT modify_time, ware_platform_id, class_id, class_group_id, class_code
			, maintain_class_type, is_enable, is_delete, class_id -> '$[0]' AS classid1
			, class_id -> '$[1]' AS classid2, class_id -> '$[2]' AS classid3
			, class_id -> '$[3]' AS classid4
		FROM h3_ware.t_ware_platform_class_info
	) t
	ON t1.ware_platform_id = t.ware_platform_id
	LEFT JOIN h3_ware.t_ware_platform_base_info t2 ON t.ware_platform_id = t2.ware_platform_id
	LEFT JOIN h3_orgmanager.t_class_group t3 ON t.class_group_id = t3.class_group_id
	LEFT JOIN h3_orgmanager.t_class ct1 ON classid1 = ct1.class_id
	LEFT JOIN h3_orgmanager.t_class ct2 ON classid2 = ct2.class_id
	LEFT JOIN h3_orgmanager.t_class ct3 ON classid3 = ct3.class_id
	LEFT JOIN h3_orgmanager.t_class ct4 ON classid4 = ct4.class_id
WHERE t1.group_id = 100120512
	AND (is_enable = 1
		OR is_enable IS NULL)
	AND (is_delete = 2
		OR is_delete IS NULL)
	AND t.class_group_id = '118'
	AND t.classid1 = '100002048'
	AND t1.group_id = 100120512
ORDER BY t.modify_time DESC
LIMIT 50

在tidb中当条件:AND t.classid1 = ‘100002048’ 执行时,数据为空,AND t.classid1 = 100002048 执行时有数据
在mysql中两种均有数据

可以用explain 语法看看同样的sql在mysql和tidb中执行计划的差别

1赞

tidb

eq(json_extract(h3_ware.t_ware_platform_class_info.class_id, "$[0]"), cast("100002048", json BINARY)), or(eq(h3_ware.t_ware_platform_class_info.is_delete, 2), 0), or(eq(h3_ware.t_ware_platform_class_info.is_enable, 1), 0)

mysql

![image|690x174](upload://hHEQLnaJPOvuf2loC3ODrNmVz7G.png) 

估计是字段转换的时候,有截取了

不至于吧,100002048 也不长啊。

怎么处理避免被截取?

这一句放到t表里面,也会出现相同的情况么?
AND t.classid1 = ‘100002048’

执行计划中很明显是做了一个类型转换


mysql里也没有数据,但是上面那个在mysql里有数据。。。

提供一下表结构和脱敏数据吧,我们验证一下

建议把SQL简化测试这个值的查询是否有这个问题。

- 如果你的问题已解决:
  - 如果你自己排查解决了,请附上你的解决方案,对自己的方案标记【对我有用】。
  - 如果别人帮助你解决了问题,那么请选择【最有价值】的回复,标记为【对我有用】,对帮助你的人,也是一种嘉奖和赞赏。
- 被标记了【对我有用】的问题,才能被搜索到,这样子也能帮助他人更高效地找到答案。标记了【对我有用】还能获得 5 积分,5 经验值。
- 如果你的问题还没有解决,请继续追问及反馈你遇到的问题。

又是union all的问题。之前也我提过类似的,union或导致数据查询问题,好像还没解决。

下面这个SQL不带union all和带union all结果不一样。
1.不带union all数据都不查不出来;
2.带union all,mysql能查出来,tidb查不出 (筛选条件:AND t1.group_id = ‘100120512’)
3.不带union all,数据都能查出来(筛选条件:AND t1.group_id = 100120512)

SELECT *
FROM h3_ware.t_ware_group_base_info t1
	LEFT JOIN (
		SELECT modify_time, ware_platform_id, class_id, class_group_id, class_code
			, maintain_class_type, is_enable, is_delete, class_id -> '$[0]' AS classid1
			, class_id -> '$[1]' AS classid2, class_id -> '$[2]' AS classid3
			, class_id -> '$[3]' AS classid4
		FROM h3_ware.t_ware_group_class_info
		WHERE group_id = 100120512
		UNION ALL
		SELECT modify_time, ware_platform_id, class_id, class_group_id, class_code
			, maintain_class_type, is_enable, is_delete, class_id -> '$[0]' AS classid1
			, class_id -> '$[1]' AS classid2, class_id -> '$[2]' AS classid3
			, class_id -> '$[3]' AS classid4
		FROM h3_ware.t_ware_platform_class_info
	) t
	ON t1.ware_platform_id = t.ware_platform_id
	LEFT JOIN h3_orgmanager.t_class ct1 ON classid1 = ct1.class_id
WHERE t1.group_id = 100120512
	AND t.class_group_id = '118'
	AND t.classid1 = '100002048'
	AND t1.group_id = '100120512'
ORDER BY t.modify_time DESC
LIMIT 50

提供一下表结构,和可验证的脱敏数据吧。

建议新开贴询问,会有更多大佬关注~