Fix some join-condition issues

When self-joining a table with an FK to or from some other table, we got a false FK-join suggestion for that column.
There was also a problem with quoted tables not being quoted in the join condition.
And there were a couple of problems when trying to join a non-existent table or using a non-existent qualifier (`SELECT * FROM Foo JOIN Bar ON Meow.`).

I also rewrote get_join_condition_matches a bit in the process, hopefully making it a bit simpler.

2 files changed, 51 insertions, 4 deletions

diff --git a/tests/test_smart_completion_multiple_schemata.py b/tests/test_smart_completion_multiple_schemata.py
index 9f74a7d4..b5ac0d3a 100644
--- a/tests/test_smart_completion_multiple_schemata.py
+++ b/tests/test_smart_completion_multiple_schemata.py
@@ -100,17 +100,18 @@ def test_schema_or_visible_table_completion(completer, complete_event):
        Completion(text='orders', start_position=0, display_meta='table')])
 
 
-@pytest.mark.parametrize('text', [
-    'SELECT  FROM users',
-    'SELECT  FROM "users"',
+@pytest.mark.parametrize('table', [
+    'users',
+    '"users"',
     ])
-def test_suggested_column_names_from_shadowed_visible_table(completer, complete_event, text):
+def test_suggested_column_names_from_shadowed_visible_table(completer, complete_event, table):
     """
     Suggest column and function names when selecting from table
     :param completer:
     :param complete_event:
     :return:
     """
+    text = 'SELECT  FROM ' + table
     position = len('SELECT ')
     result = set(completer.get_completions(
         Document(text=text, cursor_position=position),
diff --git a/tests/test_smart_completion_public_schema_only.py b/tests/test_smart_completion_public_schema_only.py
index 3226484f..01c54fe5 100644
--- a/tests/test_smart_completion_public_schema_only.py
+++ b/tests/test_smart_completion_public_schema_only.py
@@ -343,6 +343,52 @@ def test_suggested_join_conditions(completer, complete_event, text):
         Completion(text='u2.userid = u.id', start_position=0, display_meta='fk join')])
 
 @pytest.mark.parametrize('text', [
+    '''SELECT *
+    FROM users
+    CROSS JOIN "Users"
+    NATURAL JOIN users u
+    JOIN "Users" u2 ON
+    '''
+])
+def test_suggested_join_conditions_with_same_table_twice(completer, complete_event, text):
+    position = len(text)
+    result = completer.get_completions(
+        Document(text=text, cursor_position=position),
+        complete_event)
+    assert result == [
+        Completion(text='u2.userid = u.id', start_position=0, display_meta='fk join'),
+        Completion(text='u2.userid = users.id', start_position=0, display_meta='fk join'),
+        Completion(text='u2.userid = "Users".userid', start_position=0, display_meta='name join'),
+        Completion(text='u2.username = "Users".username', start_position=0, display_meta='name join'),
+        Completion(text='"Users"', start_position=0, display_meta='table alias'),
+        Completion(text='u', start_position=0, display_meta='table alias'),
+        Completion(text='u2', start_position=0, display_meta='table alias'),
+        Completion(text='users', start_position=0, display_meta='table alias')]
+
+@pytest.mark.parametrize('text', [
+    'SELECT * FROM users JOIN users u2 on foo.'
+])
+def test_suggested_join_conditions_with_invalid_qualifier(completer, complete_event, text):
+    position = len(text)
+    result = set(completer.get_completions(
+        Document(text=text, cursor_position=position),
+        complete_event))
+    assert set(result) == set()
+
+@pytest.mark.parametrize(('text', 'ref'), [
+    ('SELECT * FROM users JOIN NonTable on ', 'NonTable'),
+    ('SELECT * FROM users JOIN nontable nt on ', 'nt')
+])
+def test_suggested_join_conditions_with_invalid_table(completer, complete_event, text, ref):
+    position = len(text)
+    result = set(completer.get_completions(
+        Document(text=text, cursor_position=position),
+        complete_event))
+    assert set(result) == set([
+        Completion(text='users', start_position=0, display_meta='table alias'),
+        Completion(text=ref, start_position=0, display_meta='table alias')])
+
+@pytest.mark.parametrize('text', [
     'SELECT * FROM "Users" u JOIN u',
     'SELECT * FROM "Users" u JOIN uid',
     'SELECT * FROM "Users" u JOIN userid',