Query result issue

Hi,

I have quite a strange issue with Metabase, created question via native query, the query result keeps changing when I click run with the same query. tried with DBeaver using same database and same query the query result is correct and never changing even run it multiple times.
I have attached a screen recording for Metabase and DBeaver

Thank you,

Environment :
Server
Distributor ID: Ubuntu
Description: Ubuntu 20.04.3 LTS
Release: 20.04
Codename: focal
DB server : 10.5.13-MariaDB-1:10.5.13+maria~focal mariadb.org binary distribution
DB Engine : Columnstore
Metabase version : 0.41.5

Client
Windows 11
Chrome browser Version 96.0.4664.110 (Official Build) (64-bit)

Attachment


dbschema
-- dwh.acc_dimdept definition

CREATE TABLE `acc_dimdept` (

  `DBID` varchar(10) DEFAULT NULL,

  `DeptID` varchar(10) DEFAULT NULL,

  `DeptName` varchar(30) DEFAULT NULL

) ENGINE=Columnstore DEFAULT CHARSET=latin1;

-- dwh.acc_exp_accountmap definition

CREATE TABLE `acc_exp_accountmap` (

  `DBID` varchar(10) DEFAULT NULL,

  `MainAccount` varchar(30) DEFAULT NULL,

  `CostCenter` varchar(10) DEFAULT NULL,

  `map1` varchar(50) DEFAULT NULL,

  `map2` varchar(50) DEFAULT NULL,

  `map3` varchar(50) DEFAULT NULL,

  `map4` varchar(50) DEFAULT NULL,

  `map5` varchar(50) DEFAULT NULL

) ENGINE=Columnstore DEFAULT CHARSET=latin1;

-- dwh.acc_exp_bal definition

CREATE TABLE `acc_exp_bal` (

  `DBID` varchar(10) DEFAULT NULL,

  `CompanyID` varchar(10) DEFAULT NULL,

  `BalDate` date DEFAULT NULL,

  `MainAccount` varchar(20) DEFAULT NULL,

  `CostCenter` varchar(10) DEFAULT NULL,

  `DeptID` varchar(10) DEFAULT NULL,

  `BalAmount` double DEFAULT NULL

) ENGINE=Columnstore DEFAULT CHARSET=latin1;

-- dwh.vacc_plant_expense_a source

CREATE OR REPLACE

ALGORITHM = UNDEFINED VIEW `vacc_plant_expense_a` AS

select

    `acc_exp_bal`.`DBID` AS `dbid`,

    concat(`acc_exp_bal`.`DBID`, '-', `acc_exp_bal`.`CompanyID`) AS `companyid`,

    `acc_exp_bal`.`BalDate` AS `baldate`,

    `acc_exp_bal`.`MainAccount` AS `mainaccount`,

    `acc_exp_bal`.`CostCenter` AS `costcenter`,

    `acc_exp_bal`.`DeptID` AS `deptid`,

    `acc_exp_bal`.`BalAmount` AS `balamount`,

    `acc_exp_accountmap`.`map1` AS `map1`,

    `acc_exp_accountmap`.`map3` AS `map3`,

    `acc_exp_accountmap`.`map4` AS `map4`,

    `acc_exp_accountmap`.`map5` AS `map5`,

    `acc_dimdept`.`DeptName` AS `deptname`

from

    ((`acc_exp_bal`

join `acc_exp_accountmap` on

    (`acc_exp_accountmap`.`DBID` = `acc_exp_bal`.`DBID`

        and `acc_exp_accountmap`.`MainAccount` = `acc_exp_bal`.`MainAccount`))

left join `acc_dimdept` on

    (`acc_dimdept`.`DBID` = `acc_exp_bal`.`DBID`

        and `acc_dimdept`.`DeptID` = `acc_exp_bal`.`DeptID`))

where

    `acc_exp_accountmap`.`CostCenter` is null

    and `acc_exp_bal`.`CostCenter` is null

union

