user-relation-expression-projection-and-purge-exclusion - #837

Description

Two fixes for user space tables and materialized views.

#798, expressions in a SELECT over a user space table or materialized view were not evaluated. The projection rewrite in internal/stackql/astvisit/query_rewriting.go looked up the column referenced by each select expression in the relation's catalogue and used that catalogued column as the projection. For a bare column reference that is right. For a CASE, function call or concatenation it collapsed the expression to the referenced column, so CASE WHEN enabled = 'true' ... rendered as "enabled" AS "status". The catalogued column object is also shared across the statement, and alias / qualifier decoration mutated it in place, which is why a sibling plain column could vanish ("repo" AS "evidence", "repo" AS "evidence"). Now only a bare column reference binds to the catalogue, the catalogue entry is copied before decoration, and expressions fall through to the existing decorated-expression path. physical_table_indirect.go also no longer matches an empty alias against an empty lookup name (the materialized view indirect already had this guard); both files carry a file-level dupl suppression since they are now structurally identical, mirroring the existing physical_table_dto.go precedent.

#799, PURGE emptied or dropped materialized views and user space tables. The garbage collection and purge queries in sql_system/sqlite.go and sql_system/postgres.go selected every non-control table. They now exclude relations registered in __iql__.tables and __iql__.materialized_views, so PURGE, PURGE CONSERVATIVE and PURGE EPHEMERAL only touch provider cache tables. Postgres matches both the bare table name and schema.table for the export-namespace case. PURGE CACHE was already scoped to the analytics namespace and is unchanged.

Design notes: no new types or exported surface; the one helper added is unexported and local to the rewrite visitor. Six files touched, one of which is the lint suppression only.

Type of change

• Bug fix (non-breaking change to fix a bug).
• Feature (non-breaking change to add functionality).
• Breaking change.
• Other (eg: documentation change). Please explain.

Issues referenced.

• Fixes [BUG] Expressions in a SELECT over user space tables and materialized views are not evaluated #798
• Fixes [BUG] PURGE empties or drops materialized views and user space tables #799

Evidence

Reproduction from the issues against a file backend, after the fix:

$ stackql exec --sqlBackend '{"dsn":"file:bug.db"}' --output json "SELECT repo, CASE WHEN enabled = 'true' THEN 'pass' ELSE 'fail' END AS status FROM repos"
[{"repo":"a","status":"pass"},{"repo":"b","status":"fail"}]
$ stackql exec --sqlBackend '{"dsn":"file:bug.db"}' --output json "SELECT repo, json_object('enabled', enabled) AS evidence FROM repos"
[{"evidence":"{\"enabled\":\"true\"}","repo":"a"},{"evidence":"{\"enabled\":\"false\"}","repo":"b"}]
$ stackql exec --sqlBackend '{"dsn":"file:bug.db"}' --output json "SELECT repo, upper(enabled) AS u, enabled || '!' AS e FROM repos"
[{"e":"true!","repo":"a","u":"TRUE"},{"e":"false!","repo":"b","u":"FALSE"}]
$ stackql exec --sqlBackend '{"dsn":"file:bug.db"}' --output json "SELECT repo, CASE WHEN enabled = 'true' THEN 'pass' ELSE 'fail' END AS status FROM mv"
[{"repo":"a","status":"pass"},{"repo":"b","status":"fail"}]
$ stackql exec --sqlBackend '{"dsn":"file:bug.db"}' --output json "PURGE CONSERVATIVE"
[{"message":"PURGE of type 'conservative' successfully completed"}]
$ stackql exec --sqlBackend '{"dsn":"file:bug.db"}' --output json "SELECT count(*) AS n FROM mv"
[{"n":"2"}]
$ stackql exec --sqlBackend '{"dsn":"file:bug.db"}' --output json "PURGE"
[{"message":"purge 'GLOBAL' completed"}]
$ stackql exec --sqlBackend '{"dsn":"file:bug.db"}' --output json "SELECT count(*) AS n FROM mv"
[{"n":"2"}]

New robot scenario Select Expressions Over User Space Table and Materialized View Survive Purge in stackql_mocked_from_cmd_line.robot covers CASE, function and concatenation projections over a user space table and a materialized view, then purge conservative and global purge, asserting row counts survive. It is written with portable SQL so it also runs on the postgres backend in CI.

Local results (native, sqlite embedded):

• go test -timeout 1200s ./...: pass.
• golangci-lint run: 0 issues.
• Robot subset --test "*View*" --test "*Table*" --test "*Materialized*" --test "*Purge*" --test "*Userspace*" on stackql_mocked_from_cmd_line.robot: 149 of 151 pass. The two failures are Preview Rows Are Emitted Throughout The Run and its OTel variant, failing in the Python harness with AttributeError: 'float' object has no attribute 'total_seconds' under the locally available robotframework 7.5 rather than the pinned 7.0.1; unrelated to this change.

Checklist:

• A full round of testing has been completed, and there are no test failures as a result of these changes.
• The changes are covered with functional and/or integration robot testing.
• The changes work on all supported platforms.
• Unit tests pass locally, as per the developer guide.
• Robot tests pass locally, as per the developer guide.
• Linter passes locally, as per the developer guide.

Variations

Platforms: only linux/amd64 with the embedded sqlite backend was exercised locally. The postgres GC and purge SQL changes compile and follow the existing query shape but were not run against a live postgres here; the docker postgres_tcp robot jobs in CI exercise the new scenario on that backend.

Tech Debt

None added. materialized_view_indirect.go and physical_table_indirect.go were already near-duplicates; this PR makes the lookup logic identical and records that with a file-level dupl suppression rather than consolidating the two, which is out of scope for a bug fix.

🤖 Generated with Claude Code

https://claude.ai/code/session_01L8Boy4k8xPfMwnCUV8D39K

Generated by Claude Code