Mapping custom model columns to two different tables' fields that have the same name

Metabase Version: 0.43.1
Database: MySQL

Issue:

I have created a custom model with sql. I chose to map a few of the columns to their associated table fields in the database.

There is a field in "table_a" called "eng_name" and a field in "table_b" called "eng_name". In my model I have mapped "Column X "to it's corresponding table field table_a.eng_name and "Column Y" to it's corresponding table field table_b.eng_name. I am using table aliases in my query so it is clear that these fields are from different tables.

However, when I ask a question with the QB that groups by "Column X", the values in "Column Y" appear. My guess is that it has to do with the fact that in the database the fields have the same name "eng_name".

Expected Behavior: When I group by "Column X" (which, in the metadata, is mapped to table_a.eng_name), the values from table_a.eng_name appear.

Actual Behavior: When I group by "Column X" (which, in the metadata, is mapped to table_a.eng_name) , the values from table_b.eng_name appear.

UPDATE:

I have found where this happens with another set of different tables that have fields with the same name.

Is there any workaround or solution for this, like having to change the field names completely in the database?

Hi @pjhaberman
Post "Diagnostic Info" from Admin > Troubleshooting.
I cannot reproduce. Use a View on your database instead of Models if you are having problems. Or use SQL as the base of the Model.

{
"browser-info": {
"language": "en-US",
"platform": "Win32",
"userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/101.0.0.0 Safari/537.36",
"vendor": "Google Inc."
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "11.0.15+10",
"java.vendor": "Eclipse Adoptium",
"java.vendor.url": "https://adoptium.net/",
"java.version": "11.0.15",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "11.0.15+10",
"os.name": "Linux",
"os.version": "4.15.0-172-generic",
"user.language": "en",
"user.timezone": "Asia/Shanghai"
},
"metabase-info": {
"databases": [
"h2",
"mysql"
],
"hosting-env": "unknown",
"application-database": "mysql",
"application-database-details": {
"database": {
"name": "MySQL",
"version": "5.7.26-log"
},
"jdbc-driver": {
"name": "MariaDB Connector/J",
"version": "2.7.5"
}
},
"run-mode": "prod",
"version": {
"date": "2022-05-16",
"tag": "v0.43.1",
"branch": "release-x.43.x",
"hash": "7f1a1c4"
},
"settings": {
"report-timezone": null
}
}
}

The base of my custom model is SQL.

Also, I just recreated the SQL query in the QB and do not encounter these problems.

@pjhaberman There's several issues with Models: https://github.com/metabase/metabase/issues?q=is%3Aissue+is%3Aopen+label%3AQuerying%2FModels

Use Views on your database as an alternative.

Is there still no fix for this?

@pjhaberman Try reproducing with Sample Database, then we can all play along.