complex_having.sim

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