Table alias with SQL hint doesn't always work

After the . I use CTRL-SPACE to show the dropdown with columns and keywords but it doesn’t always work:


SELECT * FROM offerte o
where o.


SELECT * FROM offerte o, bestelling b
where o.

Doesn’t work:

SELECT * FROM offerte o,bestelling b
where o.

Notice the omission of a space after the comma seperator between the two tables.

I thought I would give @marijn a weekend time off so I made a quick patch:
replace old eachWord function in sql-hint.js with:

  function eachWord(lineText, f) {
    if (!lineText) return;
    var excepted = /[,;]/g;
    lineText=lineText.replace(excepted, ' ');
    var words = lineText.split(" ");
    for (var i = 0; i < words.length; i++) {
      f(words[i]?words[i] : '');

Hope I didn’t break anything :smile:

Found another hard to find bug, this applies to patched and non-patched version (see my post above).
The dropdown doesn’t always show up. Here are some testcases, all where cursor is located at the . and then CTRL-SPACE is pressed:

Doesn’t work (in both versions):

  1. “SELECT * FROM Plant p,offerte o WHERE o.”
  2. "SELECT * FROM Plant p,offerte o WHERE p."
    3a. "SELECT * FROM Plant p WHERE p."
    Notice there’s no space at the end!

Regarding case 3a:
3b. “select * from offerte o where o.” does work!?!!

Does work in both versions:
4. "SELECT * FROM Plant p, offerte o WHERE o. "
5. "SELECT * FROM Plant p WHERE p. "
Notice the space at the end AND the space between both tables/aliases!!

Does work(only in my patched version):
6. "SELECT * FROM Plant p,offerte o WHERE o. "
7. "SELECT * FROM Plant p,offerte o WHERE p. "
Notice the space at the end and the lack of a space between both tables/aliases.

Does work in both versions:
8. "SELECT * FROM plant p, offerte o WHERE o."
9. "SELECT * FROM plant p, offerte o WHERE o. "
Notice the space between tables/aliases. With or without space at the end both work.

Does work in both versions:
10. "SELECT * FROM Plant p, offerte o
Notice the enter after the first line.

I think @Marijn owes me a beer when he’s in Eindhoven :smile: . Took me quite some debugging but I found an very corner/edge cased bug in his convertNumberToCur(num) function which converts a number back to a Pos. Well in 90% of all input this function works ok but when it’s ends with 1 or more zeros the conversion to Pos fails. Thats why testcase 3a doesn’t work and testcase 3b does! The point . is exactly on a multiple of 10 position.

Here’s the fixed version which fixes all of the above problems:

>   function convertNumberToCur(num) {
>     //the previous implementation (v5.15.2) of this function bugs
>     //smallest number is x.000001 which means character pos 1 on line x
>     //x.000040 is seen/stored as x.00004 so it's converted to 4 instead of 40
>     //  in previous implementation
>     var line = Math.floor(num);
>     var row = (num-line) * Math.pow(10, 6);
>     return Pos(line, row);
>   }

Note that the pile of hacks that is sql-hint.js was not written by me (though I did clean some of it up). Also, if we consider the fact that you’re using a complicated piece of software built and maintained by me over the past 9 years, I’m not sure who owes who a beer.

Anyway, thanks for looking into these. Could you submit a pull request (preferably with additional test cases in test/sql-hint-test.js) on github?

Well beers waiting here… Let me know when you’re in Eindhoven. I am very grateful for this great piece of software.

I am not yet full into all GIT features like pull requests and testcases so it can take a while. Very busy on other projects right now…

Pfff found another oddity/strange behaviour in sql hint.

After listing the columns of a table…:

…and then typing the columname for example PL…:

… you would expect the columns PLANTCODE and PLANTNAAM instead of (again) the tables PLANT and PLANTSCHEMA.

Well it’s not a bug but make sure the MIME type you uses has support set including ODBCdotTable. See sql.js

@Marijn: maybe a good idea to document this somewhere?