join.py 15.4 KB
Newer Older
C
cpwu 已提交
1 2 3 4 5 6 7
import datetime

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

C
cpwu 已提交
8
PRIMARY_COL = "ts"
C
cpwu 已提交
9 10 11 12 13 14 15 16 17 18 19 20 21

INT_COL     = "c1"
BINT_COL    = "c2"
SINT_COL    = "c3"
TINT_COL    = "c4"
FLOAT_COL   = "c5"
DOUBLE_COL  = "c6"
BOOL_COL    = "c7"

BINARY_COL  = "c8"
NCHAR_COL   = "c9"
TS_COL      = "c10"

C
cpwu 已提交
22
NUM_COL     = [ INT_COL, BINT_COL, SINT_COL, TINT_COL, FLOAT_COL, DOUBLE_COL, ]
C
cpwu 已提交
23
CHAR_COL    = [ BINARY_COL, NCHAR_COL, ]
C
cpwu 已提交
24 25
BOOLEAN_COL = [ BOOL_COL, ]
TS_TYPE_COL = [ TS_COL, ]
C
cpwu 已提交
26 27 28 29 30 31 32

class TDTestCase:

    def init(self, conn, logSql):
        tdLog.debug(f"start to excute {__file__}")
        tdSql.init(conn.cursor())

C
cpwu 已提交
33 34
    def __query_condition(self,tbname):
        query_condition = []
C
cpwu 已提交
35
        for char_col in CHAR_COL:
C
cpwu 已提交
36
            query_condition.extend(
C
cpwu 已提交
37
                (
C
cpwu 已提交
38 39
                    f"{tbname}.{char_col}",
                    f"upper( {tbname}.{char_col} )",
C
cpwu 已提交
40 41
                )
            )
C
cpwu 已提交
42 43 44 45 46 47 48 49 50 51 52 53
            query_condition.extend( f"cast( {tbname}.{un_char_col} as binary(16) ) " for un_char_col in NUM_COL)
            query_condition.extend( f"cast( {tbname}.{char_col} + {tbname}.{char_col_2} as binary(32) ) " for char_col_2 in CHAR_COL )
            query_condition.extend( f"cast( {tbname}.{char_col} + {tbname}.{un_char_col} as binary(32) ) " for un_char_col in NUM_COL )
        for num_col in NUM_COL:
            query_condition.extend(
                (
                    f"{tbname}.{num_col}",
                    f"sin( {tbname}.{num_col} )"
                )
            )
            query_condition.extend( f"{tbname}.{num_col} + {tbname}.{num_col_1} " for num_col_1 in NUM_COL )

C
cpwu 已提交
54
        query_condition.append(''' "test1234!@#$%^&*():'><?/.,][}{" ''')
C
cpwu 已提交
55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74

        return query_condition

    def __join_condition(self, tb_list, filter=PRIMARY_COL):
        # sourcery skip: flip-comparison
        if 1 == len(tb_list):
            join_filter = f"{tb_list[0]}.{filter} = {tb_list[0]}.{filter} "
        elif 2 == len(tb_list):
            join_filter = f"{tb_list[0]}.{filter} = {tb_list[1]}.{filter} "
        else:
            join_filter = f"{tb_list[0]}.{filter} = {tb_list[1]}.{filter} "
            for i in range(1, len(tb_list)-1 ):
                join_filter += f"and {tb_list[i]}.{filter} = {tb_list[i+1]}.{filter}"

        return join_filter

    def __where_condition(self, col, tbname):
        if col in NUM_COL:
            return f" abs( {tbname}.{col} ) >= 0"
        elif col in CHAR_COL:
C
cpwu 已提交
75
            return f" lower( {tbname}.{col} ) like 'bina%' or lower( {tbname}.{col} ) like '_cha%' "
C
cpwu 已提交
76 77 78
        elif col in BOOLEAN_COL:
            return f" {tbname}.{col} in (false, true)  "
        elif col in TS_TYPE_COL or col in PRIMARY_COL:
C
cpwu 已提交
79
            return f" cast( {tbname}.{col} as binary(16) ) is not null "
