function.sim

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/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 for twa query

#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<now
if $rows != 1 then
  return -1
endi

if $data00 != -2.000000000 then
  print expect -2.000000000, actual: $data00
  return -1
endi

sql create table tm1 (ts timestamp,  k int);
sql insert into tm1 values('2020-10-30 18:11:56.680', -1000);
sql insert into tm1 values('2020-11-19 18:11:45.773', NULL);
sql insert into tm1 values('2020-12-09 18:11:17.098', NULL);
sql insert into tm1 values('2020-12-20 18:11:49.412', 1);
sql insert into tm1 values('2020-12-23 18:11:50.412', 2);
sql insert into tm1 values('2020-12-28 18:11:52.412', 3);

print =====================> 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

print ========================> TD-1787
sql create table cars(ts timestamp, c int) tags(id int);
sql create table car1 using cars tags(1);
sql create table car2 using cars tags(2);
sql insert into car1 (ts, c) values (now,1) car2(ts, c) values(now, 2);
sql drop table cars;
sql create table cars(ts timestamp, c int) tags(id int);
sql create table car1 using cars tags(1);
sql create table car2 using cars tags(2);
sql insert into car1 (ts, c) values (now,1) car2(ts, c) values(now, 2);

print ========================> TD-2700
sql create table tx(ts timestamp, k int);
sql insert into tx values(1500000001000, 0);
sql select sum(k) from tx interval(1d) sliding(1h);
if $rows != 24 then
  print expect 24, actual:$rows
  return -1
endi

print ========================> TD-2740
sql drop table if exists m1;
sql create table m1(ts timestamp, k int) tags(a int);
sql create table tm10 using m1 tags(0);
sql create table tm11 using m1 tags(1);
sql create table tm12 using m1 tags(2);
sql create table tm13 using m1 tags(3);
sql insert into tm10 values('2020-1-1 1:1:1', 0);
sql insert into tm11 values('2020-1-5 1:1:1', 0);
sql insert into tm12 values('2020-1-7 1:1:1', 0);
sql insert into tm13 values('2020-1-1 1:1:1', 0);
sql select count(*) from m1 where ts='2020-1-1 1:1:1' interval(1h) group by tbname;
if $rows != 2 then
  return -1
endi

sql drop table m1;
sql drop table if exists tm1;
sql drop table if exists tm2;
sql create table m1(ts timestamp, k double, b double, c int, d smallint, e int unsigned) tags(a int);
sql create table tm1 using m1 tags(1);
sql create table tm2 using m1 tags(2);
sql insert into tm1 values('2021-01-27 22:22:39.294', 1, 10, NULL, 110, 123) ('2021-01-27 22:22:40.294', 2, 20, NULL, 120, 124) ('2021-01-27 22:22:41.294', 3, 30, NULL, 130, 125)('2021-01-27 22:22:43.294', 4, 40, NULL, 140, 126)('2021-01-27 22:22:44.294', 5, 50, NULL, 150, 127);
sql insert into tm2 values('2021-01-27 22:22:40.688', 5, 101, NULL, 210, 321) ('2021-01-27 22:22:41.688', 5, 102, NULL, 220, 322) ('2021-01-27 22:22:42.688', 5, 103, NULL, 230, 323)('2021-01-27 22:22:43.688', 5, 104, NULL, 240, 324)('2021-01-27 22:22:44.688', 5, 105, NULL, 250, 325)('2021-01-27 22:22:45.688', 5, 106, NULL, 260, 326);
sql select stddev(k) from m1
if $rows != 1 then
  return -1
endi

if $data00 != 1.378704626 then
  return -1
endi

sql select stddev(c) from m1
if $rows != 0 then
  return -1
endi

sql select stddev(k), stddev(c) from m1
if $rows != 1 then
  return -1
endi

if $data00 != 1.378704626 then
  return -1
endi

if $data01 != NULL then
  return -1;
endi

sql select stddev(b),stddev(b),stddev(k) from m1;
if $rows != 1 then
  return -1
endi

if $data00 != 37.840465463 then
  return -1
endi

if $data01 != 37.840465463 then
  return -1
endi

if $data02 != 1.378704626 then
  return -1
endi

sql select stddev(k), stddev(b) from m1 group by a
if $rows != 2 then
  return -1
endi

if $data00 != 1.414213562 then
  return -1
endi

if $data01 != 14.142135624 then
  return -1
endi

if $data02 != 1 then
  return -1
endi

if $data10 != 0.000000000 then
  return -1
endi

if $data11 != 1.707825128 then
  return -1
endi

if $data12 != 2 then
  return -1
endi

sql select stddev(k), stddev(b) from m1 where a= 1 group by a
if $rows != 1 then
  return -1
