Hey Gian, Thank you very much for your quick reply. The explanation is pretty clear. I can confirm that the query works with `api_client_id + 1`:
> quote_type > WITH DIM AS ( > SELECT (api_client_id + 0) AS api_client_id, title > FROM sales_model_api_clients_dimension AS API_CLIENTS > WHERE API_CLIENTS.shop_id = 25248974 > ), > FACTS AS ( > SELECT api_client_id, COUNT(*) as api_client_count > FROM sales_facts AS ORDERS > WHERE ORDERS.__time >= TIMESTAMP '2021-06-17 00:00:00' AND ORDERS.__time < > TIMESTAMP '2021-06-18 00:00:00' AND ORDERS.shop_id = 25248974 > GROUP BY 1 > ) > > SELECT DIM.api_client_id, DIM.title, FACTS.api_client_count > FROM FACTS > LEFT JOIN DIM ON FACTS.api_client_id = DIM.api_client_id The right hand side scan subquery with a virtual column that contains the “outputType”: "right":{ "type":"query", "query":{ "queryType":"scan", "dataSource":{ "type":"table", "name":"api_clients_inline_dimension_20210619" }, "intervals":{ "type":"intervals", "intervals":[ "-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z" ] }, "virtualColumns":[ { "type":"expression", "name":"v0", "expression":"(\"api_client_id\" + 0)", "outputType":"LONG" } ], "resultFormat":"compactedList", "batchSize":20480, "order":"none", "filter":null, "columns":[ "title", "v0" ], "legacy":false, "context":{ "sqlOuterLimit":100, "sqlQueryId":"a878dfe1-94d4-4b7f-b0b7-d36290e763da" }, "descending":false, "granularity":{ "type":"all" } } }, "rightPrefix":"j0.", "condition":"(\"d0\" == \"j0.v0\")", "joinType":"LEFT" }, For comparison, the original right-hand-side subquery which has columns without the types: "right":{ "type":"query", "query":{ "queryType":"scan", "dataSource":{ "type":"table", "name":"api_clients_inline_dimension_20210619" }, "intervals":{ "type":"intervals", "intervals":[ "-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z" ] }, "virtualColumns":[ ], "resultFormat":"compactedList", "batchSize":20480, "order":"none", "filter":null, "columns":[ "api_client_id", "title" ], "legacy":false, "context":{ "sqlOuterLimit":100, "sqlQueryId":"ea2c369e-c553-424f-b2ec-13d282ec315c" }, "descending":false, "granularity":{ "type":"all" } } }, > quote_type > Regards, Jianbin Chen On Jun 24, 2021, 5:08 PM -0400, dev@druid.apache.org, wrote: > > the DIM CTE is getting run as a Scan subquery, and the type information is