function_null.py

import taos
import sys
import datetime
import inspect

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


class TDTestCase:
    updatecfgDict = {'debugFlag': 143, "cDebugFlag": 143, "uDebugFlag": 143, "rpcDebugFlag": 143, "tmrDebugFlag": 143,
                     "jniDebugFlag": 143, "simDebugFlag": 143, "dDebugFlag": 143, "dDebugFlag": 143, "vDebugFlag": 143, "mDebugFlag": 143, "qDebugFlag": 143,
                     "wDebugFlag": 143, "sDebugFlag": 143, "tsdbDebugFlag": 143, "tqDebugFlag": 143, "fsDebugFlag": 143, "fnDebugFlag": 143}

    def init(self, conn, logSql):
        tdLog.debug(f"start to excute {__file__}")
        tdSql.init(conn.cursor(), True)
        self.tb_nums = 10
        self.row_nums = 20
        self.ts = 1434938400000
        self.time_step = 1000

    def prepare_tag_datas(self):
        # prepare datas
        tdSql.execute(
            "create database if not exists testdb keep 3650 duration 1000")
        tdSql.execute(" use testdb ")
        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 (t0 timestamp, t1 int, t2 bigint, t3 smallint, t4 tinyint, t5 float, t6 double, t7 bool, t8 binary(16),t9 nchar(32))
            '''
        )

        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 ( now(), {1*i}, {11111*i}, {111*i}, {11*i}, {1.11*i}, {11.11*i}, {i%2}, "binary{i}", "nchar{i}" )')

        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 ct1 values (now()+15s, 9, -99999, -999, -99, -9.99, NULL, 1, 'binary9', 'nchar9', now()+9a )")
        tdSql.execute(
            "insert into ct1 values (now()+20s, 9, -99999, -999, NULL, -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 )
            '''
        )

    def function_for_null_data(self):

        function_names = ["abs" , "floor" , "ceil" , "round"]

        for function_name in function_names:

            scalar_sql_1 = f"select {function_name}(c1)/0 from t1 group by c1 order by c1"
            scalar_sql_2 = f"select {function_name}(c1/0) from t1 group by c1 order by c1"
            scalar_sql_3 = f"select {function_name}(NULL) from t1 group by c1 order by c1"
            tdSql.query(scalar_sql_1)
            tdSql.checkRows(10)
            tdSql.checkData(0,0,None)
            tdSql.checkData(9,0,None)
            tdSql.query(scalar_sql_2)
            tdSql.checkRows(10)
            tdSql.checkData(0,0,None)
            tdSql.checkData(9,0,None)
            tdSql.query(scalar_sql_3)
            tdSql.checkRows(10)
            tdSql.checkData(0,0,None)
            tdSql.checkData(9,0,None)

        function_names = ["sin" ,"cos" ,"tan" ,"asin" ,"acos" ,"atan"]

        PI = 3.141592654

        # sin
        tdSql.query(" select sin(c1/0) from t1 group by c1 order by c1")
        tdSql.checkData(9,0,None)

        tdSql.query(" select sin(NULL) from t1 group by c1 order by c1")
        tdSql.checkData(9,0,None)

        tdSql.query(" select sin(0.00) from t1 group by c1 order by c1")
        tdSql.checkData(9,0,0.000000000)

        tdSql.query(f" select sin({PI/2}) from t1 group by c1 order by c1")
        tdSql.checkData(9,0,1.0)

        tdSql.query(" select sin(1000) from t1 group by c1 order by c1")
        tdSql.checkData(9,0,0.826879541)

        # cos 
        tdSql.query(" select cos(c1/0) from t1 group by c1 order by c1")
        tdSql.checkData(9,0,None)

        tdSql.query(" select cos(NULL) from t1 group by c1 order by c1")
        tdSql.checkData(9,0,None)

        tdSql.query(" select cos(0.00) from t1 group by c1 order by c1")
        tdSql.checkData(9,0,1.000000000)

        tdSql.query(f" select cos({PI}/2) from t1 group by c1 order by c1")

        tdSql.query(" select cos(1000) from t1 group by c1 order by c1")
        tdSql.checkData(9,0,0.562379076)  


        # tan
        tdSql.query(" select tan(c1/0) from t1 group by c1 order by c1")
        tdSql.checkData(9,0,None)

        tdSql.query(" select tan(NULL) from t1 group by c1 order by c1")
        tdSql.checkData(9,0,None)

        tdSql.query(" select tan(0.00) from t1 group by c1 order by c1")
        tdSql.checkData(9,0,0.000000000)

        tdSql.query(f" select tan({PI}/2) from t1 group by c1 order by c1")

        tdSql.query(" select tan(1000) from t1 group by c1 order by c1")
        tdSql.checkData(9,0,1.470324156)  

        # atan              
        tdSql.query(" select atan(c1/0) from t1 group by c1 order by c1")
        tdSql.checkData(9,0,None)

        tdSql.query(" select atan(NULL) from t1 group by c1 order by c1")
        tdSql.checkData(9,0,None)

        tdSql.query(" select atan(0.00) from t1 group by c1 order by c1")
        tdSql.checkData(9,0,0.000000000)

        tdSql.query(f" select atan({PI}/2) from t1 group by c1 order by c1")
        tdSql.checkData(9,0,1.003884822)

        tdSql.query(" select atan(1000) from t1 group by c1 order by c1")
        tdSql.checkData(9,0,1.569796327)  

        # asin 
        tdSql.query(" select asin(c1/0) from t1 group by c1 order by c1")
        tdSql.checkData(9,0,None)

        tdSql.query(" select asin(NULL) from t1 group by c1 order by c1")
        tdSql.checkData(9,0,None)

        tdSql.query(" select asin(0.00) from t1 group by c1 order by c1")
        tdSql.checkData(9,0,0.000000000)

        tdSql.query(f" select asin({PI}/2) from t1 group by c1 order by c1")

        tdSql.query(" select asin(1000) from t1 group by c1 order by c1")
        tdSql.checkData(9,0,None)  

        # acos

        tdSql.query(" select acos(c1/0) from t1 group by c1 order by c1")
        tdSql.checkData(9,0,None)

        tdSql.query(" select acos(NULL) from t1 group by c1 order by c1")
        tdSql.checkData(9,0,None)

        tdSql.query(" select acos(0.00) from t1 group by c1 order by c1")
        tdSql.checkData(9,0,1.570796327)

        tdSql.query(f" select acos({PI}/2) from t1 group by c1 order by c1")
        tdSql.checkData(9,0,None)

        tdSql.query(" select acos(1000) from t1 group by c1 order by c1")
        tdSql.checkData(9,0,None)  

        function_names = ["log" ,"pow"]

        # log 
        tdSql.query(" select log(-10) from t1 group by c1 order by c1")
        tdSql.checkData(9,0,None)

        tdSql.query(" select log(NULL ,2) from t1 group by c1 order by c1")
        tdSql.checkData(9,0,None)

        tdSql.query(" select log(c1)/0 from t1 group by c1 order by c1")
        tdSql.checkData(9,0,None)

        tdSql.query(f" select log(0.00) from t1 group by c1 order by c1")
        tdSql.checkData(9,0,None)

        # pow 
        tdSql.query(" select pow(c1,10000) from t1 group by c1 order by c1")
        tdSql.checkData(9,0,None)

        tdSql.query(" select pow(c1,2)/0 from t1 group by c1 order by c1")
        tdSql.checkData(9,0,None)

        tdSql.query(" select pow(NULL,2) from t1 group by c1 order by c1")
        tdSql.checkData(9,0,None)

        tdSql.query(f" select pow(c1/0 ,1 ) from t1 group by c1 order by c1")
        tdSql.checkData(9,0,None)       

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

        tdLog.printNoPrefix("==========step1:create table ==============")

        self.prepare_tag_datas()

        tdLog.printNoPrefix("==========step2:test errors ==============")

        self.function_for_null_data()
       

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


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