AS/400 DB2 SQL optimizer issue?

[bkummel]

Hi all,

I noticed some strange behavior when executing a SQL query on our AS/
400 (V5R3) DB2 database. The query is like this:

WITH
    R AS (SELECT t1.ID, t2.ID
          FROM SCHEMA.TABLE1 t1, SCHEMA.TABLE2 t2
          WHERE t1.ID = t2.T1ID
          ORDER BY t1.ID DESC, t2.ID ASC ),
    Q AS (SELECT counter() cntr, R.*
          FROM R)
SELECT *
FROM Q
WHERE Q.cntr > 0

counter() is a stored procedure, identical to the counter() example in
the IBM documentation on stored procedure. We use it because there is
now row_number() function in V5R3. I'd expect every row in the result
to have a unique integer number, starting from 1. This is true when I
leave out the "WHERE Q.cntr > 0". But when I add that, counting starts
at a higher number, and the counter is only incremented per TABLE1
record. Perhaps an example is more clear. I expect this:

CNTR  T1_ID  T2_ID
----  -----  -----
   1      1      1
   2      1      2
   3      1      3
   4      2      1
   5      2      2
   6      3      1
   7      3      2
   8      3      3 etc...

But I get this:

CNTR  T1_ID  T2_ID
----  -----  -----
   9      1      1
   9      1      2
   9      1      3
  10      2      1
  10      2      2
  11      3      1
  11      3      2
  11      3      3 etc...

I don't understand what's going on. I did not expect the results to be
different when adding the "WHERE Q.cntr > 0" clause. Perhaps something
with the SQL optimizer? Off course I'd appreciate it if someone can
suggest how to get the behavior I was expecting.

Best regards,
Bart Kummel