system sh/stop_dnodes.sh system sh/deploy.sh -n dnode1 -i 1 system sh/exec.sh -n dnode1 -s start sql connect print =============== create database sql create database db sql select * from information_schema.ins_databases if $rows != 3 then return -1 endi sql use db print =============== create super table and child table sql create table stb1 (ts timestamp, c1 int, c2 bigint, c3 smallint, c4 tinyint, c5 float, c6 double, c7 bool, c8 binary(16),c9 nchar(32), c10 timestamp) tags (t1 int) sql show stables print $rows $data00 $data01 $data02 if $rows != 1 then return -1 endi sql create table ct1 using stb1 tags ( 1 ) sql create table ct2 using stb1 tags ( 2 ) sql create table ct3 using stb1 tags ( 3 ) sql create table ct4 using stb1 tags ( 4 ) sql show tables print $rows $data00 $data10 $data20 if $rows != 4 then return -1 endi print =============== insert data into child table ct1 (s) sql insert into ct1 values ( '2022-01-01 01:01:01.000', 1, 11111, 111, 11, 1.11, 11.11, 1, "binary1", "nchar1", now+1a ) sql insert into ct1 values ( '2022-01-01 01:01:06.000', 2, 22222, 222, 22, 2.22, 22.22, 0, "binary2", "nchar2", now+2a ) sql insert into ct1 values ( '2022-01-01 01:01:10.000', 3, 33333, 333, 33, 3.33, 33.33, 0, "binary3", "nchar3", now+3a ) sql insert into ct1 values ( '2022-01-01 01:01:16.000', 4, 44444, 444, 44, 4.44, 44.44, 1, "binary4", "nchar4", now+4a ) sql insert into ct1 values ( '2022-01-01 01:01:20.000', 5, 55555, 555, 55, 5.55, 55.55, 0, "binary5", "nchar5", now+5a ) sql insert into ct1 values ( '2022-01-01 01:01:26.000', 6, 66666, 666, 66, 6.66, 66.66, 1, "binary6", "nchar6", now+6a ) sql insert into ct1 values ( '2022-01-01 01:01:30.000', 7, 00000, 000, 00, 0.00, 00.00, 1, "binary7", "nchar7", now+7a ) sql insert into ct1 values ( '2022-01-01 01:01:36.000', 8, -88888, -888, -88, -8.88, -88.88, 0, "binary8", "nchar8", now+8a ) print =============== insert data into child table ct4 (y) sql insert into ct4 values ( '2019-01-21 01:01:01.000', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ) sql insert into ct4 values ( '2019-10-21 01:01:01.000', 1, 11111, 111, 11, 1.11, 11.11, 1, "binary1", "nchar1", now+1a ) sql insert into ct4 values ( '2019-12-31 01:01:01.000', 2, 22222, 222, 22, 2.22, 22.22, 0, "binary2", "nchar2", now+2a ) sql insert into ct4 values ( '2020-01-01 01:01:06.000', 3, 33333, 333, 33, 3.33, 33.33, 0, "binary3", "nchar3", now+3a ) sql insert into ct4 values ( '2020-05-07 01:01:10.000', 4, 44444, 444, 44, 4.44, 44.44, 1, "binary4", "nchar4", now+4a ) sql insert into ct4 values ( '2020-09-30 01:01:16.000', 5, 55555, 555, 55, 5.55, 55.55, 0, "binary5", "nchar5", now+5a ) sql insert into ct4 values ( '2020-12-21 01:01:01.000', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ) sql insert into ct4 values ( '2021-02-01 01:01:20.000', 6, 66666, 666, 66, 6.66, 66.66, 1, "binary6", "nchar6", now+6a ) sql insert into ct4 values ( '2021-10-28 01:01:26.000', 7, 00000, 000, 00, 0.00, 00.00, 1, "binary7", "nchar7", "1970-01-01 08:00:00.000" ) sql insert into ct4 values ( '2021-12-01 01:01:30.000', 8, -88888, -888, -88, -8.88, -88.88, 0, "binary8", "nchar8", "1969-01-01 01:00:00.000" ) sql insert into ct4 values ( '2022-02-31 01:01:36.000', 9, -99999999999999999, -999, -99, -9.99, -999999999999999999999.99, 1, "binary9", "nchar9", "1900-01-01 00:00:00.000" ) sql insert into ct4 values ( '2022-05-21 01:01:01.000', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ) print ================ start query ====================== print ================ query 1 having condition sql select c1 from ct1 group by c1 having count(c1) sql select c1 from ct4 group by c1 having count(c1) sql select count(c1) from ct1 group by c1 having count(c1) sql select sum(c1) ,count(c7) from ct4 group by c7 having count(c7) > 1 ; print ====> sql : select sum(c1) ,count(c7) from ct4 group by c7 having count(c7) > 1 ; print ====> rows: $rows if $rows != 2 then return -1 endi sql select sum(c1) ,count(c7) from ct4 group by c7 having count(c1) < sum(c1) ; print ====> sql : select sum(c1) ,count(c7) from ct4 group by c7 having count(c7) > 1 ; print ====> rows: $rows if $rows != 2 then return -1 endi sql select sum(c1) ,count(c1) from ct4 group by c1 having count(c7) < 2 and sum(c1) > 2 ; print ====> sql : select sum(c1) ,count(c1) from ct4 group by c1 having count(c7) < 2 and sum(c1) > 2 ; print ====> rows: $rows if $rows != 7 then return -1 endi sql select sum(c1) ,count(c1) from ct4 group by c1 having count(c7) < 1 or sum(c1) > 2 ; print ====> sql : select sum(c1) ,count(c1) from ct4 group by c1 having count(c7) < 1 or sum(c1) > 2 ; print ====> rows: $rows if $rows != 8 then return -1 endi print ================ query 1 complex with having condition sql select count(c1) from ct4 where c1 > 2 group by c7 having count(c1) > 1 limit 1 offset 0 print ====> sql : select count(c1) from ct4 where c1 > 2 group by c7 having count(c1) > 1 limit 1 offset 0 print ====> rows: $rows if $rows != 1 then return -1 endi sql select abs(c1) from ct4 where c1 > 2 group by c1 having abs(c1) > 1 limit 1 offset 1 print ====> sql : select abs(c1) from ct4 where c1 > 2 group by c1 having abs(c1) > 1 limit 1 offset 1 print ====> rows: $rows if $rows != 1 then return -1 endi sql select acos(c1) from ct4 where c1 > 2 group by c1 having abs(c1) > 1 limit 1 offset 1 print ====> sql : select acos(c1) from ct4 where c1 > 2 group by c1 having abs(c1) > 1 limit 1 offset 1 print ====> rows: $rows if $rows != 1 then return -1 endi sql select asin(c1) from ct4 where c1 > 2 group by c1 having abs(c1) > 1 limit 1 offset 1 print ====> sql : select asin(c1) from ct4 where c1 > 2 group by c1 having abs(c1) > 1 limit 1 offset 1 print ====> rows: $rows if $rows != 1 then return -1 endi sql select atan(c1) from ct4 where c1 > 2 group by c1 having abs(c1) > 1 limit 1 offset 1 print ====> sql : select atan(c1) from ct4 where c1 > 2 group by c7 having abs(c1) > 1 limit 1 offset 1 print ====> rows: $rows if $rows != 1 then return -1 endi sql select ceil(c1) from ct4 where c1 > 2 group by c1 having abs(c1) > 1 limit 1 offset 1 print ====> sql : select ceil(c1) from ct4 where c1 > 2 group by c7 having abs(c1) > 1 limit 1 offset 1 print ====> rows: $rows if $rows != 1 then return -1 endi sql select cos(c1) from ct4 where c1 > 2 group by c1 having abs(c1) > 1 limit 1 offset 1 print ====> sql : select cos(c1) from ct4 where c1 > 2 group by c7 having abs(c1) > 1 limit 1 offset 1 print ====> rows: $rows if $rows != 1 then return -1 endi sql select floor(c1) from ct4 where c1 > 2 group by c1 having abs(c1) > 1 limit 1 offset 1 print ====> sql : select floor(c1) from ct4 where c1 > 2 group by c7 having abs(c1) > 1 limit 1 offset 1 print ====> rows: $rows if $rows != 1 then return -1 endi sql select log(c1,10) from ct4 where c1 > 2 group by c1 having abs(c1) > 1 limit 1 offset 1 print ====> sql : select log(c1,10) from ct4 where c1 > 2 group by c7 having abs(c1) > 1 limit 1 offset 1 print ====> rows: $rows if $rows != 1 then return -1 endi sql select pow(c1,3) from ct4 where c1 > 2 group by c1 having abs(c1) > 1 limit 1 offset 1 print ====> sql : select pow(c1,3) from ct4 where c1 > 2 group by c7 having abs(c1) > 1 limit 1 offset 1 print ====> rows: $rows if $rows != 1 then return -1 endi sql select round(c1) from ct4 where c1 > 2 group by c1 having abs(c1) > 1 limit 1 offset 1 print ====> sql : select round(c1) from ct4 where c1 > 2 group by c7 having abs(c1) > 1 limit 1 offset 1 print ====> rows: $rows if $rows != 1 then return -1 endi sql select sqrt(c1) from ct4 where c1 > 2 group by c1 having abs(c1) > 1 limit 1 offset 1 print ====> sql : select sqrt(c1) from ct4 where c1 > 2 group by c7 having abs(c1) > 1 limit 1 offset 1 print ====> rows: $rows if $rows != 1 then return -1 endi sql select sin(c1) from ct4 where c1 > 2 group by c1 having abs(c1) > 1 limit 1 offset 1 print ====> sql : select sin(c1) from ct4 where c1 > 2 group by c7 having abs(c1) > 1 limit 1 offset 1 print ====> rows: $rows if $rows != 1 then return -1 endi sql select tan(c1) from ct4 where c1 > 2 group by c1 having abs(c1) > 1 limit 1 offset 1 print ====> sql : select tan(c1) from ct4 where c1 > 2 group by c7 having abs(c1) > 1 limit 1 offset 1 print ====> rows: $rows if $rows != 1 then return -1 endi print =================== count all rows sql select count(c1) from stb1 print ====> sql : select count(c1) from stb1 print ====> rows: $data00 if $data00 != 17 then print expect 17, actual: $data00 return -1 endi #================================================= print =============== stop and restart taosd system sh/exec.sh -n dnode1 -s stop -x SIGINT system sh/exec.sh -n dnode1 -s start $loop_cnt = 0 check_dnode_ready_0: $loop_cnt = $loop_cnt + 1 sleep 200 if $loop_cnt == 10 then print ====> dnode not ready! return -1 endi sql select * from information_schema.ins_dnodes print ===> $rows $data00 $data01 $data02 $data03 $data04 $data05 if $data00 != 1 then return -1 endi if $data04 != ready then goto check_dnode_ready_0 endi print =================== count all rows sql select count(c1) from stb1 print ====> sql : select count(c1) from stb1 print ====> rows: $data00 if $data00 != 17 then return -1 endi print ================ query 1 having condition sql select c1 from ct1 group by c1 having count(c1) sql select c1 from ct4 group by c1 having count(c1) sql select count(c1) from ct1 group by c1 having count(c1) sql select sum(c1) ,count(c7) from ct4 group by c7 having count(c7) > 1 ; print ====> sql : select sum(c1) ,count(c7) from ct4 group by c7 having count(c7) > 1 ; print ====> rows: $rows if $rows != 2 then return -1 endi sql select sum(c1) ,count(c7) from ct4 group by c7 having count(c1) < sum(c1) ; print ====> sql : select sum(c1) ,count(c7) from ct4 group by c7 having count(c7) > 1 ; print ====> rows: $rows if $rows != 2 then return -1 endi sql select sum(c1) ,count(c1) from ct4 group by c1 having count(c7) < 2 and sum(c1) > 2 ; print ====> sql : select sum(c1) ,count(c1) from ct4 group by c1 having count(c7) < 2 and sum(c1) > 2 ; print ====> rows: $rows if $rows != 7 then return -1 endi sql select sum(c1) ,count(c1) from ct4 group by c1 having count(c7) < 1 or sum(c1) > 2 ; print ====> sql : select sum(c1) ,count(c1) from ct4 group by c1 having count(c7) < 1 or sum(c1) > 2 ; print ====> rows: $rows if $rows != 8 then return -1 endi print ================ query 1 complex with having condition sql select count(c1) from ct4 where c1 > 2 group by c7 having count(c1) > 1 limit 1 offset 0 print ====> sql : select count(c1) from ct4 where c1 > 2 group by c7 having count(c1) > 1 limit 1 offset 0 print ====> rows: $rows if $rows != 1 then return -1 endi sql select abs(c1) from ct4 where c1 > 2 group by c1 having abs(c1) > 1 limit 1 offset 1 print ====> sql : select abs(c1) from ct4 where c1 > 2 group by c1 having abs(c1) > 1 limit 1 offset 1 print ====> rows: $rows if $rows != 1 then return -1 endi sql select acos(c1) from ct4 where c1 > 2 group by c1 having abs(c1) > 1 limit 1 offset 1 print ====> sql : select acos(c1) from ct4 where c1 > 2 group by c1 having abs(c1) > 1 limit 1 offset 1 print ====> rows: $rows if $rows != 1 then return -1 endi sql select asin(c1) from ct4 where c1 > 2 group by c1 having abs(c1) > 1 limit 1 offset 1 print ====> sql : select asin(c1) from ct4 where c1 > 2 group by c1 having abs(c1) > 1 limit 1 offset 1 print ====> rows: $rows if $rows != 1 then return -1 endi sql select atan(c1) from ct4 where c1 > 2 group by c1 having abs(c1) > 1 limit 1 offset 1 print ====> sql : select atan(c1) from ct4 where c1 > 2 group by c7 having abs(c1) > 1 limit 1 offset 1 print ====> rows: $rows if $rows != 1 then return -1 endi sql select ceil(c1) from ct4 where c1 > 2 group by c1 having abs(c1) > 1 limit 1 offset 1 print ====> sql : select ceil(c1) from ct4 where c1 > 2 group by c7 having abs(c1) > 1 limit 1 offset 1 print ====> rows: $rows if $rows != 1 then return -1 endi sql select cos(c1) from ct4 where c1 > 2 group by c1 having abs(c1) > 1 limit 1 offset 1 print ====> sql : select cos(c1) from ct4 where c1 > 2 group by c7 having abs(c1) > 1 limit 1 offset 1 print ====> rows: $rows if $rows != 1 then return -1 endi sql select floor(c1) from ct4 where c1 > 2 group by c1 having abs(c1) > 1 limit 1 offset 1 print ====> sql : select floor(c1) from ct4 where c1 > 2 group by c7 having abs(c1) > 1 limit 1 offset 1 print ====> rows: $rows if $rows != 1 then return -1 endi sql select log(c1,10) from ct4 where c1 > 2 group by c1 having abs(c1) > 1 limit 1 offset 1 print ====> sql : select log(c1,10) from ct4 where c1 > 2 group by c7 having abs(c1) > 1 limit 1 offset 1 print ====> rows: $rows if $rows != 1 then return -1 endi sql select pow(c1,3) from ct4 where c1 > 2 group by c1 having abs(c1) > 1 limit 1 offset 1 print ====> sql : select pow(c1,3) from ct4 where c1 > 2 group by c7 having abs(c1) > 1 limit 1 offset 1 print ====> rows: $rows if $rows != 1 then return -1 endi sql select round(c1) from ct4 where c1 > 2 group by c1 having abs(c1) > 1 limit 1 offset 1 print ====> sql : select round(c1) from ct4 where c1 > 2 group by c7 having abs(c1) > 1 limit 1 offset 1 print ====> rows: $rows if $rows != 1 then return -1 endi sql select sqrt(c1) from ct4 where c1 > 2 group by c1 having abs(c1) > 1 limit 1 offset 1 print ====> sql : select sqrt(c1) from ct4 where c1 > 2 group by c7 having abs(c1) > 1 limit 1 offset 1 print ====> rows: $rows if $rows != 1 then return -1 endi sql select sin(c1) from ct4 where c1 > 2 group by c1 having abs(c1) > 1 limit 1 offset 1 print ====> sql : select sin(c1) from ct4 where c1 > 2 group by c7 having abs(c1) > 1 limit 1 offset 1 print ====> rows: $rows if $rows != 1 then return -1 endi sql select tan(c1) from ct4 where c1 > 2 group by c1 having abs(c1) > 1 limit 1 offset 1 print ====> sql : select tan(c1) from ct4 where c1 > 2 group by c7 having abs(c1) > 1 limit 1 offset 1 print ====> rows: $rows if $rows != 1 then return -1 endi system sh/exec.sh -n dnode1 -s stop -x SIGINT