Cannot create index for original_sql pre-aggregation on Postgres
#7.420 aberto em 15 de nov. de 2023
Métricas do repositório
- Stars
- (19.563 stars)
- Métricas de merge de PR
- (Mesclagem média 5d 16h) (138 fundiu PRs em 30d)
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
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