×
The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.
On 3/1/11 5:29 AM, Lowary, Jim wrote:
What got us was some SQL statements in a old Delphi program that used
ODBC to connect the joins were like this
...
From file1 a, file2 b
Left outer join file3 c on a.field1 = c.fieldx
According to JOIN precedence, the above JOIN should be parsed by the
SQL as the effective equivalent of:
From file1 a
, ( file2 b Left outer join file3 c
on a.field1 = c.fieldx )
Notice how the a.field1 is now inside the parentheses for the JOIN ON
syntax. That is the source of the problem. Past releases were not
properly enforcing the JOIN precedence rules in parsing the statements
for implementation by the query engine.
The incorrect effect of the improper JOIN precedence, had the query
processed by the parser and thus the query engine instead, as an
effective equivalent of:
From ( file1 a, file2 b )
Left outer join file3 c
on a.field1 = c.fieldx
What we changed to was:
From file1 a
Cross join file2 b
Inner join file3 c on a.field1 = c.fieldx
Not sure I understand why they made the change (but this SQL has
been working since late 90's and original programmer is long gone).
<<SNIP>>
Thus the change was to correct the long-existing issue for the query
engine failing to enforce proper join precedence; as effected by the
improperly parsed statements composed with ambiguous JOIN syntax.
Although the query was producing the desired results, the results did
not reflect the ANSI SQL rules, because the original query was ambiguous
as written; as written, the query has a JOIN syntax issue. The previous
results are now reflected in the "changed to" query, so all is well for
that SQL.
The Memo To Users [rzaq9.pdf] was used as expected, to warn of
changes-in-function [to the SQL in this case] which could "break
existing applications". Indirectly [other releases and more docs] and
directly to the MTU:
http://www-01.ibm.com/support/docview.wss?uid=nas1b6c5ed39e84670648625740f007c74e3
http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic//rzaq9/rzaq9.pdf
FWiW: A similar past discussion thread:
http://archive.midrange.com/midrange-l/200909/msg00749.html
Until this moment, I had not considered the possibility that... The
MTU does not mention if there might be some queries which were always
giving [possibly desired but] incorrect output from the JOIN precedence
issue on prior releases. If so, the updated SQL parser will now pass
the proper\corrected query, and the query engine will produce the
corrected output according to the now-proper JOIN precedence. And for
any changed result, that would likely be seen as a problem for the
application, and thus the SQL JOIN syntax would still require a change
to correct; though not diagnosed as a syntax issue. No such example,
nor any words alluding, of that nature were included in the MTU. Caveat
lector.
Regards, Chuck
midrange.com
