Suggest table aliases + add tests for casing

If config.main.generate_aliases is True, for `SELECT * FROM `, we suggest `FooBar FB` and `foo_bar fb` instead of `FooBar` and `foo_bar`, respectively.
To be able to add a test, I had to add support for testing with different settings, which meant I could also add tests for casing.

There are two non-obvious changes that I can think of:
1. The lexical sorting of matches is modified so as to sort spaces and underscores before letters and to sort case-insensitively. This is so that e.g `Foob F` comes before 'FooBar FB' when `foob` is input.
2. We now suggest `some_func()` instead of `some_func` (because suggesting `some_func sf` didn't make any sense).

4 files changed, 326 insertions, 115 deletions

diff --git a/tests/metadata.py b/tests/metadata.py
index cd5b2fa0..f495c14c 100644
--- a/tests/metadata.py
+++ b/tests/metadata.py
@@ -56,7 +56,7 @@ class MetaData(object):
             for x in self.metadata.get('views', {}).get(schema, [])]
 
     def functions(self, schema='public', pos=0):
-        return [function(escape(x[0]), pos)
+        return [function(escape(x[0] + '()'), pos)
             for x in self.metadata.get('functions', {}).get(schema, [])]
 
     def schemas(self, pos=0):
@@ -65,9 +65,12 @@ class MetaData(object):
 
     @property
     def completer(self):
+        return self.get_completer()
+
+    def get_completer(self, settings=None, casing=None):
         metadata = self.metadata
-        import pgcli.pgcompleter as pgcompleter
-        comp = pgcompleter.PGCompleter(smart_completion=True)
+        from pgcli.pgcompleter import PGCompleter
+        comp = PGCompleter(smart_completion=True, settings=settings)
 
         schemata, tables, tbl_cols, views, view_cols = [], [], [], [], []
 
@@ -105,5 +108,6 @@ class MetaData(object):
         comp.extend_datatypes(datatypes)
         comp.extend_foreignkeys(foreignkeys)
         comp.set_search_path(['public'])
+        comp.extend_casing(casing or [])
 
         return comp
diff --git a/tests/test_smart_completion_multiple_schemata.py b/tests/test_smart_completion_multiple_schemata.py
index a8167ba4..efa00f3c 100644
--- a/tests/test_smart_completion_multiple_schemata.py
+++ b/tests/test_smart_completion_multiple_schemata.py
@@ -2,7 +2,7 @@ from __future__ import unicode_literals
 import pytest
 import itertools
 from metadata import (MetaData, alias, name_join, fk_join, join,
-    function, wildcard_expansion)
+    schema, table, function, wildcard_expansion)
 from prompt_toolkit.document import Document
 from pgcli.packages.function_metadata import FunctionMetadata, ForeignKey
 
@@ -49,19 +49,25 @@ testdata = MetaData(metadata)
 def completer():
     return testdata.completer
 
+casing = ('SELECT', 'Orders', 'User_Emails', 'CUSTOM', 'Func1')
+
+@pytest.fixture
+def completer_with_casing():
+    return testdata.get_completer(casing=casing)
+
+@pytest.fixture
+def completer_with_aliases():
+    return testdata.get_completer({'generate_aliases': True})
+
+@pytest.fixture
+def completer_aliases_casing(request):
+    return testdata.get_completer({'generate_aliases': True}, casing)
+
 @pytest.fixture
 def complete_event():
     from mock import Mock
     return Mock()
 
