Thanks for writing back and confirming that the suggested workaround is
effective.
As far as comments on the future: when we do a release with #10942, your
original query will work ok too, but I wonder if it might still be
better-performing to do the + 0. Long typed keys might be faster than
string typed keys.
And later on, when we make the changes to carry type information through to
the Scan query toolchest, the original query will work *and* will switch to
using long typed keys automatically.
On Thu, Jun 24, 2021 at 4:35 PM Jason Chen <jason.c...@shopify.com.invalid>
wrote:
> 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
>
