You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@druid.apache.org by "deepuak (via GitHub)" <gi...@apache.org> on 2023/05/04 13:26:33 UTC
[GitHub] [druid] deepuak opened a new issue, #14206: V 25.0.0: When using OR condition, giving error : Detected duplicate prefix in join clauses
deepuak opened a new issue, #14206:
URL: https://github.com/apache/druid/issues/14206
**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= 'a@b.com'
)
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= 'a@b.com'
)
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= 'a@b.com'
)
group by
manf_desc
order by
SUM(fes.dist_total_revenue) desc
limit
5
)
```
--
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: commits-unsubscribe@druid.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
[GitHub] [druid] deepuak commented on issue #14206: V 25.0.0: When using OR condition, giving error : Detected duplicate prefix in join clauses
Posted by "deepuak (via GitHub)" <gi...@apache.org>.
deepuak commented on issue #14206:
URL: https://github.com/apache/druid/issues/14206#issuecomment-1535919341
> @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 [4s@dr.com](mailto:4s@dr.com) 4S H Quarterly Zip 42132205
2023-04-18T06:25:42.242Z [a.a@c.com](mailto:a.a@c.com) 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 [a.p@cl.com](mailto:a.p@cl.com) 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","4231154
7","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 [a2_test@do.com](mailto:a2_test@do.com) 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 [aa@cl.com](mailto:aa@cl.com) 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 [aa@cla.com](mailto:aa@cla.com) 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 [a@cl.com](mailto:a@cl.com) 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 [aa@cl.com](mailto:aa@cl.com) 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 [a@cl.com](mailto:a@cl.com) MED Quarterly National ["11223344","22334455","41151519"] ["ASC","HOME CARE","HOSPITAL/HEALTH SYSTEM","LAB/DIAGNOSTIC","LONG-TERM CARE","OTHER","PHYSICIAN OFFICE","RETAIL/CONSUMER","TREATMENT CENTER"]
--
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: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
[GitHub] [druid] clintropolis commented on issue #14206: V 25.0.0: When using OR condition, giving error : Detected duplicate prefix in join clauses
Posted by "clintropolis (via GitHub)" <gi...@apache.org>.
clintropolis commented on issue #14206:
URL: https://github.com/apache/druid/issues/14206#issuecomment-1535629371
I wonder if this is fixed by #13943?
--
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: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
[GitHub] [druid] abhishekagarwal87 commented on issue #14206: V 25.0.0: When using OR condition, giving error : Detected duplicate prefix in join clauses
Posted by "abhishekagarwal87 (via GitHub)" <gi...@apache.org>.
abhishekagarwal87 commented on issue #14206:
URL: https://github.com/apache/druid/issues/14206#issuecomment-1534825890
@deepuak - can you also attach the sample data that you used to reproduce the issue?
--
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: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org