#!/bin/env python

# -*- 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_array_select.py

# @date 2022-08-15 11:09:53

# @brief This file is a test file for array type.

#

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

"""

test_array_select.py

"""

import sys

import os

import time

import pytest

file_dir = os.path.abspath(os.path.join(os.path.dirname(__file__), "../.."))

sys.path.append(file_dir)

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

sys.path.append(file_dir)

from lib import palo_config

from lib import palo_client

from lib import palo_job

from lib import palo_types

from lib import util

from lib import common

from data import schema as SCHEMA

from data import load_file as FILE

config = palo_config.config

broker_info = palo_client.BrokerInfo(config.broker_name, config.broker_property)

db = 'test_array_select_test_table_db'

table = 'test_array_select_test_table_tb'

table_2 = 'test_array_select_test_table_tb_1'

def setup_module():

"""enable array"""

client = common.get_client()

ret = client.show_variables('enable_vectorized_engine')

if len(ret) == 1 and ret[0][1] == 'false':

raise pytest.skip('skip if enable_vectorized_engine is false')

ret = client.admin_show_config('enable_array_type')

assert len(ret) == 1, 'get enable_array_type config error'

value = palo_job.AdminShowConfig(ret[0]).get_value()

if value != 'true':

client.set_frontend_config('enable_array_type', 'true')

if len(client.show_databases(db)) == 0:

print(len(client.show_databases(db)))

print(client.show_databases(db))

init(db, table, True)

def init(database_name, table_name, sort=False):

"""init test data"""

client = common.create_workspace(database_name)

ret = client.create_table(table_name, SCHEMA.array_table_list, keys_desc=SCHEMA.duplicate_key,

set_null=True)

assert ret, 'create table failed'

sql = 'insert into %s select k1, [0, 1, 1, 0] as a1, collect_list(k1) as a2, collect_list(k2) as a3, ' \

'collect_list(k3) as a4, collect_list(k4) as a5, collect_list(cast(k4 as largeint)*10) as a6, ' \

'collect_list(k5) as a7, collect_list(k9) as a8, collect_list(k8) as a9, collect_list(k10) as a10, ' \

'collect_list(k11) as a11, collect_list(k6) as a12, collect_list(k7) as a12, collect_list(k7) as a13 ' \

'from test_query_qa.test group by k1' % table_name

sql_s = 'insert into %s select k1, [0, 1, 1, 0] as a1, array_sort(collect_list(k1)) as a2, ' \

'array_sort(collect_list(k2)) as a3, array_sort(collect_list(k3)) as a4, ' \

'array_sort(collect_list(k4)) as a5, array_sort(collect_list(cast(k4 as largeint) * 10)) as a6, ' \

'array_sort(collect_list(k5)) as a7, array_sort(collect_list(k9)) as a8, ' \

'array_sort(collect_list(k8)) as a9, array_sort(collect_list(k10)) as a10, ' \

'array_sort(collect_list(k11)) as a11, array_sort(collect_list(k6)) as a12, ' \

'array_sort(collect_list(k7)) as a13, array_sort(collect_list(k7)) as a14 ' \

'from test_query_qa.test group by k1' % table_name

if sort:

insert_sql = sql_s

else:

insert_sql = sql

ret = client.execute(insert_sql)

table_name_1 = table_name + "_1"

ret = client.create_table(table_name_1, SCHEMA.array_table_list, keys_desc=SCHEMA.duplicate_key,

set_null=True)

assert ret, 'create table failed'

sql = 'insert into %s select 0, [0, 1, 1, 0], collect_list(k1), collect_list(k2), ' \

'collect_list(k3), collect_list(k4), collect_list(cast(k4 as largeint)*10), collect_list(k5), ' \

'collect_list(k9), collect_list(k8), collect_list(k10), collect_list(k11), ' \

'collect_list(k6), collect_list(k7), collect_list(k7) from ' \

'(select k1, k2, k3, k4, k5, k6, k7, k8, k9, k10, k11 from test_query_qa.baseall ' \

'union select null, null, null, null, null, null, null, null, null, null, null) tmp' % table_name_1

sql_s = 'insert into %s select 0, [0, 1, 1, 0], array_sort(collect_list(k1)), array_sort(collect_list(k2)), ' \

'array_sort(collect_list(k3)), array_sort(collect_list(k4)), ' \

'array_sort(collect_list(cast(k4 as largeint) * 10)), array_sort(collect_list(k5)), ' \

'array_sort(collect_list(k9)), array_sort(collect_list(k8)), array_sort(collect_list(k10)), ' \

'array_sort(collect_list(k11)), array_sort(collect_list(k6)), array_sort(collect_list(k7)), ' \

'array_sort(collect_list(k7)) from test_query_qa.baseall' % table_name_1

if sort:

insert_sql = sql_s

else:

insert_sql = sql

client.execute(insert_sql)

insert_sql = "insert into %s values(1, [null, 1], [1, null, 3, 4], [0, null, 32767, -32767], " \

"[4, null, 65534, 65535], [65534, null, 65535, 5, 65536, 6553600], " \

"[65534, 6, 65535, 6553600, null], [1.12, 3.45, 4.23, null], [3.1234,1.2, null], " \

"[123.00001, null, 1.1], ['2022-07-13', null, '2000-01-01'], " \

"[null, '2022-07-13 12:30:00', '2022-07-13 12:30:00'], " \

"['', 'hello char', 'tds', null], [null, 'hello varchar', ''], [null, '', 'jedsd'])" % table_name_1

ret = client.execute(insert_sql)

sql = 'insert into %s select 2, [], [], [], [], [], [], [], [], [], [], [], [], [], []' % table_name_1

ret = client.execute(sql)

sql = 'insert into %s select 3, [null], [null], [null], [null], [null], [null], [null], ' \

'[null], [null], [null], [null], [null], [null], [null]' % table_name_1

ret = client.execute(sql)

sql = 'insert into %s select 4, null, null, null, null, null, null, null, ' \

'null, null, null, null, null, null, null' % table_name_1

ret = client.execute(sql)

def test_array_avg():

"""

{

"title": "test_array_avg",

"describe": "array_avg函数测试, ARRAY_AVG(ARRAY<T> a) 求array中元素的平均值",

"tag": "function,p1"

}

"""

client = common.get_client()

# types

support_k = ['a1', 'a2', 'a3', 'a4', 'a5', 'a6', 'a7', 'a8', 'a9']

not_support_k = ['a10', 'a11', 'a12', 'a13', 'a14']

sql = 'select array_avg({0}) from {1}.{2}'

for c in support_k:

ret = client.execute(sql.format(c, db, table_2))

assert ret, 'sql: %s, error' % sql.format(c, db, table)

msg = 'No matching function with signature'

for c in not_support_k:

util.assert_return(False, msg, client.execute, sql.format(c, db, table))

sql1 = 'select k1, array_avg(a2), array_avg(a3), array_avg(a4), array_avg(a5), array_avg(a6), ' \

'array_avg(a7), array_avg(a8), array_avg(a9) from %s.%s order by k1' % (db, table)

sql2 = 'select k1, avg(k1), avg(k2), avg(k3), avg(cast(k4 as largeint)), avg(cast(k4 as largeint) * 10), ' \

'avg(k5), avg(k9), avg(k8) from test_query_qa.test group by k1 order by k1'

common.check2(client, sql1, sql2=sql2)

sql1 = 'select array_avg(a2), array_avg(a3), array_avg(a4), array_avg(a5), array_avg(a6), ' \

'array_avg(a7), array_avg(a8), array_avg(a9) from %s.%s where k1 = 0' % (db, table_2)

sql2 = 'select avg(k1), avg(k2), avg(k3), avg(k4), avg(cast(k4 as largeint) * 10), avg(k5), ' \

'avg(k9), avg(k8) from test_query_qa.baseall'

common.check2(client, sql1, sql2=sql2)

