升级到3.0.2 sql 报错

为提高效率,提问时请尽量提供详细背景信息,问题描述清晰可优先响应。以下信息点请尽量提供:

  • 系统版本 & kernel 版本:
  • TiDB 版本:
  • 磁盘型号:
  • 集群节点分布:
  • 数据量 & region 数量 & 副本数:
  • 集群 QPS、.999-Duration、读写比例:
  • 问题描述(我做了什么):sql 执行报错 :uncategorized SQLException for SQL []; SQL state [HY000]; error code [1105]; other error: Eval error: InvalidDataType(“invalid range,range.start should be smaller than range.end, but got [[116, 128, 0, 0, 0, 0, 2, 188, 117, 95, 105, 128, 0, 0, 0, 0, 0, 0, 1, 3, 128, 0, 0, 0, 93, 109, 130, 209],[116, 128, 0, 0, 0, 0, 2, 188, 117, 95, 105, 128, 0, 0, 0, 0, 0, 0, 1, 3, 128, 0, 0, 0, 93, 109, 130, 208, 3, 128, 0, 0, 0, 0, 34, 172, 97, 3, 128, 0, 0, 0, 0, 0, 1, 41])”); nested exception is java.sql.SQLException: other error: Eval error: InvalidDataType(“invalid range,range.start should be smaller than range.end, but got [[116, 128, 0, 0, 0, 0, 2, 188, 117, 95, 105, 128, 0, 0, 0, 0, 0, 0, 1, 3, 128, 0, 0, 0, 93, 109, 130, 209],[116, 128, 0, 0, 0, 0, 2, 188, 117, 95, 105, 128, 0, 0, 0, 0, 0, 0, 1, 3, 128, 0, 0, 0, 93, 109, 130, 208, 3, 128, 0, 0, 0, 0, 34, 172, 97, 3, 128, 0, 0, 0, 0, 0, 1, 41])”)

具体 sql 辛苦发一下,以及表结构,看着报错是 设置了无效的范围。

sql SQL: SELECT id,camp_id,aff_id, aff_m_id, adv_id,adv_m_id,country_id,source_id,currency, SUM(clicks) AS clicks,SUM(unique_clicks) AS unique_clicks, SUM(conversions) AS conversions, SUM(pay_out) AS pay_out,SUM(revenue) as revenue,SUM(amount) AS amount, SUM(pending_conversions) as pending_conversions,SUM(pending_pay_out) as pending_pay_out,SUM(pending_revenue) as pending_revenue,SUM(pending_amount) as pending_amount, SUM(rejected_conversions) as rejected_conversions,SUM(rejected_pay_out) as rejected_pay_out,SUM(rejected_revenue) as rejected_revenue,SUM(rejected_amount) as rejected_amount, SUM(impression) as impression,? AS start,platform_id,event_id,is_private,revenue_type,payout_type, sl_id,carrier_id,url_id,ai_id,async_clicks FROM report_2884.operation_report_2019_09_03 WHERE create_time >= ? AND create_time < ? AND is_tmp = ? GROUP BY camp_id,aff_id,aff_m_id,adv_id,adv_m_id,currency,event_id,is_private,revenue_type,payout_type,sl_id,url_id,ai_id,async_clicks ,country_id,source_id,platform_id,carrier_id ORDER BY camp_id,aff_id,aff_m_id,adv_id,adv_m_id,currency,event_id,is_private,revenue_type,payout_type,sl_id,url_id,ai_id,async_clicks,country_id,source_id,platform_id,carrier_id limit 0,100000

Cause: java.sql.SQLException: other error: Eval error: InvalidDataType(“invalid range,range.start should be smaller than range.end, but got [[116, 128, 0, 0, 0, 0, 2, 188, 117, 95, 105, 128, 0, 0, 0, 0, 0, 0, 1, 3, 128, 0, 0, 0, 93, 109, 130, 209],[116, 128, 0, 0, 0, 0, 2, 188, 117, 95, 105, 128, 0, 0, 0, 0, 0, 0, 1, 3, 128, 0, 0, 0, 93, 109, 130, 208, 3, 128, 0, 0, 0, 0, 34, 172, 97, 3, 128, 0, 0, 0, 0, 0, 1, 41])”)

检查下升级前后的 sql_mode,可能有些调整

使用 SELECT @@sql_mode 查询没有任何默认值

这是一个已知问题,目前正在修复,可以通过设置 TiDB 配置文件中的 feedback-probability 为 0.0 来绕过这个问题。

2赞