Table Completions Not Working When Typing Table Names

[Artmann]

Table name autocompletion does not work when typing after FROM clause. Keywords like SELECT, FROM, ALTER work correctly, but table
suggestions return 0 items even though the schema is successfully loaded with tables.

Environment

• Package: @deepnote/sql-language-server
• Database: PostgreSQL
• Schema: 27 tables loaded successfully

Test Data

Sample Schema

  const testSchema = {
      tables: [
          {
              catalog: null,
              database: "squeal",
              tableName: "actor",
              columns: [
                  { columnName: "actor_id", description: "actor_id(Type: integer, Null: false, Default: null)" },
                  { columnName: "first_name", description: "first_name(Type: text, Null: false, Default: null)" },
                  { columnName: "last_name", description: "last_name(Type: text, Null: false, Default: null)" }
              ]
          },
          {
              catalog: null,
              database: "squeal",
              tableName: "actor_info",
              columns: [
                  { columnName: "actor_id", description: "actor_id(Type: integer, Null: false, Default: null)" },
                  { columnName: "first_name", description: "first_name(Type: text, Null: false, Default: null)" },
                  { columnName: "last_name", description: "last_name(Type: text, Null: false, Default: null)" },
                  { columnName: "film_info", description: "film_info(Type: text, Null: false, Default: null)" }
              ]
          },
          {
              catalog: null,
              database: "squeal",
              tableName: "film",
              columns: [
                  { columnName: "film_id", description: "film_id(Type: integer, Null: false, Default: null)" },
                  { columnName: "title", description: "title(Type: text, Null: false, Default: null)" }
              ]
          },
          {
              catalog: null,
              database: "squeal",
              tableName: "film_actor",
              columns: [
                  { columnName: "actor_id", description: "actor_id(Type: integer, Null: false, Default: null)" },
                  { columnName: "film_id", description: "film_id(Type: integer, Null: false, Default: null)" }
              ]
          },
          {
              catalog: null,
              database: "squeal",
              tableName: "customer",
              columns: [
                  { columnName: "customer_id", description: "customer_id(Type: integer, Null: false, Default: null)" },
                  { columnName: "name", description: "name(Type: text, Null: false, Default: null)" }
              ]
          }
      ],
      functions: []
  };

Manual Test Cases Table

SQL Input	Cursor Position	Expected Completions	Actual (Before Fix)
SELECT * FROM a	col 15 (after 'a')	actor, actor_info	[] (empty)
SELECT * FROM ac	col 16 (after 'ac')	actor, actor_info	[] (empty)
SELECT * FROM act	col 17 (after 'act')	actor, actor_info	[] (empty)
SELECT * FROM fil	col 17 (after 'fil')	film, film_actor	[] (empty)
SELECT * FROM cus	col 17 (after 'cus')	customer	[] (empty)
SELECT * F	col 10 (after 'F')	FROM	FROM ✓
SELECT * FROM	col 14 (after space)	all tables	[] (empty)

Steps to Reproduce

1. Connect to a PostgreSQL database with tables
2. Verify schema loads successfully (see schemaLoaded notification)
3. Type SELECT * FROM a and trigger autocomplete at the end
4. Expected: Table suggestions starting with "a" (e.g., actor_info)
5. Actual: 0 completion items returned

Root causes

Issue 1: addCandidatesForParsedSelectQuery doesn't offer table suggestions

Location: src/complete/complete.js - addCandidatesForParsedSelectQuery()

When typing SELECT * FROM a, the SQL parser successfully parses the statement (treating a as a valid table identifier). The code
then goes through addCandidatesForParsedSelectQuery(), which only offers:

• Basic keywords (SELECT, FROM, AS, DISTINCT)
• JOIN candidates
• Column candidates

It does not offer table candidates. Table candidates are only offered in the error handling path via addCandidatesForSelectQuery().

Fix: Add table suggestions in addCandidatesForParsedSelectQuery() when the cursor is inside a FROM clause table node:

// In addCandidatesForParsedSelectQuery(), add before columnRef check:
const fromClause = ast.from;
if (fromClause && fromClause.tables) {
    for (const tableNode of fromClause.tables) {
        if (tableNode.location && isPosInLocation(tableNode.location, this.pos)) {
            this.addCandidatesForTables(this.schema.tables, true);
            return;
        }
    }
}

Issue 2: createCatalogDatabaseAndTableCandidates uses fully qualified names for matching

Location: src/complete/candidates/createTableCandidates.js

Even after Issue 1 is fixed, table completions still return 0 items because:

1. The function calculates qualificationLevel from lastToken.split('.').length - 1
2. For lastToken = 'a', qualificationLevel = 0
3. For a table with database = 'squeal', qualificationNeeded = 1
4. qualificationLevelNeeded = 1 - 0 = 1, so code enters case 1: which creates database identifiers, not table identifiers
5. Even if it did create table identifiers, it uses getFullyQualifiedTableName() which returns squeal.actor_info
6. Identifier.matchesLastToken() checks "squeal.actor_info".startsWith("a") which is FALSE

Fix: When user types without dots (qualificationLevel === 0), match against just the table name:

function createCatalogDatabaseAndTableCandidates(tables, lastToken, onFromClause) {
    const qualificationLevel = lastToken.split('.').length - 1;
    const qualifiedEntities = tables.flatMap((table) => {
        // ... existing qualification calculations ...

        // Always include table name suggestions when user hasn't typed a dot
        // This allows typing "act" to match "actor_info" even if table is in a database
        if (qualificationLevel === 0) {
            // User is typing without dots - suggest table names directly
            const tableIdentifier = new Identifier(
                lastToken,
                table.tableName,  // Use just tableName, not fully qualified
                '',
                ICONS.TABLE,
                onFromClause ? 'FROM' : 'OTHERS'
            );
            return [tableIdentifier];
        }

        // ... rest of existing switch statement for qualified names ...
    });
    // ...
}

[Artmann]

Fixed by #25