auto-complete behaviour

Didnt' update heidi in a while so i went from 23xx to the latest revision today. I must say: i'm impressed with the new features! Well done!!

The reason for this post though is one feature that used to work fine (more or less) but that has become useless to me in this new revision. The auto-complete.

Before, whenever i typed a dot, Heidi would look in my query for the relevant table and then list the possible colums for me. Which saved me a lot of typing. Also, this feature became a vital necessity since the revision where Heidi stopped displaying the table-fields in the menu on the left.

Now, when i type a dot, i get a list of all my databases (100+), tables in the current database, functions, keywords....but not the fields of the table - which is what i need. Is this a bug or intended behaviour?

Yesterday I committed r2552 in order to fix issue #1242. Obviously I broke something as you complain about missing columnsCould you please post the exact SQL code which does not work for you anymore so I can fix that?

I narrowed it down a little: it appears it's not working when there's more then 1 sql statement in the same query tab... so i'm thinking now that maybe this has changed because heidi expects you to open a new tab for every new query?

To reproduce:

SELECT * FROM Table1
SELECT * FROM SomeOtherTable AS SOT WHERE SOT.

All my previous revisions listed the table-fields for me when i typed that last dot. The current revision doesn't do that anymore.

Can confirm a bug here. But that was not caused by my recent commit. Even in r2523 that wasn't working, but anyway, should be easy to fix.

Should be better now in r2553 - except for the query as you typed above, which doesn't contain a semicolon as query seperator. No chance to find out that the first part doesn't belong to the relevant query.

Should be better now in r2553

I still get this error:

/* SQL Error: Table 'test.SOT' doesn't exist */

So it's still broken i'm afraid

[...] the query as you typed above, which doesn't contain a semicolon as query seperator. No chance to find out that the first part doesn't belong to the relevant query.

A SELECT statement as part of a subquery should always be preceded by zero-or-more whitespaces and one-or-more opening brackets, shouldn't it?
And since that is not the case, that last SELECT (the first one searching backwards from the dot) has to be the start of the query.

Or am i overlooking something?

But then again...i could also learn to type semicolons behind my queries ;-)
It's not really a big thing!

Since it's still broken, i filed a bug report:

http://code.google.com/p/heidisql/issues/detail?id=1317

A SELECT statement as part of a subquery should always be preceded by zero-or-more whitespaces and one-or-more opening brackets, shouldn't it?

Whitespace: no, AFAIK.
Brackets: if you mean parantheses, yes, AFAIK.
Supported: don't think the syntax helper supports sub-queries (it has bigger problems that needs to be sorted out).

SELECT * FROM Table1

SELECT * FROM SomeOtherTable AS SOT WHERE SOT.

There's no sub-queries in the above. And unless you add a ";" after Table1 to form a batch of two separate queries, the SQL is invalid too. Is there a point to this particular example? As in, do you normally avoid terminating each statement for some reason?

And since that {presence of brackets} is not the case, that last SELECT (the first one searching backwards from the dot) has to be the start of the query.

Or am i overlooking something?

Depends very much if you're talking about this particular (invalid) SQL, or if you're suggesting that a parser which works by scanning backwards, counting parantheses and looking for the word "SELECT" would be great idea.

Whitespace: no, AFAIK.

This syntax is valid and has a whitespace:

SELECT * FROM table WHERE someId IN ( SELECT id FROM table2)

Brackets: if you mean parantheses, yes, AFAIK.

Yes, that's what i meant.

SELECT * FROM Table1

SELECT * FROM SomeOtherTable AS SOT WHERE SOT.

There's no sub-queries in the above. And unless you add a ";" after Table1 to form a batch of two separate queries, the SQL is invalid too. Is there a point to this particular example? As in, do you normally avoid terminating each statement for some reason?

I know it doesn't contain a subquery. The point i made with the subquery was a reply to anse's answer that the auto-complete didn't work because the first query lacked a ; and that because of that there was no way to seperate the two. I was only trying to point out that a normal query could have a subquery in which you have a similar situation witout ; but that it is still possible to seperate the two SELECT statements.

Apart from that: i had written two queries in the same query tab, which i do often and then execute with CTRL+F9. That's why there are two select statements there. But yes: of course i could just learn to type a ; behind every query :)

Depends very much if you're talking about this particular (invalid) SQL, or if you're suggesting that a parser which works by scanning backwards, counting parantheses and looking for the word "SELECT" would be great idea.

I was suggesting the backwards scan :)

This syntax is valid and has a whitespace:

Yes, but you said "should always have whitespace", which is incorrect.

I was only trying to point out that a normal query could have a subquery in which you have a similar situation witout ; but that it is still possible to seperate the two SELECT statements.

Ok. Seems different since one has the proper parentheses and the CTRL-F9 example doesn't, but whatever.

Apart from that: i had written two queries in the same query tab, which i do often and then execute with CTRL+F9. That's why there are two select statements there.

Ah. Makes much more sense now! ;-)

I was suggesting the backwards scan :)

Then that must have been the absolutely shortest specification for a parser I've ever seen. You're clearly insane ;-).

Nah, just kidding. The Perl PCRE engine parses text from the back to the front, so maybe it's a fantastic approach. Anyway, I'm looking forward to a full spec or prototype implementation :-).

A purely backward scan won't work without counting parenthesis, as you can have sub-queries, eg

SELECT t1.*, t2.* FROM table1 AS t1,
(SELECT * FROM table2 WHERE ...) AS t2
WHERE t1.

At that point a backward scan for SELECT would find the one in parens. So then you'd have to start counting closing parans, and un-counting them at the open, to make sure you don't jump into subqueries... but then you'd need to process strings in case parenthesis appear in quotes, you don't want to count them... and so on and so on. It's definitely much simpler to add a semicolon between your queries :-)

This syntax is valid and has a whitespace:

Yes, but you said "should always have whitespace", which is incorrect.

Eh, no, i said:

A SELECT statement as part of a subquery should always be preceded by zero-or-more whitespaces and ...

I was suggesting the backwards scan :)

Then that must have been the absolutely shortest specification for a parser I've ever seen. You're clearly insane ;-).

Nah, just kidding. The Perl PCRE engine parses text from the back to the front, so maybe it's a fantastic approach. Anyway, I'm looking forward to a full spec or prototype implementation :-).

I know it's incomplete, but as i said i was only replying to anse's answer that there is 'no way'. This was merely meant as a kick in the right direction :)
I'll see what i can do about that prototype ;)