René's URL Explorer Experiment

{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"[FEATURE] OData push-down: $select strips columns used only in WHERE/ORDER BY, so the client-side filter silently drops all rows","articleBody":"## Summary\n\nWhen a column appears in `WHERE` (or `ORDER BY`) but not in the `SELECT` list, the\nOData query-option push-down emits `$select` with only the projected columns and\ndrops the predicate column from the upstream response. Because push-down keeps the\nclient-side `WHERE` authoritative and re-applies it locally, the predicate column is\nnow missing from every row, the filter matches nothing, and the query returns **zero\nrows despite an HTTP 200 carrying matching data**. It fails silently.\n\n## Repro\n\nProvider: `entra_id` (Microsoft Graph), run with `--http.log.enabled`.\n\n~~~sql\nSELECT id, displayName FROM entra_id.users.users\nWHERE userType = 'Member';\n~~~\n\nOutgoing request — `$select` has no `userType`:\n\n~~~\nhttp request url: 'https://graph.microsoft.com/v1.0/users?$filter=userType eq 'Member'\u0026$select=id,displayName', method: 'GET'\n~~~\n\nGraph returns `200` with 3 matching rows (only `id`,`displayName` — no `userType`).\nFinal result set is empty:\n\n~~~\n|----|-------------|\n| id | displayName |\n|----|-------------|\n~~~\n\nThe 3 rows were discarded by the client-side re-filter because `userType` is absent\nfrom the response. Adding the predicate column to the projection confirms the cause\n(returns the 3 rows):\n\n~~~sql\nSELECT id, displayName, userType FROM entra_id.users.users\nWHERE userType = 'Member';   -- $select=id,displayName,userType\n~~~\n\n## Root cause\n\n`internal/stackql/pushdown/pushdown.go`, `buildPushdownIntent`: the projection placed\nin the `PushdownIntent` is taken from the `SELECT` list only\n(`extractProjectionAndCount(sel.SelectExprs)`). The `WHERE`/`ORDER BY` columns are\ncollected separately (for `$filter`/`$orderby`) and never folded into the projection,\nso the `$select` any-sdk renders strips them.\n\nThe wire request itself is valid OData — Graph filters server-side and projects\ncorrectly. The defect is that stackql re-applies `WHERE`/`ORDER BY` **client-side**\nand the fetched rows no longer carry the needed column. That client-side dependency\nis knowledge only stackql has, so the fix belongs here.\n\n## Fix\n\nIn `buildPushdownIntent`, set the intent projection to the union of the SELECT,\npredicate, and order-by columns:\n\n~~~\nprojection = SELECT-cols ∪ WHERE-cols ∪ ORDER BY-cols\n~~~\n\nThis affects only the pushed `$select`; the SQL output projection is unchanged (still\njust the `SELECT` list, so `SELECT id, displayName ... WHERE userType = ...` still\noutputs two columns). Guard: no-op when the projection is empty — `SELECT *` pushes no\n`$select`, so the bug cannot occur there.\n\n## Tests\n\nAdd push-down cases against the no-auth `stackql_native_test.odata.people` mock:\n\n- filter on unprojected column: `SELECT name FROM ... WHERE city = 'NYC'` asserts the\n  wire `$select` includes `city` and the result set is non-empty.\n- order-by on unprojected column: `SELECT name FROM ... ORDER BY age` asserts `$select`\n  includes `age`.\n\n## Note\n\nWhichever columns get added to `$select` must satisfy the `select` `supportedColumns`\nallowlist. Where `select`/`filter`/`orderBy` share one allowlist (e.g. entra_id) this\nis automatic; a provider with divergent allowlists could otherwise trip the\nall-or-nothing `$select` gate — worth covering in the fix.\n","author":{"url":"https://github.com/jeffreyaven","@type":"Person","name":"jeffreyaven"},"datePublished":"2026-07-05T22:14:43.000Z","interactionStatistic":{"@type":"InteractionCounter","interactionType":"https://schema.org/CommentAction","userInteractionCount":0},"url":"https://github.com/682/stackql/issues/682"}