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 run(self):
tdSql.prepare()
print("==============step1")
tdSql.execute("create database if not exists demo;")
tdSql.execute("use demo;")
tdSql.execute(
"CREATE TABLE IF NOT EXISTS test1 (ts TIMESTAMP, ValueID int, "
"VariantValue float, Quality int, Flags int);")
tdSql.execute(
"CREATE TABLE IF NOT EXISTS test2 (ts TIMESTAMP, ValueID int, "
"VariantValue float, Quality int, Flags int);")
ordered_csv = __file__.split('query')[0] + 'test_data/ordered.csv'
disordered_csv = __file__.split(
'query')[0] + 'test_data/disordered.csv'
tdSql.execute(
" insert into test1 file '{file}';".format(file=ordered_csv))
tdSql.execute(
" insert into test2 file '{file}';".format(file=disordered_csv))
print("==============insert into test1 and test2 form test file")
print("==============step2")
tdSql.query('select * from test1;')
with open(ordered_csv) as f1:
num1 = len(f1.readlines())
tdSql.checkRows(num1)
tdSql.query('select * from test2;')
with open(disordered_csv) as f2:
num2 = len(f2.readlines())
tdSql.checkRows(num2)
print("=============execute select count(*) from xxx")
def run(self):
tdSql.prepare()
tdSql.execute(
"create table st(ts timestamp, c1 int) tags(loc nchar(20))")
tdSql.execute("create table t0 using st tags('nchar0')")
tdSql.execute("create table t1 using st tags('nchar1')")
tdSql.execute("create table t2 using st tags('nchar2')")
tdSql.execute("create table t3 using st tags('nchar3')")
tdSql.execute("create table t4 using st tags('nchar4')")
tdSql.execute("create table t5 using st tags('nchar5')")
for i in range(self.num):
tdSql.execute("insert into t0 values(%d, %d)" % (self.ts + i, i))
tdSql.execute("insert into t1 values(%d, %d)" % (self.ts + i, i))
tdSql.execute("insert into t2 values(%d, %d)" % (self.ts + i, i))
tdSql.execute("insert into t3 values(%d, %d)" % (self.ts + i, i))
tdSql.execute("insert into t4 values(%d, %d)" % (self.ts + i, i))
tdSql.execute("insert into t5 values(%d, %d)" % (self.ts + i, i))
sql = ''' select * from st where loc = 'nchar0' limit 1 union all select * from st where loc = 'nchar1' limit 1 union all select * from st where loc = 'nchar2' limit 1
union all select * from st where loc = 'nchar3' limit 1 union all select * from st where loc = 'nchar4' limit 1'''
tdSql.query(sql)
tdSql.checkRows(5)
sql = ''' select * from st where loc = 'nchar0' limit 1 union all select * from st where loc = 'nchar1' limit 1 union all select * from st where loc = 'nchar2' limit 1
union all select * from st where loc = 'nchar3' limit 1 union all select * from st where loc = 'nchar4' limit 1 union all select * from st where loc = 'nchar5' limit 1'''
tdSql.query(sql)
tdSql.checkRows(6)
def run(self):
tdSql.prepare()
startTime = time.time()
print("==============step1")
sql = "create table stb(ts timestamp, "
for i in range(1022):
sql += "col%d binary(14), " % (i + 1)
sql += "col1023 binary(22))"
tdSql.execute(sql)
for i in range(4096):
sql = "insert into stb values(%d, "
for j in range(1022):
str = "'%s', " % self.get_random_string(14)
sql += str
sql += "'%s')" % self.get_random_string(22)
tdSql.execute(sql % (self.ts + i))
tdSql.query("select * from stb")
tdSql.checkRows(4096)
tdDnodes.stop(1)
tdDnodes.start(1)
tdSql.query("select * from stb")
tdSql.checkRows(4096)
endTime = time.time()
print("total time %ds" % (endTime - startTime))
def run(self):
tdSql.prepare()
tdSql.execute(
"create table meters(ts timestamp, col1 int) tags(id int, loc nchar(20))"
)
sql = "insert into t0 using meters tags(1, 'beijing') values"
for i in range(100):
sql += "(%d, %d)" % (self.ts + i * 1000, random.randint(1, 100))
tdSql.execute(sql)
sql = "insert into t1 using meters tags(2, 'shanghai') values"
for i in range(100):
sql += "(%d, %d)" % (self.ts + i * 1000, random.randint(1, 100))
tdSql.execute(sql)
tdSql.query("select count(*) from meters interval(10s) sliding(5s)")
tdSql.checkRows(21)
tdSql.error("select count(*) from meters sliding(5s)")
tdSql.error("select count(*) from meters sliding(5s) interval(10s)")
tdSql.error("select * from meters sliding(5s) order by ts desc")
tdSql.query("select count(*) from meters group by loc")
tdSql.checkRows(2)
tdSql.error("select * from meters group by loc sliding(5s)")
def checkSuperTableWildcardLength(self):
'''
check super table wildcard length with % and _
'''
self.cleanTb()
table_name, hp_name, lp_name, ul_name = self.genTableName()
tdSql.execute(
f"CREATE TABLE {table_name} (ts timestamp, c1 int) tags (t1 int)")
sql_list = [
f'show stables like "{hp_name}"', f'show stables like "{lp_name}"',
f'show stables like "{ul_name}"'
]
for sql in sql_list:
tdSql.query(sql)
if len(table_name) >= 1:
tdSql.checkRows(1)
else:
tdSql.error(sql)
exceed_sql_list = [
f'show stables like "%{hp_name}"',
f'show stables like "{lp_name}%"',
f'show stables like "{ul_name}%"'
]
for sql in exceed_sql_list:
tdSql.error(sql)
def datatypes(self):
tdLog.debug("begin data types")
tdSql.prepare()
tdSql.execute(
"create table stb3 (ts timestamp, c1 int, c2 bigint, c3 float, c4 double, c5 binary(15), c6 nchar(15), c7 bool) tags(t1 int, t2 binary(15))"
)
tdSql.execute("create table tb0 using stb3 tags(0, 'tb0')")
tdSql.execute("create table tb1 using stb3 tags(1, 'tb1')")
tdSql.execute("create table tb2 using stb3 tags(2, 'tb2')")
tdSql.execute("create table tb3 using stb3 tags(3, 'tb3')")
tdSql.execute("create table tb4 using stb3 tags(4, 'tb4')")
tdSql.execute(
"create table strm0 as select count(ts), count(c1), max(c2), min(c4), first(c5), last(c6) from stb3 where ts < now + 30s interval(4s) sliding(2s)"
)
#tdSql.execute("create table strm0 as select count(ts), count(c1), max(c2), min(c4), first(c5) from stb where ts < now + 30s interval(4s) sliding(2s)")
tdLog.sleep(1)
tdSql.execute(
"insert into tb0 values (now, 0, 0, 0, 0, 'binary0', '涛思0', true) tb1 values (now, 1, 1, 1, 1, 'binary1', '涛思1', false) tb2 values (now, 2, 2, 2, 2, 'binary2', '涛思2', true) tb3 values (now, 3, 3, 3, 3, 'binary3', '涛思3', false) tb4 values (now, 4, 4, 4, 4, 'binary4', '涛思4', true) "
)
tdSql.waitedQuery("select * from strm0 order by ts desc", 2, 120)
tdSql.checkRows(2)
tdSql.execute(
"insert into tb0 values (now, 10, 10, 10, 10, 'binary0', '涛思0', true) tb1 values (now, 11, 11, 11, 11, 'binary1', '涛思1', false) tb2 values (now, 12, 12, 12, 12, 'binary2', '涛思2', true) tb3 values (now, 13, 13, 13, 13, 'binary3', '涛思3', false) tb4 values (now, 14, 14, 14, 14, 'binary4', '涛思4', true) "
)
tdSql.waitedQuery("select * from strm0 order by ts desc", 4, 120)
tdSql.checkRows(4)
def run(self):
tdSql.prepare()
tdSql.execute(
"create table stest(ts timestamp,size INT,filenum INT) tags (appname binary(500),tenant binary(500))"
)
tdSql.execute(
"insert into test1 using stest tags('test1','aaa') values ('2020-09-04 16:53:54.003',210,3)"
)
tdSql.execute(
"insert into test2 using stest tags('test1','aaa') values ('2020-09-04 16:53:56.003',210,3)"
)
tdSql.execute(
"insert into test11 using stest tags('test11','bbb') values ('2020-09-04 16:53:57.003',210,3)"
)
tdSql.execute(
"insert into test12 using stest tags('test11','bbb') values ('2020-09-04 16:53:58.003',210,3)"
)
tdSql.execute(
"insert into test21 using stest tags('test21','ccc') values ('2020-09-04 16:53:59.003',210,3)"
)
tdSql.execute(
"insert into test22 using stest tags('test21','ccc') values ('2020-09-04 16:54:54.003',210,3)"
)
tdSql.query(
"select sum(size) from stest interval(1d) group by appname")
tdSql.checkRows(3)
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 run(self):
tdSql.prepare()
print("==============step1")
tdSql.execute("create table if not exists tb (ts timestamp, col1 int)")
tdSql.execute("insert into tb values(%d, 1)" % self.ts)
print("==============step2")
tdSql.query("select * from tb")
tdSql.checkRows(1)
p = Process(target=self.updateMetadata, args=())
p.start()
p.join()
p.terminate()
tdSql.execute("insert into tb(ts, col1, col2) values(%d, 1, 2)" %
(self.ts + 2))
print("==============step2")
tdSql.query("select * from tb")
tdSql.checkRows(2)
# Add test case: https://jira.taosdata.com:18080/browse/TD-3474
print("==============step1")
tdSql.execute("create database test")
tdSql.execute("use test")
tdSql.execute(
"create table if not exists stb (ts timestamp, col1 int) tags(areaid int, city nchar(20))"
)
for i in range(self.tables):
city = "beijing" if i % 2 == 0 else "shanghai"
tdSql.execute("create table tb%d using stb tags(%d, '%s')" %
(i, i, city))
for j in range(self.rows):
tdSql.execute("insert into tb%d values(%d, %d)" %
(i, self.ts + j, j * 100000))
tdSql.query("select count(*) from stb")
tdSql.checkData(0, 0, 10000)
tdSql.query("select count(*) from tb1")
tdSql.checkData(0, 0, 1000)
p = Process(target=self.deleteTableAndRecreate, args=())
p.start()
p.join()
p.terminate()
tdSql.query("select count(*) from stb")
tdSql.checkData(0, 0, 10000)
tdSql.query("select count(*) from tb1")
tdSql.checkData(0, 0, 1000)
def checkStbWildcardSelectLength(self):
'''
check stb wildcard select length with % and _
'''
self.cleanTb()
table_name, hp_name, lp_name, ul_name = self.genTableName()
tdSql.execute(
f'CREATE TABLE {table_name} (ts timestamp, bi1 binary(200), nc1 nchar(200)) tags (si1 binary(200), sc1 nchar(200))'
)
tdSql.execute(
f'create table {table_name}_sub1 using {table_name} tags ("{table_name}", "{table_name}")'
)
tdSql.execute(
f'insert into {table_name}_sub1 values (now, "{table_name}", "{table_name}");'
)
sql_list = [
f'select * from {table_name} where bi1 like "{hp_name}"',
f'select * from {table_name} where bi1 like "{lp_name}"',
f'select * from {table_name} where bi1 like "{ul_name}"',
f'select * from {table_name} where nc1 like "{hp_name}"',
f'select * from {table_name} where nc1 like "{lp_name}"',
f'select * from {table_name} where nc1 like "{ul_name}"',
f'select * from {table_name} where si1 like "{hp_name}"',
f'select * from {table_name} where si1 like "{lp_name}"',
f'select * from {table_name} where si1 like "{ul_name}"',
f'select * from {table_name} where sc1 like "{hp_name}"',
f'select * from {table_name} where sc1 like "{lp_name}"',
f'select * from {table_name} where sc1 like "{ul_name}"'
]
for sql in sql_list:
tdSql.query(sql)
if len(table_name) >= 1:
tdSql.checkRows(1)
else:
tdSql.error(sql)
exceed_sql_list = [
f'select * from {table_name} where bi1 like "%{hp_name}"',
f'select * from {table_name} where bi1 like "{lp_name}%"',
f'select * from {table_name} where bi1 like "{ul_name}%"',
f'select * from {table_name} where nc1 like "%{hp_name}"',
f'select * from {table_name} where nc1 like "{lp_name}%"',
f'select * from {table_name} where nc1 like "{ul_name}%"',
f'select * from {table_name} where si1 like "%{hp_name}"',
f'select * from {table_name} where si1 like "{lp_name}%"',
f'select * from {table_name} where si1 like "{ul_name}%"',
f'select * from {table_name} where sc1 like "%{hp_name}"',
f'select * from {table_name} where sc1 like "{lp_name}%"',
f'select * from {table_name} where sc1 like "{ul_name}%"'
]
for sql in exceed_sql_list:
tdSql.error(sql)
def run(self):
tdSql.prepare()
tdSql.execute(
"CREATE TABLE meters (ts timestamp, current float, voltage int, phase float) TAGS (location binary(64), groupId int, t3 float, t4 double)"
)
tdSql.execute(
"CREATE TABLE D1001 USING meters TAGS ('Beijing.Chaoyang', 2 , NULL, NULL)"
)
tdSql.execute(
"CREATE TABLE D1002 USING meters TAGS ('Beijing.Chaoyang', 3 , NULL , 1.7)"
)
tdSql.execute(
"CREATE TABLE D1003 USING meters TAGS ('Beijing.Chaoyang', 3 , 1.1 , 1.7)"
)
tdSql.execute(
"INSERT INTO D1001 VALUES (1538548685000, 10.3, 219, 0.31) (1538548695000, 12.6, 218, 0.33) (1538548696800, 12.3, 221, 0.31)"
)
tdSql.execute(
"INSERT INTO D1002 VALUES (1538548685001, 10.5, 220, 0.28) (1538548696800, 12.3, 221, 0.31)"
)
tdSql.execute(
"INSERT INTO D1003 VALUES (1538548685001, 10.5, 220, 0.28) (1538548696800, 12.3, 221, 0.31)"
)
tdSql.query(
"SELECT SUM(current), AVG(voltage) FROM meters WHERE groupId > 1 INTERVAL(1s) GROUP BY location order by ts DESC"
)
tdSql.checkRows(3)
tdSql.checkData(0, 0, "2018-10-03 14:38:16")
tdSql.checkData(1, 0, "2018-10-03 14:38:15")
tdSql.checkData(2, 0, "2018-10-03 14:38:05")
tdSql.query(
"SELECT SUM(current), AVG(voltage) FROM meters WHERE groupId > 1 INTERVAL(1s) GROUP BY location order by ts ASC"
)
tdSql.checkRows(3)
tdSql.checkData(0, 0, "2018-10-03 14:38:05")
tdSql.checkData(1, 0, "2018-10-03 14:38:15")
tdSql.checkData(2, 0, "2018-10-03 14:38:16")
tdSql.error(
"SELECT SUM(current) as s, AVG(voltage) FROM meters WHERE groupId > 1 INTERVAL(1s) GROUP BY location order by s ASC"
)
tdSql.error(
"SELECT SUM(current) as s, AVG(voltage) FROM meters WHERE groupId > 1 INTERVAL(1s) GROUP BY location order by s DESC"
)
#add for TD-3170
tdSql.query("select avg(current) from meters group by t3;")
tdSql.checkData(0, 0, 11.6)
tdSql.query("select avg(current) from meters group by t4;")
tdSql.query("select avg(current) from meters group by t3,t4;")
def run(self):
tdSql.prepare()
print("==============step1")
tdSql.execute(
"create table if not exists st (ts timestamp, tagtype int) tags(dev nchar(5))"
)
tdSql.error(
'CREATE TABLE if not exists dev_001 using st tags("dev_001")')
tdSql.execute(
'CREATE TABLE if not exists dev_002 using st tags("dev")')
print("==============step2")
tdSql.query("show tables")
tdSql.checkRows(1)
def run(self):
tdSql.prepare()
tdSql.execute("create table st (ts timestamp, voltage int) tags (loc nchar(30))")
tdSql.execute("insert into t0 using st tags('beijing') values(%d, 220) (%d, 221) (%d, 225) (%d, 228) (%d, 222)"
% (self.ts, self.ts + 1000000000, self.ts + 2000000000, self.ts + 3000000000, self.ts + 6000000000))
tdSql.execute("insert into t1 using st tags('shanghai') values(%d, 220) (%d, 221) (%d, 225) (%d, 228) (%d, 222)"
% (self.ts, self.ts + 2000000000, self.ts + 4000000000, self.ts + 5000000000, self.ts + 7000000000))
tdSql.query("select avg(voltage) from st interval(1n)")
tdSql.checkRows(3)
tdSql.checkData(0, 0, "2020-07-01 00:00:00")
tdSql.checkData(0, 1, 221.4)
tdSql.checkData(1, 0, "2020-08-01 00:00:00")
tdSql.checkData(1, 1, 227.0)
tdSql.checkData(2, 0, "2020-09-01 00:00:00")
tdSql.checkData(2, 1, 222.0)
tdSql.query("select avg(voltage) from st interval(1n, 15d)")
tdSql.checkRows(4)
tdSql.checkData(0, 0, "2020-06-16 00:00:00")
tdSql.checkData(0, 1, 220.333333)
tdSql.checkData(1, 0, "2020-07-16 00:00:00")
tdSql.checkData(1, 1, 224.666666)
tdSql.checkData(2, 0, "2020-08-16 00:00:00")
tdSql.checkData(2, 1, 225.0)
tdSql.checkData(3, 0, "2020-09-16 00:00:00")
tdSql.checkData(3, 1, 222.0)
tdSql.query("select avg(voltage) from st interval(1n, 15d) group by loc")
tdSql.checkRows(7)
tdSql.checkData(0, 0, "2020-06-16 00:00:00")
tdSql.checkData(0, 1, 220.5)
tdSql.checkData(1, 0, "2020-07-16 00:00:00")
tdSql.checkData(1, 1, 226.5)
tdSql.checkData(2, 0, "2020-08-16 00:00:00")
tdSql.checkData(2, 1, 222.0)
tdSql.checkData(3, 0, "2020-06-16 00:00:00")
tdSql.checkData(3, 1, 220.0)
tdSql.checkData(4, 0, "2020-07-16 00:00:00")
tdSql.checkData(4, 1, 221.0)
tdSql.checkData(5, 0, "2020-08-16 00:00:00")
tdSql.checkData(5, 1, 226.5)
tdSql.checkData(6, 0, "2020-09-16 00:00:00")
tdSql.checkData(6, 1, 222.0)
# test case for https://jira.taosdata.com:18080/browse/TD-2298
tdSql.execute("create database test keep 36500")
tdSql.execute("use test")
tdSql.execute("create table t (ts timestamp, voltage int)")
for i in range(10000):
tdSql.execute("insert into t values(%d, 0)" % (1000000 + i * 6000))
tdDnodes.stop(1)
tdDnodes.start(1)
tdSql.query("select last(*) from t interval(1s)")
tdSql.checkRows(10000)
def run(self):
tdSql.prepare()
tdSql.execute(
"create table stb(ts timestamp,i int) tags (p_id nchar(20));")
tdSql.execute(
"insert into tb using stb tags('11231') values (%d, %d) (%d, %d) (%d, %d) (%d, %d)"
% (self.ts, 12, self.ts + 1, 15, self.ts + 2, 15, self.ts + 3, 12))
tdSql.query(
''' select last(ts) p_time,i from stb where p_id='11231' and ts>=%d and ts <=%d
group by i order by time desc limit 100 ''' %
(self.ts, self.ts + 4))
tdSql.checkRows(2)
tdSql.checkData(0, 0, "2018-09-17 09:00:00.003000")
tdSql.checkData(1, 0, "2018-09-17 09:00:00.002000")
def executeQueries(self):
print("==============step2")
tdSql.query("select last_row(c1) from %s%d" % (self.perfix, 1))
tdSql.checkData(0, 0, 19)
tdSql.query("select last_row(c1) from %s%d where ts <= %d" %
(self.perfix, 1, self.ts + 4 * 60000))
tdSql.checkData(0, 0, 4)
tdSql.query("select last_row(c1) as b from %s%d" % (self.perfix, 1))
tdSql.checkData(0, 0, 19)
tdSql.query("select last_row(c1) from st")
tdSql.checkData(0, 0, 19)
tdSql.query("select last_row(c1) as c from st where ts <= %d" %
(self.ts + 4 * 60000))
tdSql.checkData(0, 0, 4)
tdSql.query("select last_row(c1) as c from st where t1 < 5")
tdSql.checkData(0, 0, 19)
tdSql.query(
"select last_row(c1) as c from st where t1 <= 5 and ts <= %d" %
(self.ts + 4 * 60000))
tdSql.checkData(0, 0, 4)
tdSql.query("select last_row(c1) as c from st group by t1")
tdSql.checkRows(10)
tdSql.checkData(0, 0, 19)
tc = self.ts + 1 * 3600000
tdSql.execute("insert into %s%d(ts, c1) values(%d, %d)" %
(self.perfix, 1, tc, 10))
tc = self.ts + 3 * 3600000
tdSql.execute("insert into %s%d(ts, c1) values(%d, null)" %
(self.perfix, 1, tc))
tc = self.ts + 5 * 3600000
tdSql.execute("insert into %s%d(ts, c1) values(%d, %d)" %
(self.perfix, 1, tc, -1))
tc = self.ts + 7 * 3600000
tdSql.execute("insert into %s%d(ts, c1) values(%d, null)" %
(self.perfix, 1, tc))
def wildcardFilterOnTags(self):
tdLog.debug("begin wildcardFilterOnTag")
tdSql.prepare()
tdSql.execute(
"create table stb (ts timestamp, c1 int, c2 binary(10)) tags(t1 binary(10))"
)
tdSql.execute("create table tb1 using stb tags('a1')")
tdSql.execute("create table tb2 using stb tags('b2')")
tdSql.execute("create table tb3 using stb tags('a3')")
tdSql.execute(
"create table strm as select count(*), avg(c1), first(c2) from stb where t1 like 'a%' interval(4s) sliding(2s)"
)
tdSql.query("describe strm")
tdSql.checkRows(4)
tdLog.sleep(1)
tdSql.execute("insert into tb1 values (now, 0, 'tb1')")
tdLog.sleep(4)
tdSql.execute("insert into tb2 values (now, 2, 'tb2')")
tdLog.sleep(4)
tdSql.execute("insert into tb3 values (now, 0, 'tb3')")
tdSql.waitedQuery("select * from strm", 4, 60)
tdSql.checkRows(4)
tdSql.checkData(0, 2, 0.000000000)
if tdSql.getData(0, 3) == 'tb2':
tdLog.exit("unexpected value of data03")
if tdSql.getData(1, 3) == 'tb2':
tdLog.exit("unexpected value of data13")
if tdSql.getData(2, 3) == 'tb2':
tdLog.exit("unexpected value of data23")
if tdSql.getData(3, 3) == 'tb2':
tdLog.exit("unexpected value of data33")
tdLog.info("add table tb4 to see if stream still works correctly")
# The vnode client needs to refresh metadata cache to allow strm calculate tb4's data.
# But the current refreshing frequency is every 10 min
# commented out the case below to save running time
tdSql.execute("create table tb4 using stb tags('a4')")
tdSql.execute("insert into tb4 values(now, 4, 'tb4')")
tdSql.waitedQuery("select * from strm order by ts desc", 6, 60)
tdSql.checkRows(6)
tdSql.checkData(0, 2, 4)
tdSql.checkData(0, 3, "tb4")
tdLog.info("change tag values to see if stream still works correctly")
tdSql.execute("alter table tb4 set tag t1='b4'")
tdLog.sleep(3)
tdSql.execute("insert into tb1 values (now, 1, 'tb1_a1')")
tdLog.sleep(4)
tdSql.execute("insert into tb4 values (now, -4, 'tb4_b4')")
tdSql.waitedQuery("select * from strm order by ts desc", 8, 100)
tdSql.checkRows(8)
tdSql.checkData(0, 2, 1)
tdSql.checkData(0, 3, "tb1_a1")
def run(self):
tdSql.query("show users")
rows = tdSql.queryRows
tdSql.execute("create user test PASS 'test' ")
tdSql.query("show users")
tdSql.checkRows(rows + 1)
tdSql.error("create user tdenginetdenginetdengine PASS 'test' ")
tdSql.error("create user tdenginet PASS '1234512345123456' ")
try:
tdSql.execute("create account a&cc PASS 'pass123'")
except Exception as e:
print("create account a&cc PASS 'pass123'")
return
tdLog.exit("drop built-in user is error.")
def run(self):
tdSql.prepare()
### test case for TD-1758 ###
print("==============step1")
tdSql.execute("create table t0(ts timestamp, c int)")
tdSql.execute('create table t1(ts timestamp, c binary(1))')
tdSql.execute(
"insert into t0 values(now,1) t1 values(now,'0')(now+1a,'1')(now+2a,'2')(now+3a,'3')(now+4a,'4')"
)
print("==============step2")
tdSql.query("select * from t0")
tdSql.checkRows(1)
tdSql.checkData(0, 0, 1)
tdSql.query("select * from t1")
tdSql.checkRows(5)
def run(self):
nodes = Nodes()
ctest = ClusterTest(nodes.node1.hostName)
tdSql.init(ctest.conn.cursor(), False)
tdSql.query("show databases")
count = tdSql.queryRows
nodes.stopAllTaosd()
nodes.node1.startTaosd()
tdSql.error("show databases")
nodes.node2.startTaosd()
tdSql.error("show databases")
nodes.node3.startTaosd()
tdLog.sleep(10)
tdSql.query("show databases")
tdSql.checkRows(count)
def run(self):
tdSql.prepare()
print("==============step1")
tdSql.execute("create database if not exists demo keep 36500;")
print("==============create db demo keep 365000 days")
tdSql.execute("use demo;")
tdSql.execute(
"CREATE table if not exists test (ts timestamp, f1 int);")
print("==============create table test")
print("==============step2")
#TODO : should add more testcases
tdSql.execute("insert into test values('1930-12-12 01:19:20.345', 1);")
tdSql.execute("insert into test values('1969-12-30 23:59:59.999', 2);")
tdSql.execute("insert into test values(-3600, 3);")
tdSql.execute("insert into test values('2020-10-20 14:02:53.770', 4);")
print("==============insert data")
# tdSql.query("select * from test;")
#
# tdSql.checkRows(3)
#
# tdSql.checkData(0,0,'1969-12-12 01:19:20.345000')
# tdSql.checkData(1,0,'1970-01-01 07:00:00.000000')
# tdSql.checkData(2,0,'2020-10-20 14:02:53.770000')
print("==============step3")
tdDnodes.stopAll()
tdDnodes.start(1)
print("==============restart taosd")
print("==============step4")
tdSql.execute("use demo;")
tdSql.query("select * from test;")
# print(tdSql.queryResult)
tdSql.checkRows(4)
tdSql.checkData(0, 0, '1930-12-12 01:19:20.345000')
tdSql.checkData(1, 0, '1969-12-30 23:59:59.999000')
tdSql.checkData(2, 0, '1970-01-01 07:00:00.000000')
tdSql.checkData(3, 0, '2020-10-20 14:02:53.770000')
print("==============check data")
def run(self):
tdSql.prepare()
print("==============step1")
tdSql.execute(
"create table if not exists st (ts timestamp, tagtype int) tags(dev nchar(50))"
)
tdSql.execute(
'CREATE TABLE if not exists dev_001 using st tags("dev_01")')
tdSql.execute(
'CREATE TABLE if not exists dev_002 using st tags("dev_02")')
print("==============step2")
tdSql.execute(
"""INSERT INTO dev_001(ts, tagtype) VALUES('2020-05-13 10:00:00.000', 1),
('2020-05-13 10:00:00.001', 1)
dev_002 VALUES('2020-05-13 10:00:00.001', 1)""")
tdSql.query("select * from db.st where ts='2020-05-13 10:00:00.000'")
tdSql.checkRows(1)
def run(self):
tdSql.prepare()
print("==============step1")
tdSql.execute(
"CREATE TABLE IF NOT EXISTS ampere (ts TIMESTAMP,ampere DOUBLE) TAGS (device_name BINARY(50),build_id BINARY(50),project_id BINARY(50),alias BINARY(50))")
tdSql.execute("insert into d1001 using ampere tags('test', '2', '2', '2') VALUES (now, 123)")
tdSql.execute("ALTER TABLE ampere ADD TAG variable_id BINARY(50)")
print("==============step2")
tdSql.execute("insert into d1002 using ampere tags('test', '2', '2', '2', 'test') VALUES (now, 124)")
tdSql.query("select * from ampere")
tdSql.checkRows(2)
tdSql.checkData(0, 6, None)
tdSql.checkData(1, 6, 'test')
# Test case for: https://jira.taosdata.com:18080/browse/TD-2423
tdSql.execute("create table stb(ts timestamp, col1 int, col2 nchar(20)) tags(tg1 int, tg2 binary(20), tg3 nchar(25))")
tdSql.execute("insert into tb1 using stb(tg1, tg3) tags(1, 'test1') values(now, 1, 'test1')")
tdSql.query("select *, tg1, tg2, tg3 from tb1")
tdSql.checkRows(1)
tdSql.checkData(0, 3, 1)
tdSql.checkData(0, 4, None)
tdSql.checkData(0, 5, 'test1')
tdSql.execute("create table tb2 using stb(tg3, tg2) tags('test3', 'test2')")
tdSql.query("select tg1, tg2, tg3 from tb2")
tdSql.checkRows(1)
tdSql.checkData(0, 0, None)
tdSql.checkData(0, 1, 'test2')
tdSql.checkData(0, 2, 'test3')
def run(self):
tdSql.prepare()
print("==============step1")
tdSql.execute(
"create table if not exists stb (ts timestamp, col1 int, col2 int, col3 int) tags(loc nchar(20), id int)")
currTs = self.ts
for i in range(100):
sql = "create table tb%d using stb tags('city%d', 1)" % (i, i)
tdSql.execute(sql)
sql = "insert into tb%d values" % i
for j in range(5):
val = 1 + j
sql += "(%d, %d, %d, %d)" % (currTs, val, val, val)
currTs += 1000000
tdSql.execute(sql)
tdSql.query("select first(col1) - avg(col1) from stb where ts > '2018-09-17 08:00:00.000' and ts < '2018-09-23 04:36:40.000' interval(1h)")
tdSql.checkRows(139)
tdSql.query("select first(col1) - avg(col1) from stb where ts > '2018-09-17 08:00:00.000' and ts < '2018-09-23 04:36:40.000' interval(1h) fill(null)")
tdSql.checkRows(141)
tdSql.checkData(0, 1, None)
tdSql.checkData(140, 1, None)
tdSql.query("select max(col1) - min(col1) from stb where ts > '2018-09-17 08:00:00.000' and ts < '2018-09-23 04:36:40.000' and id = 1 group by loc, id")
rows = tdSql.queryRows
tdSql.query("select spread(col1) from stb where ts > '2018-09-17 08:00:00.000' and ts < '2018-09-23 04:36:40.000' and id = 1 group by loc, id")
tdSql.checkRows(rows)
def run(self):
# cluster environment set up
nodes = Nodes()
ctest = ClusterTest(nodes.node1.hostName)
ctest.connectDB()
tdSql.init(ctest.conn.cursor(), False)
nodes.addConfigs("offlineThreshold", "10")
nodes.removeAllDataFiles()
nodes.restartAllTaosd()
nodes.node3.stopTaosd()
tdLog.sleep(10)
tdSql.query("show dnodes")
tdSql.checkRows(3)
tdSql.checkData(2, 4, "offline")
tdLog.sleep(60)
tdSql.checkRows(3)
tdSql.checkData(2, 4, "dropping")
tdLog.sleep(300)
tdSql.checkRows(2)
nodes.removeConfigs("offlineThreshold", "10")
nodes.restartAllTaosd()
tdSql.close()
tdLog.success("%s successfully executed" % __file__)
def run(self):
tdSql.prepare()
print("==============step1")
print("prepare data")
tdSql.execute("create table db.st (ts timestamp, i int) tags(j int)")
tdSql.execute("create table db.tb using st tags(1)")
tdSql.execute("insert into db.tb values(now, 1)")
print("==============step2")
print("create table as select")
try:
tdSql.execute("create table db.test as select * from db.st")
except Exception as e:
tdLog.exit(e)
# case for defect: https://jira.taosdata.com:18080/browse/TD-2560
tdSql.execute("create table db.tb02 using st tags(2)")
tdSql.execute("create table db.tb03 using st tags(3)")
tdSql.execute("create table db.tb04 using st tags(4)")
tdSql.query("show tables like 'tb%' ")
tdSql.checkRows(4)
tdSql.query("show tables like 'tb0%' ")
tdSql.checkRows(3)
tdSql.execute("create table db.st0 (ts timestamp, i int) tags(j int)")
tdSql.execute("create table db.st1 (ts timestamp, i int, c2 int) tags(j int, loc nchar(20))")
tdSql.query("show stables like 'st%' ")
tdSql.checkRows(3)
def run(self):
self.writeCSV()
tdSql.prepare()
tdSql.execute(
"create table t1(ts timestamp, c1 int, c2 float, c3 int, c4 int)")
startTime = time.time()
tdSql.execute("insert into t1 file '%s'" % self.csvfile)
duration = time.time() - startTime
print("Insert time: %d" % duration)
tdSql.query("select * from t1")
tdSql.checkRows(self.rows)
tdSql.execute(
"create table stb(ts timestamp, c1 int, c2 float, c3 int, c4 int) tags(t1 int, t2 binary(20))"
)
tdSql.execute("insert into t2 using stb(t1) tags(1) file '%s'" %
self.csvfile)
tdSql.query("select * from stb")
tdSql.checkRows(self.rows)
tdSql.execute("insert into t3 using stb tags(1, 'test') file '%s'" %
self.csvfile)
tdSql.query("select * from stb")
tdSql.checkRows(self.rows * 2)
def run(self):
tdSql.prepare()
tdSql.execute(
"create table st (ts timestamp, voltage int) tags (loc nchar(30))")
tdSql.execute(
"insert into t0 using st tags('beijing') values(now, 220) (now - 15d, 221) (now - 30d, 225) (now - 35d, 228) (now - 45d, 222)"
)
tdSql.execute(
"insert into t1 using st tags('shanghai') values(now, 220) (now - 60d, 221) (now - 50d, 225) (now - 40d, 228) (now - 20d, 222)"
)
tdSql.query("select avg(voltage) from st interval(1n)")
tdSql.checkRows(3)
tdSql.checkData(0, 1, 223.0)
tdSql.checkData(1, 1, 225.0)
tdSql.checkData(2, 1, 220.333333)
tdSql.query("select avg(voltage) from st interval(1n, 15d)")
tdSql.checkRows(3)
tdSql.checkData(0, 1, 224.8)
tdSql.checkData(1, 1, 222.666666)
tdSql.checkData(2, 1, 220.0)
tdSql.query(
"select avg(voltage) from st interval(1n, 15d) group by loc")
tdSql.checkRows(6)
tdSql.checkData(0, 1, 225.0)
tdSql.checkData(1, 1, 223.0)
tdSql.checkData(2, 1, 220.0)
tdSql.checkData(3, 1, 224.666666)
tdSql.checkData(4, 1, 222.0)
tdSql.checkData(5, 1, 220.0)
def run(self):
nodes = Nodes()
ctest = ClusterTest(nodes.node1.hostName)
ctest.connectDB()
ctest.createSTable(1)
ctest.run()
tdSql.init(ctest.conn.cursor(), False)
tdSql.query("show databases")
count = tdSql.queryRows
tdSql.execute("use %s" % ctest.dbName)
tdSql.execute("alter database %s replica 3" % ctest.dbName)
nodes.node2.stopTaosd()
nodes.node3.stopTaosd()
tdSql.error("show databases")
nodes.node2.startTaosd()
tdSql.error("show databases")
nodes.node3.startTaosd()
tdSql.query("show databases")
tdSql.checkRows(count)
def checkRegularWildcardSelectLength(self):
'''
check regular table wildcard select length with % and _
'''
self.cleanTb()
table_name, hp_name, lp_name, ul_name = self.genTableName()
tdSql.execute(
f"CREATE TABLE {table_name} (ts timestamp, bi1 binary(200), nc1 nchar(200))"
)
tdSql.execute(
f'insert into {table_name} values (now, "{table_name}", "{table_name}")'
)
sql_list = [
f'select * from {table_name} where bi1 like "{hp_name}"',
f'select * from {table_name} where bi1 like "{lp_name}"',
f'select * from {table_name} where bi1 like "{ul_name}"',
f'select * from {table_name} where nc1 like "{hp_name}"',
f'select * from {table_name} where nc1 like "{lp_name}"',
f'select * from {table_name} where nc1 like "{ul_name}"'
]
for sql in sql_list:
tdSql.query(sql)
if len(table_name) >= 1:
tdSql.checkRows(1)
else:
tdSql.error(sql)
exceed_sql_list = [
f'select * from {table_name} where bi1 like "%{hp_name}"',
f'select * from {table_name} where bi1 like "{lp_name}%"',
f'select * from {table_name} where bi1 like "{ul_name}%"',
f'select * from {table_name} where nc1 like "%{hp_name}"',
f'select * from {table_name} where nc1 like "{lp_name}%"',
f'select * from {table_name} where nc1 like "{ul_name}%"'
]
for sql in exceed_sql_list:
tdSql.error(sql)
