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 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 run(self):
tdSql.prepare()
print("==============step1")
tdSql.execute(
"create table db.cars(ts timestamp, c int) tags(id int);")
tdSql.execute("create database db2")
tdSql.error("create table db2.car1 using db.cars tags(1)")
tdSql.error(
"insert into db2.car1 using db1.cars tags(1) values(now, 1);")
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("test case for TS-783")
tdSql.execute("drop table if exists db.state1;")
tdSql.execute("create table db.state1 (ts timestamp, c1 int);")
tdSql.error("create table db.test1 using db.state1 tags('tt');")
tdSql.execute("drop table if exists db.state2;")
tdSql.execute("create table db.state2 (ts timestamp, c1 int) tags (t binary(20));")
tdSql.query("create table db.test2 using db.state2 tags('tt');")
tdSql.error("create table db.test22 using db.test2 tags('tt');")
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 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()
print("==============step1")
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")
try:
tdSql.execute("drop table db.st")
except Exception as e:
tdLog.exit(e)
tdSql.error("select * from db.st")
tdSql.error("select * from db.tb")
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()
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)
# case for defect: https://jira.taosdata.com:18080/browse/TD-2693
tdSql.execute("create database db2")
tdSql.execute("use db2")
tdSql.execute("create table stb(ts timestamp, c int) tags(t int)")
tdSql.error(
"insert into db2.tb6 using db2.stb tags(1) values(now 1) tb2 using db2. tags( )values(now 2)"
)
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")
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()
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):
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)
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)")
# Fix defect: https://jira.taosdata.com:18080/browse/TD-2700
tdSql.execute("create database test")
tdSql.execute("use test")
tdSql.execute("create table t1(ts timestamp, k int)")
tdSql.execute("insert into t1 values(1500000001000, 0)")
tdSql.query("select sum(k) from t1 interval(1d) sliding(1h)")
tdSql.checkRows(24)
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):
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 run(self):
tdSql.prepare()
# test case for https://jira.taosdata.com:18080/browse/TD-5213
print(
"==============step1, regular table, 1 ts + 4094 cols + 1 binary=============="
)
startTime = time.time()
sql = "create table regular_table_1(ts timestamp, "
for i in range(4094):
sql += "col%d int, " % (i + 1)
sql += "col4095 binary(22))"
tdLog.info(len(sql))
tdSql.execute(sql)
for i in range(self.num):
sql = "insert into regular_table_1 values(%d, "
for j in range(4094):
str = "'%s', " % random.randint(0, 1000)
sql += str
sql += "'%s')" % self.get_random_string(22)
tdSql.execute(sql % (self.ts + i))
time.sleep(1)
tdSql.query("select count(*) from regular_table_1")
tdSql.checkData(0, 0, self.num)
tdSql.query("select * from regular_table_1")
tdSql.checkRows(self.num)
tdSql.checkCols(4096)
endTime = time.time()
print("total time %ds" % (endTime - startTime))
#insert in order
tdLog.info('test insert in order')
for i in range(self.num):
sql = "insert into regular_table_1 (ts,col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col4095) values(%d, "
for j in range(10):
str = "'%s', " % random.randint(0, 1000)
sql += str
sql += "'%s')" % self.get_random_string(22)
tdSql.execute(sql % (self.ts + i + 1000))
time.sleep(1)
tdSql.query("select count(*) from regular_table_1")
tdSql.checkData(0, 0, 2 * self.num)
tdSql.query("select * from regular_table_1")
tdSql.checkRows(2 * self.num)
tdSql.checkCols(4096)
#insert out of order
tdLog.info('test insert out of order')
for i in range(self.num):
sql = "insert into regular_table_1 (ts,col123,col2213,col331,col41,col523,col236,col71,col813,col912,col1320,col4095) values(%d, "
for j in range(10):
str = "'%s', " % random.randint(0, 1000)
sql += str
sql += "'%s')" % self.get_random_string(22)
tdSql.execute(sql % (self.ts + i + 2000))
time.sleep(1)
tdSql.query("select count(*) from regular_table_1")
tdSql.checkData(0, 0, 3 * self.num)
tdSql.query("select * from regular_table_1")
tdSql.checkRows(3 * self.num)
tdSql.checkCols(4096)
print(
"==============step2,regular table error col or value=============="
)
tdLog.info('test regular table exceeds row num')
# column > 4096
sql = "create table regular_table_2(ts timestamp, "
for i in range(4095):
sql += "col%d int, " % (i + 1)
sql += "col4096 binary(22))"
tdLog.info(len(sql))
tdSql.error(sql)
# column > 4096
sql = "insert into regular_table_1 values(%d, "
for j in range(4095):
str = "'%s', " % random.randint(0, 1000)
sql += str
sql += "'%s')" % self.get_random_string(22)
tdSql.error(sql)
# insert column < 4096
sql = "insert into regular_table_1 values(%d, "
for j in range(4092):
str = "'%s', " % random.randint(0, 1000)
sql += str
sql += "'%s')" % self.get_random_string(22)
tdSql.error(sql)
# alter column > 4096
sql = "alter table regular_table_1 add column max int; "
tdSql.error(sql)
print(
"==============step3,regular table , mix data type==============")
startTime = time.time()
sql = "create table regular_table_3(ts timestamp, "
for i in range(2000):
sql += "col%d int, " % (i + 1)
for i in range(2000, 4094):
sql += "col%d bigint, " % (i + 1)
sql += "col4095 binary(22))"
tdLog.info(len(sql))
tdSql.execute(sql)
for i in range(self.num):
sql = "insert into regular_table_3 values(%d, "
for j in range(4094):
str = "'%s', " % random.randint(0, 1000)
sql += str
sql += "'%s')" % self.get_random_string(22)
tdSql.execute(sql % (self.ts + i))
time.sleep(1)
tdSql.query("select count(*) from regular_table_3")
tdSql.checkData(0, 0, self.num)
tdSql.query("select * from regular_table_3")
tdSql.checkRows(self.num)
tdSql.checkCols(4096)
endTime = time.time()
print("total time %ds" % (endTime - startTime))
sql = "create table regular_table_4(ts timestamp, "
for i in range(500):
sql += "int_%d int, " % (i + 1)
for i in range(500, 1000):
sql += "smallint_%d smallint, " % (i + 1)
for i in range(1000, 1500):
sql += "tinyint_%d tinyint, " % (i + 1)
for i in range(1500, 2000):
sql += "double_%d double, " % (i + 1)
for i in range(2000, 2500):
sql += "float_%d float, " % (i + 1)
for i in range(2500, 3000):
sql += "bool_%d bool, " % (i + 1)
for i in range(3000, 3500):
sql += "bigint_%d bigint, " % (i + 1)
for i in range(3500, 3800):
sql += "nchar_%d nchar(4), " % (i + 1)
for i in range(3800, 4090):
sql += "binary_%d binary(10), " % (i + 1)
for i in range(4090, 4094):
sql += "timestamp_%d timestamp, " % (i + 1)
sql += "col4095 binary(22))"
tdLog.info(len(sql))
tdSql.execute(sql)
for i in range(self.num):
sql = "insert into regular_table_4 values(%d, "
for j in range(500):
str = "'%s', " % random.randint(-2147483647, 2147483647)
sql += str
for j in range(500, 1000):
str = "'%s', " % random.randint(-32767, 32767)
sql += str
for j in range(1000, 1500):
str = "'%s', " % random.randint(-127, 127)
sql += str
for j in range(1500, 2000):
str = "'%s', " % random.randint(-922337203685477580700,
922337203685477580700)
sql += str
for j in range(2000, 2500):
str = "'%s', " % random.randint(-92233720368547758070,
92233720368547758070)
sql += str
for j in range(2500, 3000):
str = "'%s', " % random.choice(['true', 'false'])
sql += str
for j in range(3000, 3500):
str = "'%s', " % random.randint(-9223372036854775807,
9223372036854775807)
sql += str
for j in range(3500, 3800):
str = "'%s', " % self.get_random_string(4)
sql += str
for j in range(3800, 4090):
str = "'%s', " % self.get_random_string(10)
sql += str
for j in range(4090, 4094):
str = "%s, " % (self.ts + j)
sql += str
sql += "'%s')" % self.get_random_string(22)
tdSql.execute(sql % (self.ts + i))
time.sleep(1)
tdSql.query("select count(*) from regular_table_4")
tdSql.checkData(0, 0, self.num)
tdSql.query("select * from regular_table_4")
tdSql.checkRows(self.num)
tdSql.checkCols(4096)
tdLog.info("end ,now new one")
#insert null value
tdLog.info('test insert null value')
for i in range(self.num):
sql = "insert into regular_table_4 values(%d, "
for j in range(2500):
str = "'%s', " % random.choice([
'NULL', 'NULL', 'NULL', 1, 10, 100, -100, -10, 88, 66,
'NULL', 'NULL', 'NULL'
])
sql += str
for j in range(2500, 3000):
str = "'%s', " % random.choice(['true', 'false'])
sql += str
for j in range(3000, 3500):
str = "'%s', " % random.randint(-9223372036854775807,
9223372036854775807)
sql += str
for j in range(3500, 3800):
str = "'%s', " % self.get_random_string(4)
sql += str
for j in range(3800, 4090):
str = "'%s', " % self.get_random_string(10)
sql += str
for j in range(4090, 4094):
str = "%s, " % (self.ts + j)
sql += str
sql += "'%s')" % self.get_random_string(22)
tdSql.execute(sql % (self.ts + i + 10000))
time.sleep(1)
tdSql.query("select count(*) from regular_table_4")
tdSql.checkData(0, 0, 2 * self.num)
tdSql.query("select * from regular_table_4")
tdSql.checkRows(2 * self.num)
tdSql.checkCols(4096)
#insert in order
tdLog.info('test insert in order')
for i in range(self.num):
sql = "insert into regular_table_4 (ts,int_2,int_22,int_169,smallint_537,smallint_607,tinyint_1030,tinyint_1491,double_1629,double_1808,float_2075,col4095) values(%d, "
for j in range(10):
str = "'%s', " % random.randint(0, 100)
sql += str
sql += "'%s')" % self.get_random_string(22)
tdSql.execute(sql % (self.ts + i + 1000))
time.sleep(1)
tdSql.query("select count(*) from regular_table_4")
tdSql.checkData(0, 0, 3 * self.num)
tdSql.query("select * from regular_table_4")
tdSql.checkRows(3 * self.num)
tdSql.checkCols(4096)
#insert out of order
tdLog.info('test insert out of order')
for i in range(self.num):
sql = "insert into regular_table_4 (ts,int_169,float_2075,int_369,tinyint_1491,tinyint_1030,float_2360,smallint_537,double_1808,double_1608,double_1629,col4095) values(%d, "
for j in range(10):
str = "'%s', " % random.randint(0, 100)
sql += str
sql += "'%s')" % self.get_random_string(22)
tdSql.execute(sql % (self.ts + i + 2000))
time.sleep(1)
tdSql.query("select count(*) from regular_table_4")
tdSql.checkData(0, 0, 4 * self.num)
tdSql.query("select * from regular_table_4")
tdSql.checkRows(4 * self.num)
tdSql.checkCols(4096)
#define TSDB_MAX_BYTES_PER_ROW 49151[old:1024 && 16384]
#ts:8\int:4\smallint:2\bigint:8\bool:1\float:4\tinyint:1\nchar:4*（）+2[offset]\binary:1*（）+2[offset]
tdLog.info('test regular_table max bytes per row 49151')
sql = "create table regular_table_5(ts timestamp, "
for i in range(500):
sql += "int_%d int, " % (i + 1)
for i in range(500, 1000):
sql += "smallint_%d smallint, " % (i + 1)
for i in range(1000, 1500):
sql += "tinyint_%d tinyint, " % (i + 1)
for i in range(1500, 2000):
sql += "double_%d double, " % (i + 1)
for i in range(2000, 2500):
sql += "float_%d float, " % (i + 1)
for i in range(2500, 3000):
sql += "bool_%d bool, " % (i + 1)
for i in range(3000, 3500):
sql += "bigint_%d bigint, " % (i + 1)
for i in range(3500, 3800):
sql += "nchar_%d nchar(20), " % (i + 1)
for i in range(3800, 4090):
sql += "binary_%d binary(34), " % (i + 1)
for i in range(4090, 4094):
sql += "timestamp_%d timestamp, " % (i + 1)
sql += "col4095 binary(69))"
tdSql.execute(sql)
tdSql.query("select * from regular_table_5")
tdSql.checkCols(4096)
# TD-5324
sql = "alter table regular_table_5 modify column col4095 binary(70); "
tdSql.error(sql)
# drop and add
sql = "alter table regular_table_5 drop column col4095; "
tdSql.execute(sql)
sql = "select * from regular_table_5; "
tdSql.query(sql)
tdSql.checkCols(4095)
sql = "alter table regular_table_5 add column col4095 binary(70); "
tdSql.error(sql)
sql = "alter table regular_table_5 add column col4095 binary(69); "
tdSql.execute(sql)
sql = "select * from regular_table_5; "
tdSql.query(sql)
tdSql.checkCols(4096)
#out TSDB_MAX_BYTES_PER_ROW 49151
tdLog.info('test regular_table max bytes per row out 49151')
sql = "create table regular_table_6(ts timestamp, "
for i in range(500):
sql += "int_%d int, " % (i + 1)
for i in range(500, 1000):
sql += "smallint_%d smallint, " % (i + 1)
for i in range(1000, 1500):
sql += "tinyint_%d tinyint, " % (i + 1)
for i in range(1500, 2000):
sql += "double_%d double, " % (i + 1)
for i in range(2000, 2500):
sql += "float_%d float, " % (i + 1)
for i in range(2500, 3000):
sql += "bool_%d bool, " % (i + 1)
for i in range(3000, 3500):
sql += "bigint_%d bigint, " % (i + 1)
for i in range(3500, 3800):
sql += "nchar_%d nchar(20), " % (i + 1)
for i in range(3800, 4090):
sql += "binary_%d binary(34), " % (i + 1)
for i in range(4090, 4094):
sql += "timestamp_%d timestamp, " % (i + 1)
sql += "col4095 binary(70))"
tdLog.info(len(sql))
tdSql.error(sql)
print(
"==============step4, super table , 1 ts + 4090 cols + 4 tags =============="
)
startTime = time.time()
sql = "create stable stable_1(ts timestamp, "
for i in range(4090):
sql += "col%d int, " % (i + 1)
sql += "col4091 binary(22))"
sql += " tags (loc nchar(10),tag_1 int,tag_2 int,tag_3 int) "
tdLog.info(len(sql))
tdSql.execute(sql)
sql = '''create table table_0 using stable_1
tags('table_0' , '1' , '2' , '3' );'''
tdSql.execute(sql)
for i in range(self.num):
sql = "insert into table_0 values(%d, "
for j in range(4090):
str = "'%s', " % random.randint(0, 1000)
sql += str
sql += "'%s')" % self.get_random_string(22)
tdSql.execute(sql % (self.ts + i))
time.sleep(1)
tdSql.query("select count(*) from table_0")
tdSql.checkData(0, 0, self.num)
tdSql.query("select * from table_0")
tdSql.checkRows(self.num)
tdSql.checkCols(4092)
sql = '''create table table_1 using stable_1
tags('table_1' , '1' , '2' , '3' );'''
tdSql.execute(sql)
for i in range(self.num):
sql = "insert into table_1 values(%d, "
for j in range(2080):
sql += "'%d', " % random.randint(0, 1000)
for j in range(2080, 4080):
sql += "'%s', " % 'NULL'
for j in range(4080, 4090):
sql += "'%s', " % random.randint(0, 10000)
sql += "'%s')" % self.get_random_string(22)
tdSql.execute(sql % (self.ts + i))
time.sleep(1)
tdSql.query("select count(*) from table_1")
tdSql.checkData(0, 0, self.num)
tdSql.query("select * from table_1")
tdSql.checkRows(self.num)
tdSql.checkCols(4092)
endTime = time.time()
print("total time %ds" % (endTime - startTime))
#insert in order
tdLog.info('test insert in order')
for i in range(self.num):
sql = "insert into table_1 (ts,col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col4091) values(%d, "
for j in range(10):
str = "'%s', " % random.randint(0, 1000)
sql += str
sql += "'%s')" % self.get_random_string(22)
tdSql.execute(sql % (self.ts + i + 1000))
time.sleep(1)
tdSql.query("select count(*) from table_1")
tdSql.checkData(0, 0, 2 * self.num)
tdSql.query("select * from table_1")
tdSql.checkRows(2 * self.num)
tdSql.checkCols(4092)
#insert out of order
tdLog.info('test insert out of order')
for i in range(self.num):
sql = "insert into table_1 (ts,col123,col2213,col331,col41,col523,col236,col71,col813,col912,col1320,col4091) values(%d, "
for j in range(10):
str = "'%s', " % random.randint(0, 1000)
sql += str
sql += "'%s')" % self.get_random_string(22)
tdSql.execute(sql % (self.ts + i + 2000))
time.sleep(1)
tdSql.query("select count(*) from table_1")
tdSql.checkData(0, 0, 3 * self.num)
tdSql.query("select * from table_1")
tdSql.checkRows(3 * self.num)
tdSql.checkCols(4092)
print("==============step5,stable table , mix data type==============")
sql = "create stable stable_3(ts timestamp, "
for i in range(500):
sql += "int_%d int, " % (i + 1)
for i in range(500, 1000):
sql += "smallint_%d smallint, " % (i + 1)
for i in range(1000, 1500):
sql += "tinyint_%d tinyint, " % (i + 1)
for i in range(1500, 2000):
sql += "double_%d double, " % (i + 1)
for i in range(2000, 2500):
sql += "float_%d float, " % (i + 1)
for i in range(2500, 3000):
sql += "bool_%d bool, " % (i + 1)
for i in range(3000, 3500):
sql += "bigint_%d bigint, " % (i + 1)
for i in range(3500, 3800):
sql += "nchar_%d nchar(4), " % (i + 1)
for i in range(3800, 4090):
sql += "binary_%d binary(10), " % (i + 1)
sql += "col4091 binary(22))"
sql += " tags (loc nchar(10),tag_1 int,tag_2 int,tag_3 int) "
tdLog.info(len(sql))
tdSql.execute(sql)
sql = '''create table table_30 using stable_3
tags('table_30' , '1' , '2' , '3' );'''
tdSql.execute(sql)
for i in range(self.num):
sql = "insert into table_30 values(%d, "
for j in range(500):
str = "'%s', " % random.randint(-2147483647, 2147483647)
sql += str
for j in range(500, 1000):
str = "'%s', " % random.randint(-32767, 32767)
sql += str
for j in range(1000, 1500):
str = "'%s', " % random.randint(-127, 127)
sql += str
for j in range(1500, 2000):
str = "'%s', " % random.randint(-922337203685477580700,
922337203685477580700)
sql += str
for j in range(2000, 2500):
str = "'%s', " % random.randint(-92233720368547758070,
92233720368547758070)
sql += str
for j in range(2500, 3000):
str = "'%s', " % random.choice(['true', 'false'])
sql += str
for j in range(3000, 3500):
str = "'%s', " % random.randint(-9223372036854775807,
9223372036854775807)
sql += str
for j in range(3500, 3800):
str = "'%s', " % self.get_random_string(4)
sql += str
for j in range(3800, 4090):
str = "'%s', " % self.get_random_string(10)
sql += str
sql += "'%s')" % self.get_random_string(22)
tdSql.execute(sql % (self.ts + i))
time.sleep(1)
tdSql.query("select count(*) from table_30")
tdSql.checkData(0, 0, self.num)
tdSql.query("select * from table_30")
tdSql.checkRows(self.num)
tdSql.checkCols(4092)
#insert null value
tdLog.info('test insert null value')
sql = '''create table table_31 using stable_3
tags('table_31' , '1' , '2' , '3' );'''
tdSql.execute(sql)
for i in range(self.num):
sql = "insert into table_31 values(%d, "
for j in range(2500):
str = "'%s', " % random.choice([
'NULL', 'NULL', 'NULL', 1, 10, 100, -100, -10, 88, 66,
'NULL', 'NULL', 'NULL'
])
sql += str
for j in range(2500, 3000):
str = "'%s', " % random.choice(['true', 'false'])
sql += str
for j in range(3000, 3500):
str = "'%s', " % random.randint(-9223372036854775807,
9223372036854775807)
sql += str
for j in range(3500, 3800):
str = "'%s', " % self.get_random_string(4)
sql += str
for j in range(3800, 4090):
str = "'%s', " % self.get_random_string(10)
sql += str
sql += "'%s')" % self.get_random_string(22)
tdSql.execute(sql % (self.ts + i))
time.sleep(1)
tdSql.query("select count(*) from table_31")
tdSql.checkData(0, 0, self.num)
tdSql.query("select * from table_31")
tdSql.checkRows(self.num)
tdSql.checkCols(4092)
#insert in order
tdLog.info('test insert in order')
for i in range(self.num):
sql = "insert into table_31 (ts,int_2,int_22,int_169,smallint_537,smallint_607,tinyint_1030,tinyint_1491,double_1629,double_1808,float_2075,col4091) values(%d, "
for j in range(10):
str = "'%s', " % random.randint(0, 100)
sql += str
sql += "'%s')" % self.get_random_string(22)
tdSql.execute(sql % (self.ts + i + 1000))
time.sleep(1)
tdSql.query("select count(*) from table_31")
tdSql.checkData(0, 0, 2 * self.num)
tdSql.query("select * from table_31")
tdSql.checkRows(2 * self.num)
tdSql.checkCols(4092)
#insert out of order
tdLog.info('test insert out of order')
for i in range(self.num):
sql = "insert into table_31 (ts,int_169,float_2075,int_369,tinyint_1491,tinyint_1030,float_2360,smallint_537,double_1808,double_1608,double_1629,col4091) values(%d, "
for j in range(10):
str = "'%s', " % random.randint(0, 100)
sql += str
sql += "'%s')" % self.get_random_string(22)
tdSql.execute(sql % (self.ts + i + 2000))
time.sleep(1)
tdSql.query("select count(*) from table_31")
tdSql.checkData(0, 0, 3 * self.num)
tdSql.query("select * from table_31")
tdSql.checkRows(3 * self.num)
tdSql.checkCols(4092)
#define TSDB_MAX_BYTES_PER_ROW 49151 TSDB_MAX_TAGS_LEN 16384
#ts:8\int:4\smallint:2\bigint:8\bool:1\float:4\tinyint:1\nchar:4*（）+2[offset]\binary:1*（）+2[offset]
tdLog.info('test super table max bytes per row 49151')
sql = "create table stable_4(ts timestamp, "
for i in range(500):
sql += "int_%d int, " % (i + 1)
for i in range(500, 1000):
sql += "smallint_%d smallint, " % (i + 1)
for i in range(1000, 1500):
sql += "tinyint_%d tinyint, " % (i + 1)
for i in range(1500, 2000):
sql += "double_%d double, " % (i + 1)
for i in range(2000, 2500):
sql += "float_%d float, " % (i + 1)
for i in range(2500, 3000):
sql += "bool_%d bool, " % (i + 1)
for i in range(3000, 3500):
sql += "bigint_%d bigint, " % (i + 1)
for i in range(3500, 3800):
sql += "nchar_%d nchar(20), " % (i + 1)
for i in range(3800, 4090):
sql += "binary_%d binary(34), " % (i + 1)
sql += "col4091 binary(101))"
sql += " tags (loc nchar(10),tag_1 int,tag_2 int,tag_3 int) "
tdSql.execute(sql)
sql = '''create table table_40 using stable_4
tags('table_40' , '1' , '2' , '3' );'''
tdSql.execute(sql)
tdSql.query("select * from table_40")
tdSql.checkCols(4092)
tdSql.query("describe table_40")
tdSql.checkRows(4096)
tdLog.info('test super table drop and add column or tag')
sql = "alter stable stable_4 drop column col4091; "
tdSql.execute(sql)
sql = "select * from stable_4; "
tdSql.query(sql)
tdSql.checkCols(4095)
sql = "alter table stable_4 add column col4091 binary(102); "
tdSql.error(sql)
sql = "alter table stable_4 add column col4091 binary(101); "
tdSql.execute(sql)
sql = "select * from stable_4; "
tdSql.query(sql)
tdSql.checkCols(4096)
sql = "alter stable stable_4 drop tag tag_1; "
tdSql.execute(sql)
sql = "select * from stable_4; "
tdSql.query(sql)
tdSql.checkCols(4095)
sql = "alter table stable_4 add tag tag_1 int; "
tdSql.execute(sql)
sql = "select * from stable_4; "
tdSql.query(sql)
tdSql.checkCols(4096)
sql = "alter table stable_4 add tag loc1 nchar(10); "
tdSql.error(sql)
tdLog.info('test super table max bytes per row 49151')
sql = "create table stable_5(ts timestamp, "
for i in range(500):
sql += "int_%d int, " % (i + 1)
for i in range(500, 1000):
sql += "smallint_%d smallint, " % (i + 1)
for i in range(1000, 1500):
sql += "tinyint_%d tinyint, " % (i + 1)
for i in range(1500, 2000):
sql += "double_%d double, " % (i + 1)
for i in range(2000, 2500):
sql += "float_%d float, " % (i + 1)
for i in range(2500, 3000):
sql += "bool_%d bool, " % (i + 1)
for i in range(3000, 3500):
sql += "bigint_%d bigint, " % (i + 1)
for i in range(3500, 3800):
sql += "nchar_%d nchar(20), " % (i + 1)
for i in range(3800, 4090):
sql += "binary_%d binary(34), " % (i + 1)
sql += "col4091 binary(102))"
sql += " tags (loc nchar(10),tag_1 int,tag_2 int,tag_3 int) "
tdSql.error(sql)
print("==============step6, super table error col ==============")
tdLog.info('test exceeds row num')
# column + tag > 4096
sql = "create stable stable_2(ts timestamp, "
for i in range(4091):
sql += "col%d int, " % (i + 1)
sql += "col4092 binary(22))"
sql += " tags (loc nchar(10),tag_1 int,tag_2 int,tag_3 int) "
tdLog.info(len(sql))
tdSql.error(sql)
# column + tag > 4096
sql = "create stable stable_2(ts timestamp, "
for i in range(4090):
sql += "col%d int, " % (i + 1)
sql += "col4091 binary(22))"
sql += " tags (loc nchar(10),tag_1 int,tag_2 int,tag_3 int,tag_4 int) "
tdLog.info(len(sql))
tdSql.error(sql)
# alter column + tag > 4096
sql = "alter table stable_1 add column max int; "
tdSql.error(sql)
# TD-5322
sql = "alter table stable_1 add tag max int; "
tdSql.error(sql)
# TD-5324
sql = "alter table stable_4 modify column col4091 binary(102); "
tdSql.error(sql)
sql = "alter table stable_4 modify tag loc nchar(20); "
tdSql.query("select * from table_40")
tdSql.checkCols(4092)
tdSql.query("describe table_40")
tdSql.checkRows(4096)
os.system(
"rm -rf tools/taosdemoAllTest/TD-5213/insert4096columns_not_use_taosdemo.py.sql"
)
def run(self):
tdSql.prepare()
# test case for https://jira.taosdata.com:18080/browse/TD-5206
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 );''')
tdSql.execute('''create stable stable_2
(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 );''')
tdSql.execute('''create table table_0 using stable_1
tags('table_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' )''')
tdSql.execute('''create table table_2 using stable_1
tags('table_2' , '-2147483647' , '-9223372036854775807' , '-32767' , '-127' , false ,
'binary2' , 'nchar2nchar2' , '-2.2' , '-22.22')''')
tdSql.execute('''create table table_3 using stable_1
tags('table_3' , '3' , '3' , '3' , '3' , true , 'binary3' , 'nchar3' , '33.33' , '3333.3333' )''')
tdSql.execute('''create table table_4 using stable_1
tags('table_4' , '4' , '4' , '4' , '4' , false , 'binary4' , 'nchar4' , '-444.444' , '-444444.444444' )''')
tdSql.execute('''create table table_5 using stable_1
tags('table_5' , '5' , '5' , '5' , '5' , true , 'binary5' , 'nchar5' , '5555.5555' , '55555555.55555555' )''')
tdSql.execute('''create table table_21 using stable_2
tags('table_5' , '5' , '5' , '5' , '5' , true , 'binary5' , 'nchar5' , '5555.5555' , '55555555.55555555' )''')
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 table_21 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))
tdLog.info("==========TEST1:test all table data==========")
sql = '''select * from stable_1,stable_2 where stable_1.t_nchar = stable_2.t_nchar and stable_1.ts = stable_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
sql = '''select * from stable_1,stable_2 where stable_1.t_tinyint = stable_2.t_tinyint and stable_1.ts = stable_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
sql = '''select * from stable_1,stable_2 where stable_1.t_binary = stable_2.t_binary and stable_1.ts = stable_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
sql = '''select * from stable_1,stable_2 where stable_1.t_double = stable_2.t_double and stable_1.ts = stable_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
sql = '''select * from stable_1,stable_2 where stable_1.t_smallint = stable_2.t_smallint and stable_1.ts = stable_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
sql = '''select * from stable_1,stable_2 where stable_1.t_bigint = stable_2.t_bigint and stable_1.ts = stable_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
sql = '''select * from stable_1,stable_2 where stable_1.t_int = stable_2.t_int and stable_1.ts = stable_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
sql = '''select * from stable_1,stable_2 where stable_1.t_float = stable_2.t_float and stable_1.ts = stable_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
sql = '''select * from stable_1,stable_2 where stable_1.t_bool = stable_2.t_bool and stable_1.ts = stable_2.ts;'''
tdSql.error(sql)
tdLog.info("==========TEST1:test drop table_0 data==========")
sql = '''drop table table_0;'''
tdSql.execute(sql)
sql = '''select * from stable_1,stable_2 where stable_1.t_nchar = stable_2.t_nchar and stable_1.ts = stable_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
sql = '''select * from stable_1,stable_2 where stable_1.t_tinyint = stable_2.t_tinyint and stable_1.ts = stable_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
sql = '''select * from stable_1,stable_2 where stable_1.t_binary = stable_2.t_binary and stable_1.ts = stable_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
sql = '''select * from stable_1,stable_2 where stable_1.t_double = stable_2.t_double and stable_1.ts = stable_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
sql = '''select * from stable_1,stable_2 where stable_1.t_smallint = stable_2.t_smallint and stable_1.ts = stable_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
sql = '''select * from stable_1,stable_2 where stable_1.t_bigint = stable_2.t_bigint and stable_1.ts = stable_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
sql = '''select * from stable_1,stable_2 where stable_1.t_int = stable_2.t_int and stable_1.ts = stable_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
sql = '''select * from stable_1,stable_2 where stable_1.t_float = stable_2.t_float and stable_1.ts = stable_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
sql = '''select * from stable_1,stable_2 where stable_1.t_bool = stable_2.t_bool and stable_1.ts = stable_2.ts;'''
tdSql.error(sql)
tdLog.info("==========TEST1:test drop table_1 data==========")
sql = '''drop table table_1;'''
tdSql.execute(sql)
sql = '''select * from stable_1,stable_2 where stable_1.t_nchar = stable_2.t_nchar and stable_1.ts = stable_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
sql = '''select * from stable_1,stable_2 where stable_1.t_tinyint = stable_2.t_tinyint and stable_1.ts = stable_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
sql = '''select * from stable_1,stable_2 where stable_1.t_binary = stable_2.t_binary and stable_1.ts = stable_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
sql = '''select * from stable_1,stable_2 where stable_1.t_double = stable_2.t_double and stable_1.ts = stable_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
sql = '''select * from stable_1,stable_2 where stable_1.t_smallint = stable_2.t_smallint and stable_1.ts = stable_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
sql = '''select * from stable_1,stable_2 where stable_1.t_bigint = stable_2.t_bigint and stable_1.ts = stable_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
sql = '''select * from stable_1,stable_2 where stable_1.t_int = stable_2.t_int and stable_1.ts = stable_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
sql = '''select * from stable_1,stable_2 where stable_1.t_float = stable_2.t_float and stable_1.ts = stable_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
sql = '''select * from stable_1,stable_2 where stable_1.t_bool = stable_2.t_bool and stable_1.ts = stable_2.ts;'''
tdSql.error(sql)
tdLog.info("==========TEST1:test drop table_2 data==========")
sql = '''drop table table_2;'''
tdSql.execute(sql)
sql = '''select * from stable_1,stable_2 where stable_1.t_nchar = stable_2.t_nchar and stable_1.ts = stable_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
sql = '''select * from stable_1,stable_2 where stable_1.t_tinyint = stable_2.t_tinyint and stable_1.ts = stable_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
sql = '''select * from stable_1,stable_2 where stable_1.t_binary = stable_2.t_binary and stable_1.ts = stable_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
sql = '''select * from stable_1,stable_2 where stable_1.t_double = stable_2.t_double and stable_1.ts = stable_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
sql = '''select * from stable_1,stable_2 where stable_1.t_smallint = stable_2.t_smallint and stable_1.ts = stable_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
sql = '''select * from stable_1,stable_2 where stable_1.t_bigint = stable_2.t_bigint and stable_1.ts = stable_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
sql = '''select * from stable_1,stable_2 where stable_1.t_int = stable_2.t_int and stable_1.ts = stable_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
sql = '''select * from stable_1,stable_2 where stable_1.t_float = stable_2.t_float and stable_1.ts = stable_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
sql = '''select * from stable_1,stable_2 where stable_1.t_bool = stable_2.t_bool and stable_1.ts = stable_2.ts;'''
tdSql.error(sql)
tdLog.info("==========TEST1:test drop table_3 data==========")
sql = '''drop table table_3;'''
tdSql.execute(sql)
sql = '''select * from stable_1,stable_2 where stable_1.t_nchar = stable_2.t_nchar and stable_1.ts = stable_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
sql = '''select * from stable_1,stable_2 where stable_1.t_tinyint = stable_2.t_tinyint and stable_1.ts = stable_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
sql = '''select * from stable_1,stable_2 where stable_1.t_binary = stable_2.t_binary and stable_1.ts = stable_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
sql = '''select * from stable_1,stable_2 where stable_1.t_double = stable_2.t_double and stable_1.ts = stable_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
sql = '''select * from stable_1,stable_2 where stable_1.t_smallint = stable_2.t_smallint and stable_1.ts = stable_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
sql = '''select * from stable_1,stable_2 where stable_1.t_bigint = stable_2.t_bigint and stable_1.ts = stable_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
sql = '''select * from stable_1,stable_2 where stable_1.t_int = stable_2.t_int and stable_1.ts = stable_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
sql = '''select * from stable_1,stable_2 where stable_1.t_float = stable_2.t_float and stable_1.ts = stable_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
sql = '''select * from stable_1,stable_2 where stable_1.t_bool = stable_2.t_bool and stable_1.ts = stable_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
tdLog.info("==========TEST1:test drop table_4 data==========")
sql = '''drop table table_4;'''
tdSql.execute(sql)
sql = '''select * from stable_1,stable_2 where stable_1.t_nchar = stable_2.t_nchar and stable_1.ts = stable_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
sql = '''select * from stable_1,stable_2 where stable_1.t_tinyint = stable_2.t_tinyint and stable_1.ts = stable_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
sql = '''select * from stable_1,stable_2 where stable_1.t_binary = stable_2.t_binary and stable_1.ts = stable_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
sql = '''select * from stable_1,stable_2 where stable_1.t_double = stable_2.t_double and stable_1.ts = stable_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
sql = '''select * from stable_1,stable_2 where stable_1.t_smallint = stable_2.t_smallint and stable_1.ts = stable_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
sql = '''select * from stable_1,stable_2 where stable_1.t_bigint = stable_2.t_bigint and stable_1.ts = stable_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
sql = '''select * from stable_1,stable_2 where stable_1.t_int = stable_2.t_int and stable_1.ts = stable_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
sql = '''select * from stable_1,stable_2 where stable_1.t_float = stable_2.t_float and stable_1.ts = stable_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
sql = '''select * from stable_1,stable_2 where stable_1.t_bool = stable_2.t_bool and stable_1.ts = stable_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
tdLog.info("==========TEST1:test drop table_5 data==========")
sql = '''drop table table_5;'''
tdSql.execute(sql)
sql = '''select * from stable_1,stable_2 where stable_1.t_nchar = stable_2.t_nchar and stable_1.ts = stable_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(0)
sql = '''select * from stable_1,stable_2 where stable_1.t_tinyint = stable_2.t_tinyint and stable_1.ts = stable_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(0)
sql = '''select * from stable_1,stable_2 where stable_1.t_binary = stable_2.t_binary and stable_1.ts = stable_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(0)
sql = '''select * from stable_1,stable_2 where stable_1.t_double = stable_2.t_double and stable_1.ts = stable_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(0)
sql = '''select * from stable_1,stable_2 where stable_1.t_smallint = stable_2.t_smallint and stable_1.ts = stable_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(0)
sql = '''select * from stable_1,stable_2 where stable_1.t_bigint = stable_2.t_bigint and stable_1.ts = stable_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(0)
sql = '''select * from stable_1,stable_2 where stable_1.t_int = stable_2.t_int and stable_1.ts = stable_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(0)
sql = '''select * from stable_1,stable_2 where stable_1.t_float = stable_2.t_float and stable_1.ts = stable_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(0)
sql = '''select * from stable_1,stable_2 where stable_1.t_bool = stable_2.t_bool and stable_1.ts = stable_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(0)
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 dev='dev_01'")
tdSql.checkRows(2)
tdSql.query("select * from db.st where dev='dev_02'")
tdSql.checkRows(1)
#For: https://jira.taosdata.com:18080/browse/TD-2671
print("==============step3")
tdSql.execute(
"create stable if not exists stb (ts timestamp, tagtype int) tags(dev nchar(50))"
)
tdSql.execute(
'CREATE TABLE if not exists dev_01 using stb tags("dev_01")')
tdSql.execute(
'CREATE TABLE if not exists dev_02 using stb tags("dev_02")')
print("==============step4")
tdSql.execute(
"""INSERT INTO dev_01(ts, tagtype) VALUES('2020-05-13 10:00:00.000', 1),
('2020-05-13 10:00:00.001', 1)
dev_02 VALUES('2020-05-13 10:00:00.001', 1)""")
tdSql.query("select * from db.stb where dev='dev_01'")
tdSql.checkRows(2)
tdSql.query("select * from db.stb where dev='dev_02'")
tdSql.checkRows(1)
tdSql.query("describe db.stb")
tdSql.checkRows(3)
tdSql.error("drop stable if exists db.dev_01")
tdSql.error("drop stable if exists db.dev_02")
tdSql.execute("alter stable db.stb add tag t1 int")
tdSql.query("describe db.stb")
tdSql.checkRows(4)
tdSql.execute("drop stable db.stb")
tdSql.query("show stables")
tdSql.checkRows(1)
tdSql.error("drop stable if exists db.dev_001")
tdSql.error("drop stable if exists db.dev_002")
for i in range(10):
tdSql.execute("drop stable if exists db.stb")
tdSql.query("show stables")
tdSql.checkRows(1)
def run(self):
tdSql.prepare()
# test case for https://jira.taosdata.com:18080/browse/TD-5074
startTime = time.time()
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 stable stable_2
(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')'''
)
tdSql.execute('''create table table_21 using stable_2
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 table_21 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))
tdLog.info("========== operator=1(OP_TableScan) ==========")
tdLog.info("========== operator=7(OP_Project) ==========")
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(
"========== operator=14(OP_MultiTableAggregate ) ==========")
sql = '''select last_row(*) from stable_1;'''
tdSql.query(sql)
tdSql.checkData(0, 1, self.num - 1)
tdLog.info("========== operator=6(OP_Aggregate) ==========")
sql = '''select last_row(*) from regular_table_1;'''
tdSql.query(sql)
tdSql.checkData(0, 1, self.num - 1)
tdLog.info("========== operator=9(OP_Limit) ==========")
sql = '''select * from stable_1 where loc = 'table_0' limit 5;'''
tdSql.query(sql)
tdSql.checkRows(5)
sql = '''select last_row(*) from (select * from stable_1 where loc = 'table_0');'''
tdSql.query(sql)
tdSql.checkRows(1)
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)
sql = '''select last_row(*) from
((select * from table_0) union all
(select * from table_1) union all
(select * from table_2));'''
tdSql.error(sql)
tdLog.info("========== operator=16(OP_DummyInput) ==========")
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)
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)
tdLog.info("========== operator=10(OP_SLimit) ==========")
sql = '''select count(*) from stable_1 group by loc slimit 3 soffset 2 ;'''
tdSql.query(sql)
tdSql.checkRows(3)
sql = '''select last_row(*) from
((select * from table_0) union all
(select * from table_1) union all
(select * from table_2));'''
tdSql.error(sql)
tdLog.info("========== operator=20(OP_Distinct) ==========")
tdLog.info("========== operator=4(OP_TagScan) ==========")
sql = '''select distinct(t_bool) from stable_1;'''
tdSql.query(sql)
tdSql.checkRows(2)
sql = '''select distinct(loc) from stable_1;'''
tdSql.query(sql)
tdSql.checkRows(6)
sql = '''select distinct(t_int) from stable_1;'''
tdSql.query(sql)
tdSql.checkRows(6)
sql = '''select distinct(t_bigint) from stable_1;'''
tdSql.query(sql)
tdSql.checkRows(6)
sql = '''select distinct(t_smallint) from stable_1;'''
tdSql.query(sql)
tdSql.checkRows(6)
sql = '''select distinct(t_tinyint) from stable_1;'''
tdSql.query(sql)
tdSql.checkRows(6)
sql = '''select distinct(t_nchar) from stable_1;'''
tdSql.query(sql)
tdSql.checkRows(6)
sql = '''select distinct(t_float) from stable_1;'''
tdSql.query(sql)
tdSql.checkRows(6)
sql = '''select distinct(t_double) from stable_1;'''
tdSql.query(sql)
tdSql.checkRows(6)
sql = '''select distinct(t_ts) from stable_1;'''
tdSql.query(sql)
tdSql.checkRows(3)
# sql = '''select distinct(tbname) from stable_1;'''
# tdSql.query(sql)
# tdSql.checkRows(6)
tdLog.info("========== operator=2(OP_DataBlocksOptScan) ==========")
sql = '''select last(q_int),first(q_int) from stable_1;'''
tdSql.query(sql)
tdSql.checkRows(1)
sql = '''select last(q_bigint),first(q_bigint) from stable_1;'''
tdSql.query(sql)
tdSql.checkRows(1)
sql = '''select last(q_smallint),first(q_smallint) from stable_1;'''
tdSql.query(sql)
tdSql.checkRows(1)
sql = '''select last(q_tinyint),first(q_tinyint) from stable_1;'''
tdSql.query(sql)
tdSql.checkRows(1)
sql = '''select last(q_bool),first(q_bool) from stable_1;'''
tdSql.query(sql)
tdSql.checkRows(1)
sql = '''select last(q_binary),first(q_binary) from stable_1;'''
tdSql.query(sql)
tdSql.checkRows(1)
sql = '''select last(q_nchar),first(q_nchar) from stable_1;'''
tdSql.query(sql)
tdSql.checkRows(1)
sql = '''select last(q_float),first(q_float) from stable_1;'''
tdSql.query(sql)
tdSql.checkRows(1)
sql = '''select last(q_double),first(q_double) from stable_1;'''
tdSql.query(sql)
tdSql.checkRows(1)
sql = '''select last(q_ts),first(q_ts) from stable_1;'''
tdSql.query(sql)
tdSql.checkRows(1)
sql = '''select last(q_int),last(q_bigint), last(q_smallint),last(q_tinyint),last(q_bool),last(q_binary),last(q_nchar),
last(q_float),last(q_double),last(q_ts),first(q_int),first(q_bigint),first(q_smallint),first(q_tinyint),
first(q_bool),first(q_binary),first(q_nchar),first(q_float),first(q_float),first(q_double),first(q_ts) from stable_1;'''
tdSql.query(sql)
tdSql.checkRows(1)
sql = '''select last(q_int),last(q_bigint), last(q_smallint),last(q_tinyint),last(q_bool),last(q_binary),last(q_nchar),
last(q_float),last(q_double),last(q_ts),first(q_int),first(q_bigint),first(q_smallint),first(q_tinyint),first(q_bool),
first(q_binary),first(q_nchar),first(q_float),first(q_float),first(q_double),first(q_ts) from regular_table_1;'''
tdSql.query(sql)
tdSql.checkRows(1)
tdLog.info("========== operator=8(OP_Groupby) ==========")
sql = '''select stddev(q_int) from table_0 group by q_int;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
sql = '''select stddev(q_int),stddev(q_bigint),stddev(q_smallint),stddev(q_tinyint),stddev(q_float),stddev(q_double) from stable_1 group by q_int;'''
tdSql.query(sql)
sql = '''select stddev(q_int),stddev(q_bigint),stddev(q_smallint),stddev(q_tinyint),stddev(q_float),stddev(q_double) from table_1 group by q_bigint;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
sql = '''select stddev(q_int),stddev(q_bigint),stddev(q_smallint),stddev(q_tinyint),stddev(q_float),stddev(q_double) from regular_table_1 group by q_smallint;'''
tdSql.query(sql)
tdLog.info("========== operator=11(OP_TimeWindow) ==========")
sql = '''select last(q_int) from table_0 interval(1m);'''
tdSql.query(sql)
tdSql.checkRows(1)
sql = '''select last(q_int),last(q_bigint), last(q_smallint),last(q_tinyint),
first(q_int),first(q_bigint),first(q_smallint),first(q_tinyint) from table_1 interval(1m);'''
tdSql.query(sql)
tdSql.checkRows(1)
sql = '''select last(q_int),last(q_bigint), last(q_smallint),last(q_tinyint),
first(q_int),first(q_bigint),first(q_smallint),first(q_tinyint) from stable_1 interval(1m);'''
tdSql.query(sql)
tdSql.checkRows(1)
sql = '''select last(q_int),last(q_bigint), last(q_smallint),last(q_tinyint),
first(q_int),first(q_bigint),first(q_smallint),first(q_tinyint) from regular_table_1 interval(1m);'''
tdSql.query(sql)
tdSql.checkRows(1)
tdLog.info("========== operator=12(OP_SessionWindow) ==========")
sql = '''select count(*) from table_1 session(ts,1s);'''
tdSql.query(sql)
tdSql.checkRows(1)
sql = '''select count(*) from regular_table_1 session(ts,1s);'''
tdSql.query(sql)
tdSql.checkRows(1)
sql = '''select count(*),sum(q_int), avg(q_int), min(q_int), max(q_int), first(q_int), last(q_int),
sum(q_bigint), avg(q_bigint), min(q_bigint), max(q_bigint), first(q_bigint), last(q_bigint),
sum(q_smallint), avg(q_smallint), min(q_smallint), max(q_smallint), first(q_smallint), last(q_smallint),
sum(q_tinyint), avg(q_tinyint), min(q_tinyint), max(q_tinyint), first(q_tinyint), last(q_tinyint),
sum(q_float), avg(q_float), min(q_float), max(q_float), first(q_float), last(q_float),
sum(q_double), avg(q_double), min(q_double), max(q_double), first(q_double), last(q_double)
from table_1 session(ts,1s);'''
tdSql.query(sql)
tdSql.checkRows(1)
sql = '''select count(*),sum(q_int), avg(q_int), min(q_int), max(q_int), first(q_int), last(q_int),
sum(q_bigint), avg(q_bigint), min(q_bigint), max(q_bigint), first(q_bigint), last(q_bigint),
sum(q_smallint), avg(q_smallint), min(q_smallint), max(q_smallint), first(q_smallint), last(q_smallint),
sum(q_tinyint), avg(q_tinyint), min(q_tinyint), max(q_tinyint), first(q_tinyint), last(q_tinyint),
sum(q_float), avg(q_float), min(q_float), max(q_float), first(q_float), last(q_float),
sum(q_double), avg(q_double), min(q_double), max(q_double), first(q_double), last(q_double)
from regular_table_1 session(ts,1s);'''
tdSql.query(sql)
tdSql.checkRows(1)
tdLog.info("========== operator=13(OP_Fill) ==========")
sql = '''select sum(q_int) from table_0
where ts >='1970-10-01 00:00:00' and ts <=now interval(1n) fill(NULL);'''
tdSql.query(sql)
tdSql.checkData(0, 1, 'None')
sql = '''select sum(q_int), avg(q_int), min(q_int), max(q_int), first(q_int), last(q_int),
sum(q_bigint), avg(q_bigint), min(q_bigint), max(q_bigint), first(q_bigint), last(q_bigint),
sum(q_smallint), avg(q_smallint), min(q_smallint), max(q_smallint), first(q_smallint), last(q_smallint),
sum(q_tinyint), avg(q_tinyint), min(q_tinyint), max(q_tinyint), first(q_tinyint), last(q_tinyint),
sum(q_float), avg(q_float), min(q_float), max(q_float), first(q_float), last(q_float),
sum(q_double), avg(q_double), min(q_double), max(q_double), first(q_double), last(q_double)
from stable_1 where ts >='1970-10-01 00:00:00' and ts <=now interval(1n) fill(NULL);'''
tdSql.query(sql)
tdSql.checkData(0, 1, 'None')
sql = '''select sum(q_int), avg(q_int), min(q_int), max(q_int), first(q_int), last(q_int),
sum(q_bigint), avg(q_bigint), min(q_bigint), max(q_bigint), first(q_bigint), last(q_bigint),
sum(q_smallint), avg(q_smallint), min(q_smallint), max(q_smallint), first(q_smallint), last(q_smallint),
sum(q_tinyint), avg(q_tinyint), min(q_tinyint), max(q_tinyint), first(q_tinyint), last(q_tinyint),
sum(q_float), avg(q_float), min(q_float), max(q_float), first(q_float), last(q_float),
sum(q_double), avg(q_double), min(q_double), max(q_double), first(q_double), last(q_double)
from regular_table_1 where ts >='1970-10-01 00:00:00' and ts <=now interval(1n) fill(NULL);'''
tdSql.query(sql)
tdSql.checkData(0, 1, 'None')
sql = '''select sum(q_int), avg(q_int), min(q_int), max(q_int), first(q_int), last(q_int),
sum(q_bigint), avg(q_bigint), min(q_bigint), max(q_bigint), first(q_bigint), last(q_bigint),
sum(q_smallint), avg(q_smallint), min(q_smallint), max(q_smallint), first(q_smallint), last(q_smallint),
sum(q_tinyint), avg(q_tinyint), min(q_tinyint), max(q_tinyint), first(q_tinyint), last(q_tinyint),
sum(q_float), avg(q_float), min(q_float), max(q_float), first(q_float), last(q_float),
sum(q_double), avg(q_double), min(q_double), max(q_double), first(q_double), last(q_double)
from table_0 where ts >='1970-10-01 00:00:00' and ts <=now interval(1n) fill(NULL);'''
tdSql.query(sql)
tdSql.checkData(0, 1, 'None')
#TD-5190
sql = '''select sum(q_tinyint),stddev(q_float) from stable_1
where ts >='1970-10-01 00:00:00' and ts <=now interval(1n) fill(NULL);'''
tdSql.query(sql)
tdSql.checkData(0, 1, 'None')
tdLog.info(
"========== operator=15(OP_MultiTableTimeInterval) ==========")
sql = '''select avg(q_int) from stable_1 where ts<now interval(10m);'''
tdSql.query(sql)
tdSql.checkRows(1)
sql = '''select sum(q_int), avg(q_int), min(q_int), max(q_int), first(q_int), last(q_int),
sum(q_bigint), avg(q_bigint), min(q_bigint), max(q_bigint), first(q_bigint), last(q_bigint),
sum(q_smallint), avg(q_smallint), min(q_smallint), max(q_smallint), first(q_smallint), last(q_smallint),
sum(q_tinyint), avg(q_tinyint), min(q_tinyint), max(q_tinyint), first(q_tinyint), last(q_tinyint),
sum(q_float), avg(q_float), min(q_float), max(q_float), first(q_float), last(q_float),
sum(q_double), avg(q_double), min(q_double), max(q_double), first(q_double), last(q_double)
from table_1 where ts<now interval(10m);'''
tdSql.query(sql)
tdSql.checkRows(1)
sql = '''select sum(q_int), avg(q_int), min(q_int), max(q_int), first(q_int), last(q_int),
sum(q_bigint), avg(q_bigint), min(q_bigint), max(q_bigint), first(q_bigint), last(q_bigint),
sum(q_smallint), avg(q_smallint), min(q_smallint), max(q_smallint), first(q_smallint), last(q_smallint),
sum(q_tinyint), avg(q_tinyint), min(q_tinyint), max(q_tinyint), first(q_tinyint), last(q_tinyint),
sum(q_float), avg(q_float), min(q_float), max(q_float), first(q_float), last(q_float),
sum(q_double), avg(q_double), min(q_double), max(q_double), first(q_double), last(q_double)
from stable_1 where ts<now interval(10m);'''
tdSql.query(sql)
tdSql.checkRows(1)
sql = '''select sum(q_int), avg(q_int), min(q_int), max(q_int), first(q_int), last(q_int),
sum(q_bigint), avg(q_bigint), min(q_bigint), max(q_bigint), first(q_bigint), last(q_bigint),
sum(q_smallint), avg(q_smallint), min(q_smallint), max(q_smallint), first(q_smallint), last(q_smallint),
sum(q_tinyint), avg(q_tinyint), min(q_tinyint), max(q_tinyint), first(q_tinyint), last(q_tinyint),
sum(q_float), avg(q_float), min(q_float), max(q_float), first(q_float), last(q_float),
sum(q_double), avg(q_double), min(q_double), max(q_double), first(q_double), last(q_double)
from regular_table_1 where ts<now interval(10m);'''
tdSql.query(sql)
tdSql.checkRows(1)
tdLog.info("========== operator=3(OP_TableSeqScan) ==========")
tdLog.info("========== operator=6(OP_Aggregate) ==========")
sql = '''select * from table_1,table_2
where table_1.ts = table_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
#TD-5206
sql = '''select * from stable_1,stable_2
where stable_1.t_nchar = stable_2.t_nchar and stable_1.ts = stable_2.ts;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
#TD-5139
sql = '''select * from table_1,regular_table_1
where table_1.ts = regular_table_1.ts;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
tdLog.info("========== operator=5(OP_TableBlockInfoScan) ==========")
sql = '''select _block_dist() from stable_1;'''
tdSql.query(sql)
tdSql.checkRows(1)
sql = '''select _block_dist() from table_1;'''
tdSql.query(sql)
tdSql.checkRows(1)
sql = '''select _block_dist() from regular_table_1;'''
tdSql.query(sql)
tdSql.checkRows(1)
tdLog.info("========== operator=17(OP_MultiwayMergeSort) ==========")
tdLog.info("========== operator=18(OP_GlobalAggregate) ==========")
tdLog.info("========== operator=19(OP_Filter) ==========")
sql = '''select loc,sum(q_int) from stable_1
group by loc having sum(q_int)>=0;'''
tdSql.query(sql)
tdSql.checkData(0, 0, 'table_0')
sql = '''select loc, sum(q_int), avg(q_int), min(q_int), max(q_int), first(q_int), last(q_int),
sum(q_bigint), avg(q_bigint), min(q_bigint), max(q_bigint), first(q_bigint), last(q_bigint),
sum(q_smallint), avg(q_smallint), min(q_smallint), max(q_smallint), first(q_smallint), last(q_smallint),
sum(q_tinyint), avg(q_tinyint), min(q_tinyint), max(q_tinyint), first(q_tinyint), last(q_tinyint),
sum(q_float), avg(q_float), min(q_float), max(q_float), first(q_float), last(q_float),
sum(q_double), avg(q_double), min(q_double), max(q_double), first(q_double), last(q_double)
from stable_1 group by loc having sum(q_int)>=0;'''
tdSql.query(sql)
tdSql.checkData(0, 0, 'table_0')
sql = '''select loc, sum(q_int), avg(q_int), min(q_int), max(q_int), first(q_int), last(q_int),
sum(q_bigint), avg(q_bigint), min(q_bigint), max(q_bigint), first(q_bigint), last(q_bigint),
sum(q_smallint), avg(q_smallint), min(q_smallint), max(q_smallint), first(q_smallint), last(q_smallint),
sum(q_tinyint), avg(q_tinyint), min(q_tinyint), max(q_tinyint), first(q_tinyint), last(q_tinyint),
sum(q_float), avg(q_float), min(q_float), max(q_float), first(q_float), last(q_float),
sum(q_double), avg(q_double), min(q_double), max(q_double), first(q_double), last(q_double)
from stable_1 group by loc having avg(q_int)>=0;'''
tdSql.query(sql)
tdSql.checkData(0, 0, 'table_0')
sql = '''select loc, sum(q_int), avg(q_int), min(q_int), max(q_int), first(q_int), last(q_int),
sum(q_bigint), avg(q_bigint), min(q_bigint), max(q_bigint), first(q_bigint), last(q_bigint),
sum(q_smallint), avg(q_smallint), min(q_smallint), max(q_smallint), first(q_smallint), last(q_smallint),
sum(q_tinyint), avg(q_tinyint), min(q_tinyint), max(q_tinyint), first(q_tinyint), last(q_tinyint),
sum(q_float), avg(q_float), min(q_float), max(q_float), first(q_float), last(q_float),
sum(q_double), avg(q_double), min(q_double), max(q_double), first(q_double), last(q_double)
from stable_1 group by loc having min(q_int)>=0;'''
tdSql.query(sql)
tdSql.checkData(0, 0, 'table_0')
sql = '''select loc, sum(q_int), avg(q_int), min(q_int), max(q_int), first(q_int), last(q_int),
sum(q_bigint), avg(q_bigint), min(q_bigint), max(q_bigint), first(q_bigint), last(q_bigint),
sum(q_smallint), avg(q_smallint), min(q_smallint), max(q_smallint), first(q_smallint), last(q_smallint),
sum(q_tinyint), avg(q_tinyint), min(q_tinyint), max(q_tinyint), first(q_tinyint), last(q_tinyint),
sum(q_float), avg(q_float), min(q_float), max(q_float), first(q_float), last(q_float),
sum(q_double), avg(q_double), min(q_double), max(q_double), first(q_double), last(q_double)
from stable_1 group by loc having max(q_int)>=0;'''
tdSql.query(sql)
tdSql.checkData(0, 0, 'table_0')
sql = '''select loc, sum(q_int), avg(q_int), min(q_int), max(q_int), first(q_int), last(q_int),
sum(q_bigint), avg(q_bigint), min(q_bigint), max(q_bigint), first(q_bigint), last(q_bigint),
sum(q_smallint), avg(q_smallint), min(q_smallint), max(q_smallint), first(q_smallint), last(q_smallint),
sum(q_tinyint), avg(q_tinyint), min(q_tinyint), max(q_tinyint), first(q_tinyint), last(q_tinyint),
sum(q_float), avg(q_float), min(q_float), max(q_float), first(q_float), last(q_float),
sum(q_double), avg(q_double), min(q_double), max(q_double), first(q_double), last(q_double)
from stable_1 group by loc having first(q_int)>=0;'''
tdSql.query(sql)
tdSql.checkData(0, 0, 'table_0')
sql = '''select loc, sum(q_int), avg(q_int), min(q_int), max(q_int), first(q_int), last(q_int),
sum(q_bigint), avg(q_bigint), min(q_bigint), max(q_bigint), first(q_bigint), last(q_bigint),
sum(q_smallint), avg(q_smallint), min(q_smallint), max(q_smallint), first(q_smallint), last(q_smallint),
sum(q_tinyint), avg(q_tinyint), min(q_tinyint), max(q_tinyint), first(q_tinyint), last(q_tinyint),
sum(q_float), avg(q_float), min(q_float), max(q_float), first(q_float), last(q_float),
sum(q_double), avg(q_double), min(q_double), max(q_double), first(q_double), last(q_double)
from stable_1 group by loc having last(q_int)>=0;'''
tdSql.query(sql)
tdSql.checkData(0, 0, 'table_0')
tdLog.info("========== operator=21(OP_Join) ==========")
sql = '''select t1.q_int,t2.q_int from
(select ts,q_int from table_1) t1 , (select ts,q_int from table_2) t2
where t2.ts = t1.ts;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
sql = '''select t1.*,t2.* from
(select * from table_1) t1 , (select * from table_2) t2
where t2.ts = t1.ts;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
sql = '''select t1.*,t2.* from
(select * from regular_table_1) t1 , (select * from table_0) t2
where t2.ts = t1.ts;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
sql = '''select t1.*,t2.* from
(select * from stable_1) t1 , (select * from table_2) t2
where t2.ts = t1.ts;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
sql = '''select t1.*,t2.* from
(select * from regular_table_1) t1 , (select * from stable_1) t2
where t2.ts = t1.ts;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
sql = '''select t1.*,t2.*,t3.* from
(select * from regular_table_1) t1 , (select * from stable_1) t2, (select * from table_0) t3
where t2.ts = t1.ts and t3.ts = t1.ts and t2.ts = t3.ts;'''
tdSql.query(sql)
tdSql.checkRows(self.num)
tdLog.info("========== operator=22(OP_StateWindow) ==========")
sql = '''select avg(q_int),sum(q_smallint) from table_1 state_window(q_int);'''
tdSql.query(sql)
tdSql.checkRows(self.num)
sql = '''select sum(q_int), avg(q_int), min(q_int), max(q_int), first(q_int), last(q_int),
sum(q_bigint), avg(q_bigint), min(q_bigint), max(q_bigint), first(q_bigint), last(q_bigint),
sum(q_smallint), avg(q_smallint), min(q_smallint), max(q_smallint), first(q_smallint), last(q_smallint),
sum(q_tinyint), avg(q_tinyint), min(q_tinyint), max(q_tinyint), first(q_tinyint), last(q_tinyint),
sum(q_float), avg(q_float), min(q_float), max(q_float), first(q_float), last(q_float),
sum(q_double), avg(q_double), min(q_double), max(q_double), first(q_double), last(q_double)
from table_1 state_window(q_bigint);'''
tdSql.query(sql)
tdSql.checkRows(self.num)
sql = '''select sum(q_int), avg(q_int), min(q_int), max(q_int), first(q_int), last(q_int),
sum(q_bigint), avg(q_bigint), min(q_bigint), max(q_bigint), first(q_bigint), last(q_bigint),
sum(q_smallint), avg(q_smallint), min(q_smallint), max(q_smallint), first(q_smallint), last(q_smallint),
sum(q_tinyint), avg(q_tinyint), min(q_tinyint), max(q_tinyint), first(q_tinyint), last(q_tinyint),
sum(q_float), avg(q_float), min(q_float), max(q_float), first(q_float), last(q_float),
sum(q_double), avg(q_double), min(q_double), max(q_double), first(q_double), last(q_double)
from regular_table_1 state_window(q_smallint);'''
tdSql.query(sql)
tdSql.checkRows(6 * self.num)
endTime = time.time()
print("total time %ds" % (endTime - startTime))
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)
tdSql.execute(
"create table stb(ts timestamp, options binary(7), city binary(10)) tags(type int)"
)
tdSql.execute(
"insert into tb1 using stb tags(1) values(%d, 'option1', 'beijing')"
% self.ts)
tdSql.execute(
"insert into tb2 using stb tags(2) values(%d, 'option2', 'shanghai')"
% self.ts)
tdSql.query(
"select options from stb where type = 1 limit 1 union all select options from stb where type = 2 limit 1"
)
tdSql.checkData(0, 0, "option1")
tdSql.checkData(1, 0, "option2")
tdSql.query(
"select 'dc' as options from stb where type = 1 limit 1 union all select 'ad' as options from stb where type = 2 limit 1"
)
tdSql.checkData(0, 0, "dc")
tdSql.checkData(1, 0, "ad")
tdSql.query(
"select 'dc' as options from stb where type = 1 limit 1 union all select 'adc' as options from stb where type = 2 limit 1"
)
tdSql.checkData(0, 0, "dc")
tdSql.checkData(1, 0, "adc")
tdSql.error(
"select 'dc' as options from stb where type = 1 limit 1 union all select 'ad' as city from stb where type = 2 limit 1"
)
# for defect https://jira.taosdata.com:18080/browse/TD-4017
tdSql.execute("alter table stb add column col int")
tdSql.execute("insert into tb1 values(%d, 'option1', 'beijing', 10)" %
(self.ts + 1000))
tdSql.query("select 'dc' as options from stb where col > 10 limit 1")
tdSql.checkRows(0)
tdSql.query(
"select 'dcs' as options from stb where col > 200 limit 1 union all select 'aaa' as options from stb limit 10"
)
tdSql.checkData(0, 0, 'aaa')
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()
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)
tdSql.query("select tbname, dev from dev_001")
tdSql.checkRows(1)
tdSql.checkData(0, 0, 'dev_001')
tdSql.checkData(0, 1, 'dev_01')
tdSql.query("select tbname, dev, tagtype from dev_001")
tdSql.checkRows(2)
tdSql.checkData(0, 0, 'dev_001')
tdSql.checkData(0, 1, 'dev_01')
tdSql.checkData(0, 2, 1)
tdSql.checkData(1, 0, 'dev_001')
tdSql.checkData(1, 1, 'dev_01')
tdSql.checkData(1, 2, 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)
## test case for https://jira.taosdata.com:18080/browse/TD-1930
tdSql.execute(
"create table tb(ts timestamp, c1 int, c2 binary(10), c3 nchar(10), c4 float, c5 bool)"
)
for i in range(10):
tdSql.execute(
"insert into tb values(%d, %d, 'binary%d', 'nchar%d', %f, %d)"
% (self.ts + i, i, i, i, i + 0.1, i % 2))
tdSql.error("select * from tb where c2 = binary2")
tdSql.error("select * from tb where c3 = nchar2")
tdSql.query("select * from tb where c2 = 'binary2' ")
tdSql.checkRows(1)
tdSql.query("select * from tb where c3 = 'nchar2' ")
tdSql.checkRows(1)
tdSql.query("select * from tb where c1 = '2' ")
tdSql.checkRows(1)
tdSql.query("select * from tb where c1 = 2 ")
tdSql.checkRows(1)
tdSql.query("select * from tb where c4 = '0.1' ")
tdSql.checkRows(1)
tdSql.query("select * from tb where c4 = 0.1 ")
tdSql.checkRows(1)
tdSql.query("select * from tb where c5 = true ")
tdSql.checkRows(5)
tdSql.query("select * from tb where c5 = 'true' ")
tdSql.checkRows(5)
def run(self):
tdSql.prepare()
# test case for https://jira.taosdata.com:18080/browse/TD-5021
tdLog.info("\n\n----------step1 : drop db and create db----------\n")
tdSql.execute('''drop database if exists db ;''')
tdSql.execute('''create database db ;''')
sql = '''show databases;'''
tdSql.query(sql)
tdSql.checkRows(1)
tdLog.info("\n\n----------step2 : create stable----------\n")
tdSql.execute('''create stable
db.stable_1 (ts timestamp, payload binary(256))
tags(t1 binary(16),t2 int);''')
sql = '''show db.stables;'''
tdSql.query(sql)
tdSql.checkRows(1)
tdLog.info("\n\n----------step3 : create table and insert----------\n")
sql = '''insert into db.table1 using db.stable_1 (t1 , t2) tags ("table_1" , 111) ( values (now, ;'''
tdLog.info(sql)
tdSql.error(sql)
try:
tdSql.execute(sql)
tdLog.exit(" unexpected token")
except Exception as e:
tdLog.info(repr(e))
tdLog.info("DB error: syntax error near ', ;' (unexpected token)")
sql = '''insert into db.table1(ts , payload) using db.stable_1 (t1 , t2) tags ("table_1" , 111) ( values (now, ;'''
tdLog.info(sql)
tdSql.error(sql)
try:
tdSql.execute(sql)
tdLog.exit(" bind columns again")
except Exception as e:
tdLog.info(repr(e))
tdLog.info(
"DB error: syntax error near ', ;' (bind columns again)")
sql = '''insert into db.table1 using db.stable_1 (t1 , t2) tags ("table_1",111) (ts , payload) ( values (now, ;'''
tdLog.info(sql)
tdSql.error(sql)
try:
tdSql.execute(sql)
tdLog.exit(" keyword VALUES or FILE required ")
except Exception as e:
tdLog.info(repr(e))
tdLog.info(
"DB error: invalid SQL: (keyword VALUES or FILE required)")
tdSql.execute('''insert into db.table1 using db.stable_1 (t1 , t2)
tags ("table_1" , 111) values ( now , 1) ''')
sql = '''select * from db.stable_1;'''
tdSql.query(sql)
tdSql.checkRows(1)
tdSql.checkData(0, 1, 1)
tdSql.checkData(0, 2, 'table_1')
tdLog.info(
"\n\n----------step4 : create table and insert again----------\n")
sql = '''insert into db.table2 using db.stable_1 (t1) tags ("table_2") ( values (now, ;'''
tdLog.info(sql)
tdSql.error(sql)
try:
tdSql.execute(sql)
tdLog.exit(" unexpected token")
except Exception as e:
tdLog.info(repr(e))
tdLog.info("DB error: syntax error near ', ;' (unexpected token)")
tdSql.execute('''insert into db.table2 using db.stable_1 (t1)
tags ("table_2") values ( now , 2) ''')
sql = '''select * from db.stable_1;'''
tdSql.query(sql)
tdSql.checkRows(2)
tdSql.checkData(1, 1, 2)
tdSql.checkData(1, 2, 'table_2')
tdLog.info(
"\n\n----------step5 : create table and insert without db----------\n"
)
tdSql.execute('''use db''')
sql = '''insert into table3 using stable_1 (t1) tags ("table_3") ( values (now, ;'''
tdLog.info(sql)
tdSql.error(sql)
try:
tdSql.execute(sql)
tdLog.exit(" unexpected token")
except Exception as e:
tdLog.info(repr(e))
tdLog.info("DB error: syntax error near ', ;' (unexpected token)")
tdSql.execute('''insert into table3 using stable_1 (t1 , t2)
tags ("table_3" , 333) values ( now , 3) ''')
sql = '''select * from stable_1;'''
tdSql.query(sql)
tdSql.checkRows(3)
tdSql.checkData(2, 1, 3)
tdSql.checkData(2, 2, 'table_3')
tdLog.info(
"\n\n----------step6 : create tables in one sql ----------\n")
sql = '''insert into table4 using stable_1 (t1) tags ("table_4") values (now, 4)
table5 using stable_1 (t1) tags ("table_5") ( values (now, ;'''
tdLog.info(sql)
tdSql.error(sql)
try:
tdSql.execute(sql)
tdLog.exit(" unexpected token")
except Exception as e:
tdLog.info(repr(e))
tdLog.info("DB error: syntax error near ', ;' (unexpected token)")
tdSql.execute(
'''insert into table4 using stable_1 (t1) tags ("table_4") values (now, 4)
table5 using stable_1 (t1) tags ("table_5") values (now, 5) '''
)
sql = '''select * from stable_1;'''
tdSql.query(sql)
tdSql.checkRows(5)
tdSql.checkData(3, 1, 4)
tdSql.checkData(3, 2, 'table_4')
tdSql.checkData(4, 1, 5)
tdSql.checkData(4, 2, 'table_5')
sql = '''insert into table6 using stable_1 (t1) tags ("table_6") ( values (now,
table7 using stable_1 (t1) tags ("table_7") values (now, 7);'''
tdLog.info(sql)
tdSql.error(sql)
try:
tdSql.execute(sql)
tdLog.exit(" invalid SQL")
except Exception as e:
tdLog.info(repr(e))
tdLog.info("invalid SQL")
tdSql.execute(
'''insert into table6 using stable_1 (t1 , t2) tags ("table_6" , 666) values (now, 6)
table7 using stable_1 (t1) tags ("table_7") values (now, 7) '''
)
sql = '''select * from stable_1;'''
tdSql.query(sql)
tdSql.checkRows(7)
tdSql.checkData(5, 1, 6)
tdSql.checkData(5, 2, 'table_6')
tdSql.checkData(6, 1, 7)
tdSql.checkData(6, 2, 'table_7')
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):
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)
tdSql.execute(
"create table stb(ts timestamp, options binary(7), city binary(10)) tags(type int)"
)
tdSql.execute(
"insert into tb1 using stb tags(1) values(%d, 'option1', 'beijing')"
% self.ts)
tdSql.execute(
"insert into tb2 using stb tags(2) values(%d, 'option2', 'shanghai')"
% self.ts)
tdSql.query(
"select options from stb where type = 1 limit 1 union all select options from stb where type = 2 limit 1"
)
tdSql.checkData(0, 0, "option1")
tdSql.checkData(1, 0, "option2")
tdSql.query(
"select 'dc' as options from stb where type = 1 limit 1 union all select 'ad' as options from stb where type = 2 limit 1"
)
tdSql.checkData(0, 0, "dc")
tdSql.checkData(1, 0, "ad")
tdSql.query(
"select 'dc' as options from stb where type = 1 limit 1 union all select 'adc' as options from stb where type = 2 limit 1"
)
tdSql.checkData(0, 0, "dc")
tdSql.checkData(1, 0, "adc")
tdSql.error(
"select 'dc' as options from stb where type = 1 limit 1 union all select 'ad' as city from stb where type = 2 limit 1"
)
