import sys 
from util.log import *
from util.cases import *
from util.sql import *
from util.dnodes import tdDnodes
from math import inf

class TDTestCase:
    def caseDescription(self):
        '''
        case1<shenglian zhou>: [TD-11204]Difference improvement that can ignore negative 
        ''' 
        return
    
    def init(self, conn, logSql):
        tdLog.debug("start to execute %s" % __file__)
        tdSql.init(conn.cursor(), logSql)
        self._conn = conn
        
    def restartTaosd(self, index=1, dbname="db"):
        tdDnodes.stop(index)
        tdDnodes.startWithoutSleep(index)
        tdSql.execute(f"use substr_func")

    def run(self):
        print("running {}".format(__file__))
        tdSql.execute("drop database if exists substr_func")
        tdSql.execute("create database if not exists substr_func")
        tdSql.execute('use substr_func')
        tdSql.execute('create table stb1 (ts timestamp, c1 bool, c2 tinyint, c3 smallint, c4 int, c5 bigint, c6 float, c7 double, c8 binary(20), c9 nchar(20), c10 tinyint unsigned, c11 smallint unsigned, c12 int unsigned, c13 bigint unsigned, c14 binary(20), c15 nchar(30)) TAGS(t1 int, t2 binary(10), t3 double);')

        tdSql.execute("create table tb1 using stb1 tags(1,'1',1.0);")

        tdSql.execute("create table tb2 using stb1 tags(2,'2',2.0);")

        tdSql.execute("create table tb3 using stb1 tags(3,'3',3.0);")

        tdSql.execute('insert into tb1 values (\'2021-11-11 09:00:00\',true,1,1,1,1,1,1,"123","1234",1,1,1,1,\'  TaosdAta  \',\'  tDenGine  \');')

        tdSql.execute("insert into tb1 values ('2021-11-11 09:00:01',true,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);")

        tdSql.execute('insert into tb1 values (\'2021-11-11 09:00:02\',true,2,NULL,2,NULL,2,NULL,"234",NULL,2,NULL,2,NULL,\' XiaoBo \',NULL);')

        tdSql.execute('insert into tb1 values (\'2021-11-11 09:00:03\',false,NULL,3,NULL,3,NULL,3,NULL,"3456",NULL,3,NULL,3,NULL,\'bOxiaO\');')

        tdSql.execute('insert into tb1 values (\'2021-11-11 09:00:04\',true,4,4,4,4,4,4,"456","4567",4,4,4,4,\'  Wade Zhang\',\' Wade ZhAng  \');')

        tdSql.execute('insert into tb1 values (\'2021-11-11 09:00:05\',true,127,32767,2147483647,9223372036854775807,3.402823466e+38,1.79769e+308,"567","5678",254,65534,4294967294,9223372036854775807,\'27\',\'278\');')

        tdSql.execute('insert into tb1 values (\'2021-11-11 09:00:06\',true,-127,-32767,-2147483647,-9223372036854775807,-3.402823466e+38,-1.79769e+308,"678","6789",0,0,0,0,\'\',\'\');')

        tdSql.execute('insert into tb2 values (\'2021-11-11 09:00:00\',true,1,1,1,1,1,1,"aBc", "AbCd",1,1,1,1,\'  TaosdAta  \',\'  tDenGine  \');')

        tdSql.execute('insert into tb2 values (\'2021-11-11 09:00:01\',true,2,2,2,2,2,2,"Bcd", "BcdE",2,2,2,2,\'  jeFF tAo\',\'Jeff Tao\');')

        tdSql.execute('insert into tb2 values (\'2021-11-11 09:00:02\',true,3,3,2,3,3,3,"cdE", "cdEf",3,3,3,3,\'  Steven Li\',\'SteVeN LI\');')

        tdSql.execute('insert into tb2 values (\'2021-11-11 09:00:03\',false,4,4,4,4,4,4,"deF", "deFg",4,4,4,4,\'  Wade Zhang\',\' Wade ZhAng  \');')

        tdSql.execute('insert into tb2 values (\'2021-11-11 09:00:04\',true,5,5,5,5,5,5,"efG", "efGH",5,5,5,5,\' simOnGuan\',\'shengliang guaN  \');')

        tdSql.execute('insert into tb2 values (\'2021-11-11 09:00:05\',true,6,6,6,6,6,6,"f Gh", " fgHk ",6,6,6,6,\' Ph. D. Liao\',\'haojun Liao  \');')

        tdSql.execute('insert into tb2 values (\'2021-11-11 09:00:06\',true,7,7,7,7,7,7,"ghi  ", "  ghiJ ",7,7,7,7,\' hongZe cheng \',\'  hongzecheng\');')

        tdSql.execute('create table tbn (ts timestamp, c1 bool, c2 tinyint, c3 smallint, c4 int, c5 bigint, c6 float, c7 double, c8 binary(20), c9 nchar(20), c10 tinyint unsigned, c11 smallint unsigned, c12 int unsigned, c13 bigint unsigned, c14 binary(20), c15 nchar(30));')

        tdSql.execute('insert into tbn values (\'2021-11-11 09:00:00\',true,1,1,1,1,1,1,"aBc", "AbCd",1,1,1,1,\'  TaosdAta  \',\'  tDenGine  \');')

        tdSql.execute('insert into tbn values (\'2021-11-11 09:00:01\',true,2,2,2,2,2,2,"Bcd", "BcdE",2,2,2,2,\'  jeFF tAo\',\'Jeff Tao\');')

        tdSql.execute('insert into tbn values (\'2021-11-11 09:00:02\',true,3,3,2,3,3,3,"cdE", "cdEf",3,3,3,3,\'  Steven Li\',\'SteVeN LI\');')

        tdSql.execute('insert into tbn values (\'2021-11-11 09:00:03\',false,4,4,4,4,4,4,"deF", "deFg",4,4,4,4,\'  Wade Zhang\',\' Wade ZhAng  \');')

        tdSql.execute('insert into tbn values (\'2021-11-11 09:00:04\',true,5,5,5,5,5,5,"efG", "efGH",5,5,5,5,\' simOnGuan\',\'shengliang guaN  \');')

        tdSql.execute('insert into tbn values (\'2021-11-11 09:00:05\',true,6,6,6,6,6,6,"f Gh", " fgHk ",6,6,6,6,\' Ph. D. Liao\',\'haojun Liao  \');')

        tdSql.execute('insert into tbn values (\'2021-11-11 09:00:06\',true,7,7,7,7,7,7,"ghi  ", "  ghiJ ",7,7,7,7,\' hongZe cheng \',\'  hongzecheng\');')

        tdSql.error('select substr(*,1,4) from tb1;')

        tdSql.error('select substr(*, -1, 1) + 1 as a from tb1;')

        tdSql.error('select substr(tb1.*, -1 ,1) + 1 as a from tb1;')

        tdSql.error('select substr(*,1,4) from tb1;')

        tdSql.error('select substr(c1,1,4) from tb1;')

        tdSql.error('select substr(13,1,4) from tb1;')

        tdSql.error('select substr(c1,1,4) from tb1;')

        tdSql.error('select substr(c2,1,4) from tb1;')

        tdSql.error('select substr(c3,1,4) from tb1;')

        tdSql.error('select substr(c4,1,4) from tb1;')

        tdSql.error('select substr(c5,1,4) from tb1;')

        tdSql.error('select substr(c6,1,4) from tb1;')

        tdSql.error('select substr(c7,1,4) from tb1;')

        tdSql.query('select substr(c8,1,4) from tb1;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '123')
        tdSql.checkData(1, 0, None)
        tdSql.checkData(2, 0, '234')
        tdSql.checkData(3, 0, None)
        tdSql.checkData(4, 0, '456')
        tdSql.checkData(5, 0, '567')
        tdSql.checkData(6, 0, '678')

        tdSql.query('select substr(c14,1,4) from tb1;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '  Ta')
        tdSql.checkData(1, 0, None)
        tdSql.checkData(2, 0, ' Xia')
        tdSql.checkData(3, 0, None)
        tdSql.checkData(4, 0, '  Wa')
        tdSql.checkData(5, 0, '27')
        tdSql.checkData(6, 0, '')

        tdSql.error('select substr(c10,1,4) from tb1;')

        tdSql.error('select substr(c11,1,4) from tb1;')

        tdSql.error('select substr(c12,1,4) from tb1;')

        tdSql.error('select substr(c13,1,4) from tb1;')

        tdSql.query("select substr('12345678900000000000000000',1,4) from tb1;")
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '1234')
        tdSql.checkData(1, 0, '1234')
        tdSql.checkData(2, 0, '1234')
        tdSql.checkData(3, 0, '1234')
        tdSql.checkData(4, 0, '1234')
        tdSql.checkData(5, 0, '1234')
        tdSql.checkData(6, 0, '1234')

        tdSql.error("select distinct substr('123',1,4) from tb1;")

        tdSql.error('select substr(t1,1,4) from stb1;')

        tdSql.error('select substr(c8, -1 , 1),avg(c3) from tb1;')

        tdSql.error('select substr(c8, -1 , 1),top(c3,1) from tb1;')

        tdSql.error('select substr(concat(c8,c14),1,4) from tb1 session(ts, 1s);')

        tdSql.error('select substr(concat(c8,c14),1,4) from tb1 STATE_WINDOW(c1);')

        tdSql.error('select substr(concat(c8,c14),1,4) from tb1 interval(1s) sliding(1s) fill(NULL);')

        tdSql.error('select substr(concat(c8,c14),1,4) from stb1 group by t1;')

        tdSql.error('select substr(concat(c8,c14),1,4) from stb1 group by ts;')

        tdSql.error('select substr(concat(c8,c14),1,4) from stb1 group by c1;')

        tdSql.error('select substr(concat(c8,c14),1,4) from stb1 group by tbname;')

        tdSql.error('select substr(concat(c8,c14),1,4) from tb1 order by c2;')

        tdSql.query('select substr(c8, -1, 1),substr(c14,1,4) from tbn;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, 'c')
        tdSql.checkData(0, 1, '  Ta')
        tdSql.checkData(1, 0, 'd')
        tdSql.checkData(1, 1, '  je')
        tdSql.checkData(2, 0, 'E')
        tdSql.checkData(2, 1, '  St')
        tdSql.checkData(3, 0, 'F')
        tdSql.checkData(3, 1, '  Wa')
        tdSql.checkData(4, 0, 'G')
        tdSql.checkData(4, 1, ' sim')
        tdSql.checkData(5, 0, 'h')
        tdSql.checkData(5, 1, ' Ph.')
        tdSql.checkData(6, 0, ' ')
        tdSql.checkData(6, 1, ' hon')

        tdSql.error('select substr(ts,1,4) from (select avg(c2) as a from stb1 interval(1s));')

        tdSql.query('select substr(cast(a as binary(10)),1,4) from (select abs(c2) as a from tb1);')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '1')
        tdSql.checkData(1, 0, None)
        tdSql.checkData(2, 0, '2')
        tdSql.checkData(3, 0, None)
        tdSql.checkData(4, 0, '4')
        tdSql.checkData(5, 0, '127')
        tdSql.checkData(6, 0, '127')

        tdSql.query('select substr("abc",1,4) from tb1;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, 'abc')
        tdSql.checkData(1, 0, 'abc')
        tdSql.checkData(2, 0, 'abc')
        tdSql.checkData(3, 0, 'abc')
        tdSql.checkData(4, 0, 'abc')
        tdSql.checkData(5, 0, 'abc')
        tdSql.checkData(6, 0, 'abc')

        tdSql.query('select substr(concat(c8,c14),1,4) from tb1;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '123 ')
        tdSql.checkData(1, 0, None)
        tdSql.checkData(2, 0, '234 ')
        tdSql.checkData(3, 0, None)
        tdSql.checkData(4, 0, '456 ')
        tdSql.checkData(5, 0, '5672')
        tdSql.checkData(6, 0, '678')

        tdSql.query('select substr((concat(c8,c14)),1,4) from tb1;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '123 ')
        tdSql.checkData(1, 0, None)
        tdSql.checkData(2, 0, '234 ')
        tdSql.checkData(3, 0, None)
        tdSql.checkData(4, 0, '456 ')
        tdSql.checkData(5, 0, '5672')
        tdSql.checkData(6, 0, '678')

        tdSql.error("select substr('11', -1 ,1)+c2 from tb1;")

        tdSql.error('select substr(c8, -4 ,2)+c2 from tb1;')

        tdSql.error('select substr(c14, 2, 2)+11 from tb1;')

        tdSql.query('select substr(c8, 1, 2),c8,c2 from tb1;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '12')
        tdSql.checkData(0, 1, '123')
        tdSql.checkData(0, 2, 1)
        tdSql.checkData(1, 0, None)
        tdSql.checkData(1, 1, None)
        tdSql.checkData(1, 2, None)
        tdSql.checkData(2, 0, '23')
        tdSql.checkData(2, 1, '234')
        tdSql.checkData(2, 2, 2)
        tdSql.checkData(3, 0, None)
        tdSql.checkData(3, 1, None)
        tdSql.checkData(3, 2, None)
        tdSql.checkData(4, 0, '45')
        tdSql.checkData(4, 1, '456')
        tdSql.checkData(4, 2, 4)
        tdSql.checkData(5, 0, '56')
        tdSql.checkData(5, 1, '567')
        tdSql.checkData(5, 2, 127)
        tdSql.checkData(6, 0, '67')
        tdSql.checkData(6, 1, '678')
        tdSql.checkData(6, 2, -127)

        tdSql.query('select substr(c8, 2, 3),t1,ts,tbname,_C0,_c0 from tb1;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '23')
        tdSql.checkData(0, 1, 1)
        tdSql.checkData(0, 2, datetime.datetime(2021, 11, 11, 9, 0))
        tdSql.checkData(0, 3, 'tb1')
        tdSql.checkData(0, 4, datetime.datetime(2021, 11, 11, 9, 0))
        tdSql.checkData(0, 5, datetime.datetime(2021, 11, 11, 9, 0))
        tdSql.checkData(1, 0, None)
        tdSql.checkData(1, 1, 1)
        tdSql.checkData(1, 2, datetime.datetime(2021, 11, 11, 9, 0, 1))
        tdSql.checkData(1, 3, 'tb1')
        tdSql.checkData(1, 4, datetime.datetime(2021, 11, 11, 9, 0, 1))
        tdSql.checkData(1, 5, datetime.datetime(2021, 11, 11, 9, 0, 1))
        tdSql.checkData(2, 0, '34')
        tdSql.checkData(2, 1, 1)
        tdSql.checkData(2, 2, datetime.datetime(2021, 11, 11, 9, 0, 2))
        tdSql.checkData(2, 3, 'tb1')
        tdSql.checkData(2, 4, datetime.datetime(2021, 11, 11, 9, 0, 2))
        tdSql.checkData(2, 5, datetime.datetime(2021, 11, 11, 9, 0, 2))
        tdSql.checkData(3, 0, None)
        tdSql.checkData(3, 1, 1)
        tdSql.checkData(3, 2, datetime.datetime(2021, 11, 11, 9, 0, 3))
        tdSql.checkData(3, 3, 'tb1')
        tdSql.checkData(3, 4, datetime.datetime(2021, 11, 11, 9, 0, 3))
        tdSql.checkData(3, 5, datetime.datetime(2021, 11, 11, 9, 0, 3))
        tdSql.checkData(4, 0, '56')
        tdSql.checkData(4, 1, 1)
        tdSql.checkData(4, 2, datetime.datetime(2021, 11, 11, 9, 0, 4))
        tdSql.checkData(4, 3, 'tb1')
        tdSql.checkData(4, 4, datetime.datetime(2021, 11, 11, 9, 0, 4))
        tdSql.checkData(4, 5, datetime.datetime(2021, 11, 11, 9, 0, 4))
        tdSql.checkData(5, 0, '67')
        tdSql.checkData(5, 1, 1)
        tdSql.checkData(5, 2, datetime.datetime(2021, 11, 11, 9, 0, 5))
        tdSql.checkData(5, 3, 'tb1')
        tdSql.checkData(5, 4, datetime.datetime(2021, 11, 11, 9, 0, 5))
        tdSql.checkData(5, 5, datetime.datetime(2021, 11, 11, 9, 0, 5))
        tdSql.checkData(6, 0, '78')
        tdSql.checkData(6, 1, 1)
        tdSql.checkData(6, 2, datetime.datetime(2021, 11, 11, 9, 0, 6))
        tdSql.checkData(6, 3, 'tb1')
        tdSql.checkData(6, 4, datetime.datetime(2021, 11, 11, 9, 0, 6))
        tdSql.checkData(6, 5, datetime.datetime(2021, 11, 11, 9, 0, 6))

        tdSql.query('select substr(c8, -3, 2),floor(c3) from tb1;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '12')
        tdSql.checkData(0, 1, 1)
        tdSql.checkData(1, 0, None)
        tdSql.checkData(1, 1, None)
        tdSql.checkData(2, 0, '23')
        tdSql.checkData(2, 1, None)
        tdSql.checkData(3, 0, None)
        tdSql.checkData(3, 1, 3)
        tdSql.checkData(4, 0, '45')
        tdSql.checkData(4, 1, 4)
        tdSql.checkData(5, 0, '56')
        tdSql.checkData(5, 1, 32767)
        tdSql.checkData(6, 0, '67')
        tdSql.checkData(6, 1, -32767)

        tdSql.query('select substr(c8, -2, 3),substr(concat(c8,c14),1,4) from tb1;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '23')
        tdSql.checkData(0, 1, '123 ')
        tdSql.checkData(1, 0, None)
        tdSql.checkData(1, 1, None)
        tdSql.checkData(2, 0, '34')
        tdSql.checkData(2, 1, '234 ')
        tdSql.checkData(3, 0, None)
        tdSql.checkData(3, 1, None)
        tdSql.checkData(4, 0, '56')
        tdSql.checkData(4, 1, '456 ')
        tdSql.checkData(5, 0, '67')
        tdSql.checkData(5, 1, '5672')
        tdSql.checkData(6, 0, '78')
        tdSql.checkData(6, 1, '678')

        tdSql.query('select substr(concat(c8,c14),1,4) from tb1 where c9 is not null and c15 is not null;')
        tdSql.checkRows(5)
        tdSql.checkData(0, 0, '123 ')
        tdSql.checkData(1, 0, None)
        tdSql.checkData(2, 0, '456 ')
        tdSql.checkData(3, 0, '5672')
        tdSql.checkData(4, 0, '678')

        tdSql.query('select substr(c14,1,4) from tb1 order by ts desc;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '')
        tdSql.checkData(1, 0, '27')
        tdSql.checkData(2, 0, '  Wa')
        tdSql.checkData(3, 0, None)
        tdSql.checkData(4, 0, ' Xia')
        tdSql.checkData(5, 0, None)
        tdSql.checkData(6, 0, '  Ta')

        tdSql.query('select substr(concat(c8,c14),1,4) from tb1 order by ts desc;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '678')
        tdSql.checkData(1, 0, '5672')
        tdSql.checkData(2, 0, '456 ')
        tdSql.checkData(3, 0, None)
        tdSql.checkData(4, 0, '234 ')
        tdSql.checkData(5, 0, None)
        tdSql.checkData(6, 0, '123 ')

        tdSql.query('select substr(concat(c8,c14),1,4) from tb1 order by ts desc limit 3 offset 2;')
        tdSql.checkRows(3)
        tdSql.checkData(0, 0, '456 ')
        tdSql.checkData(1, 0, None)
        tdSql.checkData(2, 0, '234 ')

        tdSql.query('select substr(c14,1,4) from stb1;')
        tdSql.checkRows(14)
        tdSql.checkData(0, 0, '  Ta')
        tdSql.checkData(1, 0, None)
        tdSql.checkData(2, 0, ' Xia')
        tdSql.checkData(3, 0, None)
        tdSql.checkData(4, 0, '  Wa')
        tdSql.checkData(5, 0, '27')
        tdSql.checkData(6, 0, '')
        tdSql.checkData(7, 0, '  Ta')
        tdSql.checkData(8, 0, '  je')
        tdSql.checkData(9, 0, '  St')
        tdSql.checkData(10, 0, '  Wa')
        tdSql.checkData(11, 0, ' sim')
        tdSql.checkData(12, 0, ' Ph.')
        tdSql.checkData(13, 0, ' hon')

        tdSql.query('select substr(c14,1,4) from stb1 order by ts desc;')
        tdSql.checkRows(14)

        tdSql.query('select substr(c14, 1, 1),t1 from stb1 order by ts desc;')
        tdSql.checkRows(14)

        tdSql.query('select substr(c15, 0, 3),tbname from stb1;')
        tdSql.checkRows(14)
        tdSql.checkData(0, 0, '')
        tdSql.checkData(0, 1, 'tb1')
        tdSql.checkData(1, 0, None)
        tdSql.checkData(1, 1, 'tb1')
        tdSql.checkData(2, 0, None)
        tdSql.checkData(2, 1, 'tb1')
        tdSql.checkData(3, 0, '')
        tdSql.checkData(3, 1, 'tb1')
        tdSql.checkData(4, 0, '')
        tdSql.checkData(4, 1, 'tb1')
        tdSql.checkData(5, 0, '')
        tdSql.checkData(5, 1, 'tb1')
        tdSql.checkData(6, 0, '')
        tdSql.checkData(6, 1, 'tb1')
        tdSql.checkData(7, 0, '')
        tdSql.checkData(7, 1, 'tb2')
        tdSql.checkData(8, 0, '')
        tdSql.checkData(8, 1, 'tb2')
        tdSql.checkData(9, 0, '')
        tdSql.checkData(9, 1, 'tb2')
        tdSql.checkData(10, 0, '')
        tdSql.checkData(10, 1, 'tb2')
        tdSql.checkData(11, 0, '')
        tdSql.checkData(11, 1, 'tb2')
        tdSql.checkData(12, 0, '')
        tdSql.checkData(12, 1, 'tb2')
        tdSql.checkData(13, 0, '')
        tdSql.checkData(13, 1, 'tb2')

        tdSql.query('select substr(c15, 0, 5),tbname from stb1 where t1 > 1;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '')
        tdSql.checkData(0, 1, 'tb2')
        tdSql.checkData(1, 0, '')
        tdSql.checkData(1, 1, 'tb2')
        tdSql.checkData(2, 0, '')
        tdSql.checkData(2, 1, 'tb2')
        tdSql.checkData(3, 0, '')
        tdSql.checkData(3, 1, 'tb2')
        tdSql.checkData(4, 0, '')
        tdSql.checkData(4, 1, 'tb2')
        tdSql.checkData(5, 0, '')
        tdSql.checkData(5, 1, 'tb2')
        tdSql.checkData(6, 0, '')
        tdSql.checkData(6, 1, 'tb2')

        tdSql.query('select substr(c8, 2, 3),substr(c14,1,4) from tbn;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, 'Bc')
        tdSql.checkData(0, 1, '  Ta')
        tdSql.checkData(1, 0, 'cd')
        tdSql.checkData(1, 1, '  je')
        tdSql.checkData(2, 0, 'dE')
        tdSql.checkData(2, 1, '  St')
        tdSql.checkData(3, 0, 'eF')
        tdSql.checkData(3, 1, '  Wa')
        tdSql.checkData(4, 0, 'fG')
        tdSql.checkData(4, 1, ' sim')
        tdSql.checkData(5, 0, ' Gh')
        tdSql.checkData(5, 1, ' Ph.')
        tdSql.checkData(6, 0, 'hi ')
        tdSql.checkData(6, 1, ' hon')

        tdSql.query('select substr(c8, 2, 5),substr(c14,1,4) from tbn order by ts desc;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, 'hi  ')
        tdSql.checkData(0, 1, ' hon')
        tdSql.checkData(1, 0, ' Gh')
        tdSql.checkData(1, 1, ' Ph.')
        tdSql.checkData(2, 0, 'fG')
        tdSql.checkData(2, 1, ' sim')
        tdSql.checkData(3, 0, 'eF')
        tdSql.checkData(3, 1, '  Wa')
        tdSql.checkData(4, 0, 'dE')
        tdSql.checkData(4, 1, '  St')
        tdSql.checkData(5, 0, 'cd')
        tdSql.checkData(5, 1, '  je')
        tdSql.checkData(6, 0, 'Bc')
        tdSql.checkData(6, 1, '  Ta')

        tdSql.query('select substr(substr(c8, 0, 5),1,4) from tbn;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '')
        tdSql.checkData(1, 0, '')
        tdSql.checkData(2, 0, '')
        tdSql.checkData(3, 0, '')
        tdSql.checkData(4, 0, '')
        tdSql.checkData(5, 0, '')
        tdSql.checkData(6, 0, '')

        tdSql.query('select substr(cast(a as binary(10)),1,4) from (select avg(c2) as a from stb1 interval(1s));')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '1.00')
        tdSql.checkData(1, 0, '2.00')
        tdSql.checkData(2, 0, '2.50')
        tdSql.checkData(3, 0, '4.00')
        tdSql.checkData(4, 0, '4.50')
        tdSql.checkData(5, 0, '66.5')
        tdSql.checkData(6, 0, '-60.')

        tdSql.query('select substr(c14,1,4) from (select * from stb1);')
        tdSql.checkRows(14)
        tdSql.checkData(0, 0, '  Ta')
        tdSql.checkData(1, 0, None)
        tdSql.checkData(2, 0, ' Xia')
        tdSql.checkData(3, 0, None)
        tdSql.checkData(4, 0, '  Wa')
        tdSql.checkData(5, 0, '27')
        tdSql.checkData(6, 0, '')
        tdSql.checkData(7, 0, '  Ta')
        tdSql.checkData(8, 0, '  je')
        tdSql.checkData(9, 0, '  St')
        tdSql.checkData(10, 0, '  Wa')
        tdSql.checkData(11, 0, ' sim')
        tdSql.checkData(12, 0, ' Ph.')
        tdSql.checkData(13, 0, ' hon')

        tdSql.query("select substr(cast(a as binary(10)),1,4) from (select avg(c2) as a from stb1 where ts >= '2021-11-11 09:00:00.000' and ts <= '2021-11-11 09:00:09.000' interval(1s) fill(null));")
        tdSql.checkRows(10)
        tdSql.checkData(0, 0, '1.00')
        tdSql.checkData(1, 0, '2.00')
        tdSql.checkData(2, 0, '2.50')
        tdSql.checkData(3, 0, '4.00')
        tdSql.checkData(4, 0, '4.50')
        tdSql.checkData(5, 0, '66.5')
        tdSql.checkData(6, 0, '-60.')
        tdSql.checkData(7, 0, None)
        tdSql.checkData(8, 0, None)
        tdSql.checkData(9, 0, None)

        tdSql.query("select substr(cast(a as binary(10)),1,4) from (select avg(c2) as a from stb1 where ts >= '2021-11-11 09:00:00.000' and ts <= '2021-11-11 09:00:09.000' interval(1s) fill(null)) order by ts;")
        tdSql.checkRows(10)
        tdSql.checkData(0, 0, '1.00')
        tdSql.checkData(1, 0, '2.00')
        tdSql.checkData(2, 0, '2.50')
        tdSql.checkData(3, 0, '4.00')
        tdSql.checkData(4, 0, '4.50')
        tdSql.checkData(5, 0, '66.5')
        tdSql.checkData(6, 0, '-60.')
        tdSql.checkData(7, 0, None)
        tdSql.checkData(8, 0, None)
        tdSql.checkData(9, 0, None)

        tdSql.query("select substr(cast(a as binary(10)),1,4) from (select avg(c2) as a from stb1 where ts >= '2021-11-11 09:00:00.000' and ts <= '2021-11-11 09:00:09.000' interval(1s) fill(null)) order by ts desc;")
        tdSql.checkRows(10)
        tdSql.checkData(0, 0, None)
        tdSql.checkData(1, 0, None)
        tdSql.checkData(2, 0, None)
        tdSql.checkData(3, 0, '-60.')
        tdSql.checkData(4, 0, '66.5')
        tdSql.checkData(5, 0, '4.50')
        tdSql.checkData(6, 0, '4.00')
        tdSql.checkData(7, 0, '2.50')
        tdSql.checkData(8, 0, '2.00')
        tdSql.checkData(9, 0, '1.00')

        tdSql.query("select substr(cast(a as binary(10)),1,4) from (select avg(c2) as a from stb1 where ts >= '2021-11-11 09:00:00.000' and ts <= '2021-11-11 09:00:09.000' interval(1s) fill(null)) order by a desc;")
        tdSql.checkRows(10)
        tdSql.checkData(0, 0, None)
        tdSql.checkData(1, 0, None)
        tdSql.checkData(2, 0, None)
        tdSql.checkData(3, 0, '66.5')
        tdSql.checkData(4, 0, '4.50')
        tdSql.checkData(5, 0, '4.00')
        tdSql.checkData(6, 0, '2.50')
        tdSql.checkData(7, 0, '2.00')
        tdSql.checkData(8, 0, '1.00')
        tdSql.checkData(9, 0, '-60.')

        tdSql.query("select substr(cast(a as binary(10)),1,4) from (select avg(c2) as a from stb1 where ts >= '2021-11-11 09:00:00.000' and ts <= '2021-11-11 09:00:09.000' interval(1s) fill(null)) order by a;")
        tdSql.checkRows(10)
        tdSql.checkData(0, 0, '-60.')
        tdSql.checkData(1, 0, '1.00')
        tdSql.checkData(2, 0, '2.00')
        tdSql.checkData(3, 0, '2.50')
        tdSql.checkData(4, 0, '4.00')
        tdSql.checkData(5, 0, '4.50')
        tdSql.checkData(6, 0, '66.5')
        tdSql.checkData(7, 0, None)
        tdSql.checkData(8, 0, None)
        tdSql.checkData(9, 0, None)

        tdSql.query('select substr(cast(a as binary(10)),1,4) from (select abs(c2) as a from tb1);')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '1')
        tdSql.checkData(1, 0, None)
        tdSql.checkData(2, 0, '2')
        tdSql.checkData(3, 0, None)
        tdSql.checkData(4, 0, '4')
        tdSql.checkData(5, 0, '127')
        tdSql.checkData(6, 0, '127')

        tdSql.query('select substr(tb1.c15, 2, 3),substr(tb2.c15,1,4) from tb1,tb2 where tb1.ts=tb2.ts;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, ' tD')
        tdSql.checkData(0, 1, '  tD')
        tdSql.checkData(1, 0, None)
        tdSql.checkData(1, 1, 'Jeff')
        tdSql.checkData(2, 0, None)
        tdSql.checkData(2, 1, 'SteV')
        tdSql.checkData(3, 0, 'Oxi')
        tdSql.checkData(3, 1, ' Wad')
        tdSql.checkData(4, 0, 'Wad')
        tdSql.checkData(4, 1, 'shen')
        tdSql.checkData(5, 0, '78')
        tdSql.checkData(5, 1, 'haoj')
        tdSql.checkData(6, 0, '')
        tdSql.checkData(6, 1, '  ho')

        tdSql.query('select substr(c15,1,4) from tb1 union all select substr(c15,1,4) from tb2;')
        tdSql.checkRows(14)
        tdSql.checkData(0, 0, '  tD')
        tdSql.checkData(1, 0, None)
        tdSql.checkData(2, 0, None)
        tdSql.checkData(3, 0, 'bOxi')
        tdSql.checkData(4, 0, ' Wad')
        tdSql.checkData(5, 0, '278')
        tdSql.checkData(6, 0, '')
        tdSql.checkData(7, 0, '  tD')
        tdSql.checkData(8, 0, 'Jeff')
        tdSql.checkData(9, 0, 'SteV')
        tdSql.checkData(10, 0, ' Wad')
        tdSql.checkData(11, 0, 'shen')
        tdSql.checkData(12, 0, 'haoj')
        tdSql.checkData(13, 0, '  ho')

        tdSql.error('select substr(*,1) from tb1;')

        tdSql.error('select substr(*, -1) + 1 as a from tb1;')

        tdSql.error('select substr(tb1.*, -1 ) + 1 as a from tb1;')

        tdSql.error('select substr(*,1) from tb1;')

        tdSql.error('select substr(c1,1) from tb1;')

        tdSql.error('select substr(13,1) from tb1;')

        tdSql.error('select substr(c1,1) from tb1;')

        tdSql.error('select substr(c2,1) from tb1;')

        tdSql.error('select substr(c3,1) from tb1;')

        tdSql.error('select substr(c4,1) from tb1;')

        tdSql.error('select substr(c5,1) from tb1;')

        tdSql.error('select substr(c6,1) from tb1;')

        tdSql.error('select substr(c7,1) from tb1;')

        tdSql.query('select substr(c8,1) from tb1;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '123')
        tdSql.checkData(1, 0, None)
        tdSql.checkData(2, 0, '234')
        tdSql.checkData(3, 0, None)
        tdSql.checkData(4, 0, '456')
        tdSql.checkData(5, 0, '567')
        tdSql.checkData(6, 0, '678')

        tdSql.query('select substr(c14,1) from tb1;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '  TaosdAta  ')
        tdSql.checkData(1, 0, None)
        tdSql.checkData(2, 0, ' XiaoBo ')
        tdSql.checkData(3, 0, None)
        tdSql.checkData(4, 0, '  Wade Zhang')
        tdSql.checkData(5, 0, '27')
        tdSql.checkData(6, 0, '')

        tdSql.error('select substr(c10,1) from tb1;')

        tdSql.error('select substr(c11,1) from tb1;')

        tdSql.error('select substr(c12,1) from tb1;')

        tdSql.error('select substr(c13,1) from tb1;')

        tdSql.query("select substr('12345678900000000000000000',1) from tb1;")
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '12345678900000000000000000')
        tdSql.checkData(1, 0, '12345678900000000000000000')
        tdSql.checkData(2, 0, '12345678900000000000000000')
        tdSql.checkData(3, 0, '12345678900000000000000000')
        tdSql.checkData(4, 0, '12345678900000000000000000')
        tdSql.checkData(5, 0, '12345678900000000000000000')
        tdSql.checkData(6, 0, '12345678900000000000000000')

        tdSql.error("select distinct substr('123',1) from tb1;")

        tdSql.error('select substr(t1,1) from stb1;')

        tdSql.error('select substr(c8, -1 ),avg(c3) from tb1;')

        tdSql.error('select substr(c8, -1 ),top(c3,1) from tb1;')

        tdSql.error('select substr(concat(c8,c14),1) from tb1 session(ts, 1s);')

        tdSql.error('select substr(concat(c8,c14),1) from tb1 STATE_WINDOW(c1);')

        tdSql.error('select substr(concat(c8,c14),1) from tb1 interval(1s) sliding(1s) fill(NULL);')

        tdSql.error('select substr(concat(c8,c14),1) from stb1 group by t1;')

        tdSql.error('select substr(concat(c8,c14),1) from stb1 group by ts;')

        tdSql.error('select substr(concat(c8,c14),1) from stb1 group by c1;')

        tdSql.error('select substr(concat(c8,c14),1) from stb1 group by tbname;')

        tdSql.error('select substr(concat(c8,c14),1) from tb1 order by c2;')

        tdSql.query('select substr(c8, -1),substr(c14,1) from tbn;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, 'c')
        tdSql.checkData(0, 1, '  TaosdAta  ')
        tdSql.checkData(1, 0, 'd')
        tdSql.checkData(1, 1, '  jeFF tAo')
        tdSql.checkData(2, 0, 'E')
        tdSql.checkData(2, 1, '  Steven Li')
        tdSql.checkData(3, 0, 'F')
        tdSql.checkData(3, 1, '  Wade Zhang')
        tdSql.checkData(4, 0, 'G')
        tdSql.checkData(4, 1, ' simOnGuan')
        tdSql.checkData(5, 0, 'h')
        tdSql.checkData(5, 1, ' Ph. D. Liao')
        tdSql.checkData(6, 0, ' ')
        tdSql.checkData(6, 1, ' hongZe cheng ')

        tdSql.error('select substr(ts,1) from (select avg(c2) as a from stb1 interval(1s));')

        tdSql.query('select substr(cast(a as binary(10)),1) from (select abs(c2) as a from tb1);')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '1')
        tdSql.checkData(1, 0, None)
        tdSql.checkData(2, 0, '2')
        tdSql.checkData(3, 0, None)
        tdSql.checkData(4, 0, '4')
        tdSql.checkData(5, 0, '127')
        tdSql.checkData(6, 0, '127')

        tdSql.query('select substr("abc",1) from tb1;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, 'abc')
        tdSql.checkData(1, 0, 'abc')
        tdSql.checkData(2, 0, 'abc')
        tdSql.checkData(3, 0, 'abc')
        tdSql.checkData(4, 0, 'abc')
        tdSql.checkData(5, 0, 'abc')
        tdSql.checkData(6, 0, 'abc')

        tdSql.query('select substr(concat(c8,c14),1) from tb1;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '123  TaosdAta  ')
        tdSql.checkData(1, 0, None)
        tdSql.checkData(2, 0, '234 XiaoBo ')
        tdSql.checkData(3, 0, None)
        tdSql.checkData(4, 0, '456  Wade Zhang')
        tdSql.checkData(5, 0, '56727')
        tdSql.checkData(6, 0, '678')

        tdSql.query('select substr((concat(c8,c14)),1) from tb1;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '123  TaosdAta  ')
        tdSql.checkData(1, 0, None)
        tdSql.checkData(2, 0, '234 XiaoBo ')
        tdSql.checkData(3, 0, None)
        tdSql.checkData(4, 0, '456  Wade Zhang')
        tdSql.checkData(5, 0, '56727')
        tdSql.checkData(6, 0, '678')

        tdSql.error("select substr('11', -1 )+c2 from tb1;")

        tdSql.error('select substr(c8, -4 )+c2 from tb1;')

        tdSql.error('select substr(c14, 2)+11 from tb1;')

        tdSql.query('select substr(c8, 1),c8,c2 from tb1;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '123')
        tdSql.checkData(0, 1, '123')
        tdSql.checkData(0, 2, 1)
        tdSql.checkData(1, 0, None)
        tdSql.checkData(1, 1, None)
        tdSql.checkData(1, 2, None)
        tdSql.checkData(2, 0, '234')
        tdSql.checkData(2, 1, '234')
        tdSql.checkData(2, 2, 2)
        tdSql.checkData(3, 0, None)
        tdSql.checkData(3, 1, None)
        tdSql.checkData(3, 2, None)
        tdSql.checkData(4, 0, '456')
        tdSql.checkData(4, 1, '456')
        tdSql.checkData(4, 2, 4)
        tdSql.checkData(5, 0, '567')
        tdSql.checkData(5, 1, '567')
        tdSql.checkData(5, 2, 127)
        tdSql.checkData(6, 0, '678')
        tdSql.checkData(6, 1, '678')
        tdSql.checkData(6, 2, -127)

        tdSql.query('select substr(c8, 2),t1,ts,tbname,_C0,_c0 from tb1;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '23')
        tdSql.checkData(0, 1, 1)
        tdSql.checkData(0, 2, datetime.datetime(2021, 11, 11, 9, 0))
        tdSql.checkData(0, 3, 'tb1')
        tdSql.checkData(0, 4, datetime.datetime(2021, 11, 11, 9, 0))
        tdSql.checkData(0, 5, datetime.datetime(2021, 11, 11, 9, 0))
        tdSql.checkData(1, 0, None)
        tdSql.checkData(1, 1, 1)
        tdSql.checkData(1, 2, datetime.datetime(2021, 11, 11, 9, 0, 1))
        tdSql.checkData(1, 3, 'tb1')
        tdSql.checkData(1, 4, datetime.datetime(2021, 11, 11, 9, 0, 1))
        tdSql.checkData(1, 5, datetime.datetime(2021, 11, 11, 9, 0, 1))
        tdSql.checkData(2, 0, '34')
        tdSql.checkData(2, 1, 1)
        tdSql.checkData(2, 2, datetime.datetime(2021, 11, 11, 9, 0, 2))
        tdSql.checkData(2, 3, 'tb1')
        tdSql.checkData(2, 4, datetime.datetime(2021, 11, 11, 9, 0, 2))
        tdSql.checkData(2, 5, datetime.datetime(2021, 11, 11, 9, 0, 2))
        tdSql.checkData(3, 0, None)
        tdSql.checkData(3, 1, 1)
        tdSql.checkData(3, 2, datetime.datetime(2021, 11, 11, 9, 0, 3))
        tdSql.checkData(3, 3, 'tb1')
        tdSql.checkData(3, 4, datetime.datetime(2021, 11, 11, 9, 0, 3))
        tdSql.checkData(3, 5, datetime.datetime(2021, 11, 11, 9, 0, 3))
        tdSql.checkData(4, 0, '56')
        tdSql.checkData(4, 1, 1)
        tdSql.checkData(4, 2, datetime.datetime(2021, 11, 11, 9, 0, 4))
        tdSql.checkData(4, 3, 'tb1')
        tdSql.checkData(4, 4, datetime.datetime(2021, 11, 11, 9, 0, 4))
        tdSql.checkData(4, 5, datetime.datetime(2021, 11, 11, 9, 0, 4))
        tdSql.checkData(5, 0, '67')
        tdSql.checkData(5, 1, 1)
        tdSql.checkData(5, 2, datetime.datetime(2021, 11, 11, 9, 0, 5))
        tdSql.checkData(5, 3, 'tb1')
        tdSql.checkData(5, 4, datetime.datetime(2021, 11, 11, 9, 0, 5))
        tdSql.checkData(5, 5, datetime.datetime(2021, 11, 11, 9, 0, 5))
        tdSql.checkData(6, 0, '78')
        tdSql.checkData(6, 1, 1)
        tdSql.checkData(6, 2, datetime.datetime(2021, 11, 11, 9, 0, 6))
        tdSql.checkData(6, 3, 'tb1')
        tdSql.checkData(6, 4, datetime.datetime(2021, 11, 11, 9, 0, 6))
        tdSql.checkData(6, 5, datetime.datetime(2021, 11, 11, 9, 0, 6))

        tdSql.query('select substr(c8, -3),floor(c3) from tb1;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '123')
        tdSql.checkData(0, 1, 1)
        tdSql.checkData(1, 0, None)
        tdSql.checkData(1, 1, None)
        tdSql.checkData(2, 0, '234')
        tdSql.checkData(2, 1, None)
        tdSql.checkData(3, 0, None)
        tdSql.checkData(3, 1, 3)
        tdSql.checkData(4, 0, '456')
        tdSql.checkData(4, 1, 4)
        tdSql.checkData(5, 0, '567')
        tdSql.checkData(5, 1, 32767)
        tdSql.checkData(6, 0, '678')
        tdSql.checkData(6, 1, -32767)

        tdSql.query('select substr(c8, -2),substr(concat(c8,c14),1) from tb1;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '23')
        tdSql.checkData(0, 1, '123  TaosdAta  ')
        tdSql.checkData(1, 0, None)
        tdSql.checkData(1, 1, None)
        tdSql.checkData(2, 0, '34')
        tdSql.checkData(2, 1, '234 XiaoBo ')
        tdSql.checkData(3, 0, None)
        tdSql.checkData(3, 1, None)
        tdSql.checkData(4, 0, '56')
        tdSql.checkData(4, 1, '456  Wade Zhang')
        tdSql.checkData(5, 0, '67')
        tdSql.checkData(5, 1, '56727')
        tdSql.checkData(6, 0, '78')
        tdSql.checkData(6, 1, '678')

        tdSql.query('select substr(concat(c8,c14),1) from tb1 where c9 is not null and c15 is not null;')
        tdSql.checkRows(5)
        tdSql.checkData(0, 0, '123  TaosdAta  ')
        tdSql.checkData(1, 0, None)
        tdSql.checkData(2, 0, '456  Wade Zhang')
        tdSql.checkData(3, 0, '56727')
        tdSql.checkData(4, 0, '678')

        tdSql.query('select substr(c14,1) from tb1 order by ts desc;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '')
        tdSql.checkData(1, 0, '27')
        tdSql.checkData(2, 0, '  Wade Zhang')
        tdSql.checkData(3, 0, None)
        tdSql.checkData(4, 0, ' XiaoBo ')
        tdSql.checkData(5, 0, None)
        tdSql.checkData(6, 0, '  TaosdAta  ')

        tdSql.query('select substr(concat(c8,c14),1) from tb1 order by ts desc;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '678')
        tdSql.checkData(1, 0, '56727')
        tdSql.checkData(2, 0, '456  Wade Zhang')
        tdSql.checkData(3, 0, None)
        tdSql.checkData(4, 0, '234 XiaoBo ')
        tdSql.checkData(5, 0, None)
        tdSql.checkData(6, 0, '123  TaosdAta  ')

        tdSql.query('select substr(concat(c8,c14),1) from tb1 order by ts desc limit 3 offset 2;')
        tdSql.checkRows(3)
        tdSql.checkData(0, 0, '456  Wade Zhang')
        tdSql.checkData(1, 0, None)
        tdSql.checkData(2, 0, '234 XiaoBo ')

        tdSql.query('select substr(c14,1) from stb1;')
        tdSql.checkRows(14)
        tdSql.checkData(0, 0, '  TaosdAta  ')
        tdSql.checkData(1, 0, None)
        tdSql.checkData(2, 0, ' XiaoBo ')
        tdSql.checkData(3, 0, None)
        tdSql.checkData(4, 0, '  Wade Zhang')
        tdSql.checkData(5, 0, '27')
        tdSql.checkData(6, 0, '')
        tdSql.checkData(7, 0, '  TaosdAta  ')
        tdSql.checkData(8, 0, '  jeFF tAo')
        tdSql.checkData(9, 0, '  Steven Li')
        tdSql.checkData(10, 0, '  Wade Zhang')
        tdSql.checkData(11, 0, ' simOnGuan')
        tdSql.checkData(12, 0, ' Ph. D. Liao')
        tdSql.checkData(13, 0, ' hongZe cheng ')

        tdSql.query('select substr(c14,1) from stb1 order by ts desc;')
        tdSql.checkRows(14)

        tdSql.query('select substr(c14, 1),t1 from stb1 order by ts desc;')
        tdSql.checkRows(14)

        tdSql.query('select substr(c15, 0),tbname from stb1;')
        tdSql.checkRows(14)
        tdSql.checkData(0, 0, '')
        tdSql.checkData(0, 1, 'tb1')
        tdSql.checkData(1, 0, None)
        tdSql.checkData(1, 1, 'tb1')
        tdSql.checkData(2, 0, None)
        tdSql.checkData(2, 1, 'tb1')
        tdSql.checkData(3, 0, '')
        tdSql.checkData(3, 1, 'tb1')
        tdSql.checkData(4, 0, '')
        tdSql.checkData(4, 1, 'tb1')
        tdSql.checkData(5, 0, '')
        tdSql.checkData(5, 1, 'tb1')
        tdSql.checkData(6, 0, '')
        tdSql.checkData(6, 1, 'tb1')
        tdSql.checkData(7, 0, '')
        tdSql.checkData(7, 1, 'tb2')
        tdSql.checkData(8, 0, '')
        tdSql.checkData(8, 1, 'tb2')
        tdSql.checkData(9, 0, '')
        tdSql.checkData(9, 1, 'tb2')
        tdSql.checkData(10, 0, '')
        tdSql.checkData(10, 1, 'tb2')
        tdSql.checkData(11, 0, '')
        tdSql.checkData(11, 1, 'tb2')
        tdSql.checkData(12, 0, '')
        tdSql.checkData(12, 1, 'tb2')
        tdSql.checkData(13, 0, '')
        tdSql.checkData(13, 1, 'tb2')

        tdSql.query('select substr(c15, 0),tbname from stb1 where t1 > 1;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '')
        tdSql.checkData(0, 1, 'tb2')
        tdSql.checkData(1, 0, '')
        tdSql.checkData(1, 1, 'tb2')
        tdSql.checkData(2, 0, '')
        tdSql.checkData(2, 1, 'tb2')
        tdSql.checkData(3, 0, '')
        tdSql.checkData(3, 1, 'tb2')
        tdSql.checkData(4, 0, '')
        tdSql.checkData(4, 1, 'tb2')
        tdSql.checkData(5, 0, '')
        tdSql.checkData(5, 1, 'tb2')
        tdSql.checkData(6, 0, '')
        tdSql.checkData(6, 1, 'tb2')

        tdSql.query('select substr(c8, 2),substr(c14,1) from tbn;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, 'Bc')
        tdSql.checkData(0, 1, '  TaosdAta  ')
        tdSql.checkData(1, 0, 'cd')
        tdSql.checkData(1, 1, '  jeFF tAo')
        tdSql.checkData(2, 0, 'dE')
        tdSql.checkData(2, 1, '  Steven Li')
        tdSql.checkData(3, 0, 'eF')
        tdSql.checkData(3, 1, '  Wade Zhang')
        tdSql.checkData(4, 0, 'fG')
        tdSql.checkData(4, 1, ' simOnGuan')
        tdSql.checkData(5, 0, ' Gh')
        tdSql.checkData(5, 1, ' Ph. D. Liao')
        tdSql.checkData(6, 0, 'hi  ')
        tdSql.checkData(6, 1, ' hongZe cheng ')

        tdSql.query('select substr(c8, 2),substr(c14,1) from tbn order by ts desc;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, 'hi  ')
        tdSql.checkData(0, 1, ' hongZe cheng ')
        tdSql.checkData(1, 0, ' Gh')
        tdSql.checkData(1, 1, ' Ph. D. Liao')
        tdSql.checkData(2, 0, 'fG')
        tdSql.checkData(2, 1, ' simOnGuan')
        tdSql.checkData(3, 0, 'eF')
        tdSql.checkData(3, 1, '  Wade Zhang')
        tdSql.checkData(4, 0, 'dE')
        tdSql.checkData(4, 1, '  Steven Li')
        tdSql.checkData(5, 0, 'cd')
        tdSql.checkData(5, 1, '  jeFF tAo')
        tdSql.checkData(6, 0, 'Bc')
        tdSql.checkData(6, 1, '  TaosdAta  ')

        tdSql.query('select substr(substr(c8, 0),1) from tbn;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '')
        tdSql.checkData(1, 0, '')
        tdSql.checkData(2, 0, '')
        tdSql.checkData(3, 0, '')
        tdSql.checkData(4, 0, '')
        tdSql.checkData(5, 0, '')
        tdSql.checkData(6, 0, '')

        tdSql.query('select substr(cast(a as binary(10)),1) from (select avg(c2) as a from stb1 interval(1s));')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '1.000000')
        tdSql.checkData(1, 0, '2.000000')
        tdSql.checkData(2, 0, '2.500000')
        tdSql.checkData(3, 0, '4.000000')
        tdSql.checkData(4, 0, '4.500000')
        tdSql.checkData(5, 0, '66.500000')
        tdSql.checkData(6, 0, '-60.000000')

        tdSql.query('select substr(c14,1) from (select * from stb1);')
        tdSql.checkRows(14)
        tdSql.checkData(0, 0, '  TaosdAta  ')
        tdSql.checkData(1, 0, None)
        tdSql.checkData(2, 0, ' XiaoBo ')
        tdSql.checkData(3, 0, None)
        tdSql.checkData(4, 0, '  Wade Zhang')
        tdSql.checkData(5, 0, '27')
        tdSql.checkData(6, 0, '')
        tdSql.checkData(7, 0, '  TaosdAta  ')
        tdSql.checkData(8, 0, '  jeFF tAo')
        tdSql.checkData(9, 0, '  Steven Li')
        tdSql.checkData(10, 0, '  Wade Zhang')
        tdSql.checkData(11, 0, ' simOnGuan')
        tdSql.checkData(12, 0, ' Ph. D. Liao')
        tdSql.checkData(13, 0, ' hongZe cheng ')

        tdSql.query("select substr(cast(a as binary(10)),1) from (select avg(c2) as a from stb1 where ts >= '2021-11-11 09:00:00.000' and ts <= '2021-11-11 09:00:09.000' interval(1s) fill(null));")
        tdSql.checkRows(10)
        tdSql.checkData(0, 0, '1.000000')
        tdSql.checkData(1, 0, '2.000000')
        tdSql.checkData(2, 0, '2.500000')
        tdSql.checkData(3, 0, '4.000000')
        tdSql.checkData(4, 0, '4.500000')
        tdSql.checkData(5, 0, '66.500000')
        tdSql.checkData(6, 0, '-60.000000')
        tdSql.checkData(7, 0, None)
        tdSql.checkData(8, 0, None)
        tdSql.checkData(9, 0, None)

        tdSql.query("select substr(cast(a as binary(10)),1) from (select avg(c2) as a from stb1 where ts >= '2021-11-11 09:00:00.000' and ts <= '2021-11-11 09:00:09.000' interval(1s) fill(null)) order by ts;")
        tdSql.checkRows(10)
        tdSql.checkData(0, 0, '1.000000')
        tdSql.checkData(1, 0, '2.000000')
        tdSql.checkData(2, 0, '2.500000')
        tdSql.checkData(3, 0, '4.000000')
        tdSql.checkData(4, 0, '4.500000')
        tdSql.checkData(5, 0, '66.500000')
        tdSql.checkData(6, 0, '-60.000000')
        tdSql.checkData(7, 0, None)
        tdSql.checkData(8, 0, None)
        tdSql.checkData(9, 0, None)

        tdSql.query("select substr(cast(a as binary(10)),1) from (select avg(c2) as a from stb1 where ts >= '2021-11-11 09:00:00.000' and ts <= '2021-11-11 09:00:09.000' interval(1s) fill(null)) order by ts desc;")
        tdSql.checkRows(10)
        tdSql.checkData(0, 0, None)
        tdSql.checkData(1, 0, None)
        tdSql.checkData(2, 0, None)
        tdSql.checkData(3, 0, '-60.000000')
        tdSql.checkData(4, 0, '66.500000')
        tdSql.checkData(5, 0, '4.500000')
        tdSql.checkData(6, 0, '4.000000')
        tdSql.checkData(7, 0, '2.500000')
        tdSql.checkData(8, 0, '2.000000')
        tdSql.checkData(9, 0, '1.000000')

        tdSql.query("select substr(cast(a as binary(10)),1) from (select avg(c2) as a from stb1 where ts >= '2021-11-11 09:00:00.000' and ts <= '2021-11-11 09:00:09.000' interval(1s) fill(null)) order by a desc;")
        tdSql.checkRows(10)
        tdSql.checkData(0, 0, None)
        tdSql.checkData(1, 0, None)
        tdSql.checkData(2, 0, None)
        tdSql.checkData(3, 0, '66.500000')
        tdSql.checkData(4, 0, '4.500000')
        tdSql.checkData(5, 0, '4.000000')
        tdSql.checkData(6, 0, '2.500000')
        tdSql.checkData(7, 0, '2.000000')
        tdSql.checkData(8, 0, '1.000000')
        tdSql.checkData(9, 0, '-60.000000')

        tdSql.query("select substr(cast(a as binary(10)),1) from (select avg(c2) as a from stb1 where ts >= '2021-11-11 09:00:00.000' and ts <= '2021-11-11 09:00:09.000' interval(1s) fill(null)) order by a;")
        tdSql.checkRows(10)
        tdSql.checkData(0, 0, '-60.000000')
        tdSql.checkData(1, 0, '1.000000')
        tdSql.checkData(2, 0, '2.000000')
        tdSql.checkData(3, 0, '2.500000')
        tdSql.checkData(4, 0, '4.000000')
        tdSql.checkData(5, 0, '4.500000')
        tdSql.checkData(6, 0, '66.500000')
        tdSql.checkData(7, 0, None)
        tdSql.checkData(8, 0, None)
        tdSql.checkData(9, 0, None)

        tdSql.query('select substr(cast(a as binary(10)),1) from (select abs(c2) as a from tb1);')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '1')
        tdSql.checkData(1, 0, None)
        tdSql.checkData(2, 0, '2')
        tdSql.checkData(3, 0, None)
        tdSql.checkData(4, 0, '4')
        tdSql.checkData(5, 0, '127')
        tdSql.checkData(6, 0, '127')

        tdSql.query('select substr(tb1.c15, 2),substr(tb2.c15,1) from tb1,tb2 where tb1.ts=tb2.ts;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, ' tDenGine  ')
        tdSql.checkData(0, 1, '  tDenGine  ')
        tdSql.checkData(1, 0, None)
        tdSql.checkData(1, 1, 'Jeff Tao')
        tdSql.checkData(2, 0, None)
        tdSql.checkData(2, 1, 'SteVeN LI')
        tdSql.checkData(3, 0, 'OxiaO')
        tdSql.checkData(3, 1, ' Wade ZhAng  ')
        tdSql.checkData(4, 0, 'Wade ZhAng  ')
        tdSql.checkData(4, 1, 'shengliang guaN  ')
        tdSql.checkData(5, 0, '78')
        tdSql.checkData(5, 1, 'haojun Liao  ')
        tdSql.checkData(6, 0, '')
        tdSql.checkData(6, 1, '  hongzecheng')

        tdSql.query('select substr(c15,1) from tb1 union all select substr(c15,1) from tb2;')
        tdSql.checkRows(14)
        tdSql.checkData(0, 0, '  tDenGine  ')
        tdSql.checkData(1, 0, None)
        tdSql.checkData(2, 0, None)
        tdSql.checkData(3, 0, 'bOxiaO')
        tdSql.checkData(4, 0, ' Wade ZhAng  ')
        tdSql.checkData(5, 0, '278')
        tdSql.checkData(6, 0, '')
        tdSql.checkData(7, 0, '  tDenGine  ')
        tdSql.checkData(8, 0, 'Jeff Tao')
        tdSql.checkData(9, 0, 'SteVeN LI')
        tdSql.checkData(10, 0, ' Wade ZhAng  ')
        tdSql.checkData(11, 0, 'shengliang guaN  ')
        tdSql.checkData(12, 0, 'haojun Liao  ')
        tdSql.checkData(13, 0, '  hongzecheng')


        tdSql.execute('create table stba (ts timestamp, c1 bool, c2 tinyint, c3 smallint, c4 int, c5 bigint, c6 float, c7 double, c8 binary(20), c9 nchar(20), c10 tinyint unsigned, c11 smallint unsigned, c12 int unsigned, c13 bigint unsigned, c14 binary(20), c15 nchar(30)) TAGS(t1 int, t2 binary(10), t3 double);')

        tdSql.execute("create table tba1 using stba tags(1,'1',1.0);")

        tdSql.execute('insert into tba1 values (\'2021-11-11 09:00:00\',true, 1,1,1,1,1,1,"aBc", "AbCd",1,1,1,1,\'  TaosdAta  \',\'  tDenGine  \');')

        tdSql.execute('insert into tba1 values (\'2021-11-11 09:00:01\',true, 2,2,2,2,2,2,"Bcd", "BcdE",2,2,2,2,\'  jeFF tAo\',\'Jeff Tao\');')

        tdSql.execute('insert into tba1 values (\'2021-11-11 09:00:02\',true, 3,3,2,3,3,3,"cdE", "cdEf",3,3,3,3,\'  Steven Li\',\'SteVeN LI\');')

        tdSql.execute('insert into tba1 values (\'2021-11-11 09:00:03\',false,4,4,4,4,4,4,"deF", "deFg",4,4,4,4,\'  Wade Zhang\',\' Wade ZhAng  \');')

        tdSql.execute('insert into tba1 values (\'2021-11-11 09:00:04\',true, 5,5,5,5,5,5,"efG", "efGH",5,5,5,5,\' simOnGuan\',\'shengliang guaN  \');')

        tdSql.execute('insert into tba1 values (\'2021-11-11 09:00:05\',true, 6,6,6,6,6,6,"f Gh", " fgHk ",6,6,6,6,\' Ph. D. Liao\',\'haojun Liao  \');')

        tdSql.execute('insert into tba1 values (\'2021-11-11 09:00:06\',true, 7,7,7,7,7,7,"ghi  ", "  ghiJ ",7,7,7,7,\' hongZe cheng \',\'  hongzecheng\');')

        tdSql.execute('insert into tba1 values (\'2021-11-11 09:00:07\',true, 8,8,8,8,8,8,"ghi  ", "  涛思数据 ",8,8,8,8,\' jiangyi HOU\',\'hou jiangyi\');')

        tdSql.execute('insert into tba1 values (\'2021-11-11 09:00:08\',true, 9,9,9,9,9,9," taos Data  ", "  涛思数据 ",9,9,9,9,\' Jing Wang\',\'  运营  \');')

        tdSql.execute('insert into tba1 values (\'2021-11-11 09:00:09\',true, 0,0,0,0,0,0," taOs Data  ", "  涛思数据2022 ",0,0,0,0,\'guang li\',\'销售：\');')

        self.restartTaosd(1, dbname='substr_func')

        tdSql.execute('insert into tba1 values (\'2021-11-11 09:00:10\',true, 1,1,1,1,1,1,"aBc", "AbCd",1,1,1,1,\'  TaosdAta  \',\'  tDenGine  \');')

        tdSql.execute('insert into tba1 values (\'2021-11-11 09:00:11\',true, 2,2,2,2,2,2,"Bcd", "BcdE",2,2,2,2,\'  jeFF tAo\',\'Jeff Tao\');')

        tdSql.execute('insert into tba1 values (\'2021-11-11 09:00:12\',true, 3,3,2,3,3,3,"cdE", "cdEf",3,3,3,3,\'  Steven Li\',\'SteVeN LI\');')

        tdSql.execute('insert into tba1 values (\'2021-11-11 09:00:13\',false,4,4,4,4,4,4,"deF", "deFg",4,4,4,4,\'  Wade Zhang\',\' Wade ZhAng  \');')

        tdSql.execute('insert into tba1 values (\'2021-11-11 09:00:14\',true, 5,5,5,5,5,5,"efG", "efGH",5,5,5,5,\' simOnGuan\',\'shengliang guaN  \');')

        tdSql.execute('insert into tba1 values (\'2021-11-11 09:00:15\',true, 6,6,6,6,6,6,"f Gh", " fgHk ",6,6,6,6,\' Ph. D. Liao\',\'haojun Liao  \');')

        tdSql.execute('insert into tba1 values (\'2021-11-11 09:00:16\',true, 7,7,7,7,7,7,"ghi  ", "  ghiJ ",7,7,7,7,\' hongZe cheng \',\'  hongzecheng\');')

        tdSql.execute('insert into tba1 values (\'2021-11-11 09:00:17\',true, 8,8,8,8,8,8,"ghi  ", "  涛思数据 ",8,8,8,8,\' jiangyi HOU\',\'hou jiangyi\');')

        tdSql.execute('insert into tba1 values (\'2021-11-11 09:00:18\',true, 9,9,9,9,9,9," taos Data  ", "  涛思数据 ",9,9,9,9,\' Jing Wang\',\'  运营  \');')

        tdSql.execute('insert into tba1 values (\'2021-11-11 09:00:19\',true, 0,0,0,0,0,0," taOs Data  ", "  涛思数据2022 ",0,0,0,0,\'guang li\',\'销售：\');')

        self.restartTaosd(1, dbname='substr_func')

        tdSql.execute('insert into tba1 values (\'2021-11-11 09:00:20\',true, 1,1,1,1,1,1,"aBc", "AbCd",1,1,1,1,\'  TaosdAta  \',\'  tDenGine  \');')

        tdSql.execute('insert into tba1 values (\'2021-11-11 09:00:21\',true, 2,2,2,2,2,2,"Bcd", "BcdE",2,2,2,2,\'  jeFF tAo\',\'Jeff Tao\');')

        tdSql.execute('insert into tba1 values (\'2021-11-11 09:00:22\',true, 3,3,2,3,3,3,"cdE", "cdEf",3,3,3,3,\'  Steven Li\',\'SteVeN LI\');')

        tdSql.execute('insert into tba1 values (\'2021-11-11 09:00:23\',false,4,4,4,4,4,4,"deF", "deFg",4,4,4,4,\'  Wade Zhang\',\' Wade ZhAng  \');')

        tdSql.execute('insert into tba1 values (\'2021-11-11 09:00:24\',true, 5,5,5,5,5,5,"efG", "efGH",5,5,5,5,\' simOnGuan\',\'shengliang guaN  \');')

        tdSql.execute('insert into tba1 values (\'2021-11-11 09:00:25\',true, 6,6,6,6,6,6,"f Gh", " fgHk ",6,6,6,6,\' Ph. D. Liao\',\'haojun Liao  \');')

        tdSql.execute('insert into tba1 values (\'2021-11-11 09:00:26\',true, 7,7,7,7,7,7,"ghi  ", "  ghiJ ",7,7,7,7,\' hongZe cheng \',\'  hongzecheng\');')

        tdSql.execute('insert into tba1 values (\'2021-11-11 09:00:27\',true, 8,8,8,8,8,8,"ghi  ", "  涛思数据 ",8,8,8,8,\' jiangyi HOU\',\'hou jiangyi\');')

        tdSql.execute('insert into tba1 values (\'2021-11-11 09:00:28\',true, 9,9,9,9,9,9," taos Data  ", "  涛思数据 ",9,9,9,9,\' Jing Wang\',\'  运营  \');')

        tdSql.execute('insert into tba1 values (\'2021-11-11 09:00:29\',true, 0,0,0,0,0,0," taOs Data  ", "  涛思数据2022 ",0,0,0,0,\'guang li\',\'销售：\');')

        tdSql.error('select substr(*,1,4) from tb1;')

        tdSql.error('select substr(*, -1, 1) + 1 as a from tb1;')

        tdSql.error('select substr(tb1.*, -1 ,1) + 1 as a from tb1;')

        tdSql.error('select substr(*,1,4) from tb1;')

        tdSql.error('select substr(c1,1,4) from tb1;')

        tdSql.error('select substr(13,1,4) from tb1;')

        tdSql.error('select substr(c1,1,4) from tb1;')

        tdSql.error('select substr(c2,1,4) from tb1;')

        tdSql.error('select substr(c3,1,4) from tb1;')

        tdSql.error('select substr(c4,1,4) from tb1;')

        tdSql.error('select substr(c5,1,4) from tb1;')

        tdSql.error('select substr(c6,1,4) from tb1;')

        tdSql.error('select substr(c7,1,4) from tb1;')

        tdSql.query('select substr(c8,1,4) from tb1;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '123')
        tdSql.checkData(1, 0, None)
        tdSql.checkData(2, 0, '234')
        tdSql.checkData(3, 0, None)
        tdSql.checkData(4, 0, '456')
        tdSql.checkData(5, 0, '567')
        tdSql.checkData(6, 0, '678')

        tdSql.query('select substr(c14,1,4) from tb1;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '  Ta')
        tdSql.checkData(1, 0, None)
        tdSql.checkData(2, 0, ' Xia')
        tdSql.checkData(3, 0, None)
        tdSql.checkData(4, 0, '  Wa')
        tdSql.checkData(5, 0, '27')
        tdSql.checkData(6, 0, '')

        tdSql.error('select substr(c10,1,4) from tb1;')

        tdSql.error('select substr(c11,1,4) from tb1;')

        tdSql.error('select substr(c12,1,4) from tb1;')

        tdSql.error('select substr(c13,1,4) from tb1;')

        tdSql.query("select substr('12345678900000000000000000',1,4) from tb1;")
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '1234')
        tdSql.checkData(1, 0, '1234')
        tdSql.checkData(2, 0, '1234')
        tdSql.checkData(3, 0, '1234')
        tdSql.checkData(4, 0, '1234')
        tdSql.checkData(5, 0, '1234')
        tdSql.checkData(6, 0, '1234')

        tdSql.error("select distinct substr('123',1,4) from tb1;")

        tdSql.error('select substr(t1,1,4) from stb1;')

        tdSql.error('select substr(c8, -1 , 1),avg(c3) from tb1;')

        tdSql.error('select substr(c8, -1 , 1),top(c3,1) from tb1;')

        tdSql.error('select substr(concat(c8,c14),1,4) from tb1 session(ts, 1s);')

        tdSql.error('select substr(concat(c8,c14),1,4) from tb1 STATE_WINDOW(c1);')

        tdSql.error('select substr(concat(c8,c14),1,4) from tb1 interval(1s) sliding(1s) fill(NULL);')

        tdSql.error('select substr(concat(c8,c14),1,4) from stb1 group by t1;')

        tdSql.error('select substr(concat(c8,c14),1,4) from stb1 group by ts;')

        tdSql.error('select substr(concat(c8,c14),1,4) from stb1 group by c1;')

        tdSql.error('select substr(concat(c8,c14),1,4) from stb1 group by tbname;')

        tdSql.error('select substr(concat(c8,c14),1,4) from tb1 order by c2;')

        tdSql.query('select substr(c8, -1, 1),substr(c14,1,4) from tbn;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, 'c')
        tdSql.checkData(0, 1, '  Ta')
        tdSql.checkData(1, 0, 'd')
        tdSql.checkData(1, 1, '  je')
        tdSql.checkData(2, 0, 'E')
        tdSql.checkData(2, 1, '  St')
        tdSql.checkData(3, 0, 'F')
        tdSql.checkData(3, 1, '  Wa')
        tdSql.checkData(4, 0, 'G')
        tdSql.checkData(4, 1, ' sim')
        tdSql.checkData(5, 0, 'h')
        tdSql.checkData(5, 1, ' Ph.')
        tdSql.checkData(6, 0, ' ')
        tdSql.checkData(6, 1, ' hon')

        tdSql.error('select substr(ts,1,4) from (select avg(c2) as a from stb1 interval(1s));')

        tdSql.query('select substr(cast(a as binary(10)),1,4) from (select abs(c2) as a from tb1);')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '1')
        tdSql.checkData(1, 0, None)
        tdSql.checkData(2, 0, '2')
        tdSql.checkData(3, 0, None)
        tdSql.checkData(4, 0, '4')
        tdSql.checkData(5, 0, '127')
        tdSql.checkData(6, 0, '127')

        tdSql.query('select substr("abc",1,4) from tb1;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, 'abc')
        tdSql.checkData(1, 0, 'abc')
        tdSql.checkData(2, 0, 'abc')
        tdSql.checkData(3, 0, 'abc')
        tdSql.checkData(4, 0, 'abc')
        tdSql.checkData(5, 0, 'abc')
        tdSql.checkData(6, 0, 'abc')

        tdSql.query('select substr(concat(c8,c14),1,4) from tb1;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '123 ')
        tdSql.checkData(1, 0, None)
        tdSql.checkData(2, 0, '234 ')
        tdSql.checkData(3, 0, None)
        tdSql.checkData(4, 0, '456 ')
        tdSql.checkData(5, 0, '5672')
        tdSql.checkData(6, 0, '678')

        tdSql.query('select substr((concat(c8,c14)),1,4) from tb1;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '123 ')
        tdSql.checkData(1, 0, None)
        tdSql.checkData(2, 0, '234 ')
        tdSql.checkData(3, 0, None)
        tdSql.checkData(4, 0, '456 ')
        tdSql.checkData(5, 0, '5672')
        tdSql.checkData(6, 0, '678')

        tdSql.error("select substr('11', -1 ,1)+c2 from tb1;")

        tdSql.error('select substr(c8, -4 ,2)+c2 from tb1;')

        tdSql.error('select substr(c14, 2, 2)+11 from tb1;')

        tdSql.query('select substr(c8, 1, 2),c8,c2 from tb1;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '12')
        tdSql.checkData(0, 1, '123')
        tdSql.checkData(0, 2, 1)
        tdSql.checkData(1, 0, None)
        tdSql.checkData(1, 1, None)
        tdSql.checkData(1, 2, None)
        tdSql.checkData(2, 0, '23')
        tdSql.checkData(2, 1, '234')
        tdSql.checkData(2, 2, 2)
        tdSql.checkData(3, 0, None)
        tdSql.checkData(3, 1, None)
        tdSql.checkData(3, 2, None)
        tdSql.checkData(4, 0, '45')
        tdSql.checkData(4, 1, '456')
        tdSql.checkData(4, 2, 4)
        tdSql.checkData(5, 0, '56')
        tdSql.checkData(5, 1, '567')
        tdSql.checkData(5, 2, 127)
        tdSql.checkData(6, 0, '67')
        tdSql.checkData(6, 1, '678')
        tdSql.checkData(6, 2, -127)

        tdSql.query('select substr(c8, 2, 3),t1,ts,tbname,_C0,_c0 from tb1;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '23')
        tdSql.checkData(0, 1, 1)
        tdSql.checkData(0, 2, datetime.datetime(2021, 11, 11, 9, 0))
        tdSql.checkData(0, 3, 'tb1')
        tdSql.checkData(0, 4, datetime.datetime(2021, 11, 11, 9, 0))
        tdSql.checkData(0, 5, datetime.datetime(2021, 11, 11, 9, 0))
        tdSql.checkData(1, 0, None)
        tdSql.checkData(1, 1, 1)
        tdSql.checkData(1, 2, datetime.datetime(2021, 11, 11, 9, 0, 1))
        tdSql.checkData(1, 3, 'tb1')
        tdSql.checkData(1, 4, datetime.datetime(2021, 11, 11, 9, 0, 1))
        tdSql.checkData(1, 5, datetime.datetime(2021, 11, 11, 9, 0, 1))
        tdSql.checkData(2, 0, '34')
        tdSql.checkData(2, 1, 1)
        tdSql.checkData(2, 2, datetime.datetime(2021, 11, 11, 9, 0, 2))
        tdSql.checkData(2, 3, 'tb1')
        tdSql.checkData(2, 4, datetime.datetime(2021, 11, 11, 9, 0, 2))
        tdSql.checkData(2, 5, datetime.datetime(2021, 11, 11, 9, 0, 2))
        tdSql.checkData(3, 0, None)
        tdSql.checkData(3, 1, 1)
        tdSql.checkData(3, 2, datetime.datetime(2021, 11, 11, 9, 0, 3))
        tdSql.checkData(3, 3, 'tb1')
        tdSql.checkData(3, 4, datetime.datetime(2021, 11, 11, 9, 0, 3))
        tdSql.checkData(3, 5, datetime.datetime(2021, 11, 11, 9, 0, 3))
        tdSql.checkData(4, 0, '56')
        tdSql.checkData(4, 1, 1)
        tdSql.checkData(4, 2, datetime.datetime(2021, 11, 11, 9, 0, 4))
        tdSql.checkData(4, 3, 'tb1')
        tdSql.checkData(4, 4, datetime.datetime(2021, 11, 11, 9, 0, 4))
        tdSql.checkData(4, 5, datetime.datetime(2021, 11, 11, 9, 0, 4))
        tdSql.checkData(5, 0, '67')
        tdSql.checkData(5, 1, 1)
        tdSql.checkData(5, 2, datetime.datetime(2021, 11, 11, 9, 0, 5))
        tdSql.checkData(5, 3, 'tb1')
        tdSql.checkData(5, 4, datetime.datetime(2021, 11, 11, 9, 0, 5))
        tdSql.checkData(5, 5, datetime.datetime(2021, 11, 11, 9, 0, 5))
        tdSql.checkData(6, 0, '78')
        tdSql.checkData(6, 1, 1)
        tdSql.checkData(6, 2, datetime.datetime(2021, 11, 11, 9, 0, 6))
        tdSql.checkData(6, 3, 'tb1')
        tdSql.checkData(6, 4, datetime.datetime(2021, 11, 11, 9, 0, 6))
        tdSql.checkData(6, 5, datetime.datetime(2021, 11, 11, 9, 0, 6))

        tdSql.query('select substr(c8, -3, 2),floor(c3) from tb1;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '12')
        tdSql.checkData(0, 1, 1)
        tdSql.checkData(1, 0, None)
        tdSql.checkData(1, 1, None)
        tdSql.checkData(2, 0, '23')
        tdSql.checkData(2, 1, None)
        tdSql.checkData(3, 0, None)
        tdSql.checkData(3, 1, 3)
        tdSql.checkData(4, 0, '45')
        tdSql.checkData(4, 1, 4)
        tdSql.checkData(5, 0, '56')
        tdSql.checkData(5, 1, 32767)
        tdSql.checkData(6, 0, '67')
        tdSql.checkData(6, 1, -32767)

        tdSql.query('select substr(c8, -2, 3),substr(concat(c8,c14),1,4) from tb1;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '23')
        tdSql.checkData(0, 1, '123 ')
        tdSql.checkData(1, 0, None)
        tdSql.checkData(1, 1, None)
        tdSql.checkData(2, 0, '34')
        tdSql.checkData(2, 1, '234 ')
        tdSql.checkData(3, 0, None)
        tdSql.checkData(3, 1, None)
        tdSql.checkData(4, 0, '56')
        tdSql.checkData(4, 1, '456 ')
        tdSql.checkData(5, 0, '67')
        tdSql.checkData(5, 1, '5672')
        tdSql.checkData(6, 0, '78')
        tdSql.checkData(6, 1, '678')

        tdSql.query('select substr(concat(c8,c14),1,4) from tb1 where c9 is not null and c15 is not null;')
        tdSql.checkRows(5)
        tdSql.checkData(0, 0, '123 ')
        tdSql.checkData(1, 0, None)
        tdSql.checkData(2, 0, '456 ')
        tdSql.checkData(3, 0, '5672')
        tdSql.checkData(4, 0, '678')

        tdSql.query('select substr(c14,1,4) from tb1 order by ts desc;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '')
        tdSql.checkData(1, 0, '27')
        tdSql.checkData(2, 0, '  Wa')
        tdSql.checkData(3, 0, None)
        tdSql.checkData(4, 0, ' Xia')
        tdSql.checkData(5, 0, None)
        tdSql.checkData(6, 0, '  Ta')

        tdSql.query('select substr(concat(c8,c14),1,4) from tb1 order by ts desc;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '678')
        tdSql.checkData(1, 0, '5672')
        tdSql.checkData(2, 0, '456 ')
        tdSql.checkData(3, 0, None)
        tdSql.checkData(4, 0, '234 ')
        tdSql.checkData(5, 0, None)
        tdSql.checkData(6, 0, '123 ')

        tdSql.query('select substr(concat(c8,c14),1,4) from tb1 order by ts desc limit 3 offset 2;')
        tdSql.checkRows(3)
        tdSql.checkData(0, 0, '456 ')
        tdSql.checkData(1, 0, None)
        tdSql.checkData(2, 0, '234 ')

        tdSql.query('select substr(c14,1,4) from stb1;')
        tdSql.checkRows(14)
        tdSql.checkData(0, 0, '  Ta')
        tdSql.checkData(1, 0, None)
        tdSql.checkData(2, 0, ' Xia')
        tdSql.checkData(3, 0, None)
        tdSql.checkData(4, 0, '  Wa')
        tdSql.checkData(5, 0, '27')
        tdSql.checkData(6, 0, '')
        tdSql.checkData(7, 0, '  Ta')
        tdSql.checkData(8, 0, '  je')
        tdSql.checkData(9, 0, '  St')
        tdSql.checkData(10, 0, '  Wa')
        tdSql.checkData(11, 0, ' sim')
        tdSql.checkData(12, 0, ' Ph.')
        tdSql.checkData(13, 0, ' hon')

        tdSql.query('select substr(c14,1,4) from stb1 order by ts desc;')
        tdSql.checkRows(14)

        tdSql.query('select substr(c14, 1, 1),t1 from stb1 order by ts desc;')
        tdSql.checkRows(14)

        tdSql.query('select substr(c15, 0, 3),tbname from stb1;')
        tdSql.checkRows(14)
        tdSql.checkData(0, 0, '')
        tdSql.checkData(0, 1, 'tb1')
        tdSql.checkData(1, 0, None)
        tdSql.checkData(1, 1, 'tb1')
        tdSql.checkData(2, 0, None)
        tdSql.checkData(2, 1, 'tb1')
        tdSql.checkData(3, 0, '')
        tdSql.checkData(3, 1, 'tb1')
        tdSql.checkData(4, 0, '')
        tdSql.checkData(4, 1, 'tb1')
        tdSql.checkData(5, 0, '')
        tdSql.checkData(5, 1, 'tb1')
        tdSql.checkData(6, 0, '')
        tdSql.checkData(6, 1, 'tb1')
        tdSql.checkData(7, 0, '')
        tdSql.checkData(7, 1, 'tb2')
        tdSql.checkData(8, 0, '')
        tdSql.checkData(8, 1, 'tb2')
        tdSql.checkData(9, 0, '')
        tdSql.checkData(9, 1, 'tb2')
        tdSql.checkData(10, 0, '')
        tdSql.checkData(10, 1, 'tb2')
        tdSql.checkData(11, 0, '')
        tdSql.checkData(11, 1, 'tb2')
        tdSql.checkData(12, 0, '')
        tdSql.checkData(12, 1, 'tb2')
        tdSql.checkData(13, 0, '')
        tdSql.checkData(13, 1, 'tb2')

        tdSql.query('select substr(c15, 0, 5),tbname from stb1 where t1 > 1;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '')
        tdSql.checkData(0, 1, 'tb2')
        tdSql.checkData(1, 0, '')
        tdSql.checkData(1, 1, 'tb2')
        tdSql.checkData(2, 0, '')
        tdSql.checkData(2, 1, 'tb2')
        tdSql.checkData(3, 0, '')
        tdSql.checkData(3, 1, 'tb2')
        tdSql.checkData(4, 0, '')
        tdSql.checkData(4, 1, 'tb2')
        tdSql.checkData(5, 0, '')
        tdSql.checkData(5, 1, 'tb2')
        tdSql.checkData(6, 0, '')
        tdSql.checkData(6, 1, 'tb2')

        tdSql.query('select substr(c8, 2, 3),substr(c14,1,4) from tbn;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, 'Bc')
        tdSql.checkData(0, 1, '  Ta')
        tdSql.checkData(1, 0, 'cd')
        tdSql.checkData(1, 1, '  je')
        tdSql.checkData(2, 0, 'dE')
        tdSql.checkData(2, 1, '  St')
        tdSql.checkData(3, 0, 'eF')
        tdSql.checkData(3, 1, '  Wa')
        tdSql.checkData(4, 0, 'fG')
        tdSql.checkData(4, 1, ' sim')
        tdSql.checkData(5, 0, ' Gh')
        tdSql.checkData(5, 1, ' Ph.')
        tdSql.checkData(6, 0, 'hi ')
        tdSql.checkData(6, 1, ' hon')

        tdSql.query('select substr(c8, 2, 5),substr(c14,1,4) from tbn order by ts desc;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, 'hi  ')
        tdSql.checkData(0, 1, ' hon')
        tdSql.checkData(1, 0, ' Gh')
        tdSql.checkData(1, 1, ' Ph.')
        tdSql.checkData(2, 0, 'fG')
        tdSql.checkData(2, 1, ' sim')
        tdSql.checkData(3, 0, 'eF')
        tdSql.checkData(3, 1, '  Wa')
        tdSql.checkData(4, 0, 'dE')
        tdSql.checkData(4, 1, '  St')
        tdSql.checkData(5, 0, 'cd')
        tdSql.checkData(5, 1, '  je')
        tdSql.checkData(6, 0, 'Bc')
        tdSql.checkData(6, 1, '  Ta')

        tdSql.query('select substr(substr(c8, 0, 5),1,4) from tbn;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '')
        tdSql.checkData(1, 0, '')
        tdSql.checkData(2, 0, '')
        tdSql.checkData(3, 0, '')
        tdSql.checkData(4, 0, '')
        tdSql.checkData(5, 0, '')
        tdSql.checkData(6, 0, '')

        tdSql.query('select substr(cast(a as binary(10)),1,4) from (select avg(c2) as a from stb1 interval(1s));')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '1.00')
        tdSql.checkData(1, 0, '2.00')
        tdSql.checkData(2, 0, '2.50')
        tdSql.checkData(3, 0, '4.00')
        tdSql.checkData(4, 0, '4.50')
        tdSql.checkData(5, 0, '66.5')
        tdSql.checkData(6, 0, '-60.')

        tdSql.query('select substr(c14,1,4) from (select * from stb1);')
        tdSql.checkRows(14)
        tdSql.checkData(0, 0, '  Ta')
        tdSql.checkData(1, 0, None)
        tdSql.checkData(2, 0, ' Xia')
        tdSql.checkData(3, 0, None)
        tdSql.checkData(4, 0, '  Wa')
        tdSql.checkData(5, 0, '27')
        tdSql.checkData(6, 0, '')
        tdSql.checkData(7, 0, '  Ta')
        tdSql.checkData(8, 0, '  je')
        tdSql.checkData(9, 0, '  St')
        tdSql.checkData(10, 0, '  Wa')
        tdSql.checkData(11, 0, ' sim')
        tdSql.checkData(12, 0, ' Ph.')
        tdSql.checkData(13, 0, ' hon')

        tdSql.query("select substr(cast(a as binary(10)),1,4) from (select avg(c2) as a from stb1 where ts >= '2021-11-11 09:00:00.000' and ts <= '2021-11-11 09:00:09.000' interval(1s) fill(null));")
        tdSql.checkRows(10)
        tdSql.checkData(0, 0, '1.00')
        tdSql.checkData(1, 0, '2.00')
        tdSql.checkData(2, 0, '2.50')
        tdSql.checkData(3, 0, '4.00')
        tdSql.checkData(4, 0, '4.50')
        tdSql.checkData(5, 0, '66.5')
        tdSql.checkData(6, 0, '-60.')
        tdSql.checkData(7, 0, None)
        tdSql.checkData(8, 0, None)
        tdSql.checkData(9, 0, None)

        tdSql.query("select substr(cast(a as binary(10)),1,4) from (select avg(c2) as a from stb1 where ts >= '2021-11-11 09:00:00.000' and ts <= '2021-11-11 09:00:09.000' interval(1s) fill(null)) order by ts;")
        tdSql.checkRows(10)
        tdSql.checkData(0, 0, '1.00')
        tdSql.checkData(1, 0, '2.00')
        tdSql.checkData(2, 0, '2.50')
        tdSql.checkData(3, 0, '4.00')
        tdSql.checkData(4, 0, '4.50')
        tdSql.checkData(5, 0, '66.5')
        tdSql.checkData(6, 0, '-60.')
        tdSql.checkData(7, 0, None)
        tdSql.checkData(8, 0, None)
        tdSql.checkData(9, 0, None)

        tdSql.query("select substr(cast(a as binary(10)),1,4) from (select avg(c2) as a from stb1 where ts >= '2021-11-11 09:00:00.000' and ts <= '2021-11-11 09:00:09.000' interval(1s) fill(null)) order by ts desc;")
        tdSql.checkRows(10)
        tdSql.checkData(0, 0, None)
        tdSql.checkData(1, 0, None)
        tdSql.checkData(2, 0, None)
        tdSql.checkData(3, 0, '-60.')
        tdSql.checkData(4, 0, '66.5')
        tdSql.checkData(5, 0, '4.50')
        tdSql.checkData(6, 0, '4.00')
        tdSql.checkData(7, 0, '2.50')
        tdSql.checkData(8, 0, '2.00')
        tdSql.checkData(9, 0, '1.00')

        tdSql.query("select substr(cast(a as binary(10)),1,4) from (select avg(c2) as a from stb1 where ts >= '2021-11-11 09:00:00.000' and ts <= '2021-11-11 09:00:09.000' interval(1s) fill(null)) order by a desc;")
        tdSql.checkRows(10)
        tdSql.checkData(0, 0, None)
        tdSql.checkData(1, 0, None)
        tdSql.checkData(2, 0, None)
        tdSql.checkData(3, 0, '66.5')
        tdSql.checkData(4, 0, '4.50')
        tdSql.checkData(5, 0, '4.00')
        tdSql.checkData(6, 0, '2.50')
        tdSql.checkData(7, 0, '2.00')
        tdSql.checkData(8, 0, '1.00')
        tdSql.checkData(9, 0, '-60.')

        tdSql.query("select substr(cast(a as binary(10)),1,4) from (select avg(c2) as a from stb1 where ts >= '2021-11-11 09:00:00.000' and ts <= '2021-11-11 09:00:09.000' interval(1s) fill(null)) order by a;")
        tdSql.checkRows(10)
        tdSql.checkData(0, 0, '-60.')
        tdSql.checkData(1, 0, '1.00')
        tdSql.checkData(2, 0, '2.00')
        tdSql.checkData(3, 0, '2.50')
        tdSql.checkData(4, 0, '4.00')
        tdSql.checkData(5, 0, '4.50')
        tdSql.checkData(6, 0, '66.5')
        tdSql.checkData(7, 0, None)
        tdSql.checkData(8, 0, None)
        tdSql.checkData(9, 0, None)

        tdSql.query('select substr(cast(a as binary(10)),1,4) from (select abs(c2) as a from tb1);')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '1')
        tdSql.checkData(1, 0, None)
        tdSql.checkData(2, 0, '2')
        tdSql.checkData(3, 0, None)
        tdSql.checkData(4, 0, '4')
        tdSql.checkData(5, 0, '127')
        tdSql.checkData(6, 0, '127')

        tdSql.query('select substr(tb1.c15, 2, 3),substr(tb2.c15,1,4) from tb1,tb2 where tb1.ts=tb2.ts;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, ' tD')
        tdSql.checkData(0, 1, '  tD')
        tdSql.checkData(1, 0, None)
        tdSql.checkData(1, 1, 'Jeff')
        tdSql.checkData(2, 0, None)
        tdSql.checkData(2, 1, 'SteV')
        tdSql.checkData(3, 0, 'Oxi')
        tdSql.checkData(3, 1, ' Wad')
        tdSql.checkData(4, 0, 'Wad')
        tdSql.checkData(4, 1, 'shen')
        tdSql.checkData(5, 0, '78')
        tdSql.checkData(5, 1, 'haoj')
        tdSql.checkData(6, 0, '')
        tdSql.checkData(6, 1, '  ho')

        tdSql.query('select substr(c15,1,4) from tb1 union all select substr(c15,1,4) from tb2;')
        tdSql.checkRows(14)
        tdSql.checkData(0, 0, '  tD')
        tdSql.checkData(1, 0, None)
        tdSql.checkData(2, 0, None)
        tdSql.checkData(3, 0, 'bOxi')
        tdSql.checkData(4, 0, ' Wad')
        tdSql.checkData(5, 0, '278')
        tdSql.checkData(6, 0, '')
        tdSql.checkData(7, 0, '  tD')
        tdSql.checkData(8, 0, 'Jeff')
        tdSql.checkData(9, 0, 'SteV')
        tdSql.checkData(10, 0, ' Wad')
        tdSql.checkData(11, 0, 'shen')
        tdSql.checkData(12, 0, 'haoj')
        tdSql.checkData(13, 0, '  ho')

        tdSql.error('select substr(*,1) from tb1;')

        tdSql.error('select substr(*, -1) + 1 as a from tb1;')

        tdSql.error('select substr(tb1.*, -1 ) + 1 as a from tb1;')

        tdSql.error('select substr(*,1) from tb1;')

        tdSql.error('select substr(c1,1) from tb1;')

        tdSql.error('select substr(13,1) from tb1;')

        tdSql.error('select substr(c1,1) from tb1;')

        tdSql.error('select substr(c2,1) from tb1;')

        tdSql.error('select substr(c3,1) from tb1;')

        tdSql.error('select substr(c4,1) from tb1;')

        tdSql.error('select substr(c5,1) from tb1;')

        tdSql.error('select substr(c6,1) from tb1;')

        tdSql.error('select substr(c7,1) from tb1;')

        tdSql.query('select substr(c8,1) from tb1;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '123')
        tdSql.checkData(1, 0, None)
        tdSql.checkData(2, 0, '234')
        tdSql.checkData(3, 0, None)
        tdSql.checkData(4, 0, '456')
        tdSql.checkData(5, 0, '567')
        tdSql.checkData(6, 0, '678')

        tdSql.query('select substr(c14,1) from tb1;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '  TaosdAta  ')
        tdSql.checkData(1, 0, None)
        tdSql.checkData(2, 0, ' XiaoBo ')
        tdSql.checkData(3, 0, None)
        tdSql.checkData(4, 0, '  Wade Zhang')
        tdSql.checkData(5, 0, '27')
        tdSql.checkData(6, 0, '')

        tdSql.error('select substr(c10,1) from tb1;')

        tdSql.error('select substr(c11,1) from tb1;')

        tdSql.error('select substr(c12,1) from tb1;')

        tdSql.error('select substr(c13,1) from tb1;')

        tdSql.query("select substr('12345678900000000000000000',1) from tb1;")
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '12345678900000000000000000')
        tdSql.checkData(1, 0, '12345678900000000000000000')
        tdSql.checkData(2, 0, '12345678900000000000000000')
        tdSql.checkData(3, 0, '12345678900000000000000000')
        tdSql.checkData(4, 0, '12345678900000000000000000')
        tdSql.checkData(5, 0, '12345678900000000000000000')
        tdSql.checkData(6, 0, '12345678900000000000000000')

        tdSql.error("select distinct substr('123',1) from tb1;")

        tdSql.error('select substr(t1,1) from stb1;')

        tdSql.error('select substr(c8, -1 ),avg(c3) from tb1;')

        tdSql.error('select substr(c8, -1 ),top(c3,1) from tb1;')

        tdSql.error('select substr(concat(c8,c14),1) from tb1 session(ts, 1s);')

        tdSql.error('select substr(concat(c8,c14),1) from tb1 STATE_WINDOW(c1);')

        tdSql.error('select substr(concat(c8,c14),1) from tb1 interval(1s) sliding(1s) fill(NULL);')

        tdSql.error('select substr(concat(c8,c14),1) from stb1 group by t1;')

        tdSql.error('select substr(concat(c8,c14),1) from stb1 group by ts;')

        tdSql.error('select substr(concat(c8,c14),1) from stb1 group by c1;')

        tdSql.error('select substr(concat(c8,c14),1) from stb1 group by tbname;')

        tdSql.error('select substr(concat(c8,c14),1) from tb1 order by c2;')

        tdSql.query('select substr(c8, -1),substr(c14,1) from tbn;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, 'c')
        tdSql.checkData(0, 1, '  TaosdAta  ')
        tdSql.checkData(1, 0, 'd')
        tdSql.checkData(1, 1, '  jeFF tAo')
        tdSql.checkData(2, 0, 'E')
        tdSql.checkData(2, 1, '  Steven Li')
        tdSql.checkData(3, 0, 'F')
        tdSql.checkData(3, 1, '  Wade Zhang')
        tdSql.checkData(4, 0, 'G')
        tdSql.checkData(4, 1, ' simOnGuan')
        tdSql.checkData(5, 0, 'h')
        tdSql.checkData(5, 1, ' Ph. D. Liao')
        tdSql.checkData(6, 0, ' ')
        tdSql.checkData(6, 1, ' hongZe cheng ')

        tdSql.error('select substr(ts,1) from (select avg(c2) as a from stb1 interval(1s));')

        tdSql.query('select substr(cast(a as binary(10)),1) from (select abs(c2) as a from tb1);')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '1')
        tdSql.checkData(1, 0, None)
        tdSql.checkData(2, 0, '2')
        tdSql.checkData(3, 0, None)
        tdSql.checkData(4, 0, '4')
        tdSql.checkData(5, 0, '127')
        tdSql.checkData(6, 0, '127')

        tdSql.query('select substr("abc",1) from tb1;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, 'abc')
        tdSql.checkData(1, 0, 'abc')
        tdSql.checkData(2, 0, 'abc')
        tdSql.checkData(3, 0, 'abc')
        tdSql.checkData(4, 0, 'abc')
        tdSql.checkData(5, 0, 'abc')
        tdSql.checkData(6, 0, 'abc')

        tdSql.query('select substr(concat(c8,c14),1) from tb1;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '123  TaosdAta  ')
        tdSql.checkData(1, 0, None)
        tdSql.checkData(2, 0, '234 XiaoBo ')
        tdSql.checkData(3, 0, None)
        tdSql.checkData(4, 0, '456  Wade Zhang')
        tdSql.checkData(5, 0, '56727')
        tdSql.checkData(6, 0, '678')

        tdSql.query('select substr((concat(c8,c14)),1) from tb1;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '123  TaosdAta  ')
        tdSql.checkData(1, 0, None)
        tdSql.checkData(2, 0, '234 XiaoBo ')
        tdSql.checkData(3, 0, None)
        tdSql.checkData(4, 0, '456  Wade Zhang')
        tdSql.checkData(5, 0, '56727')
        tdSql.checkData(6, 0, '678')

        tdSql.error("select substr('11', -1 )+c2 from tb1;")

        tdSql.error('select substr(c8, -4 )+c2 from tb1;')

        tdSql.error('select substr(c14, 2)+11 from tb1;')

        tdSql.query('select substr(c8, 1),c8,c2 from tb1;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '123')
        tdSql.checkData(0, 1, '123')
        tdSql.checkData(0, 2, 1)
        tdSql.checkData(1, 0, None)
        tdSql.checkData(1, 1, None)
        tdSql.checkData(1, 2, None)
        tdSql.checkData(2, 0, '234')
        tdSql.checkData(2, 1, '234')
        tdSql.checkData(2, 2, 2)
        tdSql.checkData(3, 0, None)
        tdSql.checkData(3, 1, None)
        tdSql.checkData(3, 2, None)
        tdSql.checkData(4, 0, '456')
        tdSql.checkData(4, 1, '456')
        tdSql.checkData(4, 2, 4)
        tdSql.checkData(5, 0, '567')
        tdSql.checkData(5, 1, '567')
        tdSql.checkData(5, 2, 127)
        tdSql.checkData(6, 0, '678')
        tdSql.checkData(6, 1, '678')
        tdSql.checkData(6, 2, -127)

        tdSql.query('select substr(c8, 2),t1,ts,tbname,_C0,_c0 from tb1;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '23')
        tdSql.checkData(0, 1, 1)
        tdSql.checkData(0, 2, datetime.datetime(2021, 11, 11, 9, 0))
        tdSql.checkData(0, 3, 'tb1')
        tdSql.checkData(0, 4, datetime.datetime(2021, 11, 11, 9, 0))
        tdSql.checkData(0, 5, datetime.datetime(2021, 11, 11, 9, 0))
        tdSql.checkData(1, 0, None)
        tdSql.checkData(1, 1, 1)
        tdSql.checkData(1, 2, datetime.datetime(2021, 11, 11, 9, 0, 1))
        tdSql.checkData(1, 3, 'tb1')
        tdSql.checkData(1, 4, datetime.datetime(2021, 11, 11, 9, 0, 1))
        tdSql.checkData(1, 5, datetime.datetime(2021, 11, 11, 9, 0, 1))
        tdSql.checkData(2, 0, '34')
        tdSql.checkData(2, 1, 1)
        tdSql.checkData(2, 2, datetime.datetime(2021, 11, 11, 9, 0, 2))
        tdSql.checkData(2, 3, 'tb1')
        tdSql.checkData(2, 4, datetime.datetime(2021, 11, 11, 9, 0, 2))
        tdSql.checkData(2, 5, datetime.datetime(2021, 11, 11, 9, 0, 2))
        tdSql.checkData(3, 0, None)
        tdSql.checkData(3, 1, 1)
        tdSql.checkData(3, 2, datetime.datetime(2021, 11, 11, 9, 0, 3))
        tdSql.checkData(3, 3, 'tb1')
        tdSql.checkData(3, 4, datetime.datetime(2021, 11, 11, 9, 0, 3))
        tdSql.checkData(3, 5, datetime.datetime(2021, 11, 11, 9, 0, 3))
        tdSql.checkData(4, 0, '56')
        tdSql.checkData(4, 1, 1)
        tdSql.checkData(4, 2, datetime.datetime(2021, 11, 11, 9, 0, 4))
        tdSql.checkData(4, 3, 'tb1')
        tdSql.checkData(4, 4, datetime.datetime(2021, 11, 11, 9, 0, 4))
        tdSql.checkData(4, 5, datetime.datetime(2021, 11, 11, 9, 0, 4))
        tdSql.checkData(5, 0, '67')
        tdSql.checkData(5, 1, 1)
        tdSql.checkData(5, 2, datetime.datetime(2021, 11, 11, 9, 0, 5))
        tdSql.checkData(5, 3, 'tb1')
        tdSql.checkData(5, 4, datetime.datetime(2021, 11, 11, 9, 0, 5))
        tdSql.checkData(5, 5, datetime.datetime(2021, 11, 11, 9, 0, 5))
        tdSql.checkData(6, 0, '78')
        tdSql.checkData(6, 1, 1)
        tdSql.checkData(6, 2, datetime.datetime(2021, 11, 11, 9, 0, 6))
        tdSql.checkData(6, 3, 'tb1')
        tdSql.checkData(6, 4, datetime.datetime(2021, 11, 11, 9, 0, 6))
        tdSql.checkData(6, 5, datetime.datetime(2021, 11, 11, 9, 0, 6))

        tdSql.query('select substr(c8, -3),floor(c3) from tb1;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '123')
        tdSql.checkData(0, 1, 1)
        tdSql.checkData(1, 0, None)
        tdSql.checkData(1, 1, None)
        tdSql.checkData(2, 0, '234')
        tdSql.checkData(2, 1, None)
        tdSql.checkData(3, 0, None)
        tdSql.checkData(3, 1, 3)
        tdSql.checkData(4, 0, '456')
        tdSql.checkData(4, 1, 4)
        tdSql.checkData(5, 0, '567')
        tdSql.checkData(5, 1, 32767)
        tdSql.checkData(6, 0, '678')
        tdSql.checkData(6, 1, -32767)

        tdSql.query('select substr(c8, -2),substr(concat(c8,c14),1) from tb1;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '23')
        tdSql.checkData(0, 1, '123  TaosdAta  ')
        tdSql.checkData(1, 0, None)
        tdSql.checkData(1, 1, None)
        tdSql.checkData(2, 0, '34')
        tdSql.checkData(2, 1, '234 XiaoBo ')
        tdSql.checkData(3, 0, None)
        tdSql.checkData(3, 1, None)
        tdSql.checkData(4, 0, '56')
        tdSql.checkData(4, 1, '456  Wade Zhang')
        tdSql.checkData(5, 0, '67')
        tdSql.checkData(5, 1, '56727')
        tdSql.checkData(6, 0, '78')
        tdSql.checkData(6, 1, '678')

        tdSql.query('select substr(concat(c8,c14),1) from tb1 where c9 is not null and c15 is not null;')
        tdSql.checkRows(5)
        tdSql.checkData(0, 0, '123  TaosdAta  ')
        tdSql.checkData(1, 0, None)
        tdSql.checkData(2, 0, '456  Wade Zhang')
        tdSql.checkData(3, 0, '56727')
        tdSql.checkData(4, 0, '678')

        tdSql.query('select substr(c14,1) from tb1 order by ts desc;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '')
        tdSql.checkData(1, 0, '27')
        tdSql.checkData(2, 0, '  Wade Zhang')
        tdSql.checkData(3, 0, None)
        tdSql.checkData(4, 0, ' XiaoBo ')
        tdSql.checkData(5, 0, None)
        tdSql.checkData(6, 0, '  TaosdAta  ')

        tdSql.query('select substr(concat(c8,c14),1) from tb1 order by ts desc;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '678')
        tdSql.checkData(1, 0, '56727')
        tdSql.checkData(2, 0, '456  Wade Zhang')
        tdSql.checkData(3, 0, None)
        tdSql.checkData(4, 0, '234 XiaoBo ')
        tdSql.checkData(5, 0, None)
        tdSql.checkData(6, 0, '123  TaosdAta  ')

        tdSql.query('select substr(concat(c8,c14),1) from tb1 order by ts desc limit 3 offset 2;')
        tdSql.checkRows(3)
        tdSql.checkData(0, 0, '456  Wade Zhang')
        tdSql.checkData(1, 0, None)
        tdSql.checkData(2, 0, '234 XiaoBo ')

        tdSql.query('select substr(c14,1) from stb1;')
        tdSql.checkRows(14)
        tdSql.checkData(0, 0, '  TaosdAta  ')
        tdSql.checkData(1, 0, None)
        tdSql.checkData(2, 0, ' XiaoBo ')
        tdSql.checkData(3, 0, None)
        tdSql.checkData(4, 0, '  Wade Zhang')
        tdSql.checkData(5, 0, '27')
        tdSql.checkData(6, 0, '')
        tdSql.checkData(7, 0, '  TaosdAta  ')
        tdSql.checkData(8, 0, '  jeFF tAo')
        tdSql.checkData(9, 0, '  Steven Li')
        tdSql.checkData(10, 0, '  Wade Zhang')
        tdSql.checkData(11, 0, ' simOnGuan')
        tdSql.checkData(12, 0, ' Ph. D. Liao')
        tdSql.checkData(13, 0, ' hongZe cheng ')

        tdSql.query('select substr(c14,1) from stb1 order by ts desc;')
        tdSql.checkRows(14)

        tdSql.query('select substr(c14, 1),t1 from stb1 order by ts desc;')
        tdSql.checkRows(14)

        tdSql.query('select substr(c15, 0),tbname from stb1;')
        tdSql.checkRows(14)
        tdSql.checkData(0, 0, '')
        tdSql.checkData(0, 1, 'tb1')
        tdSql.checkData(1, 0, None)
        tdSql.checkData(1, 1, 'tb1')
        tdSql.checkData(2, 0, None)
        tdSql.checkData(2, 1, 'tb1')
        tdSql.checkData(3, 0, '')
        tdSql.checkData(3, 1, 'tb1')
        tdSql.checkData(4, 0, '')
        tdSql.checkData(4, 1, 'tb1')
        tdSql.checkData(5, 0, '')
        tdSql.checkData(5, 1, 'tb1')
        tdSql.checkData(6, 0, '')
        tdSql.checkData(6, 1, 'tb1')
        tdSql.checkData(7, 0, '')
        tdSql.checkData(7, 1, 'tb2')
        tdSql.checkData(8, 0, '')
        tdSql.checkData(8, 1, 'tb2')
        tdSql.checkData(9, 0, '')
        tdSql.checkData(9, 1, 'tb2')
        tdSql.checkData(10, 0, '')
        tdSql.checkData(10, 1, 'tb2')
        tdSql.checkData(11, 0, '')
        tdSql.checkData(11, 1, 'tb2')
        tdSql.checkData(12, 0, '')
        tdSql.checkData(12, 1, 'tb2')
        tdSql.checkData(13, 0, '')
        tdSql.checkData(13, 1, 'tb2')

        tdSql.query('select substr(c15, 0),tbname from stb1 where t1 > 1;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '')
        tdSql.checkData(0, 1, 'tb2')
        tdSql.checkData(1, 0, '')
        tdSql.checkData(1, 1, 'tb2')
        tdSql.checkData(2, 0, '')
        tdSql.checkData(2, 1, 'tb2')
        tdSql.checkData(3, 0, '')
        tdSql.checkData(3, 1, 'tb2')
        tdSql.checkData(4, 0, '')
        tdSql.checkData(4, 1, 'tb2')
        tdSql.checkData(5, 0, '')
        tdSql.checkData(5, 1, 'tb2')
        tdSql.checkData(6, 0, '')
        tdSql.checkData(6, 1, 'tb2')

        tdSql.query('select substr(c8, 2),substr(c14,1) from tbn;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, 'Bc')
        tdSql.checkData(0, 1, '  TaosdAta  ')
        tdSql.checkData(1, 0, 'cd')
        tdSql.checkData(1, 1, '  jeFF tAo')
        tdSql.checkData(2, 0, 'dE')
        tdSql.checkData(2, 1, '  Steven Li')
        tdSql.checkData(3, 0, 'eF')
        tdSql.checkData(3, 1, '  Wade Zhang')
        tdSql.checkData(4, 0, 'fG')
        tdSql.checkData(4, 1, ' simOnGuan')
        tdSql.checkData(5, 0, ' Gh')
        tdSql.checkData(5, 1, ' Ph. D. Liao')
        tdSql.checkData(6, 0, 'hi  ')
        tdSql.checkData(6, 1, ' hongZe cheng ')

        tdSql.query('select substr(c8, 2),substr(c14,1) from tbn order by ts desc;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, 'hi  ')
        tdSql.checkData(0, 1, ' hongZe cheng ')
        tdSql.checkData(1, 0, ' Gh')
        tdSql.checkData(1, 1, ' Ph. D. Liao')
        tdSql.checkData(2, 0, 'fG')
        tdSql.checkData(2, 1, ' simOnGuan')
        tdSql.checkData(3, 0, 'eF')
        tdSql.checkData(3, 1, '  Wade Zhang')
        tdSql.checkData(4, 0, 'dE')
        tdSql.checkData(4, 1, '  Steven Li')
        tdSql.checkData(5, 0, 'cd')
        tdSql.checkData(5, 1, '  jeFF tAo')
        tdSql.checkData(6, 0, 'Bc')
        tdSql.checkData(6, 1, '  TaosdAta  ')

        tdSql.query('select substr(substr(c8, 0),1) from tbn;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '')
        tdSql.checkData(1, 0, '')
        tdSql.checkData(2, 0, '')
        tdSql.checkData(3, 0, '')
        tdSql.checkData(4, 0, '')
        tdSql.checkData(5, 0, '')
        tdSql.checkData(6, 0, '')

        tdSql.query('select substr(cast(a as binary(10)),1) from (select avg(c2) as a from stb1 interval(1s));')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '1.000000')
        tdSql.checkData(1, 0, '2.000000')
        tdSql.checkData(2, 0, '2.500000')
        tdSql.checkData(3, 0, '4.000000')
        tdSql.checkData(4, 0, '4.500000')
        tdSql.checkData(5, 0, '66.500000')
        tdSql.checkData(6, 0, '-60.000000')

        tdSql.query('select substr(c14,1) from (select * from stb1);')
        tdSql.checkRows(14)
        tdSql.checkData(0, 0, '  TaosdAta  ')
        tdSql.checkData(1, 0, None)
        tdSql.checkData(2, 0, ' XiaoBo ')
        tdSql.checkData(3, 0, None)
        tdSql.checkData(4, 0, '  Wade Zhang')
        tdSql.checkData(5, 0, '27')
        tdSql.checkData(6, 0, '')
        tdSql.checkData(7, 0, '  TaosdAta  ')
        tdSql.checkData(8, 0, '  jeFF tAo')
        tdSql.checkData(9, 0, '  Steven Li')
        tdSql.checkData(10, 0, '  Wade Zhang')
        tdSql.checkData(11, 0, ' simOnGuan')
        tdSql.checkData(12, 0, ' Ph. D. Liao')
        tdSql.checkData(13, 0, ' hongZe cheng ')

        tdSql.query("select substr(cast(a as binary(10)),1) from (select avg(c2) as a from stb1 where ts >= '2021-11-11 09:00:00.000' and ts <= '2021-11-11 09:00:09.000' interval(1s) fill(null));")
        tdSql.checkRows(10)
        tdSql.checkData(0, 0, '1.000000')
        tdSql.checkData(1, 0, '2.000000')
        tdSql.checkData(2, 0, '2.500000')
        tdSql.checkData(3, 0, '4.000000')
        tdSql.checkData(4, 0, '4.500000')
        tdSql.checkData(5, 0, '66.500000')
        tdSql.checkData(6, 0, '-60.000000')
        tdSql.checkData(7, 0, None)
        tdSql.checkData(8, 0, None)
        tdSql.checkData(9, 0, None)

        tdSql.query("select substr(cast(a as binary(10)),1) from (select avg(c2) as a from stb1 where ts >= '2021-11-11 09:00:00.000' and ts <= '2021-11-11 09:00:09.000' interval(1s) fill(null)) order by ts;")
        tdSql.checkRows(10)
        tdSql.checkData(0, 0, '1.000000')
        tdSql.checkData(1, 0, '2.000000')
        tdSql.checkData(2, 0, '2.500000')
        tdSql.checkData(3, 0, '4.000000')
        tdSql.checkData(4, 0, '4.500000')
        tdSql.checkData(5, 0, '66.500000')
        tdSql.checkData(6, 0, '-60.000000')
        tdSql.checkData(7, 0, None)
        tdSql.checkData(8, 0, None)
        tdSql.checkData(9, 0, None)

        tdSql.query("select substr(cast(a as binary(10)),1) from (select avg(c2) as a from stb1 where ts >= '2021-11-11 09:00:00.000' and ts <= '2021-11-11 09:00:09.000' interval(1s) fill(null)) order by ts desc;")
        tdSql.checkRows(10)
        tdSql.checkData(0, 0, None)
        tdSql.checkData(1, 0, None)
        tdSql.checkData(2, 0, None)
        tdSql.checkData(3, 0, '-60.000000')
        tdSql.checkData(4, 0, '66.500000')
        tdSql.checkData(5, 0, '4.500000')
        tdSql.checkData(6, 0, '4.000000')
        tdSql.checkData(7, 0, '2.500000')
        tdSql.checkData(8, 0, '2.000000')
        tdSql.checkData(9, 0, '1.000000')

        tdSql.query("select substr(cast(a as binary(10)),1) from (select avg(c2) as a from stb1 where ts >= '2021-11-11 09:00:00.000' and ts <= '2021-11-11 09:00:09.000' interval(1s) fill(null)) order by a desc;")
        tdSql.checkRows(10)
        tdSql.checkData(0, 0, None)
        tdSql.checkData(1, 0, None)
        tdSql.checkData(2, 0, None)
        tdSql.checkData(3, 0, '66.500000')
        tdSql.checkData(4, 0, '4.500000')
        tdSql.checkData(5, 0, '4.000000')
        tdSql.checkData(6, 0, '2.500000')
        tdSql.checkData(7, 0, '2.000000')
        tdSql.checkData(8, 0, '1.000000')
        tdSql.checkData(9, 0, '-60.000000')

        tdSql.query("select substr(cast(a as binary(10)),1) from (select avg(c2) as a from stb1 where ts >= '2021-11-11 09:00:00.000' and ts <= '2021-11-11 09:00:09.000' interval(1s) fill(null)) order by a;")
        tdSql.checkRows(10)
        tdSql.checkData(0, 0, '-60.000000')
        tdSql.checkData(1, 0, '1.000000')
        tdSql.checkData(2, 0, '2.000000')
        tdSql.checkData(3, 0, '2.500000')
        tdSql.checkData(4, 0, '4.000000')
        tdSql.checkData(5, 0, '4.500000')
        tdSql.checkData(6, 0, '66.500000')
        tdSql.checkData(7, 0, None)
        tdSql.checkData(8, 0, None)
        tdSql.checkData(9, 0, None)

        tdSql.query('select substr(cast(a as binary(10)),1) from (select abs(c2) as a from tb1);')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, '1')
        tdSql.checkData(1, 0, None)
        tdSql.checkData(2, 0, '2')
        tdSql.checkData(3, 0, None)
        tdSql.checkData(4, 0, '4')
        tdSql.checkData(5, 0, '127')
        tdSql.checkData(6, 0, '127')

        tdSql.query('select substr(tb1.c15, 2),substr(tb2.c15,1) from tb1,tb2 where tb1.ts=tb2.ts;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, ' tDenGine  ')
        tdSql.checkData(0, 1, '  tDenGine  ')
        tdSql.checkData(1, 0, None)
        tdSql.checkData(1, 1, 'Jeff Tao')
        tdSql.checkData(2, 0, None)
        tdSql.checkData(2, 1, 'SteVeN LI')
        tdSql.checkData(3, 0, 'OxiaO')
        tdSql.checkData(3, 1, ' Wade ZhAng  ')
        tdSql.checkData(4, 0, 'Wade ZhAng  ')
        tdSql.checkData(4, 1, 'shengliang guaN  ')
        tdSql.checkData(5, 0, '78')
        tdSql.checkData(5, 1, 'haojun Liao  ')
        tdSql.checkData(6, 0, '')
        tdSql.checkData(6, 1, '  hongzecheng')

        tdSql.query('select substr(c15,1) from tb1 union all select substr(c15,1) from tb2;')
        tdSql.checkRows(14)
        tdSql.checkData(0, 0, '  tDenGine  ')
        tdSql.checkData(1, 0, None)
        tdSql.checkData(2, 0, None)
        tdSql.checkData(3, 0, 'bOxiaO')
        tdSql.checkData(4, 0, ' Wade ZhAng  ')
        tdSql.checkData(5, 0, '278')
        tdSql.checkData(6, 0, '')
        tdSql.checkData(7, 0, '  tDenGine  ')
        tdSql.checkData(8, 0, 'Jeff Tao')
        tdSql.checkData(9, 0, 'SteVeN LI')
        tdSql.checkData(10, 0, ' Wade ZhAng  ')
        tdSql.checkData(11, 0, 'shengliang guaN  ')
        tdSql.checkData(12, 0, 'haojun Liao  ')
        tdSql.checkData(13, 0, '  hongzecheng')

        tdSql.query('select substr(stb1.c14, -3, 2),substr(stba.c15, 1, 2) from stb1,stba where stb1.t1=stba.t1 and stb1.ts=stba.ts;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, 'a ')
        tdSql.checkData(0, 1, '  ')
        tdSql.checkData(1, 0, None)
        tdSql.checkData(1, 1, 'Je')
        tdSql.checkData(2, 0, 'Bo')
        tdSql.checkData(2, 1, 'St')
        tdSql.checkData(3, 0, None)
        tdSql.checkData(3, 1, ' W')
        tdSql.checkData(4, 0, 'an')
        tdSql.checkData(4, 1, 'sh')
        tdSql.checkData(5, 0, '27')
        tdSql.checkData(5, 1, 'ha')
        tdSql.checkData(6, 0, '')
        tdSql.checkData(6, 1, '  ')

        tdSql.error('select substr(c14, -3, 2) as a from stb1 union all select substr(c15, -3, 2) as a from stba;')

        tdSql.query('select substr(c8, 1, 2) from stba;')
        tdSql.checkRows(30)
        tdSql.checkData(0, 0, 'aB')
        tdSql.checkData(1, 0, 'Bc')
        tdSql.checkData(2, 0, 'cd')
        tdSql.checkData(3, 0, 'de')
        tdSql.checkData(4, 0, 'ef')
        tdSql.checkData(5, 0, 'f ')
        tdSql.checkData(6, 0, 'gh')
        tdSql.checkData(7, 0, 'gh')
        tdSql.checkData(8, 0, ' t')
        tdSql.checkData(9, 0, ' t')
        tdSql.checkData(10, 0, 'aB')
        tdSql.checkData(11, 0, 'Bc')
        tdSql.checkData(12, 0, 'cd')
        tdSql.checkData(13, 0, 'de')
        tdSql.checkData(14, 0, 'ef')
        tdSql.checkData(15, 0, 'f ')
        tdSql.checkData(16, 0, 'gh')
        tdSql.checkData(17, 0, 'gh')
        tdSql.checkData(18, 0, ' t')
        tdSql.checkData(19, 0, ' t')
        tdSql.checkData(20, 0, 'aB')
        tdSql.checkData(21, 0, 'Bc')
        tdSql.checkData(22, 0, 'cd')
        tdSql.checkData(23, 0, 'de')
        tdSql.checkData(24, 0, 'ef')
        tdSql.checkData(25, 0, 'f ')
        tdSql.checkData(26, 0, 'gh')
        tdSql.checkData(27, 0, 'gh')
        tdSql.checkData(28, 0, ' t')
        tdSql.checkData(29, 0, ' t')

        tdSql.query('select substr(c9, 1, 2) from stba;')
        tdSql.checkRows(30)
        tdSql.checkData(0, 0, 'Ab')
        tdSql.checkData(1, 0, 'Bc')
        tdSql.checkData(2, 0, 'cd')
        tdSql.checkData(3, 0, 'de')
        tdSql.checkData(4, 0, 'ef')
        tdSql.checkData(5, 0, ' f')
        tdSql.checkData(6, 0, '  ')
        tdSql.checkData(7, 0, '  ')
        tdSql.checkData(8, 0, '  ')
        tdSql.checkData(9, 0, '  ')
        tdSql.checkData(10, 0, 'Ab')
        tdSql.checkData(11, 0, 'Bc')
        tdSql.checkData(12, 0, 'cd')
        tdSql.checkData(13, 0, 'de')
        tdSql.checkData(14, 0, 'ef')
        tdSql.checkData(15, 0, ' f')
        tdSql.checkData(16, 0, '  ')
        tdSql.checkData(17, 0, '  ')
        tdSql.checkData(18, 0, '  ')
        tdSql.checkData(19, 0, '  ')
        tdSql.checkData(20, 0, 'Ab')
        tdSql.checkData(21, 0, 'Bc')
        tdSql.checkData(22, 0, 'cd')
        tdSql.checkData(23, 0, 'de')
        tdSql.checkData(24, 0, 'ef')
        tdSql.checkData(25, 0, ' f')
        tdSql.checkData(26, 0, '  ')
        tdSql.checkData(27, 0, '  ')
        tdSql.checkData(28, 0, '  ')
        tdSql.checkData(29, 0, '  ')

        tdSql.query('select substr(cast(min(c2) as binary(20)), 1, 2) from tba1;')
        tdSql.checkRows(1)
        tdSql.checkData(0, 0, '0')

        tdSql.query('select substr(cast(max(c2) as binary(20)), 1, 2) from tba1;')
        tdSql.checkRows(1)
        tdSql.checkData(0, 0, '9')

        tdSql.query('select substr(cast(count(c2) as binary(20)), 1, 2) from tba1;')
        tdSql.checkRows(1)
        tdSql.checkData(0, 0, '30')

        tdSql.query('select substr(cast(sum(c2) as binary(20)), 1, 2) from tba1;')
        tdSql.checkRows(1)
        tdSql.checkData(0, 0, '13')

        tdSql.query('select substr(cast(avg(c2) as binary(20)), 1, 2) from tba1;')
        tdSql.checkRows(1)
        tdSql.checkData(0, 0, '4.')

        tdSql.query('select substr(cast(percentile(c2, 10) as binary(20)), 1, 2) from tba1;')
        tdSql.checkRows(1)
        tdSql.checkData(0, 0, '0.')

        tdSql.query('select substr(cast(apercentile(c2, 10) as binary(20)), 1, 2) from tba1;')
        tdSql.checkRows(1)
        tdSql.checkData(0, 0, '0.')

        tdSql.query('select substr(cast(stddev(c2) as binary(20)), 1, 2) from tba1;')
        tdSql.checkRows(1)
        tdSql.checkData(0, 0, '2.')

        tdSql.query('select substr(cast(spread(c2) as binary(20)), 1, 2) from tba1;')
        tdSql.checkRows(1)
        tdSql.checkData(0, 0, '9.')

        tdSql.query('select substr(cast(twa(c2) as binary(20)), 1, 2) from tba1;')
        tdSql.checkRows(1)
        tdSql.checkData(0, 0, '4.')

        tdSql.error('select substr(cast(leastsquares(c2, 1, 1) as binary(20)), 1, 2) from tba1;')

        tdSql.query('select substr(cast(interp(c2) as binary(20)), 1, 2) from tba1 every(1s)')
        tdSql.checkRows(30)
        tdSql.checkData(0, 0, datetime.datetime(2021, 11, 11, 9, 0))
        tdSql.checkData(0, 1, '1')
        tdSql.checkData(1, 0, datetime.datetime(2021, 11, 11, 9, 0, 1))
        tdSql.checkData(1, 1, '2')
        tdSql.checkData(2, 0, datetime.datetime(2021, 11, 11, 9, 0, 2))
        tdSql.checkData(2, 1, '3')
        tdSql.checkData(3, 0, datetime.datetime(2021, 11, 11, 9, 0, 3))
        tdSql.checkData(3, 1, '4')
        tdSql.checkData(4, 0, datetime.datetime(2021, 11, 11, 9, 0, 4))
        tdSql.checkData(4, 1, '5')
        tdSql.checkData(5, 0, datetime.datetime(2021, 11, 11, 9, 0, 5))
        tdSql.checkData(5, 1, '6')
        tdSql.checkData(6, 0, datetime.datetime(2021, 11, 11, 9, 0, 6))
        tdSql.checkData(6, 1, '7')
        tdSql.checkData(7, 0, datetime.datetime(2021, 11, 11, 9, 0, 7))
        tdSql.checkData(7, 1, '8')
        tdSql.checkData(8, 0, datetime.datetime(2021, 11, 11, 9, 0, 8))
        tdSql.checkData(8, 1, '9')
        tdSql.checkData(9, 0, datetime.datetime(2021, 11, 11, 9, 0, 9))
        tdSql.checkData(9, 1, '0')
        tdSql.checkData(10, 0, datetime.datetime(2021, 11, 11, 9, 0, 10))
        tdSql.checkData(10, 1, '1')
        tdSql.checkData(11, 0, datetime.datetime(2021, 11, 11, 9, 0, 11))
        tdSql.checkData(11, 1, '2')
        tdSql.checkData(12, 0, datetime.datetime(2021, 11, 11, 9, 0, 12))
        tdSql.checkData(12, 1, '3')
        tdSql.checkData(13, 0, datetime.datetime(2021, 11, 11, 9, 0, 13))
        tdSql.checkData(13, 1, '4')
        tdSql.checkData(14, 0, datetime.datetime(2021, 11, 11, 9, 0, 14))
        tdSql.checkData(14, 1, '5')
        tdSql.checkData(15, 0, datetime.datetime(2021, 11, 11, 9, 0, 15))
        tdSql.checkData(15, 1, '6')
        tdSql.checkData(16, 0, datetime.datetime(2021, 11, 11, 9, 0, 16))
        tdSql.checkData(16, 1, '7')
        tdSql.checkData(17, 0, datetime.datetime(2021, 11, 11, 9, 0, 17))
        tdSql.checkData(17, 1, '8')
        tdSql.checkData(18, 0, datetime.datetime(2021, 11, 11, 9, 0, 18))
        tdSql.checkData(18, 1, '9')
        tdSql.checkData(19, 0, datetime.datetime(2021, 11, 11, 9, 0, 19))
        tdSql.checkData(19, 1, '0')
        tdSql.checkData(20, 0, datetime.datetime(2021, 11, 11, 9, 0, 20))
        tdSql.checkData(20, 1, '1')
        tdSql.checkData(21, 0, datetime.datetime(2021, 11, 11, 9, 0, 21))
        tdSql.checkData(21, 1, '2')
        tdSql.checkData(22, 0, datetime.datetime(2021, 11, 11, 9, 0, 22))
        tdSql.checkData(22, 1, '3')
        tdSql.checkData(23, 0, datetime.datetime(2021, 11, 11, 9, 0, 23))
        tdSql.checkData(23, 1, '4')
        tdSql.checkData(24, 0, datetime.datetime(2021, 11, 11, 9, 0, 24))
        tdSql.checkData(24, 1, '5')
        tdSql.checkData(25, 0, datetime.datetime(2021, 11, 11, 9, 0, 25))
        tdSql.checkData(25, 1, '6')
        tdSql.checkData(26, 0, datetime.datetime(2021, 11, 11, 9, 0, 26))
        tdSql.checkData(26, 1, '7')
        tdSql.checkData(27, 0, datetime.datetime(2021, 11, 11, 9, 0, 27))
        tdSql.checkData(27, 1, '8')
        tdSql.checkData(28, 0, datetime.datetime(2021, 11, 11, 9, 0, 28))
        tdSql.checkData(28, 1, '9')
        tdSql.checkData(29, 0, datetime.datetime(2021, 11, 11, 9, 0, 29))
        tdSql.checkData(29, 1, '0')

        tdSql.query('select substr(cast(interp(c2) as binary(20)), 1) from stba every(1s) group by tbname;')
        tdSql.checkRows(30)
        tdSql.checkData(0, 0, datetime.datetime(2021, 11, 11, 9, 0))
        tdSql.checkData(0, 1, '1')
        tdSql.checkData(0, 2, 'tba1')
        tdSql.checkData(1, 0, datetime.datetime(2021, 11, 11, 9, 0, 1))
        tdSql.checkData(1, 1, '2')
        tdSql.checkData(1, 2, 'tba1')
        tdSql.checkData(2, 0, datetime.datetime(2021, 11, 11, 9, 0, 2))
        tdSql.checkData(2, 1, '3')
        tdSql.checkData(2, 2, 'tba1')
        tdSql.checkData(3, 0, datetime.datetime(2021, 11, 11, 9, 0, 3))
        tdSql.checkData(3, 1, '4')
        tdSql.checkData(3, 2, 'tba1')
        tdSql.checkData(4, 0, datetime.datetime(2021, 11, 11, 9, 0, 4))
        tdSql.checkData(4, 1, '5')
        tdSql.checkData(4, 2, 'tba1')
        tdSql.checkData(5, 0, datetime.datetime(2021, 11, 11, 9, 0, 5))
        tdSql.checkData(5, 1, '6')
        tdSql.checkData(5, 2, 'tba1')
        tdSql.checkData(6, 0, datetime.datetime(2021, 11, 11, 9, 0, 6))
        tdSql.checkData(6, 1, '7')
        tdSql.checkData(6, 2, 'tba1')
        tdSql.checkData(7, 0, datetime.datetime(2021, 11, 11, 9, 0, 7))
        tdSql.checkData(7, 1, '8')
        tdSql.checkData(7, 2, 'tba1')
        tdSql.checkData(8, 0, datetime.datetime(2021, 11, 11, 9, 0, 8))
        tdSql.checkData(8, 1, '9')
        tdSql.checkData(8, 2, 'tba1')
        tdSql.checkData(9, 0, datetime.datetime(2021, 11, 11, 9, 0, 9))
        tdSql.checkData(9, 1, '0')
        tdSql.checkData(9, 2, 'tba1')
        tdSql.checkData(10, 0, datetime.datetime(2021, 11, 11, 9, 0, 10))
        tdSql.checkData(10, 1, '1')
        tdSql.checkData(10, 2, 'tba1')
        tdSql.checkData(11, 0, datetime.datetime(2021, 11, 11, 9, 0, 11))
        tdSql.checkData(11, 1, '2')
        tdSql.checkData(11, 2, 'tba1')
        tdSql.checkData(12, 0, datetime.datetime(2021, 11, 11, 9, 0, 12))
        tdSql.checkData(12, 1, '3')
        tdSql.checkData(12, 2, 'tba1')
        tdSql.checkData(13, 0, datetime.datetime(2021, 11, 11, 9, 0, 13))
        tdSql.checkData(13, 1, '4')
        tdSql.checkData(13, 2, 'tba1')
        tdSql.checkData(14, 0, datetime.datetime(2021, 11, 11, 9, 0, 14))
        tdSql.checkData(14, 1, '5')
        tdSql.checkData(14, 2, 'tba1')
        tdSql.checkData(15, 0, datetime.datetime(2021, 11, 11, 9, 0, 15))
        tdSql.checkData(15, 1, '6')
        tdSql.checkData(15, 2, 'tba1')
        tdSql.checkData(16, 0, datetime.datetime(2021, 11, 11, 9, 0, 16))
        tdSql.checkData(16, 1, '7')
        tdSql.checkData(16, 2, 'tba1')
        tdSql.checkData(17, 0, datetime.datetime(2021, 11, 11, 9, 0, 17))
        tdSql.checkData(17, 1, '8')
        tdSql.checkData(17, 2, 'tba1')
        tdSql.checkData(18, 0, datetime.datetime(2021, 11, 11, 9, 0, 18))
        tdSql.checkData(18, 1, '9')
        tdSql.checkData(18, 2, 'tba1')
        tdSql.checkData(19, 0, datetime.datetime(2021, 11, 11, 9, 0, 19))
        tdSql.checkData(19, 1, '0')
        tdSql.checkData(19, 2, 'tba1')
        tdSql.checkData(20, 0, datetime.datetime(2021, 11, 11, 9, 0, 20))
        tdSql.checkData(20, 1, '1')
        tdSql.checkData(20, 2, 'tba1')
        tdSql.checkData(21, 0, datetime.datetime(2021, 11, 11, 9, 0, 21))
        tdSql.checkData(21, 1, '2')
        tdSql.checkData(21, 2, 'tba1')
        tdSql.checkData(22, 0, datetime.datetime(2021, 11, 11, 9, 0, 22))
        tdSql.checkData(22, 1, '3')
        tdSql.checkData(22, 2, 'tba1')
        tdSql.checkData(23, 0, datetime.datetime(2021, 11, 11, 9, 0, 23))
        tdSql.checkData(23, 1, '4')
        tdSql.checkData(23, 2, 'tba1')
        tdSql.checkData(24, 0, datetime.datetime(2021, 11, 11, 9, 0, 24))
        tdSql.checkData(24, 1, '5')
        tdSql.checkData(24, 2, 'tba1')
        tdSql.checkData(25, 0, datetime.datetime(2021, 11, 11, 9, 0, 25))
        tdSql.checkData(25, 1, '6')
        tdSql.checkData(25, 2, 'tba1')
        tdSql.checkData(26, 0, datetime.datetime(2021, 11, 11, 9, 0, 26))
        tdSql.checkData(26, 1, '7')
        tdSql.checkData(26, 2, 'tba1')
        tdSql.checkData(27, 0, datetime.datetime(2021, 11, 11, 9, 0, 27))
        tdSql.checkData(27, 1, '8')
        tdSql.checkData(27, 2, 'tba1')
        tdSql.checkData(28, 0, datetime.datetime(2021, 11, 11, 9, 0, 28))
        tdSql.checkData(28, 1, '9')
        tdSql.checkData(28, 2, 'tba1')
        tdSql.checkData(29, 0, datetime.datetime(2021, 11, 11, 9, 0, 29))
        tdSql.checkData(29, 1, '0')
        tdSql.checkData(29, 2, 'tba1')

        tdSql.query('select substr(cast(elapsed(ts) as binary(20)), 1, 2) from tba1;')
        tdSql.checkRows(1)
        tdSql.checkData(0, 0, '29')

        tdSql.query('select substr(cast(rate(c2) as binary(20)), 1, 2) from tba1;')
        tdSql.checkRows(1)
        tdSql.checkData(0, 0, '0.')

        tdSql.query('select substr(cast(irate(c2) as binary(20)), 1, 2) from tba1;')
        tdSql.checkRows(1)
        tdSql.checkData(0, 0, '0.')

        tdSql.query('select substr(cast(first(c2) as binary(20)), 1, 2) from tba1;')
        tdSql.checkRows(1)
        tdSql.checkData(0, 0, '1')

        tdSql.query('select substr(cast(last(c2) as binary(20)), 1, 2) from tba1;')
        tdSql.checkRows(1)
        tdSql.checkData(0, 0, '0')

        tdSql.query('select substr(cast(last_row(c2) as binary(20)), 1, 2) from tba1;')
        tdSql.checkRows(1)
        tdSql.checkData(0, 0, '0')

        tdSql.error('select substr(cast(top(c2, 1) as binary(20)), 1, 2) from tba1;')

        tdSql.error('select substr(cast(bottom(c2, 1) as binary(20)), 1, 2) from tba1;')

        tdSql.error('select substr(cast(leastsquares(c2, 1, 1) as binary(20)), 1, 2) from tba1;')

        tdSql.error('select substr(cast(derivative(c2, 1s, 0) as binary(20)), 1, 2) from tba1;')

        tdSql.error('select substr(cast(diff(c2) as binary(20)), 1, 2) from tba1;')

        tdSql.error('select substr(cast(csum(c2) as binary(20)), 1, 2) from tba1;')

        tdSql.error('select substr(cast(mavg(c2,2) as binary(20)), 1, 2) from tba1;')

        tdSql.error('select substr(cast(sample(c2,2) as binary(20)), 1, 2) from tba1;')

        tdSql.error('select substr(cast(_block_dist() as binary(20)), 1, 2) from tba1;')

        tdSql.query('select substr(stb1.c14, -3),substr(stba.c15, 1) from stb1,stba where stb1.t1=stba.t1 and stb1.ts=stba.ts;')
        tdSql.checkRows(7)
        tdSql.checkData(0, 0, 'a  ')
        tdSql.checkData(0, 1, '  tDenGine  ')
        tdSql.checkData(1, 0, None)
        tdSql.checkData(1, 1, 'Jeff Tao')
        tdSql.checkData(2, 0, 'Bo ')
        tdSql.checkData(2, 1, 'SteVeN LI')
        tdSql.checkData(3, 0, None)
        tdSql.checkData(3, 1, ' Wade ZhAng  ')
        tdSql.checkData(4, 0, 'ang')
        tdSql.checkData(4, 1, 'shengliang guaN  ')
        tdSql.checkData(5, 0, '27')
        tdSql.checkData(5, 1, 'haojun Liao  ')
        tdSql.checkData(6, 0, '')
        tdSql.checkData(6, 1, '  hongzecheng')

        tdSql.error('select substr(c14, -3) as a from stb1 union all select substr(c15, -3) as a from stba;')

        tdSql.query('select substr(c8, 1) from stba;')
        tdSql.checkRows(30)
        tdSql.checkData(0, 0, 'aBc')
        tdSql.checkData(1, 0, 'Bcd')
        tdSql.checkData(2, 0, 'cdE')
        tdSql.checkData(3, 0, 'deF')
        tdSql.checkData(4, 0, 'efG')
        tdSql.checkData(5, 0, 'f Gh')
        tdSql.checkData(6, 0, 'ghi  ')
        tdSql.checkData(7, 0, 'ghi  ')
        tdSql.checkData(8, 0, ' taos Data  ')
        tdSql.checkData(9, 0, ' taOs Data  ')
        tdSql.checkData(10, 0, 'aBc')
        tdSql.checkData(11, 0, 'Bcd')
        tdSql.checkData(12, 0, 'cdE')
        tdSql.checkData(13, 0, 'deF')
        tdSql.checkData(14, 0, 'efG')
        tdSql.checkData(15, 0, 'f Gh')
        tdSql.checkData(16, 0, 'ghi  ')
        tdSql.checkData(17, 0, 'ghi  ')
        tdSql.checkData(18, 0, ' taos Data  ')
        tdSql.checkData(19, 0, ' taOs Data  ')
        tdSql.checkData(20, 0, 'aBc')
        tdSql.checkData(21, 0, 'Bcd')
        tdSql.checkData(22, 0, 'cdE')
        tdSql.checkData(23, 0, 'deF')
        tdSql.checkData(24, 0, 'efG')
        tdSql.checkData(25, 0, 'f Gh')
        tdSql.checkData(26, 0, 'ghi  ')
        tdSql.checkData(27, 0, 'ghi  ')
        tdSql.checkData(28, 0, ' taos Data  ')
        tdSql.checkData(29, 0, ' taOs Data  ')

        tdSql.query('select substr(c9, 1) from stba;')
        tdSql.checkRows(30)
        tdSql.checkData(0, 0, 'AbCd')
        tdSql.checkData(1, 0, 'BcdE')
        tdSql.checkData(2, 0, 'cdEf')
        tdSql.checkData(3, 0, 'deFg')
        tdSql.checkData(4, 0, 'efGH')
        tdSql.checkData(5, 0, ' fgHk ')
        tdSql.checkData(6, 0, '  ghiJ ')
        tdSql.checkData(7, 0, '  涛思数据 ')
        tdSql.checkData(8, 0, '  涛思数据 ')
        tdSql.checkData(9, 0, '  涛思数据2022 ')
        tdSql.checkData(10, 0, 'AbCd')
        tdSql.checkData(11, 0, 'BcdE')
        tdSql.checkData(12, 0, 'cdEf')
        tdSql.checkData(13, 0, 'deFg')
        tdSql.checkData(14, 0, 'efGH')
        tdSql.checkData(15, 0, ' fgHk ')
        tdSql.checkData(16, 0, '  ghiJ ')
        tdSql.checkData(17, 0, '  涛思数据 ')
        tdSql.checkData(18, 0, '  涛思数据 ')
        tdSql.checkData(19, 0, '  涛思数据2022 ')
        tdSql.checkData(20, 0, 'AbCd')
        tdSql.checkData(21, 0, 'BcdE')
        tdSql.checkData(22, 0, 'cdEf')
        tdSql.checkData(23, 0, 'deFg')
        tdSql.checkData(24, 0, 'efGH')
        tdSql.checkData(25, 0, ' fgHk ')
        tdSql.checkData(26, 0, '  ghiJ ')
        tdSql.checkData(27, 0, '  涛思数据 ')
        tdSql.checkData(28, 0, '  涛思数据 ')
        tdSql.checkData(29, 0, '  涛思数据2022 ')

        tdSql.query('select substr(cast(min(c2) as binary(20)), 1) from tba1;')
        tdSql.checkRows(1)
        tdSql.checkData(0, 0, '0')

        tdSql.query('select substr(cast(max(c2) as binary(20)), 1) from tba1;')
        tdSql.checkRows(1)
        tdSql.checkData(0, 0, '9')

        tdSql.query('select substr(cast(count(c2) as binary(20)), 1) from tba1;')
        tdSql.checkRows(1)
        tdSql.checkData(0, 0, '30')

        tdSql.query('select substr(cast(sum(c2) as binary(20)), 1) from tba1;')
        tdSql.checkRows(1)
        tdSql.checkData(0, 0, '135')

        tdSql.query('select substr(cast(avg(c2) as binary(20)), 1) from tba1;')
        tdSql.checkRows(1)
        tdSql.checkData(0, 0, '4.500000')

        tdSql.query('select substr(cast(percentile(c2, 10) as binary(20)), 1) from tba1;')
        tdSql.checkRows(1)
        tdSql.checkData(0, 0, '0.900000')

        tdSql.query('select substr(cast(apercentile(c2, 10) as binary(20)), 1) from tba1;')
        tdSql.checkRows(1)
        tdSql.checkData(0, 0, '0.000000')

        tdSql.query('select substr(cast(stddev(c2) as binary(20)), 1) from tba1;')
        tdSql.checkRows(1)
        tdSql.checkData(0, 0, '2.872281')

        tdSql.query('select substr(cast(spread(c2) as binary(20)), 1) from tba1;')
        tdSql.checkRows(1)
        tdSql.checkData(0, 0, '9.000000')

        tdSql.query('select substr(cast(twa(c2) as binary(20)), 1) from tba1;')
        tdSql.checkRows(1)
        tdSql.checkData(0, 0, '4.637931')

        tdSql.error('select substr(cast(leastsquares(c2, 1, 1) as binary(20)), 1) from tba1;')

        tdSql.query('select substr(cast(interp(c2) as binary(20)), 1) from tba1 every(1s)')
        tdSql.checkRows(30)
        tdSql.checkData(0, 0, datetime.datetime(2021, 11, 11, 9, 0))
        tdSql.checkData(0, 1, '1')
        tdSql.checkData(1, 0, datetime.datetime(2021, 11, 11, 9, 0, 1))
        tdSql.checkData(1, 1, '2')
        tdSql.checkData(2, 0, datetime.datetime(2021, 11, 11, 9, 0, 2))
        tdSql.checkData(2, 1, '3')
        tdSql.checkData(3, 0, datetime.datetime(2021, 11, 11, 9, 0, 3))
        tdSql.checkData(3, 1, '4')
        tdSql.checkData(4, 0, datetime.datetime(2021, 11, 11, 9, 0, 4))
        tdSql.checkData(4, 1, '5')
        tdSql.checkData(5, 0, datetime.datetime(2021, 11, 11, 9, 0, 5))
        tdSql.checkData(5, 1, '6')
        tdSql.checkData(6, 0, datetime.datetime(2021, 11, 11, 9, 0, 6))
        tdSql.checkData(6, 1, '7')
        tdSql.checkData(7, 0, datetime.datetime(2021, 11, 11, 9, 0, 7))
        tdSql.checkData(7, 1, '8')
        tdSql.checkData(8, 0, datetime.datetime(2021, 11, 11, 9, 0, 8))
        tdSql.checkData(8, 1, '9')
        tdSql.checkData(9, 0, datetime.datetime(2021, 11, 11, 9, 0, 9))
        tdSql.checkData(9, 1, '0')
        tdSql.checkData(10, 0, datetime.datetime(2021, 11, 11, 9, 0, 10))
        tdSql.checkData(10, 1, '1')
        tdSql.checkData(11, 0, datetime.datetime(2021, 11, 11, 9, 0, 11))
        tdSql.checkData(11, 1, '2')
        tdSql.checkData(12, 0, datetime.datetime(2021, 11, 11, 9, 0, 12))
        tdSql.checkData(12, 1, '3')
        tdSql.checkData(13, 0, datetime.datetime(2021, 11, 11, 9, 0, 13))
        tdSql.checkData(13, 1, '4')
        tdSql.checkData(14, 0, datetime.datetime(2021, 11, 11, 9, 0, 14))
        tdSql.checkData(14, 1, '5')
        tdSql.checkData(15, 0, datetime.datetime(2021, 11, 11, 9, 0, 15))
        tdSql.checkData(15, 1, '6')
        tdSql.checkData(16, 0, datetime.datetime(2021, 11, 11, 9, 0, 16))
        tdSql.checkData(16, 1, '7')
        tdSql.checkData(17, 0, datetime.datetime(2021, 11, 11, 9, 0, 17))
        tdSql.checkData(17, 1, '8')
        tdSql.checkData(18, 0, datetime.datetime(2021, 11, 11, 9, 0, 18))
        tdSql.checkData(18, 1, '9')
        tdSql.checkData(19, 0, datetime.datetime(2021, 11, 11, 9, 0, 19))
        tdSql.checkData(19, 1, '0')
        tdSql.checkData(20, 0, datetime.datetime(2021, 11, 11, 9, 0, 20))
        tdSql.checkData(20, 1, '1')
        tdSql.checkData(21, 0, datetime.datetime(2021, 11, 11, 9, 0, 21))
        tdSql.checkData(21, 1, '2')
        tdSql.checkData(22, 0, datetime.datetime(2021, 11, 11, 9, 0, 22))
        tdSql.checkData(22, 1, '3')
        tdSql.checkData(23, 0, datetime.datetime(2021, 11, 11, 9, 0, 23))
        tdSql.checkData(23, 1, '4')
        tdSql.checkData(24, 0, datetime.datetime(2021, 11, 11, 9, 0, 24))
        tdSql.checkData(24, 1, '5')
        tdSql.checkData(25, 0, datetime.datetime(2021, 11, 11, 9, 0, 25))
        tdSql.checkData(25, 1, '6')
        tdSql.checkData(26, 0, datetime.datetime(2021, 11, 11, 9, 0, 26))
        tdSql.checkData(26, 1, '7')
        tdSql.checkData(27, 0, datetime.datetime(2021, 11, 11, 9, 0, 27))
        tdSql.checkData(27, 1, '8')
        tdSql.checkData(28, 0, datetime.datetime(2021, 11, 11, 9, 0, 28))
        tdSql.checkData(28, 1, '9')
        tdSql.checkData(29, 0, datetime.datetime(2021, 11, 11, 9, 0, 29))
        tdSql.checkData(29, 1, '0')

        tdSql.query('select substr(cast(interp(c2) as binary(20)), 1) from  stba every(1s) group by tbname;')
        tdSql.checkRows(30)
        tdSql.checkData(0, 0, datetime.datetime(2021, 11, 11, 9, 0))
        tdSql.checkData(0, 1, '1')
        tdSql.checkData(0, 2, 'tba1')
        tdSql.checkData(1, 0, datetime.datetime(2021, 11, 11, 9, 0, 1))
        tdSql.checkData(1, 1, '2')
        tdSql.checkData(1, 2, 'tba1')
        tdSql.checkData(2, 0, datetime.datetime(2021, 11, 11, 9, 0, 2))
        tdSql.checkData(2, 1, '3')
        tdSql.checkData(2, 2, 'tba1')
        tdSql.checkData(3, 0, datetime.datetime(2021, 11, 11, 9, 0, 3))
        tdSql.checkData(3, 1, '4')
        tdSql.checkData(3, 2, 'tba1')
        tdSql.checkData(4, 0, datetime.datetime(2021, 11, 11, 9, 0, 4))
        tdSql.checkData(4, 1, '5')
        tdSql.checkData(4, 2, 'tba1')
        tdSql.checkData(5, 0, datetime.datetime(2021, 11, 11, 9, 0, 5))
        tdSql.checkData(5, 1, '6')
        tdSql.checkData(5, 2, 'tba1')
        tdSql.checkData(6, 0, datetime.datetime(2021, 11, 11, 9, 0, 6))
        tdSql.checkData(6, 1, '7')
        tdSql.checkData(6, 2, 'tba1')
        tdSql.checkData(7, 0, datetime.datetime(2021, 11, 11, 9, 0, 7))
        tdSql.checkData(7, 1, '8')
        tdSql.checkData(7, 2, 'tba1')
        tdSql.checkData(8, 0, datetime.datetime(2021, 11, 11, 9, 0, 8))
        tdSql.checkData(8, 1, '9')
        tdSql.checkData(8, 2, 'tba1')
        tdSql.checkData(9, 0, datetime.datetime(2021, 11, 11, 9, 0, 9))
        tdSql.checkData(9, 1, '0')
        tdSql.checkData(9, 2, 'tba1')
        tdSql.checkData(10, 0, datetime.datetime(2021, 11, 11, 9, 0, 10))
        tdSql.checkData(10, 1, '1')
        tdSql.checkData(10, 2, 'tba1')
        tdSql.checkData(11, 0, datetime.datetime(2021, 11, 11, 9, 0, 11))
        tdSql.checkData(11, 1, '2')
        tdSql.checkData(11, 2, 'tba1')
        tdSql.checkData(12, 0, datetime.datetime(2021, 11, 11, 9, 0, 12))
        tdSql.checkData(12, 1, '3')
        tdSql.checkData(12, 2, 'tba1')
        tdSql.checkData(13, 0, datetime.datetime(2021, 11, 11, 9, 0, 13))
        tdSql.checkData(13, 1, '4')
        tdSql.checkData(13, 2, 'tba1')
        tdSql.checkData(14, 0, datetime.datetime(2021, 11, 11, 9, 0, 14))
        tdSql.checkData(14, 1, '5')
        tdSql.checkData(14, 2, 'tba1')
        tdSql.checkData(15, 0, datetime.datetime(2021, 11, 11, 9, 0, 15))
        tdSql.checkData(15, 1, '6')
        tdSql.checkData(15, 2, 'tba1')
        tdSql.checkData(16, 0, datetime.datetime(2021, 11, 11, 9, 0, 16))
        tdSql.checkData(16, 1, '7')
        tdSql.checkData(16, 2, 'tba1')
        tdSql.checkData(17, 0, datetime.datetime(2021, 11, 11, 9, 0, 17))
        tdSql.checkData(17, 1, '8')
        tdSql.checkData(17, 2, 'tba1')
        tdSql.checkData(18, 0, datetime.datetime(2021, 11, 11, 9, 0, 18))
        tdSql.checkData(18, 1, '9')
        tdSql.checkData(18, 2, 'tba1')
        tdSql.checkData(19, 0, datetime.datetime(2021, 11, 11, 9, 0, 19))
        tdSql.checkData(19, 1, '0')
        tdSql.checkData(19, 2, 'tba1')
        tdSql.checkData(20, 0, datetime.datetime(2021, 11, 11, 9, 0, 20))
        tdSql.checkData(20, 1, '1')
        tdSql.checkData(20, 2, 'tba1')
        tdSql.checkData(21, 0, datetime.datetime(2021, 11, 11, 9, 0, 21))
        tdSql.checkData(21, 1, '2')
        tdSql.checkData(21, 2, 'tba1')
        tdSql.checkData(22, 0, datetime.datetime(2021, 11, 11, 9, 0, 22))
        tdSql.checkData(22, 1, '3')
        tdSql.checkData(22, 2, 'tba1')
        tdSql.checkData(23, 0, datetime.datetime(2021, 11, 11, 9, 0, 23))
        tdSql.checkData(23, 1, '4')
        tdSql.checkData(23, 2, 'tba1')
        tdSql.checkData(24, 0, datetime.datetime(2021, 11, 11, 9, 0, 24))
        tdSql.checkData(24, 1, '5')
        tdSql.checkData(24, 2, 'tba1')
        tdSql.checkData(25, 0, datetime.datetime(2021, 11, 11, 9, 0, 25))
        tdSql.checkData(25, 1, '6')
        tdSql.checkData(25, 2, 'tba1')
        tdSql.checkData(26, 0, datetime.datetime(2021, 11, 11, 9, 0, 26))
        tdSql.checkData(26, 1, '7')
        tdSql.checkData(26, 2, 'tba1')
        tdSql.checkData(27, 0, datetime.datetime(2021, 11, 11, 9, 0, 27))
        tdSql.checkData(27, 1, '8')
        tdSql.checkData(27, 2, 'tba1')
        tdSql.checkData(28, 0, datetime.datetime(2021, 11, 11, 9, 0, 28))
        tdSql.checkData(28, 1, '9')
        tdSql.checkData(28, 2, 'tba1')
        tdSql.checkData(29, 0, datetime.datetime(2021, 11, 11, 9, 0, 29))
        tdSql.checkData(29, 1, '0')
        tdSql.checkData(29, 2, 'tba1')

        tdSql.query('select substr(cast(elapsed(ts) as binary(20)), 1) from tba1;')
        tdSql.checkRows(1)
        tdSql.checkData(0, 0, '29000.000000')

        tdSql.query('select substr(cast(rate(c2) as binary(20)), 1) from tba1;')
        tdSql.checkRows(1)
        tdSql.checkData(0, 0, '0.896552')

        tdSql.query('select substr(cast(irate(c2) as binary(20)), 1) from tba1;')
        tdSql.checkRows(1)
        tdSql.checkData(0, 0, '0.000000')

        tdSql.query('select substr(cast(first(c2) as binary(20)), 1) from tba1;')
        tdSql.checkRows(1)
        tdSql.checkData(0, 0, '1')

        tdSql.query('select substr(cast(last(c2) as binary(20)), 1) from tba1;')
        tdSql.checkRows(1)
        tdSql.checkData(0, 0, '0')

        tdSql.query('select substr(cast(last_row(c2) as binary(20)), 1) from tba1;')
        tdSql.checkRows(1)
        tdSql.checkData(0, 0, '0')

        tdSql.error('select substr(cast(top(c2, 1) as binary(20)), 1) from tba1;')

        tdSql.error('select substr(cast(bottom(c2, 1) as binary(20)), 1) from tba1;')

        tdSql.error('select substr(cast(leastsquares(c2, 1, 1) as binary(20)), 1) from tba1;')

        tdSql.error('select substr(cast(derivative(c2, 1s, 0) as binary(20)), 1) from tba1;')

        tdSql.error('select substr(cast(diff(c2) as binary(20)), 1) from tba1;')

        tdSql.error('select substr(cast(csum(c2) as binary(20)), 1) from tba1;')

        tdSql.error('select substr(cast(mavg(c2,2) as binary(20)), 1) from tba1;')

        tdSql.error('select substr(cast(sample(c2,2) as binary(20)), 1) from tba1;')

        tdSql.error('select substr(cast(_block_dist() as binary(20)), 1) from tba1;')


        tdSql.execute('drop database substr_func')
    def stop(self):
        tdSql.close()
        tdLog.success("%s successfully executed" % __file__)

tdCases.addWindows(__file__, TDTestCase())
tdCases.addLinux(__file__, TDTestCase())