C
cpwu 已提交
80 81 82 83 84 85 86 87 88 89 90 91 92
        else:
            return ""

    def __group_condition(self, tbname, col, having = ""):
        return f" group by {tbname}.{col} having {having}" if having else f" group by {tbname}.{col} "

    def __join_check(self, tblist, checkrows, join_flag=True):
        query_conditions = self.__query_condition(tblist[0])
        join_condition = self.__join_condition(tb_list=tblist) if join_flag else " "
        for condition in query_conditions:
            where_condition =  self.__where_condition(col=condition, tbname=tblist[0])
            group_having = self.__group_condition(tbname=tblist[0], col=condition, having=f"{condition} is not null " )
            group_no_having= self.__group_condition(tbname=tblist[0], col=condition )
C
cpwu 已提交
93 94
            groups = ["", group_having, group_no_having]
            for group_condition in groups:
C
cpwu 已提交
95 96 97 98 99
                if where_condition:
                    sql = f" select {condition} from {tblist[0]},{tblist[1]} where {join_condition} and {where_condition} {group_condition} "
                else:
                    sql = f" select {condition} from {tblist[0]},{tblist[1]} where {join_condition}  {group_condition} "

C
cpwu 已提交
100 101
                if not join_flag :
                    tdSql.error(sql=sql)
C
cpwu 已提交
102
                if len(tblist) == 2:
C
cpwu 已提交
103
                    self.__join_current(sql, checkrows + 2 ) if where_condition else self.__join_current(sql, checkrows + 5 )
C
cpwu 已提交
104
                if len(tblist) > 2 or len(tblist) < 1:
C
cpwu 已提交
105
                    tdSql.error(sql=sql)
C
cpwu 已提交
106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138

    # def __join_err_check(self,tbname):
    #     sqls = []

    #     for un_char_col in NUM_COL:
    #         sqls.extend(
    #             (
    #                 f"select length( {un_char_col} ) from {tbname} ",
    #                 f"select length(ceil( {un_char_col} )) from {tbname} ",
    #                 f"select {un_char_col} from {tbname} group by length( {un_char_col} ) ",
    #             )
    #         )

    #         sqls.extend( f"select length( {un_char_col} + {un_char_col_2} ) from {tbname} " for un_char_col_2 in NUM_COL )
    #         sqls.extend( f"select length( {un_char_col} + {ts_col} ) from {tbname} " for ts_col in TS_TYPE_COL )

    #     sqls.extend( f"select {char_col} from {tbname} group by length( {char_col} ) " for char_col in CHAR_COL)
    #     sqls.extend( f"select length( {ts_col} ) from {tbname} " for ts_col in TS_TYPE_COL )
    #     sqls.extend( f"select length( {char_col} + {ts_col} ) from {tbname} " for char_col in NUM_COL for ts_col in TS_TYPE_COL)
    #     sqls.extend( f"select length( {char_col} + {char_col_2} ) from {tbname} " for char_col in CHAR_COL for char_col_2 in CHAR_COL )
    #     sqls.extend( f"select upper({char_col}, 11) from {tbname} " for char_col in CHAR_COL )
    #     sqls.extend( f"select upper({char_col}) from {tbname} interval(2d) sliding(1d)" for char_col in CHAR_COL )
    #     sqls.extend(
    #         (
    #             f"select length() from {tbname} ",
    #             f"select length(*) from {tbname} ",
    #             f"select length(ccccccc) from {tbname} ",
    #             f"select length(111) from {tbname} ",
    #             f"select length(c8, 11) from {tbname} ",
    #         )
    #     )

    #     return sqls
C
cpwu 已提交
139

C
cpwu 已提交
140 141 142 143 144
    def __join_current(self, sql, checkrows):
        tdSql.query(sql=sql)
        tdSql.checkRows(checkrows)


C
cpwu 已提交
145
    def __test_current(self):
C
cpwu 已提交
146
        # sourcery skip: extract-duplicate-method, inline-immediately-returned-variable
C
cpwu 已提交
147
        tdLog.printNoPrefix("==========current sql condition check , must return query ok==========")