endi

if $data00 != 1.414213562 then
 return -1
endi

if $data01 != 14.142135624 then
  return -1
endi

if $data02 != 1 then
 return -1
endi

sql select stddev(k), stddev(b) from m1 group by tbname
if $rows != 2 then
  return -1
endi

if $data00 != 1.414213562 then
  return -1
endi

if $data01 != 14.142135624 then
  return -1
endi

if $data02 != @tm1@ then
  return -1
endi

if $data10 != 0.000000000 then
  return -1
endi

if $data11 != 1.707825128 then
  return -1
endi

if $data12 != @tm2@ then
  return -1
endi

sql select stddev(k), stddev(b) from m1 group by tbname,a
if $rows != 2 then
  return -1
endi

sql select stddev(k), stddev(b), stddev(c) from m1  group by tbname,a
if $rows != 2 then
  return -1
endi

if $data00 != 1.414213562 then
  return -1
endi

if $data01 != 14.142135624 then
  return -1
endi

if $data02 != NULL then
  return -1
endi

if $data03 != @tm1@ then
  return -1
endi

if $data04 != 1 then
  return -1
endi

if $data10 != 0.000000000 then
  return -1
endi

if $data11 != 1.707825128 then
  return -1
endi

if $data12 != NULL then
  return -1
endi

if $data13 != @tm2@ then
  return -1
endi

if $data14 != 2 then
  return -1
endi

sql select stddev(k), stddev(b), stddev(c) from m1 interval(10s) group by tbname,a
if $rows != 3 then
  return -1
endi

if $data01 != 0.000000000 then
  return -1
endi

if $data02 != 0.000000000 then
  return -1
endi

if $data03 != NULL then
  return -1
endi

if $data04 != @tm1@ then
  return -1
endi

if $data05 != 1 then
  return -1
endi

if $data11 != 1.118033989 then
  return -1
endi

if $data12 != 11.180339887 then
  return -1
endi

if $data13 != NULL then
  return -1
endi

if $data14 != @tm1@ then
  return -1
endi

if $data22 != 1.707825128 then
  return -1
endi

if $data23 != NULL then
  return -1
endi

if $data24 != @tm2@ then
  return -1
endi

if $data25 != 2 then
  return -1
endi

sql select count(*), first(b), stddev(b), stddev(c) from m1 interval(10s) group by a
if $rows != 3 then
  return -1
endi

if $data00 != @21-01-27 22:22:30.000@ then
  return -1
endi

if $data01 != 1 then
  return -1
endi

if $data02 != 10.000000000 then
  return -1
endi

if $data03 != 0.000000000 then
  return -1
endi

if $data04 != NULL then
  return -1
endi

if $data05 != 1 then
  return -1
endi

if $data12 != 20.000000000 then
  return -1
endi

if $data13 != 11.180339887 then
  return -1
endi

if $data14 != NULL then
  return -1
endi

if $data23 != 1.707825128 then
  return -1
endi

sql select count(*), first(b), stddev(b), stddev(c) from m1 interval(10s) group by tbname,a
if $rows != 3 then
  return -1
endi

if $data23 != 1.707825128 then
  return -1
endi

if $data25 != @tm2@ then
  return -1
endi

sql select count(*), stddev(b), stddev(b)+20, stddev(c) from m1 interval(10s) group by tbname,a
if $rows != 3 then
  return -1
endi

if $data02 != 0.000000000 then
  return -1
endi

if $data03 != 20.000000000 then
  return -1
endi

if $data13 != 31.180339887 then
  return -1
endi

if $data14 != NULL then
  return -1
endi

sql select count(*), first(b), stddev(b)+first(b), stddev(c) from m1 interval(10s) group by tbname,a
if $rows != 3 then
  return -1
endi

if $data02 != 10.000000000 then
  return -1
endi

if $data03 != 10.000000000 then
  return -1
endi

if $data12 != 20.000000000 then
  return -1
endi

if $data13 != 31.180339887 then
  return -1
endi

if $data22 != 101.000000000 then
  return -1
endi

if $data23 != 102.707825128 then
  return -1
endi

sql select stddev(e),stddev(k) from m1 where a=1
if $rows != 1 then
  return -1
endi

if $data00 != 1.414213562 then
  return -1
endi

if $data01 != 1.414213562 then
  return -1
endi

sql create stable st1 (ts timestamp, f1 int, f2 int) tags (id int);
sql create table tb1 using st1 tags(1);

sql insert into tb1 values (now, 1, 1);

sql select stddev(f1) from st1 group by f1;

if $rows != 1 then
  return -1
endi


if $data00 != 0.000000000 then
  return -1
endi