dbeaver / dbeaver/dbeaver

Completion proposals' flaws for tables and columns with nonstandard escaping

Open
#37,430 1 comment 0 reactions 0 assignees View on GitHub
xf:sql autocompletion
Dominant language
Java
Stars
51.8k
Forks
4.4k
Avg merge
3d 8h
Merged PRs (30d)
188

Description

### Description

There is an issue https://github.com/dbeaver/dbeaver/issues/37395 about single-quote literals handling in the new completion engine, which brought me to a small research with SQLite escaping rules just as one of the examples, where the default assumptions about the completion behavior could be wrong.

So here are a few cases where the completion works ok vs wrong:

The following is _valid_:
![Image](https://github.com/user-attachments/assets/ea5e1618-467d-4aa7-9b4f-da96995653db)
The following is _not_... i imagine it working correctly some time before 25.0, which is strange, but for now the table reference context is messed up with columns list context propositions, because _columns expected here_:
![Image](https://github.com/user-attachments/assets/5cb99c12-77f2-4b46-826e-2eb2197f9f53)
Itentionally checked with aliases. For column aliases it is correct as well:
![Image](https://github.com/user-attachments/assets/3d247769-fc56-4e47-8da3-392150166b58)
Here is _correct:_
![Image](https://github.com/user-attachments/assets/cdfed8b2-1bcd-49ba-ae6f-0f0b9b4cdfe8)
Here the empty completion list is _wrong_, expected the same as above:
![Image](https://github.com/user-attachments/assets/4ea88140-31d9-45e6-8667-074782c86040)

### DBeaver Version

Community Version 25.0.0.202503021833

### Operating System

Windows 10 Pro 10.0.19045

### Database and driver

SQLite JDBC 3.35.30
The one bundled with DBeaver.

### Steps to reproduce

Use provided example queries and invoke completion engine in various positions around the escaped identifiers.

### Additional context

Here are a few _valid_ queries, where signle-quote character acts as an escapement of identifer.
_It is expected to accomplish all of these single-quoted not-a-string-literals as columns or tables._
```
create table t(
id int,
value text -- it is also legitimate to escape with single quote here or in alter statements, but lets focus on queries
);

-- consider tables a and b defined the same way as t above
select * from a join b using (value);
select * from 'a' join b using (value);
select * from a join 'b' using (value);
select * from 'a' join 'b' using (value);
select * from 'a' join b using ('value');
select * from a join 'b' using ('value');
select * from 'a' join 'b' using ('value');

select t.'value' from t;
select t.'value' from 't';
select 't'.'value' from t;
select 't'.'value' from 't';

select t.value from t;
select t.value from 't';
select 't'.value from t;
select 't'.value from 't';

```
There are no string literals in these queries. All of this is also correct for WHERE clause, so this completion list is _valid_:
![Image](https://github.com/user-attachments/assets/2dd8c235-efb3-4bdb-998c-700aaee0be3b)
(which partially contradicts to #37395 but is true and expected)

PS Sadly enough, the author of #37395 assumed me trolling, however this is what we have. Sorry if the way i formulated my comment in that thread made someone feeling offended. There was no such intention. I do agree that the behavior requires refinement, as described here, as well as the whole completion engine, but **it is not completely wrong**. So the point is to fix the thing instead of stop supporting, as implied by #37395. Details do matter, and to support such a variety of deviations from SQL Standard is a great work benefitting us users.

With love to completion engine and highlighting developers!

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.