search

metabase / metabase

The simplest, fastest way to get business intelligence and analytics to everyone in your company :yum:
https://metabase.com
Other
38.44k stars 5.09k forks source link

Metabase custom column with colA * colB returns incorrect results #31025

Closed SubhasmitaSw closed 1 year ago

SubhasmitaSw commented 1 year ago

Describe the bug

Hello Metabase Team,

we've been setting up metabase for our team for some time now and it's a really integral part of the BI stack. Our scenario includes a discrepancy which I believe is a bug on your side (assuming), a simple multiplication of two columns to get a custom column returns incorrect values than expected.

Screenshot 2023-05-25 at 12 18 09 PM

In the above image, the License Cost column is a custom column where License Cost = [Hourly cost NEV] * [count of rows] which should match the calculations exactly as below but there's a total difference of 0.42. 

7.66 * 19 = 145.54
9.31 * 181 = 1685.11
1.96 * 13 = 25.48
1.79 * 4 = 7.16 
1.74 * 2 = 3.48

To Reproduce

  1. Go to New Question
  2. Insert sample data
  3. Create a custom column with value as multiplication of two other columns (float values)
  4. See error

Expected behavior

The calculations should be exactly.

Logs

[9a495989-7487-46b2-ae71-da7c190f4d62] 2023-05-25T15:58:19+05:30 DEBUG metabase.server.middleware.log POST /api/dataset 202 [ASYNC: completed] 442.1 ms (4 DB calls) App DB connections: 0/15 Jetty threads: 3/50 (3 idle, 0 queued) (112 total active threads) Queries in flight: 0 (0 queued); starburst DB 5 connections: 0/1 (0 threads blocked) [9a495989-7487-46b2-ae71-da7c190f4d62] 2023-05-25T16:06:00+05:30 INFO metabase.task.sync-databases Starting sync task for Database 41. [9a495989-7487-46b2-ae71-da7c190f4d62] 2023-05-25T16:06:00+05:30 INFO metabase.sync.util STARTING: Sync metadata for starburst Database 41 'Trino_ClickhouseDB' [9a495989-7487-46b2-ae71-da7c190f4d62] 2023-05-25T16:06:00+05:30 INFO metabase.sync.util STARTING: step 'sync-timezone' for starburst Database 41 'Trino_ClickhouseDB' [9a495989-7487-46b2-ae71-da7c190f4d62] 2023-05-25T16:06:00+05:30 INFO metabase.sync.util FINISHED: step 'sync-timezone' for starburst Database 41 'Trino_ClickhouseDB' (54.1 ms) [9a495989-7487-46b2-ae71-da7c190f4d62] 2023-05-25T16:06:00+05:30 INFO metabase.sync.util STARTING: step 'sync-tables' for starburst Database 41 'Trino_ClickhouseDB' [9a495989-7487-46b2-ae71-da7c190f4d62] 2023-05-25T16:06:00+05:30 INFO metabase.sync.sync-metadata.tables Marking tables as inactive: (Table 'default.invoice_sizes') [9a495989-7487-46b2-ae71-da7c190f4d62] 2023-05-25T16:06:00+05:30 INFO metabase.sync.sync-metadata.tables Updating description for tables: (Table 'default.ch_sizes' Table 'default.hardware_sizes') [9a495989-7487-46b2-ae71-da7c190f4d62] 2023-05-25T16:06:00+05:30 INFO metabase.sync.util FINISHED: step 'sync-tables' for starburst Database 41 'Trino_ClickhouseDB' (15.0 ms) [9a495989-7487-46b2-ae71-da7c190f4d62] 2023-05-25T16:06:00+05:30 INFO metabase.sync.util STARTING: step 'sync-fields' for starburst Database 41 'Trino_ClickhouseDB' [9a495989-7487-46b2-ae71-da7c190f4d62] 2023-05-25T16:06:00+05:30 INFO metabase.sync.util FINISHED: step 'sync-fields' for starburst Database 41 'Trino_ClickhouseDB' (347.3 ms) [9a495989-7487-46b2-ae71-da7c190f4d62] 2023-05-25T16:06:00+05:30 INFO metabase.sync.util STARTING: step 'sync-fks' for starburst Database 41 'Trino_ClickhouseDB' [9a495989-7487-46b2-ae71-da7c190f4d62] 2023-05-25T16:06:00+05:30 INFO metabase.sync.util FINISHED: step 'sync-fks' for starburst Database 41 'Trino_ClickhouseDB' (875.2 µs) [9a495989-7487-46b2-ae71-da7c190f4d62] 2023-05-25T16:06:00+05:30 INFO metabase.sync.util FINISHED: step 'sync-metabase-metadata' for starburst Database 41 'Trino_ClickhouseDB' (147.8 µs) [9a495989-7487-46b2-ae71-da7c190f4d62] 2023-05-25T16:06:00+05:30 INFO metabase.sync.util STARTING: step 'sync-metabase-metadata' for starburst Database 41 'Trino_ClickhouseDB' [9a495989-7487-46b2-ae71-da7c190f4d62] 2023-05-25T16:06:00+05:30 INFO metabase.sync.util FINISHED: Sync metadata for starburst Database 41 'Trino_ClickhouseDB' (427.2 ms) [9a495989-7487-46b2-ae71-da7c190f4d62] 2023-05-25T16:06:00+05:30 INFO metabase.sync.util STARTING: Analyze data for starburst Database 41 'Trino_ClickhouseDB' [9a495989-7487-46b2-ae71-da7c190f4d62] 2023-05-25T16:06:00+05:30 INFO metabase.sync.analyze fingerprint-fields Analyzed [*···········································] 😞 14% Table 516 'default.hardware_sizes' [9a495989-7487-46b2-ae71-da7c190f4d62] 2023-05-25T16:06:00+05:30 INFO metabase.sync.util STARTING: step 'fingerprint-fields' for starburst Database 41 'Trino_ClickhouseDB' [9a495989-7487-46b2-ae71-da7c190f4d62] 2023-05-25T16:06:00+05:30 INFO metabase.sync.analyze fingerprint-fields Analyzed [**····································] 😒 29% Table 531 'default.ch_sizes' [9a495989-7487-46b2-ae71-da7c190f4d62] 2023-05-25T16:06:00+05:30 INFO metabase.sync.util FINISHED: step 'fingerprint-fields' for starburst Database 41 'Trino_ClickhouseDB' (2.9 ms) [9a495989-7487-46b2-ae71-da7c190f4d62] 2023-05-25T16:06:00+05:30 INFO metabase.sync.util STARTING: step 'classify-fields' for starburst Database 41 'Trino_ClickhouseDB' [9a495989-7487-46b2-ae71-da7c190f4d62] 2023-05-25T16:06:00+05:30 INFO metabase.sync.analyze classify-fields Analyzed [****······················] 😌 57% Table 531 'default.ch_sizes' [9a495989-7487-46b2-ae71-da7c190f4d62] 2023-05-25T16:06:00+05:30 INFO metabase.sync.analyze classify-fields Analyzed [***·····························] 😐 43% Table 516 'default.hardware_sizes' [9a495989-7487-46b2-ae71-da7c190f4d62] 2023-05-25T16:06:00+05:30 INFO metabase.sync.util FINISHED: step 'classify-fields' for starburst Database 41 'Trino_ClickhouseDB' (967.2 µs) [9a495989-7487-46b2-ae71-da7c190f4d62] 2023-05-25T16:06:00+05:30 INFO metabase.sync.util STARTING: step 'classify-tables' for starburst Database 41 'Trino_ClickhouseDB' [9a495989-7487-46b2-ae71-da7c190f4d62] 2023-05-25T16:06:00+05:30 INFO metabase.sync.analyze classify-tables Analyzed [***···············] 😋 71% Table 516 'default.hardware_sizes' [9a495989-7487-46b2-ae71-da7c190f4d62] 2023-05-25T16:06:00+05:30 INFO metabase.sync.analyze classify-tables Analyzed [**········] 😊 86% Table 531 'default.ch_sizes' [9a495989-7487-46b2-ae71-da7c190f4d62] 2023-05-25T16:06:00+05:30 INFO metabase.sync.util FINISHED: step 'classify-tables' for starburst Database 41 'Trino_ClickhouseDB' (1.7 ms) [9a495989-7487-46b2-ae71-da7c190f4d62] 2023-05-25T16:06:00+05:30 INFO metabase.sync.util FINISHED: Analyze data for starburst Database 41 'Trino_ClickhouseDB' (10.7 ms) [9a495989-7487-46b2-ae71-da7c190f4d62] 2023-05-25T16:16:56+05:30 DEBUG metabase.server.middleware.log GET /api/session/properties 200 21.9 ms (3 DB calls) App DB connections: 0/15 Jetty threads: 5/50 (2 idle, 0 queued) (108 total active threads) Queries in flight: 0 (0 queued) [9a495989-7487-46b2-ae71-da7c190f4d62] 2023-05-25T16:16:56+05:30 DEBUG metabase.server.middleware.log GET /api/setting 200 26.4 ms (2 DB calls) App DB connections: 0/15 Jetty threads: 5/50 (2 idle, 0 queued) (110 total active threads) Queries in flight: 0 (0 queued) [9a495989-7487-46b2-ae71-da7c190f4d62] 2023-05-25T16:16:57+05:30 DEBUG metabase.server.middleware.log GET /api/setup/admin_checklist 200 7.7 ms (11 DB calls) App DB connections: 0/15 Jetty threads: 4/50 (3 idle, 0 queued) (112 total active threads) Queries in flight: 0 (0 queued) [9a495989-7487-46b2-ae71-da7c190f4d62] 2023-05-25T16:16:57+05:30 DEBUG metabase.server.middleware.log GET /api/util/bug_report_details 200 13.5 ms (1 DB calls) App DB connections: 0/15 Jetty threads: 4/50 (3 idle, 0 queued) (112 total active threads) Queries in flight: 0 (0 queued)

