Bug 156444 - 20 rows displayed when > 100 are available
20 rows displayed when > 100 are available
Status: RESOLVED FIXED
Product: db
Classification: Unclassified
Component: Show Data
6.x
All All
: P3 with 1 vote (vote)
: 6.x
Assigned To: Jiri Rechtacek
issues@db
: API
: 173782 (view as bug list)
Depends on:
Blocks:
  Show dependency treegraph
 
Reported: 2009-01-07 20:30 UTC by ciliberton
Modified: 2009-10-09 23:08 UTC (History)
1 user (show)

See Also:
Issue Type: DEFECT
:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description ciliberton 2009-01-07 20:30:00 UTC
I'm connection to a remote Apache Derby database.

If I run this query I get 197 rows:
    select * from ATV.T_PRODUCT_QUEUE pq
20 rows are displayed, but it says "Total Rows: 197 (Page 1 of 10)" and allows me to go to the different pages.

If I run THIS query I only get 20 rows back:
    select   {fn TIMESTAMPDIFF( SQL_TSI_SECOND, pq.date_created,      pq.date_timestamp )} as "c-to-t"
            ,{fn TIMESTAMPDIFF( SQL_TSI_SECOND, pq.date_last_attempt, pq.date_timestamp )} as "a-to-t"
            ,pq.*
    from     ATV.T_PRODUCT_QUEUE pq
    order by key_product desc
20 rows are displayed and it says "Total Rows: N/A", but it should show there are 197 rows.

The table contains BLOB columns, but I haven't had any problems with that.

NetBeans About:
  Product Version: NetBeans IDE 6.5 (Build 200811100001)
  Java: 1.6.0_11; Java HotSpot(TM) Client VM 11.0-b16
  System: Windows XP version 5.1 running on x86; Cp1252; en_US (nb)
  Userdir: C:\Documents and Settings\xxx\.netbeans\6.5

"Database" PlugIn:
  Version: 1.3.2 Source: NetBeans
Comment 1 ciliberton 2009-01-07 20:33:22 UTC
Looks like it may be just an issue with the table alias...

If I run this I have access to all 197 rows:
    select * from ATV.T_PRODUCT_QUEUE pq

However if I run this I only get 20 rows:
    select pq.* from ATV.T_PRODUCT_QUEUE pq
Comment 2 ciliberton 2009-01-07 20:47:24 UTC
OOOPS that last comment should have been:

  ...197 rows...:
    select * from ATV.T_PRODUCT_QUEUE pq

  ...20 rows...:
    select pq.* from ATV.T_PRODUCT_QUEUE pq order by key_product

It actually looks like the problem is with the "order by" clause.


  all 197 rows:
    select * from ATV.T_PRODUCT_QUEUE pq

  only 20 rows:
    select pq.* from ATV.T_PRODUCT_QUEUE pq order by key_product

  only 20 rows:
    select pq.* from ATV.T_PRODUCT_QUEUE pq order by pq.key_product


If I remove the "order by" clause of my original query (with the "TIMESTAMPDIFF") it returns all 197 rows.
Comment 3 John Baker 2009-01-07 21:34:59 UTC
The results aren't paginated ?  If you change the page size value in the results toolbar to 197, are all the results
returned?

Or if you leave the page size at 20 then the blue right arrow in the results toolbar should be enabled and by clicking
the next 20 rows should appear
Comment 4 Rob Englander 2009-01-07 21:39:40 UTC
ciliberton, I think what you're seeing is that when you enter a new SQL select statement, the row count gets reset to 20
even if you had set it to 100 previously.  It's returning the correct data, but not using the row count you had set
before.  Is that what you're seeing?
Comment 5 ciliberton 2009-01-08 21:00:42 UTC
I see that the page size does get reset, BUT it doesn't give the option to retrieve more rows.
i.e. the "arrows" aren't there nor is there a dialog that gives you the option.

Oh... I see I can change the page size to 100, hit "enter" and it will re-run the query and return that exact number of
rows.

Is there a reason to change the behavior depending on whether or not an "order by" clause is specified?

Perhaps I am used to the previous version which would give you the option of downloading more rows OR perhaps I was
expecting a query with "order by" to behave the same as one without an "order by".
Comment 6 Rob Englander 2009-01-08 21:07:14 UTC
I just tried it against a MySql database and it works as expected.  There are 179 rows in
the table.  When I used an order by, it did reset the count to 20.  So I changed it back to
100 and I can use the arrows to get the next page.

I tried it with a derby database and YES.  I see what you're seeing.  Good catch!  That's a bug!

Thanks for sticking with it :)
Comment 7 Jiri Rechtacek 2009-02-10 15:20:01 UTC
Reassigned to new owner.
Comment 8 Jiri Rechtacek 2009-10-06 11:38:33 UTC
*** Issue 173782 has been marked as a duplicate of this issue. ***
Comment 9 Jiri Rechtacek 2009-10-06 15:59:21 UTC
A solution requires a API change in friend contract to allow propagate page size back from UI to SQL executor support.
Comment 10 Jiri Rechtacek 2009-10-07 09:08:57 UTC
core-main/rev/800cc5321816
Comment 11 Quality Engineering 2009-10-09 23:08:01 UTC
Integrated into 'main-golden', will be available in build *200910091401* on http://bits.netbeans.org/dev/nightly/ (upload may still be in progress)
Changeset: http://hg.netbeans.org/main-golden/rev/800cc5321816
User: Jiri Rechtacek <jrechtacek@netbeans.org>
Log: #156444: 20 rows displayed when > 100 are available


By use of this website, you agree to the NetBeans Policies and Terms of Use. © 2014, Oracle Corporation and/or its affiliates. Sponsored by Oracle logo