################################################################### # Copyright (c) 2016 by TAOS Technologies, Inc. # All rights reserved. # # This file is proprietary and confidential to TAOS Technologies. # No part of this file may be reproduced, stored, transmitted, # disclosed or used in any form or by any means other than as # expressly provided by the written permission from Jianhui Tao # ################################################################### # -*- coding: utf-8 -*- import sys import taos from util.log import * from util.cases import * from util.sql import * class TDTestCase: def init(self, conn, logSql): tdLog.debug("start to execute %s" % __file__) tdSql.init(conn.cursor()) def tb_all_query(self, num, sql="tb_all", where=""): tdSql.query( f"select distinct ts2 from {sql} {where}") tdSql.checkRows(num) tdSql.query( f"select distinct cint from {sql} {where}") tdSql.checkRows(num) tdSql.query( f"select distinct cbigint from {sql} {where}") tdSql.checkRows(num) tdSql.query( f"select distinct csmallint from {sql} {where}") tdSql.checkRows(num) tdSql.query( f"select distinct ctinyint from {sql} {where}") tdSql.checkRows(num) tdSql.query( f"select distinct cfloat from {sql} {where}") tdSql.checkRows(num) tdSql.query( f"select distinct cdouble from {sql} {where}") tdSql.checkRows(num) tdSql.query( f"select distinct cbool from {sql} {where}") if num < 3: tdSql.checkRows(num) else: tdSql.checkRows(3) tdSql.query( f"select distinct cbinary from {sql} {where}") tdSql.checkRows(num) tdSql.query( f"select distinct cnchar from {sql} {where}") tdSql.checkRows(num) tdSql.query( f"select distinct ts2 as a from {sql} {where}") tdSql.checkRows(num) tdSql.query( f"select distinct cint as a from {sql} {where}") tdSql.checkRows(num) tdSql.query( f"select distinct cbigint as a from {sql} {where}") tdSql.checkRows(num) tdSql.query( f"select distinct csmallint as a from {sql} {where}") tdSql.checkRows(num) tdSql.query( f"select distinct ctinyint as a from {sql} {where}") tdSql.checkRows(num) tdSql.query( f"select distinct cfloat as a from {sql} {where}") tdSql.checkRows(num) tdSql.query( f"select distinct cdouble as a from {sql} {where}") tdSql.checkRows(num) tdSql.query( f"select distinct cbool as a from {sql} {where}") if num < 3: tdSql.checkRows(num) else: tdSql.checkRows(3) tdSql.query( f"select distinct cbinary as a from {sql} {where}") tdSql.checkRows(num) tdSql.query( f"select distinct cnchar as a from {sql} {where}") tdSql.checkRows(num) def tb_all_query_sub(self, num, sql="tb_all", where="",colName = "c1"): tdSql.query( f"select distinct {colName} from {sql} {where}") tdSql.checkRows(num) def errorCheck(self, sql='tb_all'): tdSql.error(f"select distinct from {sql}") tdSql.error(f"distinct ts2 from {sql}") tdSql.error(f"distinct c1 from") tdSql.error(f"select distinct ts2, avg(cint) from {sql}") ## the following line is going to core dump #tdSql.error(f"select avg(cint), distinct ts2 from {sql}") tdSql.error(f"select distinct ts2 from {sql} group by cint") tdSql.error(f"select distinct ts2 from {sql} interval(1s)") tdSql.error(f"select distinct ts2 from {sql} slimit 1 soffset 1") tdSql.error(f"select distinct ts2 from {sql} slimit 1") ##order by is not supported but not being prohibited #tdSql.error(f"select distinct ts2 from {sql} order by desc") #tdSql.error(f"select distinct ts2 from {sql} order by asc") ##distinct should not use on first ts, but it can be applied #tdSql.error(f"select distinct ts from {sql}") ##distinct should not be used in inner query, but error did not occur # tdSql.error(f"select distinct ts2 from (select distinct ts2 from {sql})") def query_all_tb(self, whereNum=0,inNum=0,maxNum=0,tbName="tb_all"): self.tb_all_query(maxNum,sql=tbName) self.tb_all_query(num=whereNum,where="where ts2 = '2021-08-06 18:01:50.550'",sql=tbName) self.tb_all_query(num=whereNum,where="where cint = 1073741820",sql=tbName) self.tb_all_query(num=whereNum,where="where cbigint = 1125899906842620",sql=tbName) self.tb_all_query(num=whereNum,where="where csmallint = 150",sql=tbName) self.tb_all_query(num=whereNum,where="where ctinyint = 10",sql=tbName) self.tb_all_query(num=whereNum,where="where cfloat = 0.10",sql=tbName) self.tb_all_query(num=whereNum,where="where cdouble = 0.130",sql=tbName) self.tb_all_query(num=whereNum,where="where cbool = true",sql=tbName) self.tb_all_query(num=whereNum,where="where cbinary = 'adc1'",sql=tbName) self.tb_all_query(num=whereNum,where="where cnchar = '双精度浮'",sql=tbName) self.tb_all_query(num=inNum,where="where ts2 in ( '2021-08-06 18:01:50.550' , '2021-08-06 18:01:50.551' )",sql=tbName) self.tb_all_query(num=inNum,where="where cint in (1073741820,1073741821)",sql=tbName) self.tb_all_query(num=inNum,where="where cbigint in (1125899906842620,1125899906842621)",sql=tbName) self.tb_all_query(num=inNum,where="where csmallint in (150,151)",sql=tbName) self.tb_all_query(num=inNum,where="where ctinyint in (10,11)",sql=tbName) self.tb_all_query(num=inNum,where="where cfloat in (0.10,0.11)",sql=tbName) self.tb_all_query(num=inNum,where="where cdouble in (0.130,0.131)",sql=tbName) self.tb_all_query(num=inNum,where="where cbinary in ('adc0','adc1')",sql=tbName) self.tb_all_query(num=inNum,where="where cnchar in ('双','双精度浮')",sql=tbName) self.tb_all_query(num=inNum,where="where cbool in (0, 1)",sql=tbName) self.tb_all_query(num=whereNum,where="where cnchar like '双__浮'",sql=tbName) # self.tb_all_query(num=maxNum,where="where cnchar like '双%'",sql=tbName) # self.tb_all_query(num=maxNum,where="where cbinary like 'adc_'",sql=tbName) # self.tb_all_query(num=maxNum,where="where cbinary like 'a%'",sql=tbName) # self.tb_all_query(num=whereNum,where="limit 1",sql=tbName) # self.tb_all_query(num=whereNum,where="limit 1 offset 1",sql=tbName) #subquery self.tb_all_query(num=maxNum,sql=f'(select * from {tbName})') #subquery with where in outer query self.tb_all_query(num=whereNum,where="where ts2 = '2021-08-06 18:01:50.550'",sql=f'(select * from {tbName})') self.tb_all_query(num=whereNum,where="where cint = 1073741820",sql=f'(select * from {tbName})') self.tb_all_query(num=whereNum,where="where cbigint = 1125899906842620",sql=f'(select * from {tbName})') self.tb_all_query(num=whereNum,where="where csmallint = 150",sql=f'(select * from {tbName})') self.tb_all_query(num=whereNum,where="where ctinyint = 10",sql=f'(select * from {tbName})') self.tb_all_query(num=whereNum,where="where cfloat = 0.10",sql=f'(select * from {tbName})') self.tb_all_query(num=whereNum,where="where cdouble = 0.130",sql=f'(select * from {tbName})') self.tb_all_query(num=whereNum,where="where cbool = true",sql=f'(select * from {tbName})') self.tb_all_query(num=whereNum,where="where cbinary = 'adc1'",sql=f'(select * from {tbName})') self.tb_all_query(num=whereNum,where="where cnchar = '双精度浮'",sql=f'(select * from {tbName})') self.tb_all_query(num=inNum,where="where ts2 in ( '2021-08-06 18:01:50.550' , '2021-08-06 18:01:50.551' )",sql=f'(select * from {tbName})') self.tb_all_query(num=inNum,where="where cint in (1073741820,1073741821)",sql=f'(select * from {tbName})') self.tb_all_query(num=inNum,where="where cbigint in (1125899906842620,1125899906842621)",sql=f'(select * from {tbName})') self.tb_all_query(num=inNum,where="where csmallint in (150,151)",sql=f'(select * from {tbName})') self.tb_all_query(num=inNum,where="where ctinyint in (10,11)",sql=f'(select * from {tbName})') self.tb_all_query(num=inNum,where="where cfloat in (0.10,0.11)",sql=f'(select * from {tbName})') self.tb_all_query(num=inNum,where="where cdouble in (0.130,0.131)",sql=f'(select * from {tbName})') self.tb_all_query(num=inNum,where="where cbinary in ('adc0','adc1')",sql=f'(select * from {tbName})') self.tb_all_query(num=inNum,where="where cnchar in ('双','双精度浮')",sql=f'(select * from {tbName})') self.tb_all_query(num=inNum,where="where cbool in (0, 1)",sql=f'(select * from {tbName})') self.tb_all_query(num=whereNum,where="where cnchar like '双__浮'",sql=f'(select * from {tbName})') # self.tb_all_query(num=maxNum,where="where cnchar like '双%'",sql=f'(select * from {tbName})') # self.tb_all_query(num=maxNum,where="where cbinary like 'adc_'",sql=f'(select * from {tbName})') # self.tb_all_query(num=maxNum,where="where cbinary like 'a%'",sql=f'(select * from {tbName})') # self.tb_all_query(num=whereNum,where="limit 1",sql=f'(select * from {tbName})') # self.tb_all_query(num=whereNum,where="limit 1 offset 1",sql=f'(select * from {tbName})') #table query with inner query has error tdSql.error('select distinct ts2 from (select )') #table query with error option self.errorCheck() def run(self): tdSql.prepare() tdLog.notice( "==============phase1 distinct col1 with no values==========") tdSql.execute("create stable if not exists stb_all (ts timestamp, ts2 timestamp, cint int, cbigint bigint, csmallint smallint, ctinyint tinyint,cfloat float, cdouble double, cbool bool, cbinary binary(32), cnchar nchar(32)) tags(tint int)") tdSql.execute("create table if not exists tb_all using stb_all tags(1)") self.query_all_tb() tdLog.notice( "==============phase1 finished ==========\n\n\n") tdLog.notice( "==============phase2 distinct col1 all values are null==========") tdSql.execute( "insert into tb_all values(now,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)") tdSql.execute( "insert into tb_all values(now,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)") tdSql.execute( "insert into tb_all values(now,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)") # normal query self.query_all_tb(maxNum=1, inNum=0, whereNum=0) tdLog.notice("==============phase2 finished ==========\n\n\n") tdSql.prepare() tdSql.execute("create stable if not exists stb_all (ts timestamp, ts2 timestamp, cint int, cbigint bigint, csmallint smallint, ctinyint tinyint,cfloat float, cdouble double, cbool bool, cbinary binary(32), cnchar nchar(32)) tags(tint int)") tdSql.execute("create table if not exists tb_all using stb_all tags(1)") tdLog.notice( "==============phase3 distinct with distinct values ==========\n\n\n") tdSql.execute( "insert into tb_all values(now+1s,'2021-08-06 18:01:50.550',1073741820,1125899906842620,150,10,0.10,0.130, true,'adc0','双')") tdSql.execute( "insert into tb_all values(now+2s,'2021-08-06 18:01:50.551',1073741821,1125899906842621,151,11,0.11,0.131,false,'adc1','双精度浮')") tdSql.execute( "insert into tb_all values(now+3s,'2021-08-06 18:01:50.552',1073741822,1125899906842622,152,12,0.12,0.132,NULL,'adc2','双精度')") tdSql.execute( "insert into tb_all values(now+4s,'2021-08-06 18:01:50.553',1073741823,1125899906842623,153,13,0.13,0.133,NULL,'adc3','双精')") tdSql.execute( "insert into tb_all values(now+5s,'2021-08-06 18:01:50.554',1073741824,1125899906842624,154,14,0.14,0.134,NULL,'adc4','双精度浮点型')") # normal query self.query_all_tb(maxNum=5,inNum=2,whereNum=1) tdLog.notice("==============phase3 finishes ==========\n\n\n") tdSql.prepare() tdSql.execute("create stable if not exists stb_all (ts timestamp, ts2 timestamp, cint int, cbigint bigint, csmallint smallint, ctinyint tinyint,cfloat float, cdouble double, cbool bool, cbinary binary(32), cnchar nchar(32)) tags(tint int)") tdSql.execute("create table if not exists tb_all using stb_all tags(1)") tdLog.notice( "==============phase4 distinct with some values the same values ==========\n\n\n") tdSql.execute( "insert into tb_all values(now+10s,'2021-08-06 18:01:50.550',1073741820,1125899906842620,150,10,0.10,0.130, true,'adc0','双')") tdSql.execute( "insert into tb_all values(now+20s,'2021-08-06 18:01:50.551',1073741821,1125899906842621,151,11,0.11,0.131,false,'adc1','双精度浮')") tdSql.execute( "insert into tb_all values(now+30s,'2021-08-06 18:01:50.552',1073741822,1125899906842622,152,12,0.12,0.132,NULL,'adc2','双精度')") tdSql.execute( "insert into tb_all values(now+40s,'2021-08-06 18:01:50.553',1073741823,1125899906842623,153,13,0.13,0.133,NULL,'adc3','双精')") tdSql.execute( "insert into tb_all values(now+50s,'2021-08-06 18:01:50.554',1073741824,1125899906842624,154,14,0.14,0.134,NULL,'adc4','双精度浮点型')") # normal query self.query_all_tb(maxNum=5,inNum=2,whereNum=1) tdLog.notice( "==============phase4 finishes ==========\n\n\n") def stop(self): tdSql.close() tdLog.success("%s successfully executed" % __file__) tdCases.addWindows(__file__, TDTestCase()) tdCases.addLinux(__file__, TDTestCase())