Information about your Metabase installation

{
  "browser-info": {
    "language": "en-US",
    "platform": "MacIntel",
    "userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/113.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.18+10",
    "java.vendor": "Eclipse Adoptium",
    "java.vendor.url": "https://adoptium.net/",
    "java.version": "11.0.18",
    "java.vm.name": "OpenJDK 64-Bit Server VM",
    "java.vm.version": "11.0.18+10",
    "os.name": "Linux",
    "os.version": "5.15.0-71-generic",
    "user.language": "en",
    "user.timezone": "GMT"
  },
  "metabase-info": {
    "databases": [
      "starburst",
      "clickhouse"
    ],
    "hosting-env": "unknown",
    "application-database": "h2",
    "application-database-details": {
      "database": {
        "name": "H2",
        "version": "1.4.197 (2018-03-18)"
      },
      "jdbc-driver": {
        "name": "H2 JDBC Driver",
        "version": "1.4.197 (2018-03-18)"
      }
    },
    "run-mode": "prod",
    "version": {
      "date": "2023-02-19",
      "tag": "v0.45.3",
      "branch": "release-x.45.x",
      "hash": "070f57b"
    },
    "settings": {
      "report-timezone": null
    }
  }
}

Severity

It's blocking the invoice analytics and audit that is overdue by 1 month.

