Skip to content

T
TDengine

Rattenking / TDengine
与 Fork 源项目一致

Fork自 taosdata / TDengine

通知 1
Star 0
Fork 0
T
TDengine

体验新版 GitCode，发现更多精彩内容 >>

切换分支/标签
查找文件 普通视图历史永久链接Permalink
nestquery_last_row.py 12.7 KB
Newer Older
H
[TD-4735]<test>:test nestquery last row function  
happyguoxy 已提交
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 
###################################################################
#           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 tdLog
from util.cases import tdCases
from util.sql import tdSql
import random


class TDTestCase:
    def init(self, conn, logSql):
        tdLog.debug("start to execute %s" % __file__)
        tdSql.init(conn.cursor(), logSql)

        self.ts = 1600000000000
        self.num = 10

    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')
Y
[TD-5055]<fix> handle incorret resul  
yihaoDeng 已提交
163 
        # incorrect result, not support nest > 2
H
[TD-4735]<test>:test nestquery last row function  
happyguoxy 已提交
164 165 166 167 
        sql = '''select last_row(*) from 
                ((select * from table_0) union all
                 (select * from table_1) union all
                 (select * from table_2));'''
Y
[TD-5055]<fix> handle incorret resul  
yihaoDeng 已提交
168 169 170 171 172 173 174 175 176 177 178 179 
        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')
H
[TD-4735]<test>:test nestquery last row function  
happyguoxy 已提交
180 181 182 183 184 185 186 187 188 189 190 191 192 

        # 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));'''
Y
[TD-5055]<fix> handle incorret resul  
yihaoDeng 已提交
193 194 195 196 197 198 199 200 201 202 203 204 
        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')
H
[TD-4735]<test>:test nestquery last row function  
happyguoxy 已提交
205 206 207 208 209 210 211 212 213 214 215 216 217 

        # 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));'''
Y
[TD-5055]<fix> handle incorret resul  
yihaoDeng 已提交
218 219 220 221 222 223 224 225 226 227 228 229 
        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')
H
[TD-4735]<test>:test nestquery last row function  
happyguoxy 已提交
230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 


        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")  

        #bug 5057
        # sql = '''select last_row(*)  from 
        #         (select * from (select * from stable_1))'''
        # tdLog.info(sql)
        # tdSql.error(sql)
        # try:
        #     tdSql.execute(sql)
        #     tdLog.exit(" core dumped")
        # except Exception as e:
        #     tdLog.info(repr(e))
        #     tdLog.info("core dumped") 



    def stop(self):
        tdSql.close()
        tdLog.success("%s successfully executed" % __file__)


tdCases.addWindows(__file__, TDTestCase())
Y
[TD-5055]<fix> handle incorret resul  
yihaoDeng 已提交
264 
tdCases.addLinux(__file__, TDTestCase())