where.sim

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/exec.sh -n dnode1 -s start

sleep 3000
sql connect

$dbPrefix = wh_db
$tbPrefix = wh_tb
$mtPrefix = wh_mt
$tbNum = 10
$rowNum = 10000
$totalNum = $tbNum * $rowNum

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

sql drop database if exits $db -x step1
step1:
sql create database if not exists $db 
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)

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

  $x = 0
  while $x < $rowNum
    $ms = $x . m
    $c = $x / 100
    $c = $c * 100
    $c = $x - $c
    $binary = 'binary . $c
    $binary = $binary . '
    $nchar = 'nchar . $c
    $nchar = $nchar . '
    sql insert into $tb values (now + $ms , $c , $c , $c , $c , $c , $c , $c , $binary , $nchar )
    $x = $x + 1
  endw

  $i = $i + 1
endw

sleep 100

$i = 1
$tb = $tbPrefix . $i

##
sql_error select * from $tb where c7

# TBASE-654 : invalid filter expression cause server crashed
sql_error select count(*) from $tb where c1<10 and c1<>2

sql select * from $tb where c7 = false
$val = $rowNum / 100
if $rows != $val then
  return -1
endi
sql select * from $mt where c7 = false
$val = $totalNum / 100
if $rows != $val then
  return -1
endi

sql select tbname from $mt
if $rows != $tbNum then
  return -1
endi

sql select tbname from $mt where t1 < 2
if $rows != 2 then
  return -1
endi

print $tbPrefix
$tb = $tbPrefix . 0
if $data00 != wh_tb1 then
  print expect wh_tb1, actual:$data00
  return -1
endi
$tb = $tbPrefix . 1
if $data10 != wh_tb0 then
  print expect wh_tb0, actual:$data00
  return -1
endi

## select specified columns

sql select c1 from $mt
if $rows != $totalNum then
  return -1
endi
sql select count(c1) from $mt
if $data00 != $totalNum then
  return -1
endi

sql select c1 from $mt where c1 >= 0
if $rows != $totalNum then
  return -1
endi
sql select count(c1) from $mt where c1 <> -1
if $data00 != $totalNum then
  return -1
endi
sql select count(c1) from $mt where c1 <> -1 group by t1
if $rows != $tbNum then
  return -1
endi
if $data00 != $rowNum then
  return -1
endi


## like
sql_error select * from $mt where c1 like 1
#sql_error select * from $mt where t1 like 1

## [TBASE-593]
sql create table wh_mt1 (ts timestamp, c1 smallint, c2 int, c3 bigint, c4 float, c5 double, c6 tinyint, c7 binary(10), c8 nchar(10), c9 bool, c10 timestamp) tags (t1 binary(10), t2 smallint, t3 int, t4 bigint, t5 float, t6 double)
sql create table wh_mt1_tb1 using wh_mt1 tags ('tb11', 1, 1, 1, 1, 1)
sql insert into wh_mt1_tb1 values (now, 1, 1, 1, 1, 1, 1, 'binary', 'nchar', true, '2019-01-01 00:00:00.000')
sql_error select last(*) from wh_mt1 where c1 in ('1')
sql_error select last(*) from wh_mt1_tb1 where c1 in ('1')
sql_error select last(*) from wh_mt1 where c2 in ('1')
sql_error select last(*) from wh_mt1_tb1 where c2 in ('1')
sql_error select last(*) from wh_mt1 where c3 in ('1')
sql_error select last(*) from wh_mt1_tb1 where c3 in ('1')
sql_error select last(*) from wh_mt1 where c4 in ('1')
sql_error select last(*) from wh_mt1_tb1 where c4 in ('1')
sql_error select last(*) from wh_mt1 where c5 in ('1')
sql_error select last(*) from wh_mt1_tb1 where c5 in ('1')
sql_error select last(*) from wh_mt1 where c6 in ('1')
sql_error select last(*) from wh_mt1_tb1 where c6 in ('1')
sql_error select last(*) from wh_mt1 where c7 in ('binary')
sql_error select last(*) from wh_mt1_tb1 where c7 in ('binary')
sql_error select last(*) from wh_mt1 where c8 in ('nchar')
sql_error select last(*) from wh_mt1_tb1 where c9 in (true, false)
sql_error select last(*) from wh_mt1 where c10 in ('2019-01-01 00:00:00.000')
sql_error select last(*) from wh_mt1_tb1 where c10 in ('2019-01-01 00:00:00.000')
sql_error select last(*) from wh_mt1 where t1 in ('binary')
sql_error select last(*) from wh_mt1 where t2 in (1)
sql_error select last(*) from wh_mt1 where t3 in (1)
sql_error select last(*) from wh_mt1 where t4 in (1)
sql_error select last(*) from wh_mt1 where t5 in (1)
sql_error select last(*) from wh_mt1 where t6 in (1)
sql select last(*) from wh_mt1 where c1 = 1
if $rows != 1 then
  return -1
endi

