cube icon indicating copy to clipboard operation
cube copied to clipboard

Published 20 hours ago • verified publisher icon cube-js

Identical measures behave differently: one causes "Unrecognized name" error in generated SQL for BigQuery

Open grzaks opened this issue 9 months ago • 0 comments

Problem

The following schema is simplified example of an issue I have with my production schema (~15 cubes, BigQuery data source). It consists of just two joined cubes poc_orders and poc_daily_metrics and some simple measures defined.

There is poc_orders.something measure that is using related cube measure. If you query this measure everything works as you might expect. Then there are poc_orders.count and poc_orders.count2 measures which are identical.

There is measure_working_fine which divides poc_orders.something by poc_orders.count and this one works fine.

Then there is measure_causing_error which divides poc_orders.something by poc_orders.count2 and here comes the issue: querying measure_causing_error makes the generated SQL that causes Unrecognized name: poc_orders at [1:79] error at BigQuery. Unless you query it together with poc_orders.count2, then it works fine.

How is that possible if count and count2 measures are 100% identical? Why it works only when poc_orders.count2 is also part of the query?

I pasted related schema file, generated SQL and log below and in this gist

Related Cube.js schema

cube(`poc_orders`, {
    public: false,
    sql: `
    SELECT co.*
    FROM cubes.orders co
    WHERE ${FILTER_PARAMS.orders.work_date.filter("addTimestamp")}
    `,
    joins: {
        poc_daily_metrics: {
            relationship: `many_to_one`,
            sql: `
            ${branch_uid} = ${poc_daily_metrics.branch_uid} 
            AND DATE(${work_date}) = ${poc_daily_metrics.work_date}
            `,
        },

    },
    measures: {
        count: {
            type: 'count',
        },

        count2: {
            type: 'count',
        },

        something: {
            sql: `${count} * ${poc_daily_metrics.finished_orders_count}`,
            type: 'number'
        },

        measure_working_fine: {
            sql: `${something}/${count}`,
            type: 'number',
        },

        measure_causing_error: {
            sql: `${something}/${count2}`,
            type: 'number',
        },
    },
    dimensions: {
        uid: {
            sql: `${CUBE}.uid`,
            type: `string`,
            primaryKey: true,
        },
        branch_uid: {
            sql: `${CUBE}.branchUid`,
            type: `string`,
        },
        work_date: {
            sql: `${CUBE}.addTimestamp`,
            type: `time`,
        },
    },
})

cube(`poc_daily_metrics`,
    {
        public: false,
        sql: `
                SELECT 
                    DATE(startTimestamp) as workDate,
                    branchUid,
                    COUNT(*) as finishedCount
                FROM cubes.activities
                WHERE ( 
                    ${FILTER_PARAMS.orders.work_date.filter("startTimestamp")}
                )
                GROUP BY 1, 2            
        `,
        measures: {
            finished_orders_count: {
                sql: `${CUBE}.finishedCount`,
                type: 'sum',
            },
        },
        dimensions: {
            id: {
                sql: `${work_date} || ${branch_uid}`,
                type: `string`,
                primaryKey: true,
            },
            branch_uid: {
                sql: `${CUBE}.branchUid`,
                type: `string`,
            },
            work_date: {
                sql: `${CUBE}.workDate`,
                type: `time`,
            },
        }
    }
)

Related Cube.js generated SQL for measure_working_fine

SELECT
  `poc_orders__count` * `poc_daily_metrics__finished_orders_count` / `poc_orders__count` `poc_orders__measure_working_fine`
