[I] Drill error when using a filter in Superset with a CASE WHEN in the SQL query (drill)

[via GitHub]

Zakyrel opened a new issue, #2914:
URL: https://github.com/apache/drill/issues/2914

   ![Capture d'écran 2024-05-21 
153401](https://github.com/apache/drill/assets/142769136/d2166b23-dd81-40fc-bc7d-450a4ab4842e)
   
   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,


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: dev-unsubscr...@drill.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
us...@infra.apache.org