Query fails in ASA 12, works in ASA 8

[Former Member]

Hello all, here is the issue, the following nonsense query will run in ASA 8. It seems to be an issue of combining old and new syntax for joining tables. To make matter more difficult I have at least one other person was able to embed a problem query in a stored procedure and have it work. My database which is an database upgrade from 8 to 12 will not execute the stored procedure. I am using ASA 12..01.3298. Is there some connection, database, or server option which would cause this to happen?

select count(*)  
  from sys.SYSARTICLE a,
       sys.SYSTABLE   t
       left outer join sys.SYSCOLUMN c on a.table_id = c.table_id

In ASA 12 it must be rewritten like this

select count(*) 
  from sys.SYSARTICLE a
       cross join       sys.SYSTABLE  t
       left outer join  sys.SYSCOLUMN c on a.table_id = c.table_id

[Former Member · Accepted solution]

Within the original query the "left outer join" should have higher precedence than the "," and so the scoping should be interpreted as

sys.SYSARTICLE a, ( sys.SYSTABLE t left outer join sys.SYSCOLUMN c on a.table_id = c.table_id )

which means this is not a syntactically valid statement. The fact that the ASA 8 parser allowed it was unintentional, and the tighter parsing rules in ASA 12 correctly reject it. I don't know of any options that will enable the older behaviour in ASA 12.

Note that you don't have to use "cross join" in ASA 12 to fix this. Simply fixing the ordering of the comma-join to correct the scoping error suffices.

select count(*)
from sys.SYSTABLE t,
     sys.SYSARTICLE a
     left outer join sys.SYSCOLUMN c on a.table_id = c.table_id

[Former Member · Accepted solution]

You are correct in this answer but that does not help me to identify why a query that was parse-able in ASA 8 no longer works in ASA 12. If you do change the query as shown it arrives at the same answer. So what is the problem. Further the error reported says that there is a problem at the "a". This is not helpful in determining what the problem is. The system I am upgrading has procedures that join many tables where ordering the tables could become impossible to mange based on your solution.

[former_SQLA_member1694868 · Accepted solution]

It seemed like he said the answer was that ASA 8 had looser parsing rules (unintentionally) than ASA 12. With the tighter parsing rules, the old syntax became "wrong."

When I run your query, the error is not with the first "a," but with the "a" listed in the LOJ. David's statement above shows that using the LOJ puts everything after the comma in parentheses and thus it seems the statement inside cannot see the a alias on the outside.

[Former Member · Accepted solution]

I did notice a mention in the release notes for ASA 12 to inside/outside references in regard to aliases but why does just changing the comma to a cross join change how the code is parsed? It hasn't moved. This is just my problem in understanding the semantics of the clause so I really do not want to know. Truly the queries need to be rewritten, my problem is identifying which SP have the problem. I have over 158000 lines of code to review, not an easy task.

Any suggestions?

[MarkCulp · Accepted solution]

When you changed the ',' to "cross join" the parser change the way it parsed the statement - as David said in his answer, the "left outer join has higher precedence than the ','" ... so this means that the "cross join" bound its left and right elements before the ',' operator. The "cross join" operator has the same precedence as "left outer join" and bind from left to right and therefore when you change the "," to "cross join" the "systable t cross join sysarticle a" bound first and then that expression became the left part of the "left outer join" operator.

I.e. "t, a LOJ c" is bound as (t, ( a LOJ c)) where as: "t CJ a LOJ c" is bound as ((t CJ a) LOJ c)