|
I've compared with 10.2.2.0 and the problem does NOT appear in that version. Cant really say when the problem started to appear.
I attached three files to this issue that shows the query plan for: 1. Query with no constraint and with disabled condition (noconst_nocond.txt). Query result is correct. 2. Query with constraint and disabled condition (withconst_nocond.txt). Query result is empty and incorrect. 3. Query with constraint and enabled condition (withconst_withcond.txt). Query result is correct. The query plans from (1) and (3) are very similar except from the operation being considered in (3). The query plans differ quite much between (1) and (2) but I'm not really confident in the query plan format so I'll leave the analysis to you. What I can see is that optimizer estimation is a bit off the track in most places and that an index scan on PROJECT_PK results in zero rows in (2). You can easily compare the files using meld or some other text-diff tool. Hope this helps. Constraint disabled. No prj.other condition.
Constraint enabled. No prj.other condition.
Constraint enabled. With extra condition prj.other=100
Looks like Bryan's second guess is correct. The behaviour changed in revision 516454 (
It appears that this bug is fixed by 711615 (
can not reproduce it after that change, so resolving. That is not unreasonable, since that bug also fixes a regression from Lars, if you are able to build Derby from the trunk of the 10.4 branch to verify that the problem is gone, that would be great! Related or duplicated issue; seems solved by fix to
I verified that the patch from
so marking fixed in all those. |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
have to do with subquery flattening or group by re-writing. But that's just a wild guess,
I haven't studied the problem in any depth.
Some questions:
- Have you tried this script with other versions of Derby to see if the behavior varies?
- Can you collect the query plans for the various combinations (constraint/no-constraint,
prj.other=100/no-prj.other=100), and perhaps do some initial comparisons to see
how the query plans differ? To do this, you'll want to set derby.language.logQueryPlan=true,
as described here: http://db.apache.org/derby/docs/dev/tuning/ctunsetprop34818.html