system sh/stop_dnodes.sh system sh/deploy.sh -n dnode1 -i 1 system sh/cfg.sh -n dnode1 -c walLevel -v 0 system sh/cfg.sh -n dnode1 -c tableMetaKeepTimer -v 3 system sh/exec.sh -n dnode1 -s start sleep 100 sql connect $dbPrefix = m_func_db $tbPrefix = m_func_tb $mtPrefix = m_func_mt $tbNum = 10 $rowNum = 5 $totalNum = $tbNum * $rowNum $ts0 = 1537146000000 $delta = 600000 print ========== alter.sim $i = 0 $db = $dbPrefix . $i $mt = $mtPrefix . $i sql drop database if exists $db sql create database $db keep 36500 sql use $db print =====================================> test case for twa in single block sql create table t1 (ts timestamp, k float); sql insert into t1 values('2015-08-18 00:00:00', 2.064); sql insert into t1 values('2015-08-18 00:06:00', 2.116); sql insert into t1 values('2015-08-18 00:12:00', 2.028); sql insert into t1 values('2015-08-18 00:18:00', 2.126); sql insert into t1 values('2015-08-18 00:24:00', 2.041); sql insert into t1 values('2015-08-18 00:30:00', 2.051); sql select twa(k),avg(k),count(1) from t1 where ts>='2015-8-18 00:00:00' and ts<='2015-8-18 00:05:00' if $rows != 1 then return -1 endi if $data00 != 2.063999891 then return -1 endi if $data01 != 2.063999891 then return -1 endi if $data02 != 1 then return -1 endi sql select twa(k),avg(k),count(1) from t1 where ts>='2015-8-18 00:00:00' and ts<='2015-8-18 00:07:00' if $rows != 1 then return -1 endi if $data00 != 2.089999914 then return -1 endi if $data01 != 2.089999914 then return -1 endi if $data02 != 2 then return -1 endi sql select twa(k),avg(k),count(1) from t1 where ts>='2015-8-18 00:00:00' and ts<='2015-8-18 00:07:00' interval(1m) order by ts asc if $rows != 2 then return -1 endi if $data00 != @15-08-18 00:00:00.000@ then return -1 endi if $data01 != 2.068333156 then return -1 endi if $data02 != 2.063999891 then return -1 endi if $data03 != 1 then return -1 endi if $data10 != @15-08-18 00:06:00.000@ then return -1 endi if $data11 != 2.115999937 then return -1 endi if $data12 != 2.115999937 then return -1 endi if $data13 != 1 then return -1 endi sql select twa(k),avg(k),count(1) from t1 where ts>='2015-8-18 00:00:00' and ts<='2015-8-18 00:07:00' interval(1m) order by ts desc; if $rows != 2 then return -1 endi if $data00 != @15-08-18 00:06:00.000@ then return -1 endi if $data01 != 2.115999937 then return -1 endi if $data02 != 2.115999937 then return -1 endi if $data03 != 1 then return -1 endi if $data11 != 2.068333156 then return -1 endi sql select twa(k),avg(k),count(1) from t1 where ts>='2015-8-18 00:00:00' and ts<='2015-8-18 00:27:00' interval(10m) order by ts asc if $rows != 3 then return -1 endi if $data01 != 2.088666666 then return -1 endi if $data02 != 2.089999914 then return -1 endi if $data03 != 2 then return -1 endi if $data11 != 2.077099980 then return -1 endi if $data12 != 2.077000022 then return -1 endi if $data13 != 2 then return -1 endi if $data21 != 2.069333235 then return -1 endi if $data22 != 2.040999889 then return -1 endi if $data23 != 1 then return -1 endi sql select twa(k),avg(k),count(1) from t1 where ts>='2015-8-18 00:00:00' and ts<='2015-8-18 00:27:00' interval(10m) order by ts desc if $rows != 3 then return -1 endi if $data01 != 2.069333235 then return -1 endi if $data11 != 2.077099980 then return -1 endi if $data21 != 2.088666666 then return -1 endi sql select twa(k),avg(k),count(1) from t1 where ts>='2015-8-18 00:00:00' and ts<='2015-8-18 00:30:00' order by ts asc if $data00 != 2.073699975 then return -1 endi if $data01 != 2.070999980 then return -1 endi if $data02 != 6 then return -1 endi sql select twa(k),avg(k),count(1) from t1 where ts>='2015-8-18 00:00:00' and ts<='2015-8-18 00:30:00' order by ts desc if $rows != 1 then return -1 endi if $data00 != 2.073699975 then return -1 endi if $data01 != 2.070999980 then return -1 endi if $data02 != 6 then return -1 endi sql select twa(k) from t1 where ts>'2015-8-18 00:00:00' and ts<'2015-8-18 00:00:1' if $rows != 0 then return -1 endi sql select twa(k),avg(k),count(1) from t1 where ts>='2015-8-18 00:00:00' and ts<='2015-8-18 00:30:00' interval(10m) order by ts asc sql select twa(k),avg(k),count(1) from t1 where ts>='2015-8-18 00:00:00' and ts<='2015-8-18 00:30:00' interval(10m) order by ts desc #todo add test case while column filter exists. #sql select count(*),TWA(k) from tm0 where ts>='1970-1-1 13:43:00' and ts<='1970-1-1 13:44:10' interval(9s) sql create table tm0 (ts timestamp, k float); sql insert into tm0 values(100000000, 5); sql insert into tm0 values(100003000, -9); sql select twa(k) from tm0 where ts td-2610 sql select twa(k)from tm1 where ts>='2020-11-19 18:11:45.773' and ts<='2020-12-9 18:11:17.098' if $rows != 0 then return -1 endi print =====================> td-2609 sql select apercentile(k, 50) from tm1 where ts>='2020-10-30 18:11:56.680' and ts<='2020-12-09 18:11:17.098' if $rows != 1 then return -1 endi if $data00 != -1000.000000000 then return -1 endi system sh/exec.sh -n dnode1 -s stop -x SIGINT sleep 1000 system sh/exec.sh -n dnode1 -s start print ================== server restart completed sql connect sleep 100 sql use m_func_db0 print =====================> td-2583 sql select min(k) from tm1 where ts>='2020-11-19 18:11:45.773' and ts<='2020-12-20 18:11:49.412' if $rows != 1 then return -1 endi if $data00 != 1 then print expect 1, actual: $data00 return -1 endi print =====================> td-2601 sql select count(*) from tm1 where ts<='2020-6-1 00:00:00' and ts>='2020-1-1 00:00:00' interval(1n) fill(NULL) if $rows != 0 then return -1 endi print =====================> td-2615 sql select last(ts) from tm1 interval(17a) limit 776 offset 3 if $rows != 3 then return -1 endi sql select last(ts) from tm1 interval(17a) limit 1000 offset 4 if $rows != 2 then return -1 endi sql select last(ts) from tm1 interval(17a) order by ts desc limit 1000 offset 0 if $rows != 6 then return -1 endi print ==================> td-2624 sql create table tm2(ts timestamp, k int, b binary(12)); sql insert into tm2 values('2011-01-02 18:42:45.326', -1,'abc'); sql insert into tm2 values('2020-07-30 17:44:06.283', 0, null); sql insert into tm2 values('2020-07-30 17:44:19.578', 9999999, null); sql insert into tm2 values('2020-07-30 17:46:06.417', NULL, null); sql insert into tm2 values('2020-11-09 18:42:25.538', 0, null); sql insert into tm2 values('2020-12-29 17:43:11.641', 0, null); sql insert into tm2 values('2020-12-29 18:43:17.129', 0, null); sql insert into tm2 values('2020-12-29 18:46:19.109', NULL, null); sql insert into tm2 values('2021-01-03 18:40:40.065', 0, null); sql select twa(k),first(ts) from tm2 where k <50 interval(17s); if $rows != 6 then return -1 endi if $data00 != @11-01-02 18:42:42.000@ then return -1 endi if $data02 != @11-01-02 18:42:45.326@ then return -1 endi if $data10 != @20-07-30 17:43:59.000@ then return -1 endi if $data21 != 0.000000000 then return -1 endi sql select twa(k),first(ts) from tm2 where k <50 interval(17s) order by ts desc; if $rows != 6 then return -1 endi sql select twa(k),first(ts),count(k),first(k) from tm2 interval(17s) limit 20 offset 0; if $rows != 9 then return -1 endi if $data00 != @11-01-02 18:42:42.000@ then return -1 endi if $data10 != @20-07-30 17:43:59.000@ then return -1 endi print =================>td-2610 sql select stddev(k) from tm2 where ts='2020-12-29 18:46:19.109' if $rows != 0 then print expect 0, actual:$rows return -1 endi sql select twa(k) from tm2 where ts='2020-12-29 18:46:19.109' if $rows != 0 then return -1 endi