max_rows rewrites the offset of `LIMIT offset, count` on MySQL/MariaDB/SQLite

Open Beginner friendly
#536 0 comments 0 reactions 0 assignees View on GitHub

Maintainers usually reply within 1 day

Nobody has claimed this yet.

Assessment

Difficulty
2/5
Estimated time
1-3 hours
Newbie friendliness
78/100
Issue type
Bug
Clarity
Clearly specified
Activity status
Active
Tech stack
mariadb, mysql, sql, sqlite, typescript
Domain
backend, databases

Research direction

Start with findTopLevelLimit in src/utils/sql-row-limiter.ts, which the issue identifies as reading the first number after LIMIT as the row count. Check the existing row-limiter tests and add coverage for both LIMIT offset, count and LIMIT count OFFSET offset on SQLite and MySQL. Done means the offset is preserved, the returned rows are capped at max_rows, and truncation is reported when applicable.

Written by the indexing model from the issue text.

Description

With max_rows set on a tool, a query in the LIMIT offset, count form gets a different page of rows, or more rows than the cap.

[[sources]]
id = "lite"
dsn = "sqlite:///w/t.db"

[[tools]]
name = "execute_sql"
source = "lite"
max_rows = 5

Table t(id) with ids 1..300, on v2.0.0 (main c3b3fc7):

query sqlite3 dbhub
select id from t order by id limit 100, 3 101, 102, 103 7, 8, 9
select id from t order by id limit 3, 10 4 .. 13 4 .. 13 (10 rows, over max_rows = 5, not marked truncated)
select id from t order by id limit 3 offset 100 101, 102, 103 101, 102, 103

Same on MySQL 8. No error in either case, so the agent works with the wrong rows.

findTopLevelLimit in src/utils/sql-row-limiter.ts reads the first number after LIMIT as the row count, but in LIMIT a, b that is the offset. The limiter then replaces it with min(a, max_rows + 1) (100 becomes 6), and when a is already under the cap it leaves the query alone, so b is never capped.

#444 had a fix for this and was closed with a request to open an issue first; I didn't find one, so here it is. I can send a PR that caps the second operand of LIMIT a, b and keeps the offset, if that's the direction you want.

Dominant language
TypeScript
Stars
3.9k
Forks
338
Avg merge
6h 35m
Merged PRs (30d)
37

Getting set up

  • Ships a Dockerfile or Docker Compose file
  • No pull request template
  • No contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from bytebase/dbhub

All issues in bytebase/dbhub

Similar issues

More TypeScript issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.