function_diff.py

###################################################################
#           Copyright (c) 2016 by TAOS Technologies, Inc.
#                     All rights reserved.
#
#  This file is proprietary and confidential to TAOS Technologies.
#  No part of this file may be reproduced, stored, transmitted,
#  disclosed or used in any form or by any means other than as
#  expressly provided by the written permission from Jianhui Tao
#
###################################################################

# -*- coding: utf-8 -*-

import sys
import subprocess
import random
import math
import numpy as np
import inspect
import re

from util.log import *
from util.cases import *
from util.sql import *
from util.dnodes import *


class TDTestCase:
    def init(self, conn, logSql):
        tdLog.debug("start to execute %s" % __file__)
        tdSql.init(conn.cursor())

    def diff_query_form(self, col="c1",  alias="", table_expr="t1", condition=""):

        '''
        diff function:
        :param col:         string, column name, required parameters;
        :param alias:       string, result column another name，or add other funtion;
        :param table_expr:  string or expression, data source（eg,table/stable name, result set）, required parameters;
        :param condition:   expression；
        :param args:        other funtions,like: ', last(col)',or give result column another name, like 'c2'
        :return:            diff query statement,default: select diff(c1) from t1
        '''

        return f"select diff({col}) {alias} from {table_expr} {condition}"

    def checkdiff(self,col="c1", alias="", table_expr="t1", condition="" ):
        line = sys._getframe().f_back.f_lineno
        pre_sql = self.diff_query_form(
            col=col, table_expr=table_expr, condition=condition
        ).replace("diff", "count")
        tdSql.query(pre_sql)

        if tdSql.queryRows == 0:
            tdSql.query(self.diff_query_form(
                col=col, alias=alias, table_expr=table_expr, condition=condition
            ))
            print(f"case in {line}: ", end='')
            tdSql.checkRows(0)
            return

        if "order by tbname" in condition:
            tdSql.error(self.diff_query_form(
                col=col, alias=alias, table_expr=table_expr, condition=condition
            ))
            return

        if "group" in condition:

            tb_condition = condition.split("group by")[1].split(" ")[1]
            tdSql.query(f"select distinct {tb_condition} from {table_expr}")
            query_result = tdSql.queryResult
            query_rows = tdSql.queryRows
            clear_condition = re.sub('order by [0-9a-z]*|slimit [0-9]*|soffset [0-9]*', "", condition)

            pre_row = 0
            for i in range(query_rows):
                group_name = query_result[i][0]
                if "where" in clear_condition:
                    pre_condition = re.sub('group by [0-9a-z]*', f"{tb_condition}='{group_name}'", clear_condition)
                else:
                    pre_condition = "where " + re.sub('group by [0-9a-z]*',f"{tb_condition}='{group_name}'", clear_condition)

                tdSql.query(f"select {col} {alias} from {table_expr} {pre_condition}")
                pre_data = np.array(tdSql.queryResult)[np.array(tdSql.queryResult) != None]
                if (platform.system().lower() == 'windows' and pre_data.dtype == 'int32'):
                    pre_data = np.array(pre_data, dtype = 'int64')
                pre_diff = np.diff(pre_data)
                # trans precision for data
                tdSql.query(self.diff_query_form(
                    col=col, alias=alias, table_expr=table_expr, condition=condition
                ))
                for j in range(len(pre_diff)):
                    print(f"case in {line}:", end='')
                    if  isinstance(pre_diff[j] , float) :
                        pass
                    else:
                        tdSql.checkData(pre_row+j, 1, pre_diff[j] )
                pre_row += len(pre_diff)
            return
        elif "union" in condition:
            union_sql_0 = self.diff_query_form(
                col=col, alias=alias, table_expr=table_expr, condition=condition
            ).split("union all")[0]

            union_sql_1 = self.diff_query_form(
                col=col, alias=alias, table_expr=table_expr, condition=condition
            ).split("union all")[1]

            tdSql.query(union_sql_0)
            union_diff_0 = tdSql.queryResult
            row_union_0 = tdSql.queryRows

            tdSql.query(union_sql_1)
            union_diff_1 = tdSql.queryResult

            tdSql.query(self.diff_query_form(
                col=col, alias=alias, table_expr=table_expr, condition=condition
            ))
            for i in range(tdSql.queryRows):
                print(f"case in {line}: ", end='')
                if i < row_union_0:
                    tdSql.checkData(i, 0, union_diff_0[i][0])
                else:
                    tdSql.checkData(i, 0, union_diff_1[i-row_union_0][0])
            return

        else:
            tdSql.query(f"select {col} from {table_expr} {re.sub('limit [0-9]*|offset [0-9]*','',condition)}")
            offset_val = condition.split("offset")[1].split(" ")[1] if "offset" in condition else 0
            pre_result = np.array(tdSql.queryResult)[np.array(tdSql.queryResult) != None]
            if (platform.system().lower() == 'windows' and pre_result.dtype == 'int32'):
                pre_result = np.array(pre_result, dtype = 'int64')
            pre_diff = np.diff(pre_result)[offset_val:]
            tdSql.query(self.diff_query_form(
                col=col, alias=alias, table_expr=table_expr, condition=condition
            ))

            for i in range(tdSql.queryRows):
                print(f"case in {line}: ", end='')
                if isinstance(pre_diff[i] , float ):
                    pass
                else:
                    tdSql.checkData(i, 0, pre_diff[i])

        pass

    def diff_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.checkdiff()
        case2 =  {"col": "c2"}
        self.checkdiff(**case2)
        case3 =  {"col": "c5"}
        self.checkdiff(**case3)
        case4 =  {"col": "c7"}
        self.checkdiff(**case4)
        case5 =  {"col": "c8"}
        self.checkdiff(**case5)
        case6 =  {"col": "c9"}
        self.checkdiff(**case6)

        # case7~8: nested query
        # case7 = {"table_expr": "(select c1 from stb1)"}
        # self.checkdiff(**case7)
        # case8 = {"table_expr": "(select diff(c1) c1 from stb1 group by tbname)"}
        # self.checkdiff(**case8)

        # case9~10: mix with tbname/ts/tag/col
        # case9 = {"alias": ", tbname"}
        # self.checkdiff(**case9)
        # case10 = {"alias": ", _c0"}
        # self.checkdiff(**case10)
        # case11 = {"alias": ", st1"}
        # self.checkdiff(**case11)
        # case12 = {"alias": ", c1"}
        # self.checkdiff(**case12)

        # case13~15: with  single condition
        case13 = {"condition": "where c1 <= 10"}
        self.checkdiff(**case13)
        case14 = {"condition": "where c6 in (0, 1)"}
        self.checkdiff(**case14)
        case15 = {"condition": "where c1 between 1 and 10"}
        self.checkdiff(**case15)

        # case16:  with multi-condition
        case16 = {"condition": "where c6=1 or c6 =0"}
        self.checkdiff(**case16)

        # case17: only support normal table join
        case17 = {
            "col": "t1.c1",
            "table_expr": "t1, t2",
            "condition": "where t1.ts=t2.ts"
        }
        self.checkdiff(**case17)
        # case18~19: with group by
        # case18 = {
        #     "table_expr": "t1",
        #     "condition": "group by c6"
        # }
        # self.checkdiff(**case18)
        # case19 = {
        #     "table_expr": "stb1",
        #     "condition": "partition by tbname"  # partition by tbname
        # }
        # self.checkdiff(**case19)

        # # case20~21: with order by
        # case20 = {"condition": "order by ts"}
        # self.checkdiff(**case20)

        # # case22: with union
        # case22 = {
        #     "condition": "union all select diff(c1) from t2"
        # }
        # self.checkdiff(**case22)

        # case23: with limit/slimit
        case23 = {
            "condition": "limit 1"
        }
        self.checkdiff(**case23)
        # case24 = {
        #     "table_expr": "stb1",
        #     "condition": "group by tbname slimit 1 soffset 1"
        # }
        # self.checkdiff(**case24)

        pass

    def diff_error_query(self) -> None :
        # unusual test
        #
        # table schema :ts timestamp, c1 int, c2 float, c3 timestamp, c4 binary(16), c5 double, c6 bool
        #                 c7 bigint, c8 smallint, c9 tinyint, c10 nchar(16)
        #
        # form test
        tdSql.error(self.diff_query_form(col=""))   # no col
        tdSql.error("diff(c1) from stb1")           # no select
        tdSql.error("select diff from t1")          # no diff condition
        tdSql.error("select diff c1 from t1")       # no brackets
        tdSql.error("select diff(c1)   t1")         # no from
        tdSql.error("select diff( c1 )  from ")     # no table_expr
        # tdSql.error(self.diff_query_form(col="st1"))    # tag col
        tdSql.query("select diff(st1) from t1 ")
        # tdSql.error(self.diff_query_form(col=1))        # col is a value
        tdSql.error(self.diff_query_form(col="'c1'"))   # col is a string
        tdSql.error(self.diff_query_form(col=None))     # col is NULL 1
        tdSql.error(self.diff_query_form(col="NULL"))   # col is NULL 2
        tdSql.error(self.diff_query_form(col='""'))     # col is ""
        tdSql.error(self.diff_query_form(col='c%'))     # col is spercial char 1
        tdSql.error(self.diff_query_form(col='c_'))     # col is spercial char 2
        tdSql.error(self.diff_query_form(col='c.'))     # col is spercial char 3
        tdSql.error(self.diff_query_form(col='c3'))     # timestamp col
        tdSql.error(self.diff_query_form(col='ts'))     # Primary key
        tdSql.error(self.diff_query_form(col='avg(c1)'))    # expr col
        # tdSql.error(self.diff_query_form(col='c6'))     # bool col
        tdSql.query("select diff(c6) from t1")
        tdSql.error(self.diff_query_form(col='c4'))     # binary col
        tdSql.error(self.diff_query_form(col='c10'))    # nachr col
        tdSql.error(self.diff_query_form(col='c10'))    # not table_expr col
        tdSql.error(self.diff_query_form(col='t1'))     # tbname
        tdSql.error(self.diff_query_form(col='stb1'))   # stbname
        tdSql.error(self.diff_query_form(col='db'))     # datbasename
        # tdSql.error(self.diff_query_form(col=True))     # col is BOOL 1
        # tdSql.error(self.diff_query_form(col='True'))   # col is BOOL 2
        tdSql.error(self.diff_query_form(col='*'))      # col is all col
        tdSql.error("select diff[c1] from t1")          # sql form error 1
        tdSql.error("select diff{c1} from t1")          # sql form error 2
        tdSql.error(self.diff_query_form(col="[c1]"))   # sql form error 3
        # tdSql.error(self.diff_query_form(col="c1, c2")) # sql form error 3
        # tdSql.error(self.diff_query_form(col="c1, 2"))  # sql form error 3
        tdSql.error(self.diff_query_form(alias=", count(c1)"))  # mix with aggregate function 1
        tdSql.error(self.diff_query_form(alias=", avg(c1)"))    # mix with aggregate function 2
        tdSql.error(self.diff_query_form(alias=", min(c1)"))    # mix with select function 1
        tdSql.error(self.diff_query_form(alias=", top(c1, 5)")) # mix with select function 2
        tdSql.error(self.diff_query_form(alias=", spread(c1)")) # mix with calculation function  1
        tdSql.error(self.diff_query_form(alias=", diff(c1)"))   # mix with calculation function  2
        # tdSql.error(self.diff_query_form(alias=" + 2"))         # mix with arithmetic 1
        tdSql.error(self.diff_query_form(alias=" + avg(c1)"))   # mix with arithmetic 2
        tdSql.error(self.diff_query_form(alias=", c2"))         # mix with other 1
        # tdSql.error(self.diff_query_form(table_expr="stb1"))    # select stb directly
        stb_join = {
            "col": "stb1.c1",
            "table_expr": "stb1, stb2",
            "condition": "where stb1.ts=stb2.ts and stb1.st1=stb2.st2 order by stb1.ts"
        }
        tdSql.error(self.diff_query_form(**stb_join))           # stb join
        interval_sql = {
            "condition": "where ts>0 and ts < now interval(1h) fill(next)"
        }
        tdSql.error(self.diff_query_form(**interval_sql))       # interval
        group_normal_col = {
            "table_expr": "t1",
            "condition": "group by c6"
        }
        tdSql.error(self.diff_query_form(**group_normal_col))       # group by normal col
        slimit_soffset_sql = {
            "table_expr": "stb1",
            "condition": "group by tbname slimit 1 soffset 1"
        }
        # tdSql.error(self.diff_query_form(**slimit_soffset_sql))
        order_by_tbname_sql = {
            "table_expr": "stb1",
            "condition": "group by tbname order by tbname"
        }
        tdSql.error(self.diff_query_form(**order_by_tbname_sql))

        pass

    def diff_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 diff_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 diff_support_stable(self):
        tdSql.query(" select diff(1) from stb1 ")
        tdSql.checkRows(229)
        tdSql.checkData(0,0,0)
        tdSql.query("select diff(c1) from stb1 partition by tbname ")
        tdSql.checkRows(199)
        # tdSql.query("select diff(st1) from stb1 partition by tbname")
        # tdSql.checkRows(229)
        tdSql.query("select diff(st1+c1) from stb1 partition by tbname")
        tdSql.checkRows(199)
        tdSql.query("select diff(st1+c1) from stb1 partition by tbname")
        tdSql.checkRows(199)
        tdSql.query("select diff(st1+c1) from stb1 partition by tbname")
        tdSql.checkRows(199)

        # # bug need fix 
        # tdSql.query("select diff(st1+c1) from stb1 partition by tbname slimit 1 ")
        # tdSql.checkRows(19)
        # tdSql.error("select diff(st1+c1) from stb1 partition by tbname limit 1 ")


        # bug need fix 
        tdSql.query("select diff(st1+c1) from stb1 partition by tbname")
        tdSql.checkRows(199)        

        # bug need fix 
        # tdSql.query("select tbname , diff(c1) from stb1 partition by tbname")
        # tdSql.checkRows(199)
        # tdSql.query("select tbname , diff(st1) from stb1 partition by tbname")
        # tdSql.checkRows(199)
        # tdSql.query("select tbname , diff(st1) from stb1 partition by tbname slimit 1")
        # tdSql.checkRows(19)

        # partition by tags 
        # tdSql.query("select st1 , diff(c1) from stb1 partition by st1")
        # tdSql.checkRows(199)
        # tdSql.query("select diff(c1) from stb1 partition by st1")
        # tdSql.checkRows(199)
        # tdSql.query("select st1 , diff(c1) from stb1 partition by st1 slimit 1")
        # tdSql.checkRows(19)
        # tdSql.query("select diff(c1) from stb1 partition by st1 slimit 1")
        # tdSql.checkRows(19)

        # partition by col
        # tdSql.query("select c1 , diff(c1) from stb1 partition by c1")
        # tdSql.checkRows(199)
        # tdSql.query("select diff(c1) from stb1 partition by c1")
        # tdSql.checkRows(41)
        # tdSql.query("select c1 , diff(c1) from stb1 partition by st1 slimit 1")
        # tdSql.checkRows(19)
        # tdSql.query("select diff(c1) from stb1 partition by st1 slimit 1")
        # tdSql.checkRows(19)



    def diff_test_run(self) :
        tdLog.printNoPrefix("==========run test case for diff function==========")
        tbnum = 10
        nowtime = int(round(time.time() * 1000))
        per_table_rows = 10
        self.diff_test_table(tbnum)

        tdLog.printNoPrefix("######## no data test:")
        self.diff_current_query()
        self.diff_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.diff_current_query()
        self.diff_error_query()

        tdLog.printNoPrefix("######## insert data in the range near the max(bigint/double):")
        self.diff_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.diff_current_query()
        self.diff_error_query()

        tdLog.printNoPrefix("######## insert data in the range near the min(bigint/double):")
        self.diff_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.diff_current_query()
        self.diff_error_query()

        tdLog.printNoPrefix("######## insert data without NULL data test:")
        self.diff_test_table(tbnum)
        self.diff_test_data(tbnum, per_table_rows, nowtime)
        self.diff_current_query()
        self.diff_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.diff_current_query()
        self.diff_error_query()



        tdLog.printNoPrefix("######## check after WAL test:")
        tdSql.query("show dnodes")
        index = tdSql.getData(0, 0)
        tdDnodes.stop(index)
        tdDnodes.start(index)
        self.diff_current_query()
        self.diff_error_query()

    def run(self):
        import traceback
        try:
            # run in  develop branch
            self.diff_test_run()
            self.diff_support_stable()
            pass
        except Exception as e:
            traceback.print_exc()
            raise e


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

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