################################################################### # 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 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(f"start to execute {__file__}") tdSql.init(conn.cursor(), logSql) def getBuildPath(self) -> str: selfPath = os.path.dirname(os.path.realpath(__file__)) if ("community" in selfPath): projPath = selfPath[:selfPath.find("community")] else: projPath = selfPath[:selfPath.find("tests")] for root, dirs, files in os.walk(projPath): if ("taosd" in files): rootRealPath = os.path.dirname(os.path.realpath(root)) if ("packaging" not in rootRealPath): buildPath = root[:len(root) - len("/debug/build/bin")] break return buildPath def getCfgDir(self) -> str: selfPath = os.path.dirname(os.path.realpath(__file__)) if ("community" in selfPath): cfgDir = self.getBuildPath() + "/community/sim/dnode1/cfg" else: cfgDir = self.getBuildPath() + "/sim/dnode1/cfg" return cfgDir def getCfgFile(self) -> str: return self.getCfgDir()+"/taos.cfg" def td3690(self): tdLog.printNoPrefix("==========TD-3690==========") tdSql.prepare() tdSql.execute("show variables") res_off = tdSql.cursor.fetchall() resList = np.array(res_off) index = np.where(resList == "offlineThreshold") index_value = np.dstack((index[0])).squeeze() tdSql.query("show variables") tdSql.checkData(index_value, 1, 864000) def td4082(self): tdLog.printNoPrefix("==========TD-4082==========") tdSql.prepare() cfgfile = self.getCfgFile() max_compressMsgSize = 100000000 tdSql.execute("show variables") res_com = tdSql.cursor.fetchall() rescomlist = np.array(res_com) cpms_index = np.where(rescomlist == "compressMsgSize") index_value = np.dstack((cpms_index[0])).squeeze() tdSql.query("show variables") tdSql.checkData(index_value, 1, -1) tdSql.query("show dnodes") index = tdSql.getData(0, 0) tdDnodes.stop(index) cmd = f"sed -i '$a compressMSgSize {max_compressMsgSize}' {cfgfile} " try: _ = subprocess.check_output(cmd, shell=True).decode("utf-8") except Exception as e: raise e tdDnodes.start(index) tdSql.query("show variables") tdSql.checkData(index_value, 1, 100000000) tdDnodes.stop(index) cmd = f"sed -i '$s/{max_compressMsgSize}/{max_compressMsgSize+10}/g' {cfgfile} " try: _ = subprocess.check_output(cmd, shell=True).decode("utf-8") except Exception as e: raise e tdDnodes.start(index) tdSql.query("show variables") tdSql.checkData(index_value, 1, -1) tdDnodes.stop(index) cmd = f"sed -i '$d' {cfgfile}" try: _ = subprocess.check_output(cmd, shell=True).decode("utf-8") except Exception as e: raise e tdDnodes.start(index) def td4097(self): tdLog.printNoPrefix("==========TD-4097==========") tdSql.execute("drop database if exists db") tdSql.execute("drop database if exists db1") tdSql.execute("create database if not exists db keep 3650") tdSql.execute("create database if not exists db1 keep 3650") tdSql.execute("create database if not exists new keep 3650") tdSql.execute("create database if not exists private keep 3650") tdSql.execute("create database if not exists db2 keep 3650") tdSql.execute("create stable db.stb1 (ts timestamp, c1 int) tags(t1 int)") tdSql.execute("create stable db.stb2 (ts timestamp, c1 int) tags(t1 int)") tdSql.execute("create stable db1.stb3 (ts timestamp, c1 int) tags(t1 int)") tdSql.execute("create table db.t10 using db.stb1 tags(1)") tdSql.execute("create table db.t11 using db.stb1 tags(2)") tdSql.execute("create table db.t20 using db.stb2 tags(3)") tdSql.execute("create table db1.t30 using db1.stb3 tags(4)") # tdLog.printNoPrefix("==========TD-4097==========") # 插入数据，然后进行show create 操作 # p1 不进入指定数据库 tdSql.query("show create database db") tdSql.checkRows(1) tdSql.query("show create database db1") tdSql.checkRows(1) tdSql.query("show create database db2") tdSql.checkRows(1) tdSql.query("show create database new") tdSql.checkRows(1) tdSql.query("show create database private") tdSql.checkRows(1) tdSql.error("show create database ") tdSql.error("show create databases db ") tdSql.error("show create database db.stb1") tdSql.error("show create database db0") tdSql.error("show create database db db1") tdSql.error("show create database db, db1") tdSql.error("show create database stb1") tdSql.error("show create database * ") tdSql.query("show create stable db.stb1") tdSql.checkRows(1) tdSql.error("show create stable db.t10") tdSql.error("show create stable db.stb0") tdSql.error("show create stable stb1") tdSql.error("show create stable ") tdSql.error("show create stable *") tdSql.error("show create stable db.stb1 db.stb2") tdSql.error("show create stable db.stb1, db.stb2") tdSql.query("show create table db.stb1") tdSql.checkRows(1) tdSql.query("show create table db.t10") tdSql.checkRows(1) tdSql.error("show create table db.stb0") tdSql.error("show create table stb1") tdSql.error("show create table ") tdSql.error("show create table *") tdSql.error("show create table db.stb1 db.stb2") tdSql.error("show create table db.stb1, db.stb2") # p2 进入指定数据库 tdSql.execute("use db") tdSql.query("show create database db") tdSql.checkRows(1) tdSql.query("show create database db1") tdSql.checkRows(1) tdSql.error("show create database ") tdSql.error("show create databases db ") tdSql.error("show create database db.stb1") tdSql.error("show create database db0") tdSql.error("show create database db db1") tdSql.error("show create database db, db1") tdSql.error("show create database stb1") tdSql.error("show create database * ") tdSql.query("show create stable db.stb1") tdSql.checkRows(1) tdSql.query("show create stable stb1") tdSql.checkRows(1) tdSql.query("show create stable db1.stb3") tdSql.checkRows(1) tdSql.error("show create stable db.t10") tdSql.error("show create stable db") tdSql.error("show create stable t10") tdSql.error("show create stable db.stb0") tdSql.error("show create stables stb1") tdSql.error("show create stable ") tdSql.error("show create stable *") tdSql.error("show create stable db.stb1 db.stb2") tdSql.error("show create stable stb1 stb2") tdSql.error("show create stable db.stb1, db.stb2") tdSql.error("show create stable stb1, stb2") tdSql.query("show create table db.stb1") tdSql.checkRows(1) tdSql.query("show create table stb1") tdSql.checkRows(1) tdSql.query("show create table db.t10") tdSql.checkRows(1) tdSql.query("show create table t10") tdSql.checkRows(1) tdSql.query("show create table db1.t30") tdSql.checkRows(1) tdSql.error("show create table t30") tdSql.error("show create table db.stb0") tdSql.error("show create table db.t0") tdSql.error("show create table db") tdSql.error("show create tables stb1") tdSql.error("show create tables t10") tdSql.error("show create table ") tdSql.error("show create table *") tdSql.error("show create table db.stb1 db.stb2") tdSql.error("show create table db.t11 db.t10") tdSql.error("show create table db.stb1, db.stb2") tdSql.error("show create table db.t11, db.t10") tdSql.error("show create table stb1 stb2") tdSql.error("show create table t11 t10") tdSql.error("show create table stb1, stb2") tdSql.error("show create table t11, t10") # p3 删库删表后进行查询 tdSql.execute("drop table if exists t11") tdSql.error("show create table t11") tdSql.error("show create table db.t11") tdSql.query("show create stable stb1") tdSql.checkRows(1) tdSql.query("show create table t10") tdSql.checkRows(1) tdSql.execute("drop stable if exists stb2") tdSql.error("show create table stb2") tdSql.error("show create table db.stb2") tdSql.error("show create stable stb2") tdSql.error("show create stable db.stb2") tdSql.error("show create stable db.t20") tdSql.query("show create database db") tdSql.checkRows(1) tdSql.query("show create stable db.stb1") tdSql.checkRows(1) tdSql.execute("drop database if exists db1") tdSql.error("show create database db1") tdSql.error("show create stable db1.t31") tdSql.error("show create stable db1.stb3") tdSql.query("show create database db") tdSql.checkRows(1) tdSql.query("show create stable db.stb1") tdSql.checkRows(1) tdSql.execute("drop database if exists db") tdSql.execute("drop database if exists db1") tdSql.execute("drop database if exists new") tdSql.execute("drop database if exists db2") tdSql.execute("drop database if exists private") def td4153(self): tdLog.printNoPrefix("==========TD-4153==========") pass def td4288(self): tdLog.printNoPrefix("==========TD-4288==========") # keep ~ [days,365000] tdSql.execute("drop database if exists db") tdSql.execute("create database if not exists db") tdSql.execute("show variables") res_kp = tdSql.cursor.fetchall() resList = np.array(res_kp) keep_index = np.where(resList == "keep") index_value = np.dstack((keep_index[0])).squeeze() tdSql.query("show variables") tdSql.checkData(index_value, 1, 3650) tdSql.query("show databases") selfPath = os.path.dirname(os.path.realpath(__file__)) if ("community" in selfPath): tdSql.checkData(0, 7, "3650,3650,3650") else: tdSql.checkData(0, 7, 3650) days = tdSql.getData(0, 6) tdSql.error("alter database db keep 3650001") tdSql.error("alter database db keep 9") tdSql.error("alter database db keep 0b") tdSql.error("alter database db keep 3650,9,36500") tdSql.error("alter database db keep 3650,3650,365001") tdSql.error("alter database db keep 36500,a,36500") tdSql.error("alter database db keep (36500,3650,3650)") tdSql.error("alter database db keep [36500,3650,36500]") tdSql.error("alter database db keep 36500,0xff,3650") tdSql.error("alter database db keep 36500,0o365,3650") tdSql.error("alter database db keep 36500,0A3Ch,3650") tdSql.error("alter database db keep") tdSql.error("alter database db keep0 36500") tdSql.execute("alter database db keep 36500") tdSql.query("show databases") if ("community" in selfPath): tdSql.checkData(0, 7, "36500,36500,36500") else: tdSql.checkData(0, 7, 36500) tdSql.execute("drop database if exists db") tdSql.execute("create database if not exists db1") tdSql.query("show databases") if ("community" in selfPath): tdSql.checkData(0, 7, "3650,3650,3650") else: tdSql.checkData(0, 7, 3650) tdSql.query("show variables") tdSql.checkData(index_value, 1, 3650) tdSql.execute("alter database db1 keep 365") tdSql.execute("drop database if exists db1") pass def td4724(self): tdLog.printNoPrefix("==========TD-4724==========") cfgfile = self.getCfgFile() minTablesPerVnode = 5 maxTablesPerVnode = 10 maxVgroupsPerDb = 100 tdSql.query("show dnodes") index = tdSql.getData(0, 0) tdDnodes.stop(index) vnode_cmd = f"sed -i '$a maxVgroupsPerDb {maxVgroupsPerDb}' {cfgfile} " min_cmd = f"sed -i '$a minTablesPerVnode {minTablesPerVnode}' {cfgfile} " max_cmd = f"sed -i '$a maxTablesPerVnode {maxTablesPerVnode}' {cfgfile} " try: _ = subprocess.check_output(vnode_cmd, shell=True).decode("utf-8") _ = subprocess.check_output(min_cmd, shell=True).decode("utf-8") _ = subprocess.check_output(max_cmd, shell=True).decode("utf-8") except Exception as e: raise e tdDnodes.start(index) 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) tags(t1 int)") insert_sql = "insert into " for i in range(100): tdSql.execute(f"create table db.t1{i} using db.stb1 tags({i})") insert_sql += f" t1{i} values({1604298064000 + i*1000}, {i})" tdSql.query("show dnodes") vnode_count = tdSql.getData(0, 2) if vnode_count <= 1: tdLog.exit("vnode is less than 2") tdSql.execute(insert_sql) tdDnodes.stop(index) cmd = f"sed -i '$d' {cfgfile}" try: _ = subprocess.check_output(cmd, shell=True).decode("utf-8") _ = subprocess.check_output(cmd, shell=True).decode("utf-8") _ = subprocess.check_output(cmd, shell=True).decode("utf-8") except Exception as e: raise e tdDnodes.start(index) pass def td4889(self): tdLog.printNoPrefix("==========TD-4889==========") cfg = { 'minRowsPerFileBlock': '10', 'maxRowsPerFileBlock': '200', 'minRows': '10', 'maxRows': '200', 'maxVgroupsPerDb': '100', 'maxTablesPerVnode': '1200', } tdSql.query("show dnodes") dnode_index = tdSql.getData(0,0) tdDnodes.stop(dnode_index) tdDnodes.deploy(dnode_index, cfg) tdDnodes.start(dnode_index) tdSql.execute("drop database if exists db") tdSql.execute("create database if not exists db keep 3650 blocks 3 minrows 10 maxrows 200") tdSql.execute("use db") tdSql.execute("create stable db.stb1 (ts timestamp, c1 int) tags(t1 int)") nowtime = int(round(time.time() * 1000)) for i in range(1000): tdSql.execute(f"create table db.t1{i} using db.stb1 tags({i})") sql = f"insert into db.t1{i} values" for j in range(260): sql += f"({nowtime-1000*i-j}, {i+j})" # tdSql.execute(f"insert into db.t1{i} values (now-100d, {i+j})") tdSql.execute(sql) # tdDnodes.stop(dnode_index) # tdDnodes.start(dnode_index) tdSql.query("show vgroups") index = tdSql.getData(0,0) tdSql.checkData(0, 6, 0) tdSql.execute(f"compact vnodes in({index})") start_time = time.time() while True: tdSql.query("show vgroups") if tdSql.getData(0, 6) != 0: tdLog.printNoPrefix("show vgroups row:0 col:6 data:1 == expect:1") break run_time = time.time()-start_time if run_time > 3: tdLog.exit("compacting not occured") # time.sleep(0.1) pass def td5168insert(self): 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 float, c2 float, c3 double, c4 double) tags(t1 int)") tdSql.execute("create table db.t1 using db.stb1 tags(1)") for i in range(5): c1 = 1001.11 + i*0.1 c2 = 1001.11 + i*0.1 + 1*0.01 c3 = 1001.11 + i*0.1 + 2*0.01 c4 = 1001.11 + i*0.1 + 3*0.01 tdSql.execute(f"insert into db.t1 values ('2021-07-01 08:00:0{i}.000', {c1}, {c2}, {c3}, {c4})") # tdSql.execute("insert into db.t1 values ('2021-07-01 08:00:00.000', 1001.11, 1001.12, 1001.13, 1001.14)") # tdSql.execute("insert into db.t1 values ('2021-07-01 08:00:01.000', 1001.21, 1001.22, 1001.23, 1001.24)") # tdSql.execute("insert into db.t1 values ('2021-07-01 08:00:02.000', 1001.31, 1001.32, 1001.33, 1001.34)") # tdSql.execute("insert into db.t1 values ('2021-07-01 08:00:03.000', 1001.41, 1001.42, 1001.43, 1001.44)") # tdSql.execute("insert into db.t1 values ('2021-07-01 08:00:04.000', 1001.51, 1001.52, 1001.53, 1001.54)") # for i in range(1000000): for i in range(10000): random1 = random.uniform(1000,1001) random2 = random.uniform(1000,1001) random3 = random.uniform(1000,1001) random4 = random.uniform(1000,1001) tdSql.execute(f"insert into db.t1 values (now+{i}a, {random1}, {random2},{random3}, {random4})") pass def td5168(self): tdLog.printNoPrefix("==========TD-5168==========") # 插入小范围内的随机数 tdLog.printNoPrefix("=====step0: 默认情况下插入数据========") self.td5168insert() # 获取五个时间点的数据作为基准数值,未压缩情况下精准匹配 for i in range(5): tdSql.query(f"select * from db.t1 where ts='2021-07-01 08:00:0{i}.000' ") # c1, c2, c3, c4 = tdSql.getData(0, 1), tdSql.getData(0, 2), tdSql.getData(0, 3), tdSql.getData(0, 4) for j in range(4): locals()["f" + str(j) + str(i)] = tdSql.getData(0, j+1) print(f"f{j}{i}:", locals()["f" + str(j) + str(i)]) tdSql.checkData(0, j+1, locals()["f" + str(j) + str(i)]) # tdSql.query("select * from db.t1 limit 100,1") # f10, f11, f12, f13 = tdSql.getData(0,1), tdSql.getData(0,2), tdSql.getData(0,3), tdSql.getData(0,4) # # tdSql.query("select * from db.t1 limit 1000,1") # f20, f21, f22, f23 = tdSql.getData(0,1), tdSql.getData(0,2), tdSql.getData(0,3), tdSql.getData(0,4) # # tdSql.query("select * from db.t1 limit 10000,1") # f30, f31, f32, f33 = tdSql.getData(0,1), tdSql.getData(0,2), tdSql.getData(0,3), tdSql.getData(0,4) # # tdSql.query("select * from db.t1 limit 100000,1") # f40, f41, f42, f43 = tdSql.getData(0,1), tdSql.getData(0,2), tdSql.getData(0,3), tdSql.getData(0,4) # # tdSql.query("select * from db.t1 limit 1000000,1") # f50, f51, f52, f53 = tdSql.getData(0,1), tdSql.getData(0,2), tdSql.getData(0,3), tdSql.getData(0,4) # 关闭服务并获取未开启压缩情况下的数据容量 tdSql.query("show dnodes") index = tdSql.getData(0, 0) tdDnodes.stop(index) cfgdir = self.getCfgDir() cfgfile = self.getCfgFile() lossy_cfg_cmd=f"grep lossyColumns {cfgfile}|awk '{{print $2}}'" data_size_cmd = f"du -s {cfgdir}/../data/vnode/ | awk '{{print $1}}'" dsize_init = int(subprocess.check_output(data_size_cmd,shell=True).decode("utf-8")) lossy_args = subprocess.check_output(lossy_cfg_cmd, shell=True).decode("utf-8") tdLog.printNoPrefix(f"close the lossyColumns，data size is: {dsize_init};the lossyColumns line is: {lossy_args}") ################################################### float_lossy = "float" double_lossy = "double" float_double_lossy = "float|double" no_loosy = "" double_precision_cmd = f"sed -i '$a dPrecision 0.000001' {cfgfile}" _ = subprocess.check_output(double_precision_cmd, shell=True).decode("utf-8") lossy_float_cmd = f"sed -i '$a lossyColumns {float_lossy}' {cfgfile} " lossy_double_cmd = f"sed -i '$d' {cfgfile} && sed -i '$a lossyColumns {double_lossy}' {cfgfile} " lossy_float_double_cmd = f"sed -i '$d' {cfgfile} && sed -i '$a lossyColumns {float_double_lossy}' {cfgfile} " lossy_no_cmd = f"sed -i '$a lossyColumns {no_loosy}' {cfgfile} " ################################################### # 开启有损压缩，参数float，并启动服务插入数据 tdLog.printNoPrefix("=====step1: lossyColumns设置为float========") lossy_float = subprocess.check_output(lossy_float_cmd, shell=True).decode("utf-8") tdDnodes.start(index) self.td5168insert() # 查询前面所述5个时间数据并与基准数值进行比较 for i in range(5): tdSql.query(f"select * from db.t1 where ts='2021-07-01 08:00:0{i}.000' ") # c1, c2, c3, c4 = tdSql.getData(0, 1), tdSql.getData(0, 2), tdSql.getData(0, 3), tdSql.getData(0, 4) for j in range(4): # locals()["f" + str(j) + str(i)] = tdSql.getData(0, j+1) # print(f"f{j}{i}:", locals()["f" + str(j) + str(i)]) tdSql.checkData(0, j+1, locals()["f" + str(j) + str(i)]) # 关闭服务并获取压缩参数为float情况下的数据容量 tdDnodes.stop(index) dsize_float = int(subprocess.check_output(data_size_cmd,shell=True).decode("utf-8")) lossy_args = subprocess.check_output(lossy_cfg_cmd, shell=True).decode("utf-8") tdLog.printNoPrefix(f"open the lossyColumns， data size is：{dsize_float};the lossyColumns line is: {lossy_args}") # 修改有损压缩，参数double，并启动服务 tdLog.printNoPrefix("=====step2: lossyColumns设置为double========") lossy_double = subprocess.check_output(lossy_double_cmd, shell=True).decode("utf-8") tdDnodes.start(index) self.td5168insert() # 查询前面所述5个时间数据并与基准数值进行比较 for i in range(5): tdSql.query(f"select * from db.t1 where ts='2021-07-01 08:00:0{i}.000' ") for j in range(4): tdSql.checkData(0, j+1, locals()["f" + str(j) + str(i)]) # 关闭服务并获取压缩参数为double情况下的数据容量 tdDnodes.stop(index) dsize_double = int(subprocess.check_output(data_size_cmd, shell=True).decode("utf-8")) lossy_args = subprocess.check_output(lossy_cfg_cmd, shell=True).decode("utf-8") tdLog.printNoPrefix(f"open the lossyColumns, data size is：{dsize_double};the lossyColumns line is: {lossy_args}") # 修改有损压缩，参数 float&&double ，并启动服务 tdLog.printNoPrefix("=====step3: lossyColumns设置为 float&&double ========") lossy_float_double = subprocess.check_output(lossy_float_double_cmd, shell=True).decode("utf-8") tdDnodes.start(index) self.td5168insert() # 查询前面所述5个时间数据并与基准数值进行比较 for i in range(5): tdSql.query(f"select * from db.t1 where ts='2021-07-01 08:00:0{i}.000' ") for j in range(4): tdSql.checkData(0, j+1, locals()["f" + str(j) + str(i)]) # 关闭服务并获取压缩参数为 float&&double 情况下的数据容量 tdDnodes.stop(index) dsize_float_double = int(subprocess.check_output(data_size_cmd, shell=True).decode("utf-8")) lossy_args = subprocess.check_output(lossy_cfg_cmd, shell=True).decode("utf-8") tdLog.printNoPrefix(f"open the lossyColumns， data size is：{dsize_float_double};the lossyColumns line is: {lossy_args}") if not ((dsize_float_double < dsize_init) and (dsize_double < dsize_init) and (dsize_float < dsize_init)) : tdLog.printNoPrefix(f"When lossyColumns value is float, data size is: {dsize_float}") tdLog.printNoPrefix(f"When lossyColumns value is double, data size is: {dsize_double}") tdLog.printNoPrefix(f"When lossyColumns value is float and double, data size is: {dsize_float_double}") tdLog.printNoPrefix(f"When lossyColumns is closed, data size is: {dsize_init}") tdLog.exit("压缩未生效") else: tdLog.printNoPrefix(f"When lossyColumns value is float, data size is: {dsize_float}") tdLog.printNoPrefix(f"When lossyColumns value is double, data size is: {dsize_double}") tdLog.printNoPrefix(f"When lossyColumns value is float and double, data size is: {dsize_float_double}") tdLog.printNoPrefix(f"When lossyColumns is closed, data size is: {dsize_init}") tdLog.printNoPrefix("压缩生效") pass def td5433(self): tdLog.printNoPrefix("==========TD-5433==========") 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) tags(t0 tinyint, t1 int)") tdSql.execute("create stable db.stb2 (ts timestamp, c1 int) tags(t0 binary(16), t1 binary(16))") numtab=20000 for i in range(numtab): sql = f"create table db.t{i} using db.stb1 tags({i%128}, {100+i})" tdSql.execute(sql) tdSql.execute(f"insert into db.t{i} values (now-10d, {i})") tdSql.execute(f"insert into db.t{i} values (now-9d, {i*2})") tdSql.execute(f"insert into db.t{i} values (now-8d, {i*3})") tdSql.execute("create table db.t01 using db.stb2 tags('1', '100')") tdSql.execute("create table db.t02 using db.stb2 tags('2', '200')") tdSql.execute("create table db.t03 using db.stb2 tags('3', '300')") tdSql.execute("create table db.t04 using db.stb2 tags('4', '400')") tdSql.execute("create table db.t05 using db.stb2 tags('5', '500')") tdSql.query("select distinct t1 from stb1 where t1 != '150'") tdSql.checkRows(numtab-1) tdSql.query("select distinct t1 from stb1 where t1 != 150") tdSql.checkRows(numtab-1) tdSql.query("select distinct t1 from stb1 where t1 = 150") tdSql.checkRows(1) tdSql.query("select distinct t1 from stb1 where t1 = '150'") tdSql.checkRows(1) tdSql.query("select distinct t1 from stb1") tdSql.checkRows(numtab) tdSql.query("select distinct t0 from stb1 where t0 != '2'") tdSql.checkRows(127) tdSql.query("select distinct t0 from stb1 where t0 != 2") tdSql.checkRows(127) tdSql.query("select distinct t0 from stb1 where t0 = 2") tdSql.checkRows(1) tdSql.query("select distinct t0 from stb1 where t0 = '2'") tdSql.checkRows(1) tdSql.query("select distinct t0 from stb1") tdSql.checkRows(128) tdSql.query("select distinct t1 from stb2 where t1 != '200'") tdSql.checkRows(4) tdSql.query("select distinct t1 from stb2 where t1 != 200") tdSql.checkRows(4) tdSql.query("select distinct t1 from stb2 where t1 = 200") tdSql.checkRows(1) tdSql.query("select distinct t1 from stb2 where t1 = '200'") tdSql.checkRows(1) tdSql.query("select distinct t1 from stb2") tdSql.checkRows(5) tdSql.query("select distinct t0 from stb2 where t0 != '2'") tdSql.checkRows(4) tdSql.query("select distinct t0 from stb2 where t0 != 2") tdSql.checkRows(4) tdSql.query("select distinct t0 from stb2 where t0 = 2") tdSql.checkRows(1) tdSql.query("select distinct t0 from stb2 where t0 = '2'") tdSql.checkRows(1) tdSql.query("select distinct t0 from stb2") tdSql.checkRows(5) pass def td5798(self): tdLog.printNoPrefix("==========TD-5798 + TD-5810==========") 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 int) tags(t0 tinyint, t1 int, t2 int)") tdSql.execute("create stable db.stb2 (ts timestamp, c2 int, c3 binary(16)) tags(t2 binary(16), t3 binary(16), t4 int)") maxRemainderNum=7 tbnum=101 for i in range(tbnum-1): sql = f"create table db.t{i} using db.stb1 tags({i%maxRemainderNum}, {(i-1)%maxRemainderNum}, {i%2})" tdSql.execute(sql) tdSql.execute(f"insert into db.t{i} values (now-10d, {i}, {i%3})") tdSql.execute(f"insert into db.t{i} values (now-9d, {i}, {(i-1)%3})") tdSql.execute(f"insert into db.t{i} values (now-8d, {i}, {(i-2)%3})") tdSql.execute(f"insert into db.t{i} (ts )values (now-7d)") tdSql.execute(f"create table db.t0{i} using db.stb2 tags('{i%maxRemainderNum}', '{(i-1)%maxRemainderNum}', {i%3})") tdSql.execute(f"insert into db.t0{i} values (now-10d, {i}, '{(i+1)%3}')") tdSql.execute(f"insert into db.t0{i} values (now-9d, {i}, '{(i+2)%3}')") tdSql.execute(f"insert into db.t0{i} values (now-8d, {i}, '{(i)%3}')") tdSql.execute(f"insert into db.t0{i} (ts )values (now-7d)") tdSql.execute("create table db.t100num using db.stb1 tags(null, null, null)") tdSql.execute("create table db.t0100num using db.stb2 tags(null, null, null)") tdSql.execute(f"insert into db.t100num values (now-10d, {tbnum-1}, 1)") tdSql.execute(f"insert into db.t100num values (now-9d, {tbnum-1}, 0)") tdSql.execute(f"insert into db.t100num values (now-8d, {tbnum-1}, 2)") tdSql.execute(f"insert into db.t100num (ts )values (now-7d)") tdSql.execute(f"insert into db.t0100num values (now-10d, {tbnum-1}, 1)") tdSql.execute(f"insert into db.t0100num values (now-9d, {tbnum-1}, 0)") tdSql.execute(f"insert into db.t0100num values (now-8d, {tbnum-1}, 2)") tdSql.execute(f"insert into db.t0100num (ts )values (now-7d)") #========== TD-5810 suport distinct multi-data-coloumn ========== tdSql.query(f"select distinct c1 from stb1 where c1 <{tbnum}") tdSql.checkRows(tbnum) tdSql.query(f"select distinct c2 from stb1") tdSql.checkRows(4) tdSql.query(f"select distinct c1,c2 from stb1 where c1 <{tbnum}") tdSql.checkRows(tbnum*3) tdSql.query(f"select distinct c1,c1 from stb1 where c1 <{tbnum}") tdSql.checkRows(tbnum) tdSql.query(f"select distinct c1,c2 from stb1 where c1 <{tbnum} limit 3") tdSql.checkRows(3) tdSql.query(f"select distinct c1,c2 from stb1 where c1 <{tbnum} limit 3 offset {tbnum*3-2}") tdSql.checkRows(2) tdSql.query(f"select distinct c1 from t1 where c1 <{tbnum}") tdSql.checkRows(1) tdSql.query(f"select distinct c2 from t1") tdSql.checkRows(4) tdSql.query(f"select distinct c1,c2 from t1 where c1 <{tbnum}") tdSql.checkRows(3) tdSql.query(f"select distinct c1,c1 from t1 ") tdSql.checkRows(2) tdSql.query(f"select distinct c1,c1 from t1 where c1 <{tbnum}") tdSql.checkRows(1) tdSql.query(f"select distinct c1,c2 from t1 where c1 <{tbnum} limit 3") tdSql.checkRows(3) tdSql.query(f"select distinct c1,c2 from t1 where c1 <{tbnum} limit 3 offset 2") tdSql.checkRows(1) tdSql.query(f"select distinct c3 from stb2 where c2 <{tbnum} ") tdSql.checkRows(3) tdSql.query(f"select distinct c3, c2 from stb2 where c2 <{tbnum} limit 2") tdSql.checkRows(2) tdSql.error("select distinct c5 from stb1") tdSql.error("select distinct c5 from t1") tdSql.error("select distinct c1 from db.*") tdSql.error("select c2, distinct c1 from stb1") tdSql.error("select c2, distinct c1 from t1") tdSql.error("select distinct c2 from ") tdSql.error("distinct c2 from stb1") tdSql.error("distinct c2 from t1") tdSql.error("select distinct c1, c2, c3 from stb1") tdSql.error("select distinct c1, c2, c3 from t1") tdSql.error("select distinct stb1.c1, stb1.c2, stb2.c2, stb2.c3 from stb1") tdSql.error("select distinct stb1.c1, stb1.c2, stb2.c2, stb2.c3 from t1") tdSql.error("select distinct t1.c1, t1.c2, t2.c1, t2.c2 from t1") tdSql.query(f"select distinct c1 c2, c2 c3 from stb1 where c1 <{tbnum}") tdSql.checkRows(tbnum*3) tdSql.query(f"select distinct c1 c2, c2 c3 from t1 where c1 <{tbnum}") tdSql.checkRows(3) tdSql.error("select distinct c1, c2 from stb1 order by ts") tdSql.error("select distinct c1, c2 from t1 order by ts") tdSql.error("select distinct c1, ts from stb1 group by c2") tdSql.error("select distinct c1, ts from t1 group by c2") tdSql.error("select distinct c1, max(c2) from stb1 ") tdSql.error("select distinct c1, max(c2) from t1 ") tdSql.error("select max(c2), distinct c1 from stb1 ") tdSql.error("select max(c2), distinct c1 from t1 ") tdSql.error("select distinct c1, c2 from stb1 where c1 > 3 group by t0") tdSql.error("select distinct c1, c2 from t1 where c1 > 3 group by t0") tdSql.error("select distinct c1, c2 from stb1 where c1 > 3 interval(1d) ") tdSql.error("select distinct c1, c2 from t1 where c1 > 3 interval(1d) ") tdSql.error("select distinct c1, c2 from stb1 where c1 > 3 interval(1d) fill(next)") tdSql.error("select distinct c1, c2 from t1 where c1 > 3 interval(1d) fill(next)") tdSql.error("select distinct c1, c2 from stb1 where ts > now-10d and ts < now interval(1d) fill(next)") tdSql.error("select distinct c1, c2 from t1 where ts > now-10d and ts < now interval(1d) fill(next)") tdSql.error("select distinct c1, c2 from stb1 where c1 > 3 slimit 1") tdSql.error("select distinct c1, c2 from t1 where c1 > 3 slimit 1") tdSql.query(f"select distinct c1, c2 from stb1 where c1 between {tbnum-2} and {tbnum} ") tdSql.checkRows(6) tdSql.query("select distinct c1, c2 from stb1 where c1 in (1,2,3,4,5)") tdSql.checkRows(15) tdSql.query("select distinct c1, c2 from stb1 where c1 in (100,1000,10000)") tdSql.checkRows(3) tdSql.query(f"select distinct c1,c2 from (select * from stb1 where c1 > {tbnum-2}) ") tdSql.checkRows(3) tdSql.query(f"select distinct c1,c2 from (select * from t1 where c1 < {tbnum}) ") tdSql.checkRows(3) tdSql.query(f"select distinct c1,c2 from (select * from stb1 where t2 !=0 and t2 != 1) ") tdSql.checkRows(0) tdSql.error("select distinct c1, c2 from (select distinct c1, c2 from stb1 where t0 > 2 and t1 < 3) ") tdSql.error("select c1, c2 from (select distinct c1, c2 from stb1 where t0 > 2 and t1 < 3) ") tdSql.query("select distinct c1, c2 from (select c2, c1 from stb1 where c1 > 2 ) where c1 < 4") tdSql.checkRows(3) tdSql.error("select distinct c1, c2 from (select c1 from stb1 where t0 > 2 ) where t1 < 3") tdSql.error("select distinct c1, c2 from (select c2, c1 from stb1 where c1 > 2 order by ts)") # tdSql.error("select distinct c1, c2 from (select c2, c1 from t1 where c1 > 2 order by ts)") tdSql.error("select distinct c1, c2 from (select c2, c1 from stb1 where c1 > 2 group by c1)") # tdSql.error("select distinct c1, c2 from (select max(c1) c1, max(c2) c2 from stb1 group by c1)") # tdSql.error("select distinct c1, c2 from (select max(c1) c1, max(c2) c2 from t1 group by c1)") tdSql.query("select distinct c1, c2 from (select max(c1) c1, max(c2) c2 from stb1 )") tdSql.checkRows(1) tdSql.query("select distinct c1, c2 from (select max(c1) c1, max(c2) c2 from t1 )") tdSql.checkRows(1) tdSql.error("select distinct stb1.c1, stb1.c2 from stb1 , stb2 where stb1.ts=stb2.ts and stb1.t2=stb2.t4") tdSql.error("select distinct t1.c1, t1.c2 from t1 , t2 where t1.ts=t2.ts ") # tdSql.error("select distinct c1, c2 from (select count(c1) c1, count(c2) c2 from stb1 group by ts)") # tdSql.error("select distinct c1, c2 from (select count(c1) c1, count(c2) c2 from t1 group by ts)") #========== TD-5798 suport distinct multi-tags-coloumn ========== tdSql.query("select distinct t1 from stb1") tdSql.checkRows(maxRemainderNum+1) tdSql.query("select distinct t0, t1 from stb1") tdSql.checkRows(maxRemainderNum+1) tdSql.query("select distinct t1, t0 from stb1") tdSql.checkRows(maxRemainderNum+1) tdSql.query("select distinct t1, t2 from stb1") tdSql.checkRows(maxRemainderNum*2+1) tdSql.query("select distinct t0, t1, t2 from stb1") tdSql.checkRows(maxRemainderNum*2+1) tdSql.query("select distinct t0 t1, t1 t2 from stb1") tdSql.checkRows(maxRemainderNum+1) tdSql.query("select distinct t0, t0, t0 from stb1") tdSql.checkRows(maxRemainderNum+1) tdSql.query("select distinct t0, t1 from t1") tdSql.checkRows(1) tdSql.query("select distinct t0, t1 from t100num") tdSql.checkRows(1) tdSql.query("select distinct t3 from stb2") tdSql.checkRows(maxRemainderNum+1) tdSql.query("select distinct t2, t3 from stb2") tdSql.checkRows(maxRemainderNum+1) tdSql.query("select distinct t3, t2 from stb2") tdSql.checkRows(maxRemainderNum+1) tdSql.query("select distinct t4, t2 from stb2") tdSql.checkRows(maxRemainderNum*3+1) tdSql.query("select distinct t2, t3, t4 from stb2") tdSql.checkRows(maxRemainderNum*3+1) tdSql.query("select distinct t2 t1, t3 t2 from stb2") tdSql.checkRows(maxRemainderNum+1) tdSql.query("select distinct t3, t3, t3 from stb2") tdSql.checkRows(maxRemainderNum+1) tdSql.query("select distinct t2, t3 from t01") tdSql.checkRows(1) tdSql.query("select distinct t3, t4 from t0100num") tdSql.checkRows(1) ########## should be error ######### tdSql.error("select distinct from stb1") tdSql.error("select distinct t3 from stb1") tdSql.error("select distinct t1 from db.*") tdSql.error("select distinct t2 from ") tdSql.error("distinct t2 from stb1") tdSql.error("select distinct stb1") tdSql.error("select distinct t0, t1, t2, t3 from stb1") tdSql.error("select distinct stb1.t0, stb1.t1, stb2.t2, stb2.t3 from stb1") tdSql.error("select dist t0 from stb1") tdSql.error("select distinct stb2.t2, stb2.t3 from stb1") tdSql.error("select distinct stb2.t2 t1, stb2.t3 t2 from stb1") tdSql.error("select distinct t0, t1 from t1 where t0 < 7") ########## add where condition ########## tdSql.query("select distinct t0, t1 from stb1 where t1 > 3") tdSql.checkRows(3) tdSql.query("select distinct t0, t1 from stb1 where t1 > 3 limit 2") tdSql.checkRows(2) tdSql.query("select distinct t0, t1 from stb1 where t1 > 3 limit 2 offset 2") tdSql.checkRows(1) tdSql.query("select distinct t0, t1 from stb1 where t1 > 3 slimit 2") tdSql.checkRows(3) tdSql.error("select distinct t0, t1 from stb1 where c1 > 2") tdSql.query("select distinct t0, t1 from stb1 where t1 > 3 and t1 < 5") tdSql.checkRows(1) tdSql.error("select distinct stb1.t0, stb1.t1 from stb1, stb2 where stb1.t2=stb2.t4") tdSql.error("select distinct t0, t1 from stb1 where stb2.t4 > 2") tdSql.error("select distinct t0, t1 from stb1 where t1 > 3 group by t0") tdSql.error("select distinct t0, t1 from stb1 where t1 > 3 interval(1d) ") tdSql.error("select distinct t0, t1 from stb1 where t1 > 3 interval(1d) fill(next)") tdSql.error("select distinct t0, t1 from stb1 where ts > now-10d and ts < now interval(1d) fill(next)") tdSql.error("select max(c1), distinct t0 from stb1 where t0 > 2") tdSql.error("select distinct t0, max(c1) from stb1 where t0 > 2") tdSql.error("select distinct t0 from stb1 where t0 in (select t0 from stb1 where t0 > 2)") tdSql.query("select distinct t0, t1 from stb1 where t0 in (1,2,3,4,5)") tdSql.checkRows(5) tdSql.query("select distinct t1 from (select t0, t1 from stb1 where t0 > 2) ") tdSql.checkRows(4) tdSql.error("select distinct t1 from (select distinct t0, t1 from stb1 where t0 > 2 and t1 < 3) ") tdSql.error("select distinct t1 from (select distinct t0, t1 from stb1 where t0 > 2 ) where t1 < 3") tdSql.query("select distinct t1 from (select t0, t1 from stb1 where t0 > 2 ) where t1 < 3") tdSql.checkRows(1) tdSql.error("select distinct t1, t0 from (select t1 from stb1 where t0 > 2 ) where t1 < 3") tdSql.error("select distinct t1, t0 from (select max(t1) t1, max(t0) t0 from stb1 group by t1)") tdSql.error("select distinct t1, t0 from (select max(t1) t1, max(t0) t0 from stb1)") tdSql.query("select distinct t1, t0 from (select t1,t0 from stb1 where t0 > 2 ) where t1 < 3") tdSql.checkRows(1) tdSql.error(" select distinct t1, t0 from (select t1,t0 from stb1 where t0 > 2 order by ts) where t1 < 3") tdSql.error("select t1, t0 from (select distinct t1,t0 from stb1 where t0 > 2 ) where t1 < 3") tdSql.error(" select distinct t1, t0 from (select t1,t0 from stb1 where t0 > 2 group by ts) where t1 < 3") tdSql.error("select distinct stb1.t1, stb1.t2 from stb1 , stb2 where stb1.ts=stb2.ts and stb1.t2=stb2.t4") tdSql.error("select distinct t1.t1, t1.t2 from t1 , t2 where t1.ts=t2.ts ") pass def td5935(self): tdLog.printNoPrefix("==========TD-5935==========") 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) tags(t1 int, t2 int)") nowtime=int(round((time.time()*1000))) for i in range(100): sql = f"create table db.t{i} using db.stb1 tags({i % 7}, {i % 2})" tdSql.execute(sql) for j in range(1000): tdSql.execute(f"insert into db.t{i} values ({nowtime-j*10}, {1000-j}, {round(random.random()*j,3)})") tdSql.execute(f"insert into db.t{i} (ts) values ({nowtime-10000}) ") ########### TD-5933 verify the bug of "function stddev with interval return 0 rows" is fixed ########## stddevAndIntervalSql=f"select last(*) from t0 where ts>={nowtime-10000} interval(10a) limit 10" tdSql.query(stddevAndIntervalSql) tdSql.checkRows(10) ########## TD-5978 verify the bug of "when start row is null, result by fill(next) is 0 " is fixed ########## fillsql=f"select last(*) from t0 where ts>={nowtime-10000} and ts<{nowtime} interval(10a) fill(next) limit 10" tdSql.query(fillsql) fillResult=False if (tdSql.getData(0,2) != 0) and (tdSql.getData(0, 2) is not None): fillResult=True if fillResult: tdLog.success(f"sql is :{fillsql}, fill(next) is correct") else: tdLog.exit("fill(next) is wrong") pass def td6068(self): tdLog.printNoPrefix("==========TD-6068==========") 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) tags(t1 int)") for i in range(100): sql = f"create table db.t{i} using db.stb1 tags({i})" tdSql.execute(sql) tdSql.execute(f"insert into db.t{i} values (now-10h, {i}, {i+random.random()}, now-10h, 'a_{i}', '{i-random.random()}', True)") tdSql.execute(f"insert into db.t{i} values (now-9h, {i+random.randint(1,10)}, {i+random.random()}, now-9h, 'a_{i}', '{i-random.random()}', FALSE )") tdSql.execute(f"insert into db.t{i} values (now-8h, {i+random.randint(1,10)}, {i+random.random()}, now-8h, 'b_{i}', '{i-random.random()}', True)") tdSql.execute(f"insert into db.t{i} values (now-7h, {i+random.randint(1,10)}, {i+random.random()}, now-7h, 'b_{i}', '{i-random.random()}', FALSE )") tdSql.execute(f"insert into db.t{i} values (now-6h, {i+random.randint(1,10)}, {i+random.random()}, now-6h, 'c_{i}', '{i-random.random()}', True)") tdSql.execute(f"insert into db.t{i} values (now-5h, {i+random.randint(1,10)}, {i+random.random()}, now-5h, 'c_{i}', '{i-random.random()}', FALSE )") tdSql.execute(f"insert into db.t{i} (ts)values (now-4h)") tdSql.execute(f"insert into db.t{i} (ts)values (now-11h)") tdSql.execute(f"insert into db.t{i} (ts)values (now-450m)") tdSql.query("select ts as t,derivative(c1, 10m, 0) from t1") tdSql.checkRows(5) tdSql.checkCols(3) for i in range(5): data=tdSql.getData(i, 0) tdSql.checkData(i, 1, data) tdSql.query("select ts as t, derivative(c1, 1h, 0) from stb1 group by tbname") tdSql.checkRows(500) tdSql.checkCols(4) tdSql.query("select ts as t, derivative(c1, 1s, 0) from t1") tdSql.query("select ts as t, derivative(c1, 1d, 0) from t1") tdSql.error("select ts as t, derivative(c1, 1h, 0) from stb1") tdSql.query("select ts as t, derivative(c2, 1h, 0) from t1") tdSql.checkRows(5) tdSql.error("select ts as t, derivative(c3, 1h, 0) from t1") tdSql.error("select ts as t, derivative(c4, 1h, 0) from t1") tdSql.query("select ts as t, derivative(c5, 1h, 0) from t1") tdSql.checkRows(5) tdSql.error("select ts as t, derivative(c6, 1h, 0) from t1") tdSql.error("select ts as t, derivative(t1, 1h, 0) from t1") tdSql.query("select ts as t, diff(c1) from t1") tdSql.checkRows(5) tdSql.checkCols(3) for i in range(5): data=tdSql.getData(i, 0) tdSql.checkData(i, 1, data) tdSql.query("select ts as t, diff(c1) from stb1 group by tbname") tdSql.checkRows(500) tdSql.checkCols(4) tdSql.query("select ts as t, diff(c1) from t1") tdSql.query("select ts as t, diff(c1) from t1") tdSql.error("select ts as t, diff(c1) from stb1") tdSql.query("select ts as t, diff(c2) from t1") tdSql.checkRows(5) tdSql.error("select ts as t, diff(c3) from t1") tdSql.error("select ts as t, diff(c4) from t1") tdSql.query("select ts as t, diff(c5) from t1") tdSql.checkRows(5) tdSql.error("select ts as t, diff(c6) from t1") tdSql.error("select ts as t, diff(t1) from t1") tdSql.error("select ts as t, diff(c1, c2) from t1") tdSql.error("select ts as t, bottom(c1, 0) from t1") tdSql.query("select ts as t, bottom(c1, 5) from t1") tdSql.checkRows(5) tdSql.checkCols(3) for i in range(5): data=tdSql.getData(i, 0) tdSql.checkData(i, 1, data) tdSql.query("select ts as t, bottom(c1, 5) from stb1") tdSql.checkRows(5) tdSql.query("select ts as t, bottom(c1, 5) from stb1 group by tbname") tdSql.checkRows(500) tdSql.query("select ts as t, bottom(c1, 8) from t1") tdSql.checkRows(6) tdSql.query("select ts as t, bottom(c2, 8) from t1") tdSql.checkRows(6) tdSql.error("select ts as t, bottom(c3, 5) from t1") tdSql.error("select ts as t, bottom(c4, 5) from t1") tdSql.query("select ts as t, bottom(c5, 8) from t1") tdSql.checkRows(6) tdSql.error("select ts as t, bottom(c6, 5) from t1") tdSql.error("select ts as t, bottom(c5, 8) as b from t1 order by b") tdSql.error("select ts as t, bottom(t1, 1) from t1") tdSql.error("select ts as t, bottom(t1, 1) from stb1") tdSql.error("select ts as t, bottom(t1, 3) from stb1 order by c3") tdSql.error("select ts as t, bottom(t1, 3) from t1 order by c3") tdSql.error("select ts as t, top(c1, 0) from t1") tdSql.query("select ts as t, top(c1, 5) from t1") tdSql.checkRows(5) tdSql.checkCols(3) for i in range(5): data=tdSql.getData(i, 0) tdSql.checkData(i, 1, data) tdSql.query("select ts as t, top(c1, 5) from stb1") tdSql.checkRows(5) tdSql.query("select ts as t, top(c1, 5) from stb1 group by tbname") tdSql.checkRows(500) tdSql.query("select ts as t, top(c1, 8) from t1") tdSql.checkRows(6) tdSql.query("select ts as t, top(c2, 8) from t1") tdSql.checkRows(6) tdSql.error("select ts as t, top(c3, 5) from t1") tdSql.error("select ts as t, top(c4, 5) from t1") tdSql.query("select ts as t, top(c5, 8) from t1") tdSql.checkRows(6) tdSql.error("select ts as t, top(c6, 5) from t1") tdSql.error("select ts as t, top(c5, 8) as b from t1 order by b") tdSql.error("select ts as t, top(t1, 1) from t1") tdSql.error("select ts as t, top(t1, 1) from stb1") tdSql.error("select ts as t, top(t1, 3) from stb1 order by c3") tdSql.error("select ts as t, top(t1, 3) from t1 order by c3") tdDnodes.stop(1) tdDnodes.start(1) tdSql.query("select ts as t, diff(c1) from t1") tdSql.checkRows(5) tdSql.checkCols(3) for i in range(5): data=tdSql.getData(i, 0) tdSql.checkData(i, 1, data) tdSql.query("select ts as t, diff(c1) from stb1 group by tbname") tdSql.checkRows(500) tdSql.checkCols(4) tdSql.query("select ts as t, diff(c1) from t1") tdSql.query("select ts as t, diff(c1) from t1") tdSql.error("select ts as t, diff(c1) from stb1") tdSql.query("select ts as t, diff(c2) from t1") tdSql.checkRows(5) tdSql.error("select ts as t, diff(c3) from t1") tdSql.error("select ts as t, diff(c4) from t1") tdSql.query("select ts as t, diff(c5) from t1") tdSql.checkRows(5) tdSql.error("select ts as t, diff(c6) from t1") tdSql.error("select ts as t, diff(t1) from t1") tdSql.error("select ts as t, diff(c1, c2) from t1") tdSql.error("select ts as t, bottom(c1, 0) from t1") tdSql.query("select ts as t, bottom(c1, 5) from t1") tdSql.checkRows(5) tdSql.checkCols(3) for i in range(5): data=tdSql.getData(i, 0) tdSql.checkData(i, 1, data) tdSql.query("select ts as t, bottom(c1, 5) from stb1") tdSql.checkRows(5) tdSql.query("select ts as t, bottom(c1, 5) from stb1 group by tbname") tdSql.checkRows(500) tdSql.query("select ts as t, bottom(c1, 8) from t1") tdSql.checkRows(6) tdSql.query("select ts as t, bottom(c2, 8) from t1") tdSql.checkRows(6) tdSql.error("select ts as t, bottom(c3, 5) from t1") tdSql.error("select ts as t, bottom(c4, 5) from t1") tdSql.query("select ts as t, bottom(c5, 8) from t1") tdSql.checkRows(6) tdSql.error("select ts as t, bottom(c6, 5) from t1") tdSql.error("select ts as t, bottom(c5, 8) as b from t1 order by b") tdSql.error("select ts as t, bottom(t1, 1) from t1") tdSql.error("select ts as t, bottom(t1, 1) from stb1") tdSql.error("select ts as t, bottom(t1, 3) from stb1 order by c3") tdSql.error("select ts as t, bottom(t1, 3) from t1 order by c3") tdSql.error("select ts as t, top(c1, 0) from t1") tdSql.query("select ts as t, top(c1, 5) from t1") tdSql.checkRows(5) tdSql.checkCols(3) for i in range(5): data=tdSql.getData(i, 0) tdSql.checkData(i, 1, data) tdSql.query("select ts as t, top(c1, 5) from stb1") tdSql.checkRows(5) tdSql.query("select ts as t, top(c1, 5) from stb1 group by tbname") tdSql.checkRows(500) tdSql.query("select ts as t, top(c1, 8) from t1") tdSql.checkRows(6) tdSql.query("select ts as t, top(c2, 8) from t1") tdSql.checkRows(6) tdSql.error("select ts as t, top(c3, 5) from t1") tdSql.error("select ts as t, top(c4, 5) from t1") tdSql.query("select ts as t, top(c5, 8) from t1") tdSql.checkRows(6) tdSql.error("select ts as t, top(c6, 5) from t1") tdSql.error("select ts as t, top(c5, 8) as b from t1 order by b") tdSql.error("select ts as t, top(t1, 1) from t1") tdSql.error("select ts as t, top(t1, 1) from stb1") tdSql.error("select ts as t, top(t1, 3) from stb1 order by c3") tdSql.error("select ts as t, top(t1, 3) from t1 order by c3") pass def apercentile_query_form(self, col="c1", p=0, com=',', algo="'t-digest'", alias="", table_expr="t1", condition=""): ''' apercentile function: :param col: string, column name, required parameters; :param p: float, percentile interval, [0,100], required parameters; :param algo: string, alforithm, real form like: ', algorithm' , algorithm: {type:int, data:[0, 1]}; :param alias: string, result column another name; :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)' :return: apercentile query statement,default: select apercentile(c1, 0, 1) from t1 ''' return f"select apercentile({col}, {p}{com} {algo}) {alias} from {table_expr} {condition}" def checkapert(self,col="c1", p=0, com=',', algo='"t-digest"', alias="", table_expr="t1", condition="" ): tdSql.query(f"select count({col}) from {table_expr} {condition}") if tdSql.queryRows == 0: tdSql.query(self.apercentile_query_form( col=col, p=p, com=com, algo=algo, alias=alias, table_expr=table_expr, condition=condition )) tdSql.checkRows(0) return pset = [0, 40, 60, 100] if p not in pset: pset.append(p) if "stb" in table_expr: tdSql.query(f"select spread({col}) from stb1") else: tdSql.query(f"select avg(c1) from (select spread({col.split('.')[-1]}) c1 from stb1 group by tbname)") spread_num = tdSql.getData(0, 0) for pi in pset: if "group" in condition: tdSql.query(f"select last_row({col}) from {table_expr} {condition}") query_result = tdSql.queryResult query_rows = tdSql.queryRows for i in range(query_rows): pre_condition = condition.replace("slimit",'limit').replace("group by tbname", "").split("soffset")[0] tbname = query_result[i][-1] tdSql.query(f"select percentile({col}, {pi}) {alias} from {tbname} {pre_condition}") print(tdSql.sql) pre_data = tdSql.getData(0, 0) tdSql.query(self.apercentile_query_form( col=col, p=pi, com=com, algo='"t-digest"', alias=alias, table_expr=table_expr, condition=condition )) if abs(tdSql.getData(i, 0)) >= (spread_num*0.02): tdSql.checkDeviaRation(i, 0, pre_data, 0.1) else: devia = abs((tdSql.getData(i, 0) - pre_data) / (spread_num * 0.02)) if devia < 0.5: tdLog.info(f"sql:{tdSql.sql}, result data:{tdSql.getData(i, 0)}, expect data:{pre_data}, " f"actual deviation:{devia} <= expect deviation: 0.01") else: tdLog.exit( f"[{inspect.getframeinfo(inspect.stack()[1][0]).lineno}],check failed:sql:{tdSql.sql}, " f"result data:{tdSql.getData(i, 0)}, expect data:{pre_data}, " f"actual deviation:{devia} > expect deviation: 0.01") # if "group" in condition: # tdSql.query(self.apercentile_query_form( # col=col, p=pi, com=com, algo='"default"', alias=alias, table_expr=table_expr, condition=condition # )) # query_result = tdSql.queryResult # query_rows = tdSql.queryRows # tdSql.query(self.apercentile_query_form( # col=col, p=pi, com=com, algo='"t-digest"', alias=alias, table_expr=table_expr, condition=condition # )) # for i in range(query_rows): # if abs(tdSql.getData(i, 0)) >= (spread_num*0.02): # tdSql.checkDeviaRation(i, 0, query_result[i][0], 0.1) # else: # devia = abs((tdSql.getData(i, 0) - query_result[i][0]) / (spread_num * 0.02)) # if devia < 0.5: # tdLog.info(f"sql:{tdSql.sql}, result data:{tdSql.getData(i, 0)}, expect data:{tdSql.queryResult[i][0]}, " # f"actual deviation:{devia} <= expect deviation: 0.01") # else: # tdLog.exit( # f"[{inspect.getframeinfo(inspect.stack()[1][0]).lineno}],check failed:sql:{tdSql.sql}, " # f"result data:{tdSql.getData(i, 0)}, expect data:{tdSql.queryResult[i][0]}, " # f"actual deviation:{devia} > expect deviation: 0.01") else: if ',' in alias or not alias: tdSql.query(f"select {col} from {table_expr} {condition}") elif "stb" not in table_expr: tdSql.query(f"select percentile({col}, {pi}) {alias} from {table_expr} {condition}") else: tdSql.query(self.apercentile_query_form( col=col, p=pi, com=com, algo='"default"', alias=alias, table_expr=table_expr, condition=condition )) query_result = np.array(tdSql.queryResult)[np.array(tdSql.queryResult) != None] tdSql.query(self.apercentile_query_form( col=col, p=pi, com=com, algo=algo, alias=alias, table_expr=table_expr, condition=condition )) if abs(tdSql.getData(0, 0)) >= (spread_num * 0.02): tdSql.checkDeviaRation(0, 0, np.percentile(query_result, pi), 0.1) else: devia = abs((tdSql.getData(0, 0) - np.percentile(query_result, pi)) / (spread_num * 0.02)) if devia < 0.5: tdLog.info( f"sql:{tdSql.sql}, result data:{tdSql.getData(0, 0)}, expect data:{np.percentile(query_result, pi)}, " f"actual deviation:{devia} <= expect deviation: 0.01") else: tdLog.exit( f"[{inspect.getframeinfo(inspect.stack()[1][0]).lineno}],check failed:sql:{tdSql.sql}, " f"result data:{tdSql.getData(0, 0)}, expect data:{np.percentile(query_result, pi)}, " f"actual deviation:{devia} > expect deviation: 0.01") def apercentile_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： int col self.checkapert() # case2: float col case2 = {'col':'c2'} self.checkapert(**case2) # case3: double col case3 = {'col':'c5'} self.checkapert(**case3) # case4: bigint col case4 = {'col':'c7'} self.checkapert(**case4) # case5: smallint col case5 = {'col':'c8'} self.checkapert(**case5) # case6: tinyint col case6 = {'col':'c9'} self.checkapert(**case6) # case7: stable case7 = {'table_expr':'stb1'} self.checkapert(**case7) # case8: nest query, outquery case8 = {'table_expr':'(select c1 from t1)'} self.checkapert(**case8) # case9: nest query, inquery and out query case9 = {'table_expr':'(select apercentile(c1, 0) as c1 from t1)'} self.checkapert(**case9) # case10: nest query, inquery tdSql.query("select * from (select c1 from stb1)") if tdSql.queryRows == 0: tdSql.query("select * from (select apercentile(c1,0) c1 from stb1)") tdSql.checkRows(0) else: query_result = np.array(tdSql.queryResult)[np.array(tdSql.queryResult) != None] tdSql.query("select * from (select apercentile(c1, 0) c1 from stb1)") tdSql.checkDeviaRation(0, 0, np.percentile(query_result, 0), 0.1) tdSql.query("select * from (select apercentile(c1,100) c1 from stb1)") tdSql.checkDeviaRation(0, 0, np.percentile(query_result, 100), 0.1) tdSql.query("select * from (select apercentile(c1,40) c1 from stb1)") tdSql.checkDeviaRation(0, 0, np.percentile(query_result, 40), 0.1) # case11: no algorithm = algo:0 case11 = {'com':'', 'algo': ''} self.checkapert(**case11) # case12~14: p: bin/oct/hex case12 = {'p': 0b1100100} self.checkapert(**case12) case13 = {'algo':'"T-DIGEST"'} self.checkapert(**case13) case14 = {'p':0x32, 'algo':'"DEFAULT"'} self.checkapert(**case14) # case15~21: mix with aggregate function case15 = {'alias':', count(*)'} self.checkapert(**case15) case16 = {'alias':', avg(c1)'} self.checkapert(**case16) case17 = {'alias':', twa(c1)'} self.checkapert(**case17) case18 = {'alias':', irate(c1)'} self.checkapert(**case18) case19 = {'alias':', sum(c1)'} self.checkapert(**case19) case20 = {'alias':', stddev(c1)'} self.checkapert(**case20) case21 = {'alias':', leastsquares(c1, 1, 1)'} self.checkapert(**case21) # case22~27：mix with selector function case22 = {'alias':', min(c1)'} self.checkapert(**case22) case23 = {'alias':', max(c1)'} self.checkapert(**case23) case24 = {'alias':', first(c1)'} self.checkapert(**case24) case25 = {'alias':', last(c1)'} self.checkapert(**case25) case26 = {'alias':', percentile(c1, 0)'} self.checkapert(**case26) case27 = {'alias':', apercentile(c1, 0, "t-digest")'} self.checkapert(**case27) # case28~29: mix with computing function case28 = {'alias':', spread(c1)'} self.checkapert(**case28) # case29: mix with four operation case29 = {'alias':'+ spread(c1)'} self.checkapert(**case29) # case30~36: with condition case30 = {'condition':'where ts > now'} self.checkapert(**case30) case31 = {'condition':'where c1 between 1 and 200'} self.checkapert(**case31) case32 = {'condition':f'where c1 in {tuple(i for i in range(200))}'} self.checkapert(**case32) case33 = {'condition':'where c1>100 and c2<100'} self.checkapert(**case33) case34 = {'condition':'where c1 is not null'} self.checkapert(**case34) case35 = {'condition':'where c4 like "_inary%"'} self.checkapert(**case35) case36 = {'table_expr':'stb1' ,'condition':'where tbname like "t_"'} self.checkapert(**case36) # case37~38: with join case37 = {'col':'t1.c1','table_expr':'t1, t2 ','condition':'where t1.ts=t2.ts'} self.checkapert(**case37) case38 = {'col':'stb1.c1', 'table_expr':'stb1, stb2', 'condition':'where stb1.ts=stb2.ts and stb1.st1=stb2.st2'} self.checkapert(**case38) # case39: with group by case39 = {'table_expr':'stb1', 'condition':'group by tbname'} self.checkapert(**case39) # case40: with slimit case40 = {'table_expr':'stb1', 'condition':'group by tbname slimit 1'} self.checkapert(**case40) # case41: with soffset case41 = {'table_expr':'stb1', 'condition':'group by tbname slimit 1 soffset 1'} self.checkapert(**case41) # case42: with order by case42 = {'table_expr':'stb1' ,'condition':'order by ts'} self.checkapert(**case42) case43 = {'table_expr':'t1' ,'condition':'order by ts'} self.checkapert(**case43) # case44: with limit offset case44 = {'table_expr':'stb1', 'condition':'group by tbname limit 1'} self.checkapert(**case44) case45 = {'table_expr':'stb1', 'condition':'group by tbname limit 1 offset 1'} self.checkapert(**case45) pass def error_apercentile(self): # 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.apercentile_query_form(col="",com='',algo='')) # no col , no algorithm tdSql.error(self.apercentile_query_form(col="")) # no col , algorithm tdSql.error(self.apercentile_query_form(p='',com='',algo='')) # no p , no algorithm tdSql.error(self.apercentile_query_form(p='')) # no p , algorithm tdSql.error("apercentile( c1, 100) from t1") # no select tdSql.error("select apercentile from t1") # no algorithm condition tdSql.error("select apercentile c1,0 from t1") # no brackets tdSql.error("select apercentile (c1,0) t1") # no from tdSql.error(self.apercentile_query_form(col='(c1,0)',p='',com='',algo='')) # no p , no algorithm tdSql.error("select apercentile( (c1,0) ) from t1") # no table_expr tdSql.error("select apercentile{ (c1,0) } from t1") # sql form error 1 tdSql.error("select apercentile[ (c1,0) ] from t1") # sql form error 2 tdSql.error("select [apercentile(c1,0) ] from t1") # sql form error 3 tdSql.error("select apercentile((c1, 0), 'default') from t1") # sql form error 5 tdSql.error("select apercentile(c1, (0, 'default')) from t1") # sql form error 6 tdSql.error("select apercentile(c1, (0), 1) from t1") # sql form error 7 tdSql.error("select apercentile([c1, 0], 'default') from t1") # sql form error 8 tdSql.error("select apercentile(c1, [0, 'default']) from t1") # sql form error 9 tdSql.error("select apercentile(c1, {0, 'default'}) from t1") # sql form error 10 tdSql.error("select apercentile([c1, 0]) from t1") # sql form error 11 tdSql.error("select apercentile({c1, 0}) from t1") # sql form error 12 tdSql.error("select apercentile(c1) from t1") # agrs: 1 tdSql.error("select apercentile(c1, 0, 'default', 0) from t1") # agrs: 4 tdSql.error("select apercentile(c1, 0, 0, 'default') from t1") # agrs: 4 tdSql.error("select apercentile() from t1") # agrs: null 1 tdSql.error("select apercentile from t1") # agrs: null 2 tdSql.error("select apercentile( , , ) from t1") # agrs: null 3 tdSql.error(self.apercentile_query_form(col='', p='', algo='')) # agrs: null 4 tdSql.error(self.apercentile_query_form(col="st1")) # col:tag column tdSql.error(self.apercentile_query_form(col=123)) # col:numerical tdSql.error(self.apercentile_query_form(col=True)) # col:bool tdSql.error(self.apercentile_query_form(col='')) # col:'' tdSql.error(self.apercentile_query_form(col="last(c1)")) # col:expr tdSql.error(self.apercentile_query_form(col="t%")) # col:non-numerical tdSql.error(self.apercentile_query_form(col="c3")) # col-type: timestamp tdSql.error(self.apercentile_query_form(col="c4")) # col-type: binary tdSql.error(self.apercentile_query_form(col="c6")) # col-type: bool tdSql.error(self.apercentile_query_form(col="c10")) # col-type: nchar tdSql.error(self.apercentile_query_form(p=True)) # p:bool tdSql.error(self.apercentile_query_form(p='a')) # p:str tdSql.error(self.apercentile_query_form(p='last(*)')) # p:expr tdSql.error(self.apercentile_query_form(p="2021-08-01 00:00:00.000")) # p:timestamp tdSql.error(self.apercentile_query_form(algo='t-digest')) # algorithm:str tdSql.error(self.apercentile_query_form(algo='"t_digest"')) # algorithm:str tdSql.error(self.apercentile_query_form(algo='"t-digest0"')) # algorithm:str tdSql.error(self.apercentile_query_form(algo='"t-digest."')) # algorithm:str tdSql.error(self.apercentile_query_form(algo='"t-digest%"')) # algorithm:str tdSql.error(self.apercentile_query_form(algo='"t-digest*"')) # algorithm:str tdSql.error(self.apercentile_query_form(algo='tdigest')) # algorithm:str tdSql.error(self.apercentile_query_form(algo=2.0)) # algorithm:float tdSql.error(self.apercentile_query_form(algo=1.9999)) # algorithm:float tdSql.error(self.apercentile_query_form(algo=-0.9999)) # algorithm:float tdSql.error(self.apercentile_query_form(algo=-1.0)) # algorithm:float tdSql.error(self.apercentile_query_form(algo=0b1)) # algorithm:float tdSql.error(self.apercentile_query_form(algo=0x1)) # algorithm:float tdSql.error(self.apercentile_query_form(algo=0o1)) # algorithm:float tdSql.error(self.apercentile_query_form(algo=True)) # algorithm:bool tdSql.error(self.apercentile_query_form(algo="True")) # algorithm:bool tdSql.error(self.apercentile_query_form(algo='2021-08-01 00:00:00.000')) # algorithm:timestamp tdSql.error(self.apercentile_query_form(algo='last(c1)')) # algorithm:expr # boundary test tdSql.error(self.apercentile_query_form(p=-1)) # p left out of [0, 100] tdSql.error(self.apercentile_query_form(p=-9223372036854775809)) # p left out of bigint tdSql.error(self.apercentile_query_form(p=100.1)) # p right out of [0, 100] tdSql.error(self.apercentile_query_form(p=18446744073709551616)) # p right out of unsigned-bigint tdSql.error(self.apercentile_query_form(algo=-1)) # algorithm left out of [0, 1] tdSql.error(self.apercentile_query_form(algo=-9223372036854775809)) # algorithm left out of unsigned-bigint tdSql.error(self.apercentile_query_form(algo=2)) # algorithm right out of [0, 1] tdSql.error(self.apercentile_query_form(algo=18446744073709551616)) # algorithm right out of unsigned-bigint # mix function test tdSql.error(self.apercentile_query_form(alias=', top(c1,1)')) # mix with top function tdSql.error(self.apercentile_query_form(alias=', top(c1,1)')) # mix with bottom function tdSql.error(self.apercentile_query_form(alias=', last_row(c1)')) # mix with last_row function tdSql.error(self.apercentile_query_form(alias=', distinct c1 ')) # mix with distinct function tdSql.error(self.apercentile_query_form(alias=', *')) # mix with * tdSql.error(self.apercentile_query_form(alias=', diff(c1)')) # mix with diff function tdSql.error(self.apercentile_query_form(alias=', interp(c1)', condition='ts="2021-10-10 00:00:00.000"')) # mix with interp function tdSql.error(self.apercentile_query_form(alias=', derivative(c1, 10m, 0)')) # mix with derivative function tdSql.error(self.apercentile_query_form(alias=', diff(c1)')) # mix with diff function tdSql.error(self.apercentile_query_form(alias='+ c1)')) # mix with four operation def apercentile_data(self, tbnum, data_row, basetime): for i in range(tbnum): for j in range(data_row): tdSql.execute( f"insert into t{i} values (" f"{basetime + j*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 td6108(self): tdLog.printNoPrefix("==========TD-6108==========") 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)" ) tbnum = 10 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})") tdLog.printNoPrefix("######## no data test:") self.apercentile_query() self.error_apercentile() tdLog.printNoPrefix("######## insert data test:") nowtime = int(round(time.time() * 1000)) per_table_rows = 1000 self.apercentile_data(tbnum, per_table_rows, nowtime) self.apercentile_query() self.error_apercentile() tdLog.printNoPrefix("######## insert data with NULL test:") tdSql.execute(f"insert into t1(ts) values ({nowtime-5})") tdSql.execute(f"insert into t1(ts) values ({nowtime+5})") self.apercentile_query() self.error_apercentile() tdLog.printNoPrefix("######## check after WAL test:") tdSql.query("show dnodes") index = tdSql.getData(0, 0) tdDnodes.stop(index) tdDnodes.start(index) self.apercentile_query() self.error_apercentile() def run(self): # master branch self.td3690() # self.td5168() # self.td5433() # self.td5798() # develop branch # self.td4889() In the scenario that with vnode/wal/wal* but without meta/data in vnode, the status is reset to 0 right now. def stop(self): tdSql.close() tdLog.success(f"{__file__} successfully executed") tdCases.addWindows(__file__, TDTestCase()) tdCases.addLinux(__file__, TDTestCase())