C
cpwu 已提交
148 149 150 151 152 153 154 155 156 157 158 159
        tblist_1 = ["ct1", "ct2"]
        self.__join_check(tblist_1, 1)
        tdLog.printNoPrefix(f"==========current sql condition check in {tblist_1} over==========")
        tblist_2 = ["ct2", "ct4"]
        self.__join_check(tblist_2, self.rows - 3)
        tdLog.printNoPrefix(f"==========current sql condition check in {tblist_2} over==========")
        tblist_3 = ["t1", "ct4"]
        self.__join_check(tblist_3, 1)
        tdLog.printNoPrefix(f"==========current sql condition check in {tblist_3} over==========")
        tblist_4 = ["t1", "ct1"]
        self.__join_check(tblist_4, 1)
        tdLog.printNoPrefix(f"==========current sql condition check in {tblist_4} over==========")
C
cpwu 已提交
160 161

    def __test_error(self):
C
cpwu 已提交
162
        # sourcery skip: extract-duplicate-method, move-assign-in-block
C
cpwu 已提交
163
        tdLog.printNoPrefix("==========err sql condition check , must return error==========")
C
cpwu 已提交
164 165 166 167 168 169 170 171 172 173 174 175 176 177 178
        err_list_1 = ["ct1","ct2", "ct4"]
        err_list_2 = ["ct1","ct2", "t1"]
        err_list_3 = ["ct1","ct4", "t1"]
        err_list_4 = ["ct2","ct4", "t1"]
        err_list_5 = ["ct1", "ct2","ct4", "t1"]
        self.__join_check(err_list_1, -1)
        tdLog.printNoPrefix(f"==========err sql condition check in {err_list_1} over==========")
        self.__join_check(err_list_2, -1)
        tdLog.printNoPrefix(f"==========err sql condition check in {err_list_2} over==========")
        self.__join_check(err_list_3, -1)
        tdLog.printNoPrefix(f"==========err sql condition check in {err_list_3} over==========")
        self.__join_check(err_list_4, -1)
        tdLog.printNoPrefix(f"==========err sql condition check in {err_list_4} over==========")
        self.__join_check(err_list_5, -1)
        tdLog.printNoPrefix(f"==========err sql condition check in {err_list_5} over==========")
C
cpwu 已提交
179 180 181 182 183 184 185 186 187 188 189
        self.__join_check(["ct2", "ct4"], -1, join_flag=False)
        tdLog.printNoPrefix("==========err sql condition check in has no join condition over==========")

        tdSql.error( f"select c1, c2 from ct2, ct4 where ct2.{PRIMARY_COL}=ct4.{PRIMARY_COL}" )
        tdSql.error( f"select ct2.c1, ct2.c2 from ct2, ct4 where ct2.{INT_COL}=ct4.{INT_COL}" )
        tdSql.error( f"select ct2.c1, ct2.c2 from ct2, ct4 where ct2.{TS_COL}=ct4.{TS_COL}" )
        tdSql.error( f"select ct2.c1, ct2.c2 from ct2, ct4 where ct2.{PRIMARY_COL}=ct4.{TS_COL}" )
        tdSql.error( f"select ct2.c1, ct1.c2 from ct2, ct4 where ct2.{PRIMARY_COL}=ct4.{PRIMARY_COL}" )
        tdSql.error( f"select ct2.c1, ct4.c2 from ct2, ct4 where ct2.{PRIMARY_COL}=ct4.{PRIMARY_COL} and c1 is not null " )
        tdSql.error( f"select ct2.c1, ct4.c2 from ct2, ct4 where ct2.{PRIMARY_COL}=ct4.{PRIMARY_COL} and ct1.c1 is not null " )

C
cpwu 已提交
190

