联表查询，条件使用函数或非主键,查询异常错误 ：TDengine ERROR (0x2701)

[hoilung]

OS:windows
version:3.0.7.0

联表查询，不支持函数或非主键时间戳作为条件内容

1. 联表条件使用函数,查询异常
2. 联表条件使用非主键，查询异常

数据结构内容如下

select * from data_event;

ts	device_name	attribute_name	data_type	data_value
2021-07-13 14:06:32.272356	d8408	jk	bool	1.0
2021-07-13 14:06:32.272357	d8408	dl_a	float	6.6
2021-07-13 14:06:32.272358	d8409	jk	bool	0.0
2021-07-13 14:06:32.272359	d8409	dl_a	float	8.8

在不确定设备结构的情况下，使用一张表存储数据

不想使用多个列,在不传参数时间戳的情况下获取d8408的在1s内的数据
想通过t2表查询last_row获取最后一行数据的时间戳，再结合联表查询获取所有符合条件的内容

select t1.* from 
data_event as t1,
(select ts,device_name from data_event where device_name='d8408' order by ts desc limit 1) as t2 
where timediff(t1.ts,t2.ts,1s)==0 and t1.device_name=t2.device_name

或者

select t1.* from 
data_event as t1,
(select last_row(ts) as ts from data_event where device_name='d8408') as t2 
where timediff(t1.ts,t2.ts,1s)==0 and t1.device_name='d8408'

想通过以上SQL得到如下结果

select * from data_event where timediff('2021-07-13 14:06:32.272',ts,1s)==0 and device_name='d8408'

ts	device_name	attribute_name	data_type	data_value
2021-07-13 14:06:32.272	d8408	jk	bool	1.0
2021-07-13 14:06:32.272	d8408	dl_a	float	6.6

测试如下：

查询正常

条件使用主键时间戳的正常

select t1.* from 
data_event t1,
(select last_row(ts) as ts from data_event where device_name='d8408') t2
where t1.ts=t2.ts

查询异常

条件是非主键的查询异常

select t1.* from 
data_event t1,
(select last_row(device_name) as device_name from data_event where device_name='d8408') t2
where t1.device_name=t2.device_name

条件使用函数的查询异常

select t1.* from 
data_event as t1,
(select last_row(ts) as ts from data_event where device_name='d8408') as t2 
where timediff(t1.ts,t2.ts,1s)==0