def run(self):
tbNum = 10
rowNum = 20
tdSql.prepare()
tdLog.info("===== preparing data =====")
tdSql.execute(
"create table stb(ts timestamp, tbcol int, tbcol2 float) tags(tgcol int)"
)
for i in range(tbNum):
tdSql.execute("create table tb%d using stb tags(%d)" % (i, i))
for j in range(rowNum):
tdSql.execute("insert into tb%d values (now - %dm, %d, %d)" %
(i, 1440 - j, j, j))
time.sleep(0.1)
self.createFuncStream("count(*)", "c1", 200)
self.createFuncStream("count(tbcol)", "c2", 200)
self.createFuncStream("count(tbcol2)", "c3", 200)
self.createFuncStream("avg(tbcol)", "av", 9.5)
self.createFuncStream("sum(tbcol)", "su", 1900)
self.createFuncStream("min(tbcol)", "mi", 0)
self.createFuncStream("max(tbcol)", "ma", 19)
self.createFuncStream("first(tbcol)", "fi", 0)
self.createFuncStream("last(tbcol)", "la", 19)
#tdSql.query("select stddev(tbcol) from stb interval(1d)")
#tdSql.query("select leastsquares(tbcol, 1, 1) from stb interval(1d)")
tdSql.query("select top(tbcol, 1) from stb interval(1d)")
tdSql.query("select bottom(tbcol, 1) from stb interval(1d)")
#tdSql.query("select percentile(tbcol, 1) from stb interval(1d)")
#tdSql.query("select diff(tbcol) from stb interval(1d)")
tdSql.query(
"select count(tbcol) from stb where ts < now + 4m interval(1d)")
tdSql.checkData(0, 1, 200)
#tdSql.execute("create table strm_wh as select count(tbcol) from stb where ts < now + 4m interval(1d)")
self.createFuncStream("count(tbcol)", "as", 200)
tdSql.query("select count(tbcol) from stb interval(1d) group by tgcol")
tdSql.checkData(0, 1, 20)
tdSql.query(
"select count(tbcol) from stb where ts < now + 4m interval(1d) group by tgcol"
)
tdSql.checkData(0, 1, 20)
self.checkStreamData("c1", 200)
self.checkStreamData("c2", 200)
self.checkStreamData("c3", 200)
self.checkStreamData("av", 9.5)
self.checkStreamData("su", 1900)
self.checkStreamData("mi", 0)
self.checkStreamData("ma", 19)
self.checkStreamData("fi", 0)
self.checkStreamData("la", 19)
#self.checkStreamData("wh", 200)
self.checkStreamData("as", 200)
def run(self):
tdSql.prepare()
startTime = time.time()
print("==============step1")
sql = "create table stb(ts timestamp, "
for i in range(15):
sql += "col%d binary(1022), " % (i + 1)
sql += "col1023 binary(1014))"
tdSql.execute(sql)
for i in range(4096):
sql = "insert into stb values(%d, "
for j in range(15):
str = "'%s', " % self.get_random_string(1022)
sql += str
sql += "'%s')" % self.get_random_string(1014)
tdSql.execute(sql % (self.ts + i))
time.sleep(10)
tdSql.query("select count(*) from stb")
tdSql.checkData(0, 0, 4096)
tdDnodes.stop(1)
tdDnodes.start(1)
time.sleep(1)
tdSql.query("select count(*) from stb")
tdSql.checkData(0, 0, 4096)
sql = "create table stb(ts timestamp, "
for i in range(15):
sql += "col%d binary(1022), " % (i + 1)
sql += "col1023 binary(1015))"
tdSql.error(sql)
endTime = time.time()
print("total time %ds" % (endTime - startTime))
def run(self):
# cluster environment set up
nodes = Nodes()
nodes.addConfigs("maxVgroupsPerDb", "10")
nodes.addConfigs("maxTablesPerVnode", "1000")
nodes.restartAllTaosd()
ctest = ClusterTest(nodes.node1.hostName)
ctest.connectDB()
ctest.createSTable(1)
ctest.run()
tdSql.init(ctest.conn.cursor(), False)
tdSql.execute("use %s" % ctest.dbName)
tdSql.error("create table tt1 using %s tags(1)" % ctest.stbName)
nodes.removeConfigs("maxVgroupsPerDb", "10")
nodes.removeConfigs("maxTablesPerVnode", "1000")
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)
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 insertData2(self):
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 run(self):
# tdSql.execute("drop database db ")
tdSql.prepare()
tdSql.execute("create table st (ts timestamp, num int, value int) tags (loc nchar(30))")
for i in range(self.tables):
for j in range(self.rowsPerTable):
args1=(i, i, self.ts + i * self.rowsPerTable + j * 10000, i, random.randint(1, 100))
tdSql.execute("insert into t%d using st tags('beijing%d') values(%d, %d, %d)" % args1)
tdSql.query("select * from (select * from st)")
tdSql.checkRows(self.tables * self.rowsPerTable)
tdSql.query("select * from (select * from st limit 10)")
tdSql.checkRows(10)
tdSql.query("select * from (select * from st order by ts desc limit 10)")
tdSql.checkRows(10)
# bug: https://jira.taosdata.com:18080/browse/TD-5043
tdSql.query("select * from (select * from st order by ts desc limit 10 offset 1000)")
tdSql.checkRows(0)
tdSql.query("select avg(value), sum(value) from st group by tbname")
tdSql.checkRows(self.tables)
tdSql.query("select * from (select avg(value), sum(value) from st group by tbname)")
tdSql.checkRows(self.tables)
tdSql.query("select avg(value), sum(value) from st group by tbname slimit 5")
tdSql.checkRows(5)
tdSql.query("select * from (select avg(value), sum(value) from st group by tbname slimit 5)")
tdSql.checkRows(5)
tdSql.query("select avg(value), sum(value) from st group by tbname slimit 5 soffset 7")
tdSql.checkRows(3)
tdSql.query("select * from (select avg(value), sum(value) from st group by tbname slimit 5 soffset 7)")
tdSql.checkRows(3)
# https://jira.taosdata.com:18080/browse/TD-5497
tdSql.execute("create table tt(ts timestamp ,i int)")
tdSql.execute("insert into tt values(now, 11)(now + 1s, -12)")
tdSql.query("select * from (select max(i),0-min(i) from tt)")
tdSql.checkRows(1);
tdSql.checkData(0, 0, 11);
tdSql.checkData(0, 1, 12.0);
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)
def checkRegularTableWildcardLength(self):
'''
check regular 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, a1 int)")
sql_list = [
f'show tables like "{hp_name}"', f'show tables like "{lp_name}"',
f'show tables 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 tables like "%{hp_name}"', f'show tables like "{lp_name}%"',
f'show tables like "{ul_name}%"'
]
for sql in exceed_sql_list:
tdSql.error(sql)
def run(self):
tdSql.prepare()
print("==============step1")
os.system('yes | sudo taosdemo -n 100')
print('insert into test.meters 10000000 rows')
t1 = threading.Thread(target=self.query)
t1.setDaemon(True)
t1.start()
print("==============step2")
tdSql.execute('use test;')
try:
print('============begin select * from 10000000 rows')
tdSql.query('select * from test.meters;')
# print(tdSql.queryResult)
except Exception as e:
if not "ProgrammingError('Query terminated'" in str(e):
raise Exception('fail')
print('success')
print('kill query success')
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 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 insertData(self):
print("==============step1")
tdSql.execute("create table st (ts timestamp, c1 int) tags(t1 int)")
for i in range(self.tables):
tdSql.execute("create table %s%d using st tags(%d)" %
(self.perfix, i, i))
for j in range(self.rows):
tc = self.ts + j * 60000
tdSql.execute("insert into %s%d values(%d, %d)" %
(self.perfix, i, tc, j))
def run(self):
tdSql.prepare()
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")')
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)""")
for i in range(10):
for j in range(1000):
tdSql.query("select * from db.st")
tdLog.sleep(10)
def run(self):
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 'outoforder.csv'")
duration = time.time() - startTime
print("Out of Order - Insert time: %d" % duration)
tdSql.query("select count(*) from t1")
rows = tdSql.getData(0, 0)
tdSql.execute(
"create table t2(ts timestamp, c1 int, c2 float, c3 int, c4 int)")
startTime = time.time()
tdSql.execute("insert into t2 file 'ordered.csv'")
duration = time.time() - startTime
print("Ordered - Insert time: %d" % duration)
tdSql.query("select count(*) from t2")
tdSql.checkData(0, 0, rows)
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)
def run(self):
tdSql.prepare()
print("==============step1")
tdLog.info("check nchar")
tdSql.error("create database anal (ts timestamp ,i nchar(4094))")
tdSql.execute(
"create table anal (ts timestamp ,i nchar(4093))")
print("==============step2")
tdLog.info("check binary")
tdSql.error("create database anal (ts timestamp ,i binary(16375))")
tdSql.execute(
"create table anal1 (ts timestamp ,i binary(16374))")
print("==============step3")
tdLog.info("check int & binary")
# tdSql.error("create table anal2 (ts timestamp ,i binary(16371),j int)")
tdSql.execute("create table anal2 (ts timestamp ,i binary(16370),j int)")
tdSql.execute("create table anal3 (ts timestamp ,i binary(16366), j int, k int)")
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 insertData(self):
print("==============step1")
tdSql.execute(
"create table if not exists st (ts timestamp, tagtype int) tags(dev nchar(50))"
)
for i in range(self.tables):
tdSql.execute("create table %s%d using st tags(%d)" %
(self.perfix, i, i))
rows = 15 + i
for j in range(rows):
tdSql.execute(
"insert into %s%d values(%d, %d)" %
(self.perfix, i, self.ts + i * 20 * 10000 + j * 10000, j))
def checkTbSuperSubBlockMerge(self):
tb_name = tdCom.getLongName(6, "letters")
tdSql.execute(f'CREATE TABLE {tb_name} (ts timestamp, c1 int)')
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 {tb_name}')
def insertData(self):
print("==============step1")
sql = "create table st(ts timestamp, "
for i in range(self.columns - 1):
sql += "c%d int, " % (i + 1)
sql += "c50 int) tags(t1 int)"
tdSql.execute(sql)
for i in range(self.tables):
tdSql.execute("create table %s%d using st tags(%d)" %
(self.perfix, i, i))
for j in range(self.rows):
tc = self.ts + j * 60000
tdSql.execute("insert into %s%d(ts, c1) values(%d, %d)" %
(self.perfix, i, tc, j))
def checkRegularTableCname(self):
"""
check regular table cname
"""
# len(colName) <=64, generate cname list and make first param = 63 and second param = 65
cname_list = []
for i in range(10):
cname_list.append(self.getLongName(64))
cname_list[0] = self.getLongName(63)
cname_list[1] = self.getLongName(65)
# create table and insert data
tdSql.execute(
"CREATE TABLE regular_table_cname_check (ts timestamp, pi1 int, pi2 bigint, pf1 float, pf2 double, ps1 binary(10), pi3 smallint, pi4 tinyint, pb1 bool, ps2 nchar(20))"
)
tdSql.execute(
'insert into regular_table_cname_check values (now, 1, 2, 1.1, 2.2, "a", 1, 1, true, "aa");'
)
tdSql.execute(
'insert into regular_table_cname_check values (now, 2, 3, 1.2, 2.3, "b", 2, 1, false, "aa");'
)
tdSql.execute(
'insert into regular_table_cname_check values (now, 3, 4, 1.3, 2.4, "c", 1, 3, true, "bb");'
)
# select as cname with cname_list
sql_seq = f'select count(ts) as {cname_list[0]}, sum(pi1) as {cname_list[1]}, avg(pi2) as {cname_list[2]}, count(pf1) as {cname_list[3]}, count(pf2) as {cname_list[4]}, count(ps1) as {cname_list[5]}, min(pi3) as {cname_list[6]}, max(pi4) as {cname_list[7]}, count(pb1) as {cname_list[8]}, count(ps2) as {cname_list[9]} from regular_table_cname_check'
sql_seq_no_as = sql_seq.replace(' as ', ' ')
print(sql_seq)
print(sql_seq_no_as)
res = tdSql.getColNameList(sql_seq)
res_no_as = tdSql.getColNameList(sql_seq_no_as)
# cname[1] > 64, it is expected to be equal to 64
cname_list_1_expected = cname_list[1][:-1]
cname_list[1] = cname_list_1_expected
checkColNameList = tdSql.checkColNameList(res, cname_list)
checkColNameList = tdSql.checkColNameList(res_no_as, cname_list)
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):
tdSql.prepare()
print("==============step1")
tdLog.info("create table")
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')")
print("==============step2")
tdLog.info("multiple inserts")
tdSql.execute(
"INSERT INTO dev_001 VALUES ('2020-05-13 10:00:00.000', 1),('2020-05-13 10:00:00.001', 1)"
)
tdSql.checkAffectedRows(2)
def run(self):
tbNum = 10
rowNum = 20
tdSql.prepare()
tdLog.info("===== step1 =====")
tdSql.execute(
"create table stb(ts timestamp, tbcol int, tbcol2 float) tags(tgcol int)"
)
for i in range(tbNum):
tdSql.execute("create table tb%d using stb tags(%d)" % (i, i))
for j in range(rowNum):
tdSql.execute("insert into tb%d values (now - %dm, %d, %d)" %
(i, 1440 - j, j, j))
time.sleep(0.1)
self.createFuncStream("count(*)", "c1", rowNum)
self.createFuncStream("count(tbcol)", "c2", rowNum)
self.createFuncStream("count(tbcol2)", "c3", rowNum)
self.createFuncStream("avg(tbcol)", "av", 9.5)
self.createFuncStream("sum(tbcol)", "su", 190)
self.createFuncStream("min(tbcol)", "mi", 0)
self.createFuncStream("max(tbcol)", "ma", 19)
self.createFuncStream("first(tbcol)", "fi", 0)
self.createFuncStream("last(tbcol)", "la", 19)
self.createFuncStream("stddev(tbcol)", "st", 5.766281297335398)
self.createFuncStream("percentile(tbcol, 1)", "pe", 0.19)
self.createFuncStream("count(tbcol)", "as", rowNum)
self.checkStreamData("c1", rowNum)
self.checkStreamData("c2", rowNum)
self.checkStreamData("c3", rowNum)
self.checkStreamData("av", 9.5)
self.checkStreamData("su", 190)
self.checkStreamData("mi", 0)
self.checkStreamData("ma", 19)
self.checkStreamData("fi", 0)
self.checkStreamData("la", 19)
self.checkStreamData("st", 5.766281297335398)
self.checkStreamData("pe", 0.19)
self.checkStreamData("as", rowNum)
def run(self):
print("==============step1")
try:
tdSql.execute("create user &abc PASS 'pass123'")
except Exception as e:
print(e)
print("==============step2")
try:
tdSql.execute("create user a&bc PASS 'pass123'")
except Exception as e:
print(e)
print("==============step3")
try:
tdSql.execute("create user '涛思' PASS 'pass123'")
except Exception as e:
print(e)
return
tdLog.exit("create user with special character.")
def run(self):
tbNum = 10
rowNum = 20
totalNum = tbNum * rowNum
tdSql.prepare()
tdLog.info("===== preparing data =====")
tdSql.execute(
"create table stb(ts timestamp, tbcol int, tbcol2 float) tags(tgcol int)")
for i in range(tbNum):
tdSql.execute("create table tb%d using stb tags(%d)" % (i, i))
for j in range(rowNum):
tdSql.execute(
"insert into tb%d values (now - %dm, %d, %d)" %
(i, 1440 - j, j, j))
time.sleep(0.1)
tdLog.info("===== step 1 =====")
tdSql.query("select count(*), count(tbcol), count(tbcol2) from stb interval(1d)")
tdSql.checkData(0, 1, totalNum)
tdSql.checkData(0, 2, totalNum)
tdSql.checkData(0, 3, totalNum)
tdLog.info("===== step 2 =====")
tdSql.execute("create table strm_c3 as select count(*), count(tbcol), count(tbcol2) from stb interval(1d)")
tdLog.info("===== step 3 =====")
tdSql.execute("create table strm_c32 as select count(*), count(tbcol) as c1, count(tbcol2) as c2, count(tbcol) as c3, count(tbcol) as c4, count(tbcol) as c5, count(tbcol) as c6, count(tbcol) as c7, count(tbcol) as c8, count(tbcol) as c9, count(tbcol) as c10, count(tbcol) as c11, count(tbcol) as c12, count(tbcol) as c13, count(tbcol) as c14, count(tbcol) as c15, count(tbcol) as c16, count(tbcol) as c17, count(tbcol) as c18, count(tbcol) as c19, count(tbcol) as c20, count(tbcol) as c21, count(tbcol) as c22, count(tbcol) as c23, count(tbcol) as c24, count(tbcol) as c25, count(tbcol) as c26, count(tbcol) as c27, count(tbcol) as c28, count(tbcol) as c29, count(tbcol) as c30 from stb interval(1d)")
tdLog.info("===== step 4 =====")
tdSql.query("select count(*), count(tbcol) as c1, count(tbcol2) as c2, count(tbcol) as c3, count(tbcol) as c4, count(tbcol) as c5, count(tbcol) as c6, count(tbcol) as c7, count(tbcol) as c8, count(tbcol) as c9, count(tbcol) as c10, count(tbcol) as c11, count(tbcol) as c12, count(tbcol) as c13, count(tbcol) as c14, count(tbcol) as c15, count(tbcol) as c16, count(tbcol) as c17, count(tbcol) as c18, count(tbcol) as c19, count(tbcol) as c20, count(tbcol) as c21, count(tbcol) as c22, count(tbcol) as c23, count(tbcol) as c24, count(tbcol) as c25, count(tbcol) as c26, count(tbcol) as c27, count(tbcol) as c28, count(tbcol) as c29, count(tbcol) as c30 from stb interval(1d)")
tdSql.checkData(0, 1, totalNum)
tdSql.checkData(0, 2, totalNum)
tdSql.checkData(0, 3, totalNum)
tdLog.info("===== step 5 =====")
tdSql.execute("create table strm_c31 as select count(*), count(tbcol) as c1, count(tbcol2) as c2, count(tbcol) as c3, count(tbcol) as c4, count(tbcol) as c5, count(tbcol) as c6, count(tbcol) as c7, count(tbcol) as c8, count(tbcol) as c9, count(tbcol) as c10, count(tbcol) as c11, count(tbcol) as c12, count(tbcol) as c13, count(tbcol) as c14, count(tbcol) as c15, count(tbcol) as c16, count(tbcol) as c17, count(tbcol) as c18, count(tbcol) as c19, count(tbcol) as c20, count(tbcol) as c21, count(tbcol) as c22, count(tbcol) as c23, count(tbcol) as c24, count(tbcol) as c25, count(tbcol) as c26, count(tbcol) as c27, count(tbcol) as c28, count(tbcol) as c29, count(tbcol) as c30 from stb interval(1d)")
tdLog.info("===== step 6 =====")
tdSql.query("select avg(tbcol), sum(tbcol), min(tbcol), max(tbcol), first(tbcol), last(tbcol) from stb interval(1d)")
tdSql.checkData(0, 1, 9.5)
tdSql.checkData(0, 2, 1900)
tdSql.checkData(0, 3, 0)
tdSql.checkData(0, 4, 19)
tdSql.checkData(0, 5, 0)
tdSql.checkData(0, 6, 19)
tdSql.execute("create table strm_avg as select avg(tbcol), sum(tbcol), min(tbcol), max(tbcol), first(tbcol), last(tbcol) from stb interval(1d)")
tdLog.info("===== step 7 =====")
tdSql.query("select avg(tbcol), sum(tbcol), min(tbcol), max(tbcol), first(tbcol), last(tbcol), count(tbcol) from stb where ts < now + 4m interval(1d)")
tdSql.checkData(0, 1, 9.5)
tdSql.checkData(0, 2, 1900)
tdSql.checkData(0, 3, 0)
tdSql.checkData(0, 4, 19)
tdSql.checkData(0, 5, 0)
tdSql.checkData(0, 6, 19)
tdSql.checkData(0, 7, totalNum)
tdLog.info("===== step 8 =====")
tdSql.query("select avg(tbcol), sum(tbcol), min(tbcol), max(tbcol), first(tbcol), last(tbcol), count(tbcol) from stb where ts < now + 4m interval(1d)")
tdSql.checkData(0, 1, 9.5)
tdSql.checkData(0, 2, 1900)
tdSql.checkData(0, 3, 0)
tdSql.checkData(0, 4, 19)
tdSql.checkData(0, 5, 0)
tdSql.checkData(0, 6, 19)
tdSql.checkData(0, 7, totalNum)
tdLog.info("===== step 9 =====")
tdSql.waitedQuery("select * from strm_c3", 1, 120)
tdSql.checkData(0, 1, totalNum)
tdSql.checkData(0, 2, totalNum)
tdSql.checkData(0, 3, totalNum)
tdLog.info("===== step 10 =====")
tdSql.waitedQuery("select * from strm_c31", 1, 30)
for i in range(1, 10):
tdSql.checkData(0, i, totalNum)
tdLog.info("===== step 11 =====")
tdSql.waitedQuery("select * from strm_avg", 1, 20)
tdSql.checkData(0, 1, 9.5)
tdSql.checkData(0, 2, 1900)
tdSql.checkData(0, 3, 0)
tdSql.checkData(0, 4, 19)
tdSql.checkData(0, 5, 0)
tdSql.checkData(0, 6, 19)
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 meters(ts timestamp, f1 int) tags(t1 int);"
)
for i in range(1, 11):
tdSql.execute(
"CREATE table if not exists test{num} using meters tags({num});"
.format(num=i))
print("==============insert 10 tables")
tdSql.query('show tables;')
tdSql.checkRows(10)
print("==============step2")
tdDnodes.stopAll()
path = tdDnodes.getDnodesRootDir()
filename = path + '/dnode1/data/mnode/wal/wal0'
with open(filename, 'rb') as f1:
temp = f1.read()
with open(filename, 'wb') as f2:
f2.write(temp[:-2])
tdDnodes.start(1)
print(
"==============remove last tow bytes of file 'wal0' and restart taosd"
)
print("==============step3")
tdSql.execute("use demo;")
tdSql.query('show tables;')
tdSql.checkRows(9)
for i in range(11, 21):
tdSql.execute(
"CREATE table if not exists test{num} using meters tags({num});"
.format(num=i))
tdSql.query('show tables;')
tdSql.checkRows(19)
print("==============check table numbers and create 10 tables")
def restartTaosd(self, index=1, db_name="db"):
tdDnodes.stop(index)
tdDnodes.startWithoutSleep(index)
tdSql.execute(f"use {db_name}")
def run(self):
tdSql.prepare()
print("============== last_row_cache_0.sim")
tdSql.execute("create database test1 cachelast 0")
tdSql.execute("use test1")
self.insertData()
self.executeQueries()
self.insertData2()
self.executeQueries2()
print("============== alter last cache")
tdSql.execute("alter database test1 cachelast 1")
self.executeQueries2()
tdDnodes.stop(1)
tdDnodes.start(1)
self.executeQueries2()
tdSql.execute("alter database test1 cachelast 0")
self.executeQueries2()
tdDnodes.stop(1)
tdDnodes.start(1)
self.executeQueries2()
print("============== last_row_cache_1.sim")
tdSql.execute("create database test2 cachelast 1")
tdSql.execute("use test2")
self.insertData()
self.executeQueries()
self.insertData2()
self.executeQueries2()
tdDnodes.stop(1)
tdDnodes.start(1)
self.executeQueries2()
tdSql.execute("alter database test2 cachelast 0")
self.executeQueries2()
tdDnodes.stop(1)
tdDnodes.start(1)
self.executeQueries2()
tdSql.execute("alter database test2 cachelast 1")
self.executeQueries2()
tdDnodes.stop(1)
tdDnodes.start(1)
self.executeQueries2()