FROM
  (
    SELECT
      count(`main__poc_orders`.uid) `poc_orders__count`
    FROM
      (
        SELECT
          co.*
        FROM
          cubes.orders co
        WHERE
          (1 = 1)
      ) AS `main__poc_orders`
      LEFT JOIN (
        SELECT
          DATE(startTimestamp) as workDate,
          branchUid,
          COUNT(*) as finishedCount
        FROM
          cubes.activities
        WHERE
          ((1 = 1))
        GROUP BY
          1,
          2
      ) AS `main__poc_daily_metrics` ON `main__poc_orders`.branchUid = `main__poc_daily_metrics`.branchUid
      AND DATE(`main__poc_orders`.addTimestamp) = `main__poc_daily_metrics`.workDate
    WHERE
      (
        `main__poc_orders`.addTimestamp >= TIMESTAMP(?)
        AND `main__poc_orders`.addTimestamp <= TIMESTAMP(?)
      )
  ) as q_0,
  (
    SELECT
      sum(
        `poc_daily_metrics_key__poc_daily_metrics`.finishedCount
      ) `poc_daily_metrics__finished_orders_count`
    FROM
      (
        SELECT
          DISTINCT `poc_daily_metrics_key__poc_daily_metrics`.workDate || `poc_daily_metrics_key__poc_daily_metrics`.branchUid `poc_daily_metrics__id`
        FROM
          (
            SELECT
              co.*
            FROM
              cubes.orders co
            WHERE
              (1 = 1)
          ) AS `poc_daily_metrics_key__poc_orders`
          LEFT JOIN (
            SELECT
              DATE(startTimestamp) as workDate,
              branchUid,
              COUNT(*) as finishedCount
            FROM
              cubes.activities
            WHERE
              ((1 = 1))
            GROUP BY
              1,
              2
          ) AS `poc_daily_metrics_key__poc_daily_metrics` ON `poc_daily_metrics_key__poc_orders`.branchUid = `poc_daily_metrics_key__poc_daily_metrics`.branchUid
          AND DATE(`poc_daily_metrics_key__poc_orders`.addTimestamp) = `poc_daily_metrics_key__poc_daily_metrics`.workDate
        WHERE
          (
            `poc_daily_metrics_key__poc_orders`.addTimestamp >= TIMESTAMP(?)
            AND `poc_daily_metrics_key__poc_orders`.addTimestamp <= TIMESTAMP(?)
          )
      ) AS `keys`
      LEFT JOIN (
        SELECT
          DATE(startTimestamp) as workDate,
          branchUid,
          COUNT(*) as finishedCount
        FROM
          cubes.activities
        WHERE
          ((1 = 1))
        GROUP BY
          1,
          2
      ) AS `poc_daily_metrics_key__poc_daily_metrics` ON `keys`.`poc_daily_metrics__id` = `poc_daily_metrics_key__poc_daily_metrics`.workDate || `poc_daily_metrics_key__poc_daily_metrics`.branchUid
  ) as q_1
LIMIT
  10000

Related Cube.js generated SQL for measure_causing_error

SELECT
  `poc_orders__count` * `poc_daily_metrics__finished_orders_count` / count(`poc_orders`.uid) `poc_orders__measure_causing_error`
FROM
  (
    SELECT
      count(`main__poc_orders`.uid) `poc_orders__count`
    FROM
      (
        SELECT
          co.*
        FROM
          cubes.orders co
        WHERE
          (1 = 1)
      ) AS `main__poc_orders`
      LEFT JOIN (
        SELECT
          DATE(startTimestamp) as workDate,
          branchUid,
          COUNT(*) as finishedCount
        FROM
          cubes.activities
        WHERE
          ((1 = 1))
        GROUP BY
          1,
          2
      ) AS `main__poc_daily_metrics` ON `main__poc_orders`.branchUid = `main__poc_daily_metrics`.branchUid
      AND DATE(`main__poc_orders`.addTimestamp) = `main__poc_daily_metrics`.workDate
    WHERE
      (
        `main__poc_orders`.addTimestamp >= TIMESTAMP(?)
        AND `main__poc_orders`.addTimestamp <= TIMESTAMP(?)
      )
  ) as q_0,
  (
    SELECT
      sum(
        `poc_daily_metrics_key__poc_daily_metrics`.finishedCount
      ) `poc_daily_metrics__finished_orders_count`
    FROM
      (
        SELECT
          DISTINCT `poc_daily_metrics_key__poc_daily_metrics`.workDate || `poc_daily_metrics_key__poc_daily_metrics`.branchUid `poc_daily_metrics__id`
        FROM
          (
            SELECT
              co.*
            FROM
              cubes.orders co
            WHERE
              (1 = 1)
          ) AS `poc_daily_metrics_key__poc_orders`
          LEFT JOIN (
            SELECT
              DATE(startTimestamp) as workDate,
              branchUid,
              COUNT(*) as finishedCount
            FROM
              cubes.activities
            WHERE
              ((1 = 1))
            GROUP BY
              1,
              2
          ) AS `poc_daily_metrics_key__poc_daily_metrics` ON `poc_daily_metrics_key__poc_orders`.branchUid = `poc_daily_metrics_key__poc_daily_metrics`.branchUid
          AND DATE(`poc_daily_metrics_key__poc_orders`.addTimestamp) = `poc_daily_metrics_key__poc_daily_metrics`.workDate
        WHERE
          (
            `poc_daily_metrics_key__poc_orders`.addTimestamp >= TIMESTAMP(?)
            AND `poc_daily_metrics_key__poc_orders`.addTimestamp <= TIMESTAMP(?)
          )
      ) AS `keys`
      LEFT JOIN (
        SELECT
          DATE(startTimestamp) as workDate,
          branchUid,
          COUNT(*) as finishedCount
        FROM
          cubes.activities
        WHERE
          ((1 = 1))
        GROUP BY
          1,
          2
      ) AS `poc_daily_metrics_key__poc_daily_metrics` ON `keys`.`poc_daily_metrics__id` = `poc_daily_metrics_key__poc_daily_metrics`.workDate || `poc_daily_metrics_key__poc_daily_metrics`.branchUid
  ) as q_1
