包含子查询的sql报错某字段不存在，其实字段是存在的

阿数l-l_l-l · 2025 年4 月 10 日 02:39

【TiDB 使用环境】生产环境
【TiDB 版本】v8.5.1
【操作系统】Rocky Linux 9.1

执行sql报错，这个sql在v6.5.1是没有问题的。
ERROR 1105 (HY000): Can’t find column Column#212 in schema Column: [datacenter.result_product_brand_dimension.brand_category] Unique key:

SELECT ANY_VALUE(IFNULL(`subQuery1138444`.`d1138444_99516018`, "")) as `p701110_99516018` FROM `result_shop_item_day` LEFT JOIN (SELECT tmp_table_inside.* FROM (SELECT result_shop_item_day.num_iid, sum(`result_shop_item_day`.`pay_amount`) AS top_amount, ROW_NUMBER() OVER ( ORDER BY SUM(result_shop_item_day.pay_amount) DESC) AS d1138444_99516018 FROM `result_shop_item_day` LEFT JOIN `shop` ON `result_shop_item_day`.`shop_id` = `shop`.`id` LEFT JOIN `result_product_brand_dimension` ON `result_shop_item_day`.`shop_id` = result_product_brand_dimension.shop_id AND result_shop_item_day.num_iid = result_product_brand_dimension.num_iid AND result_shop_item_day.day BETWEEN result_product_brand_dimension.start_date AND result_product_brand_dimension.end_date LEFT JOIN `m4_product_master_final` ON `result_shop_item_day`.`shop_id` = m4_product_master_final.shop_id and result_shop_item_day.num_iid = m4_product_master_final.num_iid LEFT JOIN `dim_day` ON `result_shop_item_day`.`brand_id` = dim_day.brand_id and result_shop_item_day.day = dim_day.day WHERE `result_shop_item_day`.`day` BETWEEN '2025-04-07' AND '2025-04-09' AND `result_shop_item_day`.`shop_id` = '43' AND `result_product_brand_dimension`.`brand_category` NOT IN ('购物金', '券', '邮费', '其他') AND (`result_shop_item_day`.`pay_amount` >= 0) GROUP BY `result_shop_item_day`.`num_iid`) tmp_table_inside) subQuery1138444 ON `result_shop_item_day`.`num_iid` = `subQuery1138444`.`num_iid` LEFT JOIN `shop` ON `result_shop_item_day`.`shop_id` = `shop`.`id` LEFT JOIN `result_product_brand_dimension` ON `result_shop_item_day`.`shop_id` = result_product_brand_dimension.shop_id AND result_shop_item_day.num_iid = result_product_brand_dimension.num_iid AND result_shop_item_day.day BETWEEN result_product_brand_dimension.start_date AND result_product_brand_dimension.end_date WHERE `result_shop_item_day`.`shop_id` = '43' AND `result_shop_item_day`.`day` BETWEEN '2025-04-07' AND '2025-04-09' AND `result_product_brand_dimension`.`brand_category` NOT IN ('购物金', '券', '邮费', '其他') GROUP BY IFNULL(subQuery1138444.d1138444_99516018, '') LIMIT 0,500000

如果把 sql查询的外层 AND result_product_brand_dimension.brand_category NOT IN ('aa', 'bb', 'cc', 'dd') 注释掉，查询就不会报错。

dba远航 · 2025 年4 月 10 日 02:50

desc result_product_brand_dimension; 确认一下表结构

阿数l-l_l-l · 2025 年4 月 10 日 02:52

表结构贴出来了呀，在上面也有

zhaokede · 2025 年4 月 10 日 02:59

层级太深了，外层的 LEFT JOIN result_product_brand_dimension，加个别名呢， AND result_product_brand_dimension. brand_category NOT IN ('aa', 'bb', 'cc', 'dd')也使用别名

阿数l-l_l-l · 2025 年4 月 10 日 03:01

试过了，不行

阿数l-l_l-l · 2025 年4 月 10 日 03:06

直接查最外层是没有问题的

dba远航 · 2025 年4 月 10 日 03:08

