TDengine Subquery Core Dump

user_16463254 0
Viewed 95

I am working on a project using TDengine, and the recent work requires to perform some subquery. I was experimenting with TDengine subquery, and encountered the following problems

taos> select avg(col2) from stb where col1 > (select avg(col1) from stb);
DB error: syntax error near "select avg(col1) from stb);" (0.000149s)

taos> select avg(col2), (select last(ts) from stb_54) as ts_spec from stb;
DB error: syntax error near "select last(ts) from stb_54) as ts_spec from (0.000092s)

taos>select max(col3) from (select spread(col3) as col3 from stbgroup by tbname);
Segmentation fault (core dumped)

Does TDengine supports subquery? Is there a syntax I need to follow?

database
1 Answers

According to its official documentation, TAOS SQL is slightly different from MYSQL because it is oriented to scenarios of massive data-processing in IOT. It provides a standard SQL style language for users to getting start quickly without supporting all the general functions of MySQL.

(https://www.taosdata.com/en/documentation/taos-sql)

  1. 'The FROM keyword can be followed by a list of several tables (STables) or result of a subquery'. TAOS SQL only support subquery after FROM keyword , not a subquery as parameters of WHERE clause, you can try to modify the syntax of SQL as:

taos> select avg(col2) from (select * from sub where col1>AVG(col1));

DB error: invalid operation: not support ordinary column join (0.000118s)

The result shows TAOS not support column join yet (average operation in where clause). So separate into two part:

(1) Calculate and record the average value avg1 of col1

taos> select AVG(col1) from sub;

(2) Set the average value as a constant

taos> select avg(col2) from (select * from sub where col1>avg1);

  1. It's the same situation with 1. multi-select syntax is not supported in TAOS SQL.

  2. After plenty of tests, I found that MAX() function maybe has some unreasonable issue, the easiest way to solve this problem is to use TOP() function instead:

taos> select top(col3,1) from (select spread(col3) as col3 from stb group by tbname);

user_165219180
Related