def createFuncStream(self, expr, suffix, value):
tbname = "strm_" + suffix
tdLog.info("create stream table %s" % tbname)
tdSql.query("select %s from tb1 interval(1d)" % expr)
tdSql.checkData(0, 1, value)
tdSql.execute("create table %s as select %s from tb1 interval(1d)" %
(tbname, expr))
def tbase304(self):
tdLog.debug("begin tbase304")
# we cannot reset query cache in server side, as a workaround,
# set super table name to mt304, need to change back to mt later
tdSql.execute(
"create table mt304 (ts timestamp, c1 int) tags(t1 int, t2 int)")
tdSql.execute("create table tb1 using mt304 tags(1, 1)")
tdSql.execute("create table tb2 using mt304 tags(1, -1)")
time.sleep(0.1)
tdSql.execute(
"create table strm as select count(*), avg(c1) from mt304 where t2 >= 0 interval(4s) sliding(2s)"
)
tdSql.execute("insert into tb1 values (now,1)")
tdSql.execute("insert into tb2 values (now,2)")
tdSql.waitedQuery("select * from strm", 1, 100)
if tdSql.queryRows < 1 or tdSql.queryRows > 2:
tdLog.exit("rows should be 1 or 2")
tdSql.checkData(0, 1, 1)
tdSql.checkData(0, 2, 1.000000000)
tdSql.execute("alter table mt304 drop tag t2")
tdSql.execute("insert into tb2 values (now,2)")
tdSql.execute("insert into tb1 values (now,1)")
tdSql.query("select * from strm")
tdSql.execute("alter table mt304 add tag t2 int")
tdLog.sleep(1)
tdSql.query("select * from strm")
def run(self):
nodes = Nodes()
ctest = ClusterTest(nodes.node1.hostName)
ctest.connectDB()
ctest.createSTable(3)
ctest.run()
tdSql.init(ctest.conn.cursor(), False)
nodes.node2.stopTaosd()
tdSql.execute("use %s" % ctest.dbName)
tdSql.query("show vgroups")
vnodeID = tdSql.getData(0, 0)
nodes.node2.removeDataForVnode(vnodeID)
nodes.node2.startTaosd()
# Wait for vnode file to recover
for i in range(10):
tdSql.query("select count(*) from t0")
tdLog.sleep(10)
for i in range(10):
tdSql.query("select count(*) from t0")
tdSql.checkData(0, 0, 1000)
tdSql.close()
tdLog.success("%s successfully executed" % __file__)
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")
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):
tdSql.prepare()
tdSql.execute("create table cars(ts timestamp, s int) tags(id int)")
tdSql.execute("create table car0 using cars tags(0)")
tdSql.execute("create table car1 using cars tags(1)")
tdSql.execute("create table car2 using cars tags(2)")
tdSql.execute("create table car3 using cars tags(3)")
tdSql.execute("create table car4 using cars tags(4)")
tdSql.execute("insert into car0 values('2019-01-01 00:00:00.103', 1)")
tdSql.execute("insert into car1 values('2019-01-01 00:00:00.234', 1)")
tdSql.execute("insert into car0 values('2019-01-01 00:00:01.012', 1)")
tdSql.execute("insert into car0 values('2019-01-01 00:00:02.003', 1)")
tdSql.execute("insert into car2 values('2019-01-01 00:00:02.328', 1)")
tdSql.execute("insert into car0 values('2019-01-01 00:00:03.139', 1)")
tdSql.execute("insert into car0 values('2019-01-01 00:00:04.348', 1)")
tdSql.execute("insert into car0 values('2019-01-01 00:00:05.783', 1)")
tdSql.execute("insert into car1 values('2019-01-01 00:00:01.893', 1)")
tdSql.execute("insert into car1 values('2019-01-01 00:00:02.712', 1)")
tdSql.execute("insert into car1 values('2019-01-01 00:00:03.982', 1)")
tdSql.execute("insert into car3 values('2019-01-01 00:00:01.389', 1)")
tdSql.execute("insert into car4 values('2019-01-01 00:00:01.829', 1)")
tdSql.error("create table strm as select count(*) from cars")
tdSql.execute(
"create table strm as select count(*) from cars interval(4s)")
tdSql.waitedQuery("select * from strm", 2, 100)
tdSql.checkData(0, 1, 11)
tdSql.checkData(1, 1, 2)
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.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 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 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 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()
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):
tbNum = 10
rowNum = 20
ts_begin = 1633017600000
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 (%d, %d, %d)" %
(i, ts_begin + 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()
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)
## test case for https://jira.taosdata.com:18080/browse/TD-2488
tdSql.execute("create table m1(ts timestamp, k int) tags(a int)")
tdSql.execute("create table t1 using m1 tags(1)")
tdSql.execute("create table t2 using m1 tags(2)")
tdSql.execute("insert into t1 values('2020-1-1 1:1:1', 1)")
tdSql.execute("insert into t1 values('2020-1-1 1:10:1', 2)")
tdSql.execute("insert into t2 values('2020-1-1 1:5:1', 99)")
tdSql.query("select count(*) from m1 where ts = '2020-1-1 1:5:1' ")
tdSql.checkRows(1)
tdSql.checkData(0, 0, 1)
tdDnodes.stop(1)
tdDnodes.start(1)
tdSql.query("select count(*) from m1 where ts = '2020-1-1 1:5:1' ")
tdSql.checkRows(1)
tdSql.checkData(0, 0, 1)
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):
nodes = Nodes()
ctest = ClusterTest(nodes.node1.hostName)
ctest.connectDB()
tdSql.init(ctest.conn.cursor(), False)
## Test case 1 ##
tdLog.info("Test case 1 repeat %d times" % ctest.repeat)
for i in range(ctest.repeat):
tdLog.info("Start Round %d" % (i + 1))
replica = random.randint(1, 3)
ctest.createSTable(replica)
ctest.run()
tdLog.sleep(10)
tdSql.query("select count(*) from %s.%s" %
(ctest.dbName, ctest.stbName))
tdSql.checkData(0, 0, ctest.numberOfRecords * ctest.numberOfTables)
tdLog.info("Round %d completed" % (i + 1))
tdSql.close()
tdLog.success("%s successfully executed" % __file__)
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 database db_vplu")
tdSql.execute("use db_vplu")
tdSql.execute(
"CREATE table if not exists st (ts timestamp, speed int) tags(id int)"
)
tdSql.execute(
"CREATE table if not exists st_vplu (ts timestamp, speed int) tags(id int)"
)
print("==============step2")
tdSql.execute("drop table st")
tdSql.query("show stables")
tdSql.checkRows(1)
tdSql.checkData(0, 0, "st_vplu")
tdDnodes.stopAll()
tdDnodes.start(1)
tdSql.execute("use db_vplu")
tdSql.query("show stables")
tdSql.checkRows(1)
tdSql.checkData(0, 0, "st_vplu")
tdSql.execute("drop database db")
tdSql.query("show databases")
tdSql.checkRows(1)
tdSql.checkData(0, 0, "db_vplu")
tdDnodes.stopAll()
tdDnodes.start(1)
tdSql.query("show databases")
tdSql.checkRows(1)
tdSql.checkData(0, 0, "db_vplu")
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 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 executeQueries2(self):
# For stable
tc = self.ts + 6 * 3600000
tdSql.query("select last_row(c1) from st where ts < %d " % tc)
tdSql.checkData(0, 0, -1)
tc = self.ts + 8 * 3600000
tdSql.query("select last_row(*) from st where ts < %d " % tc)
tdSql.checkData(0, 1, None)
tdSql.query("select last_row(*) from st")
tdSql.checkData(0, 1, None)
tc = self.ts + 4 * 3600000
tdSql.query("select last_row(*) from st where ts < %d " % tc)
tdSql.checkData(0, 1, None)
tc1 = self.ts + 1 * 3600000
tc2 = self.ts + 4 * 3600000
tdSql.query("select last_row(*) from st where ts > %d and ts <= %d" %
(tc1, tc2))
tdSql.checkData(0, 1, None)
# For table
tc = self.ts + 6 * 3600000
tdSql.query("select last_row(*) from %s%d where ts <= %d" %
(self.perfix, 1, tc))
tdSql.checkData(0, 1, -1)
tc = self.ts + 8 * 3600000
tdSql.query("select last_row(*) from %s%d where ts <= %d" %
(self.perfix, 1, tc))
tdSql.checkData(0, 1, None)
tdSql.query("select last_row(*) from %s%d" % (self.perfix, 1))
tdSql.checkData(0, 1, None)
tc = self.ts + 4 * 3600000
tdSql.query("select last_row(*) from %s%d where ts <= %d" %
(self.perfix, 1, tc))
tdSql.checkData(0, 1, None)
tc1 = self.ts + 1 * 3600000
tc2 = self.ts + 4 * 3600000
tdSql.query("select last_row(*) from st where ts > %d and ts <= %d" %
(tc1, tc2))
tdSql.checkData(0, 1, None)
def run(self):
tdSql.prepare()
# test case for https://jira.taosdata.com:18080/browse/TD-4735
tdSql.execute('''create stable stable_1
(ts timestamp , q_int int , q_bigint bigint , q_smallint smallint , q_tinyint tinyint,
q_bool bool , q_binary binary(20) , q_nchar nchar(20) ,
q_float float , q_double double , q_ts timestamp)
tags(loc nchar(20) , t_int int , t_bigint bigint , t_smallint smallint , t_tinyint tinyint,
t_bool bool , t_binary binary(20) , t_nchar nchar(20) ,
t_float float , t_double double , t_ts timestamp);''')
tdSql.execute('''create table table_0 using stable_1
tags('table_0' , '0' , '0' , '0' , '0' , 0 , '0' , '0' , '0' , '0' ,'0')'''
)
tdSql.execute('''create table table_1 using stable_1
tags('table_1' , '2147483647' , '9223372036854775807' , '32767' , '127' , 1 ,
'binary1' , 'nchar1' , '1' , '11' , \'1999-09-09 09:09:09.090\')'''
)
tdSql.execute('''create table table_2 using stable_1
tags('table_2' , '-2147483647' , '-9223372036854775807' , '-32767' , '-127' , false ,
'binary2' , 'nchar2nchar2' , '-2.2' , '-22.22' , \'2099-09-09 09:09:09.090\')'''
)
tdSql.execute('''create table table_3 using stable_1
tags('table_3' , '3' , '3' , '3' , '3' , true , 'binary3' , 'nchar3' , '33.33' , '3333.3333' , '0')'''
)
tdSql.execute('''create table table_4 using stable_1
tags('table_4' , '4' , '4' , '4' , '4' , false , 'binary4' , 'nchar4' , '-444.444' , '-444444.444444' , '0')'''
)
tdSql.execute('''create table table_5 using stable_1
tags('table_5' , '5' , '5' , '5' , '5' , true , 'binary5' , 'nchar5' , '5555.5555' , '55555555.55555555' , '0')'''
)
#regular table
tdSql.execute('''create table regular_table_1
(ts timestamp , q_int int , q_bigint bigint , q_smallint smallint , q_tinyint tinyint,
q_bool bool , q_binary binary(20) , q_nchar nchar(20) ,
q_float float , q_double double , q_ts timestamp) ;''')
for i in range(self.num):
tdSql.execute(
'''insert into table_0 values(%d, %d, %d, %d, %d, 0, 'binary.%s', 'nchar.%s', %f, %f, %d)'''
% (self.ts + i, i, i, i, i, i, i, i, i, self.ts + i))
tdSql.execute(
'''insert into table_1 values(%d, %d, %d, %d, %d, 1, 'binary1.%s', 'nchar1.%s', %f, %f, %d)'''
% (self.ts + i, 2147483647 - i,
9223372036854775807 - i, 32767 - i, 127 - i, i, i,
random.random(), random.random(), 1262304000001 + i))
tdSql.execute(
'''insert into table_2 values(%d, %d, %d, %d, %d, true, 'binary2.%s', 'nchar2nchar2.%s', %f, %f, %d)'''
% (self.ts + i, -2147483647 + i, -9223372036854775807 + i,
-32767 + i, -127 + i, i, i, random.uniform(
-1, 0), random.uniform(-1, 0), 1577836800001 + i))
tdSql.execute(
'''insert into table_3 values(%d, %d, %d, %d, %d, false, 'binary3.%s', 'nchar3.%s', %f, %f, %d)'''
% (self.ts + i, random.randint(-2147483647, 2147483647),
random.randint(-9223372036854775807, 9223372036854775807),
random.randint(-32767, 32767), random.randint(-127, 127),
random.randint(-100, 100), random.randint(
-10000, 10000), random.uniform(-100000, 100000),
random.uniform(-1000000000, 1000000000), self.ts + i))
tdSql.execute(
'''insert into table_4 values(%d, %d, %d, %d, %d, true, 'binary4.%s', 'nchar4.%s', %f, %f, %d)'''
% (self.ts + i, i, i, i, i, i, i, i, i, self.ts + i))
tdSql.execute(
'''insert into table_5 values(%d, %d, %d, %d, %d, false, 'binary5.%s', 'nchar5.%s', %f, %f, %d)'''
% (self.ts + i, i, i, i, i, i, i, i, i, self.ts + i))
tdSql.execute(
'''insert into regular_table_1 values(%d, %d, %d, %d, %d, 0, 'binary.%s', 'nchar.%s', %f, %f, %d)'''
% (self.ts + i, i, i, i, i, i, i, i, i, self.ts + i))
tdSql.execute(
'''insert into regular_table_1 values(%d, %d, %d, %d, %d, 1, 'binary1.%s', 'nchar1.%s', %f, %f, %d)'''
% (self.ts + 100 + i, 2147483647 - i,
9223372036854775807 - i, 32767 - i, 127 - i, i, i,
random.random(), random.random(), 1262304000001 + i))
tdSql.execute(
'''insert into regular_table_1 values(%d, %d, %d, %d, %d, true, 'binary2.%s', 'nchar2nchar2.%s', %f, %f, %d)'''
% (self.ts + 200 + i, -2147483647 + i, -9223372036854775807 +
i, -32767 + i, -127 + i, i, i, random.uniform(
-1, 0), random.uniform(-1, 0), 1577836800001 + i))
tdSql.execute(
'''insert into regular_table_1 values(%d, %d, %d, %d, %d, false, 'binary3.%s', 'nchar3.%s', %f, %f, %d)'''
% (self.ts + 300 + i, random.randint(-2147483647, 2147483647),
random.randint(-9223372036854775807, 9223372036854775807),
random.randint(-32767, 32767), random.randint(-127, 127),
random.randint(-100, 100), random.randint(
-10000, 10000), random.uniform(-100000, 100000),
random.uniform(-1000000000, 1000000000), self.ts + i))
tdSql.execute(
'''insert into regular_table_1 values(%d, %d, %d, %d, %d, true, 'binary4.%s', 'nchar4.%s', %f, %f, %d)'''
% (self.ts + 400 + i, i, i, i, i, i, i, i, i, self.ts + i))
tdSql.execute(
'''insert into regular_table_1 values(%d, %d, %d, %d, %d, false, 'binary5.%s', 'nchar5.%s', %f, %f, %d)'''
% (self.ts + 500 + i, i, i, i, i, i, i, i, i, self.ts + i))
sql = '''select * from stable_1'''
tdSql.query(sql)
tdSql.checkRows(6 * self.num)
sql = '''select * from regular_table_1'''
tdSql.query(sql)
tdSql.checkRows(6 * self.num)
tdLog.info("=======last_row(*)========")
sql = '''select last_row(*) from stable_1;'''
tdSql.query(sql)
tdSql.checkData(0, 1, self.num - 1)
sql = '''select last_row(*) from regular_table_1;'''
tdSql.query(sql)
tdSql.checkData(0, 1, self.num - 1)
sql = '''select * from stable_1
where loc = 'table_0';'''
tdSql.query(sql)
tdSql.checkRows(self.num)
sql = '''select last_row(*) from
(select * from stable_1
where loc = 'table_0');'''
tdSql.query(sql)
tdSql.checkRows(1)
sql = '''select last_row(*) from
(select * from stable_1);'''
tdSql.query(sql)
tdSql.checkData(0, 1, self.num - 1)
tdSql.checkData(0, 2, self.num - 1)
tdSql.checkData(0, 3, self.num - 1)
tdSql.checkData(0, 4, self.num - 1)
tdSql.checkData(0, 5, 'False')
tdSql.checkData(0, 6, 'binary5.9')
tdSql.checkData(0, 7, 'nchar5.9')
tdSql.checkData(0, 8, 9.00000)
tdSql.checkData(0, 9, 9.000000000)
tdSql.checkData(0, 10, '2020-09-13 20:26:40.009')
tdSql.checkData(0, 11, 'table_5')
tdSql.checkData(0, 12, 5)
tdSql.checkData(0, 13, 5)
tdSql.checkData(0, 14, 5)
tdSql.checkData(0, 15, 5)
tdSql.checkData(0, 16, 'True')
tdSql.checkData(0, 17, 'binary5')
tdSql.checkData(0, 18, 'nchar5')
tdSql.checkData(0, 21, '1970-01-01 08:00:00.000')
sql = '''select * from regular_table_1 ;'''
tdSql.query(sql)
tdSql.checkRows(6 * self.num)
sql = '''select last_row(*) from
(select * from regular_table_1);'''
tdSql.query(sql)
tdSql.checkRows(1)
tdSql.checkData(0, 1, self.num - 1)
tdSql.checkData(0, 2, self.num - 1)
tdSql.checkData(0, 3, self.num - 1)
tdSql.checkData(0, 4, self.num - 1)
tdSql.checkData(0, 5, 'False')
tdSql.checkData(0, 6, 'binary5.9')
tdSql.checkData(0, 7, 'nchar5.9')
tdSql.checkData(0, 8, 9.00000)
tdSql.checkData(0, 9, 9.000000000)
tdSql.checkData(0, 10, '2020-09-13 20:26:40.009')
# incorrect result, not support nest > 2
sql = '''select last_row(*) from
((select * from table_0) union all
(select * from table_1) union all
(select * from table_2));'''
tdSql.error(sql)
#tdSql.checkRows(1)
#tdSql.checkData(0,1,self.num-1)
#tdSql.checkData(0,2,self.num-1)
#tdSql.checkData(0,3,self.num-1)
#tdSql.checkData(0,4,self.num-1)
#tdSql.checkData(0,5,'False')
#tdSql.checkData(0,6,'binary.9')
#tdSql.checkData(0,7,'nchar.9')
#tdSql.checkData(0,8,9.00000)
#tdSql.checkData(0,9,9.000000000)
#tdSql.checkData(0,10,'2020-09-13 20:26:40.009')
# bug 5055
# sql = '''select last_row(*) from
# ((select * from stable_1) union all
# (select * from table_1) union all
# (select * from regular_table_1));'''
# tdSql.query(sql)
# tdSql.checkData(0,1,self.num-1)
sql = '''select last_row(*) from
((select last_row(*) from table_0) union all
(select last_row(*) from table_1) union all
(select last_row(*) from table_2));'''
tdSql.error(sql)
#tdSql.checkRows(1)
#tdSql.checkData(0,1,self.num-1)
#tdSql.checkData(0,2,self.num-1)
#tdSql.checkData(0,3,self.num-1)
#tdSql.checkData(0,4,self.num-1)
#tdSql.checkData(0,5,'False')
#tdSql.checkData(0,6,'binary.9')
#tdSql.checkData(0,7,'nchar.9')
#tdSql.checkData(0,8,9.00000)
#tdSql.checkData(0,9,9.000000000)
#tdSql.checkData(0,10,'2020-09-13 20:26:40.009')
# bug 5055
# sql = '''select last_row(*) from
# ((select last_row(*) from stable_1) union all
# (select last_row(*) from table_1) union all
# (select last_row(*) from regular_table_1));'''
# tdSql.query(sql)
# tdSql.checkData(0,1,self.num-1)
sql = '''select last_row(*) from
((select * from table_0 limit 5 offset 5) union all
(select * from table_1 limit 5 offset 5) union all
(select * from regular_table_1 limit 5 offset 5));'''
tdSql.error(sql)
#tdSql.checkRows(1)
#tdSql.checkData(0,1,self.num-1)
#tdSql.checkData(0,2,self.num-1)
#tdSql.checkData(0,3,self.num-1)
#tdSql.checkData(0,4,self.num-1)
#tdSql.checkData(0,5,'False')
#tdSql.checkData(0,6,'binary.9')
#tdSql.checkData(0,7,'nchar.9')
#tdSql.checkData(0,8,9.00000)
#tdSql.checkData(0,9,9.000000000)
#tdSql.checkData(0,10,'2020-09-13 20:26:40.009')
sql = '''select last_row(*) from
(select * from stable_1)
having q_int>5;'''
tdLog.info(sql)
tdSql.error(sql)
try:
tdSql.execute(sql)
tdLog.exit(" having only works with group by")
except Exception as e:
tdLog.info(repr(e))
tdLog.info("invalid operation: having only works with group by")
def run(self):
# cluster environment set up
tdLog.info("Test case 7, 10")
nodes = Nodes()
ctest = ClusterTest(nodes.node1.hostName)
ctest.connectDB()
tdSql.init(ctest.conn.cursor(), False)
nodes.node1.stopTaosd()
tdSql.query("show dnodes")
tdSql.checkRows(3)
tdSql.checkData(0, 4, "offline")
tdSql.checkData(1, 4, "ready")
tdSql.checkData(2, 4, "ready")
nodes.node1.startTaosd()
tdSql.checkRows(3)
tdSql.checkData(0, 4, "ready")
tdSql.checkData(1, 4, "ready")
tdSql.checkData(2, 4, "ready")
nodes.node2.stopTaosd()
tdSql.query("show dnodes")
tdSql.checkRows(3)
tdSql.checkData(0, 4, "ready")
tdSql.checkData(1, 4, "offline")
tdSql.checkData(2, 4, "ready")
nodes.node2.startTaosd()
tdSql.checkRows(3)
tdSql.checkData(0, 4, "ready")
tdSql.checkData(1, 4, "ready")
tdSql.checkData(2, 4, "ready")
nodes.node3.stopTaosd()
tdSql.query("show dnodes")
tdSql.checkRows(3)
tdSql.checkData(0, 4, "ready")
tdSql.checkData(1, 4, "ready")
tdSql.checkData(2, 4, "offline")
nodes.node3.startTaosd()
tdSql.checkRows(3)
tdSql.checkData(0, 4, "ready")
tdSql.checkData(1, 4, "ready")
tdSql.checkData(2, 4, "ready")
tdSql.close()
tdLog.success("%s successfully executed" % __file__)
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):
tbNum = 10
rowNum = 20
totalNum = tbNum * rowNum
tdSql.prepare()
tdLog.info("===== step1 =====")
tdSql.execute(
"create table stb0(ts timestamp, col1 int, col2 float) tags(tgcol int)"
)
for i in range(tbNum):
tdSql.execute("create table tb%d using stb0 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("===== step2 =====")
tdSql.query("select count(col1) from tb0 interval(1d)")
tdSql.checkData(0, 1, rowNum)
tdSql.query("show tables")
tdSql.checkRows(tbNum)
tdSql.execute(
"create table s0 as select count(col1) from tb0 interval(1d)")
tdSql.query("show tables")
tdSql.checkRows(tbNum + 1)
tdLog.info("===== step3 =====")
tdSql.waitedQuery("select * from s0", 1, 120)
try:
tdSql.checkData(0, 1, rowNum)
except Exception as e:
tdLog.info(repr(e))
tdLog.info("===== step4 =====")
tdSql.execute("drop table s0")
tdSql.query("show tables")
try:
tdSql.checkRows(tbNum)
except Exception as e:
tdLog.info(repr(e))
tdLog.info("===== step5 =====")
tdSql.error("select * from s0")
tdLog.info("===== step6 =====")
tdSql.execute(
"create table s0 as select count(*), count(col1), count(col2) from tb0 interval(1d)"
)
tdSql.query("show tables")
try:
tdSql.checkRows(tbNum + 1)
except Exception as e:
tdLog.info(repr(e))
tdLog.info("===== step7 =====")
tdSql.waitedQuery("select * from s0", 1, 120)
try:
tdSql.checkData(0, 1, rowNum)
tdSql.checkData(0, 2, rowNum)
tdSql.checkData(0, 3, rowNum)
except Exception as e:
tdLog.info(repr(e))
tdLog.info("===== step8 =====")
tdSql.query(
"select count(*), count(col1), count(col2) from stb0 interval(1d)")
try:
tdSql.checkData(0, 1, totalNum)
tdSql.checkData(0, 2, totalNum)
tdSql.checkData(0, 3, totalNum)
except Exception as e:
tdLog.info(repr(e))
tdSql.query("show tables")
tdSql.checkRows(tbNum + 1)
tdSql.execute(
"create table s1 as select count(*), count(col1), count(col2) from stb0 interval(1d)"
)
tdSql.query("show tables")
tdSql.checkRows(tbNum + 2)
tdLog.info("===== step9 =====")
tdSql.waitedQuery("select * from s1", 1, 120)
try:
tdSql.checkData(0, 1, totalNum)
tdSql.checkData(0, 2, totalNum)
tdSql.checkData(0, 3, totalNum)
except Exception as e:
tdLog.info(repr(e))
tdLog.info("===== step10 =====")
tdSql.execute("drop table s1")
tdSql.query("show tables")
try:
tdSql.checkRows(tbNum + 1)
except Exception as e:
tdLog.info(repr(e))
tdLog.info("===== step11 =====")
tdSql.error("select * from s1")
tdLog.info("===== step12 =====")
tdSql.execute(
"create table s1 as select count(col1) from stb0 interval(1d)")
tdSql.query("show tables")
try:
tdSql.checkRows(tbNum + 2)
except Exception as e:
tdLog.info(repr(e))
tdLog.info("===== step13 =====")
tdSql.waitedQuery("select * from s1", 1, 120)
try:
tdSql.checkData(0, 1, totalNum)
#tdSql.checkData(0, 2, None)
#tdSql.checkData(0, 3, None)
except Exception as e:
tdLog.info(repr(e))
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):
# tdSql.execute("drop database db ")
tdSql.prepare()
tdSql.execute(
"create table st (ts timestamp, num int, value int , t_instance int) tags (loc nchar(30))"
)
node = 5
number = 10
for n in range(node):
for m in range(number):
dt = m * 300000 + n * 60000 # collecting'frequency is 10s
args1 = (n, n, self.ts1 + dt, n, 100 + 2 * m + 2 * n,
10 + m + n)
# args2=(n,self.ts2+dt,n,120+n,15+n)
tdSql.execute(
"insert into t%d using st tags('beijing%d') values(%d, %d, %d, %d)"
% args1)
# tdSql.execute("insert into t1 using st tags('shanghai') values(%d, %d, %d, %d)" % args2)
# interval function
tdSql.query("select avg(value) from st interval(10m)")
# print(tdSql.queryResult)
tdSql.checkRows(6)
tdSql.checkData(0, 0, "2020-07-01 04:20:00")
tdSql.checkData(1, 1, 107.4)
# subquery with interval
tdSql.query(
"select avg(avg_val) from(select avg(value) as avg_val from st where loc='beijing0' interval(10m));"
)
tdSql.checkData(0, 0, 109.0)
# subquery with interval and select two Column in parent query
tdSql.error(
"select ts,avg(avg_val) from(select avg(value) as avg_val from st where loc='beijing0' interval(10m));"
)
# subquery with interval and sliding
tdSql.query(
"select avg(value) as avg_val from st where loc='beijing0' interval(8m) sliding(30s) limit 1;"
)
tdSql.checkData(0, 0, "2020-07-01 04:17:00")
tdSql.checkData(0, 1, 100)
tdSql.query(
"select avg(avg_val) from(select avg(value) as avg_val from st where loc='beijing1' interval(8m) sliding(30s));"
)
tdSql.checkData(0, 0, 111)
# subquery with interval and offset
tdSql.query(
"select avg(value) as avg_val from st where loc='beijing0' interval(5m,1m);"
)
tdSql.checkData(0, 0, "2020-07-01 04:21:00")
tdSql.checkData(0, 1, 100)
tdSql.query(
"select avg(avg_val) from(select avg(value) as avg_val from st where loc='beijing0' interval(5m,1m) group by loc);"
)
tdSql.checkData(0, 0, 109)
# subquery with interval,sliding and group by ; parent query with interval
tdSql.query(
"select avg(value) as avg_val from st where loc='beijing0' interval(8m) sliding(1m) group by loc limit 1 offset 52 ;"
)
tdSql.checkData(0, 0, "2020-07-01 05:09:00")
tdSql.checkData(0, 1, 118)
tdSql.query(
"select avg(avg_val) as ncst from(select avg(value) as avg_val from st where loc!='beijing0' interval(8m) sliding(1m) group by loc ) interval(5m);"
)
tdSql.checkData(1, 1, 105)
# # subquery and parent query with interval and sliding
tdSql.query(
"select avg(avg_val) from(select avg(value) as avg_val from st where loc='beijing1' interval(8m) sliding(5m)) interval(10m) sliding(2m);"
)
tdSql.checkData(29, 0, "2020-07-01 05:10:00.000")
# subquery and parent query with top and bottom
tdSql.query(
"select top(avg_val,2) from(select avg(value) as avg_val,num from st where loc!='beijing0' group by num) order by avg_val desc;"
)
tdSql.checkData(0, 1, 117)
tdSql.query(
"select bottom(avg_val,3) from(select avg(value) as avg_val,num from st where loc!='beijing0' group by num) order by avg_val asc;"
)
tdSql.checkData(0, 1, 111)
#
tdSql.query(
"select top(avg_val,2) from(select avg(value) as avg_val from st where loc='beijing1' interval(8m) sliding(3m));"
)
tdSql.checkData(0, 1, 120)
# TS-802
tdSql.query("select first(*) from st interval(5m) limit 10")
tdSql.checkRows(10)
tdSql.query(
"select * from (select first(*) from st interval(5m) limit 10) order by ts"
)
tdSql.checkRows(10)
tdSql.query(
"select * from (select first(*) from st interval(5m) limit 10) order by ts desc"
)
tdSql.checkRows(10)
# clear env
testcaseFilename = os.path.split(__file__)[-1]
os.system("rm -rf ./insert_res.txt")
os.system("rm -rf wal/%s.sql" % testcaseFilename)
def run(self):
tdSql.prepare()
self.insertData()
tdSql.query("select count(*) val from st group by tbname")
tdSql.checkRows(10)
tdSql.query(
"select * from (select count(*) val from st group by tbname)")
tdSql.checkRows(10)
tdSql.query(
"select * from (select count(*) val from st group by tbname) a where a.val < 20"
)
tdSql.checkRows(5)
tdSql.query(
"select * from (select count(*) val from st group by tbname) a where a.val > 20"
)
tdSql.checkRows(4)
tdSql.query(
"select * from (select count(*) val from st group by tbname) a where a.val = 20"
)
tdSql.checkRows(1)
tdSql.query(
"select * from (select count(*) val from st group by tbname) a where a.val <= 20"
)
tdSql.checkRows(6)
tdSql.query(
"select * from (select count(*) val from st group by tbname) a where a.val >= 20"
)
tdSql.checkRows(5)
tdSql.query(
"select count(*) from (select first(tagtype) val from st interval(30s)) a where a.val > 20"
)
tdSql.checkRows(1)
tdSql.checkData(0, 0, 1)
tdSql.query(
"select count(*) from (select first(tagtype) val from st interval(30s)) a where a.val >= 20"
)
tdSql.checkData(0, 0, 2)
tdSql.query(
"select count(*) from (select first(tagtype) val from st interval(30s)) a where a.val < 20"
)
tdSql.checkData(0, 0, 63)
tdSql.query(
"select count(*) from (select first(tagtype) val from st interval(30s)) a where a.val <= 20"
)
tdSql.checkData(0, 0, 64)
tdSql.query(
"select count(*) from (select first(tagtype) val from st interval(30s)) a where a.val = 20"
)
tdSql.checkData(0, 0, 1)
tdSql.query(
"select count(*) from (select first(tagtype) val from st interval(30s)) a where a.val > 20"
)
tdSql.checkData(0, 0, 1)
tdSql.query(
"select count(*) from (select first(tagtype) val from st interval(30s)) a where a.val >= 20"
)
tdSql.checkData(0, 0, 2)
tdSql.query(
"select count(*) from (select first(tagtype) val from st interval(30s)) a where a.val < 20"
)
tdSql.checkData(0, 0, 63)
tdSql.query(
"select count(*) from (select first(tagtype) val from st interval(30s)) a where a.val <= 20"
)
tdSql.checkData(0, 0, 64)
tdSql.query(
"select count(*) from (select first(tagtype) val from st interval(30s)) a where a.val = 20"
)
tdSql.checkData(0, 0, 1)
tdSql.query(
"select count(*) from (select last(tagtype) val from st interval(30s)) a where a.val > 20"
)
tdSql.checkData(0, 0, 3)
tdSql.query(
"select count(*) from (select last(tagtype) val from st interval(30s)) a where a.val >= 20"
)
tdSql.checkData(0, 0, 5)
tdSql.query(
"select count(*) from (select last(tagtype) val from st interval(30s)) a where a.val < 20"
)
tdSql.checkData(0, 0, 60)
tdSql.query(
"select count(*) from (select last(tagtype) val from st interval(30s)) a where a.val <= 20"
)
tdSql.checkData(0, 0, 62)
tdSql.query(
"select count(*) from (select last(tagtype) val from st interval(30s)) a where a.val = 20"
)
tdSql.checkData(0, 0, 2)
def run(self):
ts = 1500000000000
tbNum = 10
rowNum = 20
tdSql.prepare()
tdLog.info("===== step1 =====")
tdSql.execute(
"create table stb0(ts timestamp, col1 binary(20), col2 nchar(20)) tags(tgcol int)")
for i in range(tbNum):
tdSql.execute("create table tb%d using stb0 tags(%d)" % (i, i))
for j in range(rowNum):
tdSql.execute(
"insert into tb%d values (%d, 'binary%d', 'nchar%d')" %
(i, ts + 60000 * j, j, j))
tdSql.execute("insert into tb0 values(%d, null, null)" % (ts + 10000000))
time.sleep(0.1)
tdLog.info("===== step2 =====")
tdSql.query(
"select count(*), count(col1), count(col2) from stb0 interval(1d)")
tdSql.checkData(0, 1, rowNum * tbNum + 1)
tdSql.checkData(0, 2, rowNum * tbNum)
tdSql.checkData(0, 3, rowNum * tbNum)
tdSql.query("show tables")
tdSql.checkRows(tbNum)
tdSql.execute(
"create table s0 as select count(*), count(col1), count(col2) from stb0 interval(1d)")
tdSql.query("show tables")
tdSql.checkRows(tbNum + 1)
tdLog.info("===== step3 =====")
tdSql.waitedQuery("select * from s0", 1, 120)
try:
tdSql.checkData(0, 1, rowNum * tbNum + 1)
tdSql.checkData(0, 2, rowNum * tbNum)
tdSql.checkData(0, 3, rowNum * tbNum)
except Exception as e:
tdLog.info(repr(e))
tdLog.info("===== step4 =====")
tdSql.execute("drop table s0")
tdSql.query("show tables")
tdSql.checkRows(tbNum)
tdLog.info("===== step5 =====")
tdSql.error("select * from s0")
tdLog.info("===== step6 =====")
time.sleep(0.1)
tdSql.execute(
"create table s0 as select count(*), count(col1), count(col2) from tb0 interval(1d)")
tdSql.query("show tables")
tdSql.checkRows(tbNum + 1)
tdLog.info("===== step7 =====")
tdSql.waitedQuery("select * from s0", 1, 120)
try:
tdSql.checkData(0, 1, rowNum + 1)
tdSql.checkData(0, 2, rowNum)
tdSql.checkData(0, 3, rowNum)
except Exception as e:
tdLog.info(repr(e))
tdLog.info("===== step8 =====")
tdSql.query(
"select count(*), count(col1), count(col2) from stb0 interval(1d)")
tdSql.checkData(0, 1, rowNum * tbNum + 1)
tdSql.checkData(0, 2, rowNum * tbNum)
tdSql.checkData(0, 3, rowNum * tbNum)
tdSql.query("show tables")
tdSql.checkRows(tbNum + 1)
def checkStreamData(self, suffix, value):
sql = "select * from strm_" + suffix
tdSql.waitedQuery(sql, 1, 120)
tdSql.checkData(0, 1, value)
