Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

autocomplete on table fields stopped working #917

Closed
anselal opened this issue Feb 27, 2020 · 32 comments
Closed

autocomplete on table fields stopped working #917

anselal opened this issue Feb 27, 2020 · 32 comments
Labels
Milestone

Comments

@anselal
Copy link

@anselal anselal commented Feb 27, 2020

Steps to reproduce this issue

  1. Start typing a query
  2. assign an alias to a table eg. d
  3. type d. or press control+space
  4. Then I get nothing

Current behavior

autocomplete on table fields does not work

Expected behavior

It should display a popup with the names of field of the specific table

Possible solution

revert a commit ???

Environment

  • HeidiSQL version: 10.3.0.5901
  • Win 10 Pro 64bit
  • MySQL 5.5.62
@anselal anselal changed the title autocomplete stopped working autocomplete on table fields stopped working Feb 27, 2020
@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

@ansgarbecker ansgarbecker commented Feb 27, 2020

This is due to pull request #914 for issue for issue #895 , so let us fix it there. Thanks however for the quick feedback... just a few hours after committing the related change :)

@anselal

This comment has been minimized.

Copy link
Author

@anselal anselal commented Feb 27, 2020

No problem. I am using HeidiSQL everyday so issues like this are important to be fixed.
thanks for the great work

@zilzila

This comment has been minimized.

Copy link

@zilzila zilzila commented Feb 27, 2020

@anselal, could you please provide a test query with the name of the table and database and the result of

select @@global.lower_case_table_names;

Is the database in the tree on the left selected?

@anselal

This comment has been minimized.

Copy link
Author

@anselal anselal commented Feb 28, 2020

don't really want to expose my table and database name.
Any query fails to autocomplete the table fields except in the incormation_schema database.

The output of the select @@global.lower_case_table_names; is 0

@zilzila

This comment has been minimized.

Copy link

@zilzila zilzila commented Feb 28, 2020

I had in mind a test query, not a real one, to understand what type of identifiers are used (e.g. CamelCased or `quoted`).
For example, a query like select * from table_name d where d. works fine.
I suppose the query was like select * from TableName d where d. or select d. from TableName d, where were the CamelCase identifiers, right?

@anselal

This comment has been minimized.

Copy link
Author

@anselal anselal commented Feb 28, 2020

Oh, sorry I didn't get that.
the queries I write are always lowercase like select * from table_name d where d.

@zilzila

This comment has been minimized.

Copy link

@zilzila zilzila commented Feb 28, 2020

In this test, I have autocomletion popping up:

-- drop table my_test_table;
create table my_test_table (id int);

Refresh current database in DBTree (on the left).

select * from my_test_table d where d. 

Any query fails to autocomplete the table fields except in the incormation_schema database.

Maybe the database information_schema in DBTree was selected?

@anselal

This comment has been minimized.

Copy link
Author

@anselal anselal commented Feb 28, 2020

no, i have selected the correct database, else the query select * from _test_table would not run.

@anselal

This comment has been minimized.

Copy link
Author

@anselal anselal commented Feb 28, 2020

I tried your example and it works. I think that the problem is that my table names start with an Underscore

@zilzila

This comment has been minimized.

Copy link

@zilzila zilzila commented Feb 28, 2020

Tables with leading underscore also work in my case:

create table _my_test_table (id int);

select * from _my_test_table d where d.
@anselal

This comment has been minimized.

Copy link
Author

@anselal anselal commented Feb 28, 2020

Your examples work for me too, but not for tables I already have.
Don't know what to do.
Will try to reinstall HeidiSQL and let you know if that fixed the issue

@anselal

This comment has been minimized.

Copy link
Author

@anselal anselal commented Feb 28, 2020

The issue still remains.
It works in previous versions though, like stable 10.3

@zilzila

This comment has been minimized.

Copy link

@zilzila zilzila commented Feb 28, 2020

Thank you!
The problem is that I cannot reproduce this issue...

@anselal

This comment has been minimized.

Copy link
Author

@anselal anselal commented Feb 28, 2020

How does autocomplete work under the hood? Does it lookup information_schema? I would think that my information_schema got corrupted but as I mentioned it works under previous versions of HeidiSQL.

@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

@ansgarbecker ansgarbecker commented Feb 28, 2020

The IS schema is queried when you click a database or a table. The auto-completion just uses the details from these queries.

With the new logic for #914, I can also reproduce non-working completion, e.g. with comments:

SELECT *
FROM mytable s -- comment
WHERE s.<< nothing pops up
@ansgarbecker ansgarbecker reopened this Feb 28, 2020
@anselal

This comment has been minimized.

Copy link
Author

@anselal anselal commented Feb 28, 2020

I just noticed that autocomplete works without a table alias

SELECT *
FROM mytable
WHERE mytable.<< it pops up
@yphastos

This comment has been minimized.

Copy link

@yphastos yphastos commented Feb 28, 2020

I don't know if this may help...

I have the same problem with autocompleting column names for tables, and noticed that in the log, the information from information_schema, and a few other queries like show function status... show procedure status... etc is being attempted to obtain with the schema name (database name) in lowercase, instead of the correct case, eg.

My DB name is DEVEFERH but is being searched as deveferh in lowercase, generating a 1049 error:

image

I also noticed that autocompleting the TABLE names (instead of columns) does work, because in such case, the DB name seemingly is queried correctly, with the correct letter case

I hope that can help to solve the issue ASAP

@zilzila

This comment has been minimized.

Copy link

