complex_select.sim

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 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 limit/offset
sql select * from ct1 limit 1
print ====> sql : select * from ct1 limit 1
print ====> rows: $rows
print ====> rows0: $data00, $data01, $data02, $data03, $data04, $data05, $data06, $data07, $data08, $data09
if $rows != 1 then
    return -1
endi

sql select * from ct1 limit 9
print ====> sql : select * from ct1 limit 9
print ====> rows: $rows
if $rows != 8 then
    return -1
endi

sql select * from ct1 limit 1 offset 2
print ====> sql : select * from ct1 limit 1 offset 2
print ====> rows: $rows
print ====> rows0: $data00, $data01, $data02, $data03, $data04, $data05, $data06, $data07, $data08, $data09
if $rows != 1 then
    return -1
endi
if $data01 != 3 then
    return -1
endi

sql select * from ct1 limit 2 offset 7
print ====> sql : select * from ct1 limit 2 offset 7
print ====> rows: $rows
if $rows != 1 then
    return -1
endi
if $data01 != 8 then
    return -1
endi

sql select * from ct1 limit 2 offset 7
print ====> sql : select * from ct1 limit 2 offset 7
print ====> rows: $rows
print ====> rows0: $data00, $data01, $data02, $data03, $data04, $data05, $data06, $data07, $data08, $data09
if $rows != 1 then
    return -1
endi
if $data01 != 2 then
    return -1
endi
if $data11 != 3 then
    return -1
endi

sql select * from ct1 limit 2 offset 10
print ====> sql : select * from ct1 limit 2 offset 7
print ====> rows: $rows
if $rows != 0 then
    return -1
endi

sql select c1 from stb1 limit 1
print ====> sql : select c1 from stb1 limit 1
print ====> rows: $rows
print ====> rows0: $data00
if $rows != 1 then
    return -1
endi

sql select c1 from stb1 limit 50
print ====> sql : select c1 from stb1 limit 50
print ====> rows: $rows
if $rows != 33 then
    return -1
endi

sql select c1 from stb1 limit 1 offset 2
print ====> sql : select c1 from stb1 limit 1 offset 2
print ====> rows: $rows
if $rows != 1 then
    return -1
endi

sql select c1 from stb1 limit 2 offset 1
print ====> sql : select c1 from stb1 limit 2 offset 1
print ====> rows: $rows
if $rows != 2 then
    return -1
endi

sql select c1 from stb1 limit 2 offset 32
print ====> sql : select c1 from stb1 limit 2 offset 32
print ====> rows: $rows
if $rows != 1 then
    return -1
endi

sql select c1 from stb1 limit 2 offset 40
print ====> sql : select c1 from stb1 limit 2 offset 40
print ====> rows: $rows
if $rows != 0 then
    return -1
endi

print ================ query 2 where condition
sql select * from ct3 where c1 < 5
print ====> sql : select * from ct3 where c1 < 5
print ====> rows: $rows
print ====> rows0: $data00, $data01, $data02, $data03, $data04, $data05, $data06, $data07, $data08, $data09
if $rows != 4 then
    return -1
endi
if $data01 != 1 then
    return -1
endi

sql select * from ct3 where c1 > 5 and c1 <= 6
print ====> sql : select * from ct3 where c1 > 5 and c1 <= 6
print ====> rows: $rows
print ====> rows0: $data00, $data01, $data02, $data03, $data04, $data05, $data06, $data07, $data08, $data09
if $rows != 1 then
    return -1
endi
if $data01 != 6 then
    return -1
endi

sql select * from ct3 where c1 >= 5 or c1 != 4 or c1 <> 3 or c1 = 2
print ====> sql : select * from ct3 where c1 > 5 and c1 <= 6
print ====> rows: $rows
print ====> rows0: $data00, $data01, $data02, $data03, $data04, $data05, $data06, $data07, $data08, $data09
if $rows != 8 then
    return -1
endi
if $data01 != 1 then
    return -1
endi

sql select * from ct3 where c1 >= 5 and c1 is not NULL
print ====> sql : select * from ct3 where c1 >= 5 and c1 is not NULL
print ====> rows: $rows
print ====> rows0: $data00, $data01, $data02, $data03, $data04, $data05, $data06, $data07, $data08, $data09
if $rows != 4 then
    return -1