select

    `acc_exp_bal`.`DBID` AS `dbid`,

    concat(`acc_exp_bal`.`DBID`, '-', `acc_exp_bal`.`CompanyID`) AS `companyid`,

    `acc_exp_bal`.`BalDate` AS `baldate`,

    `acc_exp_bal`.`MainAccount` AS `mainaccount`,

    `acc_exp_bal`.`CostCenter` AS `costcenter`,

    `acc_exp_bal`.`DeptID` AS `deptid`,

    `acc_exp_bal`.`BalAmount` AS `balamount`,

    `acc_exp_accountmap`.`map1` AS `map1`,

    `acc_exp_accountmap`.`map3` AS `map3`,

    `acc_exp_accountmap`.`map4` AS `map4`,

    `acc_exp_accountmap`.`map5` AS `map5`,

    `acc_dimdept`.`DeptName` AS `deptname`

from

    ((`acc_exp_bal`

join `acc_exp_accountmap` on

    (`acc_exp_accountmap`.`DBID` = `acc_exp_bal`.`DBID`

        and `acc_exp_accountmap`.`MainAccount` = `acc_exp_bal`.`MainAccount`

        and `acc_exp_accountmap`.`CostCenter` = `acc_exp_bal`.`CostCenter`))

left join `acc_dimdept` on

    (`acc_dimdept`.`DBID` = `acc_exp_bal`.`DBID`

        and `acc_dimdept`.`DeptID` = `acc_exp_bal`.`DeptID`));

Hi @bambang
Check the query log on MariaDB. And make sure you don't have some query cache.
The only difference between Metabase and DBeaver is that Metabase might open a new connection, when running the query - otherwise they are similar.
But it looks like you might have a session variable somewhere, though cannot see anything in your query, but if Metabase creates a new connection, then the session variable is not defined initially.

Hi @flamber,

I have disabled the cache in MariaDB, and logged the query as below, but the problem still there, if I refresh in Metabase the query result still changes an different from dbeaver

Thanks,

MariaDB cache status

MariaDB [(none)]> SHOW VARIABLES LIKE 'query_cache_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_size | 0     |
+------------------+-------+
1 row in set (0.002 sec)

Log snippet from debug.log

DBeaver

query run #1
Jan  5 07:50:26 mdw01 ExeMgr[742344]: 26.607854 |2147487421|0|0| D 16 CAL0041: Start SQL statement: select objectid,columnname from syscolumn where schema='dwh' and tablename='acc_exp_bal' --columnRIDs/FE; ||
Jan  5 07:50:26 mdw01 ExeMgr[742344]: 26.621506 |2147487421|0|0| D 16 CAL0042: End SQL statement
Jan  5 07:50:26 mdw01 ExeMgr[742344]: 26.622354 |2147487421|0|0| D 16 CAL0041: Start SQL statement: select objectid,columnname from syscolumn where schema='dwh' and tablename='acc_exp_accountmap' --columnRIDs/FE; ||
Jan  5 07:50:26 mdw01 ExeMgr[742344]: 26.631940 |2147487421|0|0| D 16 CAL0042: End SQL statement
Jan  5 07:50:26 mdw01 ExeMgr[742344]: 26.632836 |2147487421|0|0| D 16 CAL0041: Start SQL statement: select objectid,columnname from syscolumn where schema='dwh' and tablename='acc_dimdept' --columnRIDs/FE; ||
Jan  5 07:50:26 mdw01 ExeMgr[742344]: 26.643862 |2147487421|0|0| D 16 CAL0042: End SQL statement
Jan  5 07:50:26 mdw01 ExeMgr[742344]: 26.683934 |3773|0|0| D 16 CAL0041: Start SQL statement: select month(baldate), sum(balamount)#015#012from vacc_plant_expense_a#015#012where map4 = 'Plant Expenses - Exclude COGS and Others' and baldate BETWEEN '2021-01-01' and '2021-12-31'#015#012group by 1#015#012order by 1#012LIMIT 0, 200; |dwh|
Jan  5 07:50:27 mdw01 ExeMgr[742344]: 27.259267 |3773|0|0| D 16 CAL0042: End SQL statement

