filterOtherTypes.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 taos
from util.log import *
from util.cases import *
from util.sql import *


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

        self.ts = 1537146000000

    def run(self):
        tdSql.prepare()

        print("======= Verify filter for bool, nchar and binary type =========")
        tdLog.debug(
            "create table st(ts timestamp, tbcol1 bool, tbcol2 binary(10), tbcol3 nchar(20)) tags(tagcol1 bool, tagcol2 binary(10), tagcol3 nchar(10))")
        tdSql.execute(
            "create table st(ts timestamp, tbcol1 bool, tbcol2 binary(10), tbcol3 nchar(20)) tags(tagcol1 bool, tagcol2 binary(10), tagcol3 nchar(10))")

        tdSql.execute("create table st1 using st tags(true, 'table1', '水表')")
        for i in range(1, 6):
            tdSql.execute(
                "insert into st1 values(%d, %d, 'taosdata%d', '涛思数据%d')" %
                (self.ts + i, i %
                 2, i, i))

        tdSql.execute("create table st2 using st tags(false, 'table2', '电表')")
        for i in range(6, 11):
            tdSql.execute(
                "insert into st2 values(%d, %d, 'taosdata%d', '涛思数据%d')" %
                (self.ts + i, i %
                 2, i, i))

        # =============Verify stable columns====================
        # > for bool type on column
        tdSql.error("select * from st where tbcol1 > false")

        # >= for bool type on column
        tdSql.error("select * from st where tbcol1 >= false")

        # = for bool type on column
        tdSql.query("select * from st where tbcol1 = false")
        tdSql.checkRows(5)

        # <> for bool type on column
        tdSql.query("select * from st where tbcol1 <> true")
        tdSql.checkRows(5)

        # != for bool type on column
        tdSql.query("select * from st where tbcol1 != true")
        tdSql.checkRows(5)

        # > for bool type on column
        tdSql.error("select * from st where tbcol1 < true")

        # >= for bool type on column
        tdSql.error("select * from st where tbcol1 <= true")

        # % for bool type on column
        tdSql.error("select * from st where tbcol1 like '%'")

        # _ for bool type on column
        tdSql.error("select * from st where tbcol1 like '____'")

        # > for nchar type on column
        tdSql.error("select * from st where tbcol2 > 'taosdata'")

        # >= for nchar type on column
        tdSql.error("select * from st where tbcol2 >= 'taosdata'")

        # = for nchar type on column
        tdSql.query("select * from st where tbcol2 = 'taosdata1'")
        tdSql.checkRows(1)

        # <> for nchar type on column
        tdSql.query("select * from st where tbcol2 <> 'taosdata1'")
        tdSql.checkRows(9)

        # != for nchar type on column
        tdSql.query("select * from st where tbcol2 != 'taosdata1'")
        tdSql.checkRows(9)

        # > for nchar type on column
        tdSql.error("select * from st where tbcol2 < 'taodata'")

        # >= for nchar type on column
        tdSql.error("select * from st where tbcol2 <= 'taodata'")

        # % for nchar type on column case 1
        tdSql.query("select * from st where tbcol2 like '%'")
        tdSql.checkRows(10)

        # % for nchar type on column case 2
        tdSql.query("select * from st where tbcol2 like 'a%'")
        tdSql.checkRows(0)

        # % for nchar type on column case 3
        tdSql.query("select * from st where tbcol2 like 't%_'")
        tdSql.checkRows(10)

        # % for nchar type on column case 4
        tdSql.query("select * from st where tbcol2 like '%1'")
        # tdSql.checkRows(2)

        # _ for nchar type on column case 1
        tdSql.query("select * from st where tbcol2 like '____________'")
        tdSql.checkRows(0)

        # _ for nchar type on column case 2
        tdSql.query("select * from st where tbcol2 like '__________'")
        tdSql.checkRows(1)

        # _ for nchar type on column case 3
        tdSql.query("select * from st where tbcol2 like '_________'")
        tdSql.checkRows(9)

        # _ for nchar type on column case 4
        tdSql.query("select * from st where tbcol2 like 't________'")
        tdSql.checkRows(9)

        # _ for nchar type on column case 5
        tdSql.query("select * from st where tbcol2 like '%________'")
        tdSql.checkRows(10)

        # > for binary type on column
        tdSql.error("select * from st where tbcol3 > '涛思数据'")

        # >= for binary type on column
        tdSql.error("select * from st where tbcol3 >= '涛思数据'")

        # = for binary type on column
        tdSql.query("select * from st where tbcol3 = '涛思数据1'")
        tdSql.checkRows(1)

        # <> for binary type on column
        tdSql.query("select * from st where tbcol3 <> '涛思数据1'")
        tdSql.checkRows(9)

        # != for binary type on column
        tdSql.query("select * from st where tbcol3 != '涛思数据1'")
        tdSql.checkRows(9)

        # > for binary type on column
        tdSql.error("select * from st where tbcol3 < '涛思数据'")

        # >= for binary type on column
        tdSql.error("select * from st where tbcol3 <= '涛思数据'")

        # % for binary type on column case 1
        tdSql.query("select * from st where tbcol3 like '%'")
        tdSql.checkRows(10)

        # % for binary type on column case 2
        tdSql.query("select * from st where tbcol3 like '陶%'")
        tdSql.checkRows(0)

        # % for binary type on column case 3
        tdSql.query("select * from st where tbcol3 like '涛%_'")
        tdSql.checkRows(10)

        # % for binary type on column case 4
        tdSql.query("select * from st where tbcol3 like '%1'")
        tdSql.checkRows(1)

        # _ for binary type on column case 1
        tdSql.query("select * from st where tbcol3 like '_______'")
        tdSql.checkRows(0)

        # _ for binary type on column case 2
        tdSql.query("select * from st where tbcol3 like '______'")
        tdSql.checkRows(1)

        # _ for binary type on column case 2
        tdSql.query("select * from st where tbcol3 like '_____'")
        tdSql.checkRows(9)

        # _ for binary type on column case 3
        tdSql.query("select * from st where tbcol3 like '____'")
        tdSql.checkRows(0)

        # _ for binary type on column case 4
        tdSql.query("select * from st where tbcol3 like 't____'")
        tdSql.checkRows(0)

        # =============Verify stable tags====================
        # > for bool type on tag
        tdSql.error("select * from st where tagcol1 > false")

        # >= for bool type on tag
        tdSql.error("select * from st where tagcol1 >= false")

        # = for bool type on tag
        tdSql.query("select * from st where tagcol1 = false")
        tdSql.checkRows(5)

        # <> for bool type on tag
        tdSql.query("select * from st where tagcol1 <> true")
        tdSql.checkRows(5)

        # != for bool type on tag
        tdSql.query("select * from st where tagcol1 != true")
        tdSql.checkRows(5)

        # > for bool type on tag
        tdSql.error("select * from st where tagcol1 < true")

        # >= for bool type on tag
        tdSql.error("select * from st where tagcol1 <= true")

        # % for bool type on tag
        tdSql.error("select * from st where tagcol1 like '%'")

        # _ for bool type on tag
        tdSql.error("select * from st where tagcol1 like '____'")

        # > for nchar type on tag
        tdSql.query("select * from st where tagcol2 > 'table1'")
        tdSql.checkRows(5)

        # >= for nchar type on tag
        tdSql.query("select * from st where tagcol2 >= 'table1'")
        tdSql.checkRows(10)

        # = for nchar type on tag
        tdSql.query("select * from st where tagcol2 = 'table1'")
        tdSql.checkRows(5)

        # <> for nchar type on tag
        tdSql.query("select * from st where tagcol2 <> 'table1'")
        tdSql.checkRows(5)

        # != for nchar type on tag
        tdSql.query("select * from st where tagcol2 != 'table'")
        tdSql.checkRows(10)

        # > for nchar type on tag
        tdSql.query("select * from st where tagcol2 < 'table'")
        tdSql.checkRows(0)

        # >= for nchar type on tag
        tdSql.query("select * from st where tagcol2 <= 'table'")
        tdSql.checkRows(0)

        # % for nchar type on tag case 1
        tdSql.query("select * from st where tagcol2 like '%'")
        tdSql.checkRows(10)

        # % for nchar type on tag case 2
        tdSql.query("select * from st where tagcol2 like 'a%'")
        tdSql.checkRows(0)

        # % for nchar type on tag case 3
        tdSql.query("select * from st where tagcol2 like 't%_'")
        tdSql.checkRows(10)

        # % for nchar type on tag case 4
        tdSql.query("select * from st where tagcol2 like '%1'")
        tdSql.checkRows(5)

        # _ for nchar type on tag case 1
        tdSql.query("select * from st where tagcol2 like '_______'")
        tdSql.checkRows(0)

        # _ for nchar type on tag case 2
        tdSql.query("select * from st where tagcol2 like '______'")
        tdSql.checkRows(10)

        # _ for nchar type on tag case 3
        tdSql.query("select * from st where tagcol2 like 't_____'")
        tdSql.checkRows(10)

        # _ for nchar type on tag case 4
        tdSql.query("select * from st where tagcol2 like 's________'")
        tdSql.checkRows(0)

        # _ for nchar type on tag case 5
        tdSql.query("select * from st where tagcol2 like '%__'")
        tdSql.checkRows(10)

        # > for binary type on tag
        tdSql.query("select * from st where tagcol3 > '表'")
        tdSql.checkRows(10)

        # >= for binary type on tag
        tdSql.query("select * from st where tagcol3 >= '表'")
        tdSql.checkRows(10)

        # = for binary type on tag
        tdSql.query("select * from st where tagcol3 = '水表'")
        tdSql.checkRows(5)

        # <> for binary type on tag
        tdSql.query("select * from st where tagcol3 <> '水表'")
        tdSql.checkRows(5)

        # != for binary type on tag
        tdSql.query("select * from st where tagcol3 != '水表'")
        tdSql.checkRows(5)

        # > for binary type on tag
        tdSql.query("select * from st where tagcol3 < '水表'")
        tdSql.checkRows(0)

        # >= for binary type on tag
        tdSql.query("select * from st where tagcol3 <= '水表'")
        tdSql.checkRows(5)

        # % for binary type on tag case 1
        tdSql.query("select * from st where tagcol3 like '%'")
        tdSql.checkRows(10)

        # % for binary type on tag case 2
        tdSql.query("select * from st where tagcol3 like '水%'")
        tdSql.checkRows(5)

        # % for binary type on tag case 3
        tdSql.query("select * from st where tagcol3 like '数%_'")
        tdSql.checkRows(0)

        # % for binary type on tag case 4
        tdSql.query("select * from st where tagcol3 like '%表'")
        tdSql.checkRows(10)

        # % for binary type on tag case 5
        tdSql.query("select * from st where tagcol3 like '%据'")
        tdSql.checkRows(0)

        # _ for binary type on tag case 1
        tdSql.query("select * from st where tagcol3 like '__'")
        tdSql.checkRows(10)

        # _ for binary type on tag case 2
        tdSql.query("select * from st where tagcol3 like '水_'")
        tdSql.checkRows(5)

        # _ for binary type on tag case 2
        tdSql.query("select * from st where tagcol3 like '_表'")
        tdSql.checkRows(10)

        # _ for binary type on tag case 3
        tdSql.query("select * from st where tagcol3 like '___'")
        tdSql.checkRows(0)

        # _ for binary type on tag case 4
        tdSql.query("select * from st where tagcol3 like '数_'")
        tdSql.checkRows(0)

        # _ for binary type on tag case 5
        tdSql.query("select * from st where tagcol3 like '_据'")
        tdSql.checkRows(0)

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


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