sql2 = 'select null, null, null, null, null, null, null, null'

for k in (2, 3, 4):

sql1 = 'select array_avg(a2), array_avg(a3), array_avg(a4), array_avg(a5), array_avg(a6), ' \

'array_avg(a7), array_avg(a8), array_avg(a9) from %s.%s where k1 = %s' % (db, table_2, k)

common.check2(client, sql1, sql2=sql2)

sql1 = 'select array_avg(a2), array_avg(a3), array_avg(a4), array_avg(a5), array_avg(a6), ' \

'array_avg(a7), array_avg(a8), array_avg(a9) from %s.%s where k1 = 1' % (db, table_2)

sql2 = 'select 2.6666666666666665, 0, 43691, 1350042, 1671168.75, 2.933333333, 2.1617000102996826, 62.050005'

common.check2(client, sql1, sql2=sql2)

# where

sql1 = 'select k1, array_avg(a3) from %s.%s where array_avg(a3) > 0 order by k1' % (db, table)

sql2 = 'select k1, avg(k2) from test_query_qa.test group by k1 having avg(k2) > 0 order by k1'

common.check2(client, sql1, sql2=sql2)

def test_array_max():

"""

{

"title": "test_array_max",

"describe": "array_max函数测试, T ARRAY_MAX(ARRAY<T> a) 求array中元素的最大值，字符串类型不支持该函数",

"tag": "function,p1"

}

"""

client = common.get_client()

db = 'test_array_select_test_table_db'

table = 'test_array_select_test_table_tb'

table_2 = 'test_array_select_test_table_tb_1'

# types

support_k = ['a1', 'a2', 'a3', 'a4', 'a5', 'a6', 'a7', 'a8', 'a9', 'a10', 'a11']

# not_support_k = ['a10', 'a11', 'a12', 'a13', 'a14']

not_support_k = ['a12', 'a13', 'a14']

sql = 'select array_max({0}) from {1}.{2}'

for c in support_k:

ret = client.execute(sql.format(c, db, table_2))

assert ret

msg = 'No matching function with signature'

for c in not_support_k:

util.assert_return(False, msg, client.execute, sql.format(c, db, table_2))

# query

sql1 = 'select k1, array_max(a1), array_max(a2), array_max(a3), array_max(a4), array_max(a5), ' \

'array_max(a6), array_max(a7), array_max(a8), array_max(a9), array_max(a10), array_max(a11) ' \

'from %s.%s order by k1' % (db, table)

sql2 = 'select k1, 1, max(k1), max(k2), max(k3), max(k4), max(cast(k4 as largeint) * 10), ' \

'max(k5), max(k9), max(k8), max(k10), max(k11) from test_query_qa.test group by k1 order by k1'

common.check2(client, sql1, sql2=sql2)

sql1 = 'select array_max(a1), array_max(a2), array_max(a3), array_max(a4), array_max(a5), array_max(a6), ' \

'array_max(a7), array_max(a8), array_max(a9), array_max(a10), array_max(a11) ' \

'from %s.%s where k1 = 0' % (db, table_2)

sql2 = 'select 1, max(k1), max(k2), max(k3), max(k4), max(cast(k4 as largeint) * 10), max(k5), ' \

'max(k9), max(k8), max(k10), max(k11) from test_query_qa.baseall'

common.check2(client, sql1, sql2=sql2)

sql2 = 'select null, null, null, null,null, null, null, null, null, null, null'

for k in (2, 3, 4):

sql1 = 'select array_max(a1), array_max(a2), array_max(a3), array_max(a4), array_max(a5), array_max(a6), ' \

'array_max(a7), array_max(a8), array_max(a9), array_max(a10), array_max(a11) ' \

'from %s.%s where k1 = %s' % (db, table_2, k)

common.check2(client, sql1, sql2=sql2)

sql1 = 'select array_max(a1), array_max(a2), array_max(a3), array_max(a4), array_max(a5), array_max(a6), ' \

'array_max(a7), array_max(a8), array_max(a9), array_max(a10), array_max(a11) ' \

'from %s.%s where k1 = 1' % (db, table_2)

sql2 = 'select 1, 4, 32767, 65535, 6553600, 6553600, 4.23, 3.1234, 123.00001, ' \

'cast("2022-07-13" as date), cast("2022=-07-13 12:30:00" as datetime)'

common.check2(client, sql1, sql2=sql2)

sql1 = 'select array_max([1, 2, 3, null, 0, 3, -3]), array_max([]), array_max([null, null])'

sql2 = 'select 3, null, null'

common.check2(client, sql1, sql2=sql2)

# where

sql1 = 'select k1, array_max(a3) from %s.%s where array_max(a3) > 1000000 order by k1' % (db, table)

sql2 = 'select k1, max(k2) from test_query_qa.test group by k1 having max(k2) > 1000000 order by k1'

common.check2(client, sql1, sql2=sql2)

def test_array_min():

"""

{

"title": "test_array_min",

"describe": "array_min函数测试, T ARRAY_MIN(ARRAY<T> a) 求array中元素的最小值，不支持字符串类型",

"tag": "function,p1"

}

"""

client = common.get_client()

# types

support_k = ['a1', 'a2', 'a3', 'a4', 'a5', 'a6', 'a7', 'a8', 'a9', 'a10', 'a11']

not_support_k = ['a12', 'a13', 'a14']

sql = 'select array_min({0}) from {1}.{2}'

for c in support_k:

ret = client.execute(sql.format(c, db, table))

assert ret

msg = 'No matching function with signature'

for c in not_support_k:

util.assert_return(False, msg, client.execute, sql.format(c, db, table))

# query

sql1 = 'select k1, array_min(a1), array_min(a2), array_min(a3), array_min(a4), array_min(a5), array_min(a6), ' \

'array_min(a7), array_min(a8), array_min(a9), array_min(a10), array_min(a11) ' \

'from %s.%s order by k1' % (db, table)

sql2 = 'select k1, 0, min(k1), min(k2), min(k3), min(k4), min(cast(k4 as largeint) * 10), min(k5), ' \

'min(k9), min(k8), min(k10), min(k11) from test_query_qa.test group by k1 order by k1'

common.check2(client, sql1, sql2=sql2)

sql1 = 'select array_min(a1), array_min(a2), array_min(a3), array_min(a4), array_min(a5), array_min(a6), ' \

'array_min(a7), array_min(a8), array_min(a9), array_min(a10), array_min(a11) ' \

'from %s.%s where k1 = 0' % (db, table_2)

sql2 = 'select 0, min(k1), min(k2), min(k3), min(k4), min(cast(k4 as largeint) * 10), ' \

'min(k5), min(k9), min(k8), min(k10), min(k11) from test_query_qa.baseall'

common.check2(client, sql1, sql2=sql2)

sql2 = 'select null, null, null, null,null, null, null, null, null, null, null'

for k in (2, 3, 4):

sql1 = 'select array_min(a1), array_min(a2), array_min(a3), array_min(a4), array_min(a5), array_min(a6), ' \

'array_min(a7), array_min(a8), array_min(a9), array_min(a10), array_min(a11) ' \

'from %s.%s where k1 = %s' % (db, table_2, k)

common.check2(client, sql1, sql2=sql2)

sql1 = 'select array_min(a1), array_min(a2), array_min(a3), array_min(a4), array_min(a5), array_min(a6), ' \

'array_min(a7), array_min(a8), array_min(a9), array_min(a10), array_min(a11) ' \

'from %s.%s where k1 = 1' % (db, table_2)

sql2 = 'select 1, 1, -32767, 4, 5, 6, 1.12, 1.2, 1.1, ' \

'cast("2000-01-01" as date), cast("2022-07-13 12:30:00" as datetime)'

common.check2(client, sql1, sql2=sql2)

sql1 = 'select array_min([1, 2, 3, null, 0, 3, -3]), array_min([]), array_min([null, null]), ' \

