MySQL Bugs: #80092: SQL code assist not working in basic cases

Bug #80092	SQL code assist not working in basic cases
Submitted:	21 Jan 2016 9:01	Modified:	19 May 2016 7:12
Reporter:	Mauro Molinari	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S2 (Serious)
Version:	6.3.6	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	auto-complete, auto-completion, code assist, code completion

Description:
Although bug #76954 is marked as fixed, I still have problems to make code assist work in a decent way even in the simplest cases. I'm pretty sure this worked well in MySQL Workbench 6.2.x.

First example: I have many schemas and, in one of them (the default one) I have a table named MY_TABLE with the following columns:
id, idServizio, dataRinnovo, dataProssimaScadenza, casellaRinnovata, idPropostaPadre, rinnovoConfermato, rinnovata.

I open a SQL editor and type this in the first line:
SELECT * FROM myschema.MY_TABLE WHERE |

At | I invoke code assist with ctrl+space and I would expect to see, at least as the FIRST suggested entries, the column names above. What I see, instead, is this list:
KEYW binary
KEYW case
KEYW exists
KEYW interval
KEYW not
SCMA another_schema_1
SCMA another_schema_2
...
TABL another_table_1
TABL another_table_2
...
VIEW view1
VIEW view2
...
ROUT function1
ROUT function2
...
COLN a_column_in_any_table_1
COLN a_column_in_any_table_2
...
LIBF and SVAR entries

where:
- another_schema_1, another_schema_2, ... are other schemas in the server instance I'm connected to (it's a looong list, that also includes my current default schema, because I have many schemas)
- another_table_1, another_table_2, ... is the list of all the tables of the default schema
- view1, view2, ... is the list of all the views of the default schema
- function1, function2, ... is the list of all the functions of the default schema
. a_column_in_any_table_1, a_column_in_any_table_2, ... is the list of ALL the columns of ALL the tables in the default schema... a extremely looong list