C
cpwu 已提交
191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224
        tbname = ["ct1", "ct2", "ct4", "t1"]

        for tb in tbname:
            for errsql in self.__length_err_check(tb):
                tdSql.error(sql=errsql)
            tdLog.printNoPrefix(f"==========err sql condition check in {tb} over==========")


    def all_test(self):
        self.__test_current()
        self.__test_error()


    def __create_tb(self):
        tdSql.prepare()

        tdLog.printNoPrefix("==========step1:create table")
        create_stb_sql  =  f'''create table stb1(
                ts timestamp, {INT_COL} int, {BINT_COL} bigint, {SINT_COL} smallint, {TINT_COL} tinyint,
                 {FLOAT_COL} float, {DOUBLE_COL} double, {BOOL_COL} bool,
                 {BINARY_COL} binary(16), {NCHAR_COL} nchar(32), {TS_COL} timestamp
            ) tags (t1 int)
            '''
        create_ntb_sql = f'''create table t1(
                ts timestamp, {INT_COL} int, {BINT_COL} bigint, {SINT_COL} smallint, {TINT_COL} tinyint,
                 {FLOAT_COL} float, {DOUBLE_COL} double, {BOOL_COL} bool,
                 {BINARY_COL} binary(16), {NCHAR_COL} nchar(32), {TS_COL} timestamp
            )
            '''
        tdSql.execute(create_stb_sql)
        tdSql.execute(create_ntb_sql)

        for i in range(4):
            tdSql.execute(f'create table ct{i+1} using stb1 tags ( {i+1} )')
C
cpwu 已提交
225
            { i % 32767 }, { i % 127}, { i * 1.11111 }, { i * 1000.1111 }, { i % 2}
C
cpwu 已提交
226 227

    def __insert_data(self, rows):
C
cpwu 已提交
228
        now_time = int(datetime.datetime.timestamp(datetime.datetime.now()) * 1000)