'array_min([null, 1, null]);'

sql2 = 'select -3, null, null, 1'

common.check2(client, sql1, sql2=sql2)

# where

sql1 = 'select k1, array_min(a3) from %s.%s where array_min(a3) > -1000000 order by k1' % (db, table)

sql2 = 'select k1, min(k2) from test_query_qa.test group by k1 having min(k2) > -1000000 order by k1'

common.check2(client, sql1, sql2=sql2)

def test_array_sum():

"""

{

"title": "test_array_sum",

"describe": "array_sum函数测试, ARRAY_SUM(ARRAY<T> a) 求array中所有元素的和",

"tag": "function,p1"

}

"""

client = common.get_client()

# types

support_k = ['a1', 'a2', 'a3', 'a4', 'a5', 'a6', 'a7', 'a8', 'a9']

not_support_k = ['a10', 'a11', 'a12', 'a13', 'a14']

sql = 'select array_sum({0}) from {1}.{2}'

for c in support_k:

util.assert_return(True, None, client.execute, sql.format(c, db, table))

msg = 'No matching function with signature'

for c in not_support_k:

util.assert_return(False, msg, client.execute, sql.format(c, db, table))

sql1 = 'select k1, array_sum(a1), array_sum(a2), array_sum(a3), array_sum(a4), array_sum(a5), ' \

'array_sum(a6), array_sum(a7), array_sum(a8), array_sum(a9) from %s.%s order by k1' % (db, table)

sql2 = 'select k1, 2, sum(k1), sum(k2), sum(k3), sum(k4), sum(cast(k4 as largeint) * 10), ' \

'sum(k5), sum(k9), sum(k8) from test_query_qa.test group by k1 order by k1'

common.check2(client, sql1, sql2=sql2)

sql1 = 'select array_sum(a1), array_sum(a2), array_sum(a3), array_sum(a4), array_sum(a5), ' \

'array_sum(a6), array_sum(a7), array_sum(a8), array_sum(a9) from %s.%s where k1 = 0' % (db, table_2)

sql2 = 'select 2, sum(k1), sum(k2), sum(k3), sum(k4), sum(cast(k4 as largeint) * 10), sum(k5), ' \

'sum(k9), sum(k8) from test_query_qa.baseall'

common.check2(client, sql1, sql2=sql2)

sql2 = 'select null, null, null, null,null, null, null, null, null'

for k in (2, 3, 4):

sql1 = 'select array_sum(a1), array_sum(a2), array_sum(a3), array_sum(a4), array_sum(a5), ' \

'array_sum(a6), array_sum(a7), array_sum(a8), array_sum(a9) from %s.%s where k1 = %s' \

% (db, table_2, k)

common.check2(client, sql1, sql2=sql2)

sql1 = 'select array_sum(a1), array_sum(a2), array_sum(a3), array_sum(a4), array_sum(a5), ' \

'array_sum(a6), array_sum(a7), array_sum(a8), array_sum(a9) from %s.%s where k1 = 1' % (db, table_2)

sql2 = 'select 1, 8, 0, 131073, 6750210, 6684675, 8.8, 4.3234000205993652, 124.10001'

common.check2(client, sql1, sql2=sql2)

sql1 = 'select array_sum([1, 2, 3.1, 0, null, -1]), array_sum([null, null]), array_sum([]), array_sum([-1, 1.0])'

sql2 = 'select 5.1, null, null, 0'

common.check2(client, sql1, sql2=sql2)

# where

sql1 = 'select k1, array_sum(a3) from %s.%s where array_sum(a3) > 0 order by k1' % (db, table)

sql2 = 'select k1, sum(k2) from test_query_qa.test group by k1 having sum(k2) > 0 order by k1'

common.check2(client, sql1, sql2=sql2)

def test_array_product():

"""

{

"title": "test_array_product",

"describe": "array_product函数测试, ARRAY_PRODUCT(ARRAY<T> a) 求array中所有元素的乘积值，仅支持向量化",

"tag": "function,p1"

}

"""

client = common.get_client()

ret = client.show_variables('enable_vectorized_engine')

if len(ret) == 1 and ret[0][1] != 'true':

raise pytest.skip("enable_vectorized_engine is false, skip array_product case")

# types

support_k = ['a1', 'a2', 'a3', 'a4', 'a5', 'a6', 'a7', 'a8', 'a9']

not_support_k = ['a10', 'a11', 'a12', 'a13', 'a14']

sql = 'select array_product({0}) from {1}.{2}'

for c in support_k:

util.assert_return(True, None, client.execute, sql.format(c, db, table))

msg = 'No matching function with signature'

for c in not_support_k:

util.assert_return(False, msg, client.execute, sql.format(c, db, table))

sql1 = 'select array_product(a1), array_product(a2), array_product(a3), array_product(a4), ' \

'array_product(a5), array_product(a6), array_product(a7), array_product(a8), ' \

'array_product(a9) from %s.%s where k1 = 1' % (db, table_2)

sql2 = 'select 1, 12.0, 0, 17179082760.0, 9.222949828684677e+21, 1.68877255163904e+17, ' \

'16.34472, 3.748080116434096, 135.300011'

common.check2(client, sql1, sql2=sql2)

sql1 = 'select array_product([1.12, 3.45, 4.23])'

sql2 = 'select 1.12 * 3.45 * 4.23'

common.check2(client, sql1, sql2=sql2)

sql1 = 'select k1, array_product(a1) from %s.%s order by k1' % (db, table_2)

sql2 = 'select 0, 0 union select 1, 1 union select 2, null union select 3, null ' \

'union select 3, null union select 4, null'

common.check2(client, sql1, sql2=sql2, forced=True)

sql1 = 'select array_product([0,1, -0, 1]), array_product([1, null, 3.1, -1]), ' \

'array_product([null]), array_product([])'

sql2 = 'select 0, -3.1, null, null'

common.check2(client, sql1, sql2=sql2)

# where

sql1 = 'select k1, array_product(a2) from %s.%s where array_product(a2) > 0 order by k1' % (db, table_2)

sql2 = 'select 0, 1307674368000 union select 1, 12'

common.check2(client, sql1, sql2=sql2, forced=True)

# null

sql = 'select array_product(null)'

msg = 'No matching function with signature: array_product(boolean)'

util.assert_return(False, msg, client.execute, sql)

def test_array_sort():

"""

{

"title": "test_array_sort",

"describe": "array_sort函数测试 ARRAY<T> array_sort(ARRAY<T> arr) 对array中所有元素排序并返回array",

"tag": "function,p1"

}

"""

database_name, table_name, index_name = util.gen_name_list()

init(database_name, table_name)

client = common.get_client()

table_2 = table_name + '_1'

# types

support_k = ['a1', 'a2', 'a3', 'a4', 'a5', 'a6', 'a7', 'a8', 'a9', 'a10', 'a11', 'a12', 'a13', 'a14']

sql = 'select array_sort({0}) from {1}.{2}'

for c in support_k:

util.assert_return(True, None, client.execute, sql.format(c, database_name, table_name))

sql1 = 'select k1, array_sort(a1), array_sort(a2), array_sort(a3), array_sort(a4), array_sort(a5), ' \

'array_sort(a6), array_sort(a7), array_sort(a8), array_sort(a9), array_sort(a10), ' \

'array_sort(a11), array_sort(a12), array_sort(a13), array_sort(a14) ' \

'from %s.%s order by k1' % (database_name, table_name)

sql2 = "select k1, [0, 0, 1, 1], concat('[', group_concat(CAST(`k1` AS CHARACTER) ORDER BY `k1`), ']') a2, " \

"concat('[', group_concat(CAST(`k2` AS CHARACTER) ORDER BY `k2`), ']') a3," \

"concat('[', group_concat(CAST(`k3` AS CHARACTER) ORDER BY `k3`), ']') a4," \