@zilzila zilzila commented Feb 28, 2020

Yes, at the moment there is an incorrect handling of case-sensitive identifiers of MySql (with lower_case_table_names = 0). Now I'm working on this issue.

@zilzila

This comment has been minimized.

Copy link

@zilzila zilzila commented Feb 28, 2020

I made fixes and pull request #920. Please report the test results.

@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

@ansgarbecker ansgarbecker commented Feb 29, 2020

I just merged your pull request, next nightly build will be there in a few minutes.

@anselal

This comment has been minimized.

Copy link
Author

@anselal anselal commented Feb 29, 2020

The issue still persist in 10.3.0.5903

@zilzila

This comment has been minimized.

Copy link

@zilzila zilzila commented Feb 29, 2020

In order to find out where the error is and try to reproduce it, it would be nice to have data about the structure.
I found a way to solve the problem with private data! :) Replace all identifiers with aaa, bbb, ccc, etc., do obfuscation:
MyTableName.Column_1 -> AaAaaaAaaa.Bbbbbb_1 and so on.
Substitutions for identifiers must be the same, so that in the query and in the show create table ... substitutions correspond to the same column.

Please send the following:

  1. An exact query with a problem table, without removing spaces, underscores, other characters other than a-z, A-Z, comments, without deleting/making new line breaks, etc. Only obfuscation of identifiers.
  2. Result of show create table <table_name>; (columns Table and Create Table, obfuscated).
  3. The result of show create database <db_name>; (columns Database and Create Database, obfuscated).
@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

@ansgarbecker ansgarbecker commented Feb 29, 2020

Until we have an exact reproduction recipe, you could fix the other one from my comment, using a comment after the table alias.

@zilzila

This comment has been minimized.

Copy link

@zilzila zilzila commented Feb 29, 2020

Yes, I’m doing it right now.
Unfortunally, TRegexpr of this version does not support non-capturing groups (?: ), This is very lacking and complicates the code.
I looked at the SynEdit repository, it also has an old version of TRegExpr, although on the TRegExpr developer site you can download a version where (?: ) is already supported.
Is the SynEdit project already abandoned and not developing? The last commits were a few months ago, there are several pull requests and I did not notice any activity from the SynEdit developers ...
Look-ahead and look-behind modifiers (?= ) and (?! ) are also sorely lacking, but TRegExpr developer explicitly states that there is no support and is not expected.
Maybe it makes sense to look for another class/package/etc to handle regular expressions, with normal support for modern modifiers and functionality?

@ansgarbecker ansgarbecker added this to the v11.0 milestone Feb 29, 2020
@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

@ansgarbecker ansgarbecker commented Feb 29, 2020

@zilzila I have that TRegExpr update on the roadmap (#819). But although I planned to do that update for the next release, I decided to postpone that to a later one, as the next one is very soon (just a few days left). So I'd like to roll back the relevant commits now, and redo it for the next release, so you have more time to test and to fix remaining issues. Ok?

@zilzila

This comment has been minimized.

Copy link

@zilzila zilzila commented Feb 29, 2020

Ok!

Perhaps it is better to make a branch for autocompletion? Because, as it seems to me, there are quite large changes coming that can affect many program modules.
I am talking about supporting spaces and other "forbidden" characters in quoted database names, Conn.Database. I have not watched it yet, but I believe that it will affect almost every piece of the program :) Here it will be necessary to test everything very carefully.
Also in my task list there is a task to deal with single quotes so that they do not double in quoted identifiers.
And I also want to parse the subqueries and then remove them from the FROM clause and add both alias and subquery columns to the proposal list.

A separate branch will allow committing and testing without affecting the master branch. And as soon as it is ready, it will be possible to merge into the master branch.
I do not know which option is better, you decide.

P.S. Updating to the latest version of TRegExpr is great!

@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

@ansgarbecker ansgarbecker commented Feb 29, 2020

I'd like to get just the problem from #895 fixed. This should be fixable without such large changes.

@zilzila

This comment has been minimized.

Copy link

@zilzila zilzila commented Mar 1, 2020

With the new logic for #914, I can also reproduce non-working completion, e.g. with comments:

SELECT *
FROM mytable s -- comment
WHERE s.<< nothing pops up

I made a fix for comments in the FROM table section, committed it to my fork.
What should I do now, create a pull request, just wait, or something else?

@anselal

This comment has been minimized.

Copy link
Author

@anselal anselal commented Mar 2, 2020

@ansgarbecker thnx, seems it has been fixed now !!!

@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

@ansgarbecker ansgarbecker commented Mar 2, 2020

@zilzila I'd like to keep it more simple, as written above. The issue with case sensitivity should be fixed already.

@zilzila

This comment has been minimized.

Copy link

@zilzila zilzila commented Mar 7, 2020

Sorry for not responding so long, there was a lot of work.

I would also like to have as few changes as possible, but starting to make changes, I saw that one clings to the other and for the full implementation of autocompletion it will still be necessary (IMO) to do the handling of quoted identifiers and make other changes, as I described above.
So I'm afraid that for the full implementation of autocompletion there will be a lot to change. So I decided to take up this and implement a new logic. But if you prefer to leave it as it is, then no problem.

I’ll write my additions/tests of the current implementation of autocompletion in #895

@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

@ansgarbecker ansgarbecker commented Mar 8, 2020

I like the current implementation of auto completion, and I want to keep it due to the effort I already have put into it. In case of issues, I want a more punctual fix, without adding much more code than it already has.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
4 participants
You can’t perform that action at this time.