Repository navigation
[BUG] Expressions in a SELECT over user space tables and materialized views are not evaluated #798
Description
Describe the bug
With a file based sqlite backend, a SELECT over a user space table or a materialized view returns wrong values for every computed column. A CASE expression returns the raw value of the column it references, a function call or concatenation returns the value of a different column, and a plain column in the same select list can disappear from the result. Plain column references and string literals are fine.
The same statement run against the backend file with the sqlite3 library returns the correct result, so the stored rows are right and the problem is in how the projection is built.
To Reproduce
No provider is needed.
$ stackql exec --sqlBackend '{"dsn":"file:bug.db"}' --output json "CREATE TABLE repos (repo text, enabled text)"
DDL Execution Completed
$ stackql exec --sqlBackend '{"dsn":"file:bug.db"}' --output json "INSERT INTO repos (repo, enabled) VALUES ('a', 'true'), ('b', 'false')"
insert into table completed
$ 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":"true"},{"repo":"b","status":"false"}]
$ stackql exec --sqlBackend '{"dsn":"file:bug.db"}' --output json "SELECT repo, json_object('enabled', enabled) AS evidence FROM repos"
[{"evidence":"a"},{"evidence":"b"}]
$ stackql exec --sqlBackend '{"dsn":"file:bug.db"}' --output json "SELECT repo, upper(enabled) AS u, enabled || '!' AS e FROM repos"
[{"e":"a","u":"true"},{"e":"b","u":"false"}]
$ stackql exec --sqlBackend '{"dsn":"file:bug.db"}' --output json "CREATE MATERIALIZED VIEW mv AS SELECT repo, enabled FROM repos"
DDL Execution Completed
$ 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":"true"},{"repo":"b","status":"false"}]
The same statement against the file with the sqlite3 library:
$ python -c "import sqlite3; print(sqlite3.connect('bug.db').execute(\"SELECT repo, CASE WHEN enabled = 'true' THEN 'pass' ELSE 'fail' END AS status FROM mv\").fetchall())"
[('a', 'pass'), ('b', 'fail')]
Expected behavior
[{"repo":"a","status":"pass"},{"repo":"b","status":"fail"}]
[{"evidence":"{\"enabled\":\"true\"}","repo":"a"},{"evidence":"{\"enabled\":\"false\"}","repo":"b"}]
[{"e":"true!","repo":"a","u":"TRUE"},{"e":"false!","repo":"b","u":"FALSE"}]
Environment
- stackql v0.12.718 Windows (0dba3cf), Windows 11
--sqlBackend '{"dsn":"file:bug.db"}', default in memory backend not tested because the table has to outlive the process
Additional context
Found while building a snapshot then evaluate workflow: StackQL collects provider data into materialized views in a file backend, then checks run as SQL over those views. Expressions over provider resources directly appear to be fine. Workaround: run the analysis SQL against the sqlite file with a normal sqlite client.
Activity