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 maxtablespervnode -v 4

system sh/exec.sh -n dnode1 -s start
sql connect
sleep 1000

$dbPrefix = join_m_db
$tbPrefix = join_tb
$mtPrefix = join_mt
$tbNum = 3
$rowNum = 1000
$totalNum = $tbNum * $rowNum

print =============== join_multivnode.sim
$i = 0
$db = $dbPrefix . $i
$mt = $mtPrefix . $i

$tstart = 100000

sql drop database if exists $db -x step1
step1:
sql create database if not exists $db keep 36500
sql use $db
sql create table $mt (ts timestamp, c1 int, c2 float, c3 bigint, c4 smallint, c5 tinyint, c6 double, c7 bool, c8 binary(10), c9 nchar(9)) TAGS(t1 int, t2 binary(12))

$i = 0
while $i < $tbNum
  $tb = $tbPrefix . $i
  $tg2 = ' . abc
  $tg2 = $tg2 . '
  sql create table $tb using $mt tags( $i , $tg2 )

  $x = 0
  while $x < $rowNum
    $ms = $x . m
    $c = $x / 100
    $c = $c * 100
    $c = $x - $c

    $binary = ' . binary
    $binary = $binary . $c
    $binary = $binary . '

    $nchar = ' . nchar
    $nchar = $nchar . $c
    $nchar = $nchar . '

    sql insert into $tb values ($tstart , $c , $c , $c , $c , $c , $c , $c , $binary , $nchar )
    $tstart = $tstart + 1
    $x = $x + 1
  endw

  $i = $i + 1
  $tstart = 100000
endw

sleep 100

$tstart = 100000
$mt = $mtPrefix . 1 . $i
sql create table $mt (ts timestamp, c1 int, c2 float, c3 bigint, c4 smallint, c5 tinyint, c6 double, c7 bool, c8 binary(10), c9 nchar(9)) TAGS(t1 int, t2 binary(12), t3 int)

$i = 0
$tbPrefix = join_1_tb

while $i < $tbNum
  $tb = $tbPrefix . $i
  $c = $i
  $t3 = $i + 1

  $binary = ' . abc
  $binary = $binary . $i
  $binary = $binary . '

  print $binary
  sql create table $tb using $mt tags( $i , $binary , $t3 )

  $x = 0
  while $x < $rowNum
    $ms = $x . m
    $c = $x / 100
    $c = $c * 100
    $c = $x - $c

    $binary = ' . binary
    $binary = $binary . $c
    $binary = $binary . '

    $nchar = ' . nchar
    $nchar = $nchar . $c
    $nchar = $nchar . '

    sql insert into $tb values ($tstart , $c , $c , $c , $c , $c , $c , $c , $binary , $nchar )
    $tstart = $tstart + 1
    $x = $x + 1
  endw

  $i = $i + 1
  $tstart = 100000
endw

print ===============multivnode projection join.sim

sql select join_mt0.ts,join_mt0.ts,join_mt0.t1 from join_mt0, join_mt1 where join_mt0.ts=join_mt1.ts and join_mt0.t1=join_mt1.t1;

print $row
if $row != 3000 then
  print expect 3000, actual: $row
  return -1
endi

print ======= second tags join

sql create table m1(ts timestamp, k int) tags(a binary(12), b int);
sql create table m2(ts timestamp, k int) tags(a binary(12), b int);

sql insert into tm1 using m1 tags('tm1', 1) values(10000000, 1) tm2 using m2 tags('tm2', 1) values(10000000, 99);

sql select * from m1,m2 where m1.ts=m2.ts and m1.b=m2.b;
if $row != 1 then
  return -1
endi

sql select join_mt0.ts, join_mt1.t1 from join_mt0, join_mt1 where join_mt0.ts=join_mt1.ts and join_mt0.t1=join_mt1.t1

sql select join_mt0.ts, join_mt1.t1, join_mt0.t1, join_mt1.tbname, join_mt0.tbname from join_mt0, join_mt1 where join_mt0.ts=join_mt1.ts and join_mt0.t1=join_mt1.t1

sql select join_mt0.ts, join_mt1.t1, join_mt0.t1, join_mt1.tbname, join_mt0.tbname from join_mt0, join_mt1 where join_mt0.ts=join_mt1.ts and join_mt0.t1=join_mt1.t1 limit 1

#1970-01-01 08:01:40.800 |                    10 |              45.000000000 |         0 |     true |     false |           0 |
#1970-01-01 08:01:40.790 |                    10 |             945.000000000 |        90 |     true |      true |           0 |

sql select count(join_mt0.c1), sum(join_mt1.c2), first(join_mt0.c5), last(join_mt1.c7), first(join_mt1.c7) from join_mt0, join_mt1 where join_mt0.t1=join_mt1.t1 and join_mt0.ts=join_mt1.ts interval(10a) group by join_mt0.t1 order by join_mt0.ts desc limit 20 offset 19;
if $rows != 100 then
  return -1
endi

# c5 is null ! error

sql select count(join_mt0.c1), sum(join_mt0.c2)/count(*), avg(c2), first(join_mt0.c5), last(c7) from join_mt0  interval(10a) group by join_mt0.t1 order by join_mt0.ts desc;
if $rows != 100 then
  return -1
endi

if $data00 != @70-01-01 08:01:40.990@ then
  print expect 0, actual: $data00
  return -1
endi

if $data01 != 30 then
  return -1
endi

if $data02 != 94.500000000 then
  print expect 94.500000000, actual $data02
  return -1
endi

if $data03 != 94.500000000 then
  return -1
endi

if $data04 != 90 then
  return -1
endi

if $data05 != 1 then
  return -1
endi

if $data06 != 2 then
  return -1
endi

if $data10 != @70-01-01 08:01:40.980@ then
  print expect 70-01-01 08:01:40.980, actual: $data10
  return -1
endi

if $data11 != 30 then
  return -1
endi

if $data12 != 84.500000000 then
  print expect 84.500000000, actual $data12
  return -1
endi

if $data13 != 84.500000000 then
  return -1
endi

if $data14 != 80 then
  return -1
endi

if $data15 != 1 then
  return -1
endi

if $data16 != 2 then
  return -1
endi

# this function will cause shell crash
sql select count(join_mt0.c1), first(join_mt0.c1) from join_mt0, join_mt1 where join_mt0.t1=join_mt1.t1 and join_mt0.ts=join_mt1.ts interval(10a) group by join_mt0.t1 order by join_mt0.ts desc;
if $rows != 100 then
  return -1
endi

if $data00 != @70-01-01 08:01:40.990@ then
  return -1
endi

if $data01 != 10 then
  return -1
endi

if $data02 != 90 then
  return -1
endi

if $data03 != 0 then
  return -1
endi

if $data11 != 10 then
  return -1
endi

if $data12 != 80 then
  return -1
endi

if $data13 != 0 then
  return -1
endi

sql select last(join_mt1.c7), first(join_mt1.c7) from join_mt0, join_mt1 where join_mt0.t1=join_mt1.t1 and join_mt0.ts=join_mt1.ts interval(10m) group by join_mt0.t1 order by join_mt0.ts asc;
if $rows != 1 then
 return -1
endi

if $data00 != @70-01-01 08:00:00.000@ then
  return -1
endi

if $data01 != 1 then
  return -1
endi

if $data02 != 0 then
  return -1
endi

if $data03 != 0 then
  return -1
endi

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