Jim Reynolds <[email protected]> writes: > This query seems to go into an infinite loop. It runs for hours but never > completes. The sub query runs in a few milliseconds when is not a sub query. > > Is this a bug or have I missed something fundamental. > > SELECT BFNAME FROM BFTABLE > > WHERE BFNUMBER IN > > ( SELECT BFNUMBER > > FROM BFDETAILTABLE > > GROUP BY BFNUMBER > > HAVING COUNT(*) > 1 > > )
Hi Jim, The query unfortunately doesn't satisfy the conditions[1][2] for being flattened into a join, so I think Derby will need to look at every row in BFTABLE and compare it to the results from the subquery. It shouldn't go into an infinite loop, but it could take quite a while if BFTABLE is large. [1] http://db.apache.org/derby/docs/10.5/tuning/ctuntransform36368.html [2] http://db.apache.org/derby/docs/10.5/tuning/ctuntransform25868.html Derby may be able to do something cleverer with this equivalent query: SELECT BFNAME FROM BFTABLE JOIN ( SELECT BFNUMBER FROM BFDETAILTABLE GROUP BY BFNUMBER HAVING COUNT(*) > 1 ) DT ON BFTABLE.BFNUMBER = DT.BFNUMBER; -- Knut Anders
