Results 1 to 15 of 15
  1. #1
    Join Date
    Mar 2014
    Posts
    3

    Question Unanswered: [SOLVED] Fetch first n only returns one row

    Hi everyone,

    I am joining two tables, filtering on some columns, and then have an ORDER BY . Then I apply a "return first 10 rows only" .

    The problem is, even though there are 36 rows that match the criteria, once I apply the "return first 10 rows only", only one row is returned.

    If I remove the order by, the query behaves as expected, returning only 10 rows. If I order by the primary key instead of the original column, the query behaves as expected.

    The explain plan shows "#Rows 1" where it normally would show the amount of first rows to be returned.

    Any help or pointers to explain why the return set is incorrect when using first n rows would be greatly appreciated.

    Thanks!
    Last edited by AaronR; 03-04-14 at 14:29. Reason: Changed to SOLVED

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Posting the query would be greatly appreciated.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Mar 2014
    Posts
    3
    Oh, of course I missed the query.

    By the way, the problem is solved now, after another day of hacking into it. But I will post the information in case anyone else comes across it.

    The query was something like this:

    SELECT b.type, b.subtype, a.startDate, b.surname
    FROM a INNER JOIN b ON ( a.b_id = b.id )
    WHERE a.type = 'XYZ' and
    a.subtype = 'SUBXYZ'
    ORDER BY b.gender
    FETCH FIRST 10 ROWS ONLY


    Then again,
    • If I leave the query as shown above, I only get one row back, which is incorrect, since there are 36 rows that match the conditions.
    • If I remove the FETCH FIRST, I see that 36 rows fulfill the criteria.
    • And if I remove the ORDER BY, I get the expect 10 first rows


    I tried several things that didn't work:

    • I used RUNSTATS on both tables and their indexes.
    • I tried switching the two tables around (b inner join a)
    • I tried using a subselect (where a.b_id IN (select id from b...)
    • I tried using an EXISTS, variation of the point above.
    • I dropped all indexes and constraints, including primary keys and recreated them.


    Then yesterday I was using db2exfmt to analyse the optimization plan in better detail, and I noticed that there were some warnings about statistics not being updated.

    I ran RUNSTATS again, and there you go! The expected results came back... So it was a problem with the index statistics, and somehow the optimizer was deciding that only one row was to be returned.

    Recreating the primary keys, the indexes and re-running the stats solved the problem.

    Thanks a lot for looking into it.

  4. #4
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by AaronR View Post
    Oh, of course I missed the query.

    By the way, the problem is solved now, after another day of hacking into it. But I will post the information in case anyone else comes across it.

    The query was something like this:

    SELECT b.type, b.subtype, a.startDate, b.surname
    FROM a INNER JOIN b ON ( a.b_id = b.id )
    WHERE a.type = 'XYZ' and
    a.subtype = 'SUBXYZ'
    ORDER BY b.gender
    FETCH FIRST 10 ROWS ONLY


    Then again,
    • If I leave the query as shown above, I only get one row back, which is incorrect, since there are 36 rows that match the conditions.
    • If I remove the FETCH FIRST, I see that 36 rows fulfill the criteria.
    • And if I remove the ORDER BY, I get the expect 10 first rows


    I tried several things that didn't work:

    • I used RUNSTATS on both tables and their indexes.
    • I tried switching the two tables around (b inner join a)
    • I tried using a subselect (where a.b_id IN (select id from b...)
    • I tried using an EXISTS, variation of the point above.
    • I dropped all indexes and constraints, including primary keys and recreated them.


    Then yesterday I was using db2exfmt to analyse the optimization plan in better detail, and I noticed that there were some warnings about statistics not being updated.

    I ran RUNSTATS again, and there you go! The expected results came back... So it was a problem with the index statistics, and somehow the optimizer was deciding that only one row was to be returned.

    Recreating the primary keys, the indexes and re-running the stats solved the problem.

    Thanks a lot for looking into it.

    Always supply version and fixpak. This looks like a bug, and someone probably reported it already.
    --
    Lennart

  5. #5
    Join Date
    Feb 2012
    Posts
    22

    DB2 LUW 9.7.9 - Fetch first n only returns one row , Optimizer seems confused

    Same issue here,

    Fetch first n only returns one row
    Running fine on DB2 LUW 9.7.7, after upgrading to 9.7.9 we bumped into this issue.
    Hard to tackle, seems runstat(s) can solve it, but we aren't sure ..

    So yes, I vote: surely this is a BUG in DB2 LUW ver 9.7.9

  6. #6
    Join Date
    Feb 2012
    Posts
    23

    Same issue here on DB2 LUW 9.7.9

    I've encountered the same issue. The bug (which in my opinion surely is !!) is probably introduced in fixpack 8 or 9. We were planning to upgrade from 9.7.7 to 9.7.9. We encountered the issue during testings on 9.7.9.

    The issue is hard to reproduce. The following findings to resolve this issue from my site:

    * Removing the FETCH FIRST X ROWS
    * Removing the ORDER BY
    * Removing a join
    * Switching the auto_maint db cfg parameter
    * Issuing reorg/runstats

    Note that we did a restore of a 9.7.7 database, upgrade the database (db2updatedb) to the current fixpack, ran all rebinds and restarted the instance.

    Anyway were gonna skip this fixpack upgrade as it is uncertain what is triggering the bug to appear. I guess no-one can live with a version of which you are unsure if the query is giving you the correct results.

  7. #7
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by Baddus View Post
    I've encountered the same issue. The bug (which in my opinion surely is !!) is probably introduced in fixpack 8 or 9. We were planning to upgrade from 9.7.7 to 9.7.9. We encountered the issue during testings on 9.7.9.

    The issue is hard to reproduce. The following findings to resolve this issue from my site:

    * Removing the FETCH FIRST X ROWS
    * Removing the ORDER BY
    * Removing a join
    * Switching the auto_maint db cfg parameter
    * Issuing reorg/runstats

    Note that we did a restore of a 9.7.7 database, upgrade the database (db2updatedb) to the current fixpack, ran all rebinds and restarted the instance.

    Anyway were gonna skip this fixpack upgrade as it is uncertain what is triggering the bug to appear. I guess no-one can live with a version of which you are unsure if the query is giving you the correct results.
    This is a bug introduced in fixpak9. I reported a PMR and it will be fixed in fixpak10. We didnt have a need for anything in fixpak9 so we agreed to wait for fixpak10. Lack of statistics in combination with order by and (a scalar function or a cast used in order by) together with fetch first causes the problem.
    --
    Lennart

  8. #8
    Join Date
    Feb 2012
    Posts
    23
    Thanks for your reply! That will save me the time it takes to submit a PMR.
    Good to hear that it will be solved in fixpack 10. Were also gonna wait for fixpack 10 or maybe upgrade to DB210.1 in the meanwhile.

  9. #9
    Join Date
    Feb 2012
    Posts
    22
    Hi Lennart,

    Perhaps you can provide us your PMR # ?
    or if you have an APAR # for this issue, would be great !
    so we can reference it when / if we submit a SR / PMR ?

    regards,
    Erwin Hattingh

  10. #10
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by Baddus View Post
    Thanks for your reply! That will save me the time it takes to submit a PMR.
    You still can submit the PMR and request for a special-build. Then you will get FP9 without the error.
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  11. #11
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by Baddus View Post
    maybe upgrade to DB210.1 in the meanwhile.
    Why? V10.5 is for warehouse/BLU only?
    If you plan an upgrade I'd go directly to V10.5, also for your good-old-row-based OLTP database.
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  12. #12
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by ErwinHaTsoF View Post
    Hi Lennart,

    Perhaps you can provide us your PMR # ?
    or if you have an APAR # for this issue, would be great !
    so we can reference it when / if we submit a SR / PMR ?

    regards,
    Erwin Hattingh
    The PMR is 66015,130,846. As mentioned we agreed to close it and wait for fixpak 10
    --
    Lennart

  13. #13
    Join Date
    Feb 2012
    Posts
    22
    Thanks Lennart !

    as it stands now, I won't dare use the latest 9.7 fixpack 9
    and surely not even the latest 10.5, seems 'just' stable with the recent fixpack 3 ...

  14. #14
    Join Date
    Mar 2014
    Posts
    3
    Yes, I can confirm also that the behaviour that I reported in this thread originated when we upgraded our DB2 9.7 to fixpack 9.
    Last edited by AaronR; 03-06-14 at 17:02.

  15. #15
    Join Date
    Feb 2012
    Posts
    22

    FixPak 9 fixes in FETCH FIRST N ROWS maybe broke something else

    Could be, in an attempt to fix things in DB2 LUW 9.7 FixPack 9, a new bug was introduced .... ???


    According to the APAR "fixed" issues in in FixPack 9, IBM "touched" this :

    IC88478 DB2 OPTIMIZER MIGHT NOT CHOOSE A ONE-FETCH GROUP BY WHEN CARDINALITY IS UNDERESTIMATED
    IBM IC88478: DB2 OPTIMIZER MIGHT NOT CHOOSE A ONE-FETCH GROUP BY WHEN CARDINALITY IS UNDERESTIMATED - United States

    IC90916 QUERY CONTAINING EXISTS SUBQUERY AND FETCH FIRST N ROWS CLAUSE MIGHT PRODUCE INCORRECT RESULTS
    IBM IC90916: QUERY CONTAINING EXISTS SUBQUERY AND FETCH FIRST N ROWS CLAUSE MIGHT PRODUCE INCORRECT RESULTS - United States

    IC96728 QUERY MAY RETURN INCORRECT RESULTS WHEN IT CONTAINS A SUB-SELECTWITH CORRELATION AND A FETCH FIRST N ROWS CLAUSE
    IBM IC96728: QUERY MAY RETURN INCORRECT RESULTS WHEN IT CONTAINS A SUB-SELECT WITH CORRELATION AND A FETCH FIRST N ROWS CLAUSE - United States

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •