SQL Editor Assist and Auto Complete

[DmitryNeverovich]

Description

I as user I typed
following in SQL editor and call autocompletion:
select * from migration.fine_cat_center fcc where p|
(| - means cursor position here)

What I see in autocompletion list:

Actual: DBeaver proposes me list with Buildin functions on the top and list of matched columns in the bottom.

Expected: I want that DBeaver proposes me name of the column to avoid manual typing. It means that matched columns should be in the top of the list after everything else.

Description:
DBeaver autocompletion helped me to choose right table from schema and also proposed me variants of correct fields when I start typing from scratch after 'where'.

But I just started to type first letter of existing column (field definitely exists in the table and DBeaver knows it, because it proposed me previously) and I saw long long list of buildin functions on the top.

Also I know that it is possible to type table alias 'fcc.' and call autocompletion to get right options, but I just want to do simple select and have only one table in 'from' section.

Please help me understand how to get such behavior or maybe it is a bug or improvement.

My autocompletion settings are default:

DBeaver Version

Community Edition 25.2.3

Operating System

Windows 11, Database: postgres

Database and driver

Database: 17
Database driver: 42.7.2

Steps to reproduce

1. Just type any table and select select * from migration.fine_cat_center fcc where p|
   (| - means cursor position here)
2. in 'where' section start typing column name
   (see description)

Additional context

No response

[IvanGorshechnikov]

Hi @DmitryNeverovich

Thank you for your report. I have reproduced it
As a workaround, you can disable "Sort alphabetically proposals" and DBeaver should show columns on the top

[DmitryNeverovich]

Workaround works for me. What is the reason for enabling "Sort alphabetically proposals" by default?
I think it should be disabled by default for better user experience.

[ged-yuko]

@DmitryNeverovich

Here is the proposal comparison logic used to sort them:

• if Search inside names is enabled, then (filter by matching at any position in proposal text)
  • compare proposals by match score
    (which might bring proposal of any kind to the top position if it matches better than others)
  • if match score is equal, then
    • if Sort proposals alphabetically is enabled, then compare alphabetically
    • compare by proposal item kind (at this point in time by-match-preferred proposals are already at the top)
• else when Search inside names is disabled (filter by matching only the exact proposal prefix)
  • compare proposals by item kind
  • if items has the same kind and Sort proposals alphabetically is enabled, then compare them alphabetically

Basically the whole point of Search inside names is to apply fuzzy matching in any position of the proposal text, so there just is no point in sorting results by kind in the first place, because matching score might bring item of any kind to the top of the list. So there is nothing to fix here.

To me it looks like you might want to disable Search inside names. Then it'll use best-prefix match and sort alphabetically inside by kind groups (like first bring columns to the top, functions after them, then alphabetically for columns, then alphabetically inside for functions keeping them after the columns).
Looks like the thing you are expecting, because "first letter of existing column" is definitely not about "inside names".

[DmitryNeverovich]

@ged-yuko
From my experience column names are used more often during typing than 'builtin functions' other internal database things.
I think it would be better not to disable Search inside names - it should be in the result list, but maybe it needs to increase score of database columns to show it in the top.
disabling Search inside names also helps. Why is it enabled by default?

[ged-yuko]

@DmitryNeverovich
The score is a result of text comparison of the provided input fragment against proposed fragment. With Search inside names enabled it uses special fuzzy score algorithm (which returns a string similarity estimation and excludes proposals not containing the input fragment), otherwise it uses a dumb prefix match test (which excludes proposals not starting with the input fragment). The score doesn't estimate proposal kinds, its a separate comparison step to take proposal kind into account (see comparison algorithm description above).

See how it works with Sort alphabetically and Search inside names both enabled. Here is my users table:

When i am typing just r, it shows everything which starts with it, functions on top because role field cannot be just accessed without extra steps:

With ro it now is observable - inbetween of functions! - because proposal doesn't start with ro but the word inside of it starts with ro, while the next proposal contains ro inside of the word (this is the fuzzy score effect):

With rol it now is on top because there is nothing else starting with rol so no better candidates, and down below are all the proposals considering possible mistype:

If i remove the table alias and type ro the chance of me assuming role is higher because it is more similar to how it can be addressed of the provided proposals, so it goes higher in the list:

Note group of the Rotate-somethig functions, they have the same matching score, so they are sorted alphabetically among themselve.

The point is: forcing columns to be always on top will completely break the whole logic behind how Search inside names work.