Casing issue with DROP TABLE statement ...

[TorrinLynn]

Version

1.29.0

What happened?

If schema.sql contains a mixed case table name, you can only drop the table using lowercase name, otherwise, you get a relation "<table name>" does not exist. The following schema.sql illustrates the problem:

CREATE TABLE Authors (
  id   INTEGER PRIMARY KEY,
  name text    NOT NULL
);

DROP TABLE Authors; -- generates relation "Authors" does not exist error

Changing the drop statement to DROP TABLE authors fixes the reported error.

The bug first appeared in 1.28.0 and continues to exist in 1.29.0

Relevant log output

# package tutorial
schema.sql:1:1: relation "Authors" does not exist
schema.sql:1:1: relation "authors" already exists

Database schema

CREATE TABLE Authors (
  id   INTEGER PRIMARY KEY,
  name text    NOT NULL
);

DROP TABLE Authors;

CREATE TABLE Authors (
  id   INTEGER PRIMARY KEY,
  name text    NOT NULL,
  bio  text
);

SQL queries

Configuration

version: "2"
sql:
  - engine: "sqlite"
    queries: "query.sql"
    schema: "schema.sql"
    gen:
      go:
        package: "tutorial"
        out: "tutorial"

Playground URL

No response

What operating system are you using?

macOS

What database engines are you using?

SQLite

What type of code are you generating?

Go

[TorrinLynn]

Also found that capitalizing the table name for update queries causes an error:

-- name: UpdateAuthor :exec
UPDATE Authors
set name = ?,
bio = ?
WHERE id = ?;

Generates the following error: query.sql:18:1: relation "Authors" does not exist

SELECT, INSERT, and DELETE statements don't seem to care about table name casing.

[TorrinLynn]

Using Alter Table with rename is also case sensitive.

CREATE TABLE authors (
  id   INTEGER PRIMARY KEY,
  name text    NOT NULL
);

DROP TABLE authors;

CREATE TABLE authors_new (
  id   INTEGER PRIMARY KEY,
  name text    NOT NULL,
  bio  text
);

ALTER TABLE authors_new
    RENAME TO Authors;

-- name: GetAuthor :one
SELECT * FROM authors
WHERE id = ? LIMIT 1;

-- name: ListAuthors :many
SELECT * FROM authors
ORDER BY name;

-- name: CreateAuthor :one
INSERT INTO Authors (
  name, bio
) VALUES (
  ?, ?
)
RETURNING *;

-- name: UpdateAuthor :exec
UPDATE authors
set name = ?,
bio = ?
WHERE id = ?;

-- name: DeleteAuthor :exec
DELETE FROM authors
WHERE id = ?;

Causes the following errors:

query.sql:1:1: relation "authors" does not exist
query.sql:6:1: relation "authors" does not exist
query.sql:10:1: relation "authors" does not exist
query.sql:18:1: relation "authors" does not exist
query.sql:24:1: relation "authors" does not exist

One for each SQL statement.