把上面涉及的表都找出来，在新MYSQL环境创建，然后执行一下看看，如果还报错，基本是语句问题，如果不报错，那是数据库层面

阿数l-l_l-l · 2025 年4 月 10 日 03:09

不可能是语法问题，在旧版环境 v6.5.1 是正常执行的。

WalterWj · 2025 年4 月 10 日 03:16

SQL mode 中 full group by 限制去掉试试。

阿数l-l_l-l · 2025 年4 月 10 日 03:23

本来就是去掉的呢

Kongdom · 2025 年4 月 10 日 03:41

其他表结构也发一下吧，验证一下。

zhaokede · 2025 年4 月 10 日 03:43

v8.5.1和v6.5.1，在sql_mode上面，有哪些配置不一样的？

阿数l-l_l-l · 2025 年4 月 10 日 03:47

设置为一样的了

阿数l-l_l-l · 2025 年4 月 10 日 06:47

把sql改成这样就行了

SELECT ANY_VALUE(IFNULL(`subQuery1138444`.`row_no`, "")) as `p701110_99516018` FROM `result_shop_item_day` LEFT JOIN (SELECT tmp_table_inside.*, any_value(d1138444_99516018) as row_no FROM (SELECT result_shop_item_day.num_iid, sum(`result_shop_item_day`.`pay_amount`) AS top_amount, ROW_NUMBER() OVER ( ORDER BY SUM(result_shop_item_day.pay_amount) DESC) AS d1138444_99516018 FROM `result_shop_item_day` LEFT JOIN `shop` ON `result_shop_item_day`.`shop_id` = `shop`.`id` LEFT JOIN `result_product_brand_dimension` ON `result_shop_item_day`.`shop_id` = result_product_brand_dimension.shop_id AND result_shop_item_day.num_iid = result_product_brand_dimension.num_iid AND result_shop_item_day.day BETWEEN result_product_brand_dimension.start_date AND result_product_brand_dimension.end_date LEFT JOIN `m4_product_master_final` ON `result_shop_item_day`.`shop_id` = m4_product_master_final.shop_id and result_shop_item_day.num_iid = m4_product_master_final.num_iid LEFT JOIN `dim_day` ON `result_shop_item_day`.`brand_id` = dim_day.brand_id and result_shop_item_day.day = dim_day.day WHERE `result_shop_item_day`.`day` BETWEEN '2025-04-07' AND '2025-04-09' AND `result_shop_item_day`.`shop_id` = '43' AND `result_product_brand_dimension`.`brand_category` NOT IN ('购物金', '券', '邮费', '其他') AND (`result_shop_item_day`.`pay_amount` >= 0) GROUP BY `result_shop_item_day`.`num_iid`) tmp_table_inside) subQuery1138444 ON `result_shop_item_day`.`num_iid` = `subQuery1138444`.`num_iid` LEFT JOIN `shop` ON `result_shop_item_day`.`shop_id` = `shop`.`id` LEFT JOIN `result_product_brand_dimension` ON `result_shop_item_day`.`shop_id` = result_product_brand_dimension.shop_id AND result_shop_item_day.num_iid = result_product_brand_dimension.num_iid AND result_shop_item_day.day BETWEEN result_product_brand_dimension.start_date AND result_product_brand_dimension.end_date WHERE `result_shop_item_day`.`shop_id` = '43' AND `result_shop_item_day`.`day` BETWEEN '2025-04-07' AND '2025-04-09' AND `result_product_brand_dimension`.`brand_category` NOT IN ('购物金', '券', '邮费', '其他') GROUP BY IFNULL(subQuery1138444.row_no, '') LIMIT 0,500000

猜测的原因：在 GROUP BY 场景下，非聚合列必须显式处理。窗口函数的结果虽然生成于分组后，但如果没有聚合操作，Tidb8.5.1仍会认为它们是未确定的值，导致外部查询无法找到该列

system · 2025 年4 月 17 日 06:48

此话题已在最后回复的 7 天后被自动关闭。不再允许新回复。