Hi folks,
the following JPQL leads into problems:
SELECT this FROM com.ottogroup.buying.process.dispoquota.MerchandisePlanKeyData this WHERE this.planningPeriod.id = :ppid AND this.buyingGroup.id = :bgid AND this.merchandiseGroup IS NULL AND this.productGroup IN (SELECT i.productGroup FROM Item i JOIN i.itemPlannings ip JOIN ip.stockProductLine spl JOIN spl.merchandiseGroup mg JOIN mg.buyingGroup bg WHERE bg.id = :bgid)
SQL generation transforms the IN to a "=" which leads to the error message: <2|false|0.9.5-incubating-CR304301v2> org.apache.openjpa.util.StoreException: ORA-01427: single-row subquery returns more than one row
{prepstmnt 17320121 SELECT t0.ID, t0.TSUPDATE, t0.ID_BUYINGGROUP, t0.ID_DISPOSAL, t0.FLOORSERVICELEVELPRC, t0.LIMITRETURNRATEPRC, t0.LIMITSENTPRC, t0.LIMITSURPLUSPRS, t0.ID_MERCHANDISEGROUP, t0.ID_PLANNINGPERIOD, t0.ID_PRODUCTGROUP FROM BUYING.MERCHANDISEPLANKEYDATA t0 WHERE (t0.ID_PLANNINGPERIOD = ? AND t0.ID_BUYINGGROUP = ? AND t0.ID_MERCHANDISEGROUP IS NULL AND t0.ID_PRODUCTGROUP = (SELECT t6.ID FROM BUYING.ITEM t1, BUYING.ITEMPLANNING t2, BUYING.STOCKPRODUCTLINE t3, BUYING.MERCHANDISEGROUP t4, BUYING.BUYINGGROUP t5, SYSP.PRODUCTGROUP t6 WHERE (t4.ID_BUYINGGROUP = ? AND 1 = 1) AND t1.ID = t2.ID_ITEM AND t1.ID_PRODUCTGROUP = t6.ID AND t2.ID_STOCKPRODUCTLINE = t3.ID AND t3.ID_MERCHANDISEGROUP = t4.ID AND t4.ID_BUYINGGROUP = t5.ID) AND t0.ID_PRODUCTGROUP IS NOT NULL) [params=(long) 82, (long) 48, (long) 48] [reused=0]} [code=1427, state=21000]
is it a known bug? something wrong with my JPQL? Any hints or workarounds?
Thanks in advance, Markus Hahn, Otto, Germany
