lower.py 11.0 KB
Newer Older
C
cpwu 已提交
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61
from util.log import *
from util.sql import *
from util.cases import *
from util.dnodes import *


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"

UN_CHAR_COL = [INT_COL, BINT_COL, SINT_COL, TINT_COL, FLOAT_COL, DOUBLE_COL, BOOL_COL, ]
CHAR_COL    = [ BINARY_COL, NCHAR_COL, ]
TS_TYPE_COL = [TS_COL]

class TDTestCase:

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

    def __lower_condition(self):
        lower_condition = []
        for char_col in CHAR_COL:
            lower_condition.extend(
                (
                    char_col,
                    f"upper( {char_col} )",
                )
            )
            lower_condition.extend(f"cast( {un_char_col} as binary(16) ) " for un_char_col in UN_CHAR_COL)
            lower_condition.extend( f"cast( {char_col} + {char_col_2} as binary(32) ) " for char_col_2 in CHAR_COL )
            lower_condition.extend( f"cast( {char_col} + {un_char_col} as binary(32) ) " for un_char_col in UN_CHAR_COL )

        lower_condition.append('''"test1234!@#$%^&*():'><?/.,][}{"''')

        return lower_condition

    def __where_condition(self, col):
        # return f" where count({col}) > 0 "
        return ""

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

    def __lower_current_check(self, tbname):
        lower_condition = self.__lower_condition()
        for condition in lower_condition:
            where_condition = self.__where_condition(condition)
            group_having = self.__group_condition(condition, having=f"{condition} is not null " )
            group_no_having= self.__group_condition(condition )
            groups = ["", group_having, group_no_having]

            for group_condition in groups:
C
cpwu 已提交
62
                tdSql.query(f"select lower( {condition} ), {condition} from {tbname} {where_condition}  {group_condition}")
C
cpwu 已提交
63
                for i in range(tdSql.queryRows):
C
cpwu 已提交
64
                    tdSql.checkData(i, 0, str(tdSql.getData(i, 1)).lower() ) if tdSql.getData(i, 1) else tdSql.checkData(i, 0, None)
C
cpwu 已提交
65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98

    def __lower_err_check(self,tbname):
        sqls = []

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

            sqls.extend( f"select lower( {un_char_col} + {un_char_col_2} ) from {tbname} " for un_char_col_2 in UN_CHAR_COL )
            sqls.extend( f"select lower( {un_char_col} + {ts_col} ) from {tbname} " for ts_col in TS_TYPE_COL )

        sqls.extend( f"select {char_col} from {tbname} group by lower( {char_col} ) " for char_col in CHAR_COL)
        sqls.extend( f"select lower( {ts_col} ) from {tbname} " for ts_col in TS_TYPE_COL )
        sqls.extend( f"select lower( {char_col} + {ts_col} ) from {tbname} " for char_col in UN_CHAR_COL for ts_col in TS_TYPE_COL)
        sqls.extend( f"select lower( {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 lower() from {tbname} ",
                f"select lower(*) from {tbname} ",
                f"select lower(ccccccc) from {tbname} ",
                f"select lower(111) from {tbname} ",
                f"select lower(c8, 11) from {tbname} ",
            )
        )

        return sqls

C
cpwu 已提交
99
    def __test_current(self, dbname="db"):
C
cpwu 已提交
100
        tdLog.printNoPrefix("==========current sql condition check , must return query ok==========")
C
cpwu 已提交
101
        tbname = [f"{dbname}.ct1", f"{dbname}.ct2", f"{dbname}.ct4", f"{dbname}.t1", f"{dbname}.stb1"]
C
cpwu 已提交
102 103 104 105
        for tb in tbname:
            self.__lower_current_check(tb)
            tdLog.printNoPrefix(f"==========current sql condition check in {tb} over==========")

C
cpwu 已提交
106
    def __test_error(self, dbname="db"):
C
cpwu 已提交
107
        tdLog.printNoPrefix("==========err sql condition check , must return error==========")
C
cpwu 已提交
108
        tbname = [f"{dbname}.ct1", f"{dbname}.ct2", f"{dbname}.ct4", f"{dbname}.t1", f"{dbname}.stb1"]
C
cpwu 已提交
109 110 111 112 113 114 115

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


C
cpwu 已提交
116 117 118
    def all_test(self, dbname="db"):
        self.__test_current(dbname)
        self.__test_error(dbname)
C
cpwu 已提交
119

C
cpwu 已提交
120
    def __create_tb(self, dbname="db"):
C
cpwu 已提交
121 122

        tdLog.printNoPrefix("==========step1:create table")
C
cpwu 已提交
123
        create_stb_sql  =  f'''create table {dbname}.stb1(
C
cpwu 已提交
124 125 126
                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
C
cpwu 已提交
127
            ) tags (tag1 int)
C
cpwu 已提交
128
            '''
