Query fails depending on variable order

1

Hello,

we are trying to connect to a Dremio datasource from Tableau. One of the queries which Tableau sends (but not all of them) fails with SYSTEM ERROR: AssertionError: Cannot find common type for join keys $0 (type VARCHAR(65536)) and $0 (type BIGINT). The query contains an inner join with a SELECTed table:

SELECT "s941_ihkategorien_bestand"."codierungsbezeichnung" AS "codierungsbezeichnung",
SUM("s941_ihkategorien_bestand"."anz_schaden") AS "sum_anz_schaden_ok",
SUM("s941_ihkategorien_bestand"."nettowert") AS "sum_nettowert_ok",
COUNT("s941_ihkategorien_bestand"."fahrzeugnummer") AS "usr_Calculation_135670996910780416_ok"
FROM "RAM_LCC.joins"."s941_ihkategorien_bestand" "s941_ihkategorien_bestand"
INNER JOIN (
SELECT SUM("s941_ihkategorien_bestand"."anz_schaden") AS "X__alias__0",
"s941_ihkategorien_bestand"."codierungsbezeichnung" AS "none_codierungsbezeichnung_nk"
FROM "RAM_LCC.joins"."s941_ihkategorien_bestand" "s941_ihkategorien_bestand"
WHERE (("s941_ihkategorien_bestand"."bauart" IN ('061', '151')) AND ((("s941_ihkategorien_bestand"."bestelldatum_sap" >= {d '2008-08-11'}) AND ("s941_ihkategorien_bestand"."bestelldatum_sap" <= {d '2018-11-26'})) OR ("s941_ihkategorien_bestand"."bestelldatum_sap" IS NULL)))
GROUP BY "s941_ihkategorien_bestand"."codierungsbezeichnung"
ORDER BY 1 DESC
LIMIT 20
) "t0" ON (("s941_ihkategorien_bestand"."codierungsbezeichnung" = "t0"."none_codierungsbezeichnung_nk") OR (("s941_ihkategorien_bestand"."codierungsbezeichnung" IS NULL) AND ("t0"."none_codierungsbezeichnung_nk" IS NULL)))
WHERE (("s941_ihkategorien_bestand"."bauart" IN ('061', '151')) AND ((("s941_ihkategorien_bestand"."bestelldatum_sap" >= {d '2008-08-11'}) AND ("s941_ihkategorien_bestand"."bestelldatum_sap" <= {d '2018-11-26'})) OR ("s941_ihkategorien_bestand"."bestelldatum_sap" IS NULL)))
GROUP BY "s941_ihkategorien_bestand"."codierungsbezeichnung"

When I switch the order of the SELECTed variables in the inner table, the query executes:

SELECT "s941_ihkategorien_bestand"."codierungsbezeichnung" AS "codierungsbezeichnung",
SUM("s941_ihkategorien_bestand"."anz_schaden") AS "sum_anz_schaden_ok",
SUM("s941_ihkategorien_bestand"."nettowert") AS "sum_nettowert_ok",
COUNT("s941_ihkategorien_bestand"."fahrzeugnummer") AS "usr_Calculation_135670996910780416_ok"
FROM "RAM_LCC.joins"."s941_ihkategorien_bestand" "s941_ihkategorien_bestand"
INNER JOIN (
SELECT "s941_ihkategorien_bestand"."codierungsbezeichnung" AS "none_codierungsbezeichnung_nk",
SUM("s941_ihkategorien_bestand"."anz_schaden") AS "X__alias__0"
FROM "RAM_LCC.joins"."s941_ihkategorien_bestand" "s941_ihkategorien_bestand"
WHERE (("s941_ihkategorien_bestand"."bauart" IN ('061', '151')) AND ((("s941_ihkategorien_bestand"."bestelldatum_sap" >= {d '2008-08-11'}) AND ("s941_ihkategorien_bestand"."bestelldatum_sap" <= {d '2018-11-26'})) OR ("s941_ihkategorien_bestand"."bestelldatum_sap" IS NULL)))
GROUP BY "s941_ihkategorien_bestand"."codierungsbezeichnung"
ORDER BY 2 DESC
LIMIT 20
) "t0" ON (("s941_ihkategorien_bestand"."codierungsbezeichnung" = "t0"."none_codierungsbezeichnung_nk") OR (("s941_ihkategorien_bestand"."codierungsbezeichnung" IS NULL) AND ("t0"."none_codierungsbezeichnung_nk" IS NULL)))
WHERE (("s941_ihkategorien_bestand"."bauart" IN ('061', '151')) AND ((("s941_ihkategorien_bestand"."bestelldatum_sap" >= {d '2008-08-11'}) AND ("s941_ihkategorien_bestand"."bestelldatum_sap" <= {d '2018-11-26'})) OR ("s941_ihkategorien_bestand"."bestelldatum_sap" IS NULL)))
GROUP BY "s941_ihkategorien_bestand"."codierungsbezeichnung"

I have attached a profile (10.6 KB) of the failed query. Is this a bug in Dremio? We would expect that the variable order should not matter.

2

hi @Laurens, it looks like this may be a bug. we’ll investigate. could you upload the output of the following query?

select * from information_schema.columns where table_name = 's941_ihkategorien_bestand'
3

Hi @jason, thank you! This is the result: information_schema.zip (704 Bytes)

4

Thanks @Laurens, We are looking at this and will get back to you shortly

5

Hi @Laurens

Would it be possible for you to rerun both the unsuccessful and the successful query and attach both profiles? We would like to compare how these are handled by the query planner.

Thank you!