Syntax error on correct sql text with nested JOINs

[tormozit]

I use command line tool SqlFormatter for Windows. And for text

INSERT INTO #tt3 WITH(TABLOCK) (_Q_000_F_000RRef, _Q_000_F_001RRef, _Q_000_F_002RRef) SELECT
T1._IDRRef,
T3._Document209_IDRRef,
T2._Document209_IDRRef
FROM dbo._Document209 T1
LEFT OUTER JOIN dbo._Document209_VT2775 T2
INNER JOIN dbo._Document209_VT2677 T3
ON (T3._Document209_IDRRef = T2._Document209_IDRRef)
ON (T2._Document209_IDRRef = T1._IDRRef)

I get

[TaoK]

I was surprised to see these two ON clauses stacked together like this, without any grouping hint, so I just checked, and can confirm this is indeed valid T-SQL - so this is indeed a bug.

Simplified reproduction:

CREATE TABLE TABLEA (A Int, B Int); CREATE TABLE TABLEB (A Int, B Int); CREATE TABLE TABLEC (A Int, B Int);

SELECT *
FROM TABLEA
JOIN TABLEB
JOIN TABLEC
ON TABLEB.A = TABLEC.A
ON TABLEA.A = TABLEB.A

If you move the second ON clause to its join, the query parses and formats normally (and as far as I can tell, is semantically/functionally identical), so this may serve as a sufficient workaround depending on the origin and modifiability of the problem query:

SELECT *
FROM TABLEA
JOIN TABLEB
ON TABLEA.A = TABLEB.A
JOIN TABLEC
ON TABLEB.A = TABLEC.A

If the general structure is intentional, another minimal fix (although the resulting query has some formatting legibility issues, I would argue) is to provide a grouping hint to the ON clause:

SELECT *
FROM TABLEA
JOIN (TABLEB
JOIN TABLEC
ON TABLEB.A = TABLEC.A)
ON TABLEA.A = TABLEB.A

I am curious though, about two things:

1. Did you (or whoever wrote this query) use this pattern intentionally, or is this ON clause placement a typo/accident?
2. Would you "expect" the formatter to rewrite this query / move the ON clause, or just leave it there?

[tormozit]

1. It was generated by code. I have no option to change that code.
2. I prefer this way

FROM
	dbo._Document209 AS T1
	LEFT JOIN dbo._Document209_VT2775 AS T2
		INNER JOIN dbo._Document209_VT2677 AS T3
		ON T3._Document209_IDRRef = T2._Document209_IDRRef
	ON T2._Document209_IDRRef = T1._IDRRef

[iVolt1]

First of all, poorsql has been an invaluable tool for me years now, so thanks! This bug is the only impediment I have when using this tool.

I use poorsql to compare the vendor's code to our modifications using the command line for hundreds of objects with every release cycle so I see this join pattern a lot. The pattern is very common and is how an Inner Join is made to a Left Join to return the correct data. This pattern is better written using an Outer Apply member for clarity and also is parsed correctly by poorsql.

I would prefer the code format this way which helps identify the pattern being used and the Inner keyword dropped as it is redundant and messes up the keyword alignment:

FROM dbo._Document209 AS T1
LEFT JOIN dbo._Document209_VT2775 AS T2
JOIN dbo._Document209_VT2677 AS T3 ON T3._Document209_IDRRef = T2._Document209_IDRRef
ON T2._Document209_IDRRef = T1._IDRRef

It would be great and very helpful if this bug could be fixed.

Thanks again,
iVolt1

[tormozit]

up