Relation does not exist on update only

[dimmerz92]

Version

1.28.0

What happened?

Expected sqlc generate to generate all queries.

I tried removing queries one by one and found that generating worked for everything except for UPDATE statements.

No issues in sqlc playground.

Relevant log output

# package models
sql/queries/users.sql:45:1: relation "Users" does not exist
sql/queries/users.sql:53:1: relation "Users" does not exist

Database schema

-- +goose Up
CREATE TABLE Users (
	ID INTEGER PRIMARY KEY,
	FullName TEXT NOT NULL,
	Email TEXT UNIQUE NOT NULL CHECK (Email LIKE '%_@%_.%__'),
	Password BLOB NOT NULL,
	CreatedAt INTEGER NOT NULL DEFAULT (unixepoch('now')),
	UpdatedAt INTEGER
);

-- +goose StatementBegin
CREATE TRIGGER UpdateUser
AFTER UPDATE ON Users
BEGIN
	UPDATE
		Users
	SET	
		UpdatedAt = unixepoch('now')
	WHERE
		ID = NEW.ID;
END;
-- +goose StatementEnd

-- +goose Down
DROP TABLE Users;

SQL queries

-- name: AddUser :one
INSERT INTO Users (
	FullName,
	Email,
	Password
)
VALUES (
	@FullName,
	@Email,
	@Password
)
RETURNING
	*;

-- name: CheckEmailExists :one
SELECT
	1
FROM
	Users
WHERE
	Email = @email;

-- name: GetUser :one
SELECT
	*
FROM
	Users
WHERE
	ID = @userID OR
	Email = @email;

-- name: GetPaginatedUsers :many
SELECT
	*
FROM
	Users
ORDER BY
	ID
LIMIT
	@limit
OFFSET
	@offset;

-- name: UpdateEmail :exec
UPDATE
	Users
SET
	Email = @email
WHERE
	ID = @userID;

-- name: UpdatePassword :exec
UPDATE
	Users
SET
	Password = @password
WHERE
	ID = @userID;

-- name: DeleteUser :exec
DELETE FROM
	Users
WHERE
	ID = @userID;

Configuration

version: 2
sql:
  - engine: sqlite
    queries: sql/queries
    schema: sql/schema
    gen:
      go:
        package: models
        out: models

Playground URL

https://play.sqlc.dev/p/0fa9a40b4593ee2b4dd899dd126591b50bf92961b5f13c41372b4eab4d0ea668

What operating system are you using?

macOS

What database engines are you using?

SQLite

What type of code are you generating?

Go

[dimmerz92]

Just to follow up on this one, I went back a version to v1.27.0 and it works as expected.

Seems v1.28.0 might have an issue with UPDATE statements?

Would also explain why there are no issues on the playground.

[pdenapo]

I can confirm the bug, and provide a minimal example

schema.sql:

CREATE TABLE IF NOT EXISTS table_A(
    id integer primary key,
    info text 
);

query.sql

-- name: Update_table :one
UPDATE table_A  SET info=?  WHERE id=? RETURNING *;

with version 1.28.0 gives

query.sql:1:1: relation "table_A" does not exist

with 1.27.0 it works (it is a regression).

Strangely enough, it seems to depend on the table name. Change table_A to table and it works!

On the other hand, sqlite considers table_A to be a valid table name.

sqlite> CREATE TABLE IF NOT EXISTS table_A(
id integer primary key,
info text
);
sqlite> UPDATE table_A SET info='test' WHERE id=1 RETURNING *;
sqlite>

so it seems to be a parsing error...

[pdenapo]

@dimmerz92 When testing somehing in the playground, you should set sqlc-version.txt to the version that you want to test.
It uses 1.25.0 by default. If you set it to 1.28.0, it will show that it does not work in your example!

https://play.sqlc.dev/p/0fa9a40b4593ee2b4dd899dd126591b50bf92961b5f13c41372b4eab4d0ea668

[pdenapo]

My previous playground link was wrong. Here is the correct one (I hope!)

https://play.sqlc.dev/p/7c0742c55d8324ff22e4fbf5a287dab51b513e6b12f2940551f08c4d7797e414

[dimmerz92]

@dimmerz92 When testing somehing in the playground, you should set sqlc-version.txt to the version that you want to test. It uses 1.25.0 by default. If you set it to 1.28.0, it will show that it does not work in your example!

https://play.sqlc.dev/p/0fa9a40b4593ee2b4dd899dd126591b50bf92961b5f13c41372b4eab4d0ea668

@pdenapo 1.28 was not available in the playground at the time and it caused errors.

[floppyman]

The same issue exists for version 1.29.0
It gives the same error for update statements.

And https://play.sqlc.dev/ gives a 500: Internal Server Error if you try to change the beyond version 1.25.0

[chimbori]

This is a show-stopper bug; I can confirm this breaks even with v1.29.0 locally, and that the Playground throws a 500 when changing sqlc-version.txt to v1.29.0

[compiledpanda]

I ran into this yesterday and found the issue. The relation/table name is not being lowercase when parsing/converting update statements like it is in select and insert statements. I opened PR #4028 to fix it.