system sh/stop_dnodes.sh system sh/deploy.sh -n dnode1 -i 1 system sh/exec.sh -n dnode1 -s start $loop_cnt = 0 check_dnode_ready: $loop_cnt = $loop_cnt + 1 sleep 200 if $loop_cnt == 10 then print ====> dnode not ready! return -1 endi sql show dnodes print ===> $rows $data00 $data01 $data02 $data03 $data04 $data05 if $data00 != 1 then return -1 endi if $data04 != ready then goto check_dnode_ready endi sql connect print =============== create database sql create database db sql show databases if $rows != 2 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 ct2 (d) sql insert into ct2 values ( '2022-01-01 01:00:01.000', 1, 11111, 111, 11, 1.11, 11.11, 1, "binary1", "nchar1", now+1a ) sql insert into ct2 values ( '2022-01-01 10:00:01.000', 2, 22222, 222, 22, 2.22, 22.22, 0, "binary2", "nchar2", now+2a ) sql insert into ct2 values ( '2022-01-01 20:00:01.000', 3, 33333, 333, 33, 3.33, 33.33, 0, "binary3", "nchar3", now+3a ) sql insert into ct2 values ( '2022-01-02 10:00:01.000', 4, 44444, 444, 44, 4.44, 44.44, 1, "binary4", "nchar4", now+4a ) sql insert into ct2 values ( '2022-01-02 20:00:01.000', 5, 55555, 555, 55, 5.55, 55.55, 0, "binary5", "nchar5", now+5a ) sql insert into ct2 values ( '2022-01-03 10:00:01.000', 7, 00000, 000, 00, 0.00, 00.00, 1, "binary7", "nchar7", now+6a ) sql insert into ct2 values ( '2022-01-03 20:00:01.000', 8, -88888, -888, -88, -8.88, -88.88, 0, "binary8", "nchar8", now+7a ) print =============== insert data into child table ct3 (n) sql insert into ct3 values ( '2021-12-21 01:01:01.000', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ) sql insert into ct3 values ( '2021-12-31 01:01:01.000', 1, 11111, 111, 11, 1.11, 11.11, 1, "binary1", "nchar1", now+1a ) sql insert into ct3 values ( '2022-01-01 01:01:06.000', 2, 22222, 222, 22, 2.22, 22.22, 0, "binary2", "nchar2", now+2a ) sql insert into ct3 values ( '2022-01-07 01:01:10.000', 3, 33333, 333, 33, 3.33, 33.33, 0, "binary3", "nchar3", now+3a ) sql insert into ct3 values ( '2022-01-31 01:01:16.000', 4, 44444, 444, 44, 4.44, 44.44, 1, "binary4", "nchar4", now+4a ) sql insert into ct3 values ( '2022-02-01 01:01:20.000', 5, 55555, 555, 55, 5.55, 55.55, 0, "binary5", "nchar5", now+5a ) sql insert into ct3 values ( '2022-02-28 01:01:26.000', 6, 66666, 666, 66, 6.66, 66.66, 1, "binary6", "nchar6", now+6a ) sql insert into ct3 values ( '2022-03-01 01:01:30.000', 7, 00000, 000, 00, 0.00, 00.00, 1, "binary7", "nchar7", "1970-01-01 08:00:00.000" ) sql insert into ct3 values ( '2022-03-08 01:01:36.000', 8, -88888, -888, -88, -8.88, -88.88, 0, "binary8", "nchar8", "1969-01-01 01:00:00.000" ) print =============== insert data into child table ct4 (y) sql insert into ct4 values ( '2020-10-21 01:01:01.000', 1, 11111, 111, 11, 1.11, 11.11, 1, "binary1", "nchar1", now+1a ) sql insert into ct4 values ( '2020-12-31 01:01:01.000', 2, 22222, 222, 22, 2.22, 22.22, 0, "binary2", "nchar2", now+2a ) sql insert into ct4 values ( '2021-01-01 01:01:06.000', 3, 33333, 333, 33, 3.33, 33.33, 0, "binary3", "nchar3", now+3a ) sql insert into ct4 values ( '2021-05-07 01:01:10.000', 4, 44444, 444, 44, 4.44, 44.44, 1, "binary4", "nchar4", now+4a ) sql insert into ct4 values ( '2021-09-30 01:01:16.000', 5, 55555, 555, 55, 5.55, 55.55, 0, "binary5", "nchar5", now+5a ) sql insert into ct4 values ( '2022-02-01 01:01:20.000', 6, 66666, 666, 66, 6.66, 66.66, 1, "binary6", "nchar6", now+6a ) sql insert into ct4 values ( '2022-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 ( '2022-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-12-31 01:01:36.000', 9, -99999999999999999, -999, -99, -9.99, -999999999999999999999.99, 1, "binary9", "nchar9", "1900-01-01 00:00:00.000" ) print ================ start query ====================== print ================ query 1 group by filter sql select count(*) from ct3 group by c1 print ====> sql : select count(*) from ct3 group by c1 print ====> rows: $rows if $rows != 8 then return -1 endi sql select count(*) from ct3 group by c2 print ====> sql : select count(*) from ct3 group by c2 print ====> rows: $rows if $rows != 8 then return -1 endi sql select count(*) from ct3 group by c3 print ====> sql : select count(*) from ct3 group by c3 print ====> rows: $rows if $rows != 8 then return -1 endi sql select count(*) from ct3 group by c4 print ====> sql : select count(*) from ct3 group by c4 print ====> rows: $rows if $rows != 8 then return -1 endi sql select count(*) from ct3 group by c5 print ====> sql : select count(*) from ct3 group by c5 print ====> rows: $rows if $rows != 8 then return -1 endi sql select count(*) from ct3 group by c6 print ====> sql : select count(*) from ct3 group by c6 print ====> rows: $rows if $rows != 8 then return -1 endi sql select count(*) from ct3 group by c7 print ====> sql : select count(*) from ct3 group by c7 print ====> rows: $rows if $rows != 2 then return -1 endi sql select count(*) from ct3 group by c8 print ====> sql : select count(*) from ct3 group by c8 print ====> rows: $rows if $rows != 8 then return -1 endi sql select count(*) from ct3 group by c9 print ====> sql : select count(*) from ct3 group by c9 print ====> rows: $rows if $rows != 8 then return -1 endi sql select count(*) from ct3 group by c10 print ====> sql : select count(*) from ct3 group by c10 print ====> rows: $rows if $rows != 8 then return -1 endi print ================ query 2 complex with group by sql select count(c1) from ct3 where c1 > 2 group by c7 limit 1 offset 1 print ====> sql : select count(c1) from ct3 where c1 > 2 group by c7 limit 1 offset 2 print ====> rows: $rows if $rows != 1 then return -1 endi sql select abs(c1) from ct3 where c1 > 2 group by c7 limit 1 offset 1 print ====> sql : select abs(c1) from ct3 where c1 > 2 group by c7 limit 1 offset 2 print ====> rows: $rows if $rows != 1 then return -1 endi sql select acos(c1) from ct3 where c1 > 2 group by c7 limit 1 offset 1 print ====> sql : select acos(c1) from ct3 where c1 > 2 group by c7 limit 1 offset 2 print ====> rows: $rows if $rows != 1 then return -1 endi sql select asin(c1) from ct3 where c1 > 2 group by c7 limit 1 offset 1 print ====> sql : select asin(c1) from ct3 where c1 > 2 group by c7 limit 1 offset 2 print ====> rows: $rows if $rows != 1 then return -1 endi sql select atan(c1) from ct3 where c1 > 2 group by c7 limit 1 offset 1 print ====> sql : select atan(c1) from ct3 where c1 > 2 group by c7 limit 1 offset 2 print ====> rows: $rows if $rows != 1 then return -1 endi sql select ceil(c1) from ct3 where c1 > 2 group by c7 limit 1 offset 1 print ====> sql : select ceil(c1) from ct3 where c1 > 2 group by c7 limit 1 offset 2 print ====> rows: $rows if $rows != 1 then return -1 endi sql select cos(c1) from ct3 where c1 > 2 group by c7 limit 1 offset 1 print ====> sql : select cos(c1) from ct3 where c1 > 2 group by c7 limit 1 offset 2 print ====> rows: $rows if $rows != 1 then return -1 endi sql select floor(c1) from ct3 where c1 > 2 group by c7 limit 1 offset 1 print ====> sql : select floor(c1) from ct3 where c1 > 2 group by c7 limit 1 offset 2 print ====> rows: $rows if $rows != 1 then return -1 endi sql select log(c1,10) from ct3 where c1 > 2 group by c7 limit 1 offset 1 print ====> sql : select log(c1,10) from ct3 where c1 > 2 group by c7 limit 1 offset 2 print ====> rows: $rows if $rows != 1 then return -1 endi sql select pow(c1,3) from ct3 where c1 > 2 group by c7 limit 1 offset 1 print ====> sql : select pow(c1,3) from ct3 where c1 > 2 group by c7 limit 1 offset 2 print ====> rows: $rows if $rows != 1 then return -1 endi sql select round(c1) from ct3 where c1 > 2 group by c7 limit 1 offset 1 print ====> sql : select round(c1) from ct3 where c1 > 2 group by c7 limit 1 offset 2 print ====> rows: $rows if $rows != 1 then return -1 endi sql select sqrt(c1) from ct3 where c1 > 2 group by c7 limit 1 offset 1 print ====> sql : select sqrt(c1) from ct3 where c1 > 2 group by c7 limit 1 offset 2 print ====> rows: $rows if $rows != 1 then return -1 endi sql select sin(c1) from ct3 where c1 > 2 group by c7 limit 1 offset 1 print ====> sql : select sin(c1) from ct3 where c1 > 2 group by c7 limit 1 offset 2 print ====> rows: $rows if $rows != 1 then return -1 endi sql select tan(c1) from ct3 where c1 > 2 group by c7 limit 1 offset 1 print ====> sql : select tan(c1) from ct3 where c1 > 2 group by c7 limit 1 offset 2 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 != 33 then 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 show 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 != 33 then return -1 endi print ================ query 1 group by filter sql select count(*) from ct3 group by c1 print ====> sql : select count(*) from ct3 group by c1 print ====> rows: $rows if $rows != 8 then return -1 endi sql select count(*) from ct3 group by c2 print ====> sql : select count(*) from ct3 group by c2 print ====> rows: $rows if $rows != 8 then return -1 endi sql select count(*) from ct3 group by c3 print ====> sql : select count(*) from ct3 group by c3 print ====> rows: $rows if $rows != 8 then return -1 endi sql select count(*) from ct3 group by c4 print ====> sql : select count(*) from ct3 group by c4 print ====> rows: $rows if $rows != 8 then return -1 endi sql select count(*) from ct3 group by c5 print ====> sql : select count(*) from ct3 group by c5 print ====> rows: $rows if $rows != 8 then return -1 endi sql select count(*) from ct3 group by c6 print ====> sql : select count(*) from ct3 group by c6 print ====> rows: $rows if $rows != 8 then return -1 endi sql select count(*) from ct3 group by c7 print ====> sql : select count(*) from ct3 group by c7 print ====> rows: $rows if $rows != 2 then return -1 endi sql select count(*) from ct3 group by c8 print ====> sql : select count(*) from ct3 group by c8 print ====> rows: $rows if $rows != 8 then return -1 endi sql select count(*) from ct3 group by c9 print ====> sql : select count(*) from ct3 group by c9 print ====> rows: $rows if $rows != 8 then return -1 endi sql select count(*) from ct3 group by c10 print ====> sql : select count(*) from ct3 group by c10 print ====> rows: $rows if $rows != 8 then return -1 endi print ================ query 2 complex with group by sql select count(c1) from ct3 where c1 > 2 group by c7 limit 1 offset 1 print ====> sql : select count(c1) from ct3 where c1 > 2 group by c7 limit 1 offset 2 print ====> rows: $rows if $rows != 1 then return -1 endi sql select abs(c1) from ct3 where c1 > 2 group by c7 limit 1 offset 1 print ====> sql : select abs(c1) from ct3 where c1 > 2 group by c7 limit 1 offset 2 print ====> rows: $rows if $rows != 1 then return -1 endi sql select acos(c1) from ct3 where c1 > 2 group by c7 limit 1 offset 1 print ====> sql : select acos(c1) from ct3 where c1 > 2 group by c7 limit 1 offset 2 print ====> rows: $rows if $rows != 1 then return -1 endi sql select asin(c1) from ct3 where c1 > 2 group by c7 limit 1 offset 1 print ====> sql : select asin(c1) from ct3 where c1 > 2 group by c7 limit 1 offset 2 print ====> rows: $rows if $rows != 1 then return -1 endi sql select atan(c1) from ct3 where c1 > 2 group by c7 limit 1 offset 1 print ====> sql : select atan(c1) from ct3 where c1 > 2 group by c7 limit 1 offset 2 print ====> rows: $rows if $rows != 1 then return -1 endi sql select ceil(c1) from ct3 where c1 > 2 group by c7 limit 1 offset 1 print ====> sql : select ceil(c1) from ct3 where c1 > 2 group by c7 limit 1 offset 2 print ====> rows: $rows if $rows != 1 then return -1 endi sql select cos(c1) from ct3 where c1 > 2 group by c7 limit 1 offset 1 print ====> sql : select cos(c1) from ct3 where c1 > 2 group by c7 limit 1 offset 2 print ====> rows: $rows if $rows != 1 then return -1 endi sql select floor(c1) from ct3 where c1 > 2 group by c7 limit 1 offset 1 print ====> sql : select floor(c1) from ct3 where c1 > 2 group by c7 limit 1 offset 2 print ====> rows: $rows if $rows != 1 then return -1 endi sql select log(c1,10) from ct3 where c1 > 2 group by c7 limit 1 offset 1 print ====> sql : select log(c1,10) from ct3 where c1 > 2 group by c7 limit 1 offset 2 print ====> rows: $rows if $rows != 1 then return -1 endi sql select pow(c1,3) from ct3 where c1 > 2 group by c7 limit 1 offset 1 print ====> sql : select pow(c1,3) from ct3 where c1 > 2 group by c7 limit 1 offset 2 print ====> rows: $rows if $rows != 1 then return -1 endi sql select round(c1) from ct3 where c1 > 2 group by c7 limit 1 offset 1 print ====> sql : select round(c1) from ct3 where c1 > 2 group by c7 limit 1 offset 2 print ====> rows: $rows if $rows != 1 then return -1 endi sql select sqrt(c1) from ct3 where c1 > 2 group by c7 limit 1 offset 1 print ====> sql : select sqrt(c1) from ct3 where c1 > 2 group by c7 limit 1 offset 2 print ====> rows: $rows if $rows != 1 then return -1 endi sql select sin(c1) from ct3 where c1 > 2 group by c7 limit 1 offset 1 print ====> sql : select sin(c1) from ct3 where c1 > 2 group by c7 limit 1 offset 2 print ====> rows: $rows if $rows != 1 then return -1 endi sql select tan(c1) from ct3 where c1 > 2 group by c7 limit 1 offset 1 print ====> sql : select tan(c1) from ct3 where c1 > 2 group by c7 limit 1 offset 2 print ====> rows: $rows if $rows != 1 then return -1 endi #system sh/exec.sh -n dnode1 -s stop -x SIGINT