-def test_schema_or_visible_table_completion(completer, complete_event):
-    text = 'SELECT * FROM '
-    position = len(text)
-    result = completer.get_completions(
-        Document(text=text, cursor_position=position), complete_event)
-    assert set(result) == set(testdata.schemas() + testdata.functions() + testdata.tables())
-
-
 @pytest.mark.parametrize('table', [
     'users',
     '"users"',
@@ -289,8 +295,8 @@ def test_schema_qualified_function_name(completer, complete_event):
     result = set(completer.get_completions(
         Document(text=text, cursor_position=postion), complete_event))
     assert result == set([
-        function('func3', -len('func')),
-        function('set_returning_func', -len('func'))])
+        function('func3()', -len('func')),
+        function('set_returning_func()', -len('func'))])
 
 
 @pytest.mark.parametrize('text', [
@@ -430,3 +436,51 @@ def test_suggest_columns_from_quoted_table(completer, complete_event, text):
     result = completer.get_completions(Document(text=text, cursor_position=pos),
                                        complete_event)
     assert set(result) == set(testdata.columns('Users', 'custom'))
+
+texts = ['SELECT * FROM ', 'SELECT * FROM public.Orders O CROSS JOIN ']
+
+@pytest.mark.parametrize('text', texts)
+def test_schema_or_visible_table_completion(completer, complete_event, text):
+    result = completer.get_completions(Document(text=text), complete_event)
+    assert set(result) == set(testdata.schemas()
+        + testdata.views() + testdata.tables() + testdata.functions())
+    result = completer.get_completions(Document(text=text), complete_event)
+
+@pytest.mark.parametrize('text', texts)
+def test_table_aliases(completer_with_aliases, complete_event, text):
+    result = completer_with_aliases.get_completions(
+        Document(text=text), complete_event)
+    assert set(result) == 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')])
+
+@pytest.mark.parametrize('text', texts)
+def test_aliases_with_casing(completer_aliases_casing, complete_event, text):
+    result = completer_aliases_casing.get_completions(
+        Document(text=text), complete_event)
+    assert set(result) == set([
+        schema('public'),
+        schema('CUSTOM'),
+        schema('"Custom"'),
+        table('users u'),
+        table('Orders O' if text == 'SELECT * FROM ' else 'Orders O2'),
+        table('"select" s'),
+        function('Func1() F'),
+        function('func2() f')])
+
+@pytest.mark.parametrize('text', texts)
+def test_table_casing(completer_with_casing, complete_event, text):
+    result = completer_with_casing.get_completions(
+        Document(text=text), complete_event)
+    assert set(result) == set([
+        schema('public'),
+        schema('CUSTOM'),
+        schema('"Custom"'),
+        table('users'),
+        table('Orders'),
+        table('"select"'),
+        function('Func1()'),
+        function('func2()')])
diff --git a/tests/test_smart_completion_public_schema_only.py b/tests/test_smart_completion_public_schema_only.py
index dd9e8eef..61b91972 100644
--- a/tests/test_smart_completion_public_schema_only.py
+++ b/tests/test_smart_completion_public_schema_only.py
@@ -1,7 +1,7 @@
 from __future__ import unicode_literals
 import pytest
 from metadata import (MetaData, alias, name_join, fk_join, join, keyword,
-    table, function, column, wildcard_expansion)
+    schema, table, view, function, column, wildcard_expansion)
 from prompt_toolkit.document import Document
 from pgcli.packages.function_metadata import FunctionMetadata, ForeignKey
 
@@ -14,10 +14,10 @@ metadata = {
     'views': {
         'user_emails': ['id', 'email']},
     'functions': [
-        ['custom_func1', [''], [''], [''], '', False, False,
-            False],
-        ['custom_func2', [''], [''], [''], '', False, False,
-            False],
+        ['custom_fun', [''], [''], [''], '', False, False, False],
+        ['_custom_fun', [''], [''], [''], '', False, False, False],
+        ['custom_func1', [''], [''], [''], '', False, False, False],
+        ['custom_func2', [''], [''], [''], '', False, False, False],
         ['set_returning_func', ['x', 'y'], ['integer', 'integer'],
             ['o', 'o'], '', False, False, True]],
     'datatypes': ['custom_type1', 'custom_type2'],
@@ -31,11 +31,46 @@ metadata = dict((k, {'public': v}) for k, v in metadata.items())
 
 testdata = MetaData(metadata)
 
+cased_users_cols = ['ID', 'PARENTID', 'Email', 'First_Name', 'last_name']
+cased_users2_cols = ['UserID', 'UserName']
+cased_funcs = ['Custom_Fun', '_custom_fun', 'Custom_Func1',
+    'custom_func2', 'set_returning_func']
+cased_tbls = ['Users', 'Orders']
+cased_views = ['User_Emails']
+casing =  (['SELECT', 'PUBLIC'] + cased_funcs + cased_tbls + cased_views
+    + cased_users_cols + cased_users2_cols)
+# Lists for use in assertions
+cased_funcs = [function(f + '()') for f in cased_funcs]
+cased_tbls = [table(t) for t in (cased_tbls + ['"Users"', '"select"'])]
+cased_rels = [view(t) for t in cased_views] + cased_funcs + cased_tbls
+cased_users_cols = [column(c) for c in cased_users_cols]
+aliased_rels = [table(t) for t in ('users u', '"Users" U', 'orders o',
+    '"select" s')] + [view('user_emails ue')] + [function(f) for f in (
+    '_custom_fun() cf', 'custom_fun() cf', 'custom_func1() cf',
+    'custom_func2() cf', 'set_returning_func() srf')]
+cased_aliased_rels = [table(t) for t in ('Users U', '"Users" U', 'Orders O',
+    '"select" s')] + [view('User_Emails UE')] + [function(f) for f in (
+    '_custom_fun() cf', 'Custom_Fun() CF', 'Custom_Func1() CF',
+    'custom_func2() cf', 'set_returning_func() srf')]
+
+
 @pytest.fixture
 def completer():
     return testdata.completer
 
 @pytest.fixture
+def cased_completer():
+    return testdata.get_completer(casing=casing)
+
+@pytest.fixture
+def aliased_completer():
+    return testdata.get_completer({'generate_aliases': True})
+
+@pytest.fixture
+def cased_aliased_completer(request):
+    return testdata.get_completer({'generate_aliases': True}, casing)
+
+@pytest.fixture
 def complete_event():
     from mock import Mock
     return Mock()
@@ -58,15 +93,6 @@ def test_select_keyword_completion(completer, complete_event):
     assert set(result) == set([keyword('SELECT', -3)])
 
 
-def test_schema_or_visible_table_completion(completer, complete_event):
-    text = 'SELECT * FROM '
-    position = len(text)
-    result = completer.get_completions(
-        Document(text=text, cursor_position=position), complete_event)
-    assert set(result) == set(testdata.schemas()
-        + testdata.views() + testdata.tables() + testdata.functions())
-
-
 def test_builtin_function_name_completion(completer, complete_event):
     text = 'SELECT MA'
     position = len('SELECT MA')
@@ -94,8 +120,10 @@ def test_user_function_name_completion(completer, complete_event):
     result = completer.get_completions(
         Document(text=text, cursor_position=position), complete_event)
     assert set(result) == set([
-        function('custom_func1', -2),
-        function('custom_func2', -2),
+        function('custom_fun()', -2),
+        function('_custom_fun()', -2),
+        function('custom_func1()', -2),
+        function('custom_func2()', -2),
         keyword('CURRENT', -2),
         ])
 
@@ -107,8 +135,10 @@ def test_user_function_name_completion_matches_anywhere(completer,
     result = completer.get_completions(
         Document(text=text, cursor_position=position), complete_event)
     assert set(result) == set([
-        function('custom_func1', -2),
-        function('custom_func2', -2)])
+        function('custom_fun()', -2),
+        function('_custom_fun()', -2),
+        function('custom_func1()', -2),
+        function('custom_func2()', -2)])
 
 
 def test_suggested_column_names_from_visible_table(completer, complete_event):
@@ -129,6 +159,22 @@ def test_suggested_column_names_from_visible_table(completer, complete_event):
         )
 
 
