[jira] [Updated] (DERBY-3955) test lang/selectivity.sql can be revived
[ https://issues.apache.org/jira/browse/DERBY-3955?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Myrna van Lunteren updated DERBY-3955: -- Attachment: DERBY-3955.diff4 oops, attached a bad version, it had a debugging method left in. > test lang/selectivity.sql can be revived > > > Key: DERBY-3955 > URL: https://issues.apache.org/jira/browse/DERBY-3955 > Project: Derby > Issue Type: Improvement > Components: Test >Reporter: Myrna van Lunteren >Assignee: Myrna van Lunteren >Priority: Minor > Attachments: DERBY-3955.diff1, DERBY-3955.diff2, DERBY-3955.diff3, > DERBY-3955.diff4, derby-3955_sanityCheck_dont_commit_diff.txt, > oldSelectivity.java > > > One test that was contributed during the IBM contribution of derby code was > not runnable at the time - lang/selectivity.sql. > This test is still there, and I think might have some valuable regression > tests if it gets adapted to the current functionality in Derby. > Ideally too, it should get converted to junit, or at least be made to run > through the langScripts junit suite. -- This message was sent by Atlassian JIRA (v6.1.5#6160)
[jira] [Updated] (DERBY-3955) test lang/selectivity.sql can be revived
[ https://issues.apache.org/jira/browse/DERBY-3955?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Myrna van Lunteren updated DERBY-3955: -- Attachment: (was: DERBY-3955.diff4) > test lang/selectivity.sql can be revived > > > Key: DERBY-3955 > URL: https://issues.apache.org/jira/browse/DERBY-3955 > Project: Derby > Issue Type: Improvement > Components: Test >Reporter: Myrna van Lunteren >Assignee: Myrna van Lunteren >Priority: Minor > Attachments: DERBY-3955.diff1, DERBY-3955.diff2, DERBY-3955.diff3, > derby-3955_sanityCheck_dont_commit_diff.txt, oldSelectivity.java > > > One test that was contributed during the IBM contribution of derby code was > not runnable at the time - lang/selectivity.sql. > This test is still there, and I think might have some valuable regression > tests if it gets adapted to the current functionality in Derby. > Ideally too, it should get converted to junit, or at least be made to run > through the langScripts junit suite. -- This message was sent by Atlassian JIRA (v6.1.5#6160)
[jira] [Updated] (DERBY-3955) test lang/selectivity.sql can be revived
[ https://issues.apache.org/jira/browse/DERBY-3955?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Myrna van Lunteren updated DERBY-3955: -- Attachment: DERBY-3955.diff4 Attaching a patch which attempts to address further instability in the SelectivityTest by adding update statistics calls to each fixture before doing any tests; hopefully that will ensure this works correctly for any jvm. It works with Oracle prerelease u5 and my ibm 17 sr6... This patch is bigger though because it's also adding number of other run-time statistics text checks. I will commit this shortly. > test lang/selectivity.sql can be revived > > > Key: DERBY-3955 > URL: https://issues.apache.org/jira/browse/DERBY-3955 > Project: Derby > Issue Type: Improvement > Components: Test >Reporter: Myrna van Lunteren >Assignee: Myrna van Lunteren >Priority: Minor > Attachments: DERBY-3955.diff1, DERBY-3955.diff2, DERBY-3955.diff3, > DERBY-3955.diff4, derby-3955_sanityCheck_dont_commit_diff.txt, > oldSelectivity.java > > > One test that was contributed during the IBM contribution of derby code was > not runnable at the time - lang/selectivity.sql. > This test is still there, and I think might have some valuable regression > tests if it gets adapted to the current functionality in Derby. > Ideally too, it should get converted to junit, or at least be made to run > through the langScripts junit suite. -- This message was sent by Atlassian JIRA (v6.1.5#6160)
[jira] [Updated] (DERBY-3955) test lang/selectivity.sql can be revived
[ https://issues.apache.org/jira/browse/DERBY-3955?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Myrna van Lunteren updated DERBY-3955: -- Attachment: DERBY-3955.diff3 Thanks Mike for the response. I did not make the test create a new database, but I did make the following changes: - wrap the test in a DatabasePropertySetup decorator which a. sets derby.storage.indexStats.auto to "false" b. sets derby.language.statementCacheSize to "0" and c. bounces the database This got rid of the impact from the previous test, and also of this test on other tests. However there then were a few places where the estimated rowcount was different from before, and this was fixed by: - adding more update_statistics calls I think certainly in a number of these cases the estimated rowcount is indeed the purpose of the test, and *not* whether the expected path is taken; the test verifies that the optimizer calculates the same estimated rowcount with a number of different optimizer overrides and slightly different query parameters; and there seem to be very specific calculations about the expected rowcounts in the comments. So, while I appreciate what you were saying about the sensitivity for platform differences I left this as is... If it turns out that this is indeed too fragile to maintain, then we can revisit. If desired, we can add checks to ensure the correct paths are taken, using the org.apache.derby.junit.RuntimeStatisticsParser. I'll commit this shortly, suites.All passed on my windows 7 system with this patch. > test lang/selectivity.sql can be revived > > > Key: DERBY-3955 > URL: https://issues.apache.org/jira/browse/DERBY-3955 > Project: Derby > Issue Type: Improvement > Components: Test >Reporter: Myrna van Lunteren >Priority: Minor > Attachments: DERBY-3955.diff1, DERBY-3955.diff2, DERBY-3955.diff3, > derby-3955_sanityCheck_dont_commit_diff.txt, oldSelectivity.java > > > One test that was contributed during the IBM contribution of derby code was > not runnable at the time - lang/selectivity.sql. > This test is still there, and I think might have some valuable regression > tests if it gets adapted to the current functionality in Derby. > Ideally too, it should get converted to junit, or at least be made to run > through the langScripts junit suite. -- This message was sent by Atlassian JIRA (v6.1.5#6160)
[jira] [Updated] (DERBY-3955) test lang/selectivity.sql can be revived
[ https://issues.apache.org/jira/browse/DERBY-3955?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Myrna van Lunteren updated DERBY-3955: -- Attachment: DERBY-3955.diff2 Attaching a patch which adds the rest of selectivity.sql to the SelectivityTest. Except for the section with t5, which in the old Cloudscape incarnation tested stored prepared statements, which are not supported in Derby, and without this, what remains of the test case does not test anything new. This patch touches the following files: D java\testing\org\apache\derbyTesting\functionTests\tests\lang\selectivity_derby.properties M java\testing\org\apache\derbyTesting\functionTests\tests\lang\SelectivityTest.java D java\testing\org\apache\derbyTesting\functionTests\tests\lang\selectivity.sql One thing to note - the original test was using selectivity_derby.properties, which had the following property: derby.language.statementCacheSize=0 It seems to me that the results without the property were reasonable, and I don't see that this would contribute to the test, so I did not try to preserve it in the junit test. > test lang/selectivity.sql can be revived > > > Key: DERBY-3955 > URL: https://issues.apache.org/jira/browse/DERBY-3955 > Project: Derby > Issue Type: Improvement > Components: Test >Reporter: Myrna van Lunteren >Priority: Minor > Attachments: DERBY-3955.diff1, DERBY-3955.diff2, > derby-3955_sanityCheck_dont_commit_diff.txt, oldSelectivity.java > > > One test that was contributed during the IBM contribution of derby code was > not runnable at the time - lang/selectivity.sql. > This test is still there, and I think might have some valuable regression > tests if it gets adapted to the current functionality in Derby. > Ideally too, it should get converted to junit, or at least be made to run > through the langScripts junit suite. -- This message was sent by Atlassian JIRA (v6.1.5#6160)
[jira] Updated: (DERBY-3955) test lang/selectivity.sql can be revived
[ https://issues.apache.org/jira/browse/DERBY-3955?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Dag H. Wanvik updated DERBY-3955: - Issue Type: Improvement (was: Test) > test lang/selectivity.sql can be revived > > > Key: DERBY-3955 > URL: https://issues.apache.org/jira/browse/DERBY-3955 > Project: Derby > Issue Type: Improvement > Components: Test >Reporter: Myrna van Lunteren >Assignee: Kathey Marsden >Priority: Minor > Attachments: DERBY-3955.diff1, > derby-3955_sanityCheck_dont_commit_diff.txt, oldSelectivity.java > > > One test that was contributed during the IBM contribution of derby code was > not runnable at the time - lang/selectivity.sql. > This test is still there, and I think might have some valuable regression > tests if it gets adapted to the current functionality in Derby. > Ideally too, it should get converted to junit, or at least be made to run > through the langScripts junit suite. -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-3955) test lang/selectivity.sql can be revived
[ https://issues.apache.org/jira/browse/DERBY-3955?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Mike Matrigali updated DERBY-3955: -- I am not sure what is going on here, but thought I would bring up the store part of this equation -- which might make sense of your test cases. Store maintains an "estimated" row count of a table, it does not guarantee that it matches the exact count of rows in the table and often it does not. This count is updated either when the store thinks the number has been changed by 10% or when a dirty page leaves the cache. So the number can vary depending on what state the cache is in before the test is run. Note the counts I am talking about are the raw counts on the individual tables, where the counts you are looking at come from doing additional math on those numbers - that math is in the optimizer. There are some points in the code where the sql layer of the system "knows" that it has just done a scan of every row in the table so it knows the exact count of the table and it updates the store's estimate through a store interface. I think this is triggered by a full index or base table scan. But would be reasonable to do a create index and update statistic time also. I also believe creating an index after data is added to the table does the same thing as what update statistics does, if there is only one index on the table. The cardinality statistics are only gathered on existing indexes. > test lang/selectivity.sql can be revived > > > Key: DERBY-3955 > URL: https://issues.apache.org/jira/browse/DERBY-3955 > Project: Derby > Issue Type: Test > Components: Test >Reporter: Myrna van Lunteren >Assignee: Kathey Marsden >Priority: Minor > Attachments: DERBY-3955.diff1, > derby-3955_sanityCheck_dont_commit_diff.txt, oldSelectivity.java > > > One test that was contributed during the IBM contribution of derby code was > not runnable at the time - lang/selectivity.sql. > This test is still there, and I think might have some valuable regression > tests if it gets adapted to the current functionality in Derby. > Ideally too, it should get converted to junit, or at least be made to run > through the langScripts junit suite. -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-3955) test lang/selectivity.sql can be revived
[ https://issues.apache.org/jira/browse/DERBY-3955?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Kathey Marsden updated DERBY-3955: -- Attachment: oldSelectivity.java I wrote this java program (oldSelectivity.java) so I could do some testing on old branches and discovered something interesting.Our estimated row count is different if we make the index before or after the insert, even if we update statistics. The usage is usage: java oldSelectivity you have to delete the database wombat each time you run. On trunk/10.5 where update statistics is run we get: [C:/kmarsden/repro/derby-3955] java oldSelectivity indexBeforeInsert FAIL: expected row count:8020012.5 actual:1648057.5 [C:/kmarsden/repro/derby-3955] rm -rf wombat [C:/kmarsden/repro/derby-3955] java oldSelectivity indexAfterInsert PASS: got expected row count8020012.5 On 10.4/10.3 we get the same numbers but update statistics is not run. [C:/kmarsden/repro/derby-3955] java oldSelectivity indexBeforeInsert Couldn't execute update statistics:'SYSCS_UTIL.SYSCS_UPDATE_STATISTICS' is not recognized as a function or procedure. FAIL: expected row count:8020012.5 actual:1648057.5 [C:/kmarsden/repro/derby-3955] rm -rf wombat [C:/kmarsden/repro/derby-3955] java oldSelectivity indexAfterInsert Couldn't execute update statistics:'SYSCS_UTIL.SYSCS_UPDATE_STATISTICS' is not recognized as a function or procedure. PASS: got expected row count8020012.5 For 10.1 we get a different number if the index is created before the insert [C:/kmarsden/repro/derby-3955] export CLASSPATH=".;$CLASSPATH" [C:/kmarsden/repro/derby-3955] java oldSelectivity indexBeforeInsert Couldn't execute update statistics:'SYSCS_UTIL.SYSCS_UPDATE_STATISTICS' is not recognized as a function or procedure. FAIL: expected row count:8020012.5 actual:329611.5 [C:/kmarsden/repro/derby-3955] java oldSelectivity indexAfterInsert Couldn't execute update statistics:'SYSCS_UTIL.SYSCS_UPDATE_STATISTICS' is not recognized as a function or procedure. PASS: got expected row count8020012.5 So summary is that we seem to get what the test expects if we make the index after the data is inserted, but get sundry values otherwise. Regardless of what the right value is, I think at least for 10.5 and trunk we should get the same value if we updated statistics. > test lang/selectivity.sql can be revived > > > Key: DERBY-3955 > URL: https://issues.apache.org/jira/browse/DERBY-3955 > Project: Derby > Issue Type: Test > Components: Test >Reporter: Myrna van Lunteren >Assignee: Kathey Marsden >Priority: Minor > Attachments: DERBY-3955.diff1, > derby-3955_sanityCheck_dont_commit_diff.txt, oldSelectivity.java > > > One test that was contributed during the IBM contribution of derby code was > not runnable at the time - lang/selectivity.sql. > This test is still there, and I think might have some valuable regression > tests if it gets adapted to the current functionality in Derby. > Ideally too, it should get converted to junit, or at least be made to run > through the langScripts junit suite. -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-3955) test lang/selectivity.sql can be revived
[
https://issues.apache.org/jira/browse/DERBY-3955?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Kathey Marsden updated DERBY-3955:
--
Attachment: derby-3955_sanityCheck_dont_commit_diff.txt
I started converting this test and ran into a couple issues that I wanted to
ask about. I am attaching what I have done so far as
derby-3955_sanityCheck_dont_commit_diff.txt
1) We don't seem to print estimatedRowCount() with runtime statistics output.
I had to use internal (but public) interfaces to get the estimated rowcount. Is
this ok for a functional test? Please sanity check the patch to confirm the
approach is ok.
2) On the first test we need to check the estimated row count. In the old sql
test, we have:
ij> -- choose whatever plan you want but the row estimate should be.
-- (n * n) * 0.5
get cursor c as
'select template.id
from properties joinOrder=fixed test, template
where test.two = template.two';
ij> close c;
For Derby that's
s.executeQuery("select template.id from --DERBY-PROPERTIES joinOrder=fixed\n"
+ "test, template where test.two = template.two").close();
Since tables test and template each have 4000 rows, according to the comment, I
would think the estimatedRowCount should be about 800. (In Cloudscape
5.1.60 it is actually 8020012 which is close enough I guess.) in Derby 10.5,
it is 1648057, which seems off from the comment. I can't easily test with
older Derby releases because I don't have optimizer directives or update
statistics. Is the new estimated row count an expected change or a bug?
> test lang/selectivity.sql can be revived
>
>
> Key: DERBY-3955
> URL: https://issues.apache.org/jira/browse/DERBY-3955
> Project: Derby
> Issue Type: Test
> Components: Test
>Reporter: Myrna van Lunteren
>Assignee: Kathey Marsden
>Priority: Minor
> Attachments: DERBY-3955.diff1,
> derby-3955_sanityCheck_dont_commit_diff.txt
>
>
> One test that was contributed during the IBM contribution of derby code was
> not runnable at the time - lang/selectivity.sql.
> This test is still there, and I think might have some valuable regression
> tests if it gets adapted to the current functionality in Derby.
> Ideally too, it should get converted to junit, or at least be made to run
> through the langScripts junit suite.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-3955) test lang/selectivity.sql can be revived
[ https://issues.apache.org/jira/browse/DERBY-3955?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Myrna van Lunteren updated DERBY-3955: -- Derby Categories: [Newcomer] > test lang/selectivity.sql can be revived > > > Key: DERBY-3955 > URL: https://issues.apache.org/jira/browse/DERBY-3955 > Project: Derby > Issue Type: Test > Components: Test >Reporter: Myrna van Lunteren >Priority: Minor > Attachments: DERBY-3955.diff1 > > > One test that was contributed during the IBM contribution of derby code was > not runnable at the time - lang/selectivity.sql. > This test is still there, and I think might have some valuable regression > tests if it gets adapted to the current functionality in Derby. > Ideally too, it should get converted to junit, or at least be made to run > through the langScripts junit suite. -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-3955) test lang/selectivity.sql can be revived
[ https://issues.apache.org/jira/browse/DERBY-3955?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Myrna van Lunteren updated DERBY-3955: -- Attachment: DERBY-3955.diff1 Attaching a first cut at modifying the test so it runs a bit. Still needs more work with regards to figuring out what is the correct/expected runtime statistics output. > test lang/selectivity.sql can be revived > > > Key: DERBY-3955 > URL: https://issues.apache.org/jira/browse/DERBY-3955 > Project: Derby > Issue Type: Test > Components: Test >Reporter: Myrna van Lunteren >Priority: Minor > Attachments: DERBY-3955.diff1 > > > One test that was contributed during the IBM contribution of derby code was > not runnable at the time - lang/selectivity.sql. > This test is still there, and I think might have some valuable regression > tests if it gets adapted to the current functionality in Derby. > Ideally too, it should get converted to junit, or at least be made to run > through the langScripts junit suite. -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.
