################################################################### # 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 -*- from pstats import Stats 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 * class TDTestCase: def init(self, conn, logSql): tdLog.debug("start to execute %s" % __file__) tdSql.init(conn.cursor()) def sample_query_form(self, sel="select", func="sample(", col="c1", m_comm =",", k=1,r_comm=")", alias="", fr="from",table_expr="t1", condition=""): ''' sample function: :param sel: string, must be "select", required parameters; :param func: string, in this case must be "sample(", 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: sample query statement,default: select sample(c1, 1) from t1 ''' return f"{sel} {func} {col} {m_comm} {k} {r_comm} {alias} {fr} {table_expr} {condition}" def checksample(self,sel="select", func="sample(", col="c1", m_comm =",", k=1,r_comm=")", alias="", fr="from",table_expr="t1", condition=""): # print(self.sample_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.sample_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 = "select * from t1" collist = tdSql.getColNameList(sql) if not isinstance(col, str): print(f"case in {line}: ", end='') return tdSql.error(self.sample_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.sample_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.sample_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 any([col.split(".")[0] not in table_expr, col.split(".")[1] not in collist]): print(f"case in {line}: ", end='') return tdSql.error(self.sample_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.sample_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.sample_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.sample_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 != "sample(" , r_comm != ")" , fr != "from", sel != "select"]): print(f"case in {line}: ", end='') return tdSql.error(self.sample_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.sample_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.sample_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]): if any([ not alias.isalnum(), re.compile('^[a-zA-Z]{1}.*$').match(col) is None ]): # actually， column alias also support "_"， but in this case，forbidden that。 print(f"case in {line}: ", end='') return tdSql.error(self.sample_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.sample_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.sample_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.sample_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.sample_query_form( col=col, k=k, alias=alias, table_expr=table_expr, condition=condition )) k = int(k // 1) pre_sql = re.sub("sample\([a-z0-9 .,]*\)", f"count({col})", self.sample_query_form( col=col, table_expr=table_expr, condition=condition )) tdSql.query(pre_sql) if tdSql.queryRows == 0: tdSql.query(self.sample_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 tdSql.query(self.sample_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 )) sample_result = tdSql.queryResult sample_len = tdSql.queryRows 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"and {tb_condition}='{group_name}' and {col} is not null", clear_condition) else: pre_condition = "where " + re.sub('group by [0-9a-z]*',f"{tb_condition}='{group_name}' and {col} is not null", clear_condition) tdSql.query(f"select ts, {col} {alias} from {table_expr} {pre_condition}") # pre_data = np.array(tdSql.queryResult)[np.array(tdSql.queryResult) != None] # pre_sample = np.convolve(pre_data, np.ones(k), "valid")/k pre_sample = tdSql.queryResult pre_len = tdSql.queryRows step = pre_len if pre_len < k else k # tdSql.query(self.sample_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(step): if sample_result[pre_row:pre_row+step][i] not in pre_sample: tdLog.exit(f"case in {line} is failed: sample data is not in {group_name}") else: tdLog.info(f"case in {line} is success: sample data is in {group_name}") # for j in range(len(pre_sample)): # print(f"case in {line}:", end='') # tdSql.checkData(pre_row+j, 1, pre_sample[j]) pre_row += step return elif "union" in condition: union_sql_0 = self.sample_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.sample_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_sample_0 = tdSql.queryResult row_union_0 = tdSql.queryRows tdSql.query(union_sql_1) # union_sample_1 = tdSql.queryResult row_union_1 = tdSql.queryRows tdSql.query(self.sample_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, 1, union_sample_0[i][1]) # else: # tdSql.checkData(i, 1, union_sample_1[i-row_union_0][1]) if row_union_0 + row_union_1 != sample_len: tdLog.exit(f"case in {line} is failed: sample data is not in ") else: tdLog.info(f"case in {line} is success: sample data is in ") return else: if "where" in condition: condition = re.sub('where', f"where {col} is not null and ", condition) else: condition = f"where {col} is not null" + condition print(f"select ts, {col} {alias} from {table_expr} {re.sub('limit [0-9]*|offset [0-9]*','',condition)}") tdSql.query(f"select ts, {col} {alias} 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_sample = tdSql.queryResult # pre_len = tdSql.queryRows # for i in range(sample_len): # if sample_result[pre_row:pre_row + step][i] not in pre_sample: # tdLog.exit(f"case in {line} is failed: sample data is not in {group_name}") # else: # tdLog.info(f"case in {line} is success: sample data is in {group_name}") pass def sample_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.checksample() case2 = {"col": "c2"} self.checksample(**case2) case3 = {"col": "c5"} self.checksample(**case3) case4 = {"col": "c7"} self.checksample(**case4) case5 = {"col": "c8"} self.checksample(**case5) case6 = {"col": "c9"} self.checksample(**case6) # # case7~8: nested query # case7 = {"table_expr": "(select c1 from stb1)"} # self.checksample(**case7) # case8 = {"table_expr": "(select sample(c1, 1) c1 from stb1 group by tbname)"} # self.checksample(**case8) # case9~10: mix with tbname/ts/tag/col # case9 = {"alias": ", tbname"} # self.checksample(**case9) # case10 = {"alias": ", _c0"} # self.checksample(**case10) # case11 = {"alias": ", st1"} # self.checksample(**case11) tdSql.query("select sample( c1 , 1 ) , st1 from t1") # case12 = {"alias": ", c1"} # self.checksample(**case12) tdSql.query("select sample( c1 , 1 ) , c1 from t1") # case13~15: with single condition case13 = {"condition": "where c1 <= 10"} self.checksample(**case13) case14 = {"condition": "where c6 in (0, 1)"} self.checksample(**case14) case15 = {"condition": "where c1 between 1 and 10"} self.checksample(**case15) # case16: with multi-condition case16 = {"condition": "where c6=1 or c6 =0"} self.checksample(**case16) # # case17: only support normal table join # case17 = { # "col": "t1.c1", # "table_expr": "t1, t2", # "condition": "where t1.ts=t2.ts" # } # self.checksample(**case17) # # case18~19: with group by # case19 = { # "table_expr": "stb1", # "condition": "partition by tbname" # } # self.checksample(**case19) # # case20~21: with order by # case20 = {"condition": "order by ts"} # self.checksample(**case20) # case21 = { # "table_expr": "stb1", # "condition": "partition by tbname order by tbname" # } # self.checksample(**case21) # case22: with union case22 = { "condition": "union all select sample( c1 , 1 ) from t2" } self.checksample(**case22) # case23: with limit/slimit case23 = { "condition": "limit 1" } self.checksample(**case23) # case24: value k range[1, 100], can be int or float, k = floor(k) case24 = {"k": 3} self.checksample(**case24) case25 = {"k": 2.999} self.checksample(**case25) case26 = {"k": 1000} self.checksample(**case26) case27 = { "table_expr": "stb1", "condition": "group by tbname slimit 1 " } self.checksample(**case27) # with slimit case28 = { "table_expr": "stb1", "condition": "group by tbname slimit 1 soffset 1" } self.checksample(**case28) # with soffset pass def sample_error_query(self) -> None : # unusual test # form test err1 = {"col": ""} self.checksample(**err1) # no col err2 = {"sel": ""} self.checksample(**err2) # no select err3 = {"func": "sample", "col": "", "m_comm": "", "k": "", "r_comm": ""} self.checksample(**err3) # no sample condition: select sample from err4 = {"col": "", "m_comm": "", "k": ""} self.checksample(**err4) # no sample condition: select sample() from err5 = {"func": "sample", "r_comm": ""} self.checksample(**err5) # no brackets: select sample col, k from err6 = {"fr": ""} self.checksample(**err6) # no from err7 = {"k": ""} self.checksample(**err7) # no k err8 = {"table_expr": ""} self.checksample(**err8) # no table_expr # err9 = {"col": "st1"} # self.checksample(**err9) # col: tag tdSql.query(" select sample(st1 ,1) from t1 ") # err10 = {"col": 1} # self.checksample(**err10) # col: value # err11 = {"col": "NULL"} # self.checksample(**err11) # col: NULL err12 = {"col": "%_"} self.checksample(**err12) # col: %_ err13 = {"col": "c3"} self.checksample(**err13) # col: timestamp col err14 = {"col": "_c0"} # self.checksample(**err14) # col: Primary key err15 = {"col": "avg(c1)"} # self.checksample(**err15) # expr col err16 = {"col": "c4"} self.checksample(**err16) # binary col err17 = {"col": "c10"} self.checksample(**err17) # nchar col err18 = {"col": "c6"} self.checksample(**err18) # bool col # err19 = {"col": "'c1'"} # self.checksample(**err19) # col: string err20 = {"col": None} self.checksample(**err20) # col: None # err21 = {"col": "''"} # self.checksample(**err21) # col: '' err22 = {"col": "tt1.c1"} self.checksample(**err22) # not table_expr col err23 = {"col": "t1"} self.checksample(**err23) # tbname err24 = {"col": "stb1"} self.checksample(**err24) # stbname err25 = {"col": "db"} self.checksample(**err25) # datbasename # err26 = {"col": "True"} # self.checksample(**err26) # col: BOOL 1 # err27 = {"col": True} # self.checksample(**err27) # col: BOOL 2 err28 = {"col": "*"} self.checksample(**err28) # col: all col err29 = {"func": "sample[", "r_comm": "]"} self.checksample(**err29) # form: sample[col, k] err30 = {"func": "sample{", "r_comm": "}"} self.checksample(**err30) # form: sample{col, k} err31 = {"col": "[c1]"} self.checksample(**err31) # form: sample([col], k) err32 = {"col": "c1, c2"} self.checksample(**err32) # form: sample(col, col2, k) err33 = {"col": "c1, 2"} self.checksample(**err33) # form: sample(col, k1, k2) err34 = {"alias": ", count(c1)"} self.checksample(**err34) # mix with aggregate function 1 err35 = {"alias": ", avg(c1)"} self.checksample(**err35) # mix with aggregate function 2 err36 = {"alias": ", min(c1)"} self.checksample(**err36) # mix with select function 1 err37 = {"alias": ", top(c1, 5)"} self.checksample(**err37) # mix with select function 2 err38 = {"alias": ", spread(c1)"} self.checksample(**err38) # mix with calculation function 1 err39 = {"alias": ", diff(c1)"} self.checksample(**err39) # mix with calculation function 2 # err40 = {"alias": "+ 2"} # self.checksample(**err40) # mix with arithmetic 1 # tdSql.query(" select sample(c1 , 1) + 2 from t1 ") err41 = {"alias": "+ avg(c1)"} # self.checksample(**err41) # mix with arithmetic 2 # err42 = {"alias": ", c1"} # self.checksample(**err42) tdSql.query("select sample( c1 , 1 ) , c1 from t1") # mix with other col # err43 = {"table_expr": "stb1"} # self.checksample(**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.checksample(**err44) # stb join tdSql.query("select sample( stb1.c1 , 1 ) from stb1, 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.checksample(**err45) # interval tdSql.query("select sample( c1 , 1 ) from t1 where ts>0 and ts < now interval(1h) fill(next)") err46 = { "table_expr": "t1", "condition": "group by c6" } # self.checksample(**err46) # group by normal col err49 = {"k": "2021-01-01 00:00:00.000"} self.checksample(**err49) # k: timestamp err50 = {"k": False} self.checksample(**err50) # k: False err51 = {"k": "%"} self.checksample(**err51) # k: special char err52 = {"k": ""} self.checksample(**err52) # k: "" err53 = {"k": None} self.checksample(**err53) # k: None err54 = {"k": "NULL"} self.checksample(**err54) # k: null err55 = {"k": "binary(4)"} self.checksample(**err55) # k: string err56 = {"k": "c1"} self.checksample(**err56) # k: sring，col name err57 = {"col": "c1, 1, c2"} self.checksample(**err57) # form: sample(col1, k1, col2, k2) err58 = {"col": "c1 cc1"} self.checksample(**err58) # form: sample(col newname, k) err59 = {"k": "'1'"} # self.checksample(**err59) # formL sample(colm, "1") err60 = {"k": "-1-(-2)"} # self.checksample(**err60) # formL sample(colm, -1-2) err61 = {"k": 1001} self.checksample(**err61) # k: right out of [1, 1000] err62 = {"k": -1} self.checksample(**err62) # k: negative number err63 = {"k": 0} self.checksample(**err63) # k: 0 err64 = {"k": 2**63-1} self.checksample(**err64) # k: max(bigint) err65 = {"k": 1-2**63} # self.checksample(**err65) # k: min(bigint) err66 = {"k": -2**63} self.checksample(**err66) # k: NULL err67 = {"k": 0.999999} self.checksample(**err67) # k: left out of [1, 1000] pass def sample_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 sample_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 check_sample(self , sample_query , origin_query ): tdSql.query(origin_query) origin_datas = tdSql.queryResult tdSql.query(sample_query) sample_datas = tdSql.queryResult status = True for ind , sample_data in enumerate(sample_datas): if sample_data not in origin_datas: status = False if status: tdLog.info(" sample data is in datas groups ,successed sql is : %s" % sample_query ) else: tdLog.exit(" sample data is not in datas groups ,failed sql is : %s" % sample_query ) def basic_sample_query(self): tdSql.execute(" drop database if exists db ") tdSql.execute(" create database if not exists db duration 300 ") tdSql.execute(" use db ") 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} )') 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 ) ''' ) # basic query for sample # params test for all tdSql.error(" select sample(c1,c1) from t1 ") tdSql.error(" select sample(c1,now) from t1 ") tdSql.error(" select sample(c1,tbname) from t1 ") tdSql.error(" select sample(c1,ts) from t1 ") tdSql.error(" select sample(c1,false) from t1 ") tdSql.query(" select sample(123,1) from t1 ") tdSql.query(" select sample(c1,2) from t1 ") tdSql.checkRows(2) tdSql.query(" select sample(c1,10) from t1 ") tdSql.checkRows(9) tdSql.query(" select sample(c8,10) from t1 ") tdSql.checkRows(9) tdSql.query(" select sample(c1,999) from t1 ") tdSql.checkRows(9) tdSql.query(" select sample(c1,1000) from t1 ") tdSql.checkRows(9) tdSql.query(" select sample(c8,1000) from t1 ") tdSql.checkRows(9) tdSql.error(" select sample(c1,-1) from t1 ") # bug need fix # tdSql.query("select sample(c1 ,2) , 123 from stb1;") # all type support tdSql.query(" select sample(c1 , 20 ) from ct4 ") tdSql.checkRows(9) tdSql.query(" select sample(c2 , 20 ) from ct4 ") tdSql.checkRows(9) tdSql.query(" select sample(c3 , 20 ) from ct4 ") tdSql.checkRows(9) tdSql.query(" select sample(c4 , 20 ) from ct4 ") tdSql.checkRows(9) tdSql.query(" select sample(c5 , 20 ) from ct4 ") tdSql.checkRows(9) tdSql.query(" select sample(c6 , 20 ) from ct4 ") tdSql.checkRows(9) tdSql.query(" select sample(c7 , 20 ) from ct4 ") tdSql.checkRows(9) tdSql.query(" select sample(c8 , 20 ) from ct4 ") tdSql.checkRows(9) tdSql.query(" select sample(c9 , 20 ) from ct4 ") tdSql.checkRows(9) tdSql.query(" select sample(c10 , 20 ) from ct4 ") tdSql.checkRows(9) # tdSql.query(" select sample(t1 , 20 ) from ct1 ") # tdSql.checkRows(13) # filter data tdSql.query(" select sample(c1, 20 ) from t1 where c1 is null ") tdSql.checkRows(0) tdSql.query(" select sample(c1, 20 ) from t1 where c1 =6 ") tdSql.checkRows(1) tdSql.query(" select sample(c1, 20 ) from t1 where c1 > 6 ") tdSql.checkRows(3) self.check_sample("select sample(c1, 20 ) from t1 where c1 > 6" , "select c1 from t1 where c1 > 6") tdSql.query(" select sample( c1 , 1 ) from t1 where c1 in (0, 1,2) ") tdSql.checkRows(1) tdSql.query("select sample( c1 ,3 ) from t1 where c1 between 1 and 10 ") tdSql.checkRows(3) self.check_sample("select sample( c1 ,3 ) from t1 where c1 between 1 and 10" ,"select c1 from t1 where c1 between 1 and 10") tdSql.query("select sample(c1,2) ,c2,c3 ,c5 from stb1") tdSql.checkRows(2) tdSql.checkCols(4) self.check_sample("select sample( c1 ,3 ),c2,c3,c4,c5 from t1 where c1 between 1 and 10" ,"select c1,c2,c3,c4,c5 from t1 where c1 between 1 and 10") self.check_sample("select sample( c1 ,3 ),c2,c3,c4,c5 from stb1 where c1 between 1 and 10" ,"select c1,c2,c3,c4,c5 from stb1 where c1 between 1 and 10") self.check_sample("select sample( c1 ,3 ),t1 from stb1 where c1 between 1 and 10" ,"select c1,t1 from stb1 where c1 between 1 and 10") # join tdSql.query("select sample( ct4.c1 , 1 ) from ct1, ct4 where ct4.ts=ct1.ts") # partition by tbname tdSql.query("select sample(c1,2) from stb1 partition by tbname") tdSql.checkRows(4) self.check_sample("select sample(c1,2) from stb1 partition by tbname" , "select c1 from stb1 partition by tbname") # nest query tdSql.query("select sample(c1,2) from (select c1 from t1); ") tdSql.checkRows(2) # union all tdSql.query("select sample(c1,2) from t1 union all select sample(c1,3) from t1") tdSql.checkRows(5) # fill interval # not support mix with other function tdSql.error("select top(c1,2) , sample(c1,2) from ct1") tdSql.error("select max(c1) , sample(c1,2) from ct1") tdSql.query("select c1 , sample(c1,2) from ct1") # bug for mix with scalar tdSql.query("select 123 , sample(c1,100) from ct1") tdSql.query("select sample(c1,100)+2 from ct1") tdSql.query("select abs(sample(c1,100)) from ct1") # support stable and tbname tdSql.query("select tbname ,sample(c1,2) from stb1 partition by tbname order by tbname") tdSql.checkRows(4) tdSql.checkData(0,0,'ct1') tdSql.checkData(3,0,'ct4') # # bug need fix # tdSql.query(" select tbname ,c1 ,t1, sample(c1,2) from stb1 partition by tbname order by tbname ") # tdSql.checkRows(4) # tdSql.checkData(0,0,'ct1') # tdSql.checkData(3,0,'ct4') # tdSql.checkData(0,2,1) # tdSql.checkData(3,2,4) tdSql.query(" select tbname ,c1 ,t1, sample(c1,2) from stb1 partition by t1 order by t1 ") tdSql.checkRows(4) tdSql.checkData(0,0,'ct1') tdSql.checkData(3,0,'ct4') tdSql.checkData(0,2,1) tdSql.checkData(3,2,4) # bug need fix # tdSql.query(" select tbname ,c1 ,t1, sample(c1,2) from stb1 partition by c1 order by c1 ") # tdSql.checkRows(21) # bug need fix # tdSql.query(" select sample(c1,2) from stb1 partition by c1 ") # tdSql.checkRows(21) def sample_test_run(self) : tdLog.printNoPrefix("==========support sample function==========") tbnum = 10 nowtime = int(round(time.time() * 1000)) per_table_rows = 10 self.sample_test_table(tbnum) tdLog.printNoPrefix("######## no data test:") self.sample_current_query() self.sample_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.sample_current_query() self.sample_error_query() tdLog.printNoPrefix("######## insert data in the range near the max(bigint/double):") # self.sample_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.sample_current_query() # self.sample_error_query() tdLog.printNoPrefix("######## insert data in the range near the min(bigint/double):") # self.sample_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.sample_current_query() # self.sample_error_query() tdLog.printNoPrefix("######## insert data without NULL data test:") self.sample_test_table(tbnum) self.sample_test_data(tbnum, per_table_rows, nowtime) self.sample_current_query() self.sample_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.sample_current_query() self.sample_error_query() tdLog.printNoPrefix("######## check after WAL test:") tdSql.query("show dnodes") index = tdSql.getData(0, 0) tdDnodes.stop(index) tdDnodes.start(index) self.sample_current_query() self.sample_error_query() self.basic_sample_query() def run(self): import traceback try: # run in develop branch self.sample_test_run() 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())