cube-js / cube-js/cube

Measure with `string` type, ends up with SUM aggregation in the generated SQL to pre-aggregations

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

Description

**To Reproduce**
This is my cube definition, but you can try this with any string/text based columns:
```
cube(`LE_LegalEntitiesMetadata`, {
sql: `select * from data_export.legal_entities_metadata`,

public: false,

preAggregations: {
rollup: {
dimensions: [
CUBE.legal_entity_id,
],
measures: [
CUBE.display_names
],
indexes: {
idx: {
columns: [CUBE.legal_entity_id]
}
},
refresh_key: {
every: `1 hour`,
}
}
},

measures: {
display_names: {
sql: `STRING_AGG(${CUBE.display_name}::TEXT, ', ')`,
type: `string`,
title: `Investors/Company owners`
}
},

dimensions: {
legal_entity_id: {
sql: `legal_entity_id`,
type: `string`,
primary_key: true,
shown: true
},
entity_type: {
sql: `entity_type`,
type: `string`,
},
display_name: {
sql: `display_name`,
type: `string`,
title: `Investors/Company owners`
}
},

dataSource: `legal_entity`
});
```

I am using STRING_AGG measure, but the query:
```
{
"dimensions": [
"LE_LegalEntitiesMetadata.legal_entity_id"
],
"measures": [
"LE_LegalEntitiesMetadata.display_names"
]
}
```
ends up using sum in the generated SQL:
```
SELECT
`l_e__legal_entities_metadata__legal_entity_id` `l_e__legal_entities_metadata__legal_entity_id`,
sum(`l_e__legal_entities_metadata__display_names`) `l_e__legal_entities_metadata__display_names`
FROM
dev_pre_aggregations.l_e__legal_entities_metadata_rollup AS `l_e__legal_entities_metadata__rollup`
GROUP BY
1
ORDER BY
2 DESC
LIMIT
10000
```

![Image](https://github.com/user-attachments/assets/27b4f82a-0357-4fbc-a28d-5dca1efe3f87)
![Image](https://github.com/user-attachments/assets/48608631-640f-4125-b659-ba484a3a35e0)

**Expected behavior**
It should remain as STRING_AGG
```
STRING_AGG(
"l_e__legal_entities_metadata".display_name :: TEXT,
', '
) "l_e__legal_entities_metadata__display_names"
```

**Version:**
1.1.18

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.