JOIN formatting bug in 6.4.0.633

jmeyer

Repo:

IF OBJECT_ID('tempdb..#a') IS NOT NULL
  DROP TABLE #a
IF OBJECT_ID('tempdb..#b') IS NOT NULL
  DROP TABLE #b

CREATE TABLE #a (id INT IDENTITY(1, 1)
                        NOT NULL)
CREATE TABLE #b (id INT IDENTITY(1, 1)
                        NOT NULL)

SELECT  *
FROM    #a AS a
INNER JOIN #b AS b
ON      a.id = b.id

SELECT  *
FROM    #a AS a
INNER JOIN #c AS b
ON      a.id = b.id

When formatting the code via CTRL+B, Ctrl+Q then the second SELECT statement will be changed on the ON join condition line such that b.id is then changed to a.id
This seems to happen because the object aliased by b (temp table #c) does not exist. I'd rather see an error message being thrown due to an non-existing object and not have the alias changed.

Aaron L

Thanks for the recreation steps jmeyer! I can recreate it here and will look into a fix.

SQL Prompt should be leaving it alone if it can't resolve the underlying table, but i think here it might have ended up resolving the "id" to "a" and worked backwards from there.

Aaron L

Hi jmeyer,

I think I've got a fix for you in this private build, if you could give it a try?

It looks like Prompt could resolve the column and so thought it knew what was going on (useful for suggestions in half complete scripts as you're typing them, not so useful here!)

Thanks,
Aaron.

jmeyer

Aaron:

The issue is fixed in the private build. However, if I do a

IF OBJECT_ID('tempdb..#a') IS NOT NULL
  DROP TABLE #a
IF OBJECT_ID('tempdb..#b') IS NOT NULL
  DROP TABLE #b

CREATE TABLE #a (id INT IDENTITY(1, 1)
                        NOT NULL)
CREATE TABLE #b (id INT IDENTITY(1, 1)
                        NOT NULL)

SELECT  *
FROM    #a AS a
INNER JOIN #b AS b
ON      a.id = b.id

SELECT  *
FROM    #a AS a
INNER JOIN #c AS b
ON      a.id = id

it will now resolve the missing prefix in the second ON statement to an "a.id". Technically, this is correct as SQL Prompt correctly resolves the column to the corresponding table alias. However, it doesn't make much sense to join a column to itself in the same table alias; I'm just not sure you want to add this type of validation logic to SQL Prompt.