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)
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)
$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
print select _wstart, max(c1), max(c2), max(c3), max(c4), max(c5) from $stb where ts >= $ts0 and ts <= $tsu partition by t1 interval(5m) fill(value, -1, -2, -3, -4, -5)
sql select _wstart, max(c1), max(c2), max(c3), max(c4), max(c5) from $stb where ts >= $ts0 and ts <= $tsu partition by t1 interval(5m) fill(value, -1, -2, -3, -4, -5)
$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 $data11 != -1 then
  return -1
endi

# number of fill values is smaller than number of selected columns
print select _wstart, max(c1), max(c2), max(c3) from $stb where ts >= $ts0 and ts <= $tsu interval(5m) fill(value, 6, 6, 6)
sql select _wstart, max(c1), max(c2), max(c3) from $stb where ts >= $ts0 and ts <= $tsu interval(5m) fill(value, 6, 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 _wstart, 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

print select max(c4) from $stb where t1 > 4 and ts >= $ts0 and ts <= $tsu partition by t1 interval(5m) fill(value, -1)
sql select max(c4) from $stb where t1 > 4 and ts >= $ts0 and ts <= $tsu partition by t1 interval(5m) fill(value, -1)
#if $rows != 0 then
#  return -1
#endi

sql select _wstart, min(c1), max(c4) from $stb where t1 > 4 and ts >= $ts0 and ts <= $tsu interval(5m) fill(value, -1, -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
print select _wstart, first(c7), first(c8), first(c9) from $stb where ts >= $ts0 and ts <= $tsu interval(5m) fill(value, '20000000', '20000000', '20000000')
sql select _wstart, 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 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_error select _wstart, count(*) from $stb where ts >= $ts0 and ts <= $tsu interval(5m) fill(value, '2e1');

sql select _wstart, 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 _wstart, max(c1), min(c2), avg(c3), sum(c4), first(c7), last(c8), first(c9) from $stb where ts >= $ts0 and ts <= $tsu partition by t1 interval(5m) fill(linear)
$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 _wstart, 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 partition by t1 interval(5m) fill(linear)
print select _wstart, 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 partition by t1 interval(5m) fill(linear)
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

sql select _wstart, 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)
print select _wstart, 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 partition by t1 interval(5m) fill(prev) limit 5
sql select _wstart, 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 partition by t1 interval(5m) fill(prev) 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 $data12 != NULL then
  return -1
endi
if $data18 != nchar0 then
  return -1
endi

## NULL fill
print fill(NULL)
print select _wstart, 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 partition by t1 interval(5m) fill(value, NULL) limit 5
sql select _wstart, 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 partition by t1 interval(5m) fill(NULL) limit 5
if $rows != 25 then
  return -1
endi
if $data01 != 0 then
  return -1
endi
if $data02 != NULL then
  return -1
endi
if $data06 != 1 then
  return -1
endi
if $data11 != NULL then
  return -1
endi
if $data12 != NULL then
  return -1
endi
if $data18 != NULL then
  return -1
endi

print =============== clear
sql drop database $db
sql select * from information_schema.ins_databases
if $rows != 2 then
  return -1
endi

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