where.sim

system sh/stop_dnodes.sh

system sh/deploy.sh -n dnode1 -i 1
system sh/cfg.sh -n dnode1 -c walLevel -v 1
system sh/cfg.sh -n dnode1 -c maxtablespervnode -v 4
system sh/exec.sh -n dnode1 -s start

sleep 100
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 exists $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)

$half = $tbNum / 2

$i = 0
while $i < $half
  $tb = $tbPrefix . $i

  $nextSuffix = $i + $half
  $tb1 = $tbPrefix . $nextSuffix

  sql create table $tb using $mt tags( $i )
  sql create table $tb1 using $mt tags( $nextSuffix )

  $x = 0
  while $x < $rowNum
    $y = $x * 60000
    $ms = 1600099200000 + $y
    $c = $x / 100
    $c = $c * 100
    $c = $x - $c
    $binary = 'binary . $c
    $binary = $binary . '
    $nchar = 'nchar . $c
    $nchar = $nchar . '
    sql insert into $tb values ($ms , $c , $c , $c , $c , $c , $c , $c , $binary , $nchar )  $tb1 values ($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

print select c1 from $mt
sql select c1 from $mt

print rows $rows
print totalNum $totalNum
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 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))

print ===================>td-1604
sql_error insert into tb_where_NULL values(?, ?, ?)
sql_error insert into tb_where_NULL values(now, 1, ?)
sql_error insert into tb_where_NULL values(?, 1, '')
sql_error insert into tb_where_NULL values(now, ?, '12')

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
    $y = $x * 60000
    $ms = 1601481600000 + $y
    $c = $x / 100
    $c = $c * 100
    $c = $x - $c
    $binary = 'binary . $c
    $binary = $binary . '
    $nchar = 'nchar . $c
    $nchar = $nchar . '
    sql insert into $tb values ($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 500

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

sql_error select * from wh_mt0 where c3 = 'abc' and tbname in ('test_null_filter');

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

sql select * from wh_mt0 where c3 = '1';
if $row == 0 then
  return -1
endi

sql select * from wh_mt0 where c3 is null and tbname in ('test_null_filter');
if $rows != 10000 then
  return -1
endi

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

print ==========================>td-3318
sql create table tu(ts timestamp,  k int, b binary(12))
sql insert into tu values(now, 1, 'abc')
sql select stddev(k) from tu where b <>'abc' interval(1s)
if $rows != 0 then
 return -1
endi

print ==========================> td-4783,td-4792
sql create table where_ts(ts timestamp, f int)
sql insert into where_ts values('2021-06-19 16:22:00', 1);
sql insert into where_ts values('2021-06-19 16:23:00', 2);
sql insert into where_ts values('2021-06-19 16:24:00', 3);
sql insert into where_ts values('2021-06-19 16:25:00', 1);
sql select * from (select * from where_ts) where ts<'2021-06-19 16:25:00' and ts>'2021-06-19 16:22:00'
if $rows != 2 then
  return -1
endi
print $data00, $data01
if $data01 != 2 then
  return -1
endi
sql insert into where_ts values(now, 5);
sleep 10
sql select * from (select * from where_ts) where ts<now;
if $rows != 5 then
  return -1
endi
system sh/exec.sh -n dnode1 -s stop -x SIGINT