Elgato_dark/TDengine
1
0
Fork 0
mirror of https://github.com/taosdata/TDengine synced 2026-05-24 10:09:01 +00:00
Code Issues Projects Releases Packages Wiki Activity Actions 4
TDengine/tests/pytest/query/udf.py
Shuduo Sang d69c5977db
Test/sangshuduo/td 13408 move tests in (#10589) 
* restore .gitmodules

* Revert "[TD-13408]<test>: move tests directory out"

This reverts commit 7db7bd9337.

* revert to make tests back

* immigrate file change in stand-alone repo to TDengine

* remove tests repository checkout

Co-authored-by: tangfangzhi <fztang@taosdata.com>
2022-03-07 17:59:24 +08:00

614 lines
No EOL
30 KiB
Python
Raw Permalink Blame History

###################################################################
# 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
import os
from util.log import *
from util.cases import *
from util.sql import *
from util.dnodes import *
class TDTestCase:
def init(self, conn, logSql):
tdLog.debug("start to execute %s" % __file__)
tdSql.init(conn.cursor())
self.ts = 1627750800000
self.numberOfRecords = 10000
def pre_stable(self):
os.system("gcc -g -O0 -fPIC -shared ../script/sh/abs_max.c -o /tmp/abs_max.so")
os.system("gcc -g -O0 -fPIC -shared ../script/sh/add_one.c -o /tmp/add_one.so")
os.system("gcc -g -O0 -fPIC -shared ../script/sh/sum_double.c -o /tmp/sum_double.so")
tdSql.execute("create table stb(ts timestamp ,c1 int, c2 bigint) tags(t1 int)")
for i in range(50):
for j in range(200):
sql = "insert into t%d using stb tags(%d) values(%s,%d,%d)" % (i, i, self.ts + j, 1e2+j, 1e10+j)
tdSql.execute(sql)
for i in range(50):
for j in range(200):
sql = "insert into t%d using stb tags(%d) values(%s,%d,%d)" % (i, i, self.ts + j + 200 , -1e2-j, -j-1e10)
tdSql.execute(sql)
def test_udf_null(self):
tdLog.info("test missing parameters")
tdSql.error("create aggregate function as '/tmp/abs_maxw.so' outputtype bigint;")
tdSql.error("create aggregate function abs_max as '' outputtype bigint;")
tdSql.error("create aggregate function abs_max as outputtype bigint;")
tdSql.error("create aggregate function abs_max as '/tmp/abs_maxw.so' ;")
tdSql.error("create aggregate abs_max as '/tmp/abs_maxw.so' outputtype bigint;")
tdSql.execute("create aggregate function abs_max as '/tmp/abs_max.so' outputtype bigint;")
tdSql.error("select abs_max() from stb")
tdSql.error("select abs_max(c2) from ")
tdSql.execute("drop function abs_max")
def test_udf_format(self):
# tdSql.error("create aggregate function avg as '/tmp/abs_max.so' outputtype bigint;")
tdSql.error("create aggregate function .a as '/tmp/abs_max.so' outputtype bigint;")
tdSql.error("create aggregate function .11 as '/tmp/abs_max.so' outputtype bigint;")
tdSql.error("create aggregate function 1a as '/tmp/abs_max.so' outputtype bigint;")
tdSql.error("create aggregate function \"1+1\" as '/tmp/abs_max.so' outputtype bigint;")
# tdSql.error("create aggregate function [avg] as '/tmp/abs_max.so' outputtype bigint;")
tdSql.execute("create aggregate function abs_max as '/tmp/abs_max.so' outputtype bigint;")
# tdSql.error("create aggregate function abs_max2 as '/tmp/abs_max.so' outputtype bigint;")
tdSql.execute("drop function abs_max;")
tdSql.error("create aggregate function abs_max as '/tmp/add_onew.so' outputtype bigint;")
def test_udf_test(self):
tdSql.execute("create aggregate function abs_max as '/tmp/abs_max.so' outputtype bigint;")
tdSql.error("create aggregate function abs_max as '/tmp/add_onew.so' outputtype bigint;")
sql = 'select abs_max() from db.stb'
tdSql.error(sql)
sql = 'select abs_max(c2) from db.stb'
tdSql.query(sql)
tdSql.checkData(0,0,10000000199)
def test_udf_values(self):
tdSql.execute("drop function abs_max")
tdSql.execute("create function add_one as '/tmp/add_one.so' outputtype int")
tdSql.execute("create aggregate function abs_max as '/tmp/abs_max.so' outputtype bigint;")
tdSql.execute("create aggregate function sum_double as '/tmp/sum_double.so' outputtype bigint;")
# tdSql.error("create aggregate function max as '/tmp/abs_max.so' outputtype bigint ;")
# tdSql.error("create aggregate function avg as '/tmp/abs_max.so' outputtype bigint ;")
# tdSql.error("create aggregate function dbs as '/tmp/abs_max.so' outputtype bigint ;")
tdSql.execute("drop database if exists test")
tdSql.execute("create database test")
tdSql.execute("use test")
tdSql.execute("create stable st (ts timestamp,id int , val double , number bigint, chars binary(200)) tags (ind int)")
tdSql.execute("create table tb1 using st tags(1)")
tdSql.execute("create table tb2 using st tags(2)")
start_time = 1604298064000
rows = 5
tb_nums = 2
for i in range(1, tb_nums + 1):
for j in range(rows):
start_time += 10
tdSql.execute(
"insert into tb%d values(%d, %d,%f,%d,%s) " % (i, start_time, j, float(j),j*100, "'str" + str(j) + "'"))
tdSql.query("select count(*) from st")
tdSql.execute("create table bound using st tags(3)")
epoch_time=1604298064000
intdata1 = -2**31+2
intdata2 = 2**31-2
bigintdata1 = -2**63+2
bigintdata2 = 2**63-2
print("insert into bound values(%d, %d , %f, %d , %s)"%(epoch_time,intdata1,float(intdata1),bigintdata1,"'binary"+str(intdata1)+"'"))
tdSql.execute("insert into bound values(%d, %d , %f, %d , %s)"%(epoch_time,intdata1,float(intdata1),bigintdata1,"'binary"+str(intdata1)+"'"))
tdSql.execute("insert into bound values(%d, %d , %f, %d , %s)"%(epoch_time+10,intdata1+1,float(intdata1+1),bigintdata1+1,"'binary"+str(intdata1+1)+"'"))
tdSql.execute("insert into bound values(%d, %d , %f, %d , %s)"%(epoch_time+100,intdata2,float(intdata2),bigintdata2,"'binary"+str(intdata2)+"'"))
tdSql.execute("insert into bound values(%d, %d , %f, %d , %s)"%(epoch_time+1000,intdata2+1,float(intdata2+1),bigintdata2+1,"'binary"+str(intdata2+1)+"'"))
# check super table calculation results
tdSql.query("select add_one(id) test from st")
tdSql.checkData(0,0,1)
tdSql.checkData(1,0,2)
tdSql.checkData(4,0,5)
tdSql.checkData(5,0,1)
tdSql.checkData(9,0,5)
tdSql.checkData(10,0,-2147483645)
tdSql.checkData(13,0,None)
# check common table calculation results
tdSql.query("select add_one(id) from tb1")
tdSql.checkData(0,0,1)
tdSql.checkData(1,0,2)
tdSql.checkData(4,0,5)
tdSql.error("select add_one(col) from st")
sqls= ["select add_one(chars) from st",
"select add_one(val) from st",
"select add_one(ts) from st"]
for sql in sqls:
res = tdSql.getResult(sql)
if res == []:
tdLog.info("====== this col not support use UDF , because datatype not match defind in UDF function ======")
else:
tdLog.info(" ====== unexpected error occured about UDF function =====")
sys.exit()
tdLog.info("======= UDF function abs_max check ======")
sqls= ["select abs_max(val) from st",
"select abs_max(id) from st",
"select abs_max(ts) from st"]
for sql in sqls:
res = tdSql.getResult(sql)
if res == []:
tdLog.info("====== this col not support use UDF , because datatype not match defind in UDF function ======")
else:
tdLog.info(" ====== unexpected error occured about UDF function =====")
sys.exit()
tdSql.query("select abs_max(val) from st")
tdSql.query("select abs_max(val) from tb1")
tdSql.checkRows(0)
tdSql.query("select sum_double(val) from st")
tdSql.query("select sum_double(val) from tb1")
tdSql.checkRows(0)
# check super table calculation results
tdSql.query("select abs_max(number) from st")
tdSql.checkData(0,0,9223372036854775807)
# check common table calculation results
tdSql.query("select abs_max(number) from tb1")
tdSql.checkData(0,0,400)
tdSql.query("select abs_max(number) from tb2")
tdSql.checkData(0,0,400)
tdSql.execute("select add_one(id) from st limit 10 offset 2")
tdSql.query("select add_one(id) from st where ts > 1604298064000 and ts < 1604298064020 ")
tdSql.checkData(0,0,1)
tdSql.checkData(1,0,-2147483644)
tdSql.query("select add_one(id) from tb1 where ts > 1604298064000 and ts < 1604298064020 ")
tdSql.checkData(0,0,1)
tdSql.query("select sum_double(id) from st where ts > 1604298064030 and ts < 1604298064060 ")
tdSql.checkData(0,0,14)
tdSql.query("select sum_double(id) from tb2 where ts > 1604298064030 and ts < 1604298064060 ")
tdSql.checkRows(0)
tdSql.query("select add_one(id) from st where ts = 1604298064000 ")
tdSql.checkData(0,0,-2147483645)
tdSql.query("select add_one(id) from st where ts > 1604298064000 and id in (2,3) and ind =1;")
tdSql.checkData(0,0,3)
tdSql.checkData(1,0,4)
tdSql.query("select id , add_one(id) from tb1 where ts > 1604298064000 and id in (2,3)")
tdSql.checkData(0,0,2)
tdSql.checkData(0,1,3)
tdSql.checkData(1,0,3)
tdSql.checkData(1,1,4)
tdSql.query("select sum_double(id) from tb1 where ts > 1604298064000 and id in (2,3)")
tdSql.checkData(0,0,10)
tdSql.query("select sum_double(id) from st where ts > 1604298064000 and id in (2,3) and ind =1")
tdSql.checkData(0,0,10)
tdSql.query("select abs_max(number) from st where ts > 1604298064000 and id in (2,3) and ind =1")
tdSql.checkData(0,0,300)
tdSql.query("select sum_double(id) from st where ts = 1604298064030 ")
tdSql.checkData(0,0,4)
tdSql.query("select abs_max(number) from st where ts = 1604298064100 ")
tdSql.checkData(0,0,9223372036854775806)
tdSql.query("select abs_max(number) from tb2 where ts = 1604298064100 ")
tdSql.checkData(0,0,400)
tdSql.query("select sum_double(id) from tb2 where ts = 1604298064100 ")
tdSql.checkData(0,0,8)
tdSql.query("select add_one(id) from st where ts >= 1604298064000 and ts <= 1604298064010")
tdSql.checkData(0,0,1)
tdSql.checkData(1,0,-2147483645)
tdSql.checkData(2,0,-2147483644)
tdSql.query("select add_one(id) from tb1 where ts >= 1604298064000 and ts <= 1604298064010")
tdSql.checkData(0,0,1)
tdSql.query("select sum_double(id) from st where ts >= 1604298064030 and ts <= 1604298064050")
tdSql.checkData(0,0,18)
tdSql.query("select sum_double(id) from tb2 where ts >= 1604298064030 and ts <= 1604298064100")
tdSql.checkData(0,0,20)
tdSql.query("select abs_max(number) from tb2 where ts >= 1604298064030 and ts <= 1604298064100")
tdSql.checkData(0,0,400)
tdSql.query("select abs_max(number) from st where ts >= 1604298064030 and ts <= 1604298064100")
tdSql.checkData(0,0,9223372036854775806)
tdSql.query("select id from st where id != 0 and ts >=1604298064070")
tdSql.checkData(0,0,1)
tdSql.query("select add_one(id) from st where id != 0 and ts >=1604298064070")
tdSql.checkData(0,0,2)
tdSql.query("select add_one(id) from st where id <> 0 and ts >=1604298064010")
tdSql.checkData(0,0,2)
tdSql.query("select sum_double(id) from st where id in (2,3,4) and ts >=1604298064070")
tdSql.checkData(0,0,18)
tdSql.query("select sum_double(id) from tb2 where id in (2,3,4) and ts >=1604298064070")
tdSql.checkData(0,0,18)
tdSql.query("select abs_max(number) from st where id in (2,3,4) and ts >=1604298064070")
tdSql.checkData(0,0,400)
tdSql.query("select add_one(id) from st where id = 0 ")
tdSql.checkData(0,0,1)
tdSql.checkData(1,0,1)
tdSql.query("select add_one(id) from tb2 where id = 0 ")
tdSql.checkData(0,0,1)
tdSql.query("select sum_double(id) from st where id = 1")
tdSql.checkData(0,0,4)
tdSql.query("select sum_double(id) from tb2 where id = 1")
tdSql.checkData(0,0,2)
tdSql.query("select add_one(id) from st where id is not null and ts >=1604298065000 ")
tdSql.checkData(0,0,None)
tdSql.query("select abs_max(number) from st where id is not null and ts >=1604298065000 ")
tdSql.checkData(0,0,9223372036854775807)
tdSql.query("select abs_max(number) from bound where id is not null and ts >=1604298065000 ")
tdSql.checkData(0,0,9223372036854775807)
tdSql.query("select sum_double(id) from st where id is not null and ts >=1604298064000 and ind = 1 ")
tdSql.checkData(0,0,20)
tdSql.query("select sum_double(id) from tb1 where id is not null and ts >=1604298064000 ")
tdSql.checkData(0,0,20)
tdSql.query("select add_one(id) from st where id is null and ts >=1604298065000 ")
tdSql.checkRows(0)
tdSql.query("select abs_max(number) from st where id is null and ts >=1604298065000 ")
tdSql.checkRows(0)
tdSql.query("select abs_max(number) from tb1 where id is null and ts >=1604298065000 ")
tdSql.checkRows(0)
tdSql.query("select add_one(id) from bound where id is not null and ts >=1604298065000;")
tdSql.checkData(0,0,None)
tdSql.query("select id,add_one(id) from bound;")
tdSql.checkRowCol(4,2)
tdSql.checkData(3,1,None)
tdSql.query("select add_one(id) from st where ts between 1604298064000 and 1604298064010")
tdSql.checkRows(3)
tdSql.query("select add_one(id) from tb1 where ts between 1604298064000 and 1604298064010")
tdSql.checkRows(1)
tdSql.query("select sum_double(id) from st where ts between 1604298064000 and 1604298064010 and id>=0")
tdSql.checkData(0,0,0)
tdSql.query("select sum_double(id) from tb1 where ts between 1604298064000 and 1604298064010 and id>=0")
tdSql.checkData(0,0,0)
tdSql.query("select add_one(id) from st where id in (1,2)")
tdSql.checkData(0,0,2)
tdSql.checkData(1,0,3)
tdSql.checkData(2,0,2)
tdSql.checkData(3,0,3)
tdSql.checkRows(4)
tdSql.query("select sum_double(id) from st where ts < now and ind =1 interval(1s)")
tdSql.checkData(0,1,20)
tdSql.error("select sum_double(id) from st where ts < now and ind =1 interval(3s) sliding (1s) fill (NULL) ")
tdSql.error("select sum_double(id) from st session(ts, 1s)")
tdSql.query("select sum_double(id) from tb1 session(ts, 1s)")
tdSql.checkData(0,1,20)
# intervals sliding values calculation
tdSql.query("select sum_double(id) from st where ts < now and ind =1 interval(3s) sliding (1s) limit 2")
tdSql.checkData(0,1,20)
tdSql.checkData(1,1,20)
# scalar_function can't work when using interval and sliding =========
tdSql.error("select add_one(id) from st where ts < now and ind =1 interval(3s) sliding (1s) limit 2 ")
tdSql.error("select add_one(id) from st order by ts")
tdSql.error("select ts,id,add_one(id) from st order by ts asc;")
# # UDF not support order by
tdSql.error("select ts,id,add_one(id) from st order by ts desc;")
# UDF function union all
tdSql.query("select add_one(id) from tb1 union all select add_one(id) from tb2;")
tdSql.checkRows(10)
tdSql.checkData(0,0,1)
tdSql.checkData(5,0,1)
tdSql.query("select sum_double(id) from tb1 union all select sum_double(id) from tb2;")
tdSql.checkRows(2)
tdSql.checkData(0,0,20)
tdSql.checkData(1,0,20)
tdSql.query("select abs_max(number) from tb1 union all select abs_max(number) from bound;")
tdSql.checkRows(2)
tdSql.checkData(0,0,400)
tdSql.checkData(1,0,9223372036854775807)
tdSql.execute("create stable stb (ts timestamp,id int , val double , number bigint, chars binary(200)) tags (ind int)")
tdSql.execute("create table stb1 using stb tags(3)")
tdSql.execute("insert into stb1 values(1604298064000 , 1 , 1.0 , 10000 ,'chars')")
tdSql.query("select add_one(id) from st union all select add_one(id) from stb;")
tdSql.checkRows(15)
tdSql.checkData(13,0,None)
tdSql.checkData(14,0,2)
tdSql.query("select add_one(id) from st union all select add_one(id) from stb1;")
tdSql.checkRows(15)
tdSql.checkData(13,0,None)
tdSql.checkData(14,0,2)
tdSql.query("select id ,add_one(id) from tb1 union all select id ,add_one(id) from stb1;")
tdSql.checkRows(6)
tdSql.checkData(0,0,0)
tdSql.checkData(0,1,1)
tdSql.checkData(1,0,1)
tdSql.checkData(1,1,2)
# aggregate union all for different stables
tdSql.query("select sum_double(id) from st union all select sum_double(id) from stb;")
tdSql.checkRows(2)
tdSql.checkData(0,0,44)
tdSql.checkData(1,0,2)
tdSql.query("select id from st union all select id from stb1;")
tdSql.checkRows(15)
tdSql.query("select id from tb1 union all select id from stb1")
tdSql.checkRows(6)
tdSql.query("select sum_double(id) from tb1 union all select sum_double(id) from stb")
tdSql.checkData(0,0,20)
tdSql.checkData(1,0,2)
tdSql.query("select sum_double(id) from st union all select sum_double(id) from stb1;")
tdSql.checkRows(2)
tdSql.checkData(0,0,44)
tdSql.checkData(1,0,2)
tdSql.query("select abs_max(number) from st union all select abs_max(number) from stb;")
tdSql.checkData(0,0,9223372036854775807)
tdSql.query("select abs_max(number) from bound union all select abs_max(number) from stb1;")
tdSql.checkData(0,0,9223372036854775807)
tdSql.checkData(1,0,10000)
tdSql.query("select abs_max(number) from st union all select abs_max(number) from stb1;")
tdSql.checkData(0,0,9223372036854775807)
tdSql.checkData(1,0,10000)
# group by for aggegate function ;
tdSql.query("select sum_double(id) from st group by tbname;")
tdSql.checkData(0,0,20)
tdSql.checkData(0,1,'tb1')
tdSql.checkData(1,0,20)
tdSql.checkData(1,1,'tb2')
tdSql.query("select sum_double(id) from st group by id;")
tdSql.checkRows(9)
tdSql.query("select sum_double(id) from st group by ts")
tdSql.checkRows(12)
tdSql.query("select sum_double(id) from st group by ind")
tdSql.checkRows(3)
tdSql.query("select sum_double(id) from st group by tbname order by ts asc;")
tdSql.query("select abs_max(number) from st group by id")
tdSql.checkRows(9)
tdSql.checkData(0,0,9223372036854775806)
tdSql.checkData(8,0,9223372036854775807)
tdSql.query("select abs_max(number) from st group by ts")
tdSql.checkRows(12)
tdSql.checkData(11,0,9223372036854775807)
tdSql.checkData(1,0,9223372036854775805)
tdSql.query("select abs_max(number) from st group by ind")
tdSql.checkRows(3)
tdSql.checkData(0,0,400)
tdSql.checkData(2,0,9223372036854775807)
# UDF join
tdSql.query("select add_one(tb1.id),add_one(bound.id) from tb1,bound where tb1.ts=bound.ts;")
tdSql.checkData(0,0,1)
tdSql.checkData(0,1,-2147483644)
tdSql.query("select stb1.ts,add_one(stb1.id),bound.ts,add_one(bound.id) from stb1,bound where stb1.ts=bound.ts")
tdSql.checkData(0,1,2)
tdSql.checkData(0,3,-2147483645)
tdSql.query("select st.ts,add_one(st.id),stb.ts,add_one(stb.id) from st,stb where st.ts=stb.ts and st.ind=stb.ind")
tdSql.checkData(0,1,-2147483645)
tdSql.checkData(0,3,2)
tdSql.query("select sum_double(tb1.id),sum_double(bound.id) from tb1,bound where tb1.ts=bound.ts;")
tdSql.checkData(0,0,0)
tdSql.checkData(0,1,-4294967290)
tdSql.query("select sum_double(stb1.id),sum_double(bound.id) from stb1,bound where stb1.ts=bound.ts")
tdSql.checkData(0,0,2)
tdSql.checkData(0,1,-4294967292)
#UDF join for stables
tdSql.query("select sum_double(st.id),sum_double(stb.id) from st,stb where st.ts=stb.ts and st.ind=stb.ind")
tdSql.checkData(0,0,-4294967292)
tdSql.checkData(0,1,2)
tdSql.query("select abs_max(tb1.number),abs_max(bound.number) from tb1,bound where tb1.ts=bound.ts;")
tdSql.checkData(0,0,0)
tdSql.checkData(0,1,9223372036854775805)
tdSql.query("select abs_max(stb1.number),abs_max(bound.number) from stb1,bound where stb1.ts=bound.ts")
tdSql.checkData(0,0,10000)
tdSql.checkData(0,1,9223372036854775806)
tdSql.query("select abs_max(st.number),abs_max(stb.number) from st,stb where st.ts=stb.ts and st.ind=stb.ind")
tdSql.checkData(0,0,9223372036854775806)
tdSql.checkData(0,1,10000)
# check boundary
tdSql.query("select abs_max(number) from bound")
tdSql.checkData(0,0,9223372036854775807)
tdLog.info("======= UDF function sum_double check =======")
tdSql.query("select sum_double(id) from st")
tdSql.checkData(0,0,44)
tdSql.query("select sum_double(id) from tb1")
tdSql.checkData(0,0,20)
# only one udf function in SQL can use ,follow errors notice.
tdSql.error("select sum_double(id) , abs_max(number) from tb1")
tdSql.error("select sum_double(id) , abs_max(number) from st")
# UDF not support mix up with build-in functions
# it seems like not support scalar_function mix up with aggregate functions
tdSql.error("select sum_double(id) ,add_one(id) from st")
tdSql.error("select sum_double(id) ,add_one(id) from tb1")
tdSql.error("select sum_double(id) ,max(id) from st")
tdSql.error("select sum_double(id) ,max(id) from tb1")
tdSql.error("select twa(id),add_one(id) from st")
tdSql.error("select twa(id),add_one(id) from tb1")
# UDF function not support Arithmetic ===================
tdSql.query("select max(id) + 5 from st")
tdSql.query("select max(id) + 5 from tb1")
tdSql.query("select max(id) + avg(val) from st")
tdSql.query("select abs_max(number)*5 from st")
tdSql.checkData(0,0,46116860184273879040.000000000)
tdSql.query("select abs_max(number)*5 from tb1")
tdSql.checkData(0,0,2000.000000000)
tdSql.query("select max(id) + avg(val) from tb1")
tdSql.query("select add_one(id) + 5 from st")
tdSql.checkData(4,0,10.000000000)
tdSql.query("select add_one(id)/5 from tb1")
tdSql.checkData(4,0,1.000000000)
tdSql.query("select sum_double(id)-5 from st")
tdSql.checkData(0,0,39.000000000)
tdSql.query("select sum_double(id)*5 from tb1")
tdSql.checkData(0,0,100.000000000)
tdSql.query("select abs_max(number) + 5 from tb1")
tdSql.error("select abs_max(number) + max(id) from st")
tdSql.query("select abs_max(number)*abs_max(val) from st")
tdSql.query("select sum_double(id) + sum_double(id) from st")
tdSql.checkData(0,0,88.000000000)
tdLog.info("======= UDF Nested query test =======")
tdSql.query("select sum(id) from (select id from st)")
tdSql.checkData(0,0,22)
#UDF bug -> Nested query
# outer nest query
tdSql.query("select abs_max(number) from (select number from st)")
tdSql.checkData(0,0,9223372036854775807)
tdSql.query("select abs_max(number) from (select number from bound)")
tdSql.checkData(0,0,9223372036854775807)
tdSql.query("select sum_double(id) from (select id from st)")
tdSql.checkData(0,0,44)
tdSql.query("select sum_double(id) from (select id from bound)")
tdSql.checkData(0,0,4)
tdSql.query("select add_one(id) from (select id from st);")
tdSql.checkRows(14)
tdSql.checkData(1,0,2)
tdSql.query("select add_one(id) from (select id from bound);")
tdSql.checkRows(4)
tdSql.checkData(1,0,-2147483644)
# inner nest query
tdSql.query("select id from (select add_one(id) id from st)")
tdSql.checkRows(14)
tdSql.checkData(13,0,None)
tdSql.query("select id from (select add_one(id) id from bound)")
tdSql.checkRows(4)
tdSql.checkData(3,0,None)
tdSql.query("select id from (select sum_double(id) id from bound)")
tdSql.checkData(0,0,4)
tdSql.query("select id from (select sum_double(id) id from st)") # it will crash taos shell
tdSql.checkData(0,0,44)
tdSql.query("select id from (select abs_max(number) id from st)") # it will crash taos shell
tdSql.checkData(0,0,9223372036854775807)
tdSql.query("select id from (select abs_max(number) id from bound)")
tdSql.checkData(0,0,9223372036854775807)
# inner and outer nest query
tdSql.query("select add_one(id) from (select add_one(id) id from st)")
tdSql.checkRows(14)
tdSql.checkData(0,0,2)
tdSql.checkData(1,0,3)
tdSql.query("select add_one(id) from (select add_one(id) id from tb1)")
tdSql.checkRows(5)
tdSql.checkData(0,0,2)
tdSql.checkData(1,0,3)
tdSql.query("select sum_double(sumdb) from (select sum_double(id) sumdb from st)")
tdSql.query("select sum_double(sumdb) from (select sum_double(id) sumdb from tb1)")
tdSql.query("select abs_max(number) from (select abs_max(number) number from st)")
tdSql.checkData(0,0,9223372036854775807)
tdSql.query("select abs_max(number) from (select abs_max(number) number from bound)")
tdSql.checkData(0,0,9223372036854775807)
# nest inner and outer with build-in func
tdSql.query("select max(number) from (select abs_max(number) number from st)")
tdSql.checkData(0,0,9223372036854775807)
tdSql.query("select max(number) from (select abs_max(number) number from bound)")
tdSql.checkData(0,0,9223372036854775807)
tdSql.query("select sum_double(sumdb) from (select sum_double(id) sumdb from st)")
tdSql.query("select sum(sumdb) from (select sum_double(id) sumdb from tb1)")
tdSql.checkData(0,0,20)
tdLog.info(" =====================test illegal creation method =====================")
# tdSql.execute("drop function add_one")
tdSql.execute("drop function abs_max")
tdSql.execute("drop function sum_double")
tdSql.execute("create aggregate function error_use1 as '/tmp/abs_max.so' outputtype bigint ")
tdSql.error("select error_use1(number) from st")
# illega UDF create aggregate functions as an scalar_function
# with no aggregate
tdSql.execute("create function abs_max as '/tmp/abs_max.so' outputtype bigint bufsize 128")
tdSql.error("select abs_max(number) from st")
tdSql.execute("create function sum_double as '/tmp/sum_double.so' outputtype bigint bufsize 128")
tdSql.error("select sum_double(id) from st")
# UDF -> improve : aggregate function with no bufsize : it seems with no affect
tdSql.execute("drop function abs_max")
tdSql.execute("drop function sum_double")
tdSql.execute("create aggregate function abs_max as '/tmp/abs_max.so' outputtype bigint ")
tdSql.execute("create aggregate function sum_double as '/tmp/sum_double.so' outputtype int ")
tdSql.query("select sum_double(id) from st")
tdSql.checkData(0,0,44)
tdSql.query("select sum_double(id) from tb1")
tdSql.checkData(0,0,20)
tdSql.query("select abs_max(number) from st")
tdSql.checkData(0,0,9223372036854775807)
tdSql.query("select abs_max(number) from tb1")
tdSql.checkData(0,0,400)
tdSql.query("select abs_max(number) from tb1") # it seems work well
tdSql.checkData(0,0,400)
# UDF scalar function not support group by
tdSql.error("select add_one(id) from st group by tbname")
# UDF : give aggregate for scalar_function add_one ,it can't work well
tdSql.execute("drop function add_one")
tdSql.execute("create aggregate function add_one as '/tmp/add_one.so' outputtype bigint bufsize 128")
tdSql.error("select add_one(id) from st")
# udf must give col list
tdSql.error("select add_one(*) from st ")
tdSql.error("select add_one(*) from tb1 ")
# one udf function can multi use
tdSql.query("select abs_max(id),abs_max(number) from st ")
tdSql.query("select abs_max(number),abs_max(number)*3 from st ")
tdSql.query("select abs_max(number),abs_max(number)*3 from tb1 ")
tdSql.query("select sum_double(id),sum_double(id) from st ")
def run(self):
tdSql.prepare()
tdLog.info("==============step1 prepare udf build=============")
self.pre_stable()
tdLog.info("==============step2 prepare udf null =============")
self.test_udf_null()
tdLog.info("==============step3 prepare udf format ===========")
self.test_udf_format()
tdLog.info("==============step4 test udf functions============")
self.test_udf_test()
tdLog.info("==============step4 test udf values ============")
self.test_udf_values()
def stop(self):
tdSql.close()
tdLog.success("%s successfully executed" % __file__)
tdCases.addWindows(__file__, TDTestCase())
tdCases.addLinux(__file__, TDTestCase())
Reference in a new issue View git blame Copy permalink