max.py

from util.log import *
from util.cases import *
from util.sql import *
import numpy as np


class TDTestCase:
    updatecfgDict = {'debugFlag': 143 ,"cDebugFlag":143,"uDebugFlag":143 ,"rpcDebugFlag":143 , "tmrDebugFlag":143 , 
    "jniDebugFlag":143 ,"simDebugFlag":143,"dDebugFlag":143, "dDebugFlag":143,"vDebugFlag":143,"mDebugFlag":143,"qDebugFlag":143,
    "wDebugFlag":143,"sDebugFlag":143,"tsdbDebugFlag":143,"tqDebugFlag":143 ,"fsDebugFlag":143 ,"fnDebugFlag":143,
    "maxTablesPerVnode":2 ,"minTablesPerVnode":2,"tableIncStepPerVnode":2 }
    def init(self, conn, logSql):
        tdLog.debug("start to execute %s" % __file__)
        tdSql.init(conn.cursor())

        self.rowNum = 10
        self.ts = 1537146000000

    def prepare_data(self):
        
        pass 

    def check_max_functions(self, tbname , col_name):

        max_sql = f"select max({col_name}) from {tbname};"

        same_sql = f"select {col_name} from {tbname} order by {col_name} desc limit 1"

        tdSql.query(max_sql)
        max_result = tdSql.queryResult 

        tdSql.query(same_sql)
        same_result = tdSql.queryResult

        if max_result !=same_result:
            tdLog.exit(" max function work not as expected, sql : %s "% max_sql)
        else:
            tdLog.info(" max function work as expected, sql : %s "% max_sql)


    def support_distributed_aggregate(self):
        
        # prepate datas for  20 tables distributed at different vgroups
        tdSql.execute("create database if not exists testdb keep 3650 days 1000 vgroups 5")
        tdSql.execute(" use testdb ")
        tdSql.execute(
            '''create table stb1
            (ts timestamp, c1 int, c2 bigint, c3 smallint, c4 tinyint, c5 float, c6 double, c7 bool, c8 binary(16),c9 nchar(32), c10 timestamp)
            tags (t0 timestamp, t1 int, t2 bigint, t3 smallint, t4 tinyint, t5 float, t6 double, t7 bool, t8 binary(16),t9 nchar(32))
            '''
        )

        tdSql.execute(
            '''
            create table t1
            (ts timestamp, c1 int, c2 bigint, c3 smallint, c4 tinyint, c5 float, c6 double, c7 bool, c8 binary(16),c9 nchar(32), c10 timestamp)
            '''
        )
        for i in range(20):
            tdSql.execute(f'create table ct{i+1} using stb1 tags ( now(), {1*i}, {11111*i}, {111*i}, {11*i}, {1.11*i}, {11.11*i}, {i%2}, "binary{i}", "nchar{i}" )')

        for i in range(9):
            tdSql.execute(
                f"insert into ct1 values ( now()-{i*10}s, {1*i}, {11111*i}, {111*i}, {11*i}, {1.11*i}, {11.11*i}, {i%2}, 'binary{i}', 'nchar{i}', now()+{1*i}a )"
            )
            tdSql.execute(
                f"insert into ct4 values ( now()-{i*90}d, {1*i}, {11111*i}, {111*i}, {11*i}, {1.11*i}, {11.11*i}, {i%2}, 'binary{i}', 'nchar{i}', now()+{1*i}a )"
            )

        for i in range(1,21):
            if i ==1 or i == 4:
                continue
            else:
                tbname = "ct"+f'{i}'
                for j in range(9):
                    tdSql.execute(
                f"insert into {tbname} values ( now()-{(i+j)*10}s, {1*(j+i)}, {11111*(j+i)}, {111*(j+i)}, {11*(j)}, {1.11*(j+i)}, {11.11*(j+i)}, {(j+i)%2}, 'binary{j}', 'nchar{j}', now()+{1*j}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 )
            '''
        )

        tdLog.info(" prepare data for distributed_aggregate done! ")

        # get vgroup_ids of all
        tdSql.query("show vgroups ")
        vgroups = tdSql.queryResult

        vnode_tables={}
        
        for vgroup_id in vgroups:
            vnode_tables[vgroup_id[0]]=[]
        

        # check sub_table of per vnode ,make sure sub_table has been distributed
        tdSql.query("show tables like 'ct%'")
        table_names = tdSql.queryResult
        tablenames = []
        for table_name in table_names:
            vnode_tables[table_name[6]].append(table_name[0]) 

        count = 0
        for k ,v in vnode_tables.items():
            if len(v)>=2:
                count+=1
        if count < 2:
            tdLog.exit(" the datas of all not satisfy sub_table has been distributed ")

        # check max function work status 
        
        tdSql.query("show tables like 'ct%'")
        table_names = tdSql.queryResult
        tablenames = []
        for table_name in table_names:
            tablenames.append(table_name[0])

        tdSql.query("desc stb1")
        col_names = tdSql.queryResult
        
        colnames = []
        for col_name in col_names:
            if col_name[1] in ["INT" ,"BIGINT" ,"SMALLINT" ,"TINYINT" , "FLOAT" ,"DOUBLE"]:
                colnames.append(col_name[0])
        
        for tablename in tablenames:
            for colname in colnames:
                self.check_max_functions(tablename,colname)

        # max function with basic filter 
        print(vnode_tables)



    def run(self):
        tdSql.prepare()

        intData = []        
        floatData = []

        tdSql.execute('''create table stb(ts timestamp, col1 tinyint, col2 smallint, col3 int, col4 bigint, col5 float, col6 double, 
                    col7 bool, col8 binary(20), col9 nchar(20), col11 tinyint unsigned, col12 smallint unsigned, col13 int unsigned, col14 bigint unsigned) tags(loc nchar(20))''')
        tdSql.execute("create table stb_1 using stb tags('beijing')")
        tdSql.execute('''create table ntb(ts timestamp, col1 tinyint, col2 smallint, col3 int, col4 bigint, col5 float, col6 double, 
                    col7 bool, col8 binary(20), col9 nchar(20), col11 tinyint unsigned, col12 smallint unsigned, col13 int unsigned, col14 bigint unsigned)''')
        for i in range(self.rowNum):
            tdSql.execute("insert into ntb values(%d, %d, %d, %d, %d, %f, %f, %d, 'taosdata%d', '涛思数据%d', %d, %d, %d, %d)" 
                        % (self.ts + i, i + 1, i + 1, i + 1, i + 1, i + 0.1, i + 0.1, i % 2, i + 1, i + 1, i + 1, i + 1, i + 1, i + 1))
            intData.append(i + 1)            
            floatData.append(i + 0.1)
        for i in range(self.rowNum):
            tdSql.execute("insert into stb_1 values(%d, %d, %d, %d, %d, %f, %f, %d, 'taosdata%d', '涛思数据%d', %d, %d, %d, %d)" 
                        % (self.ts + i, i + 1, i + 1, i + 1, i + 1, i + 0.1, i + 0.1, i % 2, i + 1, i + 1, i + 1, i + 1, i + 1, i + 1))
            intData.append(i + 1)            
            floatData.append(i + 0.1)                        

        # max verifacation 
        tdSql.error("select max(ts) from stb_1")
        tdSql.error("select max(ts) from db.stb_1")
        tdSql.error("select max(col7) from stb_1")
        tdSql.error("select max(col7) from db.stb_1")
        tdSql.error("select max(col8) from stb_1")
        tdSql.error("select max(col8) from db.stb_1")
        tdSql.error("select max(col9) from stb_1")
        tdSql.error("select max(col9) from db.stb_1")

        tdSql.query("select max(col1) from stb_1")
        tdSql.checkData(0, 0, np.max(intData))
        tdSql.query("select max(col1) from db.stb_1")
        tdSql.checkData(0, 0, np.max(intData))
        tdSql.query("select max(col2) from stb_1")
        tdSql.checkData(0, 0, np.max(intData))
        tdSql.query("select max(col2) from db.stb_1")
        tdSql.checkData(0, 0, np.max(intData))
        tdSql.query("select max(col3) from stb_1")
        tdSql.checkData(0, 0, np.max(intData))
        tdSql.query("select max(col3) from db.stb_1")
        tdSql.checkData(0, 0, np.max(intData))
        tdSql.query("select max(col4) from stb_1")
        tdSql.checkData(0, 0, np.max(intData))
        tdSql.query("select max(col4) from db.stb_1")
        tdSql.checkData(0, 0, np.max(intData))
        tdSql.query("select max(col11) from stb_1")
        tdSql.checkData(0, 0, np.max(intData))
        tdSql.query("select max(col11) from db.stb_1")
        tdSql.checkData(0, 0, np.max(intData))
        tdSql.query("select max(col12) from stb_1")
        tdSql.checkData(0, 0, np.max(intData))
        tdSql.query("select max(col12) from db.stb_1")
        tdSql.checkData(0, 0, np.max(intData))
        tdSql.query("select max(col13) from stb_1")
        tdSql.checkData(0, 0, np.max(intData))
        tdSql.query("select max(col13) from db.stb_1")
        tdSql.checkData(0, 0, np.max(intData))
        tdSql.query("select max(col14) from stb_1")
        tdSql.checkData(0, 0, np.max(intData))
        tdSql.query("select max(col14) from db.stb_1")
        tdSql.checkData(0, 0, np.max(intData))
        tdSql.query("select max(col5) from stb_1")
        tdSql.checkData(0, 0, np.max(floatData))
        tdSql.query("select max(col5) from db.stb_1")
        tdSql.checkData(0, 0, np.max(floatData))
        tdSql.query("select max(col6) from stb_1")
        tdSql.checkData(0, 0, np.max(floatData))
        tdSql.query("select max(col6) from db.stb_1")
        tdSql.checkData(0, 0, np.max(floatData))
        tdSql.query("select max(col1) from stb_1 where col2<=5")
        tdSql.checkData(0,0,5)
        


        tdSql.error("select max(ts) from stb")
        tdSql.error("select max(ts) from db.stb")
        tdSql.error("select max(col7) from stb")
        tdSql.error("select max(col7) from db.stb")
        tdSql.error("select max(col8) from stb")
        tdSql.error("select max(col8) from db.stb")
        tdSql.error("select max(col9) from stb")
        tdSql.error("select max(col9) from db.stb")

        tdSql.query("select max(col1) from stb")
        tdSql.checkData(0, 0, np.max(intData))
        tdSql.query("select max(col1) from db.stb")
        tdSql.checkData(0, 0, np.max(intData))
        tdSql.query("select max(col2) from stb")
        tdSql.checkData(0, 0, np.max(intData))
        tdSql.query("select max(col2) from db.stb")
        tdSql.checkData(0, 0, np.max(intData))
        tdSql.query("select max(col3) from stb")
        tdSql.checkData(0, 0, np.max(intData))
        tdSql.query("select max(col3) from db.stb")
        tdSql.checkData(0, 0, np.max(intData))
        tdSql.query("select max(col4) from stb")
        tdSql.checkData(0, 0, np.max(intData))
        tdSql.query("select max(col4) from db.stb")
        tdSql.checkData(0, 0, np.max(intData))
        tdSql.query("select max(col11) from stb")
        tdSql.checkData(0, 0, np.max(intData))
        tdSql.query("select max(col11) from db.stb")
        tdSql.checkData(0, 0, np.max(intData))
        tdSql.query("select max(col12) from stb")
        tdSql.checkData(0, 0, np.max(intData))
        tdSql.query("select max(col12) from db.stb")
        tdSql.checkData(0, 0, np.max(intData))
        tdSql.query("select max(col13) from stb")
        tdSql.checkData(0, 0, np.max(intData))
        tdSql.query("select max(col13) from db.stb")
        tdSql.checkData(0, 0, np.max(intData))
        tdSql.query("select max(col14) from stb")
        tdSql.checkData(0, 0, np.max(intData))
        tdSql.query("select max(col14) from db.stb")
        tdSql.checkData(0, 0, np.max(intData))
        tdSql.query("select max(col5) from stb")
        tdSql.checkData(0, 0, np.max(floatData))
        tdSql.query("select max(col5) from db.stb")
        tdSql.checkData(0, 0, np.max(floatData))
        tdSql.query("select max(col6) from stb")
        tdSql.checkData(0, 0, np.max(floatData))
        tdSql.query("select max(col6) from db.stb")
        tdSql.checkData(0, 0, np.max(floatData))
        tdSql.query("select max(col1) from stb where col2<=5")
        tdSql.checkData(0,0,5)



        tdSql.error("select max(ts) from ntb")
        tdSql.error("select max(ts) from db.ntb")
        tdSql.error("select max(col7) from ntb")
        tdSql.error("select max(col7) from db.ntb")
        tdSql.error("select max(col8) from ntb")
        tdSql.error("select max(col8) from db.ntb")
        tdSql.error("select max(col9) from ntb")
        tdSql.error("select max(col9) from db.ntb")

        tdSql.query("select max(col1) from ntb")
        tdSql.checkData(0, 0, np.max(intData))
        tdSql.query("select max(col1) from db.ntb")
        tdSql.checkData(0, 0, np.max(intData))
        tdSql.query("select max(col2) from ntb")
        tdSql.checkData(0, 0, np.max(intData))
        tdSql.query("select max(col2) from db.ntb")
        tdSql.checkData(0, 0, np.max(intData))
        tdSql.query("select max(col3) from ntb")
        tdSql.checkData(0, 0, np.max(intData))
        tdSql.query("select max(col3) from db.ntb")
        tdSql.checkData(0, 0, np.max(intData))
        tdSql.query("select max(col4) from ntb")
        tdSql.checkData(0, 0, np.max(intData))
        tdSql.query("select max(col4) from db.ntb")
        tdSql.checkData(0, 0, np.max(intData))
        tdSql.query("select max(col11) from ntb")
        tdSql.checkData(0, 0, np.max(intData))
        tdSql.query("select max(col11) from db.ntb")
        tdSql.checkData(0, 0, np.max(intData))
        tdSql.query("select max(col12) from ntb")
        tdSql.checkData(0, 0, np.max(intData))
        tdSql.query("select max(col12) from db.ntb")
        tdSql.checkData(0, 0, np.max(intData))
        tdSql.query("select max(col13) from ntb")
        tdSql.checkData(0, 0, np.max(intData))
        tdSql.query("select max(col13) from db.ntb")
        tdSql.checkData(0, 0, np.max(intData))
        tdSql.query("select max(col14) from ntb")
        tdSql.checkData(0, 0, np.max(intData))
        tdSql.query("select max(col14) from db.ntb")
        tdSql.checkData(0, 0, np.max(intData))
        tdSql.query("select max(col5) from ntb")
        tdSql.checkData(0, 0, np.max(floatData))
        tdSql.query("select max(col5) from db.ntb")
        tdSql.checkData(0, 0, np.max(floatData))
        tdSql.query("select max(col6) from ntb")
        tdSql.checkData(0, 0, np.max(floatData))
        tdSql.query("select max(col6) from db.ntb")
        tdSql.checkData(0, 0, np.max(floatData))
        tdSql.query("select max(col1) from stb_1 where col2<=5")
        tdSql.checkData(0,0,5)

        self.support_distributed_aggregate()

    
    def stop(self):
        tdSql.close()
        tdLog.success("%s successfully executed" % __file__)

tdCases.addWindows(__file__, TDTestCase())
tdCases.addLinux(__file__, TDTestCase())