Skip to content

MySQL: LEFT OUTER JOINed columns not considered NULLable #1247

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

Closed
Funzinator opened this issue Oct 22, 2021 · 2 comments
Closed

MySQL: LEFT OUTER JOINed columns not considered NULLable #1247

Funzinator opened this issue Oct 22, 2021 · 2 comments
Labels
bug Something isn't working triage New issues that hasn't been reviewed

Comments

@Funzinator
Copy link

Version

1.10.0

What happened?

When I join another table using LEFT OUTER JOIN, the referenced colums are not treated as potential NULL.

For example, int32 is used instead of sql.NullInt32

Relevant log output

No response

Database schema

CREATE TABLE authors
(
    id INT(10) NOT NULL,
    name VARCHAR(255) NOT NULL,
    parent_id INT(10),
    PRIMARY KEY (id)
);

CREATE TABLE parents
(
    id INT(10) NOT NULL,
    name VARCHAR(255) NOT NULL,
    PRIMARY KEY (id)
);

SQL queries

-- name: AllAuthors :many
SELECT authors.id,
       authors.name,
       parents.id as alias_id,
       parents.name as alias_name
FROM authors LEFT OUTER JOIN parents ON (authors.parent_id = parents.id);

Configuration

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

Playground URL

https://play.sqlc.dev/p/ce7fec524f816ea7f485c8d9b83c60a0e36c7de065db7d23150d7271ce59bd90

What operating system are you using?

macOS

What database engines are you using?

MySQL

What type of code are you generating?

Go

@Funzinator Funzinator added bug Something isn't working triage New issues that hasn't been reviewed labels Oct 22, 2021
@Funzinator
Copy link
Author

@timstudd, thank you. This fix already enabled me to get rid of a bunch of COALESCE statements.
Unfortunately, it still doesn't work with LEFT OUTER JOINs with the same table. It might be related to #1237.

I've prepared an example.

Should I open another ticket for that or is it OK to track it here?

@Funzinator
Copy link
Author

Thank you so much!

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