C
cpwu 已提交
229 230
        for i in range(rows):
            tdSql.execute(
C
cpwu 已提交
231
                f"insert into ct1 values ( { now_time - i * 1000 }, {i}, {11111 * i}, {111 * i % 32767 }, {11 * i % 127}, {1.11*i}, {1100.0011*i}, {i%2}, 'binary{i}', 'nchar_测试_{i}', { now_time + 1 * i } )"
C
cpwu 已提交
232 233
            )
            tdSql.execute(
C
cpwu 已提交
234
                f"insert into ct4 values ( { now_time - i * 7776000000 }, {i}, {11111 * i}, {111 * i % 32767 }, {11 * i % 127}, {1.11*i}, {1100.0011*i}, {i%2}, 'binary{i}', 'nchar_测试_{i}', { now_time + 1 * i } )"
C
cpwu 已提交
235 236
            )
            tdSql.execute(
C
cpwu 已提交
237
                f"insert into ct2 values ( { now_time - i * 7776000000 }, {-i},  {-11111 * i}, {-111 * i % 32767 }, {-11 * i % 127}, {-1.11*i}, {-1100.0011*i}, {i%2}, 'binary{i}', 'nchar_测试_{i}', { now_time + 1 * i } )"
C
cpwu 已提交
238 239 240
            )
        tdSql.execute(
            f'''insert into ct1 values
C
cpwu 已提交
241 242
            ( { now_time - rows * 5 }, 0, 0, 0, 0, 0, 0, 0, 'binary0', 'nchar_测试_0', { now_time + 8 } )
            ( { now_time + 10000 }, { rows }, -99999, -999, -99, -9.99, -99.99, 1, 'binary9', 'nchar_测试_9', { now_time + 9 } )
C
cpwu 已提交
243 244 245 246 247 248
            '''
        )

        tdSql.execute(
            f'''insert into ct4 values
            ( { now_time - rows * 7776000000 }, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL )
C
cpwu 已提交
249
            ( { now_time - rows * 3888000000 + 10800000 }, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL )
C
cpwu 已提交
250 251 252
            ( { now_time +  7776000000 }, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL )
            (
                { now_time + 5184000000}, {pow(2,31)-pow(2,15)}, {pow(2,63)-pow(2,30)}, 32767, 127,
C
cpwu 已提交
253
                { 3.3 * pow(10,38) }, { 1.3 * pow(10,308) }, { rows % 2 }, "binary_limit-1", "nchar_测试_limit-1", { now_time - 86400000}
C
cpwu 已提交
254 255 256
                )
            (
                { now_time + 2592000000 }, {pow(2,31)-pow(2,16)}, {pow(2,63)-pow(2,31)}, 32766, 126,
C
cpwu 已提交
257
                { 3.2 * pow(10,38) }, { 1.2 * pow(10,308) }, { (rows-1) % 2 }, "binary_limit-2", "nchar_测试_limit-2", { now_time - 172800000}
C
cpwu 已提交
258 259 260 261 262 263 264
                )
            '''
        )

        tdSql.execute(
            f'''insert into ct2 values
            ( { now_time - rows * 7776000000 }, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL )
C
cpwu 已提交
265
            ( { now_time - rows * 3888000000 + 10800000 }, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL )
C
cpwu 已提交
266
            ( { now_time + 7776000000 }, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL )
C
cpwu 已提交
267 268
            (
                { now_time + 5184000000 }, { -1 * pow(2,31) + pow(2,15) }, { -1 * pow(2,63) + pow(2,30) }, -32766, -126,
C
cpwu 已提交
269
                { -1 * 3.2 * pow(10,38) }, { -1.2 * pow(10,308) }, { rows % 2 }, "binary_limit-1", "nchar_测试_limit-1", { now_time - 86400000 }
C
cpwu 已提交
270 271 272
                )
            (
                { now_time + 2592000000 }, { -1 * pow(2,31) + pow(2,16) }, { -1 * pow(2,63) + pow(2,31) }, -32767, -127,
C
cpwu 已提交
273
                { - 3.3 * pow(10,38) }, { -1.3 * pow(10,308) }, { (rows-1) % 2 }, "binary_limit-2", "nchar_测试_limit-2", { now_time - 172800000 }
C
cpwu 已提交
274 275 276 277 278 279
                )
            '''
        )

        for i in range(rows):
            insert_data = f'''insert into t1 values
C
cpwu 已提交
280
                ( { now_time - i * 3600000 }, {i}, {i * 11111}, { i % 32767 }, { i % 127}, { i * 1.11111 }, { i * 1000.1111 }, { i % 2},
C
cpwu 已提交
281
                "binary_{i}", "nchar_测试_{i}", { now_time - 1000 * i } )
C
cpwu 已提交
282 283 284 285 286
                '''
            tdSql.execute(insert_data)
        tdSql.execute(
            f'''insert into t1 values
            ( { now_time + 10800000 }, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL )
C
cpwu 已提交
287
            ( { now_time - (( rows // 2 ) * 60 + 30) * 60000 }, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL )
C
cpwu 已提交
288 289 290
            ( { now_time - rows * 3600000 }, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL )
            ( { now_time + 7200000 }, { pow(2,31) - pow(2,15) }, { pow(2,63) - pow(2,30) }, 32767, 127,
                { 3.3 * pow(10,38) }, { 1.3 * pow(10,308) }, { rows % 2 },
C
cpwu 已提交
291
                "binary_limit-1", "nchar_测试_limit-1", { now_time - 86400000 }
C
cpwu 已提交
292 293 294 295
                )
            (
                { now_time + 3600000 } , { pow(2,31) - pow(2,16) }, { pow(2,63) - pow(2,31) }, 32766, 126,
                { 3.2 * pow(10,38) }, { 1.2 * pow(10,308) }, { (rows-1) % 2 },
C
cpwu 已提交
296
                "binary_limit-2", "nchar_测试_limit-2", { now_time - 172800000 }
C
cpwu 已提交
297 298 299 300 301 302 303 304 305 306 307 308
                )
            '''
        )


    def run(self):
        tdSql.prepare()

        tdLog.printNoPrefix("==========step1:create table")
        self.__create_tb()

        tdLog.printNoPrefix("==========step2:insert data")
C
cpwu 已提交
309 310
        self.rows = 10
        self.__insert_data(self.rows)
C
cpwu 已提交
311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328

        tdLog.printNoPrefix("==========step3:all check")
        self.all_test()

        # tdDnodes.stop(1)
        # tdDnodes.start(1)

        # tdSql.execute("use db")

        # tdLog.printNoPrefix("==========step4:after wal, all check again ")
        # self.all_test()

    def stop(self):
        tdSql.close()
        tdLog.success(f"{__file__} successfully executed")

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