query run #2
Jan  5 07:51:05 mdw01 ExeMgr[742344]: 05.356573 |3773|0|0| D 16 CAL0041: Start SQL statement: select month(baldate), sum(balamount)#015#012from vacc_plant_expense_a#015#012where map4 = 'Plant Expenses - Exclude COGS and Others' and baldate BETWEEN '2021-01-01' and '2021-12-31'#015#012group by 1#015#012order by 1#012LIMIT 0, 200; |dwh|
Jan  5 07:51:05 mdw01 ExeMgr[742344]: 05.760296 |3773|0|0| D 16 CAL0042: End SQL statement

query run #3
Jan  5 07:51:52 mdw01 ExeMgr[742344]: 52.768662 |3773|0|0| D 16 CAL0041: Start SQL statement: select month(baldate), sum(balamount)#015#012from vacc_plant_expense_a#015#012where map4 = 'Plant Expenses - Exclude COGS and Others' and baldate BETWEEN '2021-01-01' and '2021-12-31'#015#012group by 1#015#012order by 1#012LIMIT 0, 200; |dwh|
Jan  5 07:51:53 mdw01 ExeMgr[742344]: 53.122274 |3773|0|0| D 16 CAL0042: End SQL statement

Metabase

 query run #1
Jan  5 07:53:19 mdw01 ExeMgr[742344]: 19.286229 |2147487415|0|0| D 16 CAL0041: Start SQL statement: select objectid,columnname from syscolumn where schema='dwh' and tablename='acc_exp_bal' --columnRIDs/FE; ||
Jan  5 07:53:19 mdw01 ExeMgr[742344]: 19.298448 |2147487415|0|0| D 16 CAL0042: End SQL statement
Jan  5 07:53:19 mdw01 ExeMgr[742344]: 19.299370 |2147487415|0|0| D 16 CAL0041: Start SQL statement: select objectid,columnname from syscolumn where schema='dwh' and tablename='acc_exp_accountmap' --columnRIDs/FE; ||
Jan  5 07:53:19 mdw01 ExeMgr[742344]: 19.307878 |2147487415|0|0| D 16 CAL0042: End SQL statement
Jan  5 07:53:19 mdw01 ExeMgr[742344]: 19.308751 |2147487415|0|0| D 16 CAL0041: Start SQL statement: select objectid,columnname from syscolumn where schema='dwh' and tablename='acc_dimdept' --columnRIDs/FE; ||
Jan  5 07:53:19 mdw01 ExeMgr[742344]: 19.319150 |2147487415|0|0| D 16 CAL0042: End SQL statement
Jan  5 07:53:19 mdw01 ExeMgr[742344]: 19.338328 |3767|0|0| D 16 CAL0041: Start SQL statement: -- Metabase:: userID: 1 queryType: native queryHash: 152c8237e1361e16611a1b57ba740b25f97fd24676c0397b18d75e7c52df6eea#012select month(baldate), sum(balamount)#015#012from vacc_plant_expense_a#015#012where map4 = 'Plant Expenses - Exclude COGS and Others' and baldate BETWEEN '2021-01-01' and '2021-12-31'#015#012group by 1#015#012order by 1; |dwh|
Jan  5 07:53:19 mdw01 ExeMgr[742344]: 19.512329 |3767|0|0| D 16 CAL0042: End SQL statement
Jan  5 07:53:19 mdw01 dmlpackageproc[742358]: 19.522248 |3767|0|0| D 21 CAL0001: Start SQL statement:  ROLLBACK
Jan  5 07:53:19 mdw01 dmlpackageproc[742358]: 19.522860 |3767|0|0| D 21 CAL0001: End SQL statement

query run #2
Jan  5 07:54:41 mdw01 ExeMgr[742344]: 41.559420 |3767|0|0| D 16 CAL0041: Start SQL statement: -- Metabase:: userID: 1 queryType: native queryHash: 152c8237e1361e16611a1b57ba740b25f97fd24676c0397b18d75e7c52df6eea#012select month(baldate), sum(balamount)#015#012from vacc_plant_expense_a#015#012where map4 = 'Plant Expenses - Exclude COGS and Others' and baldate BETWEEN '2021-01-01' and '2021-12-31'#015#012group by 1#015#012order by 1; |dwh|
Jan  5 07:54:41 mdw01 ExeMgr[742344]: 41.737227 |3767|0|0| D 16 CAL0042: End SQL statement
Jan  5 07:54:41 mdw01 dmlpackageproc[742358]: 41.748420 |3767|0|0| D 21 CAL0001: Start SQL statement:  ROLLBACK
Jan  5 07:54:41 mdw01 dmlpackageproc[742358]: 41.749062 |3767|0|0| D 21 CAL0001: End SQL statement

