Skip to content

SQLite JSONB not coerced if type is capitalized. #4337

@TimVosch

Description

@TimVosch

Version

1.30.0

What happened?

#3968 introduced coercing a column to json if its type is jsonb. However, if the type is given as JSONB the coercion is not applied.

With capitalized JSONB type

const listSensors = `-- name: ListSensors :many
SELECT id, name, description, properties, created_at, updated_at, deleted_at, thing_id, feature_id FROM sensors WHERE deleted_at is NULL AND (CAST(?1 AS BOOLEAN) IS FALSE OR thing_id IN (/*SLICE:thing_ids*/?)) ORDER BY created_at
`

With uncapitalized jsonb type

const listSensors = `-- name: ListSensors :many
SELECT id, name, description, json(properties), created_at, updated_at, deleted_at, thing_id, feature_id FROM sensors WHERE deleted_at is NULL AND (CAST(?1 AS BOOLEAN) IS FALSE OR thing_id IN (/*SLICE:thing_ids*/?)) ORDER BY created_at
`

Relevant log output

Database schema

CREATE TABLE things (
   id INTEGER PRIMARY KEY NOT NULL,
   properties JSONB NOT NULL DEFAULT (JSONB('{}'))
);

CREATE TABLE things_2 (
   id INTEGER PRIMARY KEY NOT NULL,
   properties jsonb NOT NULL DEFAULT (jsonb('{}'))
);

SQL queries

-- name: ListThings :many
select * from things;

-- name: ListThings2 :many
select * from things_2;

Configuration

Playground URL

No response

What operating system are you using?

Linux

What database engines are you using?

SQLite

What type of code are you generating?

Go

Metadata

Metadata

Assignees

No one assigned

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions