def checkTbMemDiskMerge(self):
tb_name = tdCom.getLongName(8, "letters")
tdSql.execute(f'CREATE TABLE {tb_name} (ts timestamp, c1 int, c2 int)')
tdSql.execute(
f'insert into {tb_name} values ("2021-01-01 12:00:00.000", 1, 1)')
tdSql.execute(
f'insert into {tb_name} values ("2021-01-03 12:00:00.000", 3, 3)')
tdCom.restartTaosd()
tdSql.execute(
f'insert into {tb_name} values ("2021-01-02 12:00:00.000", Null, 2)'
)
tdSql.execute(
f'insert into {tb_name} values ("2021-01-04 12:00:00.000", Null, 4)'
)
query_sql = f'select * from {tb_name}'
res1 = tdSql.query(query_sql, True)
tdCom.restartTaosd()
res2 = tdSql.query(query_sql, True)
for i in range(4):
tdSql.checkEqual(res1[i], res2[i])
def queryFullColType(self, tb_name):
## ts
query_sql = f'select * from {tb_name} where ts > "2021-01-11 12:00:00" or ts < "2021-01-13 12:00:00"'
tdSql.query(query_sql)
tdSql.checkRows(11)
tdSql.checkEqual(self.queryLastC10(query_sql), 11)
## != or
query_sql = f'select * from {tb_name} where c1 != 1 or c2 = 3'
tdSql.query(query_sql)
tdSql.checkRows(3)
tdSql.checkEqual(self.queryLastC10(query_sql), 11)
## <> or
query_sql = f'select * from {tb_name} where c1 <> 1 or c3 = 3'
tdSql.query(query_sql)
tdSql.checkRows(1)
tdSql.checkEqual(self.queryLastC10(query_sql), 2)
## >= or
query_sql = f'select * from {tb_name} where c1 >= 2 or c3 = 4'
tdSql.query(query_sql)
tdSql.checkRows(2)
tdSql.checkEqual(self.queryLastC10(query_sql), 4)
## <= or
query_sql = f'select * from {tb_name} where c1 <= 1 or c3 = 4'
tdSql.query(query_sql)
tdSql.checkRows(10)
tdSql.checkEqual(self.queryLastC10(query_sql), 11)
## <> or is Null
query_sql = f'select * from {tb_name} where c1 <> 1 or c7 is Null'
tdSql.query(query_sql)
tdSql.checkRows(2)
tdSql.checkEqual(self.queryLastC10(query_sql), 11)
## > or is not Null
query_sql = f'select * from {tb_name} where c2 > 2 or c8 is not Null'
tdSql.query(query_sql)
tdSql.checkRows(11)
tdSql.checkEqual(self.queryLastC10(query_sql), 11)
## > or < or >= or <= or != or <> or = Null
query_sql = f'select * from {tb_name} where c1 > 1 or c2 < 2 or c3 >= 4 or c4 <= 2 or c5 != 1.1 or c6 <> 1.1 or c7 is Null'
tdSql.query(query_sql)
tdSql.checkRows(8)
tdSql.checkEqual(self.queryLastC10(query_sql), 11)
## tiny small int big or
query_sql = f'select * from {tb_name} where c1 = 2 or c2 = 3 or c3 = 4 or c4 = 5'
tdSql.query(query_sql)
tdSql.checkRows(5)
tdSql.checkEqual(self.queryLastC10(query_sql), 11)
## float double binary nchar bool or
query_sql = f'select * from {tb_name} where c5=6.6 or c6=7.7 or c7="binary8" or c8="nchar9" or c9=false'
tdSql.query(query_sql)
tdSql.checkRows(6)
tdSql.checkEqual(self.queryLastC10(query_sql), 11)
## all types or
query_sql = f'select * from {tb_name} where c1=2 or c2=3 or c3=4 or c4=5 or c5=6.6 or c6=7.7 or c7="binary8" or c8="nchar9" or c9=false'
tdSql.query(query_sql)
tdSql.checkRows(10)
tdSql.checkEqual(self.queryLastC10(query_sql), 11)
def queryMultiTb(self, tb_name):
## select from (condition_A or condition_B)
query_sql = f'select c10 from (select * from {tb_name} where c1 >1 or c2 >=3)'
res = tdSql.query(query_sql, True)
tdSql.checkRows(3)
tdSql.checkEqual(int(res[2][0]), 11)
## select from (condition_A or condition_B) where condition_A or condition_B
query_sql = f'select c10 from (select * from {tb_name} where c1 >1 or c2 >=3) where c1 =2 or c4 = 2'
res = tdSql.query(query_sql, True)
tdSql.checkRows(2)
tdSql.checkEqual(int(res[1][0]), 3)
## select from (condition_A or condition_B and like and in) where condition_A or condition_B or like and in
query_sql = f'select c10 from (select * from {tb_name} where c1 >1 or c2 = 2 and c7 like "binar_" and c4 in (3, 5)) where c1 != 2 or c3 = 1 or c8 like "ncha_" and c9 in (true)'
res = tdSql.query(query_sql, True)
tdSql.checkRows(7)
tdSql.checkEqual(int(res[6][0]), 10)
## select count avg sum from (condition_A or condition_B and like and in) where condition_A or condition_B or like and in interval
query_sql = f'select count(*), avg(c6), sum(c3) from (select * from {tb_name} where c1 >1 or c2 = 2 and c7 like "binar_" and c4 in (3, 5)) where c1 != 2 or c3 = 1 or c8 like "ncha_" and c9 in (true) interval(8d)'
res = tdSql.query(query_sql, True)
tdSql.checkRows(3)
tdSql.checkEqual(int(res[0][1]), 3)
tdSql.checkEqual(int(res[0][2]), 1)
tdSql.checkEqual(int(res[0][3]), 10)
tdSql.checkEqual(int(res[1][1]), 3)
tdSql.checkEqual(int(res[1][2]), 3)
tdSql.checkEqual(int(res[1][3]), 3)
tdSql.checkEqual(int(res[2][1]), 1)
tdSql.checkEqual(int(res[2][2]), 1)
tdSql.checkEqual(int(res[2][3]), 1)
## cname
query_sql = f'select c10 from (select * from {tb_name} where c1 >1 or c2 = 2 and c7 like "binar_" and c4 in (3, 5)) a where a.c1 != 2 or a.c3 = 1 or a.c8 like "ncha_" and a.c9 in (true)'
res = tdSql.query(query_sql, True)
tdSql.checkRows(7)
tdSql.checkEqual(int(res[6][0]), 10)
## multi cname
query_sql = f'select b.c10 from (select * from {tb_name} where c9 = true or c2 = 2) a, (select * from {tb_name} where c7 like "binar_" or c4 in (3, 5)) b where a.ts = b.ts'
res = tdSql.query(query_sql, True)
tdSql.checkRows(10)
tdSql.checkEqual(int(res[9][0]), 10)
def queryPreCal(self, tb_name):
## avg sum condition_A or condition_B
query_sql = f'select avg(c3), sum(c3) from {tb_name} where c10 = 5 or c8 is Null'
res = tdSql.query(query_sql, True)[0]
tdSql.checkEqual(int(res[0]), 3)
tdSql.checkEqual(int(res[1]), 6)
## avg sum condition_A or condition_B or condition_C
query_sql = f'select avg(c3), sum(c3) from {tb_name} where c10 = 4 or c8 is Null or c9 = false '
res = tdSql.query(query_sql, True)[0]
tdSql.checkEqual(int(res[0]), 2)
tdSql.checkEqual(int(res[1]), 6)
## count avg sum condition_A or condition_B or condition_C interval
query_sql = f'select count(*), avg(c3), sum(c3) from {tb_name} where c10 = 4 or c8 is Null or c9 = false interval(16d)'
res = tdSql.query(query_sql, True)
tdSql.checkRows(2)
tdSql.checkEqual(int(res[0][1]), 1)
tdSql.checkEqual(int(res[0][2]), 4)
tdSql.checkEqual(int(res[0][3]), 4)
tdSql.checkEqual(int(res[1][1]), 2)
tdSql.checkEqual(int(res[1][2]), 1)
tdSql.checkEqual(int(res[1][3]), 2)
## count avg sum condition_A or condition_B or in and like or condition_C interval
query_sql = f'select count(*), sum(c3) from {tb_name} where c10 = 4 or c8 is Null or c2 in (1, 2) and c7 like "binary_" or c1 <> 1 interval(16d)'
res = tdSql.query(query_sql, True)
tdSql.checkRows(2)
tdSql.checkEqual(int(res[0][1]), 2)
tdSql.checkEqual(int(res[0][2]), 5)
tdSql.checkEqual(int(res[1][1]), 2)
tdSql.checkEqual(int(res[1][2]), 2)
def queryMultiLike(self, tb_name):
## like and like
query_sql = f'select * from {tb_name} where c7 like "bi%" and c8 like ("ncha_")'
tdSql.query(query_sql)
tdSql.checkRows(9)
tdSql.checkEqual(self.queryLastC10(query_sql), 10)
## like or like
query_sql = f'select * from {tb_name} where c7 like "binar12345" or c8 like "nchar_"'
tdSql.query(query_sql)
tdSql.checkRows(1)
tdSql.checkEqual(self.queryLastC10(query_sql), 9)
## like and like or condition_A
query_sql = f'select * from {tb_name} where c7 like "binary_" and c8 like "ncha_" or c1 != 1'
tdSql.query(query_sql)
tdSql.checkRows(2)
tdSql.checkEqual(self.queryLastC10(query_sql), 8)
## like or like and condition_A
query_sql = f'select * from {tb_name} where c7 like ("binar_") or c8 like ("nchar_") and c10 != 8'
tdSql.query(query_sql)
tdSql.checkRows(9)
tdSql.checkEqual(self.queryLastC10(query_sql), 10)
## like or like or condition_A
query_sql = f'select * from {tb_name} where c7 like ("binary_") or c8 like ("nchar_") or c10 = 6'
tdSql.query(query_sql)
tdSql.checkRows(3)
tdSql.checkEqual(self.queryLastC10(query_sql), 9)
## like or like or like or like
query_sql = f'select * from {tb_name} where c7 like ("binary_") or c8 like ("nchar_") or c10 = 6 or c7 is Null'
tdSql.query(query_sql)
tdSql.checkRows(4)
tdSql.checkEqual(self.queryLastC10(query_sql), 11)
## like or like and like or like
query_sql = f'select * from {tb_name} where c7 like ("binary_") or c8 like ("ncha_") and c10 = 6 or c10 = 9'
tdSql.query(query_sql)
tdSql.checkRows(3)
tdSql.checkEqual(self.queryLastC10(query_sql), 9)
## condition_A or like or condition_B and like
query_sql = f'select * from {tb_name} where c1 = 2 or c7 like "binary_" or c10 = 3 and c8 like "ncha%"'
tdSql.query(query_sql)
tdSql.checkRows(3)
tdSql.checkEqual(self.queryLastC10(query_sql), 8)
## like and condition_A or like and like and condition_B
query_sql = f'select * from {tb_name} where c7 like "bin%" and c2 = 3 or c10 <= 4 and c7 like "binar_" and c8 like "ncha_"'
tdSql.query(query_sql)
tdSql.checkRows(4)
tdSql.checkEqual(self.queryLastC10(query_sql), 4)
## (like and condition_A or like) and like and condition_B
query_sql = f'select * from {tb_name} where (c7 like "bin%" and c2 = 3 or c8 like "nchar_") and c7 like "binar_" and c9 != false'
tdSql.query(query_sql)
tdSql.checkRows(2)
tdSql.checkEqual(self.queryLastC10(query_sql), 9)
def queryMultiIn(self, tb_name):
## in and in
query_sql = f'select * from {tb_name} where c7 in ("binary") and c8 in ("nchar")'
tdSql.query(query_sql)
tdSql.checkRows(8)
tdSql.checkEqual(self.queryLastC10(query_sql), 10)
## in or in
query_sql = f'select * from {tb_name} where c1 in (2, 4) or c2 in (1, 4)'
tdSql.query(query_sql)
tdSql.checkRows(2)
tdSql.checkEqual(self.queryLastC10(query_sql), 2)
## in and in or condition_A
query_sql = f'select * from {tb_name} where c7 in ("binary") and c8 in ("nchar") or c10 != 10'
tdSql.query(query_sql)
tdSql.checkRows(11)
tdSql.checkEqual(self.queryLastC10(query_sql), 11)
## in or in and condition_A
query_sql = f'select * from {tb_name} where c7 in ("binary") or c8 in ("nchar") and c10 != 10'
tdSql.query(query_sql)
tdSql.checkRows(10)
tdSql.checkEqual(self.queryLastC10(query_sql), 10)
## in or in or condition_A
query_sql = f'select * from {tb_name} where c1 in (2, 4) or c2 in (3, 4) or c9 != true'
tdSql.query(query_sql)
tdSql.checkRows(4)
tdSql.checkEqual(self.queryLastC10(query_sql), 11)
## in or in or in or in
query_sql = f'select * from {tb_name} where c1 in (2, 4) or c2 in (3, 4) or c9 in (false) or c10 in (5, 6 ,22)'
tdSql.query(query_sql)
tdSql.checkRows(6)
tdSql.checkEqual(self.queryLastC10(query_sql), 11)
## in or in and in or in
query_sql = f'select * from {tb_name} where c1 in (2, 4) or c2 in (3, 4) and c9 in (false) or c10 in (5, 6 ,22)'
tdSql.query(query_sql)
tdSql.checkRows(4)
tdSql.checkEqual(self.queryLastC10(query_sql), 11)
## condition_A or in or condition_B and in
query_sql = f'select * from {tb_name} where c1 = 2 or c2 in (2, 4) and c9 = false or c10 in (6 ,22)'
tdSql.query(query_sql)
tdSql.checkRows(3)
tdSql.checkEqual(self.queryLastC10(query_sql), 10)
## in and condition_A or in and in and condition_B
query_sql = f'select * from {tb_name} where c1 in (2, 3) and c2 <> 3 or c10 <= 4 and c10 in (4 ,22) and c9 != false'
tdSql.query(query_sql)
tdSql.checkRows(2)
tdSql.checkEqual(self.queryLastC10(query_sql), 4)
## (in and condition_A or in) and in and condition_B
query_sql = f'select * from {tb_name} where (c1 in (2, 3) and c2 <> 3 or c10 <= 4) and c10 in (4 ,22) and c9 != false'
tdSql.query(query_sql)
tdSql.checkRows(1)
tdSql.checkEqual(self.queryLastC10(query_sql), 4)
def queryMultiExpression(self, tb_name):
## condition_A and condition_B or condition_C (> < >=)
query_sql = f'select * from {tb_name} where c1 > 2 and c2 < 4 or c3 >= 4'
tdSql.query(query_sql)
tdSql.checkRows(2)
tdSql.checkEqual(self.queryLastC10(query_sql), 5)
## (condition_A and condition_B) or condition_C (<= != <>)
query_sql = f'select * from {tb_name} where (c1 <= 1 and c2 != 2) or c4 <> 3'
tdSql.query(query_sql)
tdSql.checkRows(4)
tdSql.checkEqual(self.queryLastC10(query_sql), 11)
## condition_A and (condition_B or condition_C) (Null not Null)
query_sql = f'select * from {tb_name} where c1 is not Null and (c6 = 7.7 or c8 is Null)'
tdSql.query(query_sql)
tdSql.checkRows(2)
tdSql.checkEqual(self.queryLastC10(query_sql), 11)
## condition_A or condition_B and condition_C (> < >=)
query_sql = f'select * from {tb_name} where c1 > 2 or c2 < 4 and c3 >= 4'
tdSql.query(query_sql)
tdSql.checkRows(2)
tdSql.checkEqual(self.queryLastC10(query_sql), 5)
## (condition_A or condition_B) and condition_C (<= != <>)
query_sql = f'select * from {tb_name} where (c1 <= 1 or c2 != 2) and c4 <> 3'
tdSql.query(query_sql)
tdSql.checkRows(2)
tdSql.checkEqual(self.queryLastC10(query_sql), 5)
## condition_A or (condition_B and condition_C) (Null not Null)
query_sql = f'select * from {tb_name} where c6 >= 7.7 or (c1 is not Null and c3 =5)'
tdSql.query(query_sql)
tdSql.checkRows(2)
tdSql.checkEqual(self.queryLastC10(query_sql), 7)
## condition_A or (condition_B and condition_C) or condition_D (> != < Null)
query_sql = f'select * from {tb_name} where c1 != 1 or (c2 >2 and c3 < 1) or c7 is Null'
tdSql.query(query_sql)
tdSql.checkRows(2)
tdSql.checkEqual(self.queryLastC10(query_sql), 11)
## condition_A and (condition_B or condition_C) and condition_D (>= = <= not Null)
query_sql = f'select * from {tb_name} where c4 >= 4 and (c1 = 2 or c5 <= 1.1) and c7 is not Null'
tdSql.query(query_sql)
tdSql.checkRows(1)
tdSql.checkEqual(self.queryLastC10(query_sql), 5)
## (condition_A and condition_B) or (condition_C or condition_D) (Null >= > =)
query_sql = f'select * from {tb_name} where (c8 is Null and c1 >= 1) or (c3 > 3 or c4 =2)'
tdSql.query(query_sql)
tdSql.checkRows(4)
tdSql.checkEqual(self.queryLastC10(query_sql), 11)
## (condition_A or condition_B) or condition_C or (condition_D and condition_E) (>= <= = not Null <>)
query_sql = f'select * from {tb_name} where (c1 >= 2 or c2 <= 1) or c3 = 4 or (c7 is not Null and c6 <> 1.1)'
tdSql.query(query_sql)
tdSql.checkRows(4)
tdSql.checkEqual(self.queryLastC10(query_sql), 7)
## condition_A or (condition_B and condition_C) or (condition_D and condition_E) and condition_F
query_sql = f'select * from {tb_name} where c1 != 1 or (c2 <= 1 and c3 <4) or (c3 >= 4 or c7 is not Null) and c9 <> true'
tdSql.query(query_sql)
tdSql.checkRows(3)
tdSql.checkEqual(self.queryLastC10(query_sql), 10)
## (condition_A or (condition_B and condition_C) or (condition_D and condition_E)) and condition_F
query_sql = f'select * from {tb_name} where (c1 != 1 or (c2 <= 2 and c3 >= 4) or (c3 >= 4 or c7 is not Null)) and c9 != false'
tdSql.query(query_sql)
tdSql.checkRows(9)
tdSql.checkEqual(self.queryLastC10(query_sql), 9)
## (condition_A or condition_B) or (condition_C or condition_D) and (condition_E or condition_F or condition_G)
query_sql = f'select * from {tb_name} where c1 != 1 or (c2 <= 3 and c3 > 4) and c3 <= 5 and (c7 is not Null and c9 != false)'
tdSql.query(query_sql)
tdSql.checkRows(2)
tdSql.checkEqual(self.queryLastC10(query_sql), 5)