pre-aggregation not happening for many to one cube join on other cube measures #9280
Description
This is can be reproduced with following test cubes,cquery is becoming non-compatible for pre-aggregations even have the same query
cube("custom_agg_variant", {
data_source: "custom_source",
sql: `SELECT * from invoice_variant where deleted_at is null`,
joins: {
custom_agg: {
sql: `${CUBE}.invoice_id = ${custom_agg}.id`,
relationship: `many_to_one`,
},
},
dimensions: {
id: {
sql: `${CUBE}.id`,
type: "number",
primary_key: true,
},
},
measures: {
cost_of_goods: {
sql: `CASE when ${custom_agg.is_return} = 0 then ${CUBE}.cost * ${CUBE}.quantity else -(${CUBE}.cost * ${CUBE}.quantity) end`,
type: "sum",
},
},
pre_aggregations: {
main: {
type: `rollup`,
dimensions: [
custom_agg.stock_location_id,
],
measures: [
cost_of_goods,
custom_agg.sales_exclusive,
],
partitionGranularity: `month`,
timeDimension: custom_agg.complete_date,
granularity: `hour`,
build_range_start: { sql: `SELECT DATE('2024-01-01')` },
build_range_end: { sql: `SELECT NOW()` },
},
},
});
cube("custom_agg", {
data_source: "custom_source",
sql: `SELECT total, is_return, completion_date, stock_location_id, id, tax
from invoice i where AND deleted_at is null`,
dimensions: {
complete_date: {
sql: `${CUBE}.completion_date`,
type: "time",
},
stock_location_id: {
sql: `${CUBE}.stock_location_id`,
type: "number",
},
invoice_id: {
sql: `${CUBE}.id`,
type: "number",
primary_key: true,
},
is_return: {
sql: `${CUBE}.is_return`,
type: "number"
}
},
measures: {
sales_exclusive: {
sql: `CASE WHEN ${CUBE}.is_return = 0 THEN ${CUBE}.total - ${CUBE}.tax ELSE -(${CUBE}.total - ${CUBE}.tax) END`,
type: `sum`,
},
},
});