"concat('[', group_concat(CAST(`k4` AS CHARACTER) ORDER BY `k4`), ']') a5," \

"concat('[', group_concat(CAST(cast(k4 as largeint) * 10 AS CHARACTER) ORDER BY `k4`), ']') a6," \

"concat('[', group_concat(CAST(`k5` AS CHARACTER) ORDER BY `k5`), ']') a7," \

"concat('[', group_concat(CAST(`k9` AS CHARACTER) ORDER BY `k9`), ']') a8," \

"concat('[', group_concat(CAST(`k8` AS CHARACTER) ORDER BY `k8`), ']') a9," \

"concat('[', group_concat(CAST(`k10` AS CHARACTER) ORDER BY `k10`), ']') a10," \

"concat('[', group_concat(CAST(`k11` AS CHARACTER) ORDER BY `k11`), ']') a11," \

"concat(\"['\", group_concat(k6, \"', '\" ORDER BY `k6`), \"']\") a12," \

"concat(\"['\", group_concat(k7, \"', '\" ORDER BY `k7`), \"']\") a13," \

"concat(\"['\", group_concat(k7, \"', '\" ORDER BY `k7`), \"']\") a14 " \

"from test_query_qa.test group by k1 order by k1"

common.check2(client, sql1, sql2=sql2)

sql1 = 'select array_sort(a1) s1, array_sort(a2) s2, array_sort(a3) s3, array_sort(a4) s4, array_sort(a5) s5,' \

' array_sort(a6) s6, array_sort(a7) s7, array_sort(a8) s8, array_sort(a9) s9, array_sort(a10) s10, ' \

'array_sort(a11) s11, array_sort(a12) s12, array_sort(a13) s13, array_sort(a14) s14 ' \

'from %s.%s where k1 = 1 ' % (database_name, table_2)

sql2 = 'select [NULL, 1] s1, [NULL, 1, 3, 4] s2, [NULL, -32767, 0, 32767] s3, [NULL, 4, 65534, 65535] s4, ' \

'[NULL, 5, 65534, 65535, 65536, 6553600] s5, [NULL, 6, 65534, 65535, 6553600] s6, ' \

'[NULL, 1.12, 3.45, 4.23] s7, [NULL, 1.2, 3.1234] s8, [NULL, 1.1, 123.00001] s9, ' \

'[NULL, "2000-01-01", "2022-07-13"] s10, [NULL, "2022-07-13 12:30:00", "2022-07-13 12:30:00"] s11, ' \

'[NULL, "", "hello char", "tds"] s12, [NULL, "", "hello varchar"] s13, [NULL, "", "jedsd"] s14'

common.check_by_sql(sql1, sql2, client=client, s10=palo_types.ARRAY_DATE, s11=palo_types.ARRAY_DATETIME)

sql1 = 'select array_sort([]), array_sort([1, null, 2, null, 0, -0]), array_sort([null]), array_sort(null)'

sql2 = 'select [], [null, null, 0, 0, 1, 2], [null], null'

common.check2(client, sql1=sql1, sql2=sql2)

client.clean(database_name)

def test_size():

"""

{

"title": "test_size",

"describe": "size函数测试， BIGINT size(ARRAY<T> arr), BIGINT cardinality(ARRAY<T> arr) 求array中元素数量",

"tag": "function,p1"

}

"""

client = common.get_client()

# types

support_k = ['a1', 'a2', 'a3', 'a4', 'a5', 'a6', 'a7', 'a8', 'a9', 'a10', 'a11', 'a12', 'a13', 'a14']

sql = 'select size({0}) from {1}.{2}'

for c in support_k:

util.assert_return(True, None, client.execute, sql.format(c, db, table))

sql1 = 'select k1, size(a1), size(a2), size(a3), size(a4), size(a5), size(a6), ' \

'size(a7), size(a8), size(a9), size(a10), size(a11), size(a12), size(a13), ' \

'size(a14) from %s.%s order by k1' % (db, table)

sql2 = 'select k1, 4, count(k1), count(k2), count(k3), count(k4), count(k4), count(k5), ' \

'count(k9), count(k8), count(k10), count(k11), count(k6), ' \

'count(k7), count(k7) from test_query_qa.test group by k1 order by k1'

common.check2(client, sql1, sql2=sql2)

sql1 = 'select size(a1), size(a2), size(a3), size(a4), size(a5), size(a6), size(a7), ' \

'size(a8), size(a9), size(a10), size(a11), size(a12), size(a13), size(a14) ' \

'from %s.%s where k1 = 0' % (db, table_2)

sql2 = 'select 4, count(k1), count(k2), count(k3), count(k4), count(k4), count(k5), ' \

'count(k9), count(k8), count(k10), count(k11), count(k6), count(k7), count(k7) ' \

'from test_query_qa.baseall'

common.check2(client, sql1, sql2=sql2)

sql1 = 'select size(a1), size(a2), size(a3), size(a4), size(a5), size(a6), size(a7), ' \

'size(a8), size(a9), size(a10), size(a11), size(a12), size(a13), size(a14) ' \

'from %s.%s where k1 = 1' % (db, table_2)

sql2 = 'select 2, 4, 4, 4, 6, 5, 4, 3, 3, 3, 3, 4, 3, 3'

common.check2(client, sql1, sql2=sql2)

sql1 = 'select size(a1), size(a2), size(a3), size(a4), size(a5), size(a6), size(a7), ' \

'size(a8), size(a9), size(a10), size(a11), size(a12), size(a13), size(a14) ' \

'from %s.%s where k1 = 2' % (db, table_2)

sql2 = 'select 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0'

common.check2(client, sql1, sql2=sql2)

sql1 = 'select size(a1), size(a2), size(a3), size(a4), size(a5), size(a6), size(a7), ' \

'size(a8), size(a9), size(a10), size(a11), size(a12), size(a13), size(a14) ' \

'from %s.%s where k1 = 3' % (db, table_2)

sql2 = 'select 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1'

common.check2(client, sql1, sql2=sql2)

sql1 = 'select size(a1), size(a2), size(a3), size(a4), size(a5), size(a6), size(a7), ' \

'size(a8), size(a9), size(a10), size(a11), size(a12), size(a13), size(a14) ' \

'from %s.%s where k1 = 4' % (db, table_2)

sql2 = 'select null, null, null, null, null, null, null, null, null, null, null, null, null, null'

common.check2(client, sql1, sql2=sql2)

sql1 = 'select k1 from %s.%s where size(a2) = 0' % (db, table_2)

sql2 = 'select 2'

common.check2(client, sql1, sql2=sql2)

sql1 = 'select size([]), size(null), size([null]), size([1, 2, null, null, 1, 2])'

sql2 = 'select 0, null, 1, 6'

common.check2(client, sql1, sql2=sql2)

def test_array_distinct():

"""

{

"title": "test_array_distinct",

"describe": "array_distinct函数测试，ARRAY<T> array_distinct(ARRAY<T> arr) 去除array中的重复元素并返回array",

"tag": "function,p1"

}

"""

client = common.get_client()

# types

support_k = ['a1', 'a2', 'a3', 'a4', 'a5', 'a6', 'a7', 'a8', 'a9', 'a10', 'a11', 'a12', 'a13', 'a14']

sql = 'select array_distinct({0}) from {1}.{2}'

for c in support_k:

util.assert_return(True, None, client.execute, sql.format(c, db, table))

sql1 = 'select k1, array_distinct(a1), array_distinct(a2), array_distinct(a3), array_distinct(a4), ' \

'array_distinct(a5), array_distinct(a6), array_distinct(a7), array_distinct(a8), ' \

'array_distinct(a9), array_distinct(a10), array_distinct(a11), array_distinct(a12), ' \

'array_distinct(a13), array_distinct(a14) from %s.%s order by k1' % (db, table)

sql2 = 'select k1, [0, 1] as a1, array_sort(collect_set(k1)) as a2, array_sort(collect_set(k2)) as a3, ' \

'array_sort(collect_set(k3)) as a4, array_sort(collect_set(k4)) as a5, ' \

'array_sort(collect_set(cast(k4 as largeint) * 10)) as a6, array_sort(collect_set(k5)) as a7, ' \

'array_sort(collect_set(k9)) as a8, array_sort(collect_list(k8)) as a9, ' \

'array_sort(collect_set(k10)) as a10, array_sort(collect_set(k11)) as a11, ' \

'array_sort(collect_set(k6)) as a12, array_sort(collect_set(k7)) as a13, ' \

'array_sort(collect_set(k7)) as a14 from test_query_qa.test group by k1 order by k1'

common.check2(client, sql1, sql2=sql2)

sql1 = 'select array_distinct(a1), array_distinct(a2), array_distinct(a3), array_distinct(a4), ' \

'array_distinct(a5), array_distinct(a6), array_distinct(a7), array_distinct(a8), ' \

'array_distinct(a10), array_distinct(a11), array_distinct(a12), ' \

'array_distinct(a13), array_distinct(a14) from %s.%s where k1 = 0' % (db, table_2)

sql2 = 'select [0, 1] as a1, array_sort(collect_set(k1)) as a2, array_sort(collect_set(k2)) as a3, ' \

'array_sort(collect_set(k3)) as a4, array_sort(collect_set(k4)) as a5, ' \

'array_sort(collect_set(cast(k4 as largeint) * 10)) as a6, array_sort(collect_set(k5)) as a7, ' \

'array_sort(collect_set(k9)) as a8, array_sort(collect_set(k10)) as a10, ' \

'array_sort(collect_set(k11)) as a11, array_sort(collect_set(k6)) as a12, ' \

'array_sort(collect_set(k7)) as a13, array_sort(collect_set(k7)) as a14 from test_query_qa.baseall'

common.check2(client, sql1, sql2=sql2)

sql1 = 'select e1 from %s.%s lateral view explode(array_distinct(a10)) tmp as e1 where k1 = 0 order by e1' \

% (db, table_2)

sql2 = ' select distinct k10 from test_query_qa.baseall order by 1'

common.check2(client, sql1, sql2=sql2)

sql1 = 'select array_distinct(a1), array_distinct(a2), array_distinct(a3), array_distinct(a4), ' \

'array_distinct(a5), array_distinct(a6), array_distinct(a7), array_distinct(a8), ' \

'array_distinct(a9), array_distinct(a10), array_distinct(a11), array_distinct(a12), ' \

'array_distinct(a13), array_distinct(a14) from %s.%s where k1 = 1' % (db, table_2)

sql2 = "select [NULL, 1] a1, [1, NULL, 3, 4] a2, [0, NULL, 32767, -32767] a3, " \

"[4, NULL, 65534, 65535] a4, [65534, NULL, 65535, 5, 65536, 6553600] a5, " \

"[65534, 6, 65535, 6553600, NULL] a6, [1.12, 3.45, 4.23, NULL] a7, " \

"[3.1234, 1.2, NULL] a8, [123.00001, NULL, 1.1] a9, " \

"cast(['2022-07-13', NULL, '2000-01-01'] as array<date>) a10, " \

"cast([NULL, '2022-07-13 12:30:00'] as array<datetime>) a11, " \

"['', 'hello char', 'tds', NULL] a12, [NULL, 'hello varchar', ''] a13, [NULL, '', 'jedsd'] a14"

common.check2(client, sql1, sql2=sql2)

sql1 = "select array_distinct([]), array_distinct([null, null]), " \

"array_distinct([4, 1, null, 1, 2, 3, 4, null, null])"

sql2 = "select [], [null], [4, 1, null, 2, 3]"

common.check2(client, sql1, sql2=sql2)

sql1 = "select array_distinct(a2) from %s.%s where k1 > 0 order by k1" % (db, table_2)

sql2 = "select a2 from %s.%s where k1 > 0 order by k1" % (db, table_2)

common.check2(client, sql1, sql2=sql2)

def test_array_contains():

"""

{

"title": "test_array_contains",

"describe": "array_contains函数测试，BOOLEAN array_contains(ARRAY<T> arr, T value) 判断数字中是否包含某个元素",

"tag": "function,p1"

}

"""

client = common.get_client()

db = 'test_array_select_test_table_db'

table = 'test_array_select_test_table_tb'

table_2 = 'test_array_select_test_table_tb_1'

# types

support_k = ['a1', 'a2', 'a3', 'a4', 'a5', 'a6', 'a7', 'a8', 'a9', 'a12', 'a13', 'a14', 'a10', 'a11']

sql = 'select array_contains({0}, null) from {1}.{2}'

for c in support_k:

sql1 = sql.format(c, db, table)

sql2 = 'select 0 from test_query_qa.test group by k1'

common.check2(client, sql1, sql2=sql2)

sql1 = 'select array_contains(a1, 0), array_contains(a2, 1), array_contains(a3, 255), ' \

'array_contains(a4, -2147483647), array_contains(a5, -9223372036854775807), ' \

'array_contains(a6, 1234560), array_contains(a7, 0), array_contains(a8, 6.333), ' \

'array_contains(a9, -0), array_contains(a10, "1989-03-21"), ' \

'array_contains(a11, cast("2015-04-02 00:00:00" as datetime)), ' \

'array_contains(a12, "false"), array_contains(a13, "wangjuoo4"), ' \

'array_contains(a14, "yanvjldjlll") from %s.%s' % (db, table_2)

sql2 = 'select 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 union all ' \

'select 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 union all ' \

'select 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 union all ' \

'select 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 union all ' \

'select null, null, null, null, null, null, null, null, null, null, null, null, null, null'

common.check2(client, sql1, sql2=sql2, forced=True)

sql1 = 'select k1 from %s.%s where array_contains(a2, 10)' % (db, table)

sql2 = 'select 10'

common.check2(client, sql1, sql2=sql2)

sql1 = 'select array_contains(a10, array_max(a10)), array_contains(a8, array_min(a8)), ' \

'array_contains(a7, a7[0]) from %s.%s order by k1' % (db, table)

sql2 = 'select 1, 1, 0 from test_query_qa.test group by k1'

common.check2(client, sql1, sql2=sql2)

sql1 = "select array_contains(a, '1'), array_contains(a, null), array_contains(a, '44') " \

"from (select [1, 2, 3, null] a) tmk"

sql2 = "select 1, 1, 0"

common.check2(client, sql1, sql2=sql2)

sql1 = "select array_contains([], 1), array_contains([null], 1), array_contains(null, 1), " \

"array_contains([1, 2, 3], null)"

sql2 = "select 0, 0, null, 0"

common.check2(client, sql1, sql2=sql2)

sql1 = "select array_contains(a10, null) from %s.%s limit 1" % (db, table)

sql2 = 'select 0'

common.check2(client, sql1, sql2=sql2)

sql1 = "select array_contains(a11, null) from %s.%s" % (db, table_2)

sql2 = "select 1 union all select 1 union all select null union all select 0 union all select 0"

common.check2(client, sql1, sql2=sql2, forced=True)

def test_array_position():

"""

{

"title": "test_array_position",

"describe": "array_position函数测试",

"tag": "function,p1"

}

"""

# BIGINT array_position(ARRAY<T> arr, T value) 返回arr中第一次出现元素value的位置

# 从1开始计算

client = common.get_client()

# types

support_k = ['a1', 'a2', 'a3', 'a4', 'a5', 'a6', 'a7', 'a8', 'a9', 'a10', 'a11', 'a12', 'a13', 'a14']

sql = 'select array_position({0}, null) from {1}.{2}'

for c in support_k:

sql1 = sql.format(c, db, table)

sql2 = 'select 0 from test_query_qa.test group by k1'

common.check2(client, sql1, sql2=sql2)

