sleep 3000
sql connect
sql create database if not exists db
sql use db

#### test the value of all data types in four cases: static create table, insert column value, synamic create table, alter tag value

######## case 0: smallint
print ========== smallint 
sql create table mt_smallint (ts timestamp, c smallint) tags (tagname smallint)

## case 00: static create table for test tag values
sql create table st_smallint_0  using mt_smallint tags (NULL)
sql select tagname from st_smallint_0
if $data00 != NULL then
  return -1
endi
sql create table st_smallint_1  using mt_smallint tags (NULL)
sql select tagname from st_smallint_1
if $data00 != NULL then
  return -1
endi
sql create table st_smallint_2  using mt_smallint tags ('NULL')
sql select tagname from st_smallint_2
if $data00 != NULL then
  return -1
endi
sql create table st_smallint_3  using mt_smallint tags ('NULL')
sql select tagname from st_smallint_3
if $data00 != NULL then
  return -1
endi
sql create table st_smallint_4  using mt_smallint tags ("NULL")
sql select tagname from st_smallint_4
if $data00 != NULL then
  return -1
endi
sql create table st_smallint_5  using mt_smallint tags ("NULL")
sql select tagname from st_smallint_5
if $data00 != NULL then
  return -1
endi
sql create table st_smallint_6  using mt_smallint tags (-32767)
sql select tagname from st_smallint_6
if $data00 != -32767 then
  return -1
endi
sql create table st_smallint_7  using mt_smallint tags (32767)
sql select tagname from st_smallint_7
if $data00 != 32767 then
  return -1
endi
sql create table st_smallint_8  using mt_smallint tags (37)
sql select tagname from st_smallint_8
if $data00 != 37 then
  return -1
endi
sql create table st_smallint_9  using mt_smallint tags (-100)
sql select tagname from st_smallint_9
if $data00 != -100 then
  return -1
endi
sql create table st_smallint_10 using mt_smallint tags (+113)
sql select tagname from st_smallint_10
if $data00 != 113 then
  return -1
endi
sql create table st_smallint_11 using mt_smallint tags ('-100')
sql select tagname from st_smallint_11
if $data00 != -100 then
  return -1
endi
sql create table st_smallint_12 using mt_smallint tags ("+78")
sql select tagname from st_smallint_12
if $data00 != 78 then
  return -1
endi
sql create table st_smallint_13 using mt_smallint tags (+0078)
sql select tagname from st_smallint_13
if $data00 != 78 then
  return -1
endi
sql create table st_smallint_14 using mt_smallint tags (-00078)
sql select tagname from st_smallint_14
if $data00 != -78 then
  return -1
endi

## case 01: insert values  for test column values
sql insert into st_smallint_0  values (now, NULL)
sql select * from st_smallint_0
if $rows != 1 then
  return -1
endi
if $data01 != NULL then
  return -1
endi 
sql insert into st_smallint_1  values (now, NULL)
sql select * from st_smallint_1
if $rows != 1 then
  return -1
endi
if $data01 != NULL then
  return -1
endi  
sql insert into st_smallint_2  values (now, 'NULL')
sql select * from st_smallint_2
if $rows != 1 then
  return -1
endi
if $data01 != NULL then
  return -1
endi
sql insert into st_smallint_3  values (now, 'NULL')
sql select * from st_smallint_3
if $rows != 1 then
  return -1
endi
if $data01 != NULL then
  return -1
endi
sql insert into st_smallint_4  values (now, "NULL")
sql select * from st_smallint_4
if $rows != 1 then
  return -1
endi
if $data01 != NULL then
  return -1
endi
sql insert into st_smallint_5  values (now, "NULL")
sql select * from st_smallint_5
if $rows != 1 then
  return -1
endi
if $data01 != NULL then
  return -1
endi
sql insert into st_smallint_6  values (now, 32767)
sql select * from st_smallint_6
if $rows != 1 then
  return -1
endi
if $data01 != 32767 then
  return -1
endi 
sql insert into st_smallint_7  values (now, -32767)
sql select * from st_smallint_7
if $rows != 1 then
  return -1
endi
if $data01 != -32767 then
  return -1
endi  
sql insert into st_smallint_8  values (now, +100) 
sql select * from st_smallint_8
if $rows != 1 then
  return -1
