MySQL left join fields are not sql.Null*

[tiehm]

Version

1.10.0

What happened?

The following SQL queries (with the schemas) produces different kinds of outputs where it really shouldn't. When you LEFT JOIN 2 tables, the tables which gets joined should have fields of type sql.Null* as they can be null.
This happens on postgresql already but not with mysql. With mysql the fields are non null fields unless they are already nullable by the original table definition.

Relevant log output

MySQL: 

const something = `-- name: something :many
SELECT id, name, bio, users.identifier, users.something FROM authors LEFT JOIN users on users.identifier = authors.id
`

type somethingRow struct {
	ID         int32
	Name       string
	Bio        sql.NullString
	Identifier int32
	Something  string
}

func (q *Queries) something(ctx context.Context) ([]somethingRow, error) {
	rows, err := q.db.QueryContext(ctx, something)
	if err != nil {
		return nil, err
	}
	// not so important
}

PostgreSQL

const something = `-- name: something :many
SELECT id, name, bio, users.identifier, users.something FROM authors LEFT JOIN users on users.identifier = authors.id
`

type somethingRow struct {
	ID         int32
	Name       string
	Bio        sql.NullString
	Identifier sql.NullInt32
	Something  sql.NullString
}

func (q *Queries) something(ctx context.Context) ([]somethingRow, error) {
	rows, err := q.db.QueryContext(ctx, something)
	if err != nil {
		return nil, err
	}
	// not so important
}

Database schema

CREATE TABLE users (
  identifier   int PRIMARY KEY,
  something text not null
);

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

SQL queries

-- name: something :many
SELECT id, name, bio, users.identifier, users.something FROM authors LEFT JOIN users on users.identifier = authors.id;

-- name: foo :many
select * from users;

Configuration

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

Playground URL

https://play.sqlc.dev/p/3ec9ff982309d7cfa286390a5d69c5488c05e8c36bc5dddab54e726ace13be11

What operating system are you using?

No response

What database engines are you using?

MySQL

What type of code are you generating?

Go

[tiehm]

I saw #733 and #983 but it seems like that it either is buggy now or never worked for this case then

[tiehm]

Installing the main branch fixes this issue with the merged #1249