cast.py

import taos
import sys
import datetime
import inspect

from util.log import *
from util.sql import *
from util.cases import *



class TDTestCase:

    def init(self, conn, logSql):
        tdLog.debug(f"start to excute {__file__}")
        tdSql.init(conn.cursor())

    def run(self):  # sourcery skip: extract-duplicate-method, remove-redundant-fstring
        tdSql.prepare()

        tdLog.printNoPrefix("==========step1:create table")
        tdSql.execute(
            '''create table stb1
            (ts timestamp, c1 int, c2 bigint, c3 smallint, c4 tinyint, c5 float, c6 double, c7 bool, c8 binary(16),c9 nchar(32), c10 timestamp)
            tags (t1 int)
            '''
        )
        tdSql.execute(
            '''
            create table t1
            (ts timestamp, c1 int, c2 bigint, c3 smallint, c4 tinyint, c5 float, c6 double, c7 bool, c8 binary(16),c9 nchar(32), c10 timestamp)
            '''
        )
        for i in range(4):
            tdSql.execute(f'create table ct{i+1} using stb1 tags ( {i+1} )')

        tdLog.printNoPrefix("==========step2:insert data")
        for i in range(9):
            tdSql.execute(
                f"insert into ct1 values ( now()-{i*10}s, {1*i}, {11111*i}, {111*i}, {11*i}, {1.11*i}, {11.11*i}, {i%2}, 'binary{i}', 'nchar{i}', now()+{1*i}a )"
            )
            tdSql.execute(
                f"insert into ct4 values ( now()-{i*90}d, {1*i}, {11111*i}, {111*i}, {11*i}, {1.11*i}, {11.11*i}, {i%2}, 'binary{i}', 'nchar{i}', now()+{1*i}a )"
            )
        tdSql.execute("insert into ct1 values (now()-45s, 0, 0, 0, 0, 0, 0, 0, 'binary0', 'nchar0', now()+8a )")
        tdSql.execute("insert into ct1 values (now()+10s, 9, -99999, -999, -99, -9.99, -99.99, 1, 'binary9', 'nchar9', now()+9a )")

        tdSql.execute("insert into ct4 values (now()-810d, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ) ")
        tdSql.execute("insert into ct4 values (now()-400d, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL ) ")
        tdSql.execute("insert into ct4 values (now()+90d, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL  ) ")

        tdSql.execute(
            f'''insert into t1 values
            ( '2020-04-21 01:01:01.000', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL )
            ( '2020-10-21 01:01:01.000', 1, 11111, 111, 11, 1.11, 11.11, 1, "binary1", "nchar1", now()+1a )
            ( '2020-12-31 01:01:01.000', 2, 22222, 222, 22, 2.22, 22.22, 0, "binary2", "nchar2", now()+2a )
            ( '2021-01-01 01:01:06.000', 3, 33333, 333, 33, 3.33, 33.33, 0, "binary3", "nchar3", now()+3a )
            ( '2021-05-07 01:01:10.000', 4, 44444, 444, 44, 4.44, 44.44, 1, "binary4", "nchar4", now()+4a )
            ( '2021-07-21 01:01:01.000', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL )
            ( '2021-09-30 01:01:16.000', 5, 55555, 555, 55, 5.55, 55.55, 0, "binary5", "nchar5", now()+5a )
            ( '2022-02-01 01:01:20.000', 6, 66666, 666, 66, 6.66, 66.66, 1, "binary6", "nchar6", now()+6a )
            ( '2022-10-28 01:01:26.000', 7, 00000, 000, 00, 0.00, 00.00, 1, "binary7", "nchar7", "1970-01-01 08:00:00.000" )
            ( '2022-12-01 01:01:30.000', 8, -88888, -888, -88, -8.88, -88.88, 0, "binary8", "nchar8", "1969-01-01 01:00:00.000" )
            ( '2022-12-31 01:01:36.000', 9, -99999999999999999, -999, -99, -9.99, -999999999999999999999.99, 1, "binary9", "nchar9", "1900-01-01 00:00:00.000" )
            ( '2023-02-21 01:01:01.000', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL )
            '''
        )

        tdSql.query("select c1  from ct4")
        data_ct4_c1 = [tdSql.getData(i,0) for i in range(tdSql.queryRows)]
        tdSql.query("select c1  from t1")
        data_t1_c1 = [tdSql.getData(i,0) for i in range(tdSql.queryRows)]

        tdLog.printNoPrefix("==========step2: cast int to bigint, expect no changes")

        tdSql.query("select cast(c1 as bigint) as b from ct4")
        for i in range(len(data_ct4_c1)):
            tdSql.checkData( i, 0, data_ct4_c1[i])
        tdSql.query("select cast(c1 as bigint) as b from t1")
        for i in range(len(data_t1_c1)):
            tdSql.checkData( i, 0, data_t1_c1[i])

        tdLog.printNoPrefix("==========step5: cast int to binary, expect changes to str(int) ")

        tdSql.query("select cast(c1 as binary(32)) as b from ct4")
        for i in range(len(data_ct4_c1)):
            tdSql.checkData( i, 0, str(data_ct4_c1[i]) )
        tdSql.query("select cast(c1 as binary(32)) as b from t1")
        for i in range(len(data_t1_c1)):
            tdSql.checkData( i, 0, str(data_t1_c1[i]) )

        tdLog.printNoPrefix("==========step6: cast int to nchar, expect changes to str(int) ")

        tdSql.query("select cast(c1 as nchar(32)) as b from ct4")
        for i in range(len(data_ct4_c1)):
            tdSql.checkData( i, 0, str(data_ct4_c1[i]) )
        tdSql.query("select cast(c1 as nchar(32)) as b from t1")
        for i in range(len(data_t1_c1)):
            tdSql.checkData( i, 0, str(data_t1_c1[i]) )

        tdLog.printNoPrefix("==========step7: cast int to timestamp, expect changes to timestamp ")

        tdSql.query("select cast(c1 as timestamp) as b from ct4")
        for i in range(len(data_ct4_c1)):
            if data_ct4_c1[i] is None:
                tdSql.checkData( i, 0 , None )
            else:
                utc_zone = datetime.timezone.utc
                utc_8 = datetime.timezone(datetime.timedelta(hours=8))
                date_init_stamp = datetime.datetime.utcfromtimestamp(data_ct4_c1[i]/1000)
                date_data = date_init_stamp.replace(tzinfo=utc_zone).astimezone(utc_8).strftime("%Y-%m-%d %H:%M:%S.%f")
                tdSql.checkData( i, 0, date_data)

        tdSql.query("select cast(c1 as timestamp) as b from t1")
        for i in range(len(data_t1_c1)):
            if data_ct4_c1[i] is None:
                tdSql.checkData( i, 0 , None )
            else:
                utc_zone = datetime.timezone.utc
                utc_8 = datetime.timezone(datetime.timedelta(hours=8))
                date_init_stamp = datetime.datetime.utcfromtimestamp(data_ct4_c1[i]/1000)
                date_data = date_init_stamp.replace(tzinfo=utc_zone).astimezone(utc_8).strftime("%Y-%m-%d %H:%M:%S.%f")
                tdSql.checkData( i, 0, date_data)


        tdLog.printNoPrefix("==========step8: cast bigint to bigint, expect no changes")
        tdSql.query("select c2  from ct4")
        data_ct4_c2 = [tdSql.getData(i,0) for i in range(tdSql.queryRows)]
        tdSql.query("select c2  from t1")
        data_t1_c2 = [tdSql.getData(i,0) for i in range(tdSql.queryRows)]

        tdSql.query("select cast(c2 as bigint) as b from ct4")
        for i in range(len(data_ct4_c2)):
            tdSql.checkData( i, 0, data_ct4_c2[i])
        tdSql.query("select cast(c2 as bigint) as b from t1")
        for i in range(len(data_t1_c2)):
            tdSql.checkData( i, 0, data_t1_c2[i])


        tdLog.printNoPrefix("==========step9: cast bigint to binary, expect changes to str(int) ")

        tdSql.query("select cast(c2 as binary(32)) as b from ct4")
        for i in range(len(data_ct4_c2)):
            tdSql.checkData( i, 0, str(data_ct4_c2[i]) )
        tdSql.query("select cast(c2 as binary(32)) as b from t1")
        for i in range(len(data_t1_c2)):
            tdSql.checkData( i, 0, str(data_t1_c2[i]) )

        tdLog.printNoPrefix("==========step10: cast bigint to nchar, expect changes to str(int) ")

        tdSql.query("select cast(c2 as nchar(32)) as b from ct4")
        for i in range(len(data_ct4_c2)):
            tdSql.checkData( i, 0, str(data_ct4_c2[i]) )
        tdSql.query("select cast(c2 as nchar(32)) as b from t1")
        for i in range(len(data_t1_c2)):
            tdSql.checkData( i, 0, str(data_t1_c2[i]) )

        tdLog.printNoPrefix("==========step11: cast bigint to timestamp, expect changes to timestamp ")

        tdSql.query("select cast(c2 as timestamp) as b from ct4")
        for i in range(len(data_ct4_c2)):
            if data_ct4_c2[i] is None:
                tdSql.checkData( i, 0 , None )
            else:
                utc_zone = datetime.timezone.utc
                utc_8 = datetime.timezone(datetime.timedelta(hours=8))
                date_init_stamp = datetime.datetime.utcfromtimestamp(data_ct4_c2[i]/1000)
                date_data = date_init_stamp.replace(tzinfo=utc_zone).astimezone(utc_8).strftime("%Y-%m-%d %H:%M:%S.%f")
                tdSql.checkData( i, 0, date_data)

        # tdSql.query("select cast(c2 as timestamp) as b from t1")
        # for i in range(len(data_t1_c2)):
        #     if data_t1_c2[i] is None:
        #         tdSql.checkData( i, 0 , None )
        #     else:
        #         utc_zone = datetime.timezone.utc
        #         utc_8 = datetime.timezone(datetime.timedelta(hours=8))
        #         date_init_stamp = datetime.datetime.utcfromtimestamp(data_t1_c2[i]/1000)
        #         date_data = date_init_stamp.replace(tzinfo=utc_zone).astimezone(utc_8).strftime("%Y-%m-%d %H:%M:%S.%f")
        #         tdSql.checkData( i, 0, date_data)


        tdLog.printNoPrefix("==========step12: cast smallint to bigint, expect no changes")
        tdSql.query("select c3  from ct4")
        data_ct4_c3 = [tdSql.getData(i,0) for i in range(tdSql.queryRows)]
        tdSql.query("select c3  from t1")
        data_t1_c3 = [tdSql.getData(i,0) for i in range(tdSql.queryRows)]

        tdSql.query("select cast(c3 as bigint) as b from ct4")
        for i in range(len(data_ct4_c3)):
            tdSql.checkData( i, 0, data_ct4_c3[i])
        tdSql.query("select cast(c3 as bigint) as b from t1")
        for i in range(len(data_t1_c3)):
            tdSql.checkData( i, 0, data_t1_c3[i])


        tdLog.printNoPrefix("==========step13: cast smallint to binary, expect changes to str(int) ")

        tdSql.query("select cast(c3 as binary(32)) as b from ct4")
        for i in range(len(data_ct4_c3)):
            tdSql.checkData( i, 0, str(data_ct4_c3[i]) )
        tdSql.query("select cast(c3 as binary(32)) as b from t1")
        for i in range(len(data_t1_c3)):
            tdSql.checkData( i, 0, str(data_t1_c3[i]) )

        tdLog.printNoPrefix("==========step14: cast smallint to nchar, expect changes to str(int) ")

        tdSql.query("select cast(c3 as nchar(32)) as b from ct4")
        for i in range(len(data_ct4_c3)):
            tdSql.checkData( i, 0, str(data_ct4_c3[i]) )
        tdSql.query("select cast(c3 as nchar(32)) as b from t1")
        for i in range(len(data_t1_c3)):
            tdSql.checkData( i, 0, str(data_t1_c3[i]) )

        tdLog.printNoPrefix("==========step15: cast smallint to timestamp, expect changes to timestamp ")

        tdSql.query("select cast(c3 as timestamp) as b from ct4")
        for i in range(len(data_ct4_c3)):
            if data_ct4_c3[i] is None:
                tdSql.checkData( i, 0 , None )
            else:
                utc_zone = datetime.timezone.utc
                utc_8 = datetime.timezone(datetime.timedelta(hours=8))
                date_init_stamp = datetime.datetime.utcfromtimestamp(data_ct4_c3[i]/1000)
                date_data = date_init_stamp.replace(tzinfo=utc_zone).astimezone(utc_8).strftime("%Y-%m-%d %H:%M:%S.%f")
                tdSql.checkData( i, 0, date_data)

        tdSql.query("select cast(c3 as timestamp) as b from t1")
        for i in range(len(data_t1_c3)):
            if data_ct4_c3[i] is None:
                tdSql.checkData( i, 0 , None )
            else:
                utc_zone = datetime.timezone.utc
                utc_8 = datetime.timezone(datetime.timedelta(hours=8))
                date_init_stamp = datetime.datetime.utcfromtimestamp(data_ct4_c3[i]/1000)
                date_data = date_init_stamp.replace(tzinfo=utc_zone).astimezone(utc_8).strftime("%Y-%m-%d %H:%M:%S.%f")
                tdSql.checkData( i, 0, date_data)


        tdLog.printNoPrefix("==========step16: cast smallint to bigint, expect no changes")
        tdSql.query("select c4  from ct4")
        data_ct4_c4 = [tdSql.getData(i,0) for i in range(tdSql.queryRows)]
        tdSql.query("select c4  from t1")
        data_t1_c4 = [tdSql.getData(i,0) for i in range(tdSql.queryRows)]

        tdSql.query("select cast(c4 as bigint) as b from ct4")
        for i in range(len(data_ct4_c4)):
            tdSql.checkData( i, 0, data_ct4_c4[i])
        tdSql.query("select cast(c4 as bigint) as b from t1")
        for i in range(len(data_t1_c4)):
            tdSql.checkData( i, 0, data_t1_c4[i])


        tdLog.printNoPrefix("==========step17: cast smallint to binary, expect changes to str(int) ")

        tdSql.query("select cast(c4 as binary(32)) as b from ct4")
        for i in range(len(data_ct4_c4)):
            tdSql.checkData( i, 0, str(data_ct4_c4[i]) )
        tdSql.query("select cast(c4 as binary(32)) as b from t1")
        for i in range(len(data_t1_c4)):
            tdSql.checkData( i, 0, str(data_t1_c4[i]) )

        tdLog.printNoPrefix("==========step18: cast smallint to nchar, expect changes to str(int) ")

        tdSql.query("select cast(c4 as nchar(32)) as b from ct4")
        for i in range(len(data_ct4_c4)):
            tdSql.checkData( i, 0, str(data_ct4_c4[i]) )
        tdSql.query("select cast(c4 as nchar(32)) as b from t1")
        for i in range(len(data_t1_c4)):
            tdSql.checkData( i, 0, str(data_t1_c4[i]) )

        tdLog.printNoPrefix("==========step19: cast smallint to timestamp, expect changes to timestamp ")

        tdSql.query("select cast(c4 as timestamp) as b from ct4")
        for i in range(len(data_ct4_c4)):
            if data_ct4_c4[i] is None:
                tdSql.checkData( i, 0 , None )
            else:
                utc_zone = datetime.timezone.utc
                utc_8 = datetime.timezone(datetime.timedelta(hours=8))
                date_init_stamp = datetime.datetime.utcfromtimestamp(data_ct4_c4[i]/1000)
                date_data = date_init_stamp.replace(tzinfo=utc_zone).astimezone(utc_8).strftime("%Y-%m-%d %H:%M:%S.%f")
                tdSql.checkData( i, 0, date_data)

        tdSql.query("select cast(c4 as timestamp) as b from t1")
        for i in range(len(data_t1_c4)):
            if data_ct4_c4[i] is None:
                tdSql.checkData( i, 0 , None )
            else:
                utc_zone = datetime.timezone.utc
                utc_8 = datetime.timezone(datetime.timedelta(hours=8))
                date_init_stamp = datetime.datetime.utcfromtimestamp(data_ct4_c4[i]/1000)
                date_data = date_init_stamp.replace(tzinfo=utc_zone).astimezone(utc_8).strftime("%Y-%m-%d %H:%M:%S.%f")
                tdSql.checkData( i, 0, date_data)


        tdLog.printNoPrefix("==========step20: cast float to bigint, expect no changes")
        tdSql.query("select c5  from ct4")
        data_ct4_c5 = [tdSql.getData(i,0) for i in range(tdSql.queryRows)]
        tdSql.query("select c5  from t1")
        data_t1_c5 = [tdSql.getData(i,0) for i in range(tdSql.queryRows)]

        tdSql.query("select cast(c5 as bigint) as b from ct4")
        for i in range(len(data_ct4_c5)):
            tdSql.checkData( i, 0, None ) if data_ct4_c5[i] is None else tdSql.checkData( i, 0, int(data_ct4_c5[i]) )
        tdSql.query("select cast(c5 as bigint) as b from t1")
        for i in range(len(data_t1_c5)):
            tdSql.checkData( i, 0, None ) if data_t1_c5[i] is None else tdSql.checkData( i, 0, int(data_t1_c5[i]) )

        tdLog.printNoPrefix("==========step21: cast float to binary, expect changes to str(int) ")
        tdSql.query("select cast(c5 as binary(32)) as b from ct4")
        for i in range(len(data_ct4_c5)):
            # tdSql.checkData( i, 0, str(data_ct4_c5[i]) )  if data_ct4_c5[i] is None else  tdSql.checkData( i, 0, str(round(data_ct4_c5[i], 6)) )
            tdSql.checkData( i, 0, str(data_ct4_c5[i]) )  if data_ct4_c5[i] is None else  tdSql.checkData( i, 0, f'{data_ct4_c5[i]:.6f}' )
        tdSql.query("select cast(c5 as binary(32)) as b from t1")
        for i in range(len(data_t1_c5)):
            # tdSql.checkData( i, 0, str(data_t1_c5[i]) )  if data_t1_c5[i] is None else tdSql.checkData( i, 0, str(round(data_t1_c5[i], 6)) )
            tdSql.checkData( i, 0, str(data_t1_c5[i]) )  if data_t1_c5[i] is None else tdSql.checkData( i, 0, f'{data_t1_c5[i]:.6f}' )
            # tdSql.checkData( i, 0, str(data_t1_c5[i]) )

        tdLog.printNoPrefix("==========step22: cast float to nchar, expect changes to str(int) ")
        tdSql.query("select cast(c5 as nchar(32)) as b from ct4")
        for i in range(len(data_ct4_c5)):
            tdSql.checkData( i, 0, None )  if data_ct4_c5[i] is None else  tdSql.checkData( i, 0, f'{data_ct4_c5[i]:.6f}' )
        tdSql.query("select cast(c5 as nchar(32)) as b from t1")
        for i in range(len(data_t1_c5)):
            tdSql.checkData( i, 0, None )  if data_t1_c5[i] is None else tdSql.checkData( i, 0, f'{data_t1_c5[i]:.6f}' )

        tdLog.printNoPrefix("==========step23: cast float to timestamp, expect changes to timestamp ")
        tdSql.query("select cast(c5 as timestamp) as b from ct4")
        for i in range(len(data_ct4_c5)):
            if data_ct4_c5[i] is None:
                tdSql.checkData( i, 0 , None )
            else:
                utc_zone = datetime.timezone.utc
                utc_8 = datetime.timezone(datetime.timedelta(hours=8))
                date_init_stamp = datetime.datetime.utcfromtimestamp(int(data_ct4_c5[i]/1000))
                date_data = date_init_stamp.replace(tzinfo=utc_zone).astimezone(utc_8).strftime("%Y-%m-%d %H:%M:%S.%f")
                tdSql.checkData( i, 0, date_data)
        tdSql.query("select cast(c5 as timestamp) as b from t1")
        for i in range(len(data_t1_c5)):
            if data_t1_c5[i] is None:
                tdSql.checkData( i, 0 , None )
            else:
                utc_zone = datetime.timezone.utc
                utc_8 = datetime.timezone(datetime.timedelta(hours=8))
                date_init_stamp = datetime.datetime.utcfromtimestamp(int(data_t1_c5[i]/1000))
                date_data = date_init_stamp.replace(tzinfo=utc_zone).astimezone(utc_8).strftime("%Y-%m-%d %H:%M:%S.%f")
                tdSql.checkData( i, 0, date_data)

        tdLog.printNoPrefix("==========step24: cast double to bigint, expect no changes")
        tdSql.query("select c6  from ct4")
        data_ct4_c6 = [tdSql.getData(i,0) for i in range(tdSql.queryRows)]
        tdSql.query("select c6  from t1")
        data_t1_c6 = [tdSql.getData(i,0) for i in range(tdSql.queryRows)]

        tdSql.query("select cast(c6 as bigint) as b from ct4")
        for i in range(len(data_ct4_c6)):
            tdSql.checkData( i, 0, None ) if data_ct4_c6[i] is None else tdSql.checkData( i, 0, int(data_ct4_c6[i]) )
        tdSql.query("select cast(c6 as bigint) as b from t1")
        for i in range(len(data_t1_c6)):
            if data_t1_c6[i] is None:
                tdSql.checkData( i, 0, None )
            elif data_t1_c6[i] > 99999999 or data_t1_c6[i] < -999999:
                continue
            else:
                tdSql.checkData( i, 0, int(data_t1_c6[i]) )

        tdLog.printNoPrefix("==========step25: cast double to binary, expect changes to str(int) ")
        tdSql.query("select cast(c6 as binary(32)) as b from ct4")
        for i in range(len(data_ct4_c6)):
            tdSql.checkData( i, 0, None )  if data_ct4_c6[i] is None else  tdSql.checkData( i, 0, f'{data_ct4_c6[i]:.6f}' )
        tdSql.query("select cast(c6 as binary(32)) as b from t1")
        for i in range(len(data_t1_c6)):
            tdSql.checkData( i, 0, None )  if data_t1_c6[i] is None else  tdSql.checkData( i, 0, f'{data_t1_c6[i]:.6f}' )

        tdLog.printNoPrefix("==========step26: cast double to nchar, expect changes to str(int) ")
        tdSql.query("select cast(c6 as nchar(32)) as b from ct4")
        for i in range(len(data_ct4_c6)):
            tdSql.checkData( i, 0, None )  if data_ct4_c6[i] is None else  tdSql.checkData( i, 0, f'{data_ct4_c6[i]:.6f}' )
        tdSql.query("select cast(c6 as nchar(32)) as b from t1")
        for i in range(len(data_t1_c6)):
            tdSql.checkData( i, 0, None )  if data_t1_c6[i] is None else  tdSql.checkData( i, 0, f'{data_t1_c6[i]:.6f}' )

        tdLog.printNoPrefix("==========step27: cast double to timestamp, expect changes to timestamp ")
        tdSql.query("select cast(c6 as timestamp) as b from ct4")
        for i in range(len(data_ct4_c6)):
            if data_ct4_c6[i] is None:
                tdSql.checkData( i, 0 , None )
            else:
                utc_zone = datetime.timezone.utc
                utc_8 = datetime.timezone(datetime.timedelta(hours=8))
                date_init_stamp = datetime.datetime.utcfromtimestamp(int(data_ct4_c6[i]/1000))
                date_data = date_init_stamp.replace(tzinfo=utc_zone).astimezone(utc_8).strftime("%Y-%m-%d %H:%M:%S.%f")
                tdSql.checkData( i, 0, date_data)

        # tdSql.query("select cast(c6 as timestamp) as b from t1")
        # for i in range(len(data_t1_c6)):
        #     if data_t1_c6[i] is None:
        #         tdSql.checkData( i, 0 , None )
        #     else:
        #         utc_zone = datetime.timezone.utc
        #         utc_8 = datetime.timezone(datetime.timedelta(hours=8))
        #         date_init_stamp = datetime.datetime.utcfromtimestamp(int(data_t1_c6[i]/1000))
        #         date_data = date_init_stamp.replace(tzinfo=utc_zone).astimezone(utc_8).strftime("%Y-%m-%d %H:%M:%S.%f")
        #         tdSql.checkData( i, 0, date_data)

        tdLog.printNoPrefix("==========step28: cast bool to bigint, expect no changes")
        tdSql.query("select c7  from ct4")
        data_ct4_c7 = [tdSql.getData(i,0) for i in range(tdSql.queryRows)]
        tdSql.query("select c7  from t1")
        data_t1_c7 = [tdSql.getData(i,0) for i in range(tdSql.queryRows)]

        tdSql.query("select cast(c7 as bigint) as b from ct4")
        for i in range(len(data_ct4_c7)):
            tdSql.checkData( i, 0, data_ct4_c7[i])
        tdSql.query("select cast(c7 as bigint) as b from t1")
        for i in range(len(data_t1_c7)):
            tdSql.checkData( i, 0, data_t1_c7[i])

        tdLog.printNoPrefix("==========step29: cast bool to binary, expect changes to str(int) ")
        tdSql.query("select cast(c7 as binary(32)) as b from ct4")
        for i in range(len(data_ct4_c7)):
            tdSql.checkData( i, 0, None ) if data_ct4_c7[i] is None else tdSql.checkData( i, 0, str(data_ct4_c7[i]).lower() )
        tdSql.query("select cast(c7 as binary(32)) as b from t1")
        for i in range(len(data_t1_c7)):
            tdSql.checkData( i, 0, None ) if data_t1_c7[i] is None else tdSql.checkData( i, 0, str(data_t1_c7[i]).lower() )

        tdLog.printNoPrefix("==========step30: cast bool to nchar, expect changes to str(int) ")
        tdSql.query("select cast(c7 as nchar(32)) as b from ct4")
        for i in range(len(data_ct4_c7)):
            tdSql.checkData( i, 0, None ) if data_ct4_c7[i] is None else tdSql.checkData( i, 0, str(data_ct4_c7[i]).lower() )
        tdSql.query("select cast(c7 as nchar(32)) as b from t1")
        for i in range(len(data_t1_c7)):
            tdSql.checkData( i, 0, None ) if data_t1_c7[i] is None else tdSql.checkData( i, 0, str(data_t1_c7[i]).lower() )

        tdLog.printNoPrefix("==========step31: cast bool to timestamp, expect changes to timestamp ")
        tdSql.query("select cast(c7 as timestamp) as b from ct4")
        for i in range(len(data_ct4_c7)):
            if data_ct4_c7[i] is None:
                tdSql.checkData( i, 0 , None )
            else:
                utc_zone = datetime.timezone.utc
                utc_8 = datetime.timezone(datetime.timedelta(hours=8))
                date_init_stamp = datetime.datetime.utcfromtimestamp(int(data_ct4_c7[i]/1000))
                date_data = date_init_stamp.replace(tzinfo=utc_zone).astimezone(utc_8).strftime("%Y-%m-%d %H:%M:%S.%f")
                tdSql.checkData( i, 0, date_data)
        tdSql.query("select cast(c7 as timestamp) as b from t1")
        for i in range(len(data_t1_c7)):
            if data_t1_c7[i] is None:
                tdSql.checkData( i, 0 , None )
            else:
                utc_zone = datetime.timezone.utc
                utc_8 = datetime.timezone(datetime.timedelta(hours=8))
                date_init_stamp = datetime.datetime.utcfromtimestamp(int(data_t1_c7[i]/1000))
                date_data = date_init_stamp.replace(tzinfo=utc_zone).astimezone(utc_8).strftime("%Y-%m-%d %H:%M:%S.%f")
                tdSql.checkData( i, 0, date_data)


        tdSql.query("select c8  from ct4")
        data_ct4_c8 = [tdSql.getData(i,0) for i in range(tdSql.queryRows)]
        tdSql.query("select c8  from t1")
        data_t1_c8 = [tdSql.getData(i,0) for i in range(tdSql.queryRows)]

        tdLog.printNoPrefix("==========step32: cast binary to binary, expect no changes ")
        tdSql.query("select cast(c8 as binary(32)) as b from ct4")
        for i in range(len(data_ct4_c8)):
            if data_ct4_c8[i] is None:
                tdSql.checkData( i, 0, None)
            elif tdSql.getData(i,0).strip("\07") == data_ct4_c8[i].strip():
                tdLog.info( f"sql:{tdSql.sql}, row:{i} col:0 data:{tdSql.queryResult[i][0]} == expect:{data_ct4_c8[i]}" )
            else:
                caller = inspect.getframeinfo(inspect.stack()[1][0])
                tdLog.exit(f"{caller.filename}({caller.lineno}) failed: sql:{tdSql.sql} row:{i} col:0 data:{tdSql.queryResult[i][0]} != expect:{data_ct4_c8[i]}")

        tdSql.query("select cast(c8 as binary(32)) as b from t1")
        for i in range(len(data_t1_c8)):
            if data_t1_c8[i] is None:
                tdSql.checkData( i, 0, None)
            elif tdSql.getData(i,0).strip("\07") == data_t1_c8[i].strip():
                tdLog.info( f"sql:{tdSql.sql}, row:{i} col:0 data:{tdSql.queryResult[i][0]} == expect:{data_t1_c8[i]}" )
            else:
                caller = inspect.getframeinfo(inspect.stack()[1][0])
                tdLog.exit(f"{caller.filename}({caller.lineno}) failed: sql:{tdSql.sql} row:{i} col:0 data:{tdSql.queryResult[i][0]} != expect:{data_t1_c8[i]}")

        tdLog.printNoPrefix("==========step33: cast binary to binary, expect truncate ")
        tdSql.query("select cast(c8 as binary(2)) as b from ct4")
        for i in range(len(data_ct4_c8)):
            if data_ct4_c8[i] is None:
                tdSql.checkData( i, 0, None)
            elif tdSql.getData(i,0).strip() == data_ct4_c8[i].strip()[:2]:
                tdLog.info( f"sql:{tdSql.sql}, row:{i} col:0 data:{tdSql.queryResult[i][0]} == expect:{data_ct4_c8[i].strip()[:2]}" )
            else:
                caller = inspect.getframeinfo(inspect.stack()[1][0])
                tdLog.exit(f"{caller.filename}({caller.lineno}) failed: sql:{tdSql.sql} row:{i} col:0 data:{tdSql.queryResult[i][0]} != expect:{data_ct4_c8[i].strip()[:2]}")
        tdSql.query("select cast(c8 as binary(2)) as b from t1")
        for i in range(len(data_t1_c8)):
            if data_t1_c8[i] is None:
                tdSql.checkData( i, 0, None)
            elif tdSql.getData(i,0).strip() == data_t1_c8[i].strip()[:2]:
                tdLog.info( f"sql:{tdSql.sql}, row:{i} col:0 data:{tdSql.queryResult[i][0]} == expect:{data_t1_c8[i].strip()[:2]}" )
            else:
                caller = inspect.getframeinfo(inspect.stack()[1][0])
                tdLog.exit(f"{caller.filename}({caller.lineno}) failed: sql:{tdSql.sql} row:{i} col:0 data:{tdSql.queryResult[i][0]} != expect:{data_t1_c8[i].strip()[:2]}")

        tdLog.printNoPrefix("==========step34: cast binary to nchar, expect changes to str(int) ")
        tdSql.query("select cast(c8 as nchar(32)) as b from ct4")
        for i in range(len(data_ct4_c8)):
            if data_ct4_c8[i] is None:
                tdSql.checkData( i, 0, None)
            elif tdSql.getData(i,0).strip() == data_ct4_c8[i].strip():
                tdLog.info( f"sql:{tdSql.sql}, row:{i} col:0 data:{tdSql.queryResult[i][0]} == expect:{data_ct4_c8[i]}" )
            else:
                caller = inspect.getframeinfo(inspect.stack()[1][0])
                tdLog.exit(f"{caller.filename}({caller.lineno}) failed: sql:{tdSql.sql} row:{i} col:0 data:{tdSql.queryResult[i][0]} != expect:{data_ct4_c8[i]}")
        tdSql.query("select cast(c8 as nchar(32)) as b from t1")
        for i in range(len(data_t1_c8)):
            if data_t1_c8[i] is None:
                tdSql.checkData( i, 0, None)
            elif tdSql.getData(i,0).strip() == data_t1_c8[i].strip():
                tdLog.info( f"sql:{tdSql.sql}, row:{i} col:0 data:{tdSql.queryResult[i][0]} == expect:{data_t1_c8[i]}" )
            else:
                caller = inspect.getframeinfo(inspect.stack()[1][0])
                tdLog.exit(f"{caller.filename}({caller.lineno}) failed: sql:{tdSql.sql} row:{i} col:0 data:{tdSql.queryResult[i][0]} != expect:{data_t1_c8[i]}")


        tdSql.query("select c9  from ct4")
        data_ct4_c9 = [tdSql.getData(i,0) for i in range(tdSql.queryRows)]
        tdSql.query("select c9  from t1")
        data_t1_c9 = [tdSql.getData(i,0) for i in range(tdSql.queryRows)]
        "c10 timestamp"

        tdLog.printNoPrefix("==========step35: cast nchar to nchar, expect no changes ")
        tdSql.query("select cast(c9 as nchar(32)) as b from ct4")
        for i in range(len(data_ct4_c9)):
            if data_ct4_c9[i] is None:
                tdSql.checkData( i, 0, None)
            elif tdSql.getData(i,0).strip() == data_ct4_c9[i].strip():
                tdLog.info( f"sql:{tdSql.sql}, row:{i} col:0 data:{tdSql.queryResult[i][0]} == expect:{data_ct4_c9[i]}" )
            else:
                caller = inspect.getframeinfo(inspect.stack()[1][0])
                tdLog.exit(f"{caller.filename}({caller.lineno}) failed: sql:{tdSql.sql} row:{i} col:0 data:{tdSql.queryResult[i][0]} != expect:{data_ct4_c9[i]}")
        tdSql.query("select cast(c9 as nchar(32)) as b from t1")
        for i in range(len(data_t1_c9)):
            tdSql.checkData( i, 0, data_t1_c9[i] )
            if data_t1_c9[i] is None:
                tdSql.checkData( i, 0, None)
            elif tdSql.getData(i,0).strip() == data_t1_c9[i].strip():
                tdLog.info( f"sql:{tdSql.sql}, row:{i} col:0 data:{tdSql.queryResult[i][0]} == expect:{data_t1_c9[i]}" )
            else:
                caller = inspect.getframeinfo(inspect.stack()[1][0])
                tdLog.exit(f"{caller.filename}({caller.lineno}) failed: sql:{tdSql.sql} row:{i} col:0 data:{tdSql.queryResult[i][0]} != expect:{data_t1_c9[i]}")

        tdLog.printNoPrefix("==========step36: cast nchar to nchar, expect truncate ")
        tdSql.query("select cast(c9 as nchar(2)) as b from ct4")
        for i in range(len(data_ct4_c9)):
            if data_ct4_c9[i] is None:
                tdSql.checkData( i, 0, None)
            elif tdSql.getData(i,0).strip() == data_ct4_c9[i].strip()[:2]:
                tdLog.info( f"sql:{tdSql.sql}, row:{i} col:0 data:{tdSql.queryResult[i][0]} == expect:{data_ct4_c9[i].strip()[:2]}" )
            else:
                caller = inspect.getframeinfo(inspect.stack()[1][0])
                tdLog.exit(f"{caller.filename}({caller.lineno}) failed: sql:{tdSql.sql} row:{i} col:0 data:{tdSql.queryResult[i][0]} != expect:{data_ct4_c9[i].strip()[:2]}")
        tdSql.query("select cast(c9 as nchar(2)) as b from t1")
        for i in range(len(data_t1_c9)):
            if data_t1_c9[i] is None:
                tdSql.checkData( i, 0, None)
            elif tdSql.getData(i,0).strip() == data_t1_c9[i].strip()[:2]:
                tdLog.info( f"sql:{tdSql.sql}, row:{i} col:0 data:{tdSql.queryResult[i][0]} == expect:{data_t1_c9[i].strip()[:2]}" )
            else:
                caller = inspect.getframeinfo(inspect.stack()[1][0])
                tdLog.exit(f"{caller.filename}({caller.lineno}) failed: sql:{tdSql.sql} row:{i} col:0 data:{tdSql.queryResult[i][0]} != expect:{data_t1_c9[i].strip()[:2]}")

        tdSql.query("select c9  from ct4")
        data_ct4_c10 = [tdSql.getData(i,0) for i in range(tdSql.queryRows)]
        tdSql.query("select c9  from t1")
        data_t1_c10 = [tdSql.getData(i,0) for i in range(tdSql.queryRows)]

        tdLog.printNoPrefix("==========step37: cast timestamp to nchar, expect no changes ")
        tdSql.query("select cast(c10 as nchar(32)) as b from ct4")
        for i in range(len(data_ct4_c10)):
            if data_ct4_c10[i] is None:
                tdSql.checkData( i, 0, None )
            else:
                time2str = str(int(datetime.datetime.timestamp(datetime.datetime.strptime(data_ct4_c10[i],'%Y-%m-%d %H:%M:%S.%f'))*1000))
                tdSql.checkData( i, 0, time2str )
        tdSql.query("select cast(c10 as nchar(32)) as b from t1")
        for i in range(len(data_t1_c10)):
            if data_t1_c10[i] is None:
                tdSql.checkData( i, 0, None )
            else:
                time2str = str(int(datetime.datetime.timestamp(datetime.datetime.strptime(data_t1_c10[i],'%Y-%m-%d %H:%M:%S.%f'))*1000))
                tdSql.checkData( i, 0, time2str )

        tdLog.printNoPrefix("==========step38: cast timestamp to binary, expect no changes ")
        tdSql.query("select cast(c10 as binary(32)) as b from ct4")
        for i in range(len(data_ct4_c10)):
            if data_ct4_c10[i] is None:
                tdSql.checkData( i, 0, None )
            else:
                time2str = str(int(datetime.datetime.timestamp(datetime.datetime.strptime(data_ct4_c10[i],'%Y-%m-%d %H:%M:%S.%f'))*1000))
                tdSql.checkData( i, 0, time2str )
        tdSql.query("select cast(c10 as binary(32)) as b from t1")
        for i in range(len(data_t1_c10)):
            if data_t1_c10[i] is None:
                tdSql.checkData( i, 0, None )
            else:
                time2str = str(int(datetime.datetime.timestamp(datetime.datetime.strptime(data_t1_c10[i],'%Y-%m-%d %H:%M:%S.%f'))*1000))
                tdSql.checkData( i, 0, time2str )


        tdSql.error("select cast(c1 as int) as b from ct4")
        tdSql.error("select cast(c1 as bool) as b from ct4")
        tdSql.error("select cast(c1 as tinyint) as b from ct4")
        tdSql.error("select cast(c1 as smallint) as b from ct4")
        tdSql.error("select cast(c1 as float) as b from ct4")
        tdSql.error("select cast(c1 as double) as b from ct4")
        tdSql.error("select cast(c1 as tinyint unsigned) as b from ct4")
        tdSql.error("select cast(c1 as smallint unsigned) as b from ct4")
        tdSql.error("select cast(c1 as int unsigned) as b from ct4")

        tdSql.error("select cast(c2 as int) as b from ct4")
        tdSql.error("select cast(c3 as bool) as b from ct4")
        tdSql.error("select cast(c4 as tinyint) as b from ct4")
        tdSql.error("select cast(c5 as smallint) as b from ct4")
        tdSql.error("select cast(c6 as float) as b from ct4")
        tdSql.error("select cast(c7 as double) as b from ct4")
        tdSql.error("select cast(c8 as tinyint unsigned) as b from ct4")

        tdSql.error("select cast(c8 as timestamp ) as b from ct4")
        tdSql.error("select cast(c9 as timestamp ) as b from ct4")
        tdSql.error("select cast(c9 as binary(64) ) as b from ct4")


    def stop(self):
        tdSql.close()
        tdLog.success(f"{__file__} successfully executed")

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