Completion is inconsistent

[mytskine]

What is the current behavior:

1. If I type select * from use, the first suggestion is user_devices and the 11th is user (with backticks for this one), then other tables like chat_users, and much later come sparse completions like survey_answers.
2. If I type select * from cou, the first suggestion is course, then other tables like course_categories, later enrol_lti_lti2_consumer, then backup_courses.
3. If I type select * from _cons, there is no exact match in the near 20 suggestions. I need to add an extra letter (_consu) to find the exact match enrol_lti_lti2_consumer.

What I expected:

• The table names starting like what I typed should always come first, in alphabetic order. In case 1, user should have been the first result, just like course was the first result to 2. Backticks should not interfere in the sorting.
• Exact matches should always come next. In case 2, backup_courses should precede enrol_lti_lti2_consumer (where cou is not present). In 3, the exact matches could only be found through scrolling, which is not the expected behavior.

Other grieves (I won't create an issue for each one, feel free to do so):

• There should be a configuration switch to disable sparse matches. IMO they just adds noise to the suggestion list, as case 3 clearly shows.
• The completion should be sensitive to upper case input, so that Cou should match Course but not discourse.
• Pressing Escape while in the suggestion list should cancel it.
• Pressing ctrl-g does cancel the suggestion list, but it should restore the user input.

[schmeic]

I've noticed some other inconsistencies.

It seems like mycli tries to be smart about when a completion could be only an sql keyword or a column, and will only show completions for one type. For example, SELECT COUNT( seems to assume that only a column name will work here, so no completion for DISTINCT. Seems like it would be better to not be so "smart", and always show both sql keywords and column names.

Another strange one is that sometimes mycli will only show the completion for an exact match, even if there are other available completions. For example, I have a table called requirement with column called is_active. When I do this:
SELECT is FROM requirement, with the cursor after the is I see completions for both is_active and IS. However, here:
SELECT * FROM requirement WHERE is the completion for is only shows IS. Why show only the exact match in the second case?

I've also come across situations where I get no completion choices at all, even though there should be some.

[rolandwalker]

I've converted this Issue to a Discussion since it is too general to be actionable.

In addition, many of the points raised here have been improved over time.