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

asked 28 Apr '11, 14:44

Jturner's gravatar image

Jturner
1816714
accept rate: 66%

edited 28 Apr '11, 14:50

Mark%20Culp's gravatar image

Mark Culp
23.2k9132272


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
permanent link

answered 28 Apr '11, 16:56

David%20DeHaan's gravatar image

David DeHaan
476610
accept rate: 42%

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.

(28 Apr '11, 17:51) Jturner
Replies hidden

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.

(28 Apr '11, 18:22) Siger Matt

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?

(28 Apr '11, 18:59) Jturner
Replies hidden

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)

(28 Apr '11, 20:49) Mark Culp
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×32
×31

question asked: 28 Apr '11, 14:44

question was seen: 1,155 times

last updated: 28 Apr '11, 20:49