sql1 = 'select array_position(a1, 0), array_position(a2, 1), array_position(a3, 255), ' \

'array_position(a4, -2147483647), array_position(a5, -9223372036854775807), ' \

'array_position(a6, 1234560), array_position(a7, 0), array_position(a8, 6.333), ' \

'array_position(a9, -0), array_position(a10, cast("1989-03-21" as date)), ' \

'array_position(a11, cast("2015-04-02 00:00:00" as datetime)), ' \

'array_position(a12, "false"), array_position(a13, "wangjuoo4"), ' \

'array_position(a14, "yanvjldjlll") from %s.%s' % (db, table_2)

sql2 = 'select 1, 1, 3, 1, 1, 5, 4, 8, 4, 3, 12, 1, 6, 13 union all ' \

'select 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 union all ' \

'select 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 union all ' \

'select 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 union all ' \

'select null, null, null, null, null, null, null, null, null, null, null, null, null, null'

common.check2(client, sql1, sql2=sql2, forced=True)

sql1 = 'select k1 from %s.%s where array_position(a2, 1) in (1, 2, 3, 4, 5)' % (db, table)

sql2 = 'select 1'

common.check2(client, sql1, sql2=sql2, forced=True)

sql1 = 'select array_position(a, 1), array_position(a, null), array_position(a, -10) ' \

'from (select [1, 1, null, -10, null, -10] a) tmp'

sql2 = 'select 1, 3, 4'

common.check2(client, sql1, sql2=sql2)

sql1 = 'select array_position([], 1), array_position([null], 1), array_position([1, 2, 3], null), ' \

'array_position([1, 2, 3], 4), array_position([null, null], null)'

sql2 = 'select 0, 0, 0, 0, 1'

common.check2(client, sql1, sql2=sql2)

sql1 = "select array_position(a13, null) from %s.%s" % (db, table_2)

sql2 = "select 1 union all select 1 union all select null union all select 0 union all select 0"

common.check2(client, sql1, sql2=sql2, forced=True)

def test_element_at():

"""

{

"title": "test_element_at",

"describe": "element_at函数测试",

"tag": "function,p1"

}

"""

# T element_at(ARRAY<T> arr, BIGINT position), 返回arr中位置为position的值，同arr[position]

# enable_vectorized_engine为true

client = common.get_client()

# types

support_k = ['a1', 'a2', 'a3', 'a4', 'a5', 'a6', 'a7', 'a8', 'a9', 'a10', 'a11', 'a12', 'a13', 'a14']

sql1 = 'select k1, element_at({0}, 1) from {1}.{2} order by k1'

sql2 = 'select k1, {0}[1] from {1}.{2} order by k1'

for c in support_k:

common.check2(client, sql1=sql1.format(c, db, table),

sql2=sql2.format(c, db, table))

sql1 = 'select element_at(a1, 0), element_at(a2, 1), element_at(a3, 2), element_at(a4, 3), ' \

'element_at(a5, 4), element_at(a6, 5), element_at(a7, 6), element_at(a8, 7), ' \

'element_at(a9, 8), element_at(a10, 9), element_at(a11, 10), element_at(a12, 11), ' \

'element_at(a13, 12), element_at(a14, 13) from %s.%s order by k1' % (db, table_2)

sql2 = 'select null, 1, -32767, 103, -11011903, 1234560, 0.666, 4.336, 0.1, ' \

'cast("2015-01-01" as date), cast("2015-03-13 12:36:38" as datetime), ' \

'"true", "yanavnd", "yanvjldjlll" union all ' \

'select null, 1, null, 65534, 5, null, null, null, null, null, null, null, null, null union all ' \

'select null, null, null, null, null, null, null, null, null, null, null, null, null, null union all ' \

'select null, null, null, null, null, null, null, null, null, null, null, null, null, null union all ' \

'select null, null, null, null, null, null, null, null, null, null, null, null, null, null'

common.check2(client, sql1, sql2=sql2, forced=True)

sql1 = 'select k1 from %s.%s where element_at(a2, 1) is not null' % (db, table_2)

sql2 = 'select 1 union select 0'

common.check2(client, sql1, sql2=sql2, forced=True)

sql1 = 'select k1 from %s.%s where a2[1] is null' % (db, table_2)

sql2 = 'select 4 union select 2 union select 3'

common.check2(client, sql1, sql2=sql2, forced=True)

sql1 = 'select element_at(a, 0), element_at(a, 1), element_at(a, 100), element_at(a, -1), ' \

'element_at(a, null) from (select [1, 2, 3] a) tmp'

sql2 = 'select null, 1, null, 3, null'

common.check2(client, sql1, sql2=sql2)

sql1 = 'select a[0], a[1], a[100], a[-1], a[null] from (select [1, 2, 3] a) tmp'

common.check2(client, sql1, sql2=sql2)

sql1 = 'select element_at([], 1), element_at([null], 1)'

sql2 = 'select null, null'

common.check2(client, sql1=sql1, sql2=sql2)

sql = 'select element_at(null, 1)'

msg = 'No matching function with signature: element_at(null_type, tinyint(4))'

util.assert_return(False, msg, client.execute, sql)

def test_array_slice():

"""

{

"title": "test_array_slice",

"describe": "array_slice函数测试",

"tag": "function,p1"

}

"""

# ARRAY<T> array_slice(ARRAY<T> arr, BIGINT off, BIGINT len)，返回从指定位置开始的执行长度的子数组

# enable_vectorized_engine

client = common.get_client()

# types

support_k = ['a1', 'a2', 'a3', 'a4', 'a5', 'a6', 'a7', 'a8', 'a9', 'a10', 'a11', 'a12', 'a13', 'a14']

sql1 = 'select array_slice({0}, 1, 2) from {1}.{2} order by k1'

sql2 = 'select {0}[1:2] from {1}.{2} order by k1'

for c in support_k:

common.check2(client, sql1=sql1.format(c, db, table), sql2=sql2.format(c, db, table))

sql1 = 'select array_slice(a1, 1, 1) b1, array_slice(a2, 2, 2) b2, ' \

'array_slice(a3, 1, 1) b3, array_slice(a4, 1, 2) b4, ' \

'array_slice(a5, 1, 1) b5, array_slice(a6, 2, 1) b6, ' \

'array_slice(a7, 2, 2) b7, array_slice(a8, 1, 1) b8, ' \

'array_slice(a9, 1, 1) b9, array_slice(a10, 2, 1) b10, ' \

'array_slice(a11, 1, 1) b11, array_slice(a12, 2, 2) b12, ' \

'array_slice(a13, 1, 1) b13, array_slice(a14, 1, 2) b14 from %s.%s order by k1' % (db, table_2)

sql2 = 'select [0] b1, [2, 3] b2, [-32767] b3, [-2147483647, -2147483647] b4, ' \

'[-9223372036854775807] b5, [-92233720368547758070] b6, [-258.369, -0.123] b7, ' \

'[-365] b8, [-123456.54] b9, ["1988-03-21"] b10, ["1901-01-01 00:00:00"] b11, ' \

'["false", "false"] b12, [""] b13, ["", " "] b14 union all ' \

'select [null], [null, 3], [0], [4, null], [65534], [6], [3.45, 4.23], [3.1234], ' \

'[123.00001], [null], [null], ["hello char", "tds"], [null], [null, ""] union all ' \

'select [], [], [], [], [], [], [], [], [], [], [], [], [], [] union all ' \

'select [null], [], [null], [null], [null], [], [], [null], [null], [], [null], ' \

'[], [null], [null] union all ' \

'select null, null, null, null, null, null, null, null, null, null, null, null, null, null'

common.check_by_sql(sql1, sql2, client=client, b10=palo_types.ARRAY_DATE, b11=palo_types.ARRAY_DATETIME)

sql1 = 'select array_slice([1, 2, 3], 1, 1), array_slice([1, 2, 3], 1, 0), array_slice([1, 2, 3], 1), ' \

