def test_suggested_multiple_column_names(completer):
    result = get_result(
        completer, 'SELECT id,  from custom.products', len('SELECT id, ')
    )
    assert completions_to_set(result) == completions_to_set(testdata.columns_functions_and_keywords(
        'products', 'custom'
    ))

def test_cased_joins(completer, text):
    result = get_result(completer, text)
    assert completions_to_set(result) == completions_to_set([schema('PUBLIC')] + cased_rels + [
        join('"Users" ON "Users".UserID = Users.ID'),
        join('Users Users2 ON Users2.ID = Users.PARENTID'),
        join('Users Users2 ON Users2.PARENTID = Users.ID'),
    ])

def test_suggested_join_conditions(completer, text):
    result = get_result(completer, text)
    assert completions_to_set(result) == completions_to_set([
        alias('users'),
        alias('shipments'),
        name_join('shipments.id = users.id'),
        fk_join('shipments.user_id = users.id')])

def test_aliased_joins(completer, text):
    result = get_result(completer, text)
    assert completions_to_set(result) == completions_to_set(testdata.schemas() + aliased_rels + [
        join('"Users" U ON U.userid = Users.id'),
        join('users u ON u.id = Users.parentid'),
        join('users u ON u.parentid = Users.id'),
    ])

def test_suggested_aliases_after_on_right_side(completer, text):
    position = len(
        'SELECT u.name, o.id FROM users u JOIN orders o ON o.user_id = '
    )
    result = get_result(completer, text, position)
    assert completions_to_set(result) == completions_to_set(
        [alias('u'), alias('o')])

def test_suggest_columns_from_aliased_set_returning_function(completer):
    result = get_result(
        completer, 'select f. from set_returning_func() f', len('select f.')
    )
    assert completions_to_set(result) == completions_to_set(
        testdata.columns('set_returning_func', typ='functions')
    )

def test_user_function_name_completion_matches_anywhere(completer):
    result = get_result(completer, 'SELECT om')
    assert completions_to_set(result) == completions_to_set([
        function('custom_fun()', -2),
        function('_custom_fun()', -2),
        function('custom_func1()', -2),
        function('custom_func2()', -2)])

def test_suggested_auto_qualified_column_names_two_tables(text, completer):
    position = text.index('  ') + 1
    cols = [column('U.' + c.lower()) for c in cased_users_col_names]
    cols += [column('"Users".' + c.lower()) for c in cased_users2_col_names]
    result = get_result(completer, text, position)
    assert completions_to_set(result) == completions_to_set(
        cols + testdata.functions_and_keywords())

def test_join_functions_using_suggests_common_columns(completer):
    text = '''SELECT * FROM set_returning_func() f1
              INNER JOIN set_returning_func() f2 USING ('''
    result = get_result(completer, text)
    assert completions_to_set(result) == completions_to_set(
        testdata.columns('set_returning_func', typ='functions')
    )

def test_suggested_column_names_from_schema_qualifed_table(completer):
    result = get_result(
        completer, 'SELECT  from custom.products', len('SELECT ')
    )
    assert completions_to_set(result) == completions_to_set(testdata.columns_functions_and_keywords(
        'products', 'custom'
    ))

def test_all_schema_objects_with_aliases(completer):
    text = ('SELECT * FROM ')
    result = get_result(completer, text)
    assert completions_to_set(result) >= completions_to_set(
        [table(x) for x in ('orders o', '"select" s', 'custom.shipments s')]
        + [function(x) for x in ('func2() f',)]
    )

def test_suggest_columns_from_set_returning_function(completer):
    result = get_result(
        completer, 'select  from set_returning_func()', len('select ')
    )
    assert completions_to_set(result) == completions_to_set(testdata.columns_functions_and_keywords(
        'set_returning_func', typ='functions'
    ))

def test_all_schema_objects_with_casing(completer):
    text = 'SELECT * FROM '
    result = get_result(completer, text)
    assert completions_to_set(result) >= completions_to_set(
        [table(x) for x in ('Orders', '"select"', 'CUSTOM.shipments')]
        + [function(x + '()') for x in ('func2',)]
    )

def test_suggested_multiple_column_names_with_dot(completer):
    result = get_result(
        completer,
        'SELECT users.id, users. from users u',
        len('SELECT users.id, users.')
    )
    assert completions_to_set(result) == completions_to_set(
        testdata.columns('users'))

def test_special_name_completion(completer, complete_event):
    text = '\\'
    position = len('\\')
    result = completions_to_set(completer.get_completions(
        Document(text=text, cursor_position=position),
        complete_event))
    # Special commands will NOT be suggested during naive completion mode.
    assert result == completions_to_set([])

def test_column_name_completion(completer, complete_event):
    text = 'SELECT  FROM users'
    position = len('SELECT ')
    result = completions_to_set(completer.get_completions(
        Document(text=text, cursor_position=position),
        complete_event))
    assert result == completions_to_set(
        map(Completion, completer.all_completions))

def test_set_schema(completer):
    text = ('SET SCHEMA ')
    result = get_result(completer, text)
    assert completions_to_set(result) == completions_to_set([
        schema(u"'blog'"),
        schema(u"'Custom'"),
        schema(u"'custom'"),
        schema(u"'public'")])

def test_table_casing(completer, text):
    result = get_result(completer, text)
    assert completions_to_set(result) == completions_to_set(cased_schemas + [
        table('users'),
        table('Orders'),
        table('"select"'),
        function('Func1()'),
        function('func2()')])

def test_aliases_with_casing(completer, text):
    result = get_result(completer, text)
    assert completions_to_set(result) == completions_to_set(cased_schemas + [
        table('users u'),
        table('Orders O' if text == 'SELECT * FROM ' else 'Orders O2'),
        table('"select" s'),
        function('Func1() F'),
        function('func2() f')])

def test_table_aliases(completer, text):
    result = get_result(completer, text)
    assert completions_to_set(result) == completions_to_set(testdata.schemas() + [
        table('users u'),
        table('orders o' if text == 'SELECT * FROM ' else 'orders o2'),
        table('"select" s'),
        function('func1() f'),
        function('func2() f')])