cube-js / cube-js/cube

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

Open
#7,984 4 comments 0 reactions 1 assignee Claimed by @igorlukanin View on GitHub
Dominant language
Rust
Stars
20.8k
Forks
2.1k
Avg merge
1d 2h
Merged PRs (30d)
181

Description

**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**
![cube-dev-problem](https://github.com/cube-js/cube/assets/6135741/4266974d-f8b9-45f8-a278-6da8836d4ad2)

**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]

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.