pytest/sys/test_sys_insert_value.py

#!/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.

"""
test insert into value
Date: 2019-07-24 13:42:40
"""

import sys
import re
sys.path.append("../")
from lib import palo_client
from lib import palo_config
from lib import util
from lib import palo_job
from lib import common
from data import schema as DATA

client = None
config = palo_config.config


def setup_module():
    """
    set up
    """
    global client
    client = palo_client.get_client(config.fe_host, config.fe_query_port, user=config.fe_user,
                                    password=config.fe_password, http_port=config.fe_http_port)
    client.set_variables('enable_insert_strict', 0)


def test_insert_value():
    """
    {
    "title": "test_insert_value",
    "describe": "测试insert into value基本用法",
    "tag": "function,p1"
    }
    """
    database_name, table_name, index_name = util.gen_name_list()
    client.clean(database_name)
    ret = client.create_database(database_name)
    assert ret
    client.use(database_name)
    ret = client.create_table(table_name, DATA.int_column_list, set_null=True)
    assert ret
    sql = 'insert into %s values(0, 0, 0, 0, 0)' % table_name
    ret = client.execute(sql)
    assert ret == ()
    sql = 'select * from %s' % table_name
    ret = client.execute(sql)
    assert ret == ((0, 0, 0, 0, 0),)
    sql = 'insert into %s values(0, 1, 1, 1, 1)' % table_name
    ret = client.execute(sql)
    assert ret == ()
    sql = 'select * from %s' % table_name
    ret = client.execute(sql)
    assert ret == ((0, 1, 1, 0, 1),)
    client.clean(database_name)


def test_insert_multi_value():
    """
    {
    "title": "test_insert_multi_value",
    "describe": "测试insert into value插入多条数据, set_enable_strict",
    "tag": "function,p1"
    }
    """
    database_name, table_name, index_name = util.gen_name_list()
    client.clean(database_name)
    ret = client.create_database(database_name)
    assert ret
    client.use(database_name)
    ret = client.create_table(table_name, DATA.int_column_list)
    assert ret
    sql = 'insert into %s values(0,0,0,0,0),(1,1,1,1,1),(2,2,2,2,2),(null, null, null, null, null)' % table_name
    ret = client.execute(sql)
    assert ret == ()
    sql = 'select * from %s order by k1' % table_name
    ret = client.execute(sql)
    exp = ((0, 0, 0, 0, 0), (1, 1, 1, 1, 1), (2, 2, 2, 2, 2))
    util.check(ret, exp)
    table_name += '_s'
    ret = client.create_table(table_name, DATA.int_column_list)
    assert ret
    sql = 'set enable_insert_strict=1'
    ret = client.execute(sql)
    assert ret == ()
    sql = 'insert into %s values(0,0,0,0,0),(1,1,1,1,1),(2,2,2,2,2),(null, null, null, null, null)' % table_name
    flag = True
    try:
        ret = client.execute(sql)
        print(ret)
        flag = False
    except Exception as e:
        print(str(e))
    assert flag
    client.clean(database_name)


def test_insert_value_column_list():
    """
    {
    "title": "test_insert_value_column_list",
    "describe": "测试insert into value指定列顺序",
    "tag": "function,p1"
    }
    """
    database_name, table_name, index_name = util.gen_name_list()
    client.clean(database_name)
    ret = client.create_database(database_name)
    assert ret
    client.use(database_name)
    ret = client.create_table(table_name, DATA.int_column_list)
    assert ret
    sql = 'insert into %s (v4, v3, v2, v1, k1) values(1,2,3,4,5)' % table_name
    ret = client.execute(sql)
    assert ret == ()
    sql = 'select * from %s' % table_name
    ret = client.execute(sql)
    assert ret == ((5, 4, 3, 2, 1),)
    sql = 'insert into %s (v4, v3, v2, v1) values(1,2,3,4)' % table_name
    flag = True
    try:
        client.execute(sql)
        flag = False
    except Exception as e:
        print(str(e))
    assert flag
    client.clean(database_name)


def test_insert_value_default():
    """
    {
    "title": "test_insert_value_default",
    "describe": "测试insert into value缺省列使用默认值",
    "tag": "function,p1"
    }
    """
    database_name, table_name, index_name = util.gen_name_list()
    client.clean(database_name)
    ret = client.create_database(database_name)
    assert ret
    client.use(database_name)
    ret = client.create_table(table_name, DATA.int_column_list, set_null=True)
    assert ret
    sql = 'insert into %s (v4, v3, v2, v1) values(1,2,3,4)' % table_name
    ret = client.execute(sql)
    assert ret == ()
    sql = 'select * from %s' % table_name
    ret = client.execute(sql)
    assert ret == ((None, 4, 3, 2, 1),)
    sql = 'insert into %s (v4, v3, v2, v1) values(2,2,3,2), (NULL, NULL, NULL, 3)' % table_name
    ret = client.execute(sql)
    sql = 'select * from %s order by v1' % table_name
    ret = client.execute(sql)
    assert ret == ((None, 9, 3, 2, None),)
    client.clean(database_name)


