Drill error when using a filter in Superset with a CASE WHEN in the SQL query

[Zakyrel]

Hello,

Describe the bug
Apache Drill 1.21.1
Apache Superset 3.1.1
In Superset, we setup a connexion to Drill as source for our dashboards

To Reproduce

1. In Superset, I created a graph (several in fact) using a SQL query with a CASE WHEN to display labels instead of the data. Here is an example I'm using for a graph :

with
tmp_etablissements as
(
select distinct
id, uai, nom, aca_id, nature_id
from base_kinto.vss_etablissements
),
tmp_domaines as
(
select
id, libelle
from base_kinto.vss_domaines
),
tmp_offres_lmd as
(
select
id, uai_principal
from base_kinto.vss_offres_lmd
),
tmp_diplomes_doctorat as
(
select
id, intitule, inf, id_parent, type_diplome, domaine
from base_kinto.vss_diplomes_doctorat
),
tmp_diplomes_capa as
(
select
id, intitule, inf, id_parent, type_diplome, '' as domaine
from base_kinto.vss_diplomes_capa
),
tmp_diplomes_daeu as
(
select
id, intitule, inf, id_parent, type_diplome, '' as domaine
from base_kinto.vss_diplomes_daeu
),
tmp_diplomes_deust as
(
select
id, intitule, inf, id_parent, type_diplome, domaine
from base_kinto.vss_diplomes_deust
),
tmp_diplomes_licence as
(
select
id, intitule, inf, id_parent, type_diplome, domaine
from base_kinto.vss_diplomes_licence
),
tmp_diplomes_licence_pro as
(
select
id, intitule, inf, id_parent, type_diplome, domaine
from base_kinto.vss_diplomes_licence_pro
),
tmp_diplomes_master as
(
select
id, intitule, inf, id_parent, type_diplome, domaine
from base_kinto.vss_diplomes_master
),
tmp_diplomes as
(
select * from tmp_diplomes_doctorat
union all
select * from tmp_diplomes_capa
union all
select * from tmp_diplomes_deust
union all
select * from tmp_diplomes_daeu
union all
select * from tmp_diplomes_licence
union all
select * from tmp_diplomes_licence_pro
union all
select * from tmp_diplomes_master
)
select
tmp_diplomes.id as ID,
tmp_diplomes.intitule as Nom du diplôme,
case
when tmp_diplomes.type_diplome = 'D' then 'Doctorat'
when tmp_diplomes.type_diplome = 'L' then 'Licence'
when tmp_diplomes.type_diplome = 'LP' then 'Licence Pro'
when tmp_diplomes.type_diplome = 'M' then 'Master'
else tmp_diplomes.type_diplome
end as Type diplôme,
tmp_domaines.libelle as Domaine,
tmp_etablissements.uai as UAI de l'établissement,
tmp_etablissements.nom as Nom de l'établissement
from tmp_diplomes
inner join tmp_offres_lmd on tmp_diplomes.id_parent = tmp_offres_lmd.id
inner join tmp_etablissements on tmp_offres_lmd.uai_principal = tmp_etablissements.uai
left join tmp_domaines on tmp_diplomes.domaine = tmp_domaines.id
;

As you can see, the CASE WHEN is used to change some data:
when tmp_diplomes.type_diplome = 'D' then 'Doctorat'
when tmp_diplomes.type_diplome = 'L' then 'Licence'
when tmp_diplomes.type_diplome = 'LP' then 'Licence Pro'
when tmp_diplomes.type_diplome = 'M' then 'Master'

2. Then I created a filter in the dashboard containing this graph.

3. When using this filter with one of this label from the CASE WHEN, it works fine. But when I used multiple labels from the CASE WHEN (see screenshot), I have an error message from Drill:

Error: Final Drill query state is FAILED. Unexpected exception during fragment initialization: Error while applying rule ReduceAndSimplifyFilterRule, args [rel#440596:LogicalFilter.NONE.ANY([])., 'Doctorat', =($4, 'L'), 'Licence', =($4, 'LP'), 'Licence Pro', =($4, 'M'), 'Master', $4), Sarg['Licence':CHAR(11), 'Licence Pro']:CHAR(11)))]

I tested the same SQL query but without the CASE WHEN, using 'L', 'LP' in the filter, it works fine.

I think that Drill doesn't like when using values other than the initial data.

Since it is a Drill error message, I opened this ticket here, but maybe you will check with your Apache colleague in charge of Superset and the ossie may be in Superset for every source, not only Drill ?

Let me know if you need more information.

Regards,

[Zakyrel]

Hello,

Kind reminder about this issue.

Regards,

[jnturton]

Can you share the SQL that Drill tries to execute? It should be written to the Drill logs...