Cannot create index for original_sql pre-aggregation on Postgres
- Dominant language
- Rust
- Stars
- 20.8k
- Forks
- 2.1k
- Avg merge
- 1d 2h
- Merged PRs (30d)
- 181
Description
**Describe the bug**
We would like to create an index on an original_sql pre-aggregation stored in Postgres.
Cube fails to create said index, because it incorrectly prepends the cube name to the specified index column. The column of the implicitly created table does not contain that cube name, which leads to an error.
**Relevant extract of the error message:**
> {"message":"Error while querying","queueId":128,"processingId":128,"queueSize":0,"duration":587,"queryKey":[["CREATE TABLE pre_agg_tenant_123.base_positions_main AS SELECT **position_id**, position_name FROM positions"
...
> "sql":
["CREATE INDEX positions_main_position_id_index ON pre_agg_tenant_123.base_positions_main (\"**base_positions__position_id**\")",[]]}],
"createTableIndexes":[{"indexName":"base_positions_main_position_id_index","type":"regular","columns ["\"**base_positions__position_id**\""]}],
"readOnly":false,"expandedPartition":true,"priority":-1},"addedToQueueTime":1700057570643,"error":"error: column \"**base_positions__position_id**\" does not exist
**Expected behavior**
It should be possible to create the index. Cube should not prepend the cube name to the index column while generating the sql command to create the index:
`CREATE INDEX positions_main_position_id_index ON pre_agg_tenant_123.base_positions_main (\"position_id\")`
instead of
`CREATE INDEX positions_main_position_id_index ON pre_agg_tenant_123.base_positions_main (\"base_positions__position_id\")`
**Minimally reproducible Cube Schema**
```yaml
cubes:
- name: base_positions
public: false
sql: SELECT "id_1" AS position_id, "test" AS position_name
dimensions:
- name: position_id
sql: position_id
type: string
primary_key: true
- name: position_name
sql: position_name
type: string
pre_aggregations:
- name: main
type: original_sql
external: false
indexes:
- name: position_id_index
columns:
- position_id
# Also tried:
# - CUBE.position_id
# - base_positions.position_id
# - base_positions_main.position_id
```
**Version:**
0.34.19
Contributor guide
Assessment
This issue has not been assessed yet.