#!/bin/env pyth

# -*- coding: utf-8 -*-

# Licensed to the Apache Software Foundation (ASF) under one

# or more contributor license agreements. See the NOTICE file

# distributed with this work for additional information

# regarding copyright ownership. The ASF licenses this file

# to you under the Apache License, Version 2.0 (the

# "License"); you may not use this file except in compliance

# with the License. You may obtain a copy of the License at

#

# http://www.apache.org/licenses/LICENSE-2.0

#

# Unless required by applicable law or agreed to in writing,

# software distributed under the License is distributed on an

# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY

# KIND, either express or implied. See the License for the

# specific language governing permissions and limitations

# under the License.

############################################################################

#

# @file test_sys_bitmap_function.py

# @date 2020-02-20

#

#############################################################################

"""

测试bitmap data type

"""

import os

import sys

import time

import random

sys.path.append("../../")

from lib import palo_config

from lib import palo_client

from lib import util

from lib import palo_job

from lib import common

import palo_logger

import palo_exception

client = None

#日志 异常 对象

LOG = palo_logger.Logger.getLogger()

L = palo_logger.StructedLogMessage

PaloClientException = palo_exception.PaloException

config = palo_config.config

compare = 'test_query_qa.test'

file_dir = os.path.abspath(os.path.join(os.path.dirname(__file__)))

broker_info = palo_config.broker_info

def setup_module():

"""

setUp

"""

global client

client = palo_client.get_client(config.fe_host, config.fe_query_port)

def wait_end(database_name):

"""

wait to finished

"""

ret = True

print('waitint for load...')

state = None

while ret:

job_list = client.get_load_job_list(database_name=database_name)

state = palo_job.LoadJob(job_list[-1]).get_state()

# print(state)

if state == "FINISHED" or state == "CANCELLED":

print(state)

ret = False

time.sleep(1)

assert state == "FINISHED"

def execute(line):

"""execte sql"""

print(line)

palo_result = client.execute(line)

print(palo_result)

return palo_result

def init(db_name, table_name):

"""

create db, table, bulk load, batch load

Args:

db_name:

table_name:

create_sql:

key_column:

Returns:

"""

line = 'DROP DATABASE IF EXISTS %s' % db_name

client.execute(line)

client.create_database(db_name)

client.use(db_name)

# client.execute('drop table if exists %s' % table_name)

line = 'CREATE TABLE %s (\

`id` int COMMENT "", \

`id1` tinyint COMMENT "", \

`c_float` float SUM COMMENT "", \

`bitmap_set1` bitmap bitmap_union COMMENT "", \

`bitmap_set2` bitmap bitmap_union COMMENT "", \

`bitmap_set3` bitmap bitmap_union COMMENT "" \

) ENGINE=OLAP \

DISTRIBUTED BY HASH(`id`, `id1`) BUCKETS 5 \

PROPERTIES ( \

"storage_type" = "COLUMN" \

);' % table_name

execute(line)

line = 'insert into %s select k4, k1, k9, bitmap_hash(k9), bitmap_hash(k9),' \

' bitmap_hash(k3) from %s' % (table_name, compare)

execute(line)

# wait_end(db_name)

def init_for_intersect(db_name, table_name):

"""

create db, table, bulk load, batch load

Args:

db_name:

table_name:

create_sql:

key_column:

Returns:

"""

line = 'DROP DATABASE IF EXISTS %s' % db_name

client.execute(line)

client.create_database(db_name)

client.use(db_name)

# client.execute('drop table if exists %s' % table_name)

line = """

CREATE TABLE `pv_bitmap` (

`dt` int(11) NULL COMMENT "",

`page` varchar(10) NULL COMMENT "",

`user_id_bitmap` bitmap BITMAP_UNION NULL COMMENT ""

) ENGINE=OLAP

AGGREGATE KEY(`dt`, `page`)

COMMENT "OLAP"

DISTRIBUTED BY HASH(`dt`) BUCKETS 2;

"""

execute(line)

line = """

CREATE TABLE `pv_base` (

`dt` int(11) NULL COMMENT "",

`page` varchar(10) NULL COMMENT "",

`user_id_str` varchar(40) NULL COMMENT ""

) ENGINE=OLAP

DUPLICATE KEY(`dt`, `page`)

COMMENT "OLAP"

DISTRIBUTED BY HASH(`dt`) BUCKETS 2;

"""

execute(line)

file_path = palo_config.gen_remote_file_path('sys/bitmap_load/bitmap_test.data')

set_list = ['user_id_bitmap = to_bitmap(id)']

column_name_list = ['dt', 'page', 'id']

data_desc_list = palo_client.LoadDataInfo(file_path,

'pv_bitmap', column_name_list=column_name_list, set_list=set_list)

assert client.batch_load(util.get_label(), data_desc_list, is_wait=True, broker=broker_info, max_filter_ratio=0.7)

column_name_list = ['dt', 'page', 'user_id_str']

data_desc_list = palo_client.LoadDataInfo(file_path,

'pv_base', column_name_list=column_name_list)

assert client.batch_load(util.get_label(), data_desc_list, is_wait=True, broker=broker_info, max_filter_ratio=0.7)