def test_insert_value_not_null():
    """
    {
    "title": "test_insert_value_not_null",
    "describe": "测试insert into value的Null值插入",
    "tag": "fuzz,p1"
    }
    """
    database_name, table_name, index_name = util.gen_name_list()
    client.clean(database_name)
    ret = client.create_database(database_name)
    assert ret
    client.use(database_name)
    ret = client.create_table(table_name, DATA.baseall_column_list)
    assert ret
    sql = 'insert into %s values(null, null, null, null, null, null, null,' \
          ' null, null, null, null)' % table_name
    flag = True
    try:
        ret = client.execute(sql)
        flag = False
    except Exception as e:
        print(str(e))
    assert flag == True, 'expect insert error'
    sql = 'select * from %s' % table_name
    ret = client.execute(sql)
    assert ret == ()
    table_name += '_n'
    ret = client.create_table(table_name, DATA.baseall_column_list, set_null=True)
    assert ret
    sql = 'insert into %s values(null, null, null, null, null, null, null,' \
          ' null, null, null, null)' % table_name
    ret = client.execute(sql)
    assert ret == ()
    sql = 'select * from %s' % table_name
    ret = client.execute(sql)
    assert ret == ((None, None, None, None, None, None, None, None, None, None, None),)
    client.clean(database_name)


def test_insert_select_not_null():
    """
    {
    "title": "test_insert_select_not_null",
    "describe": "测试insert into select的Null值插入",
    "tag": "fuzz,p1"
    }
    """
    database_name, table_name, index_name = util.gen_name_list()
    client.clean(database_name)
    ret = client.create_database(database_name)
    assert ret
    client.use(database_name)
    ret = client.create_table(table_name, DATA.int_column_list)
    client.set_variables('enable_insert_strict', 0)
    assert ret
    sql = 'insert into %s SELECT a.k1 AS n1, b.k2 AS n2, 1, 2, 3 FROM %s.baseall a ' \
          'LEFT OUTER JOIN %s.bigtable b ON a.k1 = b.k1 + 10' \
          % (table_name, config.palo_db, config.palo_db)
    ret = client.execute(sql)
    assert ret == ()
    sql1 = 'select * from %s order by k1' % table_name
    ret1 = client.execute(sql1)
    sql2 = 'SELECT a.k1 AS n1, b.k2 AS n2, 1, 2, 3 FROM %s.baseall a ' \
          'LEFT OUTER JOIN %s.bigtable b ON a.k1 = b.k1 + 10 where b.k2 is not null' \
          ' order by n1' % (config.palo_db, config.palo_db)
    ret2 = client.execute(sql2)
    util.check(ret1, ret2)
    client.clean(database_name)


def test_insert_value_special_data():
    """
    {
    "title": "test_insert_value_special_data",
    "describe": "测试使用insert into value插入特殊值, insert into tinyint溢出bug",
    "tag": "fuzz,p1"
    }
    """
    database_name, table_name, index_name = util.gen_name_list()
    client.clean(database_name)
    ret = client.create_database(database_name)
    assert ret
    client.use(database_name)
    ret = client.create_table(table_name, DATA.baseall_column_list, set_null=True)
    assert ret
    sql = 'insert into %s(k6) values("hello world")' % table_name
    flag = True
    client.set_variables('enable_insert_strict', 'true')
    try:
        ret = client.execute(sql)
        flag = False
    except Exception as e:
        print(str(e))
    assert flag == True
    client.set_variables('enable_insert_strict', 'false')
    ret = client.execute(sql)
    assert ret == ()
    sql = 'insert into %s(k10) values("2001-01-01 10-00-00")' % table_name
    ret = client.execute(sql)
    assert ret == ()
    sql = 'insert into %s(k5) values(100.1005)' % table_name
    ret = client.execute(sql)
    sql = 'insert into %s(k1) values(-56)' % table_name
    ret = client.execute(sql)
    sql = 'insert into %s(k1) values(200)' % table_name
    flag = True
    try:
        ret = client.execute(sql)
        flag = False
    except Exception as e:
        print(str(e))
    assert flag
    sql = 'select * from %s order by k1, k5, k6, k10' % table_name
    assert common.check_by_file('./data/LOAD/insert_value.data', sql=sql, client=client)
    client.clean(database_name)

    
def teardown_module():
    """tear down"""
    pass


if __name__ == '__main__':
    setup_module()