+def test_suggested_cased_column_names(cased_completer, complete_event):
+    """
+    Suggest column and function names when selecting from table
+    :param completer:
+    :param complete_event:
+    :return:
+    """
+    text = 'SELECT  from users'
+    position = len('SELECT ')
+    result = set(cased_completer.get_completions(
+        Document(text=text, cursor_position=position),
+        complete_event))
+    assert set(result) == set(cased_funcs + cased_users_cols
+        + testdata.builtin_functions() + testdata.keywords())
+
+
 def test_suggested_column_names_in_function(completer, complete_event):
     """
     Suggest column and function names when selecting multiple
@@ -205,6 +251,22 @@ def test_suggested_multiple_column_names_with_alias(completer, complete_event):
         complete_event))
     assert set(result) == set(testdata.columns('users'))
 
+
+def test_suggested_cased_column_names_with_alias(cased_completer, complete_event):
+    """
+    Suggest column names on table alias and dot
+    when selecting multiple columns from table
+    :param completer:
+    :param complete_event:
+    :return:
+    """
+    text = 'SELECT u.id, u. from users u'
+    position = len('SELECT u.id, u.')
+    result = set(cased_completer.get_completions(
+        Document(text=text, cursor_position=position),
+        complete_event))
+    assert set(result) == set(cased_users_cols)
+
 def test_suggested_multiple_column_names_with_dot(completer, complete_event):
     """
     Suggest column names on table names and dot