def test_bitmap_and():

"""

{

"title": "test_sys_bitmap_function.test_bitmap_and",

"describe": "test_bitmap_and",

"tag": "function,p1"

}

"""

"""

test_bitmap_and

计算两个输入bitmap的交集，返回新的bitmap.

:return:

"""

line = "select bitmap_to_string(bitmap_and(to_bitmap(NULL), to_bitmap(2))) cnt"

r = execute(line)

assert r[0][0] == '', "expect:NULL, actual:%s" % r[0][0]

line = "select bitmap_to_string(bitmap_and(to_bitmap(NULL), bitmap_hash(2))) cnt"

r = execute(line)

assert r[0][0] == '', "expect:NULL, actual:%s" % r[0][0]

line = "select bitmap_count(bitmap_and(to_bitmap(NULL), bitmap_hash(2))) cnt"

r = execute(line)

assert r[0][0] == 0, "expect:0, actual:%s" % r[0][0]

line = "select bitmap_count(bitmap_and(to_bitmap(NULL), bitmap_hash(NULL))) cnt"

r = execute(line)

assert r[0][0] == 0, "expect:0, actual:%s" % r[0][0]

db_name, table_name, invalied_name_1 = util.gen_name_list()

init(db_name, table_name)

client.use(db_name)

line1 = 'select bitmap_union_count(bitmap_and(bitmap_set1,bitmap_set2)) from {0}'.format(table_name)

ret1 = execute(line1)

line2 = 'select count(distinct(bitmap_to_string(bitmap_set1))) from {0}' \

' where bitmap_to_string(bitmap_set1)=bitmap_to_string(bitmap_set2)'.format(table_name)

ret2 = execute(line2)

assert ret1 == ret2

line1 = 'select bitmap_union_count(bitmap_and(bitmap_set1,bitmap_set3)) from {0}'.format(table_name)

ret1 = execute(line1)

line2 = 'select count(distinct(bitmap_to_string(bitmap_set1))) from {0}' \

' where bitmap_to_string(bitmap_set1)=bitmap_to_string(bitmap_set3)'.format(table_name)

ret2 = execute(line2)

assert ret1 == ret2

client.clean(db_name)

def test_bitmap_empty():

"""

{

"title": "test_sys_bitmap_function.test_bitmap_empty",

"describe": "test_bitmap_empty",

"tag": "function,p1"

}

"""

"""

test_bitmap_empty

返回一个空bitmap

:return:

"""

line = "select bitmap_count(bitmap_empty());"

r = execute(line)

assert r[0][0] == 0

line = "select bitmap_to_string(bitmap_empty());"

r = execute(line)

assert r[0][0] == ''

# todo 增加load的case

def test_bitmap_or():

"""

{

"title": "test_sys_bitmap_function.test_bitmap_or",

"describe": "test_bitmap_or",

"tag": "function,p1"

}

"""

"""

test_bitmap_or

计算两个输入bitmap的并集，返回新的bitmap.

:return:

"""

# line = "select bitmap_to_string(bitmap_or(to_bitmap(NULL), to_bitmap(2))) cnt"

# r = execute(line)

# assert r[0][0] == '2'

# line = "select bitmap_to_string(bitmap_or(to_bitmap(NULL), bitmap_hash(2))) cnt"

# r = execute(line)

# assert r[0][0] == '2'

# todo 目前会出core

# line = "select bitmap_count(bitmap_and(to_bitmap(NULL), bitmap_hash(2))) cnt"

# r = execute(line)

# assert r[0][0] == 0

# line = "select bitmap_count(bitmap_or(to_bitmap(NULL), bitmap_hash(NULL))) cnt"

# r = execute(line)

# assert r[0][0] == 0

db_name, table_name, invalied_name_1 = util.gen_name_list()

init(db_name, table_name)

client.use(db_name)

line1 = 'select bitmap_union_count(bitmap_or(bitmap_set1,bitmap_set2)) from {0}'.format(table_name)

ret1 = execute(line1)

line2 = """with tmp as (

select bitmap_to_string(bitmap_set1) as a from {0}

union select bitmap_to_string(bitmap_set2) as a from {0})

select count(distinct(a)) from tmp limit 1""".format(table_name)

ret2 = execute(line2)

assert ret1 == ret2

line1 = 'select bitmap_union_count(bitmap_or(bitmap_set1,bitmap_set3)) from {0}'.format(table_name)

ret1 = execute(line1)

line2 = """with tmp as (

select bitmap_to_string(bitmap_set1) as a from {0}

union select bitmap_to_string(bitmap_set3) as a from {0})

select count(distinct(a)) from tmp limit 1""".format(table_name)

ret2 = execute(line2)

assert ret1 == ret2

client.clean(db_name)

def test_bitmap_count_udaf():

"""

{

"title": "test_sys_bitmap_function.test_bitmap_count_udaf",

"describe": "test_bitmap_count_udaf",

"tag": "function,p1"

}

"""

"""test_bitmap_count_udaf"""

db_name, table_name, invalied_name_1 = util.gen_name_list()

init_for_intersect(db_name, table_name)

