I have a view that returns 8 rows and the values of columns of type Decimal/Float is different in database vs what's displayed on output of a Question. Any thoughts why this would happen?
When I run the view on database(postgresql) it's displaying correct values, but the same query on a Question yields a different value for those measures.
1 Like
When I created a materialized view of the actual view, the correct data gets shown on Metabase though. Not sure where the issue is.
Hi @subzero355
It would be useful to know exactly how are they different? Do you see rounding errors, wrong number of decimal places or completely off values?
Is every record wrong?
There are 5 columns, 2 dimensions and 3 measures. The values for the 3 measures are derived from couple of nested views. The values for dimensions are correct while the values for rest 3 columns are completely wrong.
What's the data type of those fields in setting->admin->data model? also, please post the diagnostic info.
Is this a GUI question or a native SQL question?
The type for one of the column says Cost and the other one says Share.
Here is the info
{
"browser-info": {
"language": "en-IN",
"platform": "MacIntel",
"userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/16.1 Safari/605.1.15",
"vendor": "Apple Computer, Inc."
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "11.0.17+8-post-Ubuntu-1ubuntu222.04",
"java.vendor": "Ubuntu",
"java.vendor.url": "https://ubuntu.com/",
"java.version": "11.0.17",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "11.0.17+8-post-Ubuntu-1ubuntu222.04",
"os.name": "Linux",
"os.version": "5.10.144-127.601.amzn2.aarch64",
"user.language": "en",
"user.timezone": "GMT"
},
"metabase-info": {
"databases": [
"h2",
"postgres"
],
"hosting-env": "unknown",
"application-database": "postgres",
"application-database-details": {
"database": {
"name": "PostgreSQL",
"version": "15.0 (Debian 15.0-1.pgdg110+1)"
},
"jdbc-driver": {
"name": "PostgreSQL JDBC Driver",
"version": "42.5.0"
}
},
"run-mode": "prod",
"version": {
"date": "2022-12-07",
"tag": "v0.45.1",
"branch": "release-x.45.x",
"hash": "019d31c"
},
"settings": {
"report-timezone": "US/Pacific"
}
}
}
Tried changing the semantic type to "No Semantic" and still the same issue.
Are you making GUI or SQL questions? please post the SQL that you're running or that Metabase generates to try to spot differences on them
I tried both, GUI and SQL, returns same incorrect values,
SELECT ns.location_name,
ns.department,
ns.net_sales,
COALESCE(lc.labor_cost, 0::numeric) AS labor_cost,
COALESCE(COALESCE(lc.labor_cost, 0::numeric)::double precision / NULLIF(ns.net_sales, 0::double precision),
0::double precision) AS labor_percentage
FROM (SELECT uss.location_name,
uss.department,
sum(uss.net_sales) AS net_sales
FROM stage.uvw_square_sales uss
WHERE uss."Date" = now()::date
GROUP BY uss.department, uss.location_name) ns
LEFT JOIN (SELECT lsd.location_name,
lsd.category AS department,
sum(COALESCE(lsd.labor_cost, 0::numeric)) AS labor_cost
FROM stage.uvw_labor_summary_department lsd
WHERE lsd.labor_date = now()::date
GROUP BY lsd.location_name, lsd.category) lc
ON ns.location_name = lc.location_name AND ns.department::text = lc.department::text;
With Metabase 0.45.2 I have the same problem. The correct value in the mysql is different from the one metabase is displaying when i query the database through my own. I have disacarded the cache in the field and in the database and also triggered a re-scan of the field values in both. I have synct the database schema, deleted docker container. Dropt everything from the query_cache table. But it is still displaying wrong
Same problem in GUI view.
Try this. It worked for me