query run #3
Jan  5 07:55:02 mdw01 ExeMgr[742344]: 02.056121 |3767|0|0| D 16 CAL0041: Start SQL statement: -- Metabase:: userID: 1 queryType: native queryHash: 152c8237e1361e16611a1b57ba740b25f97fd24676c0397b18d75e7c52df6eea#012select month(baldate), sum(balamount)#015#012from vacc_plant_expense_a#015#012where map4 = 'Plant Expenses - Exclude COGS and Others' and baldate BETWEEN '2021-01-01' and '2021-12-31'#015#012group by 1#015#012order by 1; |dwh|
Jan  5 07:55:02 mdw01 ExeMgr[742344]: 02.235314 |3767|0|0| D 16 CAL0042: End SQL statement
Jan  5 07:55:02 mdw01 dmlpackageproc[742358]: 02.250901 |3767|0|0| D 21 CAL0001: Start SQL statement:  ROLLBACK
Jan  5 07:55:02 mdw01 dmlpackageproc[742358]: 02.251604 |3767|0|0| D 21 CAL0001: End SQL statement

query run #4
Jan  5 07:55:18 mdw01 ExeMgr[742344]: 18.457808 |3767|0|0| D 16 CAL0041: Start SQL statement: -- Metabase:: userID: 1 queryType: native queryHash: 152c8237e1361e16611a1b57ba740b25f97fd24676c0397b18d75e7c52df6eea#012select month(baldate), sum(balamount)#015#012from vacc_plant_expense_a#015#012where map4 = 'Plant Expenses - Exclude COGS and Others' and baldate BETWEEN '2021-01-01' and '2021-12-31'#015#012group by 1#015#012order by 1; |dwh|
Jan  5 07:55:18 mdw01 ExeMgr[742344]: 18.654946 |3767|0|0| D 16 CAL0042: End SQL statement
Jan  5 07:55:18 mdw01 dmlpackageproc[742358]: 18.667620 |3767|0|0| D 21 CAL0001: Start SQL statement:  ROLLBACK
Jan  5 07:55:18 mdw01 dmlpackageproc[742358]: 18.667702 |3767|0|0| D 21 CAL0001: End SQL statement

query run #5
Jan  5 07:55:20 mdw01 ExeMgr[742344]: 20.372037 |3767|0|0| D 16 CAL0041: Start SQL statement: -- Metabase:: userID: 1 queryType: native queryHash: 152c8237e1361e16611a1b57ba740b25f97fd24676c0397b18d75e7c52df6eea#012select month(baldate), sum(balamount)#015#012from vacc_plant_expense_a#015#012where map4 = 'Plant Expenses - Exclude COGS and Others' and baldate BETWEEN '2021-01-01' and '2021-12-31'#015#012group by 1#015#012order by 1; |dwh|
Jan  5 07:55:20 mdw01 ExeMgr[742344]: 20.547719 |3767|0|0| D 16 CAL0042: End SQL statement
Jan  5 07:55:20 mdw01 dmlpackageproc[742358]: 20.561039 |3767|0|0| D 21 CAL0001: Start SQL statement:  ROLLBACK
Jan  5 07:55:20 mdw01 dmlpackageproc[742358]: 20.561115 |3767|0|0| D 21 CAL0001: End SQL statement

@bambang Which driver version are you using in DBeaver? Metabase currently uses 2.6.2

You have disabled caching in Metabase? Admin > Settings > Caching

Can you check the actual returned results in the browser developer Network-tab, just to rule out if there's some strange frontend issue?

@flamber,

Dbeaver mariadb driver version is 2.4.3
Caching in metabase in disabled status
I don't know how to export returned data in networking tab, but I have done a screenshot as below, hope that will enough.

if you need our data for simulation, I can export it and can use mariadb-columnstore docker for simulation

