Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Closed
timstudd opened this issue Nov 16, 2021 · 0 comments
Closed
Labels
bug Something isn't working triage New issues that hasn't been reviewed

Comments

@timstudd
Copy link
Contributor

timstudd commented Nov 16, 2021

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

@timstudd timstudd added bug Something isn't working triage New issues that hasn't been reviewed labels Nov 16, 2021
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Something isn't working triage New issues that hasn't been reviewed
Projects
None yet
Development

No branches or pull requests

1 participant