Repository navigation
fix(sqlite): type a value of one of several types as unknown - #49
Conversation
|
The latest updates on your projects. Learn more about Vercel for GitHub.
|
There was a problem hiding this comment.
Important
Two paths still give an integer type to values that can be text. In each one the PR claims the case is handled. Both were reproduced with this PR's rebuilt packages/sqlite/engine.wasm.
Reviewed changes
Reviewed the whole PR: a new rule for how SQLite types a value chosen from one of several, with its oracle case, unit test, docs bullet and rebuilt artifact.
values_type: the result isunknownwhen any non-NULL value that is not a parameter has no known type. Otherwise it issharedover the non-NULL values.- Compound columns:
select_columnnow usesvalues_type.columns()no longer falls back to the declared type for a top-level compound. lag/leadwith a default: the column is typed over the first argument and the default.- Tests and docs: there is a new oracle case
sqlite_mixed_values, a new engine test, and a new bullet on the SQLite page.tests/fingerprint.test.tspasses on the rebuiltengine.wasm.
claude-opus-5-5 ｜ 𝕏
034772d to
724b0be
Compare
There was a problem hiding this comment.
Important
Both earlier findings are fixed. The new argument() arm covers only the default, though, so a parameter given as lag/lead's first argument is now typed integer for its column while the parameter itself stays untyped. On main that column was unknown.
Reviewed changes
Reviewed 724b0be, which landed after the prior review.
- Declared-type fallback removed:
columns()no longer falls back to SQLite's declared type for any column. I ran the same statements onmainand on the PR'sengine.wasm: table columns, subqueries, views, FTS5, generated columns,WITHOUT ROWID,json_each,pragma_table_info, CTEs and unions. Only the intended compound columns changed.tests/fingerprint.test.tspasses. lag/leaddefault parameter:argument()gives a parameter in argument 2 the first argument's type.- Tests: the engine test now covers the subquery, view and
WITHcompounds andlag(n, 1, :p). Thesqlite_mixed_valuesoracle case has matching statements.
claude-opus-5-5 ｜ 𝕏
There was a problem hiding this comment.
✅ No new issues found.
Reviewed changes
Reviewed 952342e, which fixes the last open finding.
- Symmetric
lag/leadparameter typing: when there are three or more arguments, a parameter inargument()takes the type of the other value argument (n.args[2 - i]), so the value and the default each get the other's type. This matches whatvalues_typeassumes for the column, solag(:p, 1, n)now types bothpandvasinteger. Likeothers(), the arm givesNonewhen the other argument's type is unknown. - Tests: the engine test loops over both parameter positions and checks the column and the parameter. The oracle case adds
lag_parameter_value.sql, blessed as(p: integer) -> { v: integer? }.tests/fingerprint.test.tspasses on the committedpackages/sqlite/engine.wasm.
claude-opus-5-5 ｜ 𝕏
A compound's column, CASE, coalesce, ifnull, iif, min and max of several arguments, and lag and lead with a default skipped an argument of no known type, so a union of an integer column and json_extract was typed integer. Such an argument now makes the value unknown, except a parameter, which takes the others' type. A compound's column no longer falls back to the leftmost arm's declared type, and lag and lead read their default.
SQLite names a compound's declared type by one arm, so a compound read through a subquery, view or WITH query took it. Nothing else needed the declared-type fallback, so it is gone. A parameter as lag's or lead's default now takes the first argument's type.
952342e to
3c8eae5
Compare
SQLite hands back one of several values from a compound's column,
CASE,coalesce,ifnull,iif, aminormaxof several arguments, andlagorleadwith a default. The engine typed such a value by the values it knew and skipped one of no known type, so these were typedintegerthough they can return text, onSTRICTtables too:unknown, unless it is a parameter, which still takes the others' type (select n from t union all select :pkeeps:pan integer). A NULL literal still says nothing of the type.lagandleadwith a default take the type they share with the default.oracle/cases/sqlite_mixed_valuesholds each case overSTRICTtables and an untyped column, every statement run; the engine's testa_value_of_one_of_several_types_is_unknowncovers the parameter and NULL arms.packages/sqlite/engine.wasmis rebuilt.Locally the full oracle with
--strict-coverage, the engine tests, the loader's tests, the labs and the examples pass, except the loader test and the driver-matrix row for JSON built inside a scalar subquery: they fail the same way onmainonce the machine's SQLite is 3.54.1, which no longer passes JSON's subtype through a scalar subquery. That is separate from this change.