V 25.0.0: When using OR condition, giving error : Detected duplicate prefix in join clauses

[deepuak]

Druid Version 25.0.0

Query used:

select SUM(fe.dist_total_revenue),
    fe.sales_year, 
    fe.sales_month, 
    fe.sales_quarter from denorm_fact_extrapolated_unspsc fe 
    -- INNER join 
   where fe.manf_desc = 'BDD' OR fe.manf_desc in (SELECT 
                       
                      manf_desc as top5manf_desc 
                    FROM 
                      denorm_fact_extrapolated_unspsc fes 
                    where 
                      __time BETWEEN TIME_PARSE ('2019-01-31T00:00:00.000Z') 
                      AND TIME_PARSE ('2023-01-31T00:00:00.000Z')
                      AND fes.manf_desc != 'BDD'
                      and fes.taxonomy_id in (
                        SELECT 
                          unspsc_code 
                        FROM 
                          denorm_manf_entitlements 
                        where 
                          email_address= '[email protected]'
                      )
                    group by 
                      manf_desc
                    order by 
                      SUM(fes.dist_total_revenue) desc 
                    limit 
                      5
                      )
                      
                      -- top5manf on manf_desc = top5manf_desc
                      -- WHERE
                            AND  __time BETWEEN TIME_PARSE ('2019-01-31T00:00:00.000Z') 
                            AND TIME_PARSE ('2023-01-31T00:00:00.000Z')
                            AND fe.taxonomy_id IN(
                              SELECT 
                                unspsc_code 
                              FROM 
                                denorm_manf_entitlements 
                              WHERE 
                                email_address= '[email protected]'
                            )
              group by fe.sales_year, 
                      fe.sales_month, 
                      fe.sales_quarter

Error when running above code:

Error: Unknown exception
Detected duplicate prefix in join clauses: [__j0.]
org.apache.druid.java.util.common.IAE

Main Issue

If i remove below code from the query, it works

OR fe.manf_desc in (SELECT 

                      manf_desc as top5manf_desc 
                    FROM 
                      denorm_fact_extrapolated_unspsc fes 
                    where 
                      __time BETWEEN TIME_PARSE ('2019-01-31T00:00:00.000Z') 
                      AND TIME_PARSE ('2023-01-31T00:00:00.000Z')
                      AND fes.manf_desc != 'BDD'
                      and fes.taxonomy_id in (
                        SELECT 
                          unspsc_code 
                        FROM 
                          denorm_manf_entitlements 
                        where 
                          email_address= '[email protected]'
                      )
                    group by 
                      manf_desc
                    order by 
                      SUM(fes.dist_total_revenue) desc 
                    limit 
                      5
                      )

[abhishekagarwal87]

@deepuak - can you also attach the sample data that you used to reproduce the issue?

[clintropolis]

I wonder if this is fixed by #13943?

[deepuak]

@deepuak - can you also attach the sample data that you used to reproduce the issue?
Sure, here is the sample data:

table: denorm_fact_extrapolated_unspsc

