Completion options when using schema.table aliases lists non-existent columns - pgsql + nvim-cmp about vim-dadbod-completion HOT 6 CLOSED

I have an old and low priority todo for myself to add scoping to schema, but I never got to it since I didn't run into this issue myself, so it didn't bother me much.

Parsing aliases from current query is too simple to be reliable, that's why i return everything that matches for it. Better to have more things than to miss something.

I believe that in real life database you will not have these issues, since table naming should be better and longer, and short aliases should work out fine.

Anyway, thanks for reporting. Now I at least know that someone really ran into these issues.

from vim-dadbod-completion.

Does it happen if you use any other alias, or only with sp? Are you using any other source beside dadbod-completion for the sql buffer?
Also, what happens if you do omni completion (<C-x><C-o>) after sp.? Are results ok in that case?

from vim-dadbod-completion.

Yes it happens with other aliases as well, it seems that name and id are always included in the list despite the schema.table that I'm attempting to alias. The other incorrect entries appear to be a mix of table names or other column/schema names at times.

The only other source I'm using for sql buffers is buffer, and now that I've fixed the menu text with your help I can see the source is all from Text [DB].

Trying to use the omni completion with an alias also provides the same incorrect entries, but switching to the full schema.table shows the correct column list with both omni-completion and vim-dadbod-completion.

from vim-dadbod-completion.

Do you happen to have same tables in multiple schemas?
Also, is there any chance to get access to your db, or at least just a structure dump?
It's hard to debug this without the database, I can only guess what's happening.

from vim-dadbod-completion.

I used this to setup the sample adventure works database for postgres

https://github.com/lorint/AdventureWorks-for-Postgres

I'm not as familiar with the setup of this database as yet, as I'm using for learning/testing. However your comment made me do some more digging.

Yes! There is a table named 'sp' albeit under the 'pe' schema. And it has all the columns that are coming through when attempting to use the alias 'sp'.

Also, there's also a table named 's', under the 'hr' schema, which I imagine has the same column values that are coming through when trying to use that as an alias.

I guess there's no way for the completion to realize if you're attempting to complete from an alias vs from a column from a table even if it's in a different schema?

Is there another way to get the behavior I was expecting (i.e. force full reference of schema.table.column for completion results)? Or is it just ignorance on my part? If so I apologize for the noise.

from vim-dadbod-completion.

Closing this issue as it appears to be an ignorant mistake on my part (I was attempting to use aliases that were already table names in the database).

Ideally, autocomplete would be able to, in the select or where blocks, limit the scope of returned columns to only the tables listed in the from block of the current query, though I understand that's probably not possible in practice.

Thanks for creating and sharing this very useful plugin.

from vim-dadbod-completion.