Skip to content

MySQL column references for joined tables not found in UPDATE statements #1288

Closed
@timstudd

Description

@timstudd

Version

1.10.0

What happened?

sqlc fails to generate when columns of a joined table are referenced in an UPDATE statement. It should be able to generate successfully.

Relevant log output

sqlc generate failed.
# package db
query.sql:16:1: column "user_id" does not exist
query.sql:24:1: column "user_id" does not exist
query.sql:32:1: column "user_id" does not exist

Database schema

CREATE TABLE primary_table (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  user_id bigint(20) unsigned NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE join_table (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  primary_table_id bigint(20) unsigned NOT NULL,
  other_table_id bigint(20) unsigned NOT NULL,
  is_active tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (id)
);

SQL queries

-- name: UpdateJoin :exec
UPDATE  join_table as jt
        JOIN primary_table as pt
            ON jt.primary_table_id = pt.id
SET     jt.is_active = ?
WHERE   jt.id = ?
        AND pt.user_id = ?;

-- name: UpdateLeftJoin :exec
UPDATE  join_table as jt
        LEFT JOIN primary_table as pt
            ON jt.primary_table_id = pt.id
SET     jt.is_active = ?
WHERE   jt.id = ?
        AND pt.user_id = ?;

-- name: UpdateRightJoin :exec
UPDATE  join_table as jt
        RIGHT JOIN primary_table as pt
            ON jt.primary_table_id = pt.id
SET     jt.is_active = ?
WHERE   jt.id = ?
        AND pt.user_id = ?;

Configuration

{
	"version": "1",
	"packages": [
		{
			"path": "db",
			"engine": "mysql",
			"schema": "query.sql",
			"queries": "query.sql"
		}
	]
}

Playground URL

https://play.sqlc.dev/p/07be9068523e941e24fd73bea8ec61cbfa82e4ca653d322832e09a1fd2574f8f

What operating system are you using?

Linux

What database engines are you using?

MySQL

What type of code are you generating?

Go

Metadata

Metadata

Assignees

No one assigned

    Labels

    bugSomething isn't workingtriageNew issues that hasn't been reviewed

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions