Wrong suggested join condition when joining 2 models

[lbrdnk]

There is a case where wrong join condition is suggested while joining 2 models. More context can be found in this slack thread. Steps to reproduce follow.

1. Use the following sql to create a postgres database:

create table organization (
    id serial primary key,
    account_id int
);

create table account (
    id serial primary key,
    organization_id int
);

create table contact (
    id serial primary key,
    organization_id int
);

alter table
    organization
add
    constraint fk_account foreign key (account_id) references account(id);

alter table
    account
add
    constraint fk_organization foreign key (organization_id) references organization(id);

alter table
    contact
add
    constraint fk_organization foreign key (organization_id) references organization(id);

insert into organization (account_id) values (NULL);
insert into account (organization_id) values (1);
insert into contact (organization_id) values (1);
update organization set account_id = 1 where id = 1;

2. Add that database and use it as data source for next steps.

3. Now create model Account model from plain mbql query with source Account.

4. Create model Contact model from plain mbql query with source Contact.

5. Now create a new mbql query with source Account model.

6. Attempt to join Contact model in query builder.

Wrong join condition is suggested: only table Organization has column account_id. (Aside: both other tables have foreign keys to that table.)

Screen of incorrect join condition: