def run(self):
tbNum = 10
rowNum = 20
ts_begin = 1633017600000
tdSql.prepare()
tdLog.info("===== preparing data =====")
tdSql.execute(
"create table stb(ts timestamp, tbcol int, tbcol2 float) tags(tgcol int)"
)
for i in range(tbNum):
tdSql.execute("create table tb%d using stb tags(%d)" % (i, i))
for j in range(rowNum):
tdSql.execute("insert into tb%d values (%d, %d, %d)" %
(i, ts_begin + j, j, j))
time.sleep(0.1)
self.createFuncStream("count(*)", "c1", 200)
self.createFuncStream("count(tbcol)", "c2", 200)
self.createFuncStream("count(tbcol2)", "c3", 200)
self.createFuncStream("avg(tbcol)", "av", 9.5)
self.createFuncStream("sum(tbcol)", "su", 1900)
self.createFuncStream("min(tbcol)", "mi", 0)
self.createFuncStream("max(tbcol)", "ma", 19)
self.createFuncStream("first(tbcol)", "fi", 0)
self.createFuncStream("last(tbcol)", "la", 19)
#tdSql.query("select stddev(tbcol) from stb interval(1d)")
#tdSql.query("select leastsquares(tbcol, 1, 1) from stb interval(1d)")
tdSql.query("select top(tbcol, 1) from stb interval(1d)")
tdSql.query("select bottom(tbcol, 1) from stb interval(1d)")
#tdSql.query("select percentile(tbcol, 1) from stb interval(1d)")
#tdSql.query("select diff(tbcol) from stb interval(1d)")
tdSql.query(
"select count(tbcol) from stb where ts < now + 4m interval(1d)")
tdSql.checkData(0, 1, 200)
#tdSql.execute("create table strm_wh as select count(tbcol) from stb where ts < now + 4m interval(1d)")
self.createFuncStream("count(tbcol)", "as", 200)
tdSql.query("select count(tbcol) from stb interval(1d) group by tgcol")
tdSql.checkData(0, 1, 20)
tdSql.query(
"select count(tbcol) from stb where ts < now + 4m interval(1d) group by tgcol"
)
tdSql.checkData(0, 1, 20)
self.checkStreamData("c1", 200)
self.checkStreamData("c2", 200)
self.checkStreamData("c3", 200)
self.checkStreamData("av", 9.5)
self.checkStreamData("su", 1900)
self.checkStreamData("mi", 0)
self.checkStreamData("ma", 19)
self.checkStreamData("fi", 0)
self.checkStreamData("la", 19)
#self.checkStreamData("wh", 200)
self.checkStreamData("as", 200)
def run(self):
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.query("show vgroups")
dnodes = []
for i in range(10):
dnodes.append(int(tdSql.getData(i, 4)))
s = set(dnodes)
if len(s) < 3:
tdLog.exit("cluster is not balanced")
tdLog.info("cluster is balanced")
nodes.removeConfigs("maxVgroupsPerDb", "10")
nodes.removeConfigs("maxTablesPerVnode", "1000")
nodes.restartAllTaosd()
tdSql.close()
tdLog.success("%s successfully executed" % __file__)
def run(self):
rowNum = 200
totalNum = 200
tdSql.prepare()
tdLog.info("=============== step1")
tdSql.execute("create table mt(ts timestamp, tbcol int, tbcol2 float) TAGS(tgcol int)")
for i in range(5):
tdSql.execute("create table tb%d using mt tags(%d)" % (i, i))
for j in range(rowNum):
tdSql.execute("insert into tb%d values(now + %ds, %d, %d)" % (i, j, j, j))
time.sleep(0.1)
tdLog.info("=============== step2")
tdSql.query("select count(*), count(tbcol), count(tbcol2) from mt interval(10s)")
tdSql.execute("create table st as select count(*), count(tbcol), count(tbcol2) from mt interval(10s)")
tdLog.info("=============== step3")
tdSql.waitedQuery("select * from st", 1, 120)
v = tdSql.getData(0, 3)
if v >= 51:
tdLog.exit("value is %d, which is larger than 51" % v)
tdLog.info("=============== step4")
for i in range(5, 10):
tdSql.execute("create table tb%d using mt tags(%d)" % (i, i))
for j in range(rowNum):
tdSql.execute("insert into tb%d values(now + %ds, %d, %d)" % (i, j, j, j))
tdLog.info("=============== step5")
tdLog.sleep(40)
tdSql.waitedQuery("select * from st order by ts desc", 1, 120)
v = tdSql.getData(0, 3)
if v <= 51:
tdLog.exit("value is %d, which is smaller than 51" % v)
def createFuncStream(self, expr, suffix, value):
tbname = "strm_" + suffix
tdLog.info("create stream table %s" % tbname)
tdSql.query("select %s from tb1 interval(1d)" % expr)
tdSql.checkData(0, 1, value)
tdSql.execute("create table %s as select %s from tb1 interval(1d)" %
(tbname, expr))
def wildcardFilterOnTags(self):
tdLog.debug("begin wildcardFilterOnTag")
tdSql.prepare()
tdSql.execute(
"create table stb (ts timestamp, c1 int, c2 binary(10)) tags(t1 binary(10))"
)
tdSql.execute("create table tb1 using stb tags('a1')")
tdSql.execute("create table tb2 using stb tags('b2')")
tdSql.execute("create table tb3 using stb tags('a3')")
tdSql.execute(
"create table strm as select count(*), avg(c1), first(c2) from stb where t1 like 'a%' interval(4s) sliding(2s)"
)
tdSql.query("describe strm")
tdSql.checkRows(4)
tdLog.sleep(1)
tdSql.execute("insert into tb1 values (now, 0, 'tb1')")
tdLog.sleep(4)
tdSql.execute("insert into tb2 values (now, 2, 'tb2')")
tdLog.sleep(4)
tdSql.execute("insert into tb3 values (now, 0, 'tb3')")
tdSql.waitedQuery("select * from strm", 4, 60)
tdSql.checkRows(4)
tdSql.checkData(0, 2, 0.000000000)
if tdSql.getData(0, 3) == 'tb2':
tdLog.exit("unexpected value of data03")
if tdSql.getData(1, 3) == 'tb2':
tdLog.exit("unexpected value of data13")
if tdSql.getData(2, 3) == 'tb2':
tdLog.exit("unexpected value of data23")
if tdSql.getData(3, 3) == 'tb2':
tdLog.exit("unexpected value of data33")
tdLog.info("add table tb4 to see if stream still works correctly")
# The vnode client needs to refresh metadata cache to allow strm calculate tb4's data.
# But the current refreshing frequency is every 10 min
# commented out the case below to save running time
tdSql.execute("create table tb4 using stb tags('a4')")
tdSql.execute("insert into tb4 values(now, 4, 'tb4')")
tdSql.waitedQuery("select * from strm order by ts desc", 6, 60)
tdSql.checkRows(6)
tdSql.checkData(0, 2, 4)
tdSql.checkData(0, 3, "tb4")
tdLog.info("change tag values to see if stream still works correctly")
tdSql.execute("alter table tb4 set tag t1='b4'")
tdLog.sleep(3)
tdSql.execute("insert into tb1 values (now, 1, 'tb1_a1')")
tdLog.sleep(4)
tdSql.execute("insert into tb4 values (now, -4, 'tb4_b4')")
tdSql.waitedQuery("select * from strm order by ts desc", 8, 100)
tdSql.checkRows(8)
tdSql.checkData(0, 2, 1)
tdSql.checkData(0, 3, "tb1_a1")
def run(self):
# cluster environment set up
tdLog.info("Test case 7, 10")
nodes = Nodes()
ctest = ClusterTest(nodes.node1.hostName)
ctest.connectDB()
tdSql.init(ctest.conn.cursor(), False)
nodes.node1.stopTaosd()
tdSql.query("show dnodes")
tdSql.checkRows(3)
tdSql.checkData(0, 4, "offline")
tdSql.checkData(1, 4, "ready")
tdSql.checkData(2, 4, "ready")
nodes.node1.startTaosd()
tdSql.checkRows(3)
tdSql.checkData(0, 4, "ready")
tdSql.checkData(1, 4, "ready")
tdSql.checkData(2, 4, "ready")
nodes.node2.stopTaosd()
tdSql.query("show dnodes")
tdSql.checkRows(3)
tdSql.checkData(0, 4, "ready")
tdSql.checkData(1, 4, "offline")
tdSql.checkData(2, 4, "ready")
nodes.node2.startTaosd()
tdSql.checkRows(3)
tdSql.checkData(0, 4, "ready")
tdSql.checkData(1, 4, "ready")
tdSql.checkData(2, 4, "ready")
nodes.node3.stopTaosd()
tdSql.query("show dnodes")
tdSql.checkRows(3)
tdSql.checkData(0, 4, "ready")
tdSql.checkData(1, 4, "ready")
tdSql.checkData(2, 4, "offline")
nodes.node3.startTaosd()
tdSql.checkRows(3)
tdSql.checkData(0, 4, "ready")
tdSql.checkData(1, 4, "ready")
tdSql.checkData(2, 4, "ready")
tdSql.close()
tdLog.success("%s successfully executed" % __file__)
def run(self):
rowNum = 200
tdSql.prepare()
ts_now = 1633017600000
tdLog.info("=============== step1")
tdSql.execute("create table mt(ts timestamp, tbcol int, tbcol2 float) TAGS(tgcol int)")
for i in range(5):
tdSql.execute("create table tb%d using mt tags(%d)" % (i, i))
for j in range(rowNum):
tdSql.execute("insert into tb%d values(%d, %d, %d)" % (i, ts_now, j, j))
ts_now += 1000
time.sleep(0.1)
tdLog.info("=============== step2")
tdSql.query("select count(*), count(tbcol), count(tbcol2) from mt interval(10s)")
tdSql.execute("create table st as select count(*), count(tbcol), count(tbcol2) from mt interval(10s)")
tdLog.info("=============== step3")
start = time.time()
tdSql.waitedQuery("select * from st", 1, 180)
delay = int(time.time() - start) + 80
v = tdSql.getData(0, 3)
if v != 10:
tdLog.exit("value is %d, expect is 10." % v)
tdLog.info("=============== step4")
for i in range(5, 10):
tdSql.execute("create table tb%d using mt tags(%d)" % (i, i))
for j in range(rowNum):
tdSql.execute("insert into tb%d values(%d, %d, %d)" % (i, ts_now, j, j))
ts_now += 1000
tdLog.info("=============== step5")
maxValue = 0
for i in range(delay):
time.sleep(1)
tdSql.query("select * from st order by ts desc")
v = tdSql.getData(0, 3)
if v > maxValue:
maxValue = v
if v >= 10:
break
if maxValue < 10:
tdLog.exit("value is %d, expect is 10" % maxValue)
def run(self):
tdSql.prepare()
print("==============step1")
tdLog.info("create table")
tdSql.execute(
"create table if not exists st(ts timestamp, tagtype int) tags(dev nchar(50))"
)
tdSql.execute(
"CREATE TABLE if not exists dev_001 using st tags('dev_01')")
print("==============step2")
tdLog.info("multiple inserts")
tdSql.execute(
"INSERT INTO dev_001 VALUES ('2020-05-13 10:00:00.000', 1),('2020-05-13 10:00:00.001', 1)"
)
tdSql.checkAffectedRows(2)
def run(self):
tbNum = 10
rowNum = 20
tdSql.prepare()
tdLog.info("===== step1 =====")
tdSql.execute(
"create table stb(ts timestamp, tbcol int, tbcol2 float) tags(tgcol int)"
)
for i in range(tbNum):
tdSql.execute("create table tb%d using stb tags(%d)" % (i, i))
for j in range(rowNum):
tdSql.execute("insert into tb%d values (now - %dm, %d, %d)" %
(i, 1440 - j, j, j))
time.sleep(0.1)
self.createFuncStream("count(*)", "c1", rowNum)
self.createFuncStream("count(tbcol)", "c2", rowNum)
self.createFuncStream("count(tbcol2)", "c3", rowNum)
self.createFuncStream("avg(tbcol)", "av", 9.5)
self.createFuncStream("sum(tbcol)", "su", 190)
self.createFuncStream("min(tbcol)", "mi", 0)
self.createFuncStream("max(tbcol)", "ma", 19)
self.createFuncStream("first(tbcol)", "fi", 0)
self.createFuncStream("last(tbcol)", "la", 19)
self.createFuncStream("stddev(tbcol)", "st", 5.766281297335398)
self.createFuncStream("percentile(tbcol, 1)", "pe", 0.19)
self.createFuncStream("count(tbcol)", "as", rowNum)
self.checkStreamData("c1", rowNum)
self.checkStreamData("c2", rowNum)
self.checkStreamData("c3", rowNum)
self.checkStreamData("av", 9.5)
self.checkStreamData("su", 190)
self.checkStreamData("mi", 0)
self.checkStreamData("ma", 19)
self.checkStreamData("fi", 0)
self.checkStreamData("la", 19)
self.checkStreamData("st", 5.766281297335398)
self.checkStreamData("pe", 0.19)
self.checkStreamData("as", rowNum)
def run(self):
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):
nodes = Nodes()
ctest = ClusterTest(nodes.node1.hostName)
ctest.connectDB()
tdSql.init(ctest.conn.cursor(), False)
## Test case 1 ##
tdLog.info("Test case 1 repeat %d times" % ctest.repeat)
for i in range(ctest.repeat):
tdLog.info("Start Round %d" % (i + 1))
replica = random.randint(1, 3)
ctest.createSTable(replica)
ctest.run()
tdLog.sleep(10)
tdSql.query("select count(*) from %s.%s" %
(ctest.dbName, ctest.stbName))
tdSql.checkData(0, 0, ctest.numberOfRecords * ctest.numberOfTables)
tdLog.info("Round %d completed" % (i + 1))
tdSql.close()
tdLog.success("%s successfully executed" % __file__)
def run(self):
tdSql.prepare()
print("==============step1")
tdLog.info("create database and table")
tdSql.execute("create database db_test")
tdSql.execute(
"create table if not exists db_test.st(ts timestamp, tagtype int) tags(dev nchar(50))"
)
tdSql.execute(
"CREATE TABLE if not exists db_test.dev_001 using db_test.st tags('dev_01')"
)
print("==============step2")
tdLog.info("alter table add column")
tdSql.execute(
"ALTER TABLE db_test.st add COLUMN tag_version nchar(20)")
tdSql.query("describe db_test.st")
tdSql.checkRows(4)
print("==============step3")
tdLog.info("alter table drop column")
tdSql.execute("ALTER TABLE db_test.st drop COLUMN tag_version")
tdSql.query("describe db_test.st")
tdSql.checkRows(3)
print("==============step4")
tdLog.info("drop table")
tdSql.execute("drop table db_test.st")
tdSql.execute(
"create table if not exists db_test.st(ts timestamp, tagtype int) tags(dev nchar(50))"
)
tdSql.execute(
"CREATE TABLE if not exists db_test.dev_001 using db_test.st tags('dev_01')"
)
tdSql.execute(
"INSERT INTO db_test.dev_001 VALUES ('2020-05-13 10:00:00.000', 1)"
)
tdSql.query("select * from db_test.dev_001")
tdSql.checkRows(1)
print("==============step5")
tdLog.info("alter table add column")
tdSql.execute(
"ALTER TABLE db_test.st add COLUMN tag_version nchar(20)")
tdSql.query("describe db_test.st")
tdSql.checkRows(4)
tdSql.execute(
"INSERT INTO db_test.dev_001 VALUES ('2020-05-13 10:00:00.010', 1, '1.2.1')"
)
tdSql.query("select * from db_test.st")
tdSql.checkRows(2)
print("==============step6")
tdLog.info("alter table drop column")
tdSql.execute("ALTER TABLE db_test.st drop COLUMN tag_version")
tdSql.query("describe db_test.st")
tdSql.checkRows(3)
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-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()
# 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):
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()
# 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()
# 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()
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')
# https://jira.taosdata.com:18080/browse/TS-444
tdLog.info("test case for TS-444")
tdSql.query(
"select count(*) as count, loc from st where ts between 1600000000000 and 1600000000010 group by loc"
)
tdSql.checkRows(6)
tdSql.query(
"select count(*) as count, loc from st where ts between 1600000000020 and 1600000000030 group by loc"
)
tdSql.checkRows(0)
tdSql.query(
''' select count(*) as count, loc from st where ts between 1600000000000 and 1600000000010 group by loc
union all
select count(*) as count, loc from st where ts between 1600000000020 and 1600000000030 group by loc'''
)
tdSql.checkRows(6)
tdSql.query(
''' select count(*) as count, loc from st where ts between 1600000000020 and 1600000000030 group by loc
union all
select count(*) as count, loc from st where ts between 1600000000000 and 1600000000010 group by loc'''
)
tdSql.checkRows(6)
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 queryGroupTbname(self):
'''
select a1,a2...a10 from stb where tbname in (t1,t2,...t10) and ts...
'''
tdCom.cleanTb()
table_name = tdCom.getLongName(8, "letters_mixed")
tbname_list = list(map(lambda x: f'table_name_sub{x}', range(1, 11)))
tb_str = ""
for tbname in tbname_list:
globals()[tbname] = tdCom.getLongName(8, "letters_mixed")
tdSql.execute(
f'CREATE TABLE {table_name} (ts timestamp, {table_name_sub1} tinyint, \
{table_name_sub2} smallint, {table_name_sub3} int, {table_name_sub4} bigint, \
{table_name_sub5} float, {table_name_sub6} double, {table_name_sub7} binary(20),\
{table_name_sub8} nchar(20), {table_name_sub9} bool) tags ({table_name_sub10} binary(20))'
)
for tbname in tbname_list:
tb_str += tbname
tdSql.execute(
f'create table {globals()[tbname]} using {table_name} tags ("{globals()[tbname]}")'
)
for i in range(10):
for tbname in tbname_list:
tdSql.execute(
f'insert into {globals()[tbname]} values (now, 1, 2, 3, 4, 1.1, 2.2, "{globals()[tbname]}", "{globals()[tbname]}", True)'
)
for i in range(100):
tdSql.query(
f'select {table_name_sub1},{table_name_sub2},{table_name_sub3},{table_name_sub4},{table_name_sub5},{table_name_sub6},{table_name_sub7},{table_name_sub8},{table_name_sub9} from {table_name} where tbname in ("{table_name_sub1}","{table_name_sub2}","{table_name_sub3}","{table_name_sub4}","{table_name_sub5}","{table_name_sub6}","{table_name_sub7}","{table_name_sub8}","{table_name_sub9}") and ts >= "1980-01-01 00:00:00.000"'
)
tdSql.checkRows(90)
# TS-634
tdLog.info("test case for bug TS-634")
tdSql.execute("create database test")
tdSql.execute("use test")
tdSql.execute(
"create table meters (ts TIMESTAMP,voltage INT) TAGS (tableid INT)"
)
tdSql.execute("CREATE TABLE t1 USING meters TAGS (1)")
tdSql.execute("CREATE TABLE t2 USING meters TAGS (2)")
ts = 1605381041000
for i in range(10):
tdSql.execute("INSERT INTO t1 values(%d, %d)" %
(ts + i, random.randint(0, 100)))
tdSql.execute("INSERT INTO t2 values(%d, %d)" %
(ts + i, random.randint(0, 100)))
tdSql.query(
"select last_row(*), tbname from meters group by tbname order by ts desc"
)
tdSql.checkRows(2)
tdSql.execute("INSERT INTO t2 values(now, 2)")
tdSql.query(
"select last_row(*), tbname from meters group by tbname order by ts desc"
)
tdSql.checkRows(2)
def run(self):
tbNum = 10
rowNum = 20
totalNum = tbNum * rowNum
tdSql.prepare()
tdLog.info("===== preparing data =====")
tdSql.execute(
"create table stb(ts timestamp, tbcol int, tbcol2 float) tags(tgcol int)")
for i in range(tbNum):
tdSql.execute("create table tb%d using stb tags(%d)" % (i, i))
for j in range(rowNum):
tdSql.execute(
"insert into tb%d values (now - %dm, %d, %d)" %
(i, 1440 - j, j, j))
time.sleep(0.1)
tdLog.info("===== step 1 =====")
tdSql.query("select count(*), count(tbcol), count(tbcol2) from stb interval(1d)")
tdSql.checkData(0, 1, totalNum)
tdSql.checkData(0, 2, totalNum)
tdSql.checkData(0, 3, totalNum)
tdLog.info("===== step 2 =====")
tdSql.execute("create table strm_c3 as select count(*), count(tbcol), count(tbcol2) from stb interval(1d)")
tdLog.info("===== step 3 =====")
tdSql.execute("create table strm_c32 as select count(*), count(tbcol) as c1, count(tbcol2) as c2, count(tbcol) as c3, count(tbcol) as c4, count(tbcol) as c5, count(tbcol) as c6, count(tbcol) as c7, count(tbcol) as c8, count(tbcol) as c9, count(tbcol) as c10, count(tbcol) as c11, count(tbcol) as c12, count(tbcol) as c13, count(tbcol) as c14, count(tbcol) as c15, count(tbcol) as c16, count(tbcol) as c17, count(tbcol) as c18, count(tbcol) as c19, count(tbcol) as c20, count(tbcol) as c21, count(tbcol) as c22, count(tbcol) as c23, count(tbcol) as c24, count(tbcol) as c25, count(tbcol) as c26, count(tbcol) as c27, count(tbcol) as c28, count(tbcol) as c29, count(tbcol) as c30 from stb interval(1d)")
tdLog.info("===== step 4 =====")
tdSql.query("select count(*), count(tbcol) as c1, count(tbcol2) as c2, count(tbcol) as c3, count(tbcol) as c4, count(tbcol) as c5, count(tbcol) as c6, count(tbcol) as c7, count(tbcol) as c8, count(tbcol) as c9, count(tbcol) as c10, count(tbcol) as c11, count(tbcol) as c12, count(tbcol) as c13, count(tbcol) as c14, count(tbcol) as c15, count(tbcol) as c16, count(tbcol) as c17, count(tbcol) as c18, count(tbcol) as c19, count(tbcol) as c20, count(tbcol) as c21, count(tbcol) as c22, count(tbcol) as c23, count(tbcol) as c24, count(tbcol) as c25, count(tbcol) as c26, count(tbcol) as c27, count(tbcol) as c28, count(tbcol) as c29, count(tbcol) as c30 from stb interval(1d)")
tdSql.checkData(0, 1, totalNum)
tdSql.checkData(0, 2, totalNum)
tdSql.checkData(0, 3, totalNum)
tdLog.info("===== step 5 =====")
tdSql.execute("create table strm_c31 as select count(*), count(tbcol) as c1, count(tbcol2) as c2, count(tbcol) as c3, count(tbcol) as c4, count(tbcol) as c5, count(tbcol) as c6, count(tbcol) as c7, count(tbcol) as c8, count(tbcol) as c9, count(tbcol) as c10, count(tbcol) as c11, count(tbcol) as c12, count(tbcol) as c13, count(tbcol) as c14, count(tbcol) as c15, count(tbcol) as c16, count(tbcol) as c17, count(tbcol) as c18, count(tbcol) as c19, count(tbcol) as c20, count(tbcol) as c21, count(tbcol) as c22, count(tbcol) as c23, count(tbcol) as c24, count(tbcol) as c25, count(tbcol) as c26, count(tbcol) as c27, count(tbcol) as c28, count(tbcol) as c29, count(tbcol) as c30 from stb interval(1d)")
tdLog.info("===== step 6 =====")
tdSql.query("select avg(tbcol), sum(tbcol), min(tbcol), max(tbcol), first(tbcol), last(tbcol) from stb interval(1d)")
tdSql.checkData(0, 1, 9.5)
tdSql.checkData(0, 2, 1900)
tdSql.checkData(0, 3, 0)
tdSql.checkData(0, 4, 19)
tdSql.checkData(0, 5, 0)
tdSql.checkData(0, 6, 19)
tdSql.execute("create table strm_avg as select avg(tbcol), sum(tbcol), min(tbcol), max(tbcol), first(tbcol), last(tbcol) from stb interval(1d)")
tdLog.info("===== step 7 =====")
tdSql.query("select avg(tbcol), sum(tbcol), min(tbcol), max(tbcol), first(tbcol), last(tbcol), count(tbcol) from stb where ts < now + 4m interval(1d)")
tdSql.checkData(0, 1, 9.5)
tdSql.checkData(0, 2, 1900)
tdSql.checkData(0, 3, 0)
tdSql.checkData(0, 4, 19)
tdSql.checkData(0, 5, 0)
tdSql.checkData(0, 6, 19)
tdSql.checkData(0, 7, totalNum)
tdLog.info("===== step 8 =====")
tdSql.query("select avg(tbcol), sum(tbcol), min(tbcol), max(tbcol), first(tbcol), last(tbcol), count(tbcol) from stb where ts < now + 4m interval(1d)")
tdSql.checkData(0, 1, 9.5)
tdSql.checkData(0, 2, 1900)
tdSql.checkData(0, 3, 0)
tdSql.checkData(0, 4, 19)
tdSql.checkData(0, 5, 0)
tdSql.checkData(0, 6, 19)
tdSql.checkData(0, 7, totalNum)
tdLog.info("===== step 9 =====")
tdSql.waitedQuery("select * from strm_c3", 1, 120)
tdSql.checkData(0, 1, totalNum)
tdSql.checkData(0, 2, totalNum)
tdSql.checkData(0, 3, totalNum)
tdLog.info("===== step 10 =====")
tdSql.waitedQuery("select * from strm_c31", 1, 30)
for i in range(1, 10):
tdSql.checkData(0, i, totalNum)
tdLog.info("===== step 11 =====")
tdSql.waitedQuery("select * from strm_avg", 1, 20)
tdSql.checkData(0, 1, 9.5)
tdSql.checkData(0, 2, 1900)
tdSql.checkData(0, 3, 0)
tdSql.checkData(0, 4, 19)
tdSql.checkData(0, 5, 0)
tdSql.checkData(0, 6, 19)
def run(self):
tdSql.prepare()
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')
# https://jira.taosdata.com:18080/browse/TS-444
tdLog.info("test case for TS-444")
tdSql.query(
"select count(*) as count, loc from st where ts between 1600000000000 and 1600000000010 group by loc"
)
tdSql.checkRows(6)
tdSql.query(
"select count(*) as count, loc from st where ts between 1600000000020 and 1600000000030 group by loc"
)
tdSql.checkRows(0)
tdSql.query(
''' select count(*) as count, loc from st where ts between 1600000000000 and 1600000000010 group by loc
union all
select count(*) as count, loc from st where ts between 1600000000020 and 1600000000030 group by loc'''
)
tdSql.checkRows(6)
tdSql.query(
''' select count(*) as count, loc from st where ts between 1600000000020 and 1600000000030 group by loc
union all
select count(*) as count, loc from st where ts between 1600000000000 and 1600000000010 group by loc'''
)
tdSql.checkRows(6)
# https://jira.taosdata.com:18080/browse/TS-715
tdLog.info("test case for TS-715")
sql = ""
tdSql.execute(
"create table st2(ts timestamp, c1 int, c2 int, c3 int) tags(loc nchar(20))"
)
for i in range(101):
if i == 0:
sql = "select last(*) from sub0 "
else:
sql += f"union all select last(*) from sub{i} "
tdSql.execute("create table sub%d using st2 tags('nchar%d')" %
(i, i))
tdSql.execute(
"insert into sub%d values(%d, %d, %d, %d)(%d, %d, %d, %d)" %
(i, self.ts + i, i, i, i, self.ts + i + 101, i + 101, i + 101,
i + 101))
tdSql.error(sql)
# TS-795
tdLog.info("test case for TS-795")
functions = [
"*", "count", "avg", "twa", "irate", "sum", "stddev",
"leastsquares", "min", "max", "first", "last", "top", "bottom",
"percentile", "apercentile", "last_row"
]
for func in functions:
expr = func
if func == "top" or func == "bottom":
expr += "(c1, 1)"
elif func == "percentile" or func == "apercentile":
expr += "(c1, 0.5)"
elif func == "leastsquares":
expr = func + "(c1, 1, 1)"
elif func == "*":
expr = func
else:
expr += "(c1)"
for i in range(100):
if i == 0:
sql = f"select {expr} from sub0 "
else:
sql += f"union all select {expr} from sub{i} "
tdSql.query(sql)
if func == "*":
tdSql.checkRows(200)
else:
tdSql.checkRows(100)