user_id_str_limit = 'user_id_str>0 and user_id_str<18446744073709551615'

line = 'select dt,page,bitmap_union_count(user_id_bitmap) from pv_bitmap group by dt,page order by dt,page'

ret1 = client.execute(line)

line = 'select dt,page,bitmap_count(bitmap_union(user_id_bitmap)) from pv_bitmap group by dt,page order by dt,page'

ret2 = client.execute(line)

line = 'select dt,page,count(user_id_str) from pv_base where {0}' \

' group by dt,page order by dt,page'.format(user_id_str_limit)

ret3 = client.execute(line)

assert ret1 == ret2

assert ret1 == ret3

line = 'select dt,bitmap_union_count(user_id_bitmap) from pv_bitmap group by dt order by dt'

ret1 = client.execute(line)

line = 'select dt,bitmap_count(bitmap_union(user_id_bitmap)) from pv_bitmap group by dt order by dt'

ret2 = client.execute(line)

line = 'select dt,count(user_id_str) from pv_base where {0}' \

' group by dt order by dt'.format(user_id_str_limit)

ret3 = client.execute(line)

assert ret1 == ret2

assert ret1 == ret3

line = 'select dt,page,bitmap_union_count(user_id_bitmap) from pv_bitmap group by dt,page order by dt,page'

ret1 = client.execute(line)

line = 'select dt,page,bitmap_count(bitmap_union(user_id_bitmap)) from pv_bitmap' \

' group by dt,page order by dt,page'

ret2 = client.execute(line)

line = 'select dt,page,count(user_id_str) from pv_base where {0}' \

' group by dt,page order by dt,page'.format(user_id_str_limit)

ret3 = client.execute(line)

assert ret1 == ret2

assert ret1 == ret3

client.clean(db_name)

def test_bitmap_count_udaf2():

"""

{

"title": "test_sys_bitmap_function.test_bitmap_count_udaf2",

"describe": "test_bitmap_count_udaf2",

"tag": "function,p1"

}

"""

"""test_bitmap_count_udaf2"""

db_name, table_name, invalied_name_1 = util.gen_name_list()

line = 'DROP DATABASE IF EXISTS %s' % db_name

client.execute(line)

client.create_database(db_name)

client.use(db_name)

line = 'CREATE TABLE test_bitmap_tb ( id int COMMENT "", id1 tinyint COMMENT "", c_float float SUM COMMENT "",' \

' bitmap_set bitmap BITMAP_UNION COMMENT "" ) ENGINE=OLAP' \

' DISTRIBUTED BY HASH(id, id1) BUCKETS 5 PROPERTIES ( "storage_type" = "COLUMN" );'

client.execute(line)

line = 'insert into test_bitmap_tb select k3, k1, k9, TO_BITMAP(k3) from test_query_qa.baseall;'

client.execute(line)

line = 'select id,id1,c_float,bitmap_count(bitmap_set) from test_bitmap_tb where id=2147483647;'

ret2 = client.execute(line)

for i in range(len(ret2)):

assert ret2[i][3] == 1

line = 'select id,id1,c_float,bitmap_count(bitmap_set) from test_bitmap_tb where id=-2147483647;'

ret3 = client.execute(line)

# "to_bitmap(-2147483647) should be 0"

for i in range(len(ret2)):

assert ret3[i][3] == 0, 'expect: 0, actural: %s' % ret3[i][3]

client.clean(db_name)

def test_intersect_count_udaf():

"""

{

"title": "test_sys_bitmap_function.test_intersect_count_udaf",

"describe": "test_intersect_count_udaf",

"tag": "function,p1"

}

"""

"""test_intersect_count_udaf"""

db_name, table_name, invalied_name_1 = util.gen_name_list()

init_for_intersect(db_name, table_name)

user_id_str_limit = 'user_id_str>0 and user_id_str<18446744073709551615'

date_list = ['20191001', '20191002', '20191003', '20191004', '20191005']

d1, d2, d3 = random.sample(date_list, 3)

line = """

select intersect_count(user_id_bitmap, dt, '{0}') as first_day,

intersect_count(user_id_bitmap, dt, '{1}') as second_day,

intersect_count(user_id_bitmap, dt, '{2}') as third_day

from pv_bitmap

where dt in ('{0}', '{1}', '{2}');

""".format(d1, d2, d3)

ret1 = client.execute(line)

line = """

with t1 as (select count(distinct(user_id_str)) from pv_base where dt='{0}' and {3}),

t2 as (select count(distinct(user_id_str)) from pv_base where dt='{1}' and {3}),

t3 as (select count(distinct(user_id_str)) from pv_base where dt='{2}' and {3})

select * from t1, t2, t3;

""".format(d1, d2, d3, user_id_str_limit)

ret2 = client.execute(line)

assert ret1 == ret2

line = """

select intersect_count(user_id_bitmap, dt, '{0}', '{1}', '{2}') as retention

from pv_bitmap

where dt in ('{0}', '{1}', '{2}');

""".format(d1, d2, d3)

ret1 = client.execute(line)