In other words, the code assist is totally useless because is shows EVERYTHING (even things that can't be applied there) and in an apparently unreasonable order. What I would expect to see FIRST is the list of all the columns of MY_TABLE, then I'd say applicable MySQL functions, etc... I mean, I'm expecting to see just the entries I both can and most likely want to put there. It's useless that MySQL Workbench shows me the columns of all the other tables!! Not only useless, but also detrimental, because it leads me to write an invalid/wrong query!

Another example, this time using aliases:
SELECT * FROM myschema.MY_TABLE t WHERE t.|

If I invoke code assist at |, by pressing ctrl+space, nothing is shown (no popup window with suggestions is shown).

How is that code assist does not work in these two basic cases? How is it supposed to work then?

How to repeat:
Simply open the SQL editor and start typing a basic select query like the mentioned ones.

Hello Mauro Molinari,

Thank you for the report.

Thanks,
Umesh

Mauro, can you tell me what you consider wrong in the first example? If you didn't type anything yet WB will of course show you all possible names and with many objects in the server you get a long list. Start typing and you will see that the list quickly gets much shorter.

The order in the list must always be the same otherwise it's quickly getting highly confusing. So atm we have keywords first, then schemas, tables and column (and more after that). What would you suggest instead? Showing columns first? But what about the cases where you need a keyword instead? Or a schema? Or a table? Isn't that the same situation again, just with a different type?

WB cannot read your mind, so it doesn't know what you are most interested in when you invoke code completion. Hence it shows you all possible input. If you have a better idea what to do then please let me know.

Hi Mike!
Of course the tool can't read my mind, by any modern IDE that provide code assist applies context-sensitive suggestions.
If I'm in the WHERE clause (at the beginning or after an operator, for instance), the most obvious things to suggest are column names or already defined aliases. Probably other keywords or object names are applicable, but less likely to occur, so they should just appear after the most probable ones.
In any case, I don't think EVERY keyword/object name would be valid to insert there, whatever query you're writing, so they should simply be omitted from the suggestion list.
For instance, column names of tables I've not mentioned in my FROM/JOIN clauses cannot be typed there, unless qualified with their owning table name. And tables of other schemas must in turn be qualified with their schema name, so they shouldn't be suggested unless I've already typed the "otherSchema." prefix.

If reordering the suggestion list based on context is too complicated, at least removing entries that certainly can't occur would be very useful. Code assist is not usually just a way to type less, but should also help to type right.

I meant "BUT any modern IDE"...

Thanks for your reply Mauro. I guess we disagree on what is "obvious" to show. In a WHERE clause, right at the start, not only columns are common. I have seen many queries which use totally other constructs like functions, CASE, EXISTS etc. Should user who prefer those be searching now, just to satisfy other users who prefer to see columns first?

And for the columns: when you look carefully, you will see that the candidates list only includes those columns that can directly be used without a qualifier (i.e. those from the given table references). This is however not yet perfect as it requires to scan the query beyond the caret position which might contain invalid code.

Similar for keywords: you get exactly only those that are allowed in a given position (e.g. only 11 at the start of a WHERE clause).

One possible optimization in the list is to sort candidate types by likelyhood or by frequency of usage. However, I believe this creates as many problems as it solves, because it changes the order of the candidate groups constantly.

Another option is to only show a short list of most likely candidates and have an expand entry or similar that allows to show the full list (and also use the full list if the already typed input does not match anything in the initial list). This would at least avoid reordering things.

However, nothing of that is a bug, but just something to improve the current implementation. The not working second case however is and it's already being handled as we speak.

Hi Mike!

>In a WHERE clause, right at the start, not only columns are common. I have seen many queries which use totally other constructs like functions, CASE, EXISTS etc

I didn't say they can't be used, but I also have many years of experience of SQL and, honestly, how often you simply put a condition like "id = ?" or "name like 'foo%'" or simple conditions like these in a where clause compared to the cases in which you use CASE, EXISTS etc. functions? This is why I said IMHO column names should be suggested before functions...

>And for the columns: when you look carefully, you will see that the candidates list only includes those columns that can directly be used without a qualifier

No, this is not the case, I'm sure. As I wrote in my report, I see the list of ALL the columns of ALL the tables in my default schema, when I simply type:
SELECT * FROM myschema.MY_TABLE WHERE |
and invoke code assist at |.
I would expect to see just the columns of MY_TABLE instead!

>One possible optimization in the list is to sort candidate types by likelyhood or by frequency of usage. However, I believe this creates as many problems as it solves, because it changes the order of the candidate groups constantly

I do not agree it would be worse. Recent task-focused and context-focused projects at Eclipse, for instance, are exactly going in this direction and they are certainly successful. I'm thinking of Mylyn and Code Recommenders, for instance. But also the plain Java Development Tool plugin alone sorts suggestions by relevance depending on the context in which code assist is invoked. For instance, if I'm within a method body, Ctrl+Space will show methods before class names, because it's much more likely that you want to invoke an instance method of the owning class rather than to type a class name to qualify the invocation of some static method of some other class.

>Another option is to only show a short list of most likely candidates and have an expand entry or similar that allows to show the full list

Just as an example of an alternative approach, in Eclipse hitting "Ctrl+Space" repeatedly shows different lists of suggestions. In this case, the first list may be focused and the second may contain the full list.

>However, nothing of that is a bug, but just something to improve the current implementation

As I said, I'm still sure the list of columns in my first example shows columns of different tables, and I think it's a bug too.

Some food for thoughts. Thanks Mauro.

Posted by developer:
 
Fixed as of the upcoming MySQL Workbench 6.3.7 release, and here's the changelog entry:

Code completion has been further improved to only show relevant details,
such as if a table is referenced in the FROM clause then only show columns
from that table and not all from the default schema, and entries have been
reordered to show columns first (only preceded by keywords).

Thank you for the bug report.

Great news, I look forward to trying 6.3.7 release!