Database Tool Window Pagination and Manual LIMIT Conflicts
26.5K reputation · 12 Sept 2023, 02:04 UTC
PHPStorm's Database tool window manages large datasets by applying a configurable Page Size, which automatically appends LIMIT and OFFSET clauses to the executed SQL to prevent JVM memory exhaustion.
A conflict arises when a developer writes a custom SQL query that already includes a hardcoded LIMIT clause to bound the initial result set. It is unclear how the IDE's internal pagination mechanism handles these pre-existing constraints when a user attempts to navigate to the next page of results.
Does the IDE's automatic pagination override the manual LIMIT clause in the query, or does it wrap the query as a subselect to apply the page offset? How does this behavior differ across database dialects that use varying pagination syntax?
jetbrains
phpstorm1 answer
1 question comment
Use comments to ask for clarification. Post a solution as an answer.
26,525 reputation · 12 Sept 2023, 05:16 UTC
When a manual LIMIT is present, the effective result set depends on how the tool rewrites for the target dialect, not just on wrapping.
What to check
- Open the query preview or history panel and compare page 1 vs page 2 SQL. Some tools wrap as
SELECT * FROM (user_sql) t LIMIT ? OFFSET ?, others append dialect-specific paging. - For dialects without native LIMIT/OFFSET, the wrapper uses ROWNUM, OFFSET FETCH, or FETCH FIRST. The outer paging is then applied to the already truncated inner result, which can produce empty pages once OFFSET exceeds the manual limit.
- Queries with ORDER BY, window functions, or FOR UPDATE are especially sensitive: outer LIMIT/OFFSET can change locking and make window frames evaluate over a partial set.
Behavior is tool and version specific; detection of an existing LIMIT and whether pagination is disabled or warned varies. Verifying the generated SQL is the practical way to confirm the interaction.