endi
if $data01 != 100 then
  return -1
endi   
sql insert into st_smallint_9  values (now, "-098")
sql select * from st_smallint_9
if $rows != 1 then
  return -1
endi
if $data01 != -98 then
  return -1
endi 
sql insert into st_smallint_10 values (now, '0')
sql select * from st_smallint_10
if $rows != 1 then
  return -1
endi
if $data01 != 0 then
  return -1
endi 
sql insert into st_smallint_11 values (now, -0)  
sql select * from st_smallint_11
if $rows != 1 then
  return -1
endi
if $data01 != 0 then
  return -1
endi 
sql insert into st_smallint_12 values (now, "+056")
sql select * from st_smallint_12
if $rows != 1 then
  return -1
endi
if $data01 != 56 then
  return -1
endi 

sql insert into st_smallint_13 values (now, +056)
sql select * from st_smallint_13
if $rows != 1 then
  return -1
endi
if $data01 != 56 then
  return -1
endi 

sql insert into st_smallint_14 values (now, -056)
sql select * from st_smallint_14
if $rows != 1 then
  return -1
endi
if $data01 != -56 then
  return -1
endi 

## case 02: dynamic create table for test tag values
sql insert into st_smallint_16 using mt_smallint tags (NULL)   values (now, NULL)
sql select tagname from st_smallint_16
if $data00 != NULL then
  return -1
endi
sql select * from st_smallint_16
if $data01 != NULL then
  return -1
endi
    
sql insert into st_smallint_17 using mt_smallint tags (NULL)   values (now, NULL)
sql select tagname from st_smallint_17
if $data00 != NULL then
  return -1
endi
sql select * from st_smallint_17
if $data01 != NULL then
  return -1
endi  
sql insert into st_smallint_18 using mt_smallint tags ('NULL') values (now, 'NULL')
sql select tagname from st_smallint_18
if $data00 != NULL then
  return -1
endi
sql select * from st_smallint_18
if $data01 != NULL then
  return -1
endi
sql insert into st_smallint_19 using mt_smallint tags ('NULL') values (now, 'NULL')
sql select tagname from st_smallint_19
if $data00 != NULL then
  return -1
endi
sql select * from st_smallint_19
if $data01 != NULL then
  return -1
endi
sql insert into st_smallint_20 using mt_smallint tags ("NULL") values (now, "NULL")
sql select tagname from st_smallint_20
if $data00 != NULL then
  return -1
endi
sql select * from st_smallint_20
if $data01 != NULL then
  return -1
endi
sql insert into st_smallint_21 using mt_smallint tags ("NULL") values (now, "NULL")
sql select tagname from st_smallint_21
if $data00 != NULL then
  return -1
endi
sql select * from st_smallint_21
if $data01 != NULL then
  return -1
endi
sql insert into st_smallint_22 using mt_smallint tags (32767)  values (now, 32767)
sql select tagname from st_smallint_22
if $data00 != 32767 then
  return -1
endi
sql select * from st_smallint_22
if $data01 != 32767 then
  return -1
endi 
sql insert into st_smallint_23 using mt_smallint tags (-32767)  values (now, -32767)
sql select tagname from st_smallint_23
if $data00 != -32767 then
  return -1
endi
sql select * from st_smallint_23
if $data01 != -32767 then
  return -1
endi  
sql insert into st_smallint_24 using mt_smallint tags (10)    values (now, 10)
sql select tagname from st_smallint_24
if $data00 != 10 then
  return -1
endi
sql select * from st_smallint_24
if $data01 != 10 then
  return -1
endi    
sql insert into st_smallint_25 using mt_smallint tags ("-0") values (now, "-0")
sql select tagname from st_smallint_25
if $data00 != 0 then
  return -1
endi
sql select * from st_smallint_25
if $data01 != 0 then
  return -1
endi 
sql insert into st_smallint_26 using mt_smallint tags ('123') values (now, '123')
sql select tagname from st_smallint_26
if $data00 != 123 then
  return -1
endi
sql select * from st_smallint_26
if $data01 != 123 then
  return -1
endi 
sql insert into st_smallint_27 using mt_smallint tags (+056) values (now, +00056)
sql select tagname from st_smallint_27
if $data00 != 56 then
  return -1
