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