line = """

with t1 as (select distinct(user_id_str) from pv_base where dt='{0}' and {3}),

t2 as (select distinct(user_id_str) from pv_base where dt='{1}' and {3}),

t3 as (select distinct(user_id_str) from pv_base where dt='{2}' and {3})

select count(t1.user_id_str)

from t1 join t2 join t3

on t1.user_id_str = t2.user_id_str and t1.user_id_str = t3.user_id_str;

""".format(d1, d2, d3, user_id_str_limit)

ret2 = client.execute(line)

assert ret1 == ret2

client.clean(db_name)

def test_bitmap_with_rollup():

"""

{

"title": "test_sys_bitmap_function.test_bitmap_with_rollup",

"describe": "test_bitmap_with_rollup",

"tag": "function,p1"

}

"""

"""test_bitmap_with_rollup"""

db_name, table_name, invalied_name_1 = util.gen_name_list()

init_for_intersect(db_name, table_name)

tb = 'pv_bitmap'

rollup_table_name = tb + '_index'

column_name_list = ['dt', 'user_id_bitmap']

client.create_rollup_table(tb, rollup_table_name, column_name_list, is_wait=True)

time.sleep(5)

line = 'select dt, bitmap_union_count(user_id_bitmap) from %s group by dt' % (tb,)

rolllup = common.get_explain_rollup(client, line)

assert rollup_table_name in rolllup, 'expect %s, actural %s' % (rollup_table_name, rolllup)

client.clean(db_name)

def test_bitmap_contains():

"""

{

"title": "test_sys_bitmap_function.test_bitmap_contains",

"describe": "判断一个bitmap中是否包含指定的数值",

"tag": "function,p1"

}

"""

line = "select bitmap_contains(to_bitmap(4), 1), bitmap_contains(to_bitmap(1), 1), \

bitmap_contains(to_bitmap(0), 0), bitmap_contains(to_bitmap(0), 1)"

ret = execute(line)

assert ret == ((0, 1, 1, 0), )

line = "select bitmap_contains(bitmap_from_string('4, 5, 6, 7'), 4), \

bitmap_contains(bitmap_from_string('4, 5, 6, 7'), 8), \

bitmap_contains(bitmap_from_string('4, 4, 4, 4'), 4), \

bitmap_contains(bitmap_from_string('423, 5'), 4), \

bitmap_contains(bitmap_from_string('4, 00'), 0)"

ret = execute(line)

assert ret == ((1, 0, 1, 0, 1), )

line = " select bitmap_contains(to_bitmap(9), 964291337)"

assert execute(line) == ((0, ), )

def test_bitmap_from_string():

"""

{

"title": "test_sys_bitmap_function.test_bitmap_from_string",

"describe": "判断一个bitmap中是否包含指定的数值",

"tag": "function,p1"

}

"""

line = "select bitmap_to_string(bitmap_from_string('4, 5, 6')), bitmap_to_string(bitmap_from_string('0'))"

ret = execute(line)

assert ret == (('4,5,6', '0'), )

line = "select bitmap_to_string(bitmap_from_string('abc')), bitmap_to_string(bitmap_from_string(NULL))"

ret = execute(line)

assert ret == ((None, None), )

def test_bitmap_has_any():

"""

{

"title": "test_sys_bitmap_function.test_bitmap_has_any",

"describe": "判断两个bitmap是否有交集",

"tag": "function,p1"

}

"""

line = "select bitmap_has_any(bitmap_from_string('1,2,3,4'), bitmap_from_string('4,5,6'))"

ret = execute(line)

assert ret == ((1, ), )

line = "select bitmap_has_any(bitmap_from_string('1,2,3'), bitmap_from_string('4,5,6'))"

ret = execute(line)

assert ret == ((0, ), )

line = "select bitmap_has_any(bitmap_from_string('1,2,3'), bitmap_from_string('1,2,3'))"

ret = execute(line)

assert ret == ((1, ), )

line = "select bitmap_has_any(bitmap_from_string('1,2,3'), bitmap_from_string('1,2,3,4,5,6'))"

ret = execute(line)

assert ret == ((1, ), )

line = "select bitmap_has_any(bitmap_from_string('1'), bitmap_from_string('1,1,1'))"

ret = execute(line)

assert ret == ((1, ), )

def test_bitmap_not():

"""

{

"title": "test_sys_bitmap_function.test_bitmap_not",

"describe": "第一个bitmap减去第二个bitmap",

"tag": "function,p1"

}

"""

line = "select bitmap_to_string(bitmap_not(bitmap_from_string('1,2,3,4'), bitmap_from_string('4')))"

ret = execute(line)

assert ret == (('1,2,3', ), )

line = "select bitmap_to_string(bitmap_not(bitmap_from_string('1,2,3,4'), bitmap_from_string('4,5,6')))"

ret = execute(line)

assert ret == (('1,2,3', ), )

line = "select bitmap_to_string(bitmap_not(bitmap_from_string('1,2,3'), bitmap_from_string('4,5,6')))"

ret = execute(line)

assert ret == (('1,2,3', ), )

line = "select bitmap_to_string(bitmap_not(bitmap_from_string('1,2,3'), bitmap_from_string('1,2,3')))"

ret = execute(line)

assert ret == (('', ), )

