Fields not nullable when INNER JOINing to a LEFT JOIN

[maxhawkins]

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.

[davherrmann]

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

[jwilner]

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)

[jwilner]

Please see #983 for WIP on proper support.

[fr3fou]

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

[davherrmann]

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

Could we maybe reopen the issue?

[GuillaumeDesforges]

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