Thanks for taking the time to respond Mamta. I am running a patched version of 10.9.1.0. The patches have to do with issues that I provided patches for to Derby relating to LogicalConnection deadlocks. These patches were just incorporated as I saw emails indicating so just the other day.
I have verified that I cannot reproduce this problem using 10.8.2.2. release. I exported the data from my production database and created a new database using 10.8.2.2 release and do not see the problem. I am trying to get this into a readily reproducible issue so that if I make a change I can verify it works or not because of the change. According to the jira https://issues.apache.org/jira/browse/DERBY-6045 It says the fix is in 10.8.3.1, 10.9.2.2, 10.10.1.3 and 10.11.0.0. Since my production database was hard upgraded to 10.9.1.0, I have no possibility of going backwards. I know how to build Derby (from the trunk) so I would like to build Derby 10.9.2.2 but don't know the proper way to get the source for that revision. Any help here will be greatly appreciated as well. One not, when I do not see the problem, the optimizer plan indicates that the estimated row count is 0.0 with a cost > 0.0 for both tables. When I do see the problem, the optimizer plan indicates that the estimated row count is 0.0 with a cost of 0.0 which seems incorrect. Note that I have used the system procedure to drop that database statistics and recreated them to no avail. I am in the process of trying this with the derby.storage.indexStats.debug.keepDisposableStats=true option as well and will provide follow up. On Aug 23, 2013, at 1:10 PM, Mamta Satoor <[email protected]<mailto:[email protected]>> wrote: Hi Brett, We have encountered and fixed at least one bug in case of single-column unique index, where starting 10.9 we have decided not to keep stats for such indexes. The bug fixed is DERBY-6045 and it is in Derby 10.10.1.1(revision 1480153). I was wondering if you could share java org.apache.derby.tools.sysinfo so we know if you have that fix. If you do have that fix, then it is possible (though I am not certain) that there may be other bug in this area that you are running into. One way to figure that out is to use the property derby.storage.indexStats.debug.keepDisposableStats=true. This property will force Derby to collect the stats for single-column unique index. So, if you can recreate your db with this property and run the query, we can check the query plan to see if it starts using the single-column unique index. It might be possible to use existing db with this property and recreate the stats and try the query if creating the db from scratch with this property is a time consuming process. thanks, Mamta On Wed, Aug 21, 2013 at 4:55 PM, Bryan Pendleton <[email protected]<mailto:[email protected]>> wrote: Any thoughts or comments on why this the optimizer chooses on over the other? Unfortunately, no. But there have been concerns about the optimizer's cost analysis for years; see https://issues.apache.org/jira/browse/DERBY-1905 and some of the other issues linked from it (DERBY-1259, DERBY-1260). So, while I know it's frustrating for you to be wrestling with an optimizer that won't behave, it's also good news, for Derby, that you've made such good progress in trying to understand this peculiar behavior. Perhaps when we get to the bottom of the strange behavior you're seeing, we'll have a better understanding of some of the other strange optimizer behaviors we've seen in the past. thanks, bryan
