################################################################### # Copyright (c) 2016 by TAOS Technologies, Inc. # All rights reserved. # # This file is proprietary and confidential to TAOS Technologies. # No part of this file may be reproduced, stored, transmitted, # disclosed or used in any form or by any means other than as # expressly provided by the written permission from Jianhui Tao # ################################################################### # -*- coding: utf-8 -*- import sys import subprocess import random import math import numpy as np import inspect import re from util.log import * from util.cases import * from util.sql import * from util.dnodes import * class TDTestCase: def init(self, conn, logSql): tdLog.debug("start to execute %s" % __file__) tdSql.init(conn.cursor()) def csum_query_form(self, col="c1", alias="", table_expr="t1", condition=""): ''' csum function: :param col: string, column name, required parameters; :param alias: string, result column another name，or add other funtion; :param table_expr: string or expression, data source（eg,table/stable name, result set）, required parameters; :param condition: expression； :param args: other funtions,like: ', last(col)',or give result column another name, like 'c2' :return: csum query statement,default: select csum(c1) from t1 ''' return f"select csum({col}) {alias} from {table_expr} {condition}" def checkcsum(self,col="c1", alias="", table_expr="t1", condition="" ): line = sys._getframe().f_back.f_lineno pre_sql = self.csum_query_form( col=col, table_expr=table_expr, condition=condition ).replace("csum", "count") tdSql.query(pre_sql) if tdSql.queryRows == 0: tdSql.query(self.csum_query_form( col=col, alias=alias, table_expr=table_expr, condition=condition )) print(f"case in {line}: ", end='') tdSql.checkRows(0) return if "order by tbname" in condition: tdSql.error(self.csum_query_form( col=col, alias=alias, table_expr=table_expr, condition=condition )) return if "group" in condition: tb_condition = condition.split("group by")[1].split(" ")[1] tdSql.query(f"select distinct {tb_condition} from {table_expr}") query_result = tdSql.queryResult query_rows = tdSql.queryRows clear_condition = re.sub('order by [0-9a-z]*|slimit [0-9]*|soffset [0-9]*', "", condition) pre_row = 0 for i in range(query_rows): group_name = query_result[i][0] if "where" in clear_condition: pre_condition = re.sub('group by [0-9a-z]*', f"{tb_condition}='{group_name}'", clear_condition) else: pre_condition = "where " + re.sub('group by [0-9a-z]*',f"{tb_condition}='{group_name}'", clear_condition) tdSql.query(f"select {col} {alias} from {table_expr} {pre_condition}") pre_data = np.array(tdSql.queryResult)[np.array(tdSql.queryResult) != None] if (platform.system().lower() == 'windows' and pre_data.dtype == 'int32'): pre_data = np.array(pre_data, dtype = 'int64') print("data is ", pre_data) pre_csum = np.cumsum(pre_data) tdSql.query(self.csum_query_form( col=col, alias=alias, table_expr=table_expr, condition=condition )) for j in range(len(pre_csum)): print(f"case in {line}:", end='') tdSql.checkData(pre_row+j, 1, pre_csum[j]) pre_row += len(pre_csum) return elif "union" in condition: union_sql_0 = self.csum_query_form( col=col, alias=alias, table_expr=table_expr, condition=condition ).split("union all")[0] union_sql_1 = self.csum_query_form( col=col, alias=alias, table_expr=table_expr, condition=condition ).split("union all")[1] tdSql.query(union_sql_0) union_csum_0 = tdSql.queryResult row_union_0 = tdSql.queryRows tdSql.query(union_sql_1) union_csum_1 = tdSql.queryResult tdSql.query(self.csum_query_form( col=col, alias=alias, table_expr=table_expr, condition=condition )) for i in range(tdSql.queryRows): print(f"case in {line}: ", end='') if i < row_union_0: tdSql.checkData(i, 0, union_csum_0[i][0]) else: tdSql.checkData(i, 0, union_csum_1[i-row_union_0][0]) return else: tdSql.query(f"select {col} from {table_expr} {re.sub('limit [0-9]*|offset [0-9]*','',condition)}") offset_val = condition.split("offset")[1].split(" ")[1] if "offset" in condition else 0 pre_result = np.array(tdSql.queryResult)[np.array(tdSql.queryResult) != None] if (platform.system().lower() == 'windows' and pre_result.dtype == 'int32'): pre_result = np.array(pre_result, dtype = 'int64') pre_csum = np.cumsum(pre_result)[offset_val:] tdSql.query(self.csum_query_form( col=col, alias=alias, table_expr=table_expr, condition=condition )) for i in range(tdSql.queryRows): print(f"case in {line}: ", end='') if pre_csum[i] >1.7e+308 or pre_csum[i] < -1.7e+308: continue else: tdSql.checkData(i, 0, pre_csum[i]) pass def csum_current_query(self) : # table schema :ts timestamp, c1 int, c2 float, c3 timestamp, c4 binary(16), c5 double, c6 bool # c7 bigint, c8 smallint, c9 tinyint, c10 nchar(16) # case1～6： numeric col:int/bigint/tinyint/smallint/float/double self.checkcsum() case2 = {"col": "c2"} self.checkcsum(**case2) case3 = {"col": "c5"} self.checkcsum(**case3) case4 = {"col": "c7"} self.checkcsum(**case4) case5 = {"col": "c8"} self.checkcsum(**case5) case6 = {"col": "c9"} self.checkcsum(**case6) # case7~8: nested query # case7 = {"table_expr": "(select c1 from stb1)"} # self.checkcsum(**case7) # case8 = {"table_expr": "(select csum(c1) c1 from stb1 group by tbname)"} # self.checkcsum(**case8) # case9~10: mix with tbname/ts/tag/col # case9 = {"alias": ", tbname"} # self.checkcsum(**case9) # case10 = {"alias": ", _c0"} # self.checkcsum(**case10) # case11 = {"alias": ", st1"} # self.checkcsum(**case11) # case12 = {"alias": ", c1"} # self.checkcsum(**case12) # case13~15: with single condition case13 = {"condition": "where c1 <= 10"} self.checkcsum(**case13) case14 = {"condition": "where c6 in (0, 1)"} self.checkcsum(**case14) case15 = {"condition": "where c1 between 1 and 10"} self.checkcsum(**case15) # case16: with multi-condition case16 = {"condition": "where c6=1 or c6 =0"} self.checkcsum(**case16) # case17: only support normal table join case17 = { "col": "t1.c1", "table_expr": "t1, t2", "condition": "where t1.ts=t2.ts" } self.checkcsum(**case17) # case18~19: with group by # case18 = { # "table_expr": "t1", # "condition": "group by c6" # } # self.checkcsum(**case18) # case19 = { # "table_expr": "stb1", # "condition": "partition by tbname" # partition by tbname # } # self.checkcsum(**case19) # # case20~21: with order by # case20 = {"condition": "order by ts"} # self.checkcsum(**case20) # # case22: with union # case22 = { # "condition": "union all select csum(c1) from t2" # } # self.checkcsum(**case22) # case23: with limit/slimit case23 = { "condition": "limit 1" } self.checkcsum(**case23) # case24 = { # "table_expr": "stb1", # "condition": "group by tbname slimit 1 soffset 1" # } # self.checkcsum(**case24) pass def csum_error_query(self) -> None : # unusual test # # table schema :ts timestamp, c1 int, c2 float, c3 timestamp, c4 binary(16), c5 double, c6 bool # c7 bigint, c8 smallint, c9 tinyint, c10 nchar(16) # # form test tdSql.error(self.csum_query_form(col="")) # no col tdSql.error("csum(c1) from stb1") # no select tdSql.error("select csum from t1") # no csum condition tdSql.error("select csum c1 from t1") # no brackets tdSql.error("select csum(c1) t1") # no from tdSql.error("select csum( c1 ) from ") # no table_expr # tdSql.error(self.csum_query_form(col="st1")) # tag col # tdSql.error(self.csum_query_form(col=1)) # col is a value tdSql.error(self.csum_query_form(col="'c1'")) # col is a string tdSql.error(self.csum_query_form(col=None)) # col is NULL 1 tdSql.error(self.csum_query_form(col="NULL")) # col is NULL 2 tdSql.error(self.csum_query_form(col='""')) # col is "" tdSql.error(self.csum_query_form(col='c%')) # col is spercial char 1 tdSql.error(self.csum_query_form(col='c_')) # col is spercial char 2 tdSql.error(self.csum_query_form(col='c.')) # col is spercial char 3 tdSql.error(self.csum_query_form(col='c3')) # timestamp col tdSql.error(self.csum_query_form(col='ts')) # Primary key tdSql.error(self.csum_query_form(col='avg(c1)')) # expr col tdSql.error(self.csum_query_form(col='c6')) # bool col tdSql.error(self.csum_query_form(col='c4')) # binary col tdSql.error(self.csum_query_form(col='c10')) # nachr col tdSql.error(self.csum_query_form(col='c10')) # not table_expr col tdSql.error(self.csum_query_form(col='t1')) # tbname tdSql.error(self.csum_query_form(col='stb1')) # stbname tdSql.error(self.csum_query_form(col='db')) # datbasename tdSql.error(self.csum_query_form(col=True)) # col is BOOL 1 tdSql.error(self.csum_query_form(col='True')) # col is BOOL 2 tdSql.error(self.csum_query_form(col='*')) # col is all col tdSql.error("select csum[c1] from t1") # sql form error 1 tdSql.error("select csum{c1} from t1") # sql form error 2 tdSql.error(self.csum_query_form(col="[c1]")) # sql form error 3 # tdSql.error(self.csum_query_form(col="c1, c2")) # sql form error 3 # tdSql.error(self.csum_query_form(col="c1, 2")) # sql form error 3 tdSql.error(self.csum_query_form(alias=", count(c1)")) # mix with aggregate function 1 tdSql.error(self.csum_query_form(alias=", avg(c1)")) # mix with aggregate function 2 tdSql.error(self.csum_query_form(alias=", min(c1)")) # mix with select function 1 tdSql.error(self.csum_query_form(alias=", top(c1, 5)")) # mix with select function 2 tdSql.error(self.csum_query_form(alias=", spread(c1)")) # mix with calculation function 1 tdSql.error(self.csum_query_form(alias=", diff(c1)")) # mix with calculation function 2 # tdSql.error(self.csum_query_form(alias=" + 2")) # mix with arithmetic 1 tdSql.error(self.csum_query_form(alias=" + avg(c1)")) # mix with arithmetic 2 tdSql.error(self.csum_query_form(alias=", c2")) # mix with other 1 # tdSql.error(self.csum_query_form(table_expr="stb1")) # select stb directly stb_join = { "col": "stb1.c1", "table_expr": "stb1, stb2", "condition": "where stb1.ts=stb2.ts and stb1.st1=stb2.st2 order by stb1.ts" } tdSql.error(self.csum_query_form(**stb_join)) # stb join interval_sql = { "condition": "where ts>0 and ts < now interval(1h) fill(next)" } tdSql.error(self.csum_query_form(**interval_sql)) # interval group_normal_col = { "table_expr": "t1", "condition": "group by c6" } tdSql.error(self.csum_query_form(**group_normal_col)) # group by normal col slimit_soffset_sql = { "table_expr": "stb1", "condition": "group by tbname slimit 1 soffset 1" } # tdSql.error(self.csum_query_form(**slimit_soffset_sql)) order_by_tbname_sql = { "table_expr": "stb1", "condition": "group by tbname order by tbname" } tdSql.error(self.csum_query_form(**order_by_tbname_sql)) pass def csum_test_data(self, tbnum:int, data_row:int, basetime:int) -> None : for i in range(tbnum): for j in range(data_row): tdSql.execute( f"insert into t{i} values (" f"{basetime + (j+1)*10}, {random.randint(-200, -1)}, {random.uniform(200, -1)}, {basetime + random.randint(-200, -1)}, " f"'binary_{j}', {random.uniform(-200, -1)}, {random.choice([0,1])}, {random.randint(-200,-1)}, " f"{random.randint(-200, -1)}, {random.randint(-127, -1)}, 'nchar_{j}' )" ) tdSql.execute( f"insert into t{i} values (" f"{basetime - (j+1) * 10}, {random.randint(1, 200)}, {random.uniform(1, 200)}, {basetime - random.randint(1, 200)}, " f"'binary_{j}_1', {random.uniform(1, 200)}, {random.choice([0, 1])}, {random.randint(1,200)}, " f"{random.randint(1,200)}, {random.randint(1,127)}, 'nchar_{j}_1' )" ) tdSql.execute( f"insert into tt{i} values ( {basetime-(j+1) * 10}, {random.randint(1, 200)} )" ) pass def csum_test_table(self,tbnum: int) -> None : tdSql.execute("drop database if exists db") tdSql.execute("create database if not exists db keep 3650") tdSql.execute("use db") tdSql.execute( "create stable db.stb1 (\ ts timestamp, c1 int, c2 float, c3 timestamp, c4 binary(16), c5 double, c6 bool, \ c7 bigint, c8 smallint, c9 tinyint, c10 nchar(16)\ ) \ tags(st1 int)" ) tdSql.execute( "create stable db.stb2 (ts timestamp, c1 int) tags(st2 int)" ) for i in range(tbnum): tdSql.execute(f"create table t{i} using stb1 tags({i})") tdSql.execute(f"create table tt{i} using stb2 tags({i})") pass def csum_test_run(self) : tdLog.printNoPrefix("==========TD-10594==========") tbnum = 10 nowtime = int(round(time.time() * 1000)) per_table_rows = 2 self.csum_test_table(tbnum) tdLog.printNoPrefix("######## no data test:") self.csum_current_query() self.csum_error_query() tdLog.printNoPrefix("######## insert only NULL test:") for i in range(tbnum): tdSql.execute(f"insert into t{i}(ts) values ({nowtime - 5})") tdSql.execute(f"insert into t{i}(ts) values ({nowtime + 5})") self.csum_current_query() self.csum_error_query() tdLog.printNoPrefix("######## insert data in the range near the max(bigint/double):") self.csum_test_table(tbnum) tdSql.execute(f"insert into t1(ts, c1,c2,c5,c7) values " f"({nowtime - (per_table_rows + 1) * 10}, {2**31-1}, {3.4*10**38}, {1.7*10**308}, {2**63-1})") tdSql.execute(f"insert into t1(ts, c1,c2,c5,c7) values " f"({nowtime - (per_table_rows + 2) * 10}, {2**31-1}, {3.4*10**38}, {1.7*10**308}, {2**63-1})") self.csum_current_query() self.csum_error_query() tdLog.printNoPrefix("######## insert data in the range near the min(bigint/double):") self.csum_test_table(tbnum) tdSql.execute(f"insert into t1(ts, c1,c2,c5,c7) values " f"({nowtime - (per_table_rows + 1) * 10}, {1-2**31}, {-3.4*10**38}, {-1.7*10**308}, {1-2**63})") tdSql.execute(f"insert into t1(ts, c1,c2,c5,c7) values " f"({nowtime - (per_table_rows + 2) * 10}, {1-2**31}, {-3.4*10**38}, {-1.7*10**308}, {512-2**63})") self.csum_current_query() self.csum_error_query() tdLog.printNoPrefix("######## insert data without NULL data test:") self.csum_test_table(tbnum) self.csum_test_data(tbnum, per_table_rows, nowtime) self.csum_current_query() self.csum_error_query() tdLog.printNoPrefix("######## insert data mix with NULL test:") for i in range(tbnum): tdSql.execute(f"insert into t{i}(ts) values ({nowtime})") tdSql.execute(f"insert into t{i}(ts) values ({nowtime-(per_table_rows+3)*10})") tdSql.execute(f"insert into t{i}(ts) values ({nowtime+(per_table_rows+3)*10})") self.csum_current_query() self.csum_error_query() tdLog.printNoPrefix("######## check after WAL test:") tdSql.query("show dnodes") index = tdSql.getData(0, 0) tdDnodes.stop(index) tdDnodes.start(index) self.csum_current_query() self.csum_error_query() tdSql.query("select csum(1) from t1 ") tdSql.checkRows(7) tdSql.checkData(0,0,1) tdSql.checkData(1,0,2) tdSql.checkData(2,0,3) tdSql.checkData(3,0,4) tdSql.query("select csum(abs(c1))+2 from t1 ") tdSql.checkRows(4) def csum_support_stable(self): tdSql.query(" select csum(1) from stb1 ") tdSql.checkRows(70) tdSql.query("select csum(c1) from stb1 partition by tbname ") tdSql.checkRows(40) # tdSql.query("select csum(st1) from stb1 partition by tbname") # tdSql.checkRows(70) tdSql.query("select csum(st1+c1) from stb1 partition by tbname") tdSql.checkRows(40) tdSql.query("select csum(st1+c1) from stb1 partition by tbname") tdSql.checkRows(40) tdSql.query("select csum(st1+c1) from stb1 partition by tbname") tdSql.checkRows(40) # # bug need fix # tdSql.query("select csum(st1+c1) from stb1 partition by tbname slimit 1 ") # tdSql.checkRows(4) # tdSql.error("select csum(st1+c1) from stb1 partition by tbname limit 1 ") # bug need fix tdSql.query("select csum(st1+c1) from stb1 partition by tbname") tdSql.checkRows(40) # bug need fix # tdSql.query("select tbname , csum(c1) from stb1 partition by tbname") # tdSql.checkRows(40) # tdSql.query("select tbname , csum(st1) from stb1 partition by tbname") # tdSql.checkRows(70) # tdSql.query("select tbname , csum(st1) from stb1 partition by tbname slimit 1") # tdSql.checkRows(7) # partition by tags # tdSql.query("select st1 , csum(c1) from stb1 partition by st1") # tdSql.checkRows(40) # tdSql.query("select csum(c1) from stb1 partition by st1") # tdSql.checkRows(40) # tdSql.query("select st1 , csum(c1) from stb1 partition by st1 slimit 1") # tdSql.checkRows(4) # tdSql.query("select csum(c1) from stb1 partition by st1 slimit 1") # tdSql.checkRows(4) # partition by col # tdSql.query("select c1 , csum(c1) from stb1 partition by c1") # tdSql.checkRows(41) # tdSql.query("select csum(c1) from stb1 partition by c1") # tdSql.checkRows(41) # tdSql.query("select c1 , csum(c1) from stb1 partition by st1 slimit 1") # tdSql.checkRows(4) # tdSql.query("select csum(c1) from stb1 partition by st1 slimit 1") # tdSql.checkRows(4) def run(self): import traceback try: # run in develop branch self.csum_test_run() self.csum_support_stable() pass except Exception as e: traceback.print_exc() raise e def stop(self): tdSql.close() tdLog.success("%s successfully executed" % __file__) tdCases.addWindows(__file__, TDTestCase()) tdCases.addLinux(__file__, TDTestCase())