def queryGroupTbname(self):
'''
select a1,a2...a10 from stb where tbname in (t1,t2,...t10) and ts...
'''
tdCom.cleanTb()
table_name = tdCom.getLongName(8, "letters_mixed")
tbname_list = list(map(lambda x: f'table_name_sub{x}', range(1, 11)))
tb_str = ""
for tbname in tbname_list:
globals()[tbname] = tdCom.getLongName(8, "letters_mixed")
tdSql.execute(
f'CREATE TABLE {table_name} (ts timestamp, {table_name_sub1} tinyint, \
{table_name_sub2} smallint, {table_name_sub3} int, {table_name_sub4} bigint, \
{table_name_sub5} float, {table_name_sub6} double, {table_name_sub7} binary(20),\
{table_name_sub8} nchar(20), {table_name_sub9} bool) tags ({table_name_sub10} binary(20))'
)
for tbname in tbname_list:
tb_str += tbname
tdSql.execute(
f'create table {globals()[tbname]} using {table_name} tags ("{globals()[tbname]}")'
)
for i in range(10):
for tbname in tbname_list:
tdSql.execute(
f'insert into {globals()[tbname]} values (now, 1, 2, 3, 4, 1.1, 2.2, "{globals()[tbname]}", "{globals()[tbname]}", True)'
)
for i in range(100):
tdSql.query(
f'select {table_name_sub1},{table_name_sub2},{table_name_sub3},{table_name_sub4},{table_name_sub5},{table_name_sub6},{table_name_sub7},{table_name_sub8},{table_name_sub9} from {table_name} where tbname in ("{table_name_sub1}","{table_name_sub2}","{table_name_sub3}","{table_name_sub4}","{table_name_sub5}","{table_name_sub6}","{table_name_sub7}","{table_name_sub8}","{table_name_sub9}") and ts >= "1980-01-01 00:00:00.000"'
)
tdSql.checkRows(90)
def checkStbSuperSubBlockMerge(self):
stb_name = tdCom.getLongName(5, "letters")
tb_name = f'{stb_name}_sub'
tdSql.execute(
f'CREATE TABLE {stb_name} (ts timestamp, c1 int) tags (t1 int)')
tdSql.execute(f'CREATE TABLE {tb_name} using {stb_name} tags (1)')
start_ts = 1577808001000
for i in range(10):
tdSql.execute(f'insert into {tb_name} values ({start_ts}, {i})')
start_ts += 1
tdCom.restartTaosd()
for i in range(10):
tdSql.execute(f'insert into {tb_name} values ({start_ts}, Null)')
start_ts += 1
tdCom.restartTaosd()
for i in range(10):
new_ts = i + 10 + 10
tdSql.execute(
f'insert into {tb_name} values ({start_ts}, {new_ts})')
start_ts += 1
tdCom.restartTaosd()
tdSql.query(f'select * from {stb_name}')
def initTb(self):
tdCom.cleanTb()
tb_name = tdCom.getLongName(8, "letters")
tdSql.execute(
f"CREATE TABLE {tb_name} (ts timestamp, c1 tinyint, c2 smallint, c3 int, c4 bigint, c5 float, c6 double, c7 binary(100), c8 nchar(200), c9 bool, c10 int)"
)
self.insertData(tb_name)
return tb_name
def initStb(self):
tdCom.cleanTb()
tb_name = tdCom.getLongName(8, "letters")
tdSql.execute(
f"CREATE TABLE {tb_name} (ts timestamp, c1 tinyint, c2 smallint, c3 int, c4 bigint, c5 float, c6 double, c7 binary(100), c8 nchar(200), c9 bool, c10 int) tags (t1 tinyint, t2 smallint, t3 int, t4 bigint, t5 float, t6 double, t7 binary(100), t8 nchar(200), t9 bool, t10 int)"
)
tdSql.execute(
f'CREATE TABLE {tb_name}_sub using {tb_name} tags (1, 1, 1, 3, 1.1, 1.1, "binary", "nchar", true, 1)'
)
self.insertData(f'{tb_name}_sub')
return tb_name
def checkStbWhereIn(self):
'''
where in ---> upper lower mixed
'''
tdCom.cleanTb()
table_name = tdCom.getLongName(8, "letters_mixed")
table_name_sub = f'{table_name}_sub'
tb_name_lower = table_name_sub.lower()
tb_name_upper = table_name_sub.upper()
## create stb and tb
tdSql.execute(f'CREATE TABLE {table_name} (ts timestamp, id int, bi1 binary(20)) tags (si1 binary(20))')
tdSql.execute(f'create table {table_name_sub}1 using {table_name} tags ("{table_name_sub}1")')
tdSql.execute(f'create table {tb_name_lower}2 using {table_name} tags ("{tb_name_lower}2")')
tdSql.execute(f'create table {tb_name_upper}3 using {table_name} tags ("{tb_name_upper}3")')
## insert values
tdSql.execute(f'insert into {table_name_sub}1 values (now-1s, 1, "{table_name_sub}1")')
tdSql.execute(f'insert into {tb_name_lower}2 values (now-2s, 2, "{tb_name_lower}21")')
tdSql.execute(f'insert into {tb_name_lower}2 values (now-3s, 3, "{tb_name_lower}22")')
tdSql.execute(f'insert into {tb_name_upper}3 values (now-4s, 4, "{tb_name_upper}31")')
tdSql.execute(f'insert into {tb_name_upper}3 values (now-5s, 5, "{tb_name_upper}32")')
tdSql.execute(f'insert into {tb_name_upper}3 values (now-6s, 6, "{tb_name_upper}33")')
## query where tbname in single
tdSql.query(f'select * from {table_name} where tbname in ("{table_name_sub}1")')
tdSql.checkRows(1)
tdSql.query(f'select * from {table_name} where tbname in ("{table_name_sub.upper()}1")')
tdSql.checkRows(1)
tdSql.query(f'select * from {table_name} where tbname in ("{table_name_sub.lower()}1")')
tdSql.checkRows(1)
tdSql.query(f'select * from {table_name} where tbname in ("{tb_name_lower}2")')
tdSql.checkRows(2)
tdSql.query(f'select * from {table_name} where tbname in ("{tb_name_lower.upper()}2")')
tdSql.checkRows(2)
tdSql.query(f'select * from {table_name} where tbname in ("{tb_name_upper}3")')
tdSql.checkRows(3)
tdSql.query(f'select * from {table_name} where tbname in ("{tb_name_upper.lower()}3")')
tdSql.checkRows(3)
## query where tbname in multi
tdSql.query(f'select * from {table_name} where id=5 and tbname in ("{table_name_sub}1", "{tb_name_lower.upper()}2", "{tb_name_upper.lower()}3")')
tdSql.checkRows(1)
tdSql.query(f'select * from {table_name} where tbname in ("{table_name_sub}1", "{tb_name_lower.upper()}2", "{tb_name_upper.lower()}3")')
tdSql.checkRows(6)
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 queryGroupTbname(self):
'''
select a1,a2...a10 from stb where tbname in (t1,t2,...t10) and ts...
'''
tdCom.cleanTb()
table_name = tdCom.getLongName(8, "letters_mixed")
tbname_list = list(map(lambda x: f'table_name_sub{x}', range(1, 11)))
tb_str = ""
for tbname in tbname_list:
globals()[tbname] = tdCom.getLongName(8, "letters_mixed")
tdSql.execute(
f'CREATE TABLE {table_name} (ts timestamp, {table_name_sub1} tinyint, \
{table_name_sub2} smallint, {table_name_sub3} int, {table_name_sub4} bigint, \
{table_name_sub5} float, {table_name_sub6} double, {table_name_sub7} binary(20),\
{table_name_sub8} nchar(20), {table_name_sub9} bool) tags ({table_name_sub10} binary(20))'
)
for tbname in tbname_list:
tb_str += tbname
tdSql.execute(
f'create table {globals()[tbname]} using {table_name} tags ("{globals()[tbname]}")'
)
for i in range(10):
for tbname in tbname_list:
tdSql.execute(
f'insert into {globals()[tbname]} values (now, 1, 2, 3, 4, 1.1, 2.2, "{globals()[tbname]}", "{globals()[tbname]}", True)'
)
for i in range(100):
tdSql.query(
f'select {table_name_sub1},{table_name_sub2},{table_name_sub3},{table_name_sub4},{table_name_sub5},{table_name_sub6},{table_name_sub7},{table_name_sub8},{table_name_sub9} from {table_name} where tbname in ("{table_name_sub1}","{table_name_sub2}","{table_name_sub3}","{table_name_sub4}","{table_name_sub5}","{table_name_sub6}","{table_name_sub7}","{table_name_sub8}","{table_name_sub9}") and ts >= "1980-01-01 00:00:00.000"'
)
tdSql.checkRows(90)
# TS-634
tdLog.info("test case for bug TS-634")
tdSql.execute("create database test")
tdSql.execute("use test")
tdSql.execute(
"create table meters (ts TIMESTAMP,voltage INT) TAGS (tableid INT)"
)
tdSql.execute("CREATE TABLE t1 USING meters TAGS (1)")
tdSql.execute("CREATE TABLE t2 USING meters TAGS (2)")
ts = 1605381041000
for i in range(10):
tdSql.execute("INSERT INTO t1 values(%d, %d)" %
(ts + i, random.randint(0, 100)))
tdSql.execute("INSERT INTO t2 values(%d, %d)" %
(ts + i, random.randint(0, 100)))
tdSql.query(
"select last_row(*), tbname from meters group by tbname order by ts desc"
)
tdSql.checkRows(2)
tdSql.execute("INSERT INTO t2 values(now, 2)")
tdSql.query(
"select last_row(*), tbname from meters group by tbname order by ts desc"
)
tdSql.checkRows(2)