endi
sql select * from st_smallint_27
if $data01 != 56 then
  return -1
endi 
sql insert into st_smallint_28 using mt_smallint tags (-056) values (now, -0056)
sql select tagname from st_smallint_28
if $data00 != -56 then
  return -1
endi
sql select * from st_smallint_28
if $data01 != -56 then
  return -1
endi 

## case 03: alter tag values
#sql alter table st_smallint_0 set tag tagname=32767
#sql select tagname from st_smallint_0
#if $data00 != 32767 then
#  return -1
#endi
#sql alter table st_smallint_0 set tag tagname=-32767
#sql select tagname from st_smallint_0
#if $data00 != -32767 then
#  return -1
#endi
#sql alter table st_smallint_0 set tag tagname=+100
#sql select tagname from st_smallint_0
#if $data00 != 100 then
#  return -1
#endi
#sql alter table st_smallint_0 set tag tagname=-33
#sql select tagname from st_smallint_0
#if $data00 != -33 then
#  return -1
#endi
#sql alter table st_smallint_0 set tag tagname='+98'
#sql select tagname from st_smallint_0
#if $data00 != 98 then
#  return -1
#endi
#sql alter table st_smallint_0 set tag tagname='-076'
#sql select tagname from st_smallint_0
#if $data00 != -76 then
#  return -1
#endi
#sql alter table st_smallint_0 set tag tagname=+0012
#sql select tagname from st_smallint_0
#if $data00 != 12 then
#  return -1
#endi
#sql alter table st_smallint_0 set tag tagname=-00063
#sql select tagname from st_smallint_0
#if $data00 != -63 then
#  return -1
#endi

## case 04: illegal input
sql_error create table st_smallint_e0 using mt_smallint tags (32768)
sql_error create table st_smallint_e0 using mt_smallint tags (-32768)
sql_error create table st_smallint_e0 using mt_smallint tags (3276899)
sql_error create table st_smallint_e0 using mt_smallint tags (-3276833)
#sql_error create table st_smallint_e0 using mt_smallint tags (12.80)   truncate integer part
#sql_error create table st_smallint_e0 using mt_smallint tags (-11.80)
sql_error create table st_smallint_e0 using mt_smallint tags (123abc)  
sql_error create table st_smallint_e0 using mt_smallint tags ("123abc")
sql_error create table st_smallint_e0 using mt_smallint tags (abc)     
sql_error create table st_smallint_e0 using mt_smallint tags ("abc")   
sql_error create table st_smallint_e0 using mt_smallint tags (" ")     
sql_error create table st_smallint_e0 using mt_smallint tags ('')      

sql create table st_smallint_e0  using mt_smallint tags (123)
sql create table st_smallint_e1  using mt_smallint tags (123)
sql create table st_smallint_e2  using mt_smallint tags (123)
sql create table st_smallint_e3  using mt_smallint tags (123)
sql create table st_smallint_e4  using mt_smallint tags (123)
sql create table st_smallint_e5  using mt_smallint tags (123)
sql create table st_smallint_e6  using mt_smallint tags (123)
sql create table st_smallint_e7  using mt_smallint tags (123)
sql create table st_smallint_e8  using mt_smallint tags (123)
sql create table st_smallint_e9  using mt_smallint tags (123)
sql create table st_smallint_e10 using mt_smallint tags (123)
sql create table st_smallint_e11 using mt_smallint tags (123)
sql create table st_smallint_e12 using mt_smallint tags (123)

sql_error insert into st_smallint_e0  values (now, 32768)     
sql_error insert into st_smallint_e1  values (now, -32768)    
sql_error insert into st_smallint_e2  values (now, 42768)    
sql_error insert into st_smallint_e3  values (now, -32769)   
#sql_error insert into st_smallint_e4  values (now, 12.80)   
#sql_error insert into st_smallint_e5  values (now, -11.80)  
sql_error insert into st_smallint_e6  values (now, 123abc)  
sql_error insert into st_smallint_e7  values (now, "123abc")
sql_error insert into st_smallint_e9  values (now, abc)     
sql_error insert into st_smallint_e10 values (now, "abc")   
sql_error insert into st_smallint_e11 values (now, " ")     
sql_error insert into st_smallint_e12 values (now, '')      