endi
if $data01 != 5 then
    return -1
endi

sql_error select ts from ct3 where ts != 0
sql select * from ct3 where ts <> 0
print ====> sql : select * from ct3 where ts <> 0
print ====> rows: $rows
if $rows != 8 then
    return -1
endi

sql select * from ct3 where c1 between 1 and 3
print ====> sql : select * from ct3 where c1 between 1 and 3
print ====> rows: $rows
if $rows != 3 then
    return -1
endi

sql_error select * from ct3 where c7 between false and true

sql select * from ct3 where c1 in (1,2,3)
print ====> sql : select * from ct3 where c1 in (1,2,3)
print ====> rows: $rows
if $rows != 3 then
    return -1
endi

sql select * from ct3 where c1 in (‘true','false')
print ====> sql : select * from ct3 where c1 in (‘true','false')
print ====> rows: $rows
if $rows != 8 then
    return -1
endi

sql select * from ct3 where c9 like "_char_"
print ====> sql : select * from ct3 where c1 in (1,2,3)
print ====> rows: $rows
if $rows != 8 then
    return -1
endi

sql select * from ct3 where c8 like "bi%"
print ====> sql : select * from ct3 where c1 in (1,2,3)
print ====> rows: $rows
if $rows != 8 then
    return -1
endi

sql select c1 from stb1 where c1 < 5
print ====> sql : select c1 from stb1 where c1 < 5
print ====> rows: $rows
print ====> rows0: $data00
if $rows != 16 then
    return -1
endi
if $data01 != 1 then
    return -1
endi

sql select c1 from stb1 where stb1 > 5 and c1 <= 6
print ====> sql : select c1 from stb1 where c1 > 5 and c1 <= 6
print ====> rows: $rows
print ====> rows0: $data00
if $rows != 4 then
    return -1
endi
if $data01 != 6 then
    return -1
endi

sql select c1 from stb1 where c1 >= 5 or c1 != 4 or c1 <> 3 or c1 = 2
print ====> sql : sselect c1 from stb1 where c1 >= 5 or c1 != 4 or c1 <> 3 or c1 = 2
print ====> rows: $rows
print ====> rows0: $data00
if $rows != 32 then
    return -1
endi
if $data01 != 1 then
    return -1
endi

sql select c1 from stb1 where c1 >= 5 and c1 is not NULL
print ====> sql : select c1 from stb1 where c1 >= 5 and c1 is not NULL
print ====> rows: $rows
print ====> rows0: $data00
if $rows != 17 then
    return -1
endi
if $data01 != 5 then
    return -1
endi

sql_error select ts from stb1 where ts != 0
sql select c1 from stb1 where ts <> 0
print ====> sql : select c1 from stb1 where ts <> 0
print ====> rows: $rows
if $rows != 32 then
    return -1
endi

sql select c1 from stb1 where c1 between 1 and 3
print ====> sql : select c1 from stb1 where c1 between 1 and 3
print ====> rows: $rows
if $rows != 12 then
    return -1
endi

sql_error select c1 from stb1 where c7 between false and true

sql select c1 from stb1 where c1 in (1,2,3)
print ====> sql : select c1 from stb1 where c1 in (1,2,3)
print ====> rows: $rows
if $rows != 12 then
    return -1
endi

sql select c1 from stb1 where c1 in (‘true','false')
print ====> sql : select c1 from stb1 where c1 in (‘true','false')
print ====> rows: $rows
if $rows != 32 then
    return -1
endi

sql select c1 from stb1 where c9 like "_char_"
print ====> sql : select c1 from stb1 where c9 like "_char_"
print ====> rows: $rows
if $rows != 32 then
    return -1
endi

sql select c1 from stb1 where c8 like "bi%"
print ====> sql : select c1 from stb1 where c8 like "bi%"
print ====> rows: $rows
if $rows != 32 then
    return -1
endi


print ================ query 3 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 4 scalar function + where + group by + limit/offset
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


system sh/exec.sh -n dnode1 -s stop -x SIGINT