C
cpwu 已提交
129
        create_ntb_sql = f'''create table {dbname}.t1(
C
cpwu 已提交
130 131 132 133 134 135 136 137 138
                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):
C
cpwu 已提交
139
            tdSql.execute(f'create table {dbname}.ct{i+1} using {dbname}.stb1 tags ( {i+1} )')
C
cpwu 已提交
140

C
cpwu 已提交
141
    def __insert_data(self, rows, dbname="db"):
C
cpwu 已提交
142 143
        now_time = int(datetime.datetime.timestamp(datetime.datetime.now()) * 1000)
        for i in range(rows):
C
cpwu 已提交
144
            tdSql.execute(
C
cpwu 已提交
145
                f"insert into {dbname}.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 已提交
146 147
            )
            tdSql.execute(
C
cpwu 已提交
148
                f"insert into {dbname}.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 已提交
149 150
            )
            tdSql.execute(
C
cpwu 已提交
151
                f"insert into {dbname}.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 已提交
152 153
            )
        tdSql.execute(
C
cpwu 已提交
154 155 156
            f'''insert into {dbname}.ct1 values
            ( { 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 已提交
157 158 159 160
            '''
        )

        tdSql.execute(
C
cpwu 已提交
161
            f'''insert into {dbname}.ct4 values
C
cpwu 已提交
162
            ( { now_time - rows * 7776000000 }, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL )
C
cpwu 已提交
163
            ( { now_time - rows * 3888000000 + 10800000 }, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL )
C
cpwu 已提交
164
            ( { now_time +  7776000000 }, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL )
C
cpwu 已提交
165
            (
C
cpwu 已提交
166
                { now_time + 5184000000}, {pow(2,31)-pow(2,15)}, {pow(2,63)-pow(2,30)}, 32767, 127,
C
cpwu 已提交
167
                { 3.3 * pow(10,38) }, { 1.3 * pow(10,308) }, { rows % 2 }, "binary_limit-1", "nchar_测试_limit-1", { now_time - 86400000}
C
cpwu 已提交
168 169
                )
            (
C
cpwu 已提交
170
                { now_time + 2592000000 }, {pow(2,31)-pow(2,16)}, {pow(2,63)-pow(2,31)}, 32766, 126,
C
cpwu 已提交
171
                { 3.2 * pow(10,38) }, { 1.2 * pow(10,308) }, { (rows-1) % 2 }, "binary_limit-2", "nchar_测试_limit-2", { now_time - 172800000}
C
cpwu 已提交
172 173 174 175 176
                )
            '''
        )

        tdSql.execute(
C
cpwu 已提交
177
            f'''insert into {dbname}.ct2 values
C
cpwu 已提交
178
            ( { now_time - rows * 7776000000 }, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL )
C
cpwu 已提交
179
            ( { now_time - rows * 3888000000 + 10800000 }, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL )
C
cpwu 已提交
180
            ( { now_time + 7776000000 }, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL )
C
cpwu 已提交
181
            (
C
cpwu 已提交
182
                { now_time + 5184000000 }, { -1 * pow(2,31) + pow(2,15) }, { -1 * pow(2,63) + pow(2,30) }, -32766, -126,
C
cpwu 已提交
183
                { -1 * 3.2 * pow(10,38) }, { -1.2 * pow(10,308) }, { rows % 2 }, "binary_limit-1", "nchar_测试_limit-1", { now_time - 86400000 }
C
cpwu 已提交
184 185
                )
            (
C
cpwu 已提交
186
                { now_time + 2592000000 }, { -1 * pow(2,31) + pow(2,16) }, { -1 * pow(2,63) + pow(2,31) }, -32767, -127,
C
cpwu 已提交
187
                { - 3.3 * pow(10,38) }, { -1.3 * pow(10,308) }, { (rows-1) % 2 }, "binary_limit-2", "nchar_测试_limit-2", { now_time - 172800000 }
C
cpwu 已提交
188 189 190 191 192
                )
            '''
        )

        for i in range(rows):
C
cpwu 已提交
193
            insert_data = f'''insert into {dbname}.t1 values
C
cpwu 已提交
194
                ( { now_time - i * 3600000 }, {i}, {i * 11111}, { i % 32767 }, { i % 127}, { i * 1.11111 }, { i * 1000.1111 }, { i % 2},
C
cpwu 已提交
195
                "binary_{i}", "nchar_测试_{i}", { now_time - 1000 * i } )
C
cpwu 已提交
196 197 198
                '''
            tdSql.execute(insert_data)
        tdSql.execute(
C
cpwu 已提交
199
            f'''insert into {dbname}.t1 values
C
cpwu 已提交
200
            ( { now_time + 10800000 }, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL )
C
cpwu 已提交
201
            ( { now_time - (( rows // 2 ) * 60 + 30) * 60000 }, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL )
C
cpwu 已提交
202 203
            ( { 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,
C
cpwu 已提交
204
                { 3.3 * pow(10,38) }, { 1.3 * pow(10,308) }, { rows % 2 },
C
cpwu 已提交
205
                "binary_limit-1", "nchar_测试_limit-1", { now_time - 86400000 }
C
cpwu 已提交
206 207
                )
            (
C
cpwu 已提交
208
                { now_time + 3600000 } , { pow(2,31) - pow(2,16) }, { pow(2,63) - pow(2,31) }, 32766, 126,
C
cpwu 已提交
209
                { 3.2 * pow(10,38) }, { 1.2 * pow(10,308) }, { (rows-1) % 2 },
C
cpwu 已提交
210
                "binary_limit-2", "nchar_测试_limit-2", { now_time - 172800000 }
C
cpwu 已提交
211 212 213 214 215 216 217 218 219 220 221 222
                )
            '''
        )


    def run(self):
        tdSql.prepare()

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

        tdLog.printNoPrefix("==========step2:insert data")
C
cpwu 已提交
223
        self.__insert_data(10)
C
cpwu 已提交
224 225 226 227

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

C
cpwu 已提交
228
        tdSql.execute("flush database db")
C
cpwu 已提交
229

C
cpwu 已提交
230 231
        tdLog.printNoPrefix("==========step4:after wal, all check again ")
        self.all_test()
C
cpwu 已提交
232 233 234 235 236 237 238

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

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