sqlc
sqlc copied to clipboard
sqlc-dev
fix(engine/sqlite): added json_tree and json_each definitions
@kyleconroy I would like to introduce the concept of hidden table for tables like json_tree and sqlite_schema, which are included in the catalog but for which I do not want to generate code. Do you have any ideas?
I gave this a try locally, as the underlying bug is blocking us. Unfortunately, it still doesn't work for me on this sqlite query:
-- name: LookUpVendors :many
SELECT
"vendor".*
FROM
vendor AS "vendor",
json_each("vendor".benefits) AS "benefit",
json_each("vendor".states) AS "state"
WHERE
"state".value IN (@state, "National Coverage")
AND "benefit".value = @benefit;
Schema (extract):
CREATE TABLE vendor(
id text PRIMARY KEY,
name text NOT NULL,
benefits text NOT NULL,
states text NOT NULL
);
Error message:
query.sql:10:2: 396: column "value" does not exist
When I try the query in sqlite3 directly, it works.
Thanks so much for working on this!
Also, FYI, this rewrite works in v1.19 but fails with this PR:
-- name: LookUpVendors :many
SELECT
vendor.*
FROM
vendor,
json_each(vendor.benefits) AS b,
json_each(vendor.states) AS s
WHERE
CAST(s.value AS text) IN (@state, "US")
AND CAST(b.value AS text) = @benefit;
...although that query in v1.19 generates a miscompile, with a method signature like:
func (q *Queries) LookUpVendors(ctx context.Context, benefit interface{}) ([]*Vendor, error) {
(note that it only takes a single benefit argument, not a benefit and a state arg.)
@josharian thanks!
workaround:
-- name: LookUpVendors :many
SELECT
vendor.*
FROM
vendor,
json_each(vendor.benefits) AS benefit,
json_each(vendor.states) AS state
WHERE
state.value IN (CAST(@state AS TEXT), 'National Coverage')
AND benefit.value = CAST(@benefit AS TEXT);
- avoid double quoted identifiers because of a bug in handling them
- [x] #2576
- CAST parameters because there is a bug in the combination of table_function and parameter type inference.
You can avoid the problem by doing these two things.