sql_error insert into st_smallint_e13 using mt_smallint tags (033) values (now, 32768)     
sql_error insert into st_smallint_e14 using mt_smallint tags (033) values (now, -32768)    
sql_error insert into st_smallint_e15 using mt_smallint tags (033) values (now, 32968)    
sql_error insert into st_smallint_e16 using mt_smallint tags (033) values (now, -33768)   
#sql_error insert into st_smallint_e17 using mt_smallint tags (033) values (now, 12.80)   
#sql_error insert into st_smallint_e18 using mt_smallint tags (033) values (now, -11.80)  
sql_error insert into st_smallint_e19 using mt_smallint tags (033) values (now, 123abc)  
sql_error insert into st_smallint_e20 using mt_smallint tags (033) values (now, "123abc")
sql_error insert into st_smallint_e22 using mt_smallint tags (033) values (now, abc)     
sql_error insert into st_smallint_e23 using mt_smallint tags (033) values (now, "abc")   
sql_error insert into st_smallint_e24 using mt_smallint tags (033) values (now, " ")     
sql_error insert into st_smallint_e25 using mt_smallint tags (033) values (now, '')  

sql_error insert into st_smallint_e13 using mt_smallint tags (32768)      values (now, -033)
sql_error insert into st_smallint_e14 using mt_smallint tags (-32768)     values (now, -033)
sql_error insert into st_smallint_e15 using mt_smallint tags (72768)     values (now, -033)
sql_error insert into st_smallint_e16 using mt_smallint tags (-92768)    values (now, -033)
#sql_error insert into st_smallint_e17 using mt_smallint tags (12.80)    values (now, -033)
#sql_error insert into st_smallint_e18 using mt_smallint tags (-11.80)   values (now, -033)
sql_error insert into st_smallint_e19 using mt_smallint tags (123abc)   values (now, -033)
sql_error insert into st_smallint_e20 using mt_smallint tags ("123abc") values (now, -033)
sql_error insert into st_smallint_e22 using mt_smallint tags (abc)      values (now, -033)
sql_error insert into st_smallint_e23 using mt_smallint tags ("abc")    values (now, -033)
sql_error insert into st_smallint_e24 using mt_smallint tags (" ")      values (now, -033)
sql_error insert into st_smallint_e25 using mt_smallint tags ('')       values (now, -033)

sql insert into st_smallint_e13 using mt_smallint tags (033) values (now, 00062)
sql insert into st_smallint_e14 using mt_smallint tags (033) values (now, 00062)
sql insert into st_smallint_e15 using mt_smallint tags (033) values (now, 00062)
sql insert into st_smallint_e16 using mt_smallint tags (033) values (now, 00062)
sql insert into st_smallint_e17 using mt_smallint tags (033) values (now, 00062)
sql insert into st_smallint_e18 using mt_smallint tags (033) values (now, 00062)
sql insert into st_smallint_e19 using mt_smallint tags (033) values (now, 00062)
sql insert into st_smallint_e20 using mt_smallint tags (033) values (now, 00062)
sql insert into st_smallint_e21 using mt_smallint tags (033) values (now, 00062)
sql insert into st_smallint_e22 using mt_smallint tags (033) values (now, 00062)
sql insert into st_smallint_e23 using mt_smallint tags (033) values (now, 00062)
sql insert into st_smallint_e24 using mt_smallint tags (033) values (now, 00062)
sql insert into st_smallint_e25 using mt_smallint tags (033) values (now, 00062)

sql_error alter table st_smallint_e13 set tag tagname=32768     
sql_error alter table st_smallint_e14 set tag tagname=-32768    
sql_error alter table st_smallint_e15 set tag tagname=52768    
sql_error alter table st_smallint_e16 set tag tagname=-32778 
sql_error alter table st_smallint_e19 set tag tagname=123abc  
sql_error alter table st_smallint_e20 set tag tagname="123abc" 
sql_error alter table st_smallint_e22 set tag tagname=abc     
sql_error alter table st_smallint_e23 set tag tagname="abc"   
sql_error alter table st_smallint_e24 set tag tagname=" "     
sql_error alter table st_smallint_e25 set tag tagname=''