Bug in SQL parser???

[Franco Lombardo]

I'm trying to support AS400 database in Activiti (http://www.activiti.org).
This open source project creates lots of query like this:

SELECT SUB.* FROM
(select
RES.* , row_number() over (Order by A.field1) rnk
FROM (select A.field1, B.field1 from A inner join B on A.X = B.Y) RES
) SUB
WHERE SUB.rnk >= ? AND SUB.rnk < ?

Unfortunately this query fails with msg SQL5001 - Column qualifier or table
A undefined.

Note that if I change my statement this way:
SELECT SUB.* FROM
(select
RES.* , row_number() over (Order by NEW_NAME) rnk
FROM (select A.field1 NEW_NAME, B.field1 from A inner join B on A.X = B.Y)
RES
) SUB
WHERE SUB.rnk >= ? AND SUB.rnk < ?

all works fine (but I can't modify Activiti this way :-).
Is it a bug of SQL parser or I don't understand something?
Any suggestion?

Thanks!

Bye

Franco

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
http://www.francolombardo.net
Scala, Java, As400.....
http://twitter.com/f_lombardo
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

[CRPence]

On 25 Oct 2012 05:47, Franco Lombardo wrote:
> I'm trying to support AS400 database in Activiti
> (http://www.activiti.org).
> This open source project creates lots of query like this:
>
> SELECT SUB.*
> FROM
> (select RES.* , row_number() over (Order by A.field1) rnk
> FROM (select A.field1, B.field1
> from A inner join B on A.X = B.Y
> ) RES
> ) SUB
> WHERE SUB.rnk >= ? AND SUB.rnk < ?
>
> Unfortunately this query fails with msg SQL5001 - Column qualifier
> or table A undefined.
>
> Note that if I change my statement this way:
>
> SELECT SUB.*
> FROM
> (select RES.* , row_number() over (Order by NEW_NAME) rnk
> FROM
> (select A.field1 NEW_NAME, B.field1
> from A inner join B on A.X = B.Y
> ) RES
> ) SUB
> WHERE SUB.rnk >= ? AND SUB.rnk < ?
>

> All works fine (but I can't modify Activiti this way :-).

> Is it a bug of SQL parser or I don't understand something?

The scope of the correlation-name as qualifier prevents an outer
[higher level] query from "seeing" fields using those [qualified] names
of the inner [lower level; further nested] queries. The scope of the
correlation name\identifier "A" is available only in the deepest nested
query, from the given example. So although apparently the use of the H2
SQL by that software enables [though seemingly improperly] that SQL
SELECT query to run without error, the DB2 for i SQL seems in my opinion
to be *correctly diagnosing* the "undefined qualifier" condition.

The failing query is additionally problematic for any reference to an
unqualified name FIELD1 by the /ranking/ query [the query of the NTE
using table designator RES], because the use of "FIELD1" would be
ambiguous. In that case, the SQL0203 diagnostic condition is expected,
instead of the SQL5001. Using the expected qualified reference for
"OVER (ORDER BY RES.FIELD1)" is ambiguous because there are two FIELD1
column references in the derived table.

The second\modified query resolves those issues by giving a unique
name to the column\expression of the nested query (NTE).

FWiW: An alternate syntax for [re]naming the columns of the "RES"
named\designated NTE would be to specify a column-list [AKA a "list of
column names" or a "column name list"] after the "AS RES" correlation
identifier:

SELECT SUB.*
FROM
(select RES.* , row_number() over (Order by NEW_NAME) rnk

FROM (select A.field1, B.field1
from A inner join B on A.X = B.Y

) RES ( NEW_NAME, FIELD1_TOO )

) SUB
WHERE SUB.rnk >= ? AND SUB.rnk < ?

In the above revised query, the "exposed names" for the RES
table-reference are NEW_NAME and FIELD1_TOO whereas the _qualified_
exposed names are RES.NEW_NAME and RES.FIELD1_TOO which were simply
qualified with the correlation-name specified for that table-reference
[i.e. for the Nested Table Expression or Derived table with
correlation-name "RES"].

> Any suggestion?

Assuming my conclusion about the correctness of the IBM DB2 for i SQL
is functioning properly, then...

If indeed "can't modify" describes the situation, then ask that the
providers of Activiti correct the SQL in the version of the software
that will be downloaded and used. They have community\forum links at
the given URL, where presumably one could start such a conversation.?

Instead, assuming some evidence can be found to support the presumed
effectiveness of the failing syntax in any flavor of DB2 or the ANSI SQL
specifications, then open an issue with your service provider inquiring
why the DB2 for i SQL does not allow that syntax.

--
Regards, Chuck

[Franco Lombardo]

Chuck,

thank for this answer and for the one you gave me on the Midrange mailing
list.

The syntax you suggested:

SELECT SUB.*
FROM
(select RES.* , row_number() over (Order by order of RES) rnk
/* use the _ORDER OF_ the NTE for ranking */

FROM
(select A.field1, B.field1
from A inner join B on A.X = B.Y

order by A.field1 /* have the NTE collate by A.FIELD1 */

) RES
) SUB
WHERE SUB.rnk >= ? AND SUB.rnk < ?

doesn't raise any SQL error, even if it seems it doesn't return records in
the expected order, but I'm no sure of this, there could be some bug in
untit tests... I'll let you know.

[CRPence]

On 26 Oct 2012 02:51, Franco Lombardo wrote:
>
> <<SNIP>>

>
> The syntax you suggested:
>
> SELECT SUB.*
> FROM
> (select RES.* , row_number() over (Order by order of RES) rnk
> /* use the _ORDER OF_ the NTE for ranking */
> FROM
> (select A.field1, B.field1
> from A inner join B on A.X = B.Y
> order by A.field1 /* have the NTE collate by A.FIELD1 */
> ) RES
> ) SUB
> WHERE SUB.rnk >= ? AND SUB.rnk < ?
>
> doesn't raise any SQL error, even if it seems it doesn't return
> records in the expected order, but I'm no sure of this, there

> could be some bug in unit tests... I'll let you know.
>

Interesting. FWiW I had never used ORDER OF. I must admit, I only
learned of its existence, after a review of the SQl5001 documentation to
ensure I was clear on my understanding of the scope for use of the
correlation-name\table-designator:

_Listing of SQL messages_
http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/topic/rzala/rzalaml.htm
"... SQL5001
... In an OLAP function, the ORDER OF table designator must refer to a
table designator in the FROM clause of the subselect."

That led me to re-read some of the OLAP documentation, from which I
inferred the function of thee ORDER OF clause from the example given.
However I did not look very closely, nor attempt to thoroughly
understand its implied effect.

_Using OLAP specifications_
http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/topic/sqlp/rbafyolap.htm
"Example: Ranking and ordering by table expression results

Suppose that you want to find the top five employees whose salaries are
the highest along with their department names. The department name is in
the department table, so a join operation is needed. Because ordering is
already being done in the nested table expression, that ordering can
also be used for determining the ROW_NUMBER value. The ORDER BY ORDER OF
table clause is used to do this.
..."

--
Regards, Chuck

[Franco Lombardo]

"CRPence" wrote:

>Suppose that you want to find the top five employees whose salaries are the
>highest along with their department names. The department name is in the
>department table, so a join operation is needed. Because ordering is
>already being done in the nested table expression, that ordering can also
>be used for determining the ROW_NUMBER value. The ORDER BY ORDER OF table
>clause is used to do this.

So unit test aren't wrong! Too bad! :-(

Anyway, thank you for your great advices: you're the Chuck Norris of
as400!!!

:-)