__time product_id monthenddatekey sales_year sales_month sales_quarter dist_total_revenue dist_total_units facility_type unspsc_1 unspsc_2 unspsc_3 unspsc_4 taxonomy_id manf_desc prod_desc sku direct_total_revenue direct_total_units
2017-01-31T00:00:00.000Z 1000003 20170131 2017 1 1 73 1 PHYSICIAN OFFICE Medical Equipment and Accessories and Supplies Surgical products Surgical clamps and forceps and surgical ligators and related instruments Surgical clamps or clips or forceps 42291802 DERRON SURGICAL INSTRUMENTS FORCEP TISSUE 5" STRAIGHT JAW STAINLESS STEEL EA 42138 73 1
2017-01-31T00:00:00.000Z 1000006 20170131 2017 1 1 340 1 PHYSICIAN OFFICE Medical Equipment and Accessories and Supplies Patient care and treatment products and supplies Vascular and compression therapy equipment and supplies Vascular or compression apparel or support 42142802 DEVON MEDICAL PUMP DVT SYSTEM CIRONA 6100 EA 6100 340 1
2017-01-31T00:00:00.000Z 1000056 20170131 2017 1 1 1128 34 LONG-TERM CARE Medical Equipment and Accessories and Supplies Medical cleaning and sterilization products Cold sterilization and disinfectant solutions Medical surface disinfectants 42281604 DIVERSEY DISINFECTANT,EXPOSE II 256, 2X2L, JFILL 5699 1128 34
2017-01-31T00:00:00.000Z 1000062 20170131 2017 1 1 267 12 LONG-TERM CARE Cleaning Equipment and Supplies Cleaning and janitorial supplies Cleaning and disinfecting solutions Floor cleaners 47131801 DIVERSEY DBD-MBO-SHAMPOO,CARPET, HIGH FOAM, 4 X 1 5002689 267 12
2017-01-31T00:00:00.000Z 1000064 20170131 2017 1 1 57 12 LONG-TERM CARE Cleaning Equipment and Supplies Cleaning and janitorial supplies Cleaning and disinfecting solutions Furniture polish or waxes 47131806 DIVERSEY POLISH FURNITURE PLEDGE 10OZ DISC BY MFG-RPL NOT SET UP 723725 57 12
2017-01-31T00:00:00.000Z 1000066 20170131 2017 1 1 402 192 HOSPITAL/HEALTH SYSTEM Cleaning Equipment and Supplies Cleaning and janitorial supplies Cleaning and disinfecting solutions Toilet cleaners 47131829 DIVERSEY CLEANER,BOWL,CREW,MILD ACID,1X32OZ 94476081 402 192
2017-01-31T00:00:00.000Z 1000067 20170131 2017 1 1 327 5 HOSPITAL/HEALTH SYSTEM Cleaning Equipment and Supplies Cleaning and janitorial supplies Cleaning and disinfecting solutions Stain cleaners or removers 47131827 DIVERSEY STRIPPER,FLOOR,ULTRA,5GAL,BOX 95386176 327 5
2017-01-31T00:00:00.000Z 1000086 20170131 2017 1 1 61 1 TREATMENT CENTER Medical Equipment and Accessories and Supplies Orthopedic and prosthetic and sports medicine products Orthopedic softgoods for lower extremity Knee therapeutic brace or support 42241703 DJO GLOBAL BRACE KNEE PATELLA RIGHT XXXLARGE 1102607 61 1
2017-01-31T00:00:00.000Z 1000086 20170131 2017 1 1 154 2 PHYSICIAN OFFICE Medical Equipment and Accessories and Supplies Orthopedic and prosthetic and sports medicine products Orthopedic softgoods for lower extremity Knee therapeutic brace or support 42241703 DJO GLOBAL BRACE KNEE PATELLA RIGHT XXXLARGE 1102607 154 2
2017-01-31T00:00:00.000Z 1000086 20170131 2017 1 1 536 9 HOSPITAL/HEALTH SYSTEM Medical Equipment and Accessories and Supplies Orthopedic and prosthetic and sports medicine products Orthopedic softgoods for lower extremity Knee therapeutic brace or support 42241703 DJO GLOBAL BRACE KNEE PATELLA RIGHT XXXLARGE 1102607 536 9

table: denorm_manf_entitlements

