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([]).[](input=RelSubset#440443,condition=SEARCH(CASE(=($4, 'D'), '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...