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, [email protected], wrote:
>
> the DIM CTE is getting run as a Scan subquery, and the type information is
