Query result disappears when scrolling to bottom

[teneighty]

This is strange to me, I wonder if this is a bug. I have a query that builds a result table from two tables:

Code: Select all

SELECT "Suites"."Room", "Counts"."Room" AS "Room or Suite", "Counts"."Category", "Counts"."Item", "Counts"."Quantity" 
FROM "Counts" 
LEFT JOIN "Suites" ON "Counts"."Room" = "Suites"."Suite"

It is an HSQLDB embedded database file. When I scroll down, when I get to the bottom of the result table the whole thing disappears. The columns are still shows, and there is one empty row. I hit refresh or run query and it all comes back, but I cannot touch the very bottom of the result table or it disappears, every time.

I actually use this query as a subquery to sum "Quantity" for different "Item" values. "Category" makes it easy to filter results. Although it's not absolutely critical, it would be nice to be able to review the results of the above query as it serves as a master list.

I would use only one table, "Counts", if every room was unique, but sometimes many rooms are the same. Instead of counting their contents every time, I have a separate table "Suites" where I define suites or room templates and associate those with individual room numbers. LEFT JOIN I believe, in the above query expands, or duplicates, entries in the "Counts" table for any template rooms. Example: "Counts" contains two entries for room A. If "Suites"."Suite" has 6 entries for A, rooms 101 through 106 for example in "Suites"."Room", then the results will return 6 rows for each of the two rows for A in the "Counts" table, with the real room number in another column. If if an entry contains 107 in "Counts"."Room" and 107 does not occur in "Suites"."Suite" then nothing happens, it returns exactly what is in the "Counts" table.

See partial table examples below, to illustrate the contents and interaction of data in the two tables, if it is potentially relevant. Result shows 5 type C lights and 12 (2+2+2+2+2+2) type B lights in the query results.

Counts
| Room | Category | Item | Quantity |
| 107 | Lights | C | 5 |
| A | Lights | B | 2 |


Suites:
| Suite | Room |
--------------------
| A | 101 |
| A | 102 |
...
| A | 106 |

Results
| 101 | A |
| 102 | A |
...
| 106 | A |
| (null) | 107 |

This is the only query in this project that uses two sources and LEFT JOIN, so I wonder if the disappearing act has something to do with that. Have I done something wrong/undesirable/unwise in the SQL code? Queries doings SUMs based on results of this query (as a subquery), work fine.

[UnklDonald418]

Since it has been so long since your post and there have been no replies I will throw in my 2 cents worth.
I built a couple of tables and tried your query. I get results similar to what you show, but no problems with disappearing results. I am running it on a Windows based computer.
Perhaps the problem is related to Kubuntu.

[teneighty]

That is interesting. Thanks for testing that.