################################################################### # 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 import taos from util.log import * from util.cases import * from util.sql import * from util.dnodes import * dbname = 'db' class TDTestCase: def init(self, conn, logSql): tdLog.debug("start to execute %s" % __file__) tdSql.init(conn.cursor()) def mavg_query_form(self, sel="select", func="mavg(", col="c1", m_comm =",", k=1,r_comm=")", alias="", fr="from",table_expr=f"{dbname}.t1", condition=""): ''' mavg function: :param sel: string, must be "select", required parameters; :param func: string, in this case must be "mavg(", otherwise return other function, required parameters; :param col: string, column name, required parameters; :param m_comm: string, comma between col and k , required parameters; :param k: int/float，the width of the sliding window, [1,100], required parameters; :param r_comm: string, must be ")", use with "(" in func, required parameters; :param alias: string, result column another name，or add other funtion; :param fr: string, must be "from", required parameters; :param table_expr: string or expression, data source（eg,table/stable name, result set）, required parameters; :param condition: expression； :return: mavg query statement,default: select mavg(c1, 1) from t1 ''' return f"{sel} {func} {col} {m_comm} {k} {r_comm} {alias} {fr} {table_expr} {condition}" def checkmavg(self,sel="select", func="mavg(", col="c1", m_comm =",", k=1,r_comm=")", alias="", fr="from",table_expr=f"{dbname}.t1", condition=""): # print(self.mavg_query_form(sel=sel, func=func, col=col, m_comm=m_comm, k=k, r_comm=r_comm, alias=alias, fr=fr, # table_expr=table_expr, condition=condition)) line = sys._getframe().f_back.f_lineno if not all([sel , func , col , m_comm , k , r_comm , fr , table_expr]): print(f"case in {line}: ", end='') return tdSql.error(self.mavg_query_form( sel=sel, func=func, col=col, m_comm=m_comm, k=k, r_comm=r_comm, alias=alias, fr=fr, table_expr=table_expr, condition=condition )) sql = f"select * from {dbname}.t1" collist = tdSql.getColNameList(sql) if not isinstance(col, str): print(f"case in {line}: ", end='') return tdSql.error(self.mavg_query_form( sel=sel, func=func, col=col, m_comm=m_comm, k=k, r_comm=r_comm, alias=alias, fr=fr, table_expr=table_expr, condition=condition )) if len([x for x in col.split(",") if x.strip()]) != 1: print(f"case in {line}: ", end='') return tdSql.error(self.mavg_query_form( sel=sel, func=func, col=col, m_comm=m_comm, k=k, r_comm=r_comm, alias=alias, fr=fr, table_expr=table_expr, condition=condition )) col = col.replace(",", "").replace(" ", "") if any([re.compile('^[a-zA-Z]{1}.*$').match(col) is None , not col.replace(".","").isalnum()]): print(f"case in {line}: ", end='') return tdSql.error(self.mavg_query_form( sel=sel, func=func, col=col, m_comm=m_comm, k=k, r_comm=r_comm, alias=alias, fr=fr, table_expr=table_expr, condition=condition )) # if all(["," in col , len(col.split(",")) != 2]): # print(f"case in {line}: ", end='') # return tdSql.error(self.mavg_query_form( # sel=sel, func=func, col=col, m_comm=m_comm, k=k, r_comm=r_comm, alias=alias, fr=fr, # table_expr=table_expr, condition=condition # )) # # if ("," in col): # if (not col.split(",")[0].strip()) ^ (not col.split(",")[1].strip()): # col = col.strip().split(",")[0] if not col.split(",")[1].strip() else col.strip().split(",")[1] # else: # print(f"case in {line}: ", end='') # return tdSql.error(self.mavg_query_form( # sel=sel, func=func, col=col, m_comm=m_comm, k=k, r_comm=r_comm, alias=alias, fr=fr, # table_expr=table_expr, condition=condition # )) # pass if '.' in col: if any([col.split(".")[0] not in table_expr, col.split(".")[1] not in collist]): print(f"case in {line}: ", end='') return tdSql.error(self.mavg_query_form( sel=sel, func=func, col=col, m_comm=m_comm, k=k, r_comm=r_comm, alias=alias, fr=fr, table_expr=table_expr, condition=condition )) pass if "." not in col: if col not in collist: print(f"case in {line}: ", end='') return tdSql.error(self.mavg_query_form( sel=sel, func=func, col=col, m_comm=m_comm, k=k, r_comm=r_comm, alias=alias, fr=fr, table_expr=table_expr, condition=condition )) pass colname = col if "." not in col else col.split(".")[1] col_index = collist.index(colname) if any([tdSql.cursor.istype(col_index, "TIMESTAMP"), tdSql.cursor.istype(col_index, "BOOL")]): print(f"case in {line}: ", end='') return tdSql.error(self.mavg_query_form( sel=sel, func=func, col=col, m_comm=m_comm, k=k, r_comm=r_comm, alias=alias, fr=fr, table_expr=table_expr, condition=condition )) if any([tdSql.cursor.istype(col_index, "BINARY") , tdSql.cursor.istype(col_index,"NCHAR")]): print(f"case in {line}: ", end='') return tdSql.error(self.mavg_query_form( sel=sel, func=func, col=col, m_comm=m_comm, k=k, r_comm=r_comm, alias=alias, fr=fr, table_expr=table_expr, condition=condition )) if any( [func != "mavg(" , r_comm != ")" , fr != "from", sel != "select"]): print(f"case in {line}: ", end='') return tdSql.error(self.mavg_query_form( sel=sel, func=func, col=col, m_comm=m_comm, k=k, r_comm=r_comm, alias=alias, fr=fr, table_expr=table_expr, condition=condition )) if all(["(" not in table_expr, "stb" in table_expr, "group" not in condition.lower()]): print(f"case in {line}: ", end='') return tdSql.error(self.mavg_query_form( sel=sel, func=func, col=col, m_comm=m_comm, k=k, r_comm=r_comm, alias=alias, fr=fr, table_expr=table_expr, condition=condition )) if "order by tbname" in condition.lower(): print(f"case in {line}: ", end='') return tdSql.error(self.mavg_query_form( sel=sel, func=func, col=col, m_comm=m_comm, k=k, r_comm=r_comm, alias=alias, fr=fr, table_expr=table_expr, condition=condition )) if all(["group" in condition.lower(), "tbname" not in condition.lower()]): print(f"case in {line}: ", end='') return tdSql.error(self.mavg_query_form( sel=sel, func=func, col=col, m_comm=m_comm, k=k, r_comm=r_comm, alias=alias, fr=fr, table_expr=table_expr, condition=condition )) alias_list = ["tbname", "_c0", "st", "ts"] if all([alias, "," not in alias, not alias.isalnum()]): # actually， column alias also support "_"， but in this case，forbidden that。 print(f"case in {line}: ", end='') return tdSql.error(self.mavg_query_form( sel=sel, func=func, col=col, m_comm=m_comm, k=k, r_comm=r_comm, alias=alias, fr=fr, table_expr=table_expr, condition=condition )) if all([alias, "," in alias]): if all(parm != alias.lower().split(",")[1].strip() for parm in alias_list): print(f"case in {line}: ", end='') return tdSql.error(self.mavg_query_form( sel=sel, func=func, col=col, m_comm=m_comm, k=k, r_comm=r_comm, alias=alias, fr=fr, table_expr=table_expr, condition=condition )) pass condition_exception = [ "~", "^", "insert", "distinct", "count", "avg", "twa", "irate", "sum", "stddev", "leastquares", "min", "max", "first", "last", "top", "bottom", "percentile", "apercentile", "last_row", "interp", "diff", "derivative", "spread", "ceil", "floor", "round", "interval", "fill", "slimit", "soffset"] if "union" not in condition.lower(): if any(parm in condition.lower().strip() for parm in condition_exception): print(f"case in {line}: ", end='') return tdSql.error(self.mavg_query_form( sel=sel, func=func, col=col, m_comm=m_comm, k=k, r_comm=r_comm, alias=alias, fr=fr, table_expr=table_expr, condition=condition )) pass if not any([isinstance(k, int) , isinstance(k, float)]) : print(f"case in {line}: ", end='') return tdSql.error(self.mavg_query_form( col=col, k=k, alias=alias, table_expr=table_expr, condition=condition )) if not(1 <= k < 1001): print(f"case in {line}: ", end='') return tdSql.error(self.mavg_query_form( col=col, k=k, alias=alias, table_expr=table_expr, condition=condition )) k = int(k // 1) pre_sql = re.sub("mavg\([a-z0-9 .,]*\)", f"count({col})", self.mavg_query_form( col=col, table_expr=table_expr, condition=condition )) tdSql.query(pre_sql) if tdSql.queryRows == 0: tdSql.query(self.mavg_query_form( sel=sel, func=func, col=col, m_comm=m_comm, k=k, r_comm=r_comm, alias=alias, fr=fr, table_expr=table_expr, condition=condition )) print(f"case in {line}: ", end='') tdSql.checkRows(0) 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') pre_mavg = np.convolve(pre_data, np.ones(k), "valid")/k tdSql.query(self.mavg_query_form( sel=sel, func=func, col=col, m_comm=m_comm, k=k, r_comm=r_comm, alias=alias, fr=fr, table_expr=table_expr, condition=condition )) for j in range(len(pre_mavg)): print(f"case in {line}:", end='') tdSql.checkData(pre_row+j, 0, pre_mavg[j]) pre_row += len(pre_mavg) return elif "union" in condition: union_sql_0 = self.mavg_query_form( sel=sel, func=func, col=col, m_comm=m_comm, k=k, r_comm=r_comm, alias=alias, fr=fr, table_expr=table_expr, condition=condition ).split("union all")[0] union_sql_1 = self.mavg_query_form( sel=sel, func=func, col=col, m_comm=m_comm, k=k, r_comm=r_comm, alias=alias, fr=fr, table_expr=table_expr, condition=condition ).split("union all")[1] tdSql.query(union_sql_0) union_mavg_0 = tdSql.queryResult row_union_0 = tdSql.queryRows tdSql.query(union_sql_1) union_mavg_1 = tdSql.queryResult tdSql.query(self.mavg_query_form( sel=sel, func=func, col=col, m_comm=m_comm, k=k, r_comm=r_comm, alias=alias, fr=fr, 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_mavg_0[i][0]) else: tdSql.checkData(i, 0, union_mavg_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 # print(f"select {col} from {table_expr} {re.sub('limit [0-9]*|offset [0-9]*','',condition)}") if not tdSql.queryResult: 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_mavg = pre_mavg = np.convolve(pre_result, np.ones(k), "valid")[offset_val:]/k tdSql.query(self.mavg_query_form( sel=sel, func=func, col=col, m_comm=m_comm, k=k, r_comm=r_comm, alias=alias, fr=fr, table_expr=table_expr, condition=condition )) for i in range(tdSql.queryRows): print(f"case in {line}: ", end='') tdSql.checkData(i, 0, pre_mavg[i]) pass def mavg_current_query(self, dbname="db") : # 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.checkmavg() case2 = {"col": "c2"} self.checkmavg(**case2) case3 = {"col": "c5"} self.checkmavg(**case3) case4 = {"col": "c7"} self.checkmavg(**case4) case5 = {"col": "c8"} self.checkmavg(**case5) case6 = {"col": "c9"} self.checkmavg(**case6) # case7~8: nested query case7 = {"table_expr": f"(select c1 from {dbname}.stb1)"} self.checkmavg(**case7) # case8 = {"table_expr": f"(select _c0, mavg(c1, 1) c1 from {dbname}.stb1 group by tbname)"} # self.checkmavg(**case8) # case9~10: mix with tbname/ts/tag/col case9 = {"alias": ", tbname"} self.checkmavg(**case9) case10 = {"alias": ", _c0"} self.checkmavg(**case10) # case11 = {"alias": ", st1"} # self.checkmavg(**case11) # case12 = {"alias": ", c1"} # self.checkmavg(**case12) # case13~15: with single condition case13 = {"condition": "where c1 <= 10"} self.checkmavg(**case13) case14 = {"condition": "where c6 in (0, 1)"} self.checkmavg(**case14) case15 = {"condition": "where c1 between 1 and 10"} self.checkmavg(**case15) # case16: with multi-condition case16 = {"condition": "where c6=1 or c6 =0"} self.checkmavg(**case16) # case17: only support normal table join case17 = { "col": "t1.c1", "table_expr": f"{dbname}.t1 t1, {dbname}.t2 t2", "condition": "where t1.ts=t2.ts" } self.checkmavg(**case17) # # case18~19: with group by # case19 = { # "table_expr": f"{dbname}.stb1", # "condition": "partition by tbname" # } # self.checkmavg(**case19) # # case20~21: with order by # case20 = {"condition": "order by ts"} # self.checkmavg(**case20) case21 = { "table_expr": f"{dbname}.stb1", "condition": "group by tbname order by tbname" } self.checkmavg(**case21) # # case22: with union # case22 = { # "condition": f"union all select mavg( c1 , 1 ) from {dbname}.t2" # } # self.checkmavg(**case22) # case23: with limit/slimit case23 = { "condition": "limit 1" } self.checkmavg(**case23) # case24: value k range[1, 100], can be int or float, k = floor(k) case24 = {"k": 3} self.checkmavg(**case24) case25 = {"k": 2.999} self.checkmavg(**case25) case26 = {"k": 1000} self.checkmavg(**case26) pass def mavg_error_query(self, dbname="db") -> None : # unusual test # form test err1 = {"col": ""} self.checkmavg(**err1) # no col err2 = {"sel": ""} self.checkmavg(**err2) # no select err3 = {"func": "mavg", "col": "", "m_comm": "", "k": "", "r_comm": ""} self.checkmavg(**err3) # no mavg condition: select mavg from err4 = {"col": "", "m_comm": "", "k": ""} self.checkmavg(**err4) # no mavg condition: select mavg() from err5 = {"func": "mavg", "r_comm": ""} self.checkmavg(**err5) # no brackets: select mavg col, k from err6 = {"fr": ""} self.checkmavg(**err6) # no from err7 = {"k": ""} self.checkmavg(**err7) # no k err8 = {"table_expr": ""} self.checkmavg(**err8) # no table_expr err9 = {"col": "st1"} # self.checkmavg(**err9) # col: tag err10 = {"col": 1} # self.checkmavg(**err10) # col: value err11 = {"col": "NULL"} self.checkmavg(**err11) # col: NULL err12 = {"col": "%_"} self.checkmavg(**err12) # col: %_ err13 = {"col": "c3"} self.checkmavg(**err13) # col: timestamp col err14 = {"col": "_c0"} self.checkmavg(**err14) # col: Primary key err15 = {"col": "avg(c1)"} self.checkmavg(**err15) # expr col err16 = {"col": "c4"} self.checkmavg(**err16) # binary col err17 = {"col": "c10"} self.checkmavg(**err17) # nchar col err18 = {"col": "c6"} self.checkmavg(**err18) # bool col err19 = {"col": "'c1'"} self.checkmavg(**err19) # col: string err20 = {"col": None} self.checkmavg(**err20) # col: None err21 = {"col": "''"} self.checkmavg(**err21) # col: '' err22 = {"col": "tt1.c1"} self.checkmavg(**err22) # not table_expr col err23 = {"col": "t1"} self.checkmavg(**err23) # tbname err24 = {"col": "stb1"} self.checkmavg(**err24) # stbname err25 = {"col": "db"} self.checkmavg(**err25) # datbasename err26 = {"col": "True"} self.checkmavg(**err26) # col: BOOL 1 err27 = {"col": True} self.checkmavg(**err27) # col: BOOL 2 err28 = {"col": "*"} self.checkmavg(**err28) # col: all col err29 = {"func": "mavg[", "r_comm": "]"} self.checkmavg(**err29) # form: mavg[col, k] err30 = {"func": "mavg{", "r_comm": "}"} self.checkmavg(**err30) # form: mavg{col, k} err31 = {"col": "[c1]"} self.checkmavg(**err31) # form: mavg([col], k) err32 = {"col": "c1, c2"} self.checkmavg(**err32) # form: mavg(col, col2, k) err33 = {"col": "c1, 2"} self.checkmavg(**err33) # form: mavg(col, k1, k2) err34 = {"alias": ", count(c1)"} self.checkmavg(**err34) # mix with aggregate function 1 err35 = {"alias": ", avg(c1)"} self.checkmavg(**err35) # mix with aggregate function 2 err36 = {"alias": ", min(c1)"} self.checkmavg(**err36) # mix with select function 1 err37 = {"alias": ", top(c1, 5)"} self.checkmavg(**err37) # mix with select function 2 err38 = {"alias": ", spread(c1)"} self.checkmavg(**err38) # mix with calculation function 1 err39 = {"alias": ", diff(c1)"} self.checkmavg(**err39) # mix with calculation function 2 # err40 = {"alias": "+ 2"} # self.checkmavg(**err40) # mix with arithmetic 1 #tdSql.query(" select mavg( c1 , 1 ) + 2 from t1 ") err41 = {"alias": "+ avg(c1)"} self.checkmavg(**err41) # mix with arithmetic 2 # err42 = {"alias": ", c1"} # self.checkmavg(**err42) # mix with other col # err43 = {"table_expr": f"{dbname}.stb1"} # self.checkmavg(**err43) # select stb directly # err44 = { # "col": "stb1.c1", # "table_expr": "stb1, stb2", # "condition": "where stb1.ts=stb2.ts and stb1.st1=stb2.st2 order by stb1.ts" # } # self.checkmavg(**err44) # stb join tdSql.query(f"select mavg( stb1.c1 , 1 ) from {dbname}.stb1 stb1, {dbname}.stb2 stb2 where stb1.ts=stb2.ts and stb1.st1=stb2.st2 order by stb1.ts;") err45 = { "condition": "where ts>0 and ts < now interval(1h) fill(next)" } self.checkmavg(**err45) # interval err46 = { "table_expr": f"{dbname}.t1", "condition": "group by c6" } self.checkmavg(**err46) # group by normal col err47 = { "table_expr": f"{dbname}.stb1", "condition": "group by tbname slimit 1 " } # self.checkmavg(**err47) # with slimit err48 = { "table_expr": f"{dbname}.stb1", "condition": "group by tbname slimit 1 soffset 1" } # self.checkmavg(**err48) # with soffset err49 = {"k": "2021-01-01 00:00:00.000"} self.checkmavg(**err49) # k: timestamp err50 = {"k": False} self.checkmavg(**err50) # k: False err51 = {"k": "%"} self.checkmavg(**err51) # k: special char err52 = {"k": ""} self.checkmavg(**err52) # k: "" err53 = {"k": None} self.checkmavg(**err53) # k: None err54 = {"k": "NULL"} self.checkmavg(**err54) # k: null err55 = {"k": "binary(4)"} self.checkmavg(**err55) # k: string err56 = {"k": "c1"} self.checkmavg(**err56) # k: sring，col name err57 = {"col": "c1, 1, c2"} self.checkmavg(**err57) # form: mavg(col1, k1, col2, k2) err58 = {"col": "c1 cc1"} self.checkmavg(**err58) # form: mavg(col newname, k) err59 = {"k": "'1'"} # self.checkmavg(**err59) # formL mavg(colm, "1") err60 = {"k": "-1-(-2)"} # self.checkmavg(**err60) # formL mavg(colm, -1-2) err61 = {"k": 1001} self.checkmavg(**err61) # k: right out of [1, 1000] err62 = {"k": -1} self.checkmavg(**err62) # k: negative number err63 = {"k": 0} self.checkmavg(**err63) # k: 0 err64 = {"k": 2**63-1} self.checkmavg(**err64) # k: max(bigint) err65 = {"k": 1-2**63} # self.checkmavg(**err65) # k: min(bigint) err66 = {"k": -2**63} self.checkmavg(**err66) # k: NULL err67 = {"k": 0.999999} self.checkmavg(**err67) # k: left out of [1, 1000] err68 = { "table_expr": f"{dbname}.stb1", "condition": f"group by tbname order by tbname" # order by tbname not supported } self.checkmavg(**err68) pass def mavg_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 {dbname}.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 {dbname}.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 {dbname}.tt{i} values ( {basetime-(j+1) * 10}, {random.randint(1, 200)} )" ) pass def mavg_test_table(self,tbnum: int) -> None : tdSql.execute(f"drop database if exists {dbname}") tdSql.execute(f"create database if not exists {dbname} keep 3650") tdSql.execute(f"use {dbname}") tdSql.execute( f"create stable {dbname}.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( f"create stable {dbname}.stb2 (ts timestamp, c1 int) tags(st2 int)" ) for i in range(tbnum): tdSql.execute(f"create table {dbname}.t{i} using {dbname}.stb1 tags({i})") tdSql.execute(f"create table {dbname}.tt{i} using {dbname}.stb2 tags({i})") pass def mavg_test_run(self) : tdLog.printNoPrefix("==========TD-10594==========") tbnum = 10 nowtime = int(round(time.time() * 1000)) per_table_rows = 2 self.mavg_test_table(tbnum) tdLog.printNoPrefix("######## no data test:") self.mavg_current_query() self.mavg_error_query() tdLog.printNoPrefix("######## insert only NULL test:") for i in range(tbnum): tdSql.execute(f"insert into {dbname}.t{i}(ts) values ({nowtime - 5})") tdSql.execute(f"insert into {dbname}.t{i}(ts) values ({nowtime + 5})") self.mavg_current_query() self.mavg_error_query() tdLog.printNoPrefix("######## insert data in the range near the max(bigint/double):") # self.mavg_test_table(tbnum) # tdSql.execute(f"insert into {dbname}.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 {dbname}.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.mavg_current_query() # self.mavg_error_query() tdLog.printNoPrefix("######## insert data in the range near the min(bigint/double):") # self.mavg_test_table(tbnum) # tdSql.execute(f"insert into {dbname}.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 {dbname}.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.mavg_current_query() # self.mavg_error_query() tdLog.printNoPrefix("######## insert data without NULL data test:") self.mavg_test_table(tbnum) self.mavg_test_data(tbnum, per_table_rows, nowtime) self.mavg_current_query() self.mavg_error_query() tdLog.printNoPrefix("######## insert data mix with NULL test:") for i in range(tbnum): tdSql.execute(f"insert into {dbname}.t{i}(ts) values ({nowtime})") tdSql.execute(f"insert into {dbname}.t{i}(ts) values ({nowtime-(per_table_rows+3)*10})") tdSql.execute(f"insert into {dbname}.t{i}(ts) values ({nowtime+(per_table_rows+3)*10})") self.mavg_current_query() self.mavg_error_query() tdLog.printNoPrefix("######## check after WAL test:") tdSql.query("select * from information_schema.ins_dnodes") index = tdSql.getData(0, 0) tdDnodes.stop(index) tdDnodes.start(index) self.mavg_current_query() self.mavg_error_query() tdSql.query(f"select mavg(1,1) from {dbname}.t1") tdSql.checkRows(7) tdSql.checkData(0,0,1.000000000) tdSql.checkData(1,0,1.000000000) tdSql.checkData(5,0,1.000000000) tdSql.query(f"select mavg(abs(c1),1) from {dbname}.t1") tdSql.checkRows(4) def mavg_support_stable(self): tdSql.query(f" select mavg(1,3) from {dbname}.stb1 ") tdSql.checkRows(68) tdSql.checkData(0,0,1.000000000) tdSql.query(f"select mavg(c1,3) from {dbname}.stb1 partition by tbname ") tdSql.checkRows(20) tdSql.query(f"select mavg(st1,3) from {dbname}.stb1 partition by tbname") tdSql.checkRows(50) tdSql.query(f"select mavg(st1+c1,3) from {dbname}.stb1 partition by tbname") tdSql.checkRows(20) tdSql.query(f"select mavg(st1+c1,3) from {dbname}.stb1 partition by tbname") tdSql.checkRows(20) tdSql.query(f"select mavg(st1+c1,3) from {dbname}.stb1 partition by tbname") tdSql.checkRows(20) # bug need fix tdSql.query(f"select mavg(st1+c1,3) from {dbname}.stb1 partition by tbname") tdSql.checkRows(20) # bug need fix tdSql.query(f"select tbname , mavg(c1,3) from {dbname}.stb1 partition by tbname") tdSql.checkRows(20) tdSql.query(f"select tbname , mavg(st1,3) from {dbname}.stb1 partition by tbname") tdSql.checkRows(50) tdSql.query(f"select tbname , mavg(st1,3) from {dbname}.stb1 partition by tbname slimit 1") tdSql.checkRows(5) # partition by tags tdSql.query(f"select st1 , mavg(c1,3) from {dbname}.stb1 partition by st1") tdSql.checkRows(20) tdSql.query(f"select mavg(c1,3) from {dbname}.stb1 partition by st1") tdSql.checkRows(20) tdSql.query(f"select st1 , mavg(c1,3) from {dbname}.stb1 partition by st1 slimit 1") tdSql.checkRows(2) tdSql.query(f"select mavg(c1,3) from {dbname}.stb1 partition by st1 slimit 1") tdSql.checkRows(2) # partition by col tdSql.query(f"select c1 , mavg(c1,3) from {dbname}.stb1 partition by c1") tdSql.checkRows(0) tdSql.query(f"select c1 , mavg(c1,1) from {dbname}.stb1 partition by c1") tdSql.checkRows(40) tdSql.query(f"select c1, c2, c3, c4, mavg(c1,3) from {dbname}.stb1 partition by tbname ") tdSql.checkRows(20) tdSql.query(f"select c1, c2, c3, c4, mavg(123,3) from {dbname}.stb1 partition by tbname ") tdSql.checkRows(50) def run(self): import traceback try: # run in develop branch self.mavg_test_run() self.mavg_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())