## [TBASE-597]
sql create table wh_mt2 (ts timestamp, c1 timestamp, c2 binary(10), c3 nchar(10)) tags (t1 binary(10))
sql create table wh_mt2_tb1 using wh_mt2 tags ('wh_mt2_tb1')

# 2019-01-01 00:00:00.000     1546272000000
# 2019-01-01 00:10:00.000     1546272600000
# 2019-01-01 09:00:00.000     1546304400000
# 2019-01-01 09:10:00.000     1546305000000
sql insert into wh_mt2_tb1 values ('2019-01-01 00:00:00.000', '2019-01-01 09:00:00.000', 'binary10', 'nchar10') 
sql insert into wh_mt2_tb1 values ('2019-01-01 00:10:00.000', '2019-01-01 09:10:00.000', 'binary10', 'nchar10') 
sql select * from wh_mt2_tb1 where c1 > 1546304400000
if $rows != 1 then
  return -1
endi
if $data00 != @19-01-01 00:10:00.000@ then
  return -1
endi
if $data01 != @19-01-01 09:10:00.000@ then
  return -1
endi

sql select * from wh_mt2_tb1 where c1 > '2019-01-01 09:00:00.000'
if $rows != 1 then
  return -1
endi
if $data00 != @19-01-01 00:10:00.000@ then
  return -1
endi
if $data01 != @19-01-01 09:10:00.000@ then
  return -1
endi

sql select * from wh_mt2_tb1 where c1 > 1546304400000 and ts < '2019-01-01 00:10:00.000'
if $rows != 0 then
  return -1
endi

sql select * from wh_mt2_tb1 where c1 > '2019-01-01 09:00:00.000' and ts < '2019-01-01 00:10:00.000'
if $rows != 0 then
  return -1
endi

sql select * from wh_mt2_tb1 where c1 >= 1546304400000 and c1 <= '2019-01-01 09:10:00.000'
if $rows != 2 then
  return -1
endi
if $data00 != @19-01-01 00:00:00.000@ then
  return -1
endi
if $data01 != @19-01-01 09:00:00.000@ then
  return -1
endi
if $data10 != @19-01-01 00:10:00.000@ then
  return -1
endi
if $data11 != @19-01-01 09:10:00.000@ then
  return -1
endi

sql select * from wh_mt2_tb1 where c1 >= '2019-01-01 09:00:00.000' and c1 <= '2019-01-01 09:10:00.000'
if $rows != 2 then
  return -1
endi
if $data00 != @19-01-01 00:00:00.000@ then
  return -1
endi
if $data01 != @19-01-01 09:00:00.000@ then
  return -1
endi
if $data10 != @19-01-01 00:10:00.000@ then
  return -1
endi
if $data11 != @19-01-01 09:10:00.000@ then
  return -1
endi

sql create table tb_where_NULL (ts timestamp, c1 float, c2 binary(10))
sql insert into tb_where_NULL values ('2019-01-01 09:00:00.000', 1, 'val1')
sql insert into tb_where_NULL values ('2019-01-01 09:00:01.000', NULL, NULL)
sql insert into tb_where_NULL values ('2019-01-01 09:00:02.000', 2, 'val2')
sql_error select * from tb_where_NULL where c1 = NULL
sql_error select * from tb_where_NULL where c1 <> NULL
sql_error select * from tb_where_NULL where c1 < NULL
sql select * from tb_where_NULL where c1 = "NULL"
if $rows != 0 then
  return -1
endi

sql select * from tb_where_NULL where c1 <> "NULL"
if $rows != 2 then
  return -1
endi
sql select * from tb_where_NULL where c1 <> "nulL"
if $rows != 2 then
  return -1
endi

sql select * from tb_where_NULL where c1 > "NULL"
if $rows != 0 then
  return -1
endi

sql select * from tb_where_NULL where c1 >= "NULL"
if $rows != 0 then
  return -1
endi

sql select * from tb_where_NULL where c2 = "NULL"
if $rows != 0 then
  return -1
endi

sql select * from tb_where_NULL where c2 <> "NULL"
if $rows != 2 then
  return -1
endi
sql select * from tb_where_NULL where c2 <> "nUll"
if $rows != 2 then
  return -1
endi

print ==========tbase-1363
#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)

$i = 0
while $i < 1
  $tb = test_null_filter
  sql create table $tb using $mt tags( $i )

  $x = 0
  while $x < 10000
    $ms = $x . m
    $c = $x / 100
    $c = $c * 100
    $c = $x - $c
    $binary = 'binary . $c
    $binary = $binary . '
    $nchar = 'nchar . $c
    $nchar = $nchar . '
    sql insert into $tb values (now + $ms , null , null , null , null , null , null , null , null , null )
    $x = $x + 1
  endw

  $i = $i + 1
endw

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

system sh/exec.sh -n dnode1 -s start

sql select * from wh_mt0 where c3 = 'abc' and tbname in ('test_null_filter');
if $row != 0 then
  return -1
endi

sql select * from wh_mt0 where c3 = 'abc' and tbname in ('test_null_filter');
if $row != 0 then
  return -1
endi

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