Fields not nullable when INNER JOINing to a LEFT JOIN

This contrived example:

CREATE TABLE users (
  user_id    INT PRIMARY KEY,
  city_id    INT -- nullable
);
CREATE TABLE cities (
  city_id    INT PRIMARY KEY,
  mayor_id   INT NOT NULL
);
CREATE TABLE mayors (
  mayor_id   INT PRIMARY KEY,
  full_name  TEXT NOT NULL
);

-- name: GetMayors :many
SELECT
    user_id,
    mayors.full_name
FROM users
LEFT JOIN cities USING (city_id)
INNER JOIN mayors USING (mayor_id);

Produces this results struct:

type GetMayorsRow struct {
	UserID   int32
	FullName string
}

Running this query will fail if city_id is NULL. Because of the left join, the correct type for FullName is sql.NullString.

This might be a duplicate of #374? Just ran into this when doing a FULL JOIN.

Outer joins definitely aren't supported yet, but mayors.full_name shouldn't be null with the provided query; both joins would need to be left joins.

mysql> SELECT * FROM users;
+---------+---------+
| user_id | city_id |
+---------+---------+
|       1 |    NULL |
|       2 |       1 |
+---------+---------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM mayors;
+----------+-----------+
| mayor_id | full_name |
+----------+-----------+
|        1 | bob       |
+----------+-----------+
1 row in set (0.00 sec)

mysql> SELECT * FROM cities;
+---------+----------+
| city_id | mayor_id |
+---------+----------+
|       1 |        1 |
+---------+----------+
1 row in set (0.00 sec)

mysql> SELECT
    ->     user_id,
    ->     mayors.full_name
    -> FROM users
    -> LEFT JOIN cities USING (city_id)
    -> INNER JOIN mayors USING (mayor_id);
+---------+-----------+
| user_id | full_name |
+---------+-----------+
|       2 | bob       |
+---------+-----------+
1 row in set (0.00 sec)

mysql> SELECT
    ->     user_id,
    ->     mayors.full_name
    -> FROM users
    -> LEFT JOIN cities USING (city_id)
    -> LEFT JOIN mayors USING (mayor_id);
+---------+-----------+
| user_id | full_name |
+---------+-----------+
|       1 | NULL      |
|       2 | bob       |
+---------+-----------+
2 rows in set (0.00 sec)

Please see #983 for WIP on proper support.

Should this be closed, seeing as #983 fixed it?

I just stumbled upon this again. The example still produces the incorrect type, see this playground link.

Could we maybe reopen the issue?

Same issue, with a inner join followed by left joins.

Navigation Menu

Repository navigation

Fields not nullable when INNER JOINing to a LEFT JOIN #604

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Fields not nullable when INNER JOINing to a LEFT JOIN #604

Description

Activity

davherrmann commented on Oct 11, 2020

jwilner commented on Apr 26, 2021

jwilner commented on Apr 26, 2021

fr3fou commented on Jun 21, 2021

davherrmann commented on Mar 1, 2022

GuillaumeDesforges commented on Jan 10, 2025

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions