fill_stb.sim

system sh/stop_dnodes.sh
system sh/deploy.sh -n dnode1 -i 1
system sh/exec.sh -n dnode1 -s start
sql connect

$dbPrefix = fl1_db
$tbPrefix = fl1_tb
$stbPrefix = fl1_stb
$tbNum = 10
$rowNum = 10
$totalNum = $tbNum * $rowNum
$ts0 = 1537146000000
$delta = 600000
print ========== fill.sim
$i = 0
$db = $dbPrefix . $i
$stb = $stbPrefix . $i

sql drop database $db -x step1
step1:
sql create database $db
sql use $db
sql create table $stb (ts timestamp, c1 int, c2 bigint, c3 float, c4 double, c5 smallint, c6 tinyint, c7 bool, c8 binary(10), c9 nchar(10)) tags(t1 int, t2 nchar(20), t3 binary(20), t4 bigint, t5 bool, t6 double)

$i = 0
$ts = $ts0
$halfNum = $tbNum / 2
while $i < $halfNum
  $i1 = $i + $halfNum
  $tb = $tbPrefix . $i
  $tb1 = $tbPrefix . $i1
  $tgstr = 'tb . $i
  $tgstr = $tgstr . '
  $tgstr1 = 'tb . $i1
  $tgstr1 = $tgstr1 . '
  sql create table $tb using $stb tags( $i , $tgstr , $tgstr , $i , $i , $i )
  sql create table $tb1 using $stb tags( $i1 , $tgstr1 , $tgstr1 , $i , $i , $i )
 
  $x = 0
  while $x < $rowNum
    $xs = $x * $delta
    $ts = $ts0 + $xs
    $c = $x / 10
    $c = $c * 10
    $c = $x - $c
    $binary = 'binary . $c
    $binary = $binary . '
    $nchar = 'nchar . $c
    $nchar = $nchar . '
    sql insert into $tb values ( $ts , $c , $c , $c , $c , $c , $c , true, $binary , $nchar ) 
    sql insert into $tb1 values ( $ts , $c , NULL , $c , NULL , $c , $c , true, $binary , $nchar ) 
    $x = $x + 1
  endw 

  $i = $i + 1
endw 
print ====== tables created

# setup
$i = 0
$tb = $tbPrefix . $i
$tsu = $rowNum * $delta
$tsu = $tsu - $delta
$tsu = $tsu + $ts0

#### fill test cases for stables

# NULL values in each group
#sql select count(*) from $stb where ts >= '2018-09-16 00:00:00.000' and ts <= $tsu interval(1d) fill(prev) group by t1
#$val = $tbNum * 2
#if rows != $val then
#  return -1
#endi
#if $data00 != @18-09-16 00:00:00.000@ then
#  return -1
#endi
#if $data01 != NULL then
#  return -1
#endi
#if $data02 != NULL then
#  return -1
#endi
#if $data11 != $rowNum then
#  return -1
#endi
#if $data12 != 0 then
#  return -1
#endi
#if $data20 != @18-09-16 00:00:00.000@ then
#  return -1
#endi
#if $data21 != NULL then
#  return -1
#endi
#if $data22 != NULL then
#  return -1
#endi

# number of fill values exceeds number of selected columns
print select _wstart, count(ts), max(c1), max(c2), max(c3), max(c4), max(c5) from $stb where ts >= $ts0 and ts <= $tsu interval(5m) fill(value, -1, -2, -3, -4, -5, -6, -7, -8)
sql select _wstart, count(ts), max(c1), max(c2), max(c3), max(c4), max(c5) from $stb where ts >= $ts0 and ts <= $tsu interval(5m) fill(value, -1, -2, -3, -4, -5, -6, -7, -8)
$val = $rowNum * 2
$val = $val - 1
print $rows  $val
if $rows != $val then
  return -1
endi
if $data11 != -1 then
  return -1
endi
if $data12 != -2 then
  return -1
endi
if $data13 != -3 then
  return -1
endi
if $data14 != -4.00000 then
  print expect -4.00000, actual: $data14
  return -1
endi
if $data15 != -5.000000000 then
  print expect -5.000000000, actual: $data15
  return -1
endi
if $data31 != -1 then
  return -1
endi
if $data52 != -2 then
  return -1
endi
if $data73 != -3 then
  return -1
endi
if $data74 != -4.00000 then
  return -1
endi

## fill(value) + group by
sql select max(c1), max(c2), max(c3), max(c4), max(c5) from $stb where ts >= $ts0 and ts <= $tsu interval(5m) fill(value, -1, -2, -3, -4, -5, -6, -7, -8) group by t1
$val = $rowNum * 2
print $rowNum, $val

$val = $val - 1
$val = $val * $tbNum
print ==================== $val

if $rows != 190 then
  print expect 190, actual:$rows
  return -1
endi
if $data06 != 0 then
  return -1
endi
if $data11 != -1 then
  return -1
endi
#if $data16 != 0 then
#  return -1
#endi

# number of fill values is smaller than number of selected columns
sql select max(c1), max(c2), max(c3) from $stb where ts >= $ts0 and ts <= $tsu interval(5m) fill(value, 6, 6)
if $data11 != 6 then
  return -1
endi
if $data12 != 6 then
  return -1
endi
if $data13 != 6.00000 then
  return -1
endi
 
# unspecified filling method
sql_error select max(c1), max(c2), max(c3), max(c4), max(c5) from $stb where ts >= $ts0 and ts <= $tsu interval(5m) fill (6, 6, 6, 6, 6)

# fill_char_values_to_arithmetic_fields
sql_error select sum(c1), avg(c2), max(c3), min(c4), avg(c4), count(c6), last(c7), last(c8) from $stb where ts >= $ts0 and ts <= $tsu interval(5m) fill(value, 'c', 'c', 'c', 'c', 'c', 'c', 'c', 'c')

# fill_multiple_columns
sql_error select sum(c1), avg(c2), min(c3), max(c4), count(c6), first(c7), last(c8) from $stb where ts >= $ts0 and ts <= $tsu interval(5m) fill(value, 99, 99, 99, 99, 99, abc, abc)
sql select sum(c1), avg(c2), min(c3), max(c4) from $stb where ts >= $ts0 and ts <= $tsu interval(5m) fill(value, 99, 99, 99, 99)
$val = $rowNum * 2
$val = $val - 1
if $rows != $val then
  return -1
endi
if $data01 != 0 then
  return -1
endi
if $data11 != 99 then
  return -1
endi

sql select * from $stb
#print data08 = $data08
if $data09 != nchar0 then
  return -1
endi
sql select max(c4) from $stb where t1 > 4 and ts >= $ts0 and ts <= $tsu interval(5m) fill(value, -1) group by t1
if $rows != 0 then
  return -1
endi
sql select min(c1), max(c4) from $stb where t1 > 4 and ts >= $ts0 and ts <= $tsu interval(5m) fill(value, -1)
$val = $rowNum * 2
$val = $val - 1
if $rows != $val then
  return -1
endi
if $data00 != @18-09-17 09:00:00.000@ then
  return -1
endi
if $data01 != 0 then
  return -1
endi
if $data02 != -1.000000000 then
  return -1
endi
if $data11 != -1 then
  return -1
endi
if $data12 != -1.000000000 then
  return -1
endi

# fill_into_nonarithmetic_fieds
sql select first(c7), first(c8), first(c9) from $stb where ts >= $ts0 and ts <= $tsu interval(5m) fill(value, 20000000, 20000000, 20000000)
#if $data11 != 20000000 then
if $data11 != 1 then
  return -1
endi

sql select first(c7), first(c8), first(c9) from $stb where ts >= $ts0 and ts <= $tsu interval(5m) fill(value, 1, 1, 1)
sql select first(c7), first(c8), first(c9) from $stb where ts >= $ts0 and ts <= $tsu interval(5m) fill(value, 1.1, 1.1, 1.1)
sql select first(c7), first(c8), first(c9) from $stb where ts >= $ts0 and ts <= $tsu interval(5m) fill(value, 1e1, 1e1, 1e1)
sql select first(c8), first(c9) from $stb where ts >= $ts0 and ts <= $tsu interval(5m) fill(value, '1e', '1e1')
# fill quoted values into bool column will throw error unless the value is 'true' or 'false' Note:2018-10-24
# fill values into binary or nchar columns will be set to NULL automatically Note:2018-10-24
sql_error select first(c7), first(c8), first(c9) from $stb where ts >= $ts0 and ts <= $tsu interval(5m) fill(value, '1e', '1e1','1e1')
sql select first(c7), first(c8), first(c9) from $stb where ts >= $ts0 and ts <= $tsu interval(5m) fill(value, true, true, true)
sql select first(c7), first(c8), first(c9) from $stb where ts >= $ts0 and ts <= $tsu interval(5m) fill(value, 'true', 'true','true')


# fill nonarithmetic values into arithmetic fields
sql_error select count(*) from $stb where ts >= $ts0 and ts <= $tsu interval(5m) fill(value, abc);
sql_error select count(*) from $stb where ts >= $ts0 and ts <= $tsu interval(5m) fill(value, 'true');

