Some unexpected behaviour in a command window with query result rows. I'm running release 9.0.180.0.

Somewhat hard to illustrate in screen captures, but here's the behaviour:

• SQL statement in a command pane, query runs and returns 68,602 rows, with 68,602 rows fetched, then
• I grab the scroll bar on the right hand side of the results pane and drag it to the "bottom", and now
• rather than scroll to the bottom of the result set showing the "last" rows returned, the scroll handle snaps back up to "near" the top of the results pane, preventing me from ever seeing those last rows

I've also noted that for queries that include an ORDER BY clause, scrolling too close to the end of the results pane rows seems to just be showing the first rows "again". Pain!

Anyone else seen this behaviour?

That's expected. The system is filling records in on the fly. You're trying to use a command window as an interactive viewer, which isn't a practical thing. After all, there's no way you're going to be able to read, or make sense of, many tens of thousands of records presented in random order.

Remember, there's no ordering in SQL. If you want order, use ORDER BY and FETCH to get whatever it is you prioritize as an interest, for example the top 100 records of cities by population, or whatever. Use FETCH because it doesn't make sense to fetch hundreds of thousands of records when you can only see 30 or so at a time.

If you don't use FETCH when the system refreshes the results display on the fly it's going to show you the records in the ORDER BY you commanded again, because, after all the source data might have changed. That's why it pays to use a FETCH for the number of records that make sense to view and digest interactively.

If you feel interactive browsing of a table with many tens of thousands of records is effective for you, no problem: SELECT <the fields you want> INTO [table name] to create a table with the results of the query. You can then browse that interactively as much as you want.

I've seen two variants.

First, when the query is still fetching records (as can be seen in the Info pane, for example). If you left-click the scroll handle, drag it, and the table fetches a couple of records, the scroll bar will reinitialize and the scroll handle will jump, ignoring whatever dragging you did.

Second, when you move the cursor away from the scroll bar. If you left-click the scroll handle, drag it along the scroll bar and then try to drag it outside of the scroll bar, after some distance the handle will jump back to its original position. The distance at which the handle will jump back depends on the direction in which you move the cursor. In the attached screen, when you move the cursor along the blue lines, the scroll handle follows it, but as you cross into the red, the handle jumps back to the original position. You can see that the handle jumps back pretty fast if you go outside of the scroll bar moving *down*, but stays for much longer if you go outside of the scroll bar moving left or right. So, when trying to scroll to the very bottom (= down), it seems relatively easy to overshoot the scroll bar.

If you see the jumps again and it's not one of these situations, in which they are expected, please let us know.

Attachments:
scrollbar.png

When I open a table from SQLServer (from the Assessor's Office) it first displays 5,000 records. Fine. I then hit the fetch all button and it takes 10 seconds or so to fetch. It then reports 473,454 records, 0 selected, 473,557 displayed, and 473,557 fetched. Odd that fetched is higher than the records reported? Waiting longer doesn't help. If I then scroll to the bottom of the table, being careful to let go the left click over the scroll rectangle, it immediately jumps back to the top.

This may be relevant. The table allows me to make selections, apparently Mfd is happy with the btree index it contains. However, I've run into SQL error messages that complain about the indexes when trying to use Mfd queries on tables in the Assessor database. However, I'm not sure if this particular table was involved. Happy to dig further if need be.

Is there anything in the log window? The number of shown records can be lower than the number of fetched records if some records have been hidden by a filter (I suppose you had no filter applied) or had invalid data (eg, NULL values in the unique fields).

Does the table window scroll back to the top if you navigate down using the keyboard - just hold PgDown for a while and let it page through several tens of screens, then check if at any point the window will scroll back up? Does it seem to be a mouse-only thing or not?

Here's the log window which shows the problem with indexes. No filters applied. It does seem to be a mouse-only thing. PgDown works and then if I hold the up arrow down it scrolls back up.

Attachments:
Capture.JPG