line = "select bitmap_to_string(bitmap_not(bitmap_from_string('1,2,3'), bitmap_from_string('1,2,3,4,5,6')))"

ret = execute(line)

assert ret == (('', ), )

line = "select bitmap_to_string(bitmap_not(bitmap_from_string('1'), bitmap_from_string('1,1,1')))"

ret = execute(line)

assert ret == (('', ), )

db_name, table_name, invalied_name_1 = util.gen_name_list()

init(db_name, table_name)

client.use(db_name)

line1 = 'select bitmap_union_count(bitmap_not(bitmap_set1,bitmap_set2)) from %s' % table_name

ret1 = execute(line1)

line2 = 'select count(distinct(bitmap_to_string(bitmap_set1))) - bitmap_union_count(bitmap_and(bitmap_set1, \

bitmap_set2)) from %s' % table_name

ret2 = execute(line2)

assert ret1 == ret2, "bitmap_not function failed"

client.clean(db_name)

def test_bitmap_xor():

"""

{

"title": "test_sys_bitmap_function.test_bitmap_xor",

"describe": "返回两个bitmap异或的结果",

"tag": "function,p1"

}

"""

line = "select bitmap_to_string(bitmap_xor(bitmap_from_string('1,2,3,4'), bitmap_from_string('4,5,6')))"

ret = execute(line)

assert ret == (('1,2,3,5,6', ), )

line = "select bitmap_to_string(bitmap_xor(bitmap_from_string('1,2,3'), bitmap_from_string('4,5,6')))"

ret = execute(line)

assert ret == (('1,2,3,4,5,6', ), )

line = "select bitmap_to_string(bitmap_xor(bitmap_from_string('1,2,3'), bitmap_from_string('1,2,3')))"

ret = execute(line)

assert ret == (('', ), )

line = "select bitmap_to_string(bitmap_xor(bitmap_from_string('1,2,3'), bitmap_from_string('1,2,3,4,5,6')))"

ret = execute(line)

assert ret == (('4,5,6', ), )

line = "select bitmap_to_string(bitmap_xor(bitmap_from_string('1'), bitmap_from_string('1,1,1')))"

ret = execute(line)

assert ret == (('', ), )

def test_bitmap_min():

"""

{

"title": "test_sys_bitmap_function.test_bitmap_min",

"describe": "计算并返回bitmap中的最小值",

"tag": "function,p1"

}

"""

line = "select bitmap_min(bitmap_from_string(''))"

ret = execute(line)

assert ret == ((None, ), )

line = "select bitmap_min(bitmap_from_string('10,99,100,1000,1,9999'))"

ret = execute(line)

assert ret == ((1, ), )

line = "select bitmap_min(bitmap_from_string('9,9,9,9,10,9'))"

ret = execute(line)

assert ret == ((9, ), )

def test_bitmap_union_int():

"""

{

"title": "test_sys_bitmap_function.test_bitmap_union_int",

"describe": "计算整型列的去重值",

"tag": "function,p1"

}

"""

db_name, table_name, invalied_name_1 = util.gen_name_list()

init_for_intersect(db_name, table_name)

user_id_str_limit = 'user_id_str>0 and user_id_str<18446744073709551615'

line = 'select bitmap_union_int(cast(user_id_str as int)) from pv_base where %s group by dt,page order by dt,page' \

% user_id_str_limit

ret1 = client.execute(line)

line = 'select count(distinct cast(user_id_str as int)) from pv_base where %s group by dt,page order by dt,page' \

% user_id_str_limit

ret2 = client.execute(line)

assert ret1 == ret2

client.clean(db_name)

def test_bitmap_union():

"""

{

"title": "test_sys_bitmap_function.test_bitmap_union",

"describe": "返回一组bitmap的并集",

"tag": "function,p1"

}

"""

db_name, table_name, invalied_name_1 = util.gen_name_list()

init_for_intersect(db_name, table_name)

user_id_str_limit = 'user_id_str>0 and user_id_str<18446744073709551615'

line = 'select bitmap_count(bitmap_union(user_id_bitmap)) from pv_bitmap group by dt,page order by dt,page'

ret1 = client.execute(line)

line = 'select count(distinct user_id_str) from pv_base where %s group by dt,page order by dt,page' \

% user_id_str_limit

ret2 = client.execute(line)

assert ret1 == ret2

client.clean(db_name)

def test_bitmap_intersect():

"""

{

"title": "test_sys_bitmap_function.test_bitmap_intersect",

"describe": "返回一组bitmap的交集",

"tag": "function,p1"

}

"""

db_name, table_name, invalied_name_1 = util.gen_name_list()

init_for_intersect(db_name, table_name)

user_id_str_limit = 'user_id_str>0 and user_id_str<18446744073709551615'

line = 'select count(*) from (select user_id_str from pv_base where dt="20191003" and page="baidu" \

union all select user_id_str from pv_base where dt="20191001" and page="baidu") a where %s' \

% user_id_str_limit

ret1 = client.execute(line)

line = 'select bitmap_count(bitmap_intersect(x)) from (select bitmap_union(user_id_bitmap) x from pv_bitmap \

where dt in ("20191003", "20191001") and page="baidu") a'

ret2 = client.execute(line)

assert ret1 == ret2

client.clean(db_name)

def test_bitmap_or_count():

"""

{

"title": "test_sys_bitmap_function:test_bitmap_or_count",

"describe": "计算两个输入bitmap的并集:返回并集的个数",

"tag": "function,p1"

}

"""

line = "select bitmap_or_count(bitmap_from_string('1,2,3,4'), bitmap_from_string('4,5,6'))"

ret = execute(line)

assert ret == ((6, ), )

line = "select bitmap_or_count(bitmap_from_string('1,2,3'), bitmap_from_string('4,5,6'))"

ret = execute(line)

assert ret == ((6, ), )

line = "select bitmap_or_count(bitmap_from_string('1,2,3'), bitmap_from_string('1,2,3'))"

ret = execute(line)

assert ret == ((3, ), )

line = "select bitmap_or_count(bitmap_from_string(''), bitmap_from_string('1,2,3,4,5,6'))"

ret = execute(line)

assert ret == ((6, ), )

line = "select bitmap_or_count(bitmap_from_string('1,1'), bitmap_from_string('1,1,1,1,1,1,1'))"

ret = execute(line)

assert ret == ((1, ), )

line = "select bitmap_or_count(bitmap_from_string('101,110,100,1110'), bitmap_from_string('1,0,110,101,11111'))"

ret = execute(line)

assert ret == ((7, ), )

def test_bitmap_and_count():

"""

{

"title": "test_sys_bitmap_function:test_bitmap_and_count",

"describe": "计算两个输入bitmap的交集，返回交集的个数",

"tag": "function,p1"

}

"""

line = "select bitmap_and_count(bitmap_from_string('1,2,3,4'), bitmap_from_string('4,5,6'))"

ret = execute(line)

assert ret == ((1, ), )

line = "select bitmap_and_count(bitmap_from_string('1,2,3'), bitmap_from_string('4,5,6'))"

ret = execute(line)

assert ret == ((0, ), )

line = "select bitmap_and_count(bitmap_from_string('1,2,3'), bitmap_from_string('1,2,3'))"

ret = execute(line)

assert ret == ((3, ), )

line = "select bitmap_and_count(bitmap_from_string(''), bitmap_from_string('1,2,3,4,5,6'))"

ret = execute(line)

assert ret == ((0, ), )

line = "select bitmap_and_count(bitmap_from_string('1,1'), bitmap_from_string('1,1,1,1,1,1,1'))"

ret = execute(line)

assert ret == ((1, ), )

line = "select bitmap_and_count(bitmap_from_string('101,110,100,1110'), bitmap_from_string('1,0,110,101,11111'))"

ret = execute(line)

assert ret == ((2, ), )

def test_bitmap_xor_count():

"""

{

"title": "test_sys_bitmap_function:test_bitmap_xor_count",

"describe": "将两个bitmap进行异或操作，返回交集的个数",

"tag": "function,p1"

}

"""

line = "select bitmap_xor_count(bitmap_from_string('1,2,3,4'), bitmap_from_string('4,5,6'))"

ret = execute(line)

assert ret == ((5, ), )

line = "select bitmap_xor_count(bitmap_from_string('1,2,3'), bitmap_from_string('4,5,6'))"

ret = execute(line)

assert ret == ((6, ), )

line = "select bitmap_xor_count(bitmap_from_string('1,2,3'), bitmap_from_string('1,2,3'))"

ret = execute(line)

assert ret == ((0, ), )

line = "select bitmap_xor_count(bitmap_from_string(''), bitmap_from_string('1,2,3,4,5,6'))"

ret = execute(line)

assert ret == ((6, ), )

line = "select bitmap_xor_count(bitmap_from_string('1,1'), bitmap_from_string('1,1,1,1,1,1,1'))"

ret = execute(line)

assert ret == ((0, ), )

line = "select bitmap_xor_count(bitmap_from_string('101,110,100,1110'), bitmap_from_string('1,0,110,101,11111'))"

ret = execute(line)

assert ret == ((5, ), )

def test_bitmap_and_not():

"""

{

"title": "test_sys_bitmap_function:test_bitmap_and_not",

"describe": "将两个bitmap进行与非操作并返回计算结果",

"tag": "function,p1"

}

"""

line_1 = "select bitmap_to_string(bitmap_and_not(bitmap_from_string('1,2,3,4'), bitmap_from_string('4,5,6')))"

ret_1 = execute(line_1)

line_2 = "select bitmap_to_string(bitmap_not(bitmap_from_string('1,2,3,4'), \

bitmap_and(bitmap_from_string('1,2,3,4'), bitmap_from_string('4,5,6'))))"

ret_2 = execute(line_2)

assert ret_1 == ret_2

line_1 = "select bitmap_to_string(bitmap_and_not(bitmap_from_string('1,2,3'), bitmap_from_string('1,2,3')))"

ret_1 = execute(line_1)

line_2 = "select bitmap_to_string(bitmap_not(bitmap_from_string('1,2,3'), bitmap_and(bitmap_from_string('1,2,3'),\

bitmap_from_string('1,2,3'))))"

ret_2 = execute(line_2)

assert ret_1 == ret_2

line_1 = "select bitmap_to_string(bitmap_and_not(bitmap_from_string(''), bitmap_from_string('1,2,3,4,5,6')))"

ret_1 = execute(line_1)

line_2 = "select bitmap_to_string(bitmap_not(bitmap_from_string(''), bitmap_and(bitmap_from_string(''), \

bitmap_from_string('1,2,3,4,5,6'))))"

ret_2 = execute(line_2)

assert ret_1 == ret_2

line_1 = "select bitmap_to_string(bitmap_and_not(bitmap_from_string('1,1'), bitmap_from_string('1,1,1,1,1,1,1')))"

ret_1 = execute(line_1)

line_2 = "select bitmap_to_string(bitmap_not(bitmap_from_string('1,1'), bitmap_and(bitmap_from_string('1,1'), \

bitmap_from_string('1,1,1,1,1,1,1'))))"

ret_2 = execute(line_2)

assert ret_1 == ret_2

line_1 = "select bitmap_to_string(bitmap_and_not(bitmap_from_string('101,110,100,1110'), \

bitmap_from_string('1,0,110,101,11111')))"

ret_1 = execute(line_1)

line_2 = "select bitmap_to_string(bitmap_not(bitmap_from_string('101,110,100,1110'), \

bitmap_and(bitmap_from_string('101,110,100,1110'), bitmap_from_string('1,0,110,101,11111'))))"

ret_2 = execute(line_2)

assert ret_1 == ret_2

def test_bitmap_and_not_count():

"""

{

"title": "test_sys_bitmap_function:test_bitmap_and_not_count",

"describe": "将两个bitmap进行与非操作并返回计算返回的大小",

"tag": "function,p1"

}

"""

line = "select bitmap_and_not_count(bitmap_from_string('1,2,3,4'), bitmap_from_string('4,5,6'))"

ret = execute(line)

assert ret == ((3, ), )

line = "select bitmap_and_not_count(bitmap_from_string('1,2,3'), bitmap_from_string('4,5,6'))"

ret = execute(line)

assert ret == ((3, ), )

line = "select bitmap_and_not_count(bitmap_from_string('1,2,3'), bitmap_from_string('1,2,3'))"

ret = execute(line)

assert ret == ((0, ), )

line = "select bitmap_and_not_count(bitmap_from_string(''), bitmap_from_string('1,2,3,4,5,6'))"

ret = execute(line)

assert ret == ((0, ), )

line = "select bitmap_and_not_count(bitmap_from_string('1,1'), bitmap_from_string('1,1,1,1,1,1,1'))"

ret = execute(line)

assert ret == ((0, ), )

line = "select bitmap_and_not_count(bitmap_from_string('101,110,100,1110'), \

bitmap_from_string('1,0,110,101,11111'))"

ret = execute(line)

assert ret == ((2, ), )

def test_bitmap_has_all():

"""

{

"title": "test_sys_bitmap_function:test_bitmap_has_all",

"describe": "如果第一个bitmap包含第二个bitmap的全部元素，则返回true。如果第二个bitmap包含的元素为空，返回true",

"tag": "function,p1"

}

"""

line = "select bitmap_has_all(bitmap_from_string('1,2,3,4'), bitmap_from_string('4,5,6'))"

ret = execute(line)

assert ret == ((0, ), )

line = "select bitmap_has_all(bitmap_from_string('1,2,3'), bitmap_from_string('4,5,6'))"

ret = execute(line)

assert ret == ((0, ), )

line = "select bitmap_has_all(bitmap_from_string('1,2,3'), bitmap_from_string('1,2,3'))"

ret = execute(line)

assert ret == ((1, ), )

line = "select bitmap_has_all(bitmap_from_string('1,2,3,4,5,6'), bitmap_from_string(''))"

ret = execute(line)

assert ret == ((1, ), )

line = "select bitmap_has_all(bitmap_from_string('1,2,3,4,5,6'), bitmap_from_string('6,5,4,3,2'))"

ret = execute(line)

assert ret == ((1, ), )

line = "select bitmap_has_all(bitmap_from_string('1,1'), bitmap_from_string('1,1,1,1,1,1,1'))"

ret = execute(line)

assert ret == ((1, ), )

line = "select bitmap_has_all(bitmap_from_string(''), bitmap_from_string(''))"

ret = execute(line)

assert ret == ((1, ), )

def test_bitmap_max():

"""

{

"title": "test_sys_bitmap_function:test_bitmap_max",

"describe": "计算并返回bitmap中的最大值",

"tag": "function,p1"

}

"""

line = "select bitmap_max(bitmap_from_string(''))"

ret = execute(line)

assert ret == ((None, ), )

line = "select bitmap_max(bitmap_from_string('10,99,100,1000,1,9999'))"

ret = execute(line)

assert ret == ((9999, ), )

line = "select bitmap_max(bitmap_from_string('9,9,9,9,8,9'))"

ret = execute(line)

assert ret == ((9, ), )

line = "select bitmap_max(bitmap_from_string('3,3,3,3,3,3,3,3,3'))"

ret = execute(line)

assert ret == ((3, ), )

