The greater than numeric comparison does not work properly against mysql/starrocks

[chrisfw]

Bug Description Using the mysql/starrocks dialect, a simple select with a numeric greater than comparison returns incorrect results. I tried using both a simple select with a where clause and wrapping it in an outer select and neither worked correctly.

select * from (
select balance_amount from cibalancedetailhistory) a
where a.balance_amount > 100;

balance_amount is a decimal64 (11,2) in the underlying mysql/Starrocks database

To Reproduce Steps to reproduce the behavior:

1. Create a table in starrocks with the decimal datatype specified above
2. Populate the table BALANCEAMOUNT column with a range of decimal amounts
3. Run a select query as provided above

Expected behavior Only values greater than the specified where clause comparison value should be returned.

Screenshots

Minimally reproducible Cube Schema In case your bug report is data modelling related please put your minimally reproducible Cube Schema here. You can use selects without tables in order to achieve that as follows.

cube(`cibalancedetailhistory`, {
  sql_table: `db.cibalancedetailhistory`,

  data_source: `default`,

  joins: {

  },

  dimensions: {
    balance_detail_history_id: {
      sql: `BALANCEDETAILHISTORYID`,
      type: `number`
    },
    history_date: {
      sql: `HISTORYDATE`,
      type: `time`
    },
    balance_amount: {
      sql: `BALANCEAMOUNT`,
      type: `number`
    },
  },
  measures: {
    count: {
      sql: `BALANCEDETAILHISTORYID`,
      type: `count`,
    }
  },

  pre_aggregations: {
    // Pre-aggregation definitions go here.
    // Learn more in the documentation: https://cube.dev/docs/caching/pre-aggregations/getting-started
  }
});

Version: [e.g. 0.35.0]

[paveltiunov]

@chrisfw are you using SQL API to query Cube? It'd be also helpful to see SQL executed in starrocks. You can find it in logs.

[chrisfw]

Hi @paveltiunov , yes I am using the SQL API. The query + results screen capture I provided is from the DBeaver SQL connection to cube. I will get the Starrocks executed sql for you when I resume work on Tuesday. Regards, Chris

[igorlukanin]

@chrisfw By any chance, are you able to provide the "Starrocks executed sql"?

[chrisfw]

@igorlukanin , the sql is in the bug description above:

select * from (
select balance_amount from cibalancedetailhistory) a
where a.balance_amount > 100;