Derby
  1. Derby
  2. DERBY-3955

test lang/selectivity.sql can be revived

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 10.11.1.1
    • Component/s: Test
    • Labels:
      None
    • Issue & fix info:
      Newcomer

      Description

      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.

      1. DERBY-3955.diff4
        31 kB
        Myrna van Lunteren
      2. DERBY-3955.diff3
        97 kB
        Myrna van Lunteren
      3. DERBY-3955.diff2
        95 kB
        Myrna van Lunteren
      4. oldSelectivity.java
        7 kB
        Kathey Marsden
      5. derby-3955_sanityCheck_dont_commit_diff.txt
        8 kB
        Kathey Marsden
      6. DERBY-3955.diff1
        43 kB
        Myrna van Lunteren

        Issue Links

          Activity

          Hide
          Myrna van Lunteren added a comment -

          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.

          Show
          Myrna van Lunteren added a comment - 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.
          Hide
          Kathey Marsden added a comment -

          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 8000000. (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?

          Show
          Kathey Marsden added a comment - 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 8000000. (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?
          Hide
          Kathey Marsden added a comment -

          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 <indexBeforeInsert |indexAfterInsert>
          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.

          Show
          Kathey Marsden added a comment - 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 <indexBeforeInsert |indexAfterInsert> 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.
          Hide
          Mike Matrigali added a comment -

          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.

          Show
          Mike Matrigali added a comment - 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.
          Hide
          Kathey Marsden added a comment -

          Thanks Mike, that is very helpful. If I do a full scan of both tables before running the query in the case where I create the index before inserting the data, I get the expected estimated row count (at least on trunk. Haven't tried elsewhere). I agree that it makes sense for update statistics to update this store statistic as well. I'll file a Jira issue for that.

          Show
          Kathey Marsden added a comment - Thanks Mike, that is very helpful. If I do a full scan of both tables before running the query in the case where I create the index before inserting the data, I get the expected estimated row count (at least on trunk. Haven't tried elsewhere). I agree that it makes sense for update statistics to update this store statistic as well. I'll file a Jira issue for that.
          Hide
          Kathey Marsden added a comment -

          I attached an initial, partial conversion to DERBY-4116 to test that issue (derby-4116_diff.txt), which I plan to check in if it passes review and then will follow up with the rest of the conversion in follow up patches.

          Show
          Kathey Marsden added a comment - I attached an initial, partial conversion to DERBY-4116 to test that issue (derby-4116_diff.txt), which I plan to check in if it passes review and then will follow up with the rest of the conversion in follow up patches.
          Hide
          Kathey Marsden added a comment -

          Not working on this now

          Show
          Kathey Marsden added a comment - Not working on this now
          Hide
          Myrna van Lunteren added a comment -

          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.

          Show
          Myrna van Lunteren added a comment - 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.
          Hide
          Myrna van Lunteren added a comment -

          I have not committed the patch because I ran into trouble when I ran the lang._Suite.

          The behavior is that the expected row count for some of the test cases is different when run in the suite, and also, it seemed that the expanded test caused failures in the test OptimizerOverridesTest, even though all three tests use CleanDatabaseSetup.

          To overcome the failures in the OptimizerOverridesTest, I changed the name of the table used in SelectivityTest from 'test' (which was also used in OptimizerOverridesTest), to 'seltest', and that seemed to do the trick for that issue.

          I debugged the other failures to where if the test GroupByExpressionTest is run before the expanded SelectivityTest, there is a failure after the following query:
          ------------
          s.executeUpdate("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
          s.executeQuery("select template.id from " +
          "--DERBY-PROPERTIES joinOrder=fixed \n" +
          "seltest, template --DERBY-PROPERTIES joinStrategy=hash, " +
          "index=null \n" +
          "where seltest.twenty = template.twenty " +
          "and seltest.two = template.two").close();
          checkEstimatedRowCount(conn,401000.625);
          ----------
          and in 3 other places.

          I thought the difference in estimated rowcount might be dependent on the name of the table also, because GroupByExpressionsTest also used table name 'test', but even after the change of the table name to 'seltest; in the SelectivityTest, this failure still occurred. I eliminated things from the GroupByExpressionsTest until I get this error when the only thing happening in the GroupByExpressionsTest is the creation of the 3 tables 'test', 'coal', and 'AllTypes'. If any of these 3 are eliminated, the error does not occur.

          Does anyone have a suggestion of how to prevent the earlier test from affecting the estimated rowcount in the later test?

          Show
          Myrna van Lunteren added a comment - I have not committed the patch because I ran into trouble when I ran the lang._Suite. The behavior is that the expected row count for some of the test cases is different when run in the suite, and also, it seemed that the expanded test caused failures in the test OptimizerOverridesTest, even though all three tests use CleanDatabaseSetup. To overcome the failures in the OptimizerOverridesTest, I changed the name of the table used in SelectivityTest from 'test' (which was also used in OptimizerOverridesTest), to 'seltest', and that seemed to do the trick for that issue. I debugged the other failures to where if the test GroupByExpressionTest is run before the expanded SelectivityTest, there is a failure after the following query: ------------ s.executeUpdate("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)"); s.executeQuery("select template.id from " + "--DERBY-PROPERTIES joinOrder=fixed \n" + "seltest, template --DERBY-PROPERTIES joinStrategy=hash, " + "index=null \n" + "where seltest.twenty = template.twenty " + "and seltest.two = template.two").close(); checkEstimatedRowCount(conn,401000.625); ---------- and in 3 other places. I thought the difference in estimated rowcount might be dependent on the name of the table also, because GroupByExpressionsTest also used table name 'test', but even after the change of the table name to 'seltest; in the SelectivityTest, this failure still occurred. I eliminated things from the GroupByExpressionsTest until I get this error when the only thing happening in the GroupByExpressionsTest is the creation of the 3 tables 'test', 'coal', and 'AllTypes'. If any of these 3 are eliminated, the error does not occur. Does anyone have a suggestion of how to prevent the earlier test from affecting the estimated rowcount in the later test?
          Hide
          Mike Matrigali added a comment -

          In general I think checking for specific estimated row counts is a bad idea in tests, unless there is a very specific need to do so. Tests that look at plans should be looking at the shape of the plan, and the specific estimates do not matter. Check that the expected statement nodes using the expected indexes are there.

          In some cases estimates are going to be affected by background activity and may thus lead to intermittent failures when run across hardware.

          If you must check for specific estimates do everything you can to isolate your test from varying performance issues:
          o turn off the automatic background statistics gatherer (as you can't be sure when it will kick in across platforms and thus estimates might be affected by machines).
          o run the test in a different db than other tests. the row estimates in store are just estimates, and they are updated internally based on page cache events (like a page with deleted or inserted rows getting written to disk). The state of the cache may be dependent on previous tests. It may be ok to just shutdown and restart before the test, but certainly running in a different db is safest.
          o after you change the number of rows in a table run an compress on the tables. again estimates are based on cache behavior, and especially after deletes there are background jobs going on recovering the deleted space and they may perform differently on different machines. running a compress after update, delete, and or inserts will get the row estimates from store into a reproducible state. some of the code uses an internal procedure to wait for background tasks to finish, but my opinion is that we should use "supported" interfaces in tests whenever possible. If the test needs to specifically exercise the path where there are committed deleted rows in the table that have not been reclaimed it may then be necessary to use the internal call to get reproducible results. Probably most query plan tests can follow a mode where they do all the ddl and data load up front and run compress to get system to known estimate state.

          Show
          Mike Matrigali added a comment - In general I think checking for specific estimated row counts is a bad idea in tests, unless there is a very specific need to do so. Tests that look at plans should be looking at the shape of the plan, and the specific estimates do not matter. Check that the expected statement nodes using the expected indexes are there. In some cases estimates are going to be affected by background activity and may thus lead to intermittent failures when run across hardware. If you must check for specific estimates do everything you can to isolate your test from varying performance issues: o turn off the automatic background statistics gatherer (as you can't be sure when it will kick in across platforms and thus estimates might be affected by machines). o run the test in a different db than other tests. the row estimates in store are just estimates, and they are updated internally based on page cache events (like a page with deleted or inserted rows getting written to disk). The state of the cache may be dependent on previous tests. It may be ok to just shutdown and restart before the test, but certainly running in a different db is safest. o after you change the number of rows in a table run an compress on the tables. again estimates are based on cache behavior, and especially after deletes there are background jobs going on recovering the deleted space and they may perform differently on different machines. running a compress after update, delete, and or inserts will get the row estimates from store into a reproducible state. some of the code uses an internal procedure to wait for background tasks to finish, but my opinion is that we should use "supported" interfaces in tests whenever possible. If the test needs to specifically exercise the path where there are committed deleted rows in the table that have not been reclaimed it may then be necessary to use the internal call to get reproducible results. Probably most query plan tests can follow a mode where they do all the ddl and data load up front and run compress to get system to known estimate state.
          Hide
          Myrna van Lunteren added a comment -

          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.

          Show
          Myrna van Lunteren added a comment - 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.
          Hide
          ASF subversion and git services added a comment -

          Commit 1560247 from Myrna van Lunteren in branch 'code/trunk'
          [ https://svn.apache.org/r1560247 ]

          DERBY-3955; test lang/selectivity.sql can be revived
          committing patch 3 - which adds the remaining test cases from selectivity.sql to SelectivityTest.java

          Show
          ASF subversion and git services added a comment - Commit 1560247 from Myrna van Lunteren in branch 'code/trunk' [ https://svn.apache.org/r1560247 ] DERBY-3955 ; test lang/selectivity.sql can be revived committing patch 3 - which adds the remaining test cases from selectivity.sql to SelectivityTest.java
          Hide
          Myrna van Lunteren added a comment -

          It seems that this test, when run with a jdk 1.8 (pre release?) is showing a problem:
          e.g. http://download.java.net/javadesktop/derby/request_5586696/javadb-task-3791625.html
          shows the following stack trace:
          junit.framework.AssertionFailedError: expected:<4010> but was:<12913>
          at org.apache.derbyTesting.junit.BaseJDBCTestCase.checkEstimatedRowCount(BaseJDBCTestCase.java:1561)
          at org.apache.derbyTesting.functionTests.tests.lang.SelectivityTest.testStatMatcher(SelectivityTest.java:785)
          at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:118)
          at org.apache.derbyTesting.junit.BaseJDBCTestCase.runBareOverridable(BaseJDBCTestCase.java:440)
          at org.apache.derbyTesting.junit.BaseJDBCTestCase.runBare(BaseJDBCTestCase.java:457)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
          at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
          at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
          at junit.extensions.TestSetup.run(TestSetup.java:25)
          at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)

          I think this is related to the fact that the fixtures in the test are run in a different sequence, i.e.
          jdk1.8:
          -----------------
          (emb)lang.SelectivityTest.testSingleColumnSelectivity used 296 ms
          (emb)lang.SelectivityTest.testMultiPredicate used 126 ms
          (emb)lang.SelectivityTest.testTwoWayJoins used 26 ms
          (emb)lang.SelectivityTest.testStatMatcher used 254 ms
          (emb)lang.SelectivityTest.testBeetle4321 used 78 ms
          (emb)lang.SelectivityTest.testBasic used 2198 ms
          (emb)lang.SelectivityTest.testThreeWayJoins used 758 ms
          (emb)lang.SelectivityTest.testScratch used 115 ms
          -----------------
          my ibm 1.7 run:
          -----------------
          (emb)lang.SelectivityTest.testSingleColumn used 323 ms .
          (emb)lang.SelectivityTest.testMultiPredicate used 184 ms .
          (emb)lang.SelectivityTest.testTwoWayJoins used 27 ms .
          (emb)lang.SelectivityTest.testThreeWayJoins used 608 ms .
          (emb)lang.SelectivityTest.testScratch used 145 ms .
          (emb)lang.SelectivityTest.testStatMatcher used 114 ms .
          (emb)lang.SelectivityTest.testBeetle4321 used 76 ms .
          (emb)lang.SelectivityTest.testBasic used 2830 ms
          -----------------
          These fixtures should be independent of eachother, I'll look into this.

          Show
          Myrna van Lunteren added a comment - It seems that this test, when run with a jdk 1.8 (pre release?) is showing a problem: e.g. http://download.java.net/javadesktop/derby/request_5586696/javadb-task-3791625.html shows the following stack trace: junit.framework.AssertionFailedError: expected:<4010> but was:<12913> at org.apache.derbyTesting.junit.BaseJDBCTestCase.checkEstimatedRowCount(BaseJDBCTestCase.java:1561) at org.apache.derbyTesting.functionTests.tests.lang.SelectivityTest.testStatMatcher(SelectivityTest.java:785) at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:118) at org.apache.derbyTesting.junit.BaseJDBCTestCase.runBareOverridable(BaseJDBCTestCase.java:440) at org.apache.derbyTesting.junit.BaseJDBCTestCase.runBare(BaseJDBCTestCase.java:457) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57) at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24) at junit.extensions.TestSetup$1.protect(TestSetup.java:21) at junit.extensions.TestSetup.run(TestSetup.java:25) at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57) I think this is related to the fact that the fixtures in the test are run in a different sequence, i.e. jdk1.8: ----------------- (emb)lang.SelectivityTest.testSingleColumnSelectivity used 296 ms (emb)lang.SelectivityTest.testMultiPredicate used 126 ms (emb)lang.SelectivityTest.testTwoWayJoins used 26 ms (emb)lang.SelectivityTest.testStatMatcher used 254 ms (emb)lang.SelectivityTest.testBeetle4321 used 78 ms (emb)lang.SelectivityTest.testBasic used 2198 ms (emb)lang.SelectivityTest.testThreeWayJoins used 758 ms (emb)lang.SelectivityTest.testScratch used 115 ms ----------------- my ibm 1.7 run: ----------------- (emb)lang.SelectivityTest.testSingleColumn used 323 ms . (emb)lang.SelectivityTest.testMultiPredicate used 184 ms . (emb)lang.SelectivityTest.testTwoWayJoins used 27 ms . (emb)lang.SelectivityTest.testThreeWayJoins used 608 ms . (emb)lang.SelectivityTest.testScratch used 145 ms . (emb)lang.SelectivityTest.testStatMatcher used 114 ms . (emb)lang.SelectivityTest.testBeetle4321 used 76 ms . (emb)lang.SelectivityTest.testBasic used 2830 ms ----------------- These fixtures should be independent of eachother, I'll look into this.
          Hide
          ASF subversion and git services added a comment -

          Commit 1561859 from Myrna van Lunteren in branch 'code/trunk'
          [ https://svn.apache.org/r1561859 ]

          DERBY-3955; test lang/selectivity.sql can be revived
          fixing an issue that showed up when the sequence of fixtures was different

          Show
          ASF subversion and git services added a comment - Commit 1561859 from Myrna van Lunteren in branch 'code/trunk' [ https://svn.apache.org/r1561859 ] DERBY-3955 ; test lang/selectivity.sql can be revived fixing an issue that showed up when the sequence of fixtures was different
          Hide
          Myrna van Lunteren added a comment -

          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.

          Show
          Myrna van Lunteren added a comment - 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.
          Hide
          Myrna van Lunteren added a comment -

          oops, attached a bad version, it had a debugging method left in.

          Show
          Myrna van Lunteren added a comment - oops, attached a bad version, it had a debugging method left in.
          Hide
          Myrna van Lunteren added a comment - - edited

          I committed the (new) patch DERBY-3955.diff4 with revision 1561949 http://svn.apache.org/r1561949 - (but typed DERBy-3955, so the svngitjira tool didn't pick it up). Fixed the text now.

          Show
          Myrna van Lunteren added a comment - - edited I committed the (new) patch DERBY-3955 .diff4 with revision 1561949 http://svn.apache.org/r1561949 - (but typed DERBy-3955, so the svngitjira tool didn't pick it up). Fixed the text now.
          Hide
          Myrna van Lunteren added a comment -

          It looks as if the instability has been addressed now, the latest test runs at Oracle seem clear, and where reasonable checks to see if the optimizer picks expected paths have been added. I do not intend to add to this test at this time, so resolving.

          Show
          Myrna van Lunteren added a comment - It looks as if the instability has been addressed now, the latest test runs at Oracle seem clear, and where reasonable checks to see if the optimizer picks expected paths have been added. I do not intend to add to this test at this time, so resolving.
          Hide
          Myrna van Lunteren added a comment -

          close all resolved issues with myself as reporter and assignee.

          Show
          Myrna van Lunteren added a comment - close all resolved issues with myself as reporter and assignee.

            People

            • Assignee:
              Myrna van Lunteren
              Reporter:
              Myrna van Lunteren
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development