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, "udfDebugFlag": 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}, {1*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())