[Bug]: SQL editor fetches the whole result set — its 1000-row limit is applied client-side only

[fskorgen]

Apache Hop version?

2.19

Java version?

21

Operating system

Windows

What happened?

SqlEditor` caps a SELECT at 1000 rows:

List<Object[]> rows = db.getRows(sql.getStatement(), 1000);

That limit only ever reaches the read loop. Database.getRows(String, int) delegates to
getRows(sql, limit, null), which runs openQuery(...) and then stops reading after limit rows in
getRows(ResultSet, limit, IProgressMonitor). Nothing tells the driver.

The driver is told by setMaxRows, and openQuery only calls it when the connection carries a row
limit:

if (rowlimit > 0 && databaseMeta.supportsSetMaxRows()) {
  pstmt.setMaxRows(rowlimit);
}

rowlimit comes from Database.setQueryLimit(int), which SqlEditor never calls. So the statement
goes to the server unbounded, the driver materialises the full result, and only then are the first
1000 rows handed back and the rest discarded.

Why it is worse than it sounds

The default SELECT * FROM <table> is exactly the shape a user types in a SQL editor, and the
JDBC drivers that buffer a result set client-side by default — SQL Server's among them — read the
entire table before returning row one. SqlEditor runs behind a modal ProgressMonitorDialog, so
HopGui is unusable for the duration, and on a large fact table the JVM can run out of heap for rows
nobody asked for.

Measured on a 60-column MSSQL table through this code path: ~1 minute to return 100 rows
without setQueryLimit, immediate with it.

The query timeout does not save it either. setQueryTimeout bounds statement execution, not the
row-fetch loop, so a statement that returns quickly and then streams for a minute is never
interrupted.

Why this is an oversight rather than a choice

The class next door already does it correctly. GetPreviewTableProgressDialog, added in 2.18:

database.setStatementQueryTimeoutSeconds(queryTimeoutSeconds);
...
database.setQueryLimit(limit);

Preview therefore bounds the fetch server-side; the SQL editor does not. Both are user-facing
row-limited reads in the same package, and only one tells the driver.

Steps to reproduce

1. Point a connection at SQL Server (or any driver that buffers by default) holding a table with
   millions of rows and a wide row.
2. Open the SQL editor on that connection — e.g. from the database explorer dialog.
3. Run SELECT * FROM <that table>.

Expected: roughly the cost of 1000 rows; the editor's own cap is what makes this safe to type.
Actual: the full table is transferred before anything appears, HopGui is blocked behind the modal
progress dialog, and heap use tracks the whole result rather than 1000 rows.

Comparing SHOW PROFILE/server-side row counts, or simply watching the fetch time against
SELECT TOP 1000 *, shows the limit is not reaching the server.

Suggested fix

Set the limit on the connection before the script runs, in runSqlScriptWithMonitor, next to the
timeout it already sets:

if (timeoutSeconds > 0) {
  db.setStatementQueryTimeoutSeconds(timeoutSeconds);
}
db.setQueryLimit(1000);
db.connect();

setMaxRows is guarded by supportsSetMaxRows(), so dialects that do not support it (MonetDB
overrides it to false) are unaffected and keep today's client-side behaviour. The 1000 would be
better as the row limit the user chose rather than a constant, but that is a separate improvement —
the defect is that the existing constant never leaves the JVM.

Issue Priority

Priority: 2

Issue Component

Component: Hop Gui

[mattcasters]

This is already fixed on the Database perspective work in #8214 (issue-8212).

Database.getRows(sql, limit) now applies setQueryLimit(limit) before openQuery, so Statement.setMaxRows reaches the driver instead of only stopping the client read loop. SqlEditor also calls setQueryLimit(1000) on that connection.

Will close this against #8214 when that PR merges.