def test_bitmap_subset_in_range():

"""

{

"title": "test_sys_bitmap_function:test_bitmap_subset_in_range",

"describe": "返回bitmap指定范围内的子集",

"tag": "function,p1"

}

"""

line = "select bitmap_to_string(bitmap_subset_in_range(bitmap_from_string(''), 1, 10))"

ret = execute(line)

assert ret == (('', ), )

line = "select bitmap_to_string(bitmap_subset_in_range(bitmap_from_string('1,2,3,4,5'), 1, 5))"

ret = execute(line)

assert ret == (('1,2,3,4', ), )

line = "select bitmap_to_string(bitmap_subset_in_range(bitmap_from_string('1,2,3,4,5'), 2, 6))"

ret = execute(line)

assert ret == (('2,3,4,5', ), )

line = "select bitmap_to_string(bitmap_subset_in_range(bitmap_from_string('1,2,3,4,5'), 6, 10))"

ret = execute(line)

assert ret == (('', ), )

line = "select bitmap_to_string(bitmap_subset_in_range(bitmap_from_string('1,2,3,4,5'), 6, 1))"

ret = execute(line)

assert ret == ((None, ), )

line = "select bitmap_to_string(bitmap_subset_in_range(bitmap_from_string('1,2,3,4,5'), 5, 5))"

ret = execute(line)

assert ret == ((None, ), )

line = "select bitmap_to_string(bitmap_subset_in_range(bitmap_from_string('1,2,3,4,5,5,3,2,7,8,2,5'), 2, 6))"

ret = execute(line)

assert ret == (('2,3,4,5', ), )

def test_bitmap_subset_limit():

"""

{

"title": "test_sys_bitmap_function:test_bitmap_subset_limit",

"describe": "生成子bitmap",

"tag": "function,p1"

}

"""

line = "select bitmap_to_string(bitmap_subset_limit(bitmap_from_string(''), 1, 10))"

ret = execute(line)

assert ret == (('', ), )

line = "select bitmap_to_string(bitmap_subset_limit(bitmap_from_string('1,2,3,4,5'), 1, 4))"

ret = execute(line)

assert ret == (('1,2,3,4', ), )

line = "select bitmap_to_string(bitmap_subset_limit(bitmap_from_string('1,2,3,4,5'), 2, 6))"

ret = execute(line)

assert ret == (('2,3,4,5', ), )

line = "select bitmap_to_string(bitmap_subset_limit(bitmap_from_string('1,2,3,4,5'), 6, 10))"

ret = execute(line)

assert ret == (('', ), )

line = "select bitmap_to_string(bitmap_subset_limit(bitmap_from_string('8,8,9,10,6,7'), 3, 3))"

ret = execute(line)

assert ret == (('6,7,8', ), )

line = "select bitmap_to_string(bitmap_subset_limit(bitmap_from_string('1,2,3,4,5'), 5, 5))"

ret = execute(line)

assert ret == (('5', ), )

line = "select bitmap_to_string(bitmap_subset_limit(bitmap_from_string('1,2,3,4,5,5,3,2,7,8,2,5'), 2, 6))"

ret = execute(line)

assert ret == (('2,3,4,5,7,8', ), )

def test_sub_bitmap():

"""

{

"title": "test_sys_bitmap_function:test_sub_bitmap",

"describe": "截取bitmap，返回子集",

"tag": "function,p1"

}

"""

line = "select bitmap_to_string(sub_bitmap(bitmap_from_string(''), 1, 10))"

ret = execute(line)

assert ret == ((None, ), )

line = "select bitmap_to_string(sub_bitmap(bitmap_from_string('1,2,3,4,5'), 1, 4))"

ret = execute(line)

assert ret == (('2,3,4,5', ), )

line = "select bitmap_to_string(sub_bitmap(bitmap_from_string('1,0,1,2,3,1,5'), 0, 3))"

ret = execute(line)

assert ret == (('0,1,2', ), )

line = "select bitmap_to_string(sub_bitmap(bitmap_from_string('1,0,1,2,3,1,5'), -3, 2))"

ret = execute(line)

assert ret == (('2,3', ), )

line = "select bitmap_to_string(sub_bitmap(bitmap_from_string('1,0,1,2,3,1,5'), 2, 100))"

ret = execute(line)

assert ret == (('2,3,5', ), )

line = "select bitmap_to_string(sub_bitmap(bitmap_from_string('2,1,5,4,3'), 2, 2))"

ret = execute(line)

assert ret == (('3,4', ), )

line = "select bitmap_to_string(sub_bitmap(bitmap_from_string('6,4,5,2,6,7,8,1,2,3'), 2, 6))"

ret = execute(line)

assert ret == (('3,4,5,6,7,8', ), )

if __name__ == '__main__':

setup_module()

tb = 'test_sys_bitmap_function_test_bitmap_with_rollup_db.pv_bitmap'

line = 'select dt, bitmap_union_count(user_id_bitmap) from %s group by dt' % (tb,)

rollup = common.get_explain_rollup(client, line)

print(rollup)

# test_sc_add_bitmap_column()

# test_sc_drop_bitmap_column()

# test_sc_modified_bitmap_column()