Model does not work for two foreign keys towards one physical table

[sanaered]

Hi, I read Field filter fails if it references a column from an aliased table · Issue #3324 · metabase/metabase · GitHub so I'm aware that aliases are an issue. I thought it was only when you use SQL to create your model, but also the Notebook Editor can't handle multiple joins to the same table.

Simplified example: I have a 'hotel booking' fact table which holds the booking date and the stay date for each booking. And of course I have a shared dimension with all dates.

When I create two joins using the different columns for stay date and book date, I don't get the correct result, he only uses the first relationship, duplicates it. Also the label shows that it is in fact just using the first relationship.

According to me, this is basic functionality that should be available. I know that the workaround is to create a view for every alias of my dates dimension. But I'm not happy with that.

[notrom]

I've tripped over these sorts of things quite a few times. It doesn't seem to like joining on the same table more than once, it seems to produce a few different issues. There are a few issues in Github for that sort of thing e.g. Display and UI/UX issues with multiple joins to the same table / renaming a column renames all with the same name at the same time · Issue #12685 · metabase/metabase · GitHub and the related ones noted there.

Giving them a thumbs up over there might help raise the priority.

[sanaered]

Thanks for the info. I added some thumbs in GitHub. It does, however, pain me to see that these tickets have been open for > 3 years.

[Luiggi]

I’m a bit confused, is the issue the field filter with aliases or joins not happening correctly? Can you provide repro steps with the sample database?

[sanaered]

It is about joins not happening correctly. I was able to reproduce it on the sample database.

Step 1: alter data model for Sample Database in admin settings: re-label PRODUCT_ID in ORDERS table to Manager ID and use it as a second foreign key to the PEOPLE table.

Step 2: create a model on these tables. Start from Orders, join twice to People, first with User ID, second with Manager ID. Pick ID, User ID and Manager ID from Orders. Pick ID and Name from each People table.
At first sight the result looks OK except for the strange labels on the last two columns: I would have expected a reference to 'Manager' in there. But the data looks OK, so I could live with that.

I was starting to think that the issue I previously saw was maybe related to my postgreSQL database. So I was thinking about moving some sample data to a postgreSQL database and do the same test. But I went to dinner first.

And when I came back an hour later, the result had changed!!!! I first couldn't believe my eyes, but it was exactly the same as what I reported: only the first relationship gets used.

And strangely, when you just open up the tabular data for this model, you get other labels, now mentioning 'manager':

Going back into the query editor, also there the labels change:

Summarizing, I detect 3 issues:

1. the resulting data set from a model changes over time
2. multiple joins to one physical table do not work
3. in that case the labelling of the columns out of these joins is not correct and changes over time

Especially the 'it changes over time' baffles me. Caching is off. How is this possible?