René's URL Explorer Experiment

{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"client.query.sql() silently truncates at 5,000 rows — should auto-paginate","articleBody":"## Problem\n\n`client.query.sql()` sends a single `GET /{entity_set}?sql=\u003cquery\u003e` and returns whatever the server responds with. The Dataverse Web API default page size is 5,000 rows. If the query matches more than 5,000 rows, **results are silently truncated** — no error, no warning.\n\nThis is inconsistent with the rest of the SDK:\n- `client.records.get()` — auto-paginates via `@odata.nextLink`, returns all results\n- `client.dataframe.get()` — auto-paginates, consolidates into a single DataFrame\n\n`client.query.sql()` is the only read method that silently drops data.\n\n## Observed behavior\n\n```python\n# Table has 136,590 rows\nresults = client.query.sql(\"SELECT srcid FROM prefix_ballbyball\")\nlen(results)  # → 5000 (silently truncated, no error)\n```\n\nThe user gets 5,000 rows and has no way to know 131,590 rows are missing.\n\n## Expected behavior\n\n`client.query.sql()` should follow `@odata.nextLink` in the response and return all matching rows, consistent with `records.get()` and `dataframe.get()`.\n\n## Proposed fix\n\nCheck for `@odata.nextLink` in the response from `_query_sql()` and continue fetching pages until exhausted:\n\n```python\ndef _query_sql(self, sql: str) -\u003e list[dict[str, Any]]:\n    # ... existing validation ...\n    url = f\"{self.api}/{entity_set}\"\n    params = {\"sql\": sql}\n    all_rows = []\n    \n    r = self._request(\"get\", url, params=params)\n    body = r.json() if r.text else {}\n    all_rows.extend(body.get(\"value\", []))\n    \n    next_link = body.get(\"@odata.nextLink\")\n    while next_link:\n        r = self._request(\"get\", next_link)\n        body = r.json() if r.text else {}\n        all_rows.extend(body.get(\"value\", []))\n        next_link = body.get(\"@odata.nextLink\")\n    \n    return all_rows\n```\n\nAlternatively, return a page iterator consistent with `records.get()` — but this would change the return type from `list[Record]` to `Iterable[list[Record]]`.\n\n## Impact\n\nAny user querying a table with \u003e5,000 rows via `client.query.sql()` is getting silently truncated results today. The `?sql=` parameter is commonly used for filtered reads and its utility would increase significantly if it could return full result sets.\n\n## Context\n\nDiscovered during end-to-end testing of a 21-table cricket dataset import (297K total records). After import, analytical queries via `client.query.sql()` returned only 5,000 of 136,590 rows from the largest table, producing incorrect aggregation results.","author":{"url":"https://github.com/suyask-msft","@type":"Person","name":"suyask-msft"},"datePublished":"2026-04-06T03:32:27.000Z","interactionStatistic":{"@type":"InteractionCounter","interactionType":"https://schema.org/CommentAction","userInteractionCount":1},"url":"https://github.com/157/PowerPlatform-DataverseClient-Python/issues/157"}