sql select count(*) from $stb where ts >= $ts0 and ts <= $tsu interval(5m) fill(value, '2e1');
$val = $rowNum * 2
$val = $val - 1
if $rows != $val then 
  return -1
endi
if $data01 != $rowNum then
  return -1
endi
if $data11 != 20 then 
  return -1
endi

sql select count(*) from $stb where ts >= $ts0 and ts <= $tsu interval(5m) fill(value, 2e1);
if $rows != $val then 
  return -1
endi
if $data01 != $rowNum then
  return -1
endi
if $data11 != 20 then 
  return -1
endi

sql select count(*) from $stb where ts >= $ts0 and ts <= $tsu interval(5m) fill(value, '20');
if $rows != $val then 
  return -1
endi
if $data01 != $rowNum then
  return -1
endi
if $data11 != 20 then 
  return -1
endi

## linear fill
sql select max(c1), min(c2), avg(c3), sum(c4), first(c7), last(c8), first(c9) from $stb where ts >= $ts0 and ts <= $tsu interval(5m) fill(linear) group by t1
$val = $rowNum * 2
$val = $val - 1
$val = $val * $tbNum
if $rows != $val then
  return -1
endi
if $data08 != 0 then
  return -1
endi
if $data15 != NULL then
  return -1
endi
if $data16 != NULL then
  return -1
endi
if $data17 != NULL then
  return -1
endi
if $data18 != 0 then
  return -1
endi

## [TBASE-365]
sql select max(c1), min(c2), avg(c3), sum(c4), first(c7), last(c8), first(c9) from $stb where ts >= $ts0 and ts <= $tsu and t1 > 4 interval(5m) fill(linear) group by t1
if $rows != 95 then
  return -1
endi
#if $data02 != NULL then
#  return -1
#endi
#if $data04 != NULL then
#  return -1
#endi
if $data06 != binary0 then
  return -1
endi
if $data07 != nchar0 then
  return -1
endi
if $data12 != NULL then
  return -1
endi
if $data14 != NULL then
  return -1
endi
if $data16 != NULL then
  return -1
endi
if $data17 != NULL then
  return -1
endi
if $data08 != 5 then
  return -1
endi
if $data18 != 5 then
  return -1
endi

sql select max(c1), min(c2), sum(c3), avg(c4), first(c7), last(c8), first(c9) from $stb where ts >= $ts0 and ts <= $tsu interval(5m) fill(linear)
$val = $rowNum * 2
$val = $val - 1
if $rows != $val then
  return -1
endi
if $data07 != nchar0 then
  return -1
endi
if $data17 != NULL then
  return -1
endi

sql select max(c1), min(c2), sum(c3), avg(c4), first(c9), last(c8), first(c9) from $stb where ts >= '2018-09-16 00:00:00.000' and ts <= '2018-09-18 00:00:00.000' interval(1d) fill(linear)
if $rows != 3 then
  return -1
endi

## previous fill
print fill(prev)
sql select max(c1), min(c2), avg(c3), sum(c4), count(c5), first(c7), last(c8), first(c9) from $stb where ts >= $ts0 and ts <= $tsu and t1 > 4 interval(5m) fill(prev) group by t1 limit 5
if $rows != 25 then 
  return -1
endi
if $data01 != 0 then
  return -1
endi
if $data02 != NULL then
  return -1
endi
if $data04 != NULL then
  return -1
endi
if $data09 != 5 then 
  return -1
endi
if $data12 != NULL then
  return -1
endi
if $data19 != 5 then 
  return -1
endi
if $data18 != nchar0 then
  return -1
endi
if $data59 != 6 then 
  return -1
endi
if $data69 != 6 then
  return -1
endi

## NULL fill
print fill(NULL)
sql select max(c1), min(c2), avg(c3), sum(c4), count(c5), first(c7), last(c8), first(c9) from $stb where ts >= $ts0 and ts <= $tsu and t1 > 4 interval(5m) fill(value, NULL) group by t1 limit 5
if $rows != 25 then 
  return -1
endi
if $data01 != 0 then
  return -1
endi
if $data02 != NULL then
  return -1
endi
if $data04 != NULL then
  return -1
endi
if $data06 != 1 then
  return -1
endi
if $data09 != 5 then 
  return -1
endi
if $data11 != NULL then
  return -1
endi
if $data12 != NULL then
  return -1
endi
if $data19 != 5 then 
  return -1
endi
if $data18 != NULL then
  return -1
endi
if $data59 != 6 then 
  return -1
endi
if $data69 != 6 then
  return -1
endi

print =============== clear
sql drop database $db
sql show databases
if $rows != 0 then 
  return -1
endi

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