'array_slice([1, 2, 3], null, null), array_slice([1, 2, 3], 1, 100), array_slice([1, 2, 3], 1, -2), ' \

'array_slice([1, 2, 3], -1, 2)'

sql2 = 'select [1], [], [1, 2, 3], null, [1, 2, 3], [], [3]'

common.check2(client, sql1, sql2=sql2)

sql1 = 'select array_slice([null], 1, 1), array_slice([1, null, 2], -2, 3), array_slice([], 1, 2), ' \

'array_slice([], -1, 1), array_slice([null], -1, 2), array_slice([null], 100)'

sql2 = ' select [null], [null, 2], [], [], [null], []'

common.check2(client, sql1, sql2=sql2)

def test_array_remove():

"""

{

"title": "test_array_remove",

"describe": "array_remove函数测试",

"tag": "function,p1"

}

"""

# ARRAY<T> array_remove(ARRAY<T> arr, T val)，返回arr中删除所有val元素的子数组

client = common.get_client()

# types

support_k = ['a1', 'a2', 'a3', 'a4', 'a5', 'a6', 'a7', 'a8', 'a9', 'a10', 'a11', 'a12', 'a13', 'a14']

sql = 'select array_remove({0}, {0}[1]) from {1}.{2}'

for c in support_k:

util.assert_return(True, None, client.execute, sql.format(c, db, table))

sql1 = 'select array_remove(a1, 0), array_remove(a2, k1), array_remove(a3, 4), ' \

'array_remove(a4, 4), array_remove(a5, 4), array_remove(a6, 4), ' \

'array_remove(a7, 4), array_remove(a8, 4), array_remove(a9, 4), ' \

'array_remove(a10, date("2022-11-11")), array_remove(a11, date("2022-11-11")), ' \

'array_remove(a12, 100), array_remove(a13, 100), array_remove(a14, 200) ' \

'from %s.%s order by k1' % (db, table)

sql2 = 'select [1, 1], [], a3, a4, a5, a6, a7, a8, a9, a10, a11, a12, a13, a14 from %s.%s order by k1' \

% (db, table)

common.check2(client, sql1, sql2=sql2)

sql1 = 'select array_remove(a1, 1), array_remove(a2, 1), array_remove(a3, 32767), ' \

'array_remove(a4, 65535), array_remove(a5, 6553600), array_remove(a6, 6553600), ' \

'array_remove(a7, 4.23), array_remove(a8, 3.1234), array_remove(a9, 123.00001), ' \

'array_remove(a10, cast("2000-01-01" as date)), array_remove(a11, "2022-07-13 12:30:00"), ' \

'array_remove(a12, "hello char"), array_remove(a13, ""), array_remove(a14, "jedsd") ' \

'from %s.%s where k1 > 0' % (db, table_2)

sql2 = 'select [null] a1, [null, 3, 4] a2, [0, NULL, -32767] a3, [4, NULL, 65534] a4, ' \

'[65534, NULL, 65535, 5, 65536] a5, [65534, 6, 65535, NULL] a6, ' \

'[1.12, 3.45, NULL] a7, [1.2, NULL] a8, [NULL, 1.1] a9, ' \

'["2022-07-13", NULL] a10, [NULL] a11, ' \

'["", "tds", NULL] a12, [NULL, "hello varchar"] a13, [NULL, ""] a14 union all ' \

'select [], [], [], [], [], [], [], [], [], [], [], [], [], [] union all ' \

'select [null], [null], [null], [null], [null], [null], [null], [null], [null], ' \

'[null], [null], [null], [null], [null] union all ' \

'select null, null, null, null, null, null, null, null, null, null, null, null, null, null'

common.check_by_sql(sql1, sql2, client=client, a10=palo_types.ARRAY_DATE)

sql1 = 'select array_remove(["", "abc", "dfef"], null), array_remove([1, 2, 3], "1"), ' \

'array_remove(["", "abc", "dfef"], "abcd")'

sql2 = 'select null, [2, 3], ["", "abc", "dfef"]'

common.check2(client, sql1, sql2=sql2)

# 类型匹配case

sql1 = "select array_remove([1, 2, 3, 4], 1.3), array_remove([1, 2, 3, 4.0], 1.3)"

sql2 = "select [2, 3, 4], [1, 2, 3, 4]"

common.check2(client, sql1, sql2=sql2)

def test_array_join():

"""

{

"title": "test_array_join",

"describe": "array_join函数测试",

"tag": "function,p1"

}

"""

# VARCHAR array_join(ARRAY<T> arr, VARCHAR sep[, VARCHAR null_replace])

client = common.get_client()

# types

support_k = ['a1', 'a2', 'a3', 'a4', 'a5', 'a6', 'a7', 'a8', 'a9', 'a10', 'a11', 'a12', 'a13', 'a14']

sql = 'select array_join({0}, "_") from {1}.{2}'

for c in support_k:

util.assert_return(True, None, client.execute, sql.format(c, db, table))

sql1 = 'select k1, array_join(a12, "_"), array_join(a13, "_"), array_join(a14, "_") ' \

'from %s.%s order by k1' % (db, table)

sql2 = 'select k1, group_concat(k6, "_" order by k6), group_concat(k7, "_" order by k7), ' \

'group_concat(k7, "_" order by k7) from test_query_qa.test group by k1 order by k1'

common.check2(client, sql1, sql2=sql2)

sql1 = 'select k1, array_join(a1, "_"), array_join(a2, "_"), array_join(a3, "_"), ' \

'array_join(a4, "_"), array_join(a5, "_"), array_join(a6, "_"), ' \

'array_join(a7, "_"), array_join(a10, "_"), array_join(a11, "_") ' \

'from %s.%s order by k1' % (db, table)

sql2 = 'select k1, "0_1_1_0", group_concat(cast(k1 as string), "_" order by k1) a2, ' \

'group_concat(cast(k2 as string), "_" order by k2) a3, ' \

'group_concat(cast(k3 as string), "_" order by k3) a4, ' \

'group_concat(cast(k4 as string), "_" order by k4) a5, ' \

'group_concat(cast(cast(k4 as largeint) * 10 as string), "_" order by k4) a6, ' \

'group_concat(cast(k5 as string), "_" order by k5) a7, ' \

'group_concat(cast(k10 as string), "_" order by k10) a10, ' \

'group_concat(cast(k11 as string), "_" order by k11) a11 ' \

'from test_query_qa.test group by k1 order by k1'

common.check2(client, sql1, sql2=sql2)

sql1 = 'select array_join(a12, "_"), array_join(a13, "_"), array_join(a14, "_") ' \

'from %s.%s where k1 = 0' % (db, table_2)

sql2 = 'select group_concat(k6, "_" order by k6), group_concat(k7, "_" order by k7), ' \

'group_concat(k7, "_" order by k7) from test_query_qa.baseall'

common.check2(client, sql1, sql2=sql2)

sql1 = 'select array_join(a12, "_", ">.<"), array_join(a13, "_"), array_join(a14, "_") ' \

'from %s.%s where k1 > 0' % (db, table_2)

sql2 = 'select "", "", "" union all select null, null, null union all ' \

'select "_hello char_tds_>.<", "hello varchar_", "_jedsd" union all ' \

'select ">.<", "", ""'

common.check2(client, sql1, sql2=sql2, forced=True)

sql1 = 'select count(*) from %s.%s where array_join(a1, ",") = "0,1,1,0"' % (db, table)

sql2 = 'select count(distinct k1) from test_query_qa.test'

common.check2(client, sql1, sql2=sql2)

sql1 = 'select array_join(a, 1, 2), array_join(a, ",", "dd"), array_join(a, null, "ppt") ' \

'from (select [1, 2, null, 3] a) tmp'

sql2 = 'select "1121213", "1,2,dd,3", null'

common.check2(client, sql1, sql2=sql2)

