Completion proposals' flaws for tables and columns with nonstandard escaping
- 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_:

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_:

Itentionally checked with aliases. For column aliases it is correct as well:

Here is _correct:_

Here the empty completion list is _wrong_, expected the same as above:

### 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_:

(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
Assessment
This issue has not been assessed yet.