@@ -231,29 +293,38 @@ def test_suggest_columns_after_three_way_join(completer, complete_event):
     assert (column('id') in
             set(result))
 
-@pytest.mark.parametrize('text', [
-    'SELECT * FROM users u JOIN "Users" u2 ON ',
-    'SELECT * FROM users u INNER join "Users" u2 ON ',
-    'SELECT * FROM USERS u right JOIN "Users" u2 ON ',
-    'SELECT * FROM users u LEFT JOIN "Users" u2 ON ',
-    'SELECT * FROM Users u FULL JOIN "Users" u2 ON ',
-    'SELECT * FROM users u right outer join "Users" u2 ON ',
-    'SELECT * FROM Users u LEFT OUTER JOIN "Users" u2 ON ',
-    'SELECT * FROM users u FULL OUTER JOIN "Users" u2 ON ',
+join_condition_texts = [
+    'SELECT * FROM users U JOIN "Users" U2 ON ',
+    'SELECT * FROM users U INNER join "Users" U2 ON ',
+    'SELECT * FROM USERS U right JOIN "Users" U2 ON ',
+    'SELECT * FROM users U LEFT JOIN "Users" U2 ON ',
+    'SELECT * FROM Users U FULL JOIN "Users" U2 ON ',
+    'SELECT * FROM users U right outer join "Users" U2 ON ',
+    'SELECT * FROM Users U LEFT OUTER JOIN "Users" U2 ON ',
+    'SELECT * FROM users U FULL OUTER JOIN "Users" U2 ON ',
     '''SELECT *
-    FROM users u
-    FULL OUTER JOIN "Users" u2 ON
+    FROM users U
+    FULL OUTER JOIN "Users" U2 ON
     '''
-])
+]
+
+@pytest.mark.parametrize('text', join_condition_texts)
 def test_suggested_join_conditions(completer, complete_event, text):
