incorrect alias generated while joining cubes: error ER_BAD_FIELD_ERROR #9271
Description
I am getting this error
ER_BAD_FIELD_ERROR: Unknown column 'aggregated_data.is_reversed' in 'field list'.
And this error is coming due to the fact that we have Chasm and fan traps and the generated query does not have this alias aggregated_data. Please have a look at the code below
cube("aggregated_data", {
data_source: "main_source",
sql: `SELECT total_amount, is_reversed, processed_date, account_id, location_id, record_id, tax_amount FROM transactions WHERE status = 'Processed' AND deleted_at IS NULL`,
joins: {
aggregated_data_details: {
sql: `${CUBE}.record_id = ${aggregated_data_details}.transaction_id AND ${CUBE}.account_id = ${aggregated_data_details}.account_id`,
relationship: `one_to_many`,
},
},
dimensions: {
account_id: {
sql: `${CUBE}.account_id`,
type: "number",
},
transaction_id: {
sql: `${CUBE}.record_id`,
type: "number",
primary_key: true,
},
is_reversed: {
sql: `${CUBE}.is_reversed`,
type: "number`,
},
},
measures: {
total_cost: {
sql: `CASE WHEN ${CUBE}.is_reversed = 0 THEN ${aggregated_data_details.item_cost} ELSE -(${aggregated_data_details.item_cost}) END`,
type: `sum`,
},
total_processed_transactions: {
sql: `CASE WHEN ${CUBE}.is_reversed = 0 THEN 1 ELSE 0 END`,
type: `sum`,
},
},
});
cube("aggregated_data_details", {
data_source: "main_source",
sql: `SELECT * FROM transaction_items ti WHERE ti.deleted_at IS NULL`,
dimensions: {
item_id: {
sql: `${CUBE}.id`,
type: "number",
primary_key: true,
},
},
measures: {
item_quantity: {
sql: `${CUBE}.quantity`,
type: "number",
},
item_cost: {
sql: `${CUBE}.cost`,
type: "number`,
},
total_item_cost: {
sql: `${CUBE.item_cost} * ${CUBE.item_quantity}`,
type: "number`,
},
},
});