My questions are loading find in one instances and then a few moments later giving an error without any changes to base model being applied. See screenshot.
This is happening very often and is super frustrating. What have I setup poorly to lead to this?
TonyC
May 13, 2024, 11:48am
2
Yeah there are some issues around bigquery when using nested joins and also some space in names, for reference:
opened 09:23PM - 07 May 24 UTC
Type:Bug
Priority:P2
Querying/MBQL
.Backend
.Needs Triage
.Team/QueryProcessor
### Describe the bug
If you use the GUI query builder to create multi-level agg… regation in BigQuery the outermost SELECT contains incorrect aliases for fields from the subquery if:
- The joined source table has a space in the name OR
- The field from the joined source table has a space in the name
### To Reproduce
1. Create two test tables in Big Query - one should have a space in the name, the other should not
2. Join from the table that doesn't have a space to the table that does
3. Group the results by a column on the the table with a space in the name
4. Summarize again on the grouped results - when you aggregate the second time the query fails because the main select statement doesn't include the correct alias for the field from the subquery
You can also reproduce this with two tables that have no spaces in the name if you're summarizing on a field that has a space in the name:
### Expected behavior
The correct SQL should be generated
### Logs
BQ Error:
https://www.googleapis.com/bigquery/v2/projects/data-mb-analysis/queries { "code": 400, "errors": [ { "domain": "global", "location": "q", "locationType": "parameter", "message": "Name Employee Test - EmpCode__EmpLName not found inside source at [2:21]", "reason": "invalidQuery" } ], "message": "Name Employee Test - EmpCode__EmpLName not found inside source at [2:21]", "status": "INVALID_ARGUMENT" }
### Information about your Metabase installation
```JSON
{
"browser-info": {
"language": "en-US",
"platform": "Win32",
"userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/124.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.23+9",
"java.vendor": "Eclipse Adoptium",
"java.vendor.url": "https://adoptium.net/",
"java.version": "11.0.23",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "11.0.23+9",
"os.name": "Linux",
"os.version": "5.15.133.1-microsoft-standard-WSL2",
"user.language": "en",
"user.timezone": "GMT"
},
"metabase-info": {
"databases": [
"h2",
"postgres",
"bigquery-cloud-sdk"
],
"hosting-env": "unknown",
"application-database": "postgres",
"application-database-details": {
"database": {
"name": "PostgreSQL",
"version": "11.22 (Debian 11.22-1.pgdg110+1)"
},
"jdbc-driver": {
"name": "PostgreSQL JDBC Driver",
"version": "42.7.2"
}
},
"run-mode": "prod",
"version": {
"date": "2024-05-02",
"tag": "v1.49.8",
"hash": "38cb850"
},
"settings": {
"report-timezone": null
}
}
}
```
### Severity
Pretty annoying but you can technically work around it by adding an aggregate in the of the offending field in the "summarize" box of the first query and then doing your final aggregate on that aggregate
### Additional context
_No response_
What version of metabase are you running?
Vinny
September 24, 2024, 2:12pm
3
I have the exact same issue for quite some time now.