__time email_address manufacturer_desc data_frequency geography_entitlement unspsc_code cot
2023-04-18T06:25:42.242Z [email protected] 4S H Quarterly Zip 42132205
2023-04-18T06:25:42.242Z [email protected] CLAR Monthly Zip ["42311502","42311506","42311510","42311513","42311515","42311523","42311527","42311540","42311545","42311546","42311547","42311552","42312005","42312103","42312105","42312313","42312401"]
2023-04-18T06:25:42.242Z [email protected] 3B ["14111539","31162506","31201512","41112601","41113035","41113117","41116116","41116132","42131613","42131701","42131713","42131721","42141503","42142105","42142106","42142108","42142111","42142535","42143101","42152453","42172105","42181702","42181707","42181708","42181715","42181716","42181904","42182101","42182103","42182105","42182107","42182109","42182110","42182114","42182207","42203502","42203503","42221507","42221604","42221703","42221708","42221803","42222307","42241502","42241504","42241505","42241506","42241507","42241510","42281607","42281802","42281803","42281804","42281805","42281806","42281807","42281809","42281810","42281904","42281907","42281909","42294211","42294927","42295104","42295143","42295408","42295414","42295415","42295421","42295424","42295480","42311502","42311506","42311510","42311513","42311514","42311515","42311518","42311527","42311531","42311540","42311543","42311545","42311546","42311547","42311552","42311554","42312005","42312010","42312018","42312103","42312105","42312313","42312401","42312601","42312602","42312603","44121605","44121613","46181802","46181804","46182001","46182002","46182005","46182007","47131602","47131704","49241704","52151644","53131607","53131608","53131613","53131616","53131626","60104202"]
2023-04-18T06:25:42.242Z [email protected] MED Quarterly National ["11223344","22334455","41151519"] ["ASC","HOME CARE","HOSPITAL/HEALTH SYSTEM","LAB/DIAGNOSTIC","LONG-TERM CARE","OTHER","PHYSICIAN OFFICE","RETAIL/CONSUMER","TREATMENT CENTER"]
2023-04-18T06:25:42.242Z [email protected] CLA Monthly Zip ["42311502","42311506","42311510","42311513","42311515","42311523","42311527","42311540","42311545","42311546","42311547","42311552","42312005","42312103","42312105","42312313","42312401"]
2023-04-18T06:25:42.242Z [email protected] CLA Monthly Zip ["42311502","42311506","42311510","42311513","42311515","42311523","42311527","42311540","42311545","42311546","42311547","42311552","42312005","42312103","42312105","42312313","42312401"]
2023-04-18T06:25:42.242Z [email protected] 3MI ["14111539","31162506","31201512","41112601","41113035","41113117","41116116","41116132","42131613","42131701","42131713","42131721","42141503","42142105","42142106","42142108","42142111","42142535","42143101","42152453","42172105","42181702","42181707","42181708","42181715","42181716","42181904","42182101","42182103","42182105","42182107","42182109","42182110","42182114","42182207","42203502","42203503","42221507","42221604","42221703","42221708","42221803","42222307","42241502","42241504","42241505","42241506","42241507","42241510","42281607","42281802","42281803","42281804","42281805","42281806","42281807","42281809","42281810","42281904","42281907","42281909","42294211","42294927","42295104","42295143","42295408","42295414","42295415","42295421","42295424","42295480","42311502","42311506","42311510","42311513","42311514","42311515","42311518","42311527","42311531","42311540","42311543","42311545","42311546","42311547","42311552","42311554","42312005","42312010","42312018","42312103","42312105","42312313","42312401","42312601","42312602","42312603","44121605","44121613","46181802","46181804","46182001","46182002","46182005","46182007","47131602","47131704","49241704","52151644","53131607","53131608","53131613","53131616","53131626","60104202"]
2023-04-18T06:25:42.242Z aa@clcom CLAR Monthly Zip ["42311502","42311506","42311510","42311513","42311515","42311523","42311527","42311540","42311545","42311546","42311547","42311552","42312005","42312103","42312105","42312313","42312401"]
2023-04-18T06:25:42.242Z [email protected] CLARIVA Monthly Zip ["42311502","42311506","42311510","42311513","42311515","42311523","42311527","42311540","42311545","42311546","42311547","42311552","42312005","42312103","42312105","42312313","42312401"]
2023-04-18T06:25:42.242Z [email protected] MED Quarterly National ["11223344","22334455","41151519"] ["ASC","HOME CARE","HOSPITAL/HEALTH SYSTEM","LAB/DIAGNOSTIC","LONG-TERM CARE","OTHER","PHYSICIAN OFFICE","RETAIL/CONSUMER","TREATMENT CENTER"]