Pivot table totals do not add up correctly?

We've been using Metabase for several months now and we are pretty happy with it so far.

Now I think I found a bug:

Lately I started trying out the pivot table feature and found it quite useful until I realized the Totals were not adding up correctly, neither on rows nor columns. This was driving me crazy, so I searched here and on github and apparently nobody has ran into this problem, so maybe I'm doing something wrong:
I'm pivoting a very simple table with a couple of dimensions and a measure:

It's very easy to see that the totals don't match. But why? If I download the results in a csv and pivot the table in LibreOffice, the totals do match:
image

I found this issue on github, which seems to be somewhat related, but isn't the same, obviously: https://github.com/metabase/metabase/issues/15211

Any ideas of what might be happening? I would appreciate it very much.

Cheers!

Hi @ralcm
Interesting. Since the totals are nearly correct (and on 2021-06-06 they match), my brain is instantly thinking about timezones.

Post "Diagnostic Info" from Admin > Troubleshooting.

Pivot Table queries are quite difficult to debug, since it's a big collection of queries that generate the final result. It would probably require that you looked at the database query log to see exactly what's going on.

Are you able to post the plain (non-pivoted) table of the result, so I can try to see if I can reproduce?

Hi @flamber!

Here's the Diagnostic info:

{
  "browser-info": {
"language": "en-US",
"platform": "Linux x86_64",
"userAgent": "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.77 Safari/537.36",
"vendor": "Google Inc."
  },
  "system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "11.0.11+9",
"java.vendor": "AdoptOpenJDK",
"java.vendor.url": "https://adoptopenjdk.net/",
"java.version": "11.0.11",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "11.0.11+9",
"os.name": "Linux",
"os.version": "4.14.225-121.362.amzn1.x86_64",
"user.language": "en",
"user.timezone": "GMT"
  },
  "metabase-info": {
"databases": [
  "redshift"
],
"hosting-env": "elastic-beanstalk",
"application-database": "postgres",
"application-database-details": {
  "database": {
    "name": "PostgreSQL",
    "version": "11.10"
  },
  "jdbc-driver": {
    "name": "PostgreSQL JDBC Driver",
    "version": "42.2.18"
  }
},
"run-mode": "prod",
"version": {
  "date": "2021-04-27",
  "tag": "v0.39.1",
  "branch": "release-x.39.x",
  "hash": "6beba48"
},
"settings": {
  "report-timezone": null
}
  }
} 

And here are the table values:

type created_at Distinct values of id
d 2021-02-20 1
d 2021-04-26 2
d 2021-04-27 1
d 2021-05-03 1
d 2021-05-04 1
d 2021-05-07 1
d 2021-05-12 1
d 2021-05-14 3
d 2021-05-16 1
d 2021-05-17 1
d 2021-05-18 3
d 2021-05-19 2
d 2021-05-20 3
d 2021-05-21 11
d 2021-05-22 20
d 2021-05-23 3
d 2021-05-24 28
d 2021-05-25 81
d 2021-05-26 98
d 2021-05-27 74
d 2021-05-28 85
d 2021-05-29 17
d 2021-05-30 15
d 2021-05-31 79
d 2021-06-01 79
d 2021-06-02 73
d 2021-06-03 53
d 2021-06-04 34
d 2021-06-05 18
d 2021-06-06 3
d 2021-06-07 25
t 2021-05-24 1
t 2021-05-25 1
t 2021-05-26 4
t 2021-05-27 7
t 2021-05-28 14
t 2021-05-29 1
t 2021-05-31 13
t 2021-06-01 5
t 2021-06-02 5
t 2021-06-03 5
t 2021-06-04 4
t 2021-06-05 2
t 2021-06-07 9
u 2021-05-24 2
u 2021-05-25 28
u 2021-05-26 34
u 2021-05-27 19
u 2021-05-28 21
u 2021-05-29 6
u 2021-05-30 5
u 2021-05-31 16
u 2021-06-01 13
u 2021-06-02 18
u 2021-06-03 16
u 2021-06-04 10
u 2021-06-05 11
u 2021-06-06 4
u 2021-06-07 7

Thanks!

@ralcm Okay, you need to check your query log, so we can understand what is happening.
I would need the raw database table (including schema) to understand what's going on.
I'm still quite sure it's a timezone problem. Could it be that your "created_at" column is without timezone?