Thank you,

network-tab

metabase cache

DBeaver driver version

@bambang

  1. Upgrade DBeaver's driver to 2.6.2, so we can make sure it's not a driver issue.
  2. Open your browser developer Network-tab, then refresh the query, which will create a request in Network-tab, which you can click on to see the Response-data. (and close the console, so you have more space for the network requests)

@flamber

I have install new driver on DBeaver using 2.6.2 and also tried the latest version 2.7.4 and the result is the same like in Metabase, now the query result in DBeaver also change on every rerun, so its likely driver issue .

for temporary solution is it possible to downgrade jdbc version in metabase?

Thank you,

@bambang That's good, since we've now narrowed down the problem.

I would recommend that you try every version between 2.4.3 and 2.6.2, so you know exactly when it started to behave differently, and then report a detailed issue to MariaDB https://jira.mariadb.org/projects/CONJ/issues/
They will likely take it very serious mark it as Critical if you provide easy way to reproduce.
I'm guessing it may be some specific configuration you have on your database, since otherwise this problem would have been reported by a lot of other people.

Please comment here on which version started becoming problematic and a link to the issue you create.

You would have to build your own version of Metabase with an older driver, which you define here:
https://github.com/metabase/metabase/blob/master/deps.edn#L120

@flamber

tested version
2.5.4, 2.5.0, 2.4.4 and 2.4.3
all driver above downloaded from https://mvnrepository.com/artifact/org.mariadb.jdbc/mariadb-java-client, couldn't find driver older than 2.6 from mariadb website
testing result show same error as in previous post

the strange thing is the same version driver with DBeaver (2.4.3) downloaded from mvnrepository, show the same error as other drivers, checked also the driver properties, all have the same setting

really confusing :sweat:

@bambang I'm not sure where you downloaded the original 2.4.3 from, since DBeaver uses the Maven repo by default. Make sure you validate the checksum.
Also, I would recommend restarting DBeaver after changing driver, so you are sure that it doesn't keep some of the old driver somewhere in memory.
And make sure you click the "Test Connection" in DBeaver, so you can see exactly which driver is being used.

@flamber

downloaded mariadb jdbc driver from this URL
https://repo1.maven.org/maven2/org/mariadb/jdbc/mariadb-java-client/2.4.3/mariadb-java-client-2.4.3.jar

and did a checksum (MD5) comparison between driver from dbeaver and downloaded from maven, both have same md5 hash.

also tried to upgrade dbeaver jdbc driver from 2.4.3 to 2.6.2 ( same version used in metabase) and query result in dbeaver showing correct result

I have checked version in test connection and restart in every new driver installed

@bambang I don't understand. You said earlier that different driver versions also showed incorrect results in DBeaver.

There's thousands of installations of Metabase that uses MySQL/MariaDB, so if this was a common problem, then we would know it.

I have never seen this problem before, so I cannot tell you where the problem is, but I'm guessing that you have some variable someone, which isn't reset, or there's a cache somewhere.

@flamber yes, in my previous test, i have done test to compare different version of jdbc driver downloaded from maven repo ( same as dbeaver jdbc source ), and show incorrect result.

in the latest test, tested the same version jdbc (2.4.3), in the same DBeaver installation, one driver is default from DBeaver and the other one is custom add.

in DBeaver, we can create a custom driver within its 'driver manager' (screenshot below), number 1 is default from dbeaver and the number 2 is custom add, both use 2.4.3 driver, but show different result

I'll try to post this issue in DBeaver forum, since this issue is replicable in DBeaver and mariadb columnstore docker, Thank you for your help in this case :+1:

@bambang The icon seems to suggest that you are using a MySQL driver for "2", while a MariaDB driver for "1". The two projects were almost identical several years ago, but they have now diverted a bit from each other.
But since the problem seems to be with the driver and not the client (DBeaver or Metabase), then you should probably open an issue in the driver repo.

@flamber

update for this issue, as this issue are able to replicate in DBeaver, then I post it on DBeaver forum

@bambang But if you can replicate the problem in DBeaver, then the problem is the driver, not DBeaver or Metabase. Or some weird setting on your database.