sqlite: `json_each`'s `value` parameter isn't supported

[jamietanna]

Version

1.15.0 - 1.23.0

What happened?

The noted query produces an error:

query/query.sql:72:1: column reference "value" not found
exit status 1
db/generate.go:3: running "go": exit status 1

When running go generate, with the following build tag:

//go:generate go run github.com/kyleconroy/sqlc/cmd/sqlc generate

When running this in an sqlite3 v3.38.5 shell, this produces:

slug                       raw                                                           key  value  type  atom  id  parent  fullkey  path  count(*)
-------------------------  ------------------------------------------------------------  ---  -----  ----  ----  --  ------  -------  ----  --------
/posts/2022/9/4/cXkdvjoj/  {"type":["h-entry"],"properties":{"category":["foo","bar"],"  1    bar    text  bar   2           $[1]     $     9       
                           published":["2022-09-04T20:17:13+01:00"],"updated":["2022-09                                                             
                           -04T20:17:13+01:00"],"visibility":["private"]}}                                                                          

For the following entry in the database:

select * from posts WHERE slug = '/posts/2022/9/4/cXkdvjoj/';
/posts/2022/9/4/cXkdvjoj/|{"type":["h-entry"],"properties":{"category":["foo","bar"],"published":["2022-09-04T20:17:13+01:00"],"updated":["2022-09-04T20:17:13+01:00"],"visibility":["private"]}}

Relevant log output

No response

Database schema

CREATE TABLE IF NOT EXISTS posts (
  slug text NOT NULL PRIMARY KEY,
  raw text NOT NULL -- raw is a JSON object
);

SQL queries

select
value, count(value)
  -- json_extract(posts.raw, '$.properties.category') category
  from posts, json_each(json_extract(posts.raw, '$.properties.category'))
  GROUP BY value
  ORDER BY count(value) DESC
  ;

Configuration

{
  "version": "1",
  "packages": [
    {
      "path": "sqlite",
      "name": "posts",
      "schema": "schema",
      "queries": "query",
      "engine": "sqlite",
      "emit_json_tags": true,
      "emit_prepared_queries": true,
      "emit_interface": true
    }
  ]
}

Playground URL

No response

What operating system are you using?

Linux

What database engines are you using?

No response

What type of code are you generating?

Go

[jamietanna]

This also appears to be true when the following query is used:

-- name: RetrievePublicPostsForCategory :many
select
  category.value,
  slug,
  raw,
  json_extract(posts.raw, '$.properties.published[0]') published,
  json_extract(posts.raw, '$.properties.visibility[0]') visibility
  from
    posts,
    json_each(json_extract(posts.raw, '$.properties.category')) category
  WHERE visibility == 'public'
  AND category.value = ?
  ORDER BY published DESC
  LIMIT 50;

But I get:

query/query.sql:131:1: table alias "category" does not exist

[jamietanna]

It looks like we can reference json_each.value can be referenced when wrapping it into a json_group_array, just not as a standalone field (with v1.17.0)

Schema:

CREATE TABLE IF NOT EXISTS renovate (
  organisation TEXT NOT NULL,
  repo TEXT NOT NULL,

  package_name TEXT NOT NULL,
  version TEXT NOT NULL,
  locked_version TEXT,

  package_manager TEXT NOT NULL,
  package_file_path TEXT NOT NULL,

  datasource TEXT NOT NULL,
  -- dep_types is a JSON array
  dep_types TEXT,

  UNIQUE (organisation, repo, package_file_path, package_name, package_manager, dep_types) ON CONFLICT REPLACE
);

Query:

select
count(*),
package_name,
dt.value as dep_type
from
renovate,
json_each(renovate.dep_types) as dt
where package_name like 'github.com/gorilla/%'
and datasource = 'go'
group by package_name order by count(*) DESC;

[jamietanna]

@kyleconroy looks like this may also affect Postgres.

With the following diff:

diff --git a/internal/endtoend/testdata/table_function/postgresql/stdlib/query.sql b/internal/endtoend/testdata/table_function/postgresql/stdlib/query.sql
index 94da2699..b68d262b 100644
--- a/internal/endtoend/testdata/table_function/postgresql/stdlib/query.sql
+++ b/internal/endtoend/testdata/table_function/postgresql/stdlib/query.sql
@@ -8,9 +8,10 @@ CREATE TABLE transactions (
 /* name: GetTransaction :many */
 SELECT
        json_extract(transactions.data, '$.transaction.signatures[0]'),
-       json_group_array(instructions.value)
+       json_group_array(instructions.value),
+  instructions.value
 FROM
   transactions, 

Results in:

make regen
go build -o ~/bin/sqlc-dev ./cmd/sqlc/
go build -o ~/bin/sqlc-gen-json ./cmd/sqlc-gen-json
go run ./scripts/regenerate/
2023/03/18 10:51:11 internal/endtoend/testdata/table_function/postgresql/stdlib: sqlc-dev generate failed
# package querytest
query.sql:12:3: column "value" does not exist
exit status 1
make: *** [Makefile:24: regen] Error 1
make regen  10.29s user 4.31s system 128% cpu 11.330 total

[jamietanna]

Related to https://github.com/kyleconroy/sqlc/issues/1480 and https://github.com/kyleconroy/sqlc/issues/1766

[josharian]

Ping. :) Still reproduces with sqlc 1.20

[jamietanna]

Reproducer on 1.23 https://play.sqlc.dev/p/fbd33981c0f4df63392ac49cfb8179691a133969a68762b73c97a7a21be09f55

[traut]

it looks I'm hitting a related issue: v1.29.0 does not see a named parameter inside json_each, it is completely ignored