################################################################### # 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 from numpy.lib.function_base import insert import taos from util.log import * from util.cases import * from util.sql import * import numpy as np # constant define WAITS = 5 # wait seconds class TDTestCase: # # --------------- main frame ------------------- # def caseDescription(self): ''' limit and offset keyword function test cases; case1: limit offset base function test case2: limit offset advance test ''' return # init def init(self, conn, logSql): tdLog.debug("start to execute %s" % __file__) tdSql.init(conn.cursor()) tdSql.prepare() self.create_tables(); self.ts = 1500000000000 # run case def run(self): # insert data self.insert_data("t1", self.ts, 300*10000, 30000); # test base case self.test_case1() tdLog.debug(" LIMIT test_case1 ............ [OK]") # test advance case self.test_case2() tdLog.debug(" LIMIT test_case2 ............ [OK]") # stop def stop(self): tdSql.close() tdLog.success("%s successfully executed" % __file__) # # --------------- case ------------------- # # create table def create_tables(self): # super table tdSql.execute("create table st(ts timestamp, i1 int) tags(area int)"); # child table tdSql.execute("create table t1 using st tags(1)"); tdSql.execute("create table t2 using st tags(2)"); tdSql.execute("create table t3 using st tags(3)"); return # insert data1 def insert_data(self, tbname, ts_start, count, batch_num): pre_insert = "insert into %s values"%tbname sql = pre_insert tdLog.debug("doing insert table %s rows=%d ..."%(tbname, count)) for i in range(count): sql += " (%d,%d)"%(ts_start + i*1000, i) if i >0 and i%batch_num == 0: tdSql.execute(sql) sql = pre_insert # end sql if sql != pre_insert: tdSql.execute(sql) tdLog.debug("INSERT TABLE DATA ............ [OK]") return # test case1 base def test_case1(self): # # limit base function # # base no where sql = "select * from t1 limit 10" tdSql.waitedQuery(sql, 10, WAITS) tdSql.checkData(0, 1, 0) tdSql.checkData(9, 1, 9) sql = "select * from t1 order by ts desc limit 10" # desc tdSql.waitedQuery(sql, 10, WAITS) tdSql.checkData(0, 1, 2999999) tdSql.checkData(9, 1, 2999990) # have where sql = "select * from t1 where ts>='2017-07-14 10:40:01' and ts<'2017-07-14 10:40:06' limit 10" tdSql.waitedQuery(sql, 5, WAITS) tdSql.checkData(0, 1, 1) tdSql.checkData(4, 1, 5) sql = "select * from t1 where ts>='2017-08-18 03:59:52' and ts<'2017-08-18 03:59:57' order by ts desc limit 10" # desc tdSql.waitedQuery(sql, 5, WAITS) tdSql.checkData(0, 1, 2999996) tdSql.checkData(4, 1, 2999992) # # offset base function # # no where sql = "select * from t1 limit 10 offset 5" tdSql.waitedQuery(sql, 10, WAITS) tdSql.checkData(0, 1, 5) tdSql.checkData(9, 1, 14) sql = "select * from t1 order by ts desc limit 10 offset 5" # desc tdSql.waitedQuery(sql, 10, WAITS) tdSql.checkData(0, 1, 2999994) tdSql.checkData(9, 1, 2999985) # have where only ts sql = "select * from t1 where ts>='2017-07-14 10:40:10' and ts<'2017-07-14 10:40:20' limit 10 offset 5" tdSql.waitedQuery(sql, 5, WAITS) tdSql.checkData(0, 1, 15) tdSql.checkData(4, 1, 19) sql = "select * from t1 where ts>='2017-08-18 03:59:52' and ts<'2017-08-18 03:59:57' order by ts desc limit 10 offset 4" # desc tdSql.waitedQuery(sql, 1, WAITS) tdSql.checkData(0, 1, 2999992) # have where with other column condition sql = "select * from t1 where i1>=1 and i1<11 limit 10 offset 5" tdSql.waitedQuery(sql, 5, WAITS) tdSql.checkData(0, 1, 6) tdSql.checkData(4, 1, 10) sql = "select * from t1 where i1>=300000 and i1<=500000 order by ts desc limit 10 offset 100000" # desc tdSql.waitedQuery(sql, 10, WAITS) tdSql.checkData(0, 1, 400000) tdSql.checkData(9, 1, 399991) # have where with ts and other column condition sql = "select * from t1 where ts>='2017-07-14 10:40:10' and ts<'2017-07-14 10:40:50' and i1>=20 and i1<=25 limit 10 offset 5" tdSql.waitedQuery(sql, 1, WAITS) tdSql.checkData(0, 1, 25) return # test advance def test_case2(self): # # OFFSET merge file data with memory data # # offset sql = "select * from t1 limit 10 offset 72000" tdSql.waitedQuery(sql, 10, WAITS) tdSql.checkData(0, 1, 72000) # each insert one row into NO.0 NO.2 NO.7 blocks sql = "insert into t1 values (%d, 0) (%d, 2) (%d, 7)"%(self.ts+1, self.ts + 2*3300*1000+1, self.ts + 7*3300*1000+1) tdSql.execute(sql) # query result sql = "select * from t1 limit 10 offset 72000" tdSql.waitedQuery(sql, 10, WAITS) tdSql.checkData(0, 1, 72000 - 3) # have where sql = "select * from t1 where ts>='2017-07-14 10:40:10' and ts<'2017-07-22 18:40:10' limit 10 offset 72000" tdSql.waitedQuery(sql, 10, WAITS) tdSql.checkData(0, 1, 72000 - 3 + 10 + 1) # have where desc sql = "select * from t1 where ts<'2017-07-14 20:40:00' order by ts desc limit 15 offset 36000" tdSql.waitedQuery(sql, 3, WAITS) tdSql.checkData(0, 1, 1) # # add case with filename # tdCases.addWindows(__file__, TDTestCase()) tdCases.addLinux(__file__, TDTestCase())