-    position = len(text)
     result = set(completer.get_completions(
-        Document(text=text, cursor_position=position),
-        complete_event))
+        Document(text=text,), complete_event))
     assert set(result) == set([
-        alias('u'),
-        alias('u2'),
-        fk_join('u2.userid = u.id')])
+        alias('U'),
+        alias('U2'),
+        fk_join('U2.userid = U.id')])
+
+@pytest.mark.parametrize('text', join_condition_texts)
+def test_cased_join_conditions(cased_completer, complete_event, text):
+    result = set(cased_completer.get_completions(
+        Document(text=text), complete_event))
+    assert set(result) == set([
+        alias('U'),
+        alias('U2'),
+        fk_join('U2.UserID = U.ID')])
 
 @pytest.mark.parametrize('text', [
     '''SELECT *
@@ -273,10 +344,11 @@ def test_suggested_join_conditions_with_same_table_twice(completer, complete_eve
         fk_join('u2.userid = users.id'),
         name_join('u2.userid = "Users".userid'),
         name_join('u2.username = "Users".username'),
-        alias('"Users"'),
         alias('u'),
         alias('u2'),
-        alias('users')]
+        alias('users'),
+        alias('"Users"')
+    ]
 
 @pytest.mark.parametrize('text', [
     'SELECT * FROM users JOIN users u2 on foo.'
@@ -314,24 +386,44 @@ def test_suggested_joins_fuzzy(completer, complete_event, text):
     expected = join('users ON users.id = u.userid', -len(last_word))
     assert expected in result
 
-@pytest.mark.parametrize('text', [
-    'SELECT * FROM users JOIN ',
+join_texts = [
+    'SELECT * FROM Users JOIN ',
     '''SELECT *
-    FROM users
+    FROM Users
     INNER JOIN '''
-])
+]
+
+@pytest.mark.parametrize('text', join_texts)
 def test_suggested_joins(completer, complete_event, text):
-    position = len(text)
     result = set(completer.get_completions(
-        Document(text=text, cursor_position=position),
-        complete_event))
+        Document(text=text), complete_event))
     assert set(result) == set(testdata.schemas() + testdata.tables()
         + testdata.views() + [
-        join('"Users" ON "Users".userid = users.id'),
-        join('users users2 ON users2.id = users.parentid'),
-        join('users users2 ON users2.parentid = users.id'),
+        join('"Users" ON "Users".userid = Users.id'),
+        join('users users2 ON users2.id = Users.parentid'),
+        join('users users2 ON users2.parentid = Users.id'),
         ] + testdata.functions())
 
+@pytest.mark.parametrize('text', join_texts)
+def test_cased_joins(cased_completer, complete_event, text):
+    result = set(cased_completer.get_completions(
+        Document(text=text), complete_event))
+    assert set(result) == 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'),
+        ])
+
+@pytest.mark.parametrize('text', join_texts)
+def test_aliased_joins(aliased_completer, complete_event, text):
+    result = set(aliased_completer.get_completions(
+        Document(text=text), complete_event))
+    assert set(result) == 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'),
+        ])
+
 @pytest.mark.parametrize('text', [
     'SELECT * FROM public."Users" JOIN ',
     'SELECT * FROM public."Users" RIGHT OUTER JOIN ',
@@ -459,15 +551,17 @@ def test_table_names_after_from(completer, complete_event, text):
     assert set(result) == set(testdata.schemas() + testdata.tables()
         + testdata.views() + testdata.functions())
     assert [c.text for c in result] == [
-        '"Users"',
-        'custom_func1',
-        'custom_func2',
+        '_custom_fun()',
+        'custom_fun()',
+        'custom_func1()',
+        'custom_func2()',
         'orders',
         'public',
         '"select"',
-        'set_returning_func',
+        'set_returning_func()',
         'user_emails',
-        'users'
+        'users',
+        '"Users"',
         ]
 
 def test_auto_escaped_col_names(completer, complete_event):
@@ -681,3 +775,62 @@ def test_suggest_columns_from_quoted_table(completer, complete_event):
     result = completer.get_completions(Document(text=text, cursor_position=pos),
                                        complete_event)
     assert set(result) == set(testdata.columns('Users'))
+
+@pytest.mark.parametrize('text', ['SELECT * FROM ',
+    'SELECT * FROM Orders o CROSS JOIN '])
+def test_schema_or_visible_table_completion(completer, complete_event, text):
+    result = completer.get_completions(Document(text=text), complete_event)
+    assert set(result) == set(testdata.schemas()
+        + testdata.views() + testdata.tables() + testdata.functions())
+
+@pytest.mark.parametrize('text', ['SELECT * FROM '])
+def test_table_aliases(aliased_completer, complete_event, text):
+    result = aliased_completer.get_completions(
+        Document(text=text), complete_event)
+    assert set(result) == set(testdata.schemas() + aliased_rels)
+
+@pytest.mark.parametrize('text', ['SELECT * FROM Orders o CROSS JOIN '])
+def test_duplicate_table_aliases(aliased_completer, complete_event, text):
+    result = aliased_completer.get_completions(
+        Document(text=text), complete_event)
+    assert set(result) == set(testdata.schemas() + [
+        table('orders o2'),
+        table('users u'),
+        table('"Users" U'),
+        table('"select" s'),
+        view('user_emails ue'),
+        function('_custom_fun() cf'),
+        function('custom_fun() cf'),
+        function('custom_func1() cf'),
+        function('custom_func2() cf'),
+        function('set_returning_func() srf')])
+
+@pytest.mark.parametrize('text', ['SELECT * FROM Orders o CROSS JOIN '])
+def test_duplicate_aliases_with_casing(cased_aliased_completer,
+                                        complete_event, text):
+    result = cased_aliased_completer.get_completions(
+        Document(text=text), complete_event)
+    assert set(result) == set([
+        schema('PUBLIC'),
+        table('Orders O2'),
+        table('Users U'),
+        table('"Users" U'),
+        table('"select" s'),
+        view('User_Emails UE'),
+        function('_custom_fun() cf'),
+        function('Custom_Fun() CF'),
+        function('Custom_Func1() CF'),
+        function('custom_func2() cf'),
+        function('set_returning_func() srf')])
+
+@pytest.mark.parametrize('text', ['SELECT * FROM '])
+def test_aliases_with_casing(cased_aliased_completer, complete_event, text):
+    result = cased_aliased_completer.get_completions(
+        Document(text=text), complete_event)
+    assert set(result) == set([schema('PUBLIC')] + cased_aliased_rels)
+
+@pytest.mark.parametrize('text', ['SELECT * FROM '])
+def test_table_casing(cased_completer, complete_event, text):
+    result = cased_completer.get_completions(
+        Document(text=text), complete_event)
+    assert set(result) == set([schema('PUBLIC')] + cased_rels)
diff --git a/tests/test_sqlcompletion.py b/tests/test_sqlcompletion.py
index c8e3f541..de4cbe52 100644
--- a/tests/test_sqlcompletion.py
+++ b/tests/test_sqlcompletion.py
@@ -1,6 +1,6 @@
 from pgcli.packages.sqlcompletion import (
     suggest_type, Special, Database, Schema, Table, Column, View, Keyword,
-    Function, Datatype, Alias, JoinCondition, Join)
+    FromClauseItem, Function, Datatype, Alias, JoinCondition, Join)
 import pytest
 
 # Returns the expected select-clause suggestions for a single-table select
@@ -96,9 +96,7 @@ def test_suggests_tables_views_and_schemas(expression):
 def test_suggest_tables_views_schemas_and_functions(expression):
     suggestions = suggest_type(expression, expression)
     assert set(suggestions) == set([
-        Table(schema=None),
-        View(schema=None),
-        Function(schema=None, filter='for_from_clause'),
+        FromClauseItem(schema=None),
         Schema()
     ])
 
@@ -109,11 +107,10 @@ def test_suggest_tables_views_schemas_and_functions(expression):
 ])
 def test_suggest_after_join_with_two_tables(expression):
     suggestions = suggest_type(expression, expression)
+    tables = tuple([(None, 'foo', None, False), (None, 'bar', None, False)])
     assert set(suggestions) == set([
-        Table(schema=None),
-        View(schema=None),
-        Function(schema=None, filter='for_from_clause'),
-        Join(((None, 'foo', None, False), (None, 'bar', None, False)), None),
+        FromClauseItem(schema=None, tables=tables),
+        Join(tables, None),
         Schema(),
     ])
 
@@ -124,10 +121,9 @@ def test_suggest_after_join_with_two_tables(expression):
 ])
 def test_suggest_after_join_with_one_table(expression):
     suggestions = suggest_type(expression, expression)
+    tables = ((None, 'foo', None, False),)
     assert set(suggestions) == set([
-        Table(schema=None),
-        View(schema=None),
-        Function(schema=None, filter='for_from_clause'),
+        FromClauseItem(schema=None, tables=tables),
         Join(((None, 'foo', None, False),), None),
         Schema(),
     ])
@@ -136,7 +132,6 @@ def test_suggest_after_join_with_one_table(expression):
 @pytest.mark.parametrize('expression', [
     'INSERT INTO sch.',
     'COPY sch.',
-    'UPDATE sch.',
     'DESCRIBE sch.',
 ])
 def test_suggest_qualified_tables_and_views(expression):
@@ -148,6 +143,17 @@ def test_suggest_qualified_tables_and_views(expression):
 
 
 @pytest.mark.parametrize('expression', [
+    'UPDATE sch.',
+])
+def test_suggest_qualified_aliasable_tables_and_views(expression):
+    suggestions = suggest_type(expression, expression)
+    assert set(suggestions) == set([
+        Table(schema='sch'),
+        View(schema='sch'),
+    ])
+
+
+@pytest.mark.parametrize('expression', [
     'SELECT * FROM sch.',
     'SELECT * FROM sch."',
     'SELECT * FROM sch."foo',
@@ -156,11 +162,7 @@ def test_suggest_qualified_tables_and_views(expression):
 ])
 def test_suggest_qualified_tables_views_and_functions(expression):
     suggestions = suggest_type(expression, expression)
-    assert set(suggestions) == set([
-        Table(schema='sch'),
-        View(schema='sch'),
-        Function(schema='sch', filter='for_from_clause'),
-    ])
+    assert set(suggestions) == set([FromClauseItem(schema='sch')])
 
 
 @pytest.mark.parametrize('expression', [
@@ -168,11 +170,10 @@ def test_suggest_qualified_tables_views_and_functions(expression):
 ])
 def test_suggest_qualified_tables_views_functions_and_joins(expression):
     suggestions = suggest_type(expression, expression)
+    tbls = tuple([(None, 'foo', None, False)])
     assert set(suggestions) == set([
-        Table(schema='sch'),
-        View(schema='sch'),
-        Function(schema='sch', filter='for_from_clause'),
-        Join(((None, 'foo', None, False),), 'sch'),
+        FromClauseItem(schema='sch', tables=tbls),
+        Join(tbls, 'sch'),
     ])
 
 
@@ -207,9 +208,7 @@ def test_table_comma_suggests_tables_and_schemas():
     suggestions = suggest_type('SELECT a, b FROM tbl1, ',
             'SELECT a, b FROM tbl1, ')
     assert set(suggestions) == set([
-        Table(schema=None),
-        View(schema=None),
-        Function(schema=None, filter='for_from_clause'),
+        FromClauseItem(schema=None),
         Schema(),
     ])
 
@@ -330,15 +329,24 @@ def test_outer_table_reference_in_exists_subquery_suggests_columns():
 
 @pytest.mark.parametrize('expression', [
     'SELECT * FROM (SELECT * FROM ',
+])
+def test_sub_select_table_name_completion(expression):
+    suggestion = suggest_type(expression, expression)
+    assert set(suggestion) == set([
+        FromClauseItem(schema=None),
+        Schema(),
+    ])
+
+
+@pytest.mark.parametrize('expression', [
     'SELECT * FROM foo WHERE EXISTS (SELECT * FROM ',
     'SELECT * FROM foo WHERE bar AND NOT EXISTS (SELECT * FROM ',
 ])
-def test_sub_select_table_name_completion(expression):
+def test_sub_select_table_name_completion_with_outer_table(expression):
     suggestion = suggest_type(expression, expression)
+    tbls = tuple([(None, 'foo', None, False)])
     assert set(suggestion) == set([
-        Table(schema=None),
-        View(schema=None),
-        Function(schema=None, filter='for_from_clause'),
+        FromClauseItem(schema=None, tables=tbls),
         Schema(),
     ])
 
@@ -376,22 +384,22 @@ def test_sub_select_dot_col_name_completion():
 def test_join_suggests_tables_and_schemas(tbl_alias, join_type):
     text = 'SELECT * FROM abc {0} {1} JOIN '.format(tbl_alias, join_type)
     suggestion = suggest_type(text, text)
+    tbls = tuple([(None, 'abc', tbl_alias or None, False)])
     assert set(suggestion) == set([
-        Table(schema=None),
-        View(schema=None),
-        Function(schema=None, filter='for_from_clause'),
+        FromClauseItem(schema=None, tables=tbls),
         Schema(),
-        Join(((None, 'abc', tbl_alias if tbl_alias else None, False),), None),
+        Join(tbls, None),
     ])
 
 
 def test_left_join_with_comma():
     text = 'select * from foo f left join bar b,'
     suggestions = suggest_type(text, text)
+    # tbls should also include (None, 'bar', 'b', False)
+    # but there's a bug with commas
+    tbls = tuple([(None, 'foo', 'f', False)])
     assert set(suggestions) == set([
-         Table(schema=None),
-         View(schema=None),
-         Function(schema=None, filter='for_from_clause'),
+         FromClauseItem(schema=None, tables=tbls),
          Schema(),
     ])
 
@@ -497,9 +505,7 @@ def test_2_statements_2nd_current():
     suggestions = suggest_type('select * from a; select * from ',
                                'select * from a; select * from ')
     assert set(suggestions) == set([
-        Table(schema=None),
-        View(schema=None),
-        Function(schema=None, filter='for_from_clause'),
+        FromClauseItem(schema=None),
         Schema(),
     ])
 
@@ -515,9 +521,7 @@ def test_2_statements_2nd_current():
     suggestions = suggest_type('select * from; select * from ',
                                'select * from; select * from ')
     assert set(suggestions) == set([
-        Table(schema=None),
-        View(schema=None),
-        Function(schema=None, filter='for_from_clause'),
+        FromClauseItem(schema=None),
         Schema(),
     ])
 
@@ -526,9 +530,7 @@ def test_2_statements_1st_current():
     suggestions = suggest_type('select * from ; select * from b',
                                'select * from ')
     assert set(suggestions) == set([
-        Table(schema=None),
-        View(schema=None),
-        Function(schema=None, filter='for_from_clause'),
+        FromClauseItem(schema=None),
         Schema(),
     ])
 
@@ -541,9 +543,7 @@ def test_3_statements_2nd_current():
     suggestions = suggest_type('select * from a; select * from ; select * from c',
                                'select * from a; select * from ')
     assert set(suggestions) == set([
-        Table(schema=None),
-        View(schema=None),
-        Function(schema=None, filter='for_from_clause'),
+        FromClauseItem(schema=None),
         Schema(),
     ])
 
@@ -698,7 +698,7 @@ def test_select_suggests_fields_from_function():
 ])
 def test_ignore_leading_double_quotes(sql):
     suggestions = suggest_type(sql, sql)
-    assert Table(schema=None) in set(suggestions)
+    assert FromClauseItem(schema=None) in set(suggestions)
 
 
 @pytest.mark.parametrize('sql', [