sql1 = "select array_join([null, null], '\"', 'd'), array_join([null], '\"', 'd'), " \

"array_join([], '\"', 'd'), array_join([null, null, 1, null, null], '\"', 'd'), " \

"array_join([null, null, 1, null, null], '-')"

sql2 = "select 'd\"d', 'd', '', 'd\"d\"1\"d\"d', '1'"

common.check2(client, sql1, sql2=sql2)

sql1 = 'select array_join(["", "1", "2"], "_")'

sql2 = 'select "_1_2"'

common.check2(client, sql1, sql2=sql2)

def test_array_except():

"""

{

"title": "test_array_except",

"describe": "array_except函数测试",

"tag": "function,p1"

}

"""

# ARRAY<T> array_except(ARRAY<T> array1, ARRAY<T> array2) 所有在array1内但不在array2内的元素，不包含重复项

# enable_vectorized_engine

client = common.get_client()

# types

support_k = ['a1', 'a2', 'a3', 'a4', 'a5', 'a6', 'a7', 'a8', 'a9', 'a10', 'a11', 'a12', 'a13', 'a14']

line1 = 'select array_except({0}, {0}) from {1}.{2} order by k1'

line2 = 'select [] from test_query_qa.test group by k1'

for c in support_k:

common.check2(client, sql1=line1.format(c, db, table), sql2=line2)

sql1 = 'select k1, array_except(a1, [1]), array_except(a2, [null]), array_except(a3, [null]), ' \

'array_except(a4, [null]), array_except(a5, [null]), array_except(a6, [null]), ' \

'array_except(a7, [null]), array_except(a8, [null]), array_except(a9, [null]), ' \

'array_except(a10, [null]), array_except(a11, [null]), array_except(a12, [null]), ' \

'array_except(a13, [null]), array_except(a14, [null]) from %s.%s where k1 = 0 order by k1' % (db, table_2)

sql2 = 'select k1, [0], array_distinct(a2), array_distinct(a3), array_distinct(a4), ' \

'array_distinct(a5), array_distinct(a6), array_distinct(a7), array_distinct(a8), ' \

'array_distinct(a9), array_distinct(a10), array_distinct(a11), array_distinct(a12), ' \

'array_distinct(a13), array_distinct(a14) from %s.%s where k1=0 order by k1' % (db, table_2)

common.check2(client, sql1=sql1, sql2=sql2)

sql1 = 'select array_except([null], [1])'

sql2 = 'select [null]'

common.check2(client, sql1=sql1, sql2=sql2)

sql1 = 'select array_except(a3, [null]) from %s.%s where k1 != 0 order by k1' % (db, table_2)

sql2 = 'select "[]" union all select "[]" union all select null union all select "[0, 32767, -32767]"'

common.check2(client, sql1=sql1, sql2=sql2, forced=True)

# array不同子类型的array_except，如array<int> vs array<date>， not support

sql = 'select array_except({0}, {1}) from {2}.{3} where k1 = 0 order by k1'

msg = 'No matching function with signature: array_except(array<smallint(6)>, array<date>).'

util.assert_return(False, msg, client.execute, sql.format('a3', 'a10', db, table))

sql1 = "select array_except(['1', '2', '3', null], [1, 1, 1, 'hek']) from test_query_qa.baseall"

sql2 = "select ['2', '3', NULL] from test_query_qa.baseall;"

common.check2(client, sql1, sql2=sql2)

sql1 = "select array_except(['2022-07-13', null], a10) from %s.%s order by k1" % (db, table_2)

sql2 = "select '[2022-07-13, NULL]' a union all select '[]' union all " \

"select '[2022-07-13, NULL]' union all select '[2022-07-13]' union all select null"

common.check_by_sql(sql1, sql2, client=client, a=palo_types.ARRAY_DATE)

sql1 = "select array_except([], []), array_except([null], []), array_except([], [null]), " \

"array_except([1, 2, null, 3, 4, 3, 2, null], [null, 3]), " \

"array_except([1, 2, null, 3, 4, 3, 2, null], [null, 5, 6]), array_except([1, 2, 3], null)"

sql2 = "select [], [null], [], [1, 2, 4], [1, 2, 3, 4], null"

common.check2(client, sql1, sql2=sql2)

sql1 = "select array_except([null], [1])"

sql2 = "select [null]"

common.check2(client, sql1, sql2=sql2)

sql1 = "select array_except(a3, a7) from %s.%s where k1 = 0 order by k1" % (db, table_2)

sql2 = "select [-32767, 255, 1985, 1986, 1989, 1991, 1992, 32767]"

common.check2(client, sql1, sql2=sql2)

def test_array_intersect():

"""

{

"title": "test_array_intersect",

"describe": "array_intersect函数测试",

"tag": "function,p1"

}

"""

# ARRAY<T> array_intersect(ARRAY<T> array1, ARRAY<T> array2) array1和array2的交集中的所有元素，不包含重复项

# enable_vectorized_engine

client = common.get_client()

# types

support_k = ['a1', 'a2', 'a3', 'a4', 'a5', 'a6', 'a7', 'a8', 'a9', 'a10', 'a11', 'a12', 'a13', 'a14']

sql1 = 'select array_intersect({0}, []) from {1}.{2}'

sql2 = 'select [] from test_query_qa.test group by k1'

for c in support_k:

common.check2(client, sql1=sql1.format(c, db, table), sql2=sql2)

sql1 = 'select k1, array_intersect(a1, [1]), array_intersect(a2, [null]), array_intersect(a3, [null]), ' \

'array_intersect(a4, [null]), array_intersect(a5, [null]), array_intersect(a6, [null]), ' \

'array_intersect(a7, [null]), array_intersect(a8, [null]), array_intersect(a9, [null]), ' \

'array_intersect(a9, [null]), array_intersect(a10, [null]), array_intersect(a11, [null]), ' \

'array_intersect(a12, [null]), array_intersect(a13, [null]), array_intersect(a14, [null]) ' \

'from %s.%s order by k1' % (db, table)

sql2 = 'select k1, [1], [], [], [], [], [], [], [], [], [], [], [], [], [], [] ' \

'from test_query_qa.test group by k1 order by k1'

common.check2(client, sql1=sql1, sql2=sql2)

sql0 = 'select t1.{0}, t2.{0}, array_intersect(t1.{0}, t2.{0}) from {1}.{2} t1 cross join {1}.{2} t2 ' \

'where t1.k1=1 and t2.k1 > 0'

sql1 = 'select array_intersect(t1.{0}, t2.{0}) from {1}.{2} t1 cross join {1}.{2} t2 ' \

'where t1.k1=1 and t2.k1 > 0'

sql2 = 'select array_distinct({0}) from {1}.{2} where k1 > 0'

for c in support_k:

print(sql0.format(c, db, table_2))

common.check2(client, sql1=sql1.format(c, db, table_2), sql2=sql2.format(c, db, table_2), forced=True)

# array不同子类型的array_except，如array<int> vs array<date>， not support

sql = 'select array_intersect({0}, {1}) from {2}.{3} where k1 = 0 order by k1'

msg = 'No matching function with signature: array_intersect(array<smallint(6)>, array<date>).'

util.assert_return(False, msg, client.execute, sql.format('a3', 'a10', db, table))

sql1 = "select array_intersect(['1', '2', 'a', 'b', null], ['b,a', 'a', '3', '4', null])"

sql2 = "select ['a', NULL]"

common.check2(client, sql1=sql1, sql2=sql2)

sql1 = "select array_intersect(['1', '2', 'a', 'b', null], ['b,a', 'a', '3', '4', null]) from test_query_qa.baseall"

sql2 = "select ['a', NULL] from test_query_qa.baseall"

common.check2(client, sql1=sql1, sql2=sql2)

sql1 = "select k1 from %s.%s where size(array_intersect(a11, ['1989-03-21 13:11:00'])) = 0 " \

"order by k1" % (db, table_2)