记录一次TDengine的SQL查询报错排查

原创已于 2025-06-30 21:41:47 修改 · 673 阅读

11 ·

CC 4.0 BY-SA版权

文章标签：

#时序数据库

于 2025-06-30 21:40:43 首次发布

问题描述

平时很正常的一个接口，查询tdengine的数据，在切换成集群版之后报错了，原来的腾讯云版本是正常的

排查过程

查看报错，提示的是SQL语法异常

### Error querying database.  Cause: java.sql.SQLException: TDengine ERROR (0x2600): sql: select sum(pn) as pn , sum(pi) as pi from stpv_modbus_decdata_dac01796 where sn='2811045236260156' and ts >= '2024-12-19 00:00:00' and ts <= '2024-12-19 23:59:59', desc: syntax error near ") as pi from stpv_modbus_decdata_dac01796 where sn='2811045236260156' and ts >= '2024-12-19 00:00:00' and ts <= '2024-12-19 23:59:59'"

### The error may exist in com/hgtc/publicmethod/mapper/DataAccessMapper.java (best guess)

### The error may involve com.hgtc.publicmethod.mapper.DataAccessMapper.selectTd-Inline

### The error occurred while setting parameters

### SQL: select sum(pn) as pn , sum(pi) as pi from stpv_modbus_decdata_dac01796 where sn='2811045236260156' and ts >= '2024-12-19 00:00:00' and ts <= '2024-12-19 23:59:59'

### Cause: java.sql.SQLException: TDengine ERROR (0x2600): sql: select sum(pn) as pn , sum(pi) as pi from stpv_modbus_decdata_dac01796 where sn='2811045236260156' and ts >= '2024-12-19 00:00:00' and ts <= '2024-12-19 23:59:59', desc: syntax error near ") as pi from stpv_modbus_decdata_dac01796 where sn='2811045236260156' and ts >= '2024-12-19 00:00:00' and ts <= '2024-12-19 23:59:59'"

; uncategorized SQLException; SQL state []; error code [9728]; TDengine ERROR (0x2600): sql: select sum(pn) as pn , sum(pi) as pi from stpv_modbus_decdata_dac01796 where sn='2811045236260156' and ts >= '2024-12-19 00:00:00' and ts <= '2024-12-19 23:59:59', desc: syntax error near ") as pi from stpv_modbus_decdata_dac01796 where sn='2811045236260156' and ts >= '2024-12-19 00:00:00' and ts <= '2024-12-19 23:59:59'"; nested exception is java.sql.SQLException: TDengine ERROR (0x2600): sql: select sum(pn) as pn , sum(pi) as pi from stpv_modbus_decdata_dac01796 where sn='2811045236260156' and ts >= '2024-12-19 00:00:00' and ts <= '2024-12-19 23:59:59', desc: syntax error near ") as pi from stpv_modbus_decdata_dac01796 where sn='2811045236260156' and ts >= '2024-12-19 00:00:00' and ts <= '2024-12-19 23:59:59'"

org.springframework.jdbc.UncategorizedSQLException:

语法异常，先把sql复制出来：

select

sum(pn) as pn,

sum(pi) as pi

from springtech_pv.stpv_modbus_decdata_dac01796

where sn='2811045236260156' and ts >= '2024-12-19 00:00:00' and ts <= '2024-12-19 23:59:59'

把这个sql在老腾讯云tdengine编辑器中执行，一点问题没有

放新的集群版本tdengine编辑器中执行，报错了！！

问了之后发现腾讯云的是3.2版本，集群版是3.3，合理怀疑是版本问题

接下来就是找出导致报错的sql片段就好了

我们把查询条件换成select * from ，执行没报错，说明where条件没问题，问题在搜索字段中

select

sum(pn) as pn,

sum(pi) as pi

from springtech_pv.stpv_modbus_decdata_dac01796

where sn='2811045236260156' and ts >= '2024-12-19 00:00:00' and ts <= '2024-12-19 23:59:59'

把搜索条件去掉一个，只留sum(pn) as pn，执行没问题，说明问题在sum(pi) as pi上

单独查询sum(pi) as pi，报错了，说明pi是3.3版本的一个新关键字

解决方法

百度一下，发现pi是3.3版本tdengine新加入的函数圆周率，所以是关键字，解决办法只需要转义一下pi就行了，改成`pi`

select sum(`pi`) as `pi`

from springtech_pv.stpv_modbus_decdata_dac01796

where sn='2811045236260156' and ts >= '2024-12-19 00:00:00' and ts <= '2024-12-19 23:59:59'