LIMIT
  10000

Diff between the two above generated SQLs

--- generated_valid.sql	2024-04-29 22:00:39
+++ generated_invalid.sql	2024-04-29 22:01:19
@@ -1,5 +1,5 @@
 SELECT
-  `poc_orders__count` * `poc_daily_metrics__finished_orders_count` / `poc_orders__count` `poc_orders__measure_working_fine`
+  `poc_orders__count` * `poc_daily_metrics__finished_orders_count` / count(`poc_orders`.uid) `poc_orders__measure_causing_error`
 FROM
   (
     SELECT
\ No newline at end of file

Part of the cube console output

2024-04-29T22:07:51+02:00 --
2024-04-29T22:07:51+02:00 {
2024-04-29T22:07:51+02:00   "params": [
2024-04-29T22:07:51+02:00     "2024-04-28T00:00:00.000Z",
2024-04-29T22:07:51+02:00     "2024-04-28T23:59:59.999Z",
2024-04-29T22:07:51+02:00     "2024-04-28T00:00:00.000Z",
2024-04-29T22:07:51+02:00     "2024-04-28T23:59:59.999Z"
2024-04-29T22:07:51+02:00   ]
2024-04-29T22:07:51+02:00 } 
2024-04-29T22:07:51+02:00 Error: Unrecognized name: poc_orders at [1:79]
2024-04-29T22:07:51+02:00     at QueryQueue.parseResult (/cube/node_modules/@cubejs-backend/query-orchestrator/src/orchestrator/QueryQueue.js:397:13)
2024-04-29T22:07:51+02:00     at QueryQueue.executeInQueue (/cube/node_modules/@cubejs-backend/query-orchestrator/src/orchestrator/QueryQueue.js:369:19)
2024-04-29T22:07:51+02:00     at processTicksAndRejections (node:internal/process/task_queues:95:5)
2024-04-29T22:07:51+02:00     at /cube/node_modules/@cubejs-backend/query-orchestrator/src/orchestrator/QueryCache.ts:756:17
2024-04-29T22:07:51+02:00     at QueryOrchestrator.fetchQuery (/cube/node_modules/@cubejs-backend/query-orchestrator/src/orchestrator/QueryOrchestrator.ts:302:20)
2024-04-29T22:07:51+02:00     at OrchestratorApi.executeQuery (/cube/node_modules/@cubejs-backend/server-core/src/core/OrchestratorApi.ts:98:20)
2024-04-29T22:07:51+02:00     at /cube/node_modules/@cubejs-backend/api-gateway/src/gateway.ts:1467:21
2024-04-29T22:07:51+02:00     at async Promise.all (index 0)
2024-04-29T22:07:51+02:00     at ApiGateway.getSqlResponseInternal (/cube/node_modules/@cubejs-backend/api-gateway/src/gateway.ts:1465:31)
2024-04-29T22:07:51+02:00     at /cube/node_modules/@cubejs-backend/api-gateway/src/gateway.ts:1643:28
2024-04-29T22:07:51+02:00     at async Promise.all (index 0)
2024-04-29T22:07:51+02:00     at ApiGateway.load (/cube/node_modules/@cubejs-backend/api-gateway/src/gateway.ts:1634:23)
2024-04-29T22:07:51+02:00     at /cube/node_modules/@cubejs-backend/api-gateway/src/gateway.ts:272:7
2024-04-29T22:07:51+02:00 Orchestrator error: f44bd509-2f0f-4363-bfb4-51cc089113be-span-1 (294ms)
2024-04-29T22:07:51+02:00 --
2024-04-29T22:07:51+02:00 {
2024-04-29T22:07:51+02:00   "measures": [
2024-04-29T22:07:51+02:00     "poc_orders.measure_causing_error"
2024-04-29T22:07:51+02:00   ],
2024-04-29T22:07:51+02:00   "timeDimensions": [
2024-04-29T22:07:51+02:00     {
2024-04-29T22:07:51+02:00       "dimension": "poc_orders.work_date",
2024-04-29T22:07:51+02:00       "dateRange": "Yesterday"
2024-04-29T22:07:51+02:00     }
2024-04-29T22:07:51+02:00   ]
2024-04-29T22:07:51+02:00 }
2024-04-29T22:07:51+02:00 --
2024-04-29T22:07:51+02:00 {
2024-04-29T22:07:51+02:00   "securityContext": {
2024-04-29T22:07:51+02:00     "iat": 1714418741,
2024-04-29T22:07:51+02:00     "exp": 1714505141
2024-04-29T22:07:51+02:00   }
2024-04-29T22:07:51+02:00 } 
2024-04-29T22:07:51+02:00 Error: Unrecognized name: poc_orders at [1:79]

The Cube version is v0.35.24

grzaks avatar Apr 30 '24 07:04 grzaks