Additional context

No response

paoliniluis commented 1 year ago

Hi! is this calculation being done on your Trino or in Clickhouse?

SubhasmitaSw commented 1 year ago

hey, this calculation is being done on Metabase, the table (data) is stored in clickhouse, connected over via Trino to Metabase.

paoliniluis commented 1 year ago

Please post the sql being generated

SubhasmitaSw commented 1 year ago

Entire SQL of the workout 

SELECT date_trunc('month', "source"."charged_from") AS "charged_from", sum("source"."License Cost") AS "sum"
FROM (SELECT "source"."charged_from" AS "charged_from", "source"."Hourly cost NEV" AS "Hourly cost NEV", "source"."count" AS "count", ("source"."Hourly cost NEV" * "source"."count") AS "License Cost" FROM (SELECT date_trunc('month', "source"."charged_from") AS "charged_from", "source"."Hourly cost NEV" AS "Hourly cost NEV", count(*) AS "count" FROM (SELECT "prod_civo_dotcom"."invoice_items"."id" AS "id", "prod_civo_dotcom"."invoice_items"."invoice_id" AS "invoice_id", "prod_civo_dotcom"."invoice_items"."code" AS "code", "prod_civo_dotcom"."invoice_items"."label" AS "label", "prod_civo_dotcom"."invoice_items"."charged_from" AS "charged_from", "prod_civo_dotcom"."invoice_items"."charged_to" AS "charged_to", "prod_civo_dotcom"."invoice_items"."hours" AS "hours", "prod_civo_dotcom"."invoice_items"."cost" AS "cost", "prod_civo_dotcom"."invoice_items"."created_at" AS "created_at", "prod_civo_dotcom"."invoice_items"."updated_at" AS "updated_at", "prod_civo_dotcom"."invoice_items"."product_id" AS "product_id", "prod_civo_dotcom"."invoice_items"."region" AS "region", "prod_civo_dotcom"."invoice_items"."charge_id" AS "charge_id", ("prod_civo_dotcom"."invoice_items"."hours" * 0.022321) AS "Hourly cost NEV" FROM "prod_civo_dotcom"."invoice_items") "source"
WHERE ((lower("source"."code") like '%node%')
   AND ("source"."region" = 'PHX1'
    OR "source"."region" = 'true, :kubernetes=>true} country:uk out_of_capacity:true>') AND "source"."invoice_id" = 1672049)
GROUP BY date_trunc('month', "source"."charged_from"), "source"."Hourly cost NEV"
ORDER BY date_trunc('month', "source"."charged_from") ASC, "source"."Hourly cost NEV" ASC) "source") "source" GROUP BY date_trunc('month', "source"."charged_from") ORDER BY date_trunc('month', "source"."charged_from") ASC
paoliniluis commented 1 year ago

Is it possible you’re using a question based on a sql question?

SubhasmitaSw commented 1 year ago

No custom sql questions are used here. Just a table from a database and some basic metabase functions (custom column, summarize, filter) applied to it.

ranquild commented 1 year ago

@SubhasmitaSw by default we only show 2 decimal places in numbers. Based on the image above it's very like to be the source of confusion. Could you double check whether there are additional decimal places in numbers that you multiply?

ranquild commented 1 year ago

@SubhasmitaSw please re-open if it's not related to the above. Thanks!