Derby
  1. Derby
  2. DERBY-4457

'Column value mismatch' in 'testDistinctInsertWithGeneratedColumn(...lang.DistinctTest)' on Jvm 1.5, 1.4, phoneME.

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 10.6.1.0
    • Fix Version/s: 10.5.3.1, 10.6.1.0
    • Component/s: Test
    • Labels:
      None
    • Environment:
    • Bug behavior facts:
      Regression Test Failure

      Description

      See e.g. http://dbtg.foundry.sun.com/derby/test/Daily/jvm1.5/testing/testlog/sol/885498-suitesAll_diff.txt :

      1) testDistinctInsertWithGeneratedColumn(org.apache.derbyTesting.functionTests.tests.lang.DistinctTest)junit.framework.AssertionFailedError: Column value mismatch @ column 'C12', row 1:
      Expected: >1<
      Found: >2<
      at org.apache.derbyTesting.junit.JDBC.assertRowInResultSet(JDBC.java:1081)
      at org.apache.derbyTesting.junit.JDBC.assertRowInResultSet(JDBC.java:993)
      at org.apache.derbyTesting.junit.JDBC.assertFullResultSet(JDBC.java:881)
      at org.apache.derbyTesting.junit.JDBC.assertFullResultSet(JDBC.java:819)
      at org.apache.derbyTesting.junit.JDBC.assertFullResultSet(JDBC.java:777)
      at org.apache.derbyTesting.functionTests.tests.lang.DistinctTest.testDistinctInsertWithGeneratedColumn(DistinctTest.java:462)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:109)
      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 junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
      at junit.extensions.TestSetup$1.protect(TestSetup.java:21)
      at junit.extensions.TestSetup.run(TestSetup.java:25)

      1. d4457.diff
        1 kB
        Knut Anders Hatlen

        Issue Links

          Activity

          Hide
          Knut Anders Hatlen added a comment -

          This failure was introduced by DERBY-4442, and the root cause is that the test relies on a specific implicit ordering of the results that's not guaranteed.

          In short, the following sequence of statements (simplified test case)

          create table source (x int);
          insert into source values 1,2;
          create table dest (x int, y int generated always as identity);
          insert into dest select distinct x from source;

          used to result in the table DEST containing the rows (1,1) and (2,2) regardless of JVM.

          After DERBY-4442, it contains (1,1), (2,2) with Sun's JVM version 1.6 or later, whereas earlier JVM versions produce a table that contains the rows (2,1) and (1,2).

          Even without the DERBY-4442 patch, one will see that "SELECT DISTINCT X FROM SOURCE" gives different results on different JVMs. But before DERBY-4442, a SELECT DISTINCT that acted as a source for an INSERT statement would go through a sort if the target table contained an identity column, so the rows would happen to be inserted in sorted order. After DERBY-4442, a sort is no longer needed, and the rows are inserted in the same order by which they returned from the DistinctScanResultSet.

          Since the insertion order is not guaranteed unless there's an ORDER BY clause (which is not accepted syntax until DERBY-4 is fixed), the difference in the results depending on JVM is OK, and the test should be changed to be independent of the JVM's hash implementation.

          Show
          Knut Anders Hatlen added a comment - This failure was introduced by DERBY-4442 , and the root cause is that the test relies on a specific implicit ordering of the results that's not guaranteed. In short, the following sequence of statements (simplified test case) create table source (x int); insert into source values 1,2; create table dest (x int, y int generated always as identity); insert into dest select distinct x from source; used to result in the table DEST containing the rows (1,1) and (2,2) regardless of JVM. After DERBY-4442 , it contains (1,1), (2,2) with Sun's JVM version 1.6 or later, whereas earlier JVM versions produce a table that contains the rows (2,1) and (1,2). Even without the DERBY-4442 patch, one will see that "SELECT DISTINCT X FROM SOURCE" gives different results on different JVMs. But before DERBY-4442 , a SELECT DISTINCT that acted as a source for an INSERT statement would go through a sort if the target table contained an identity column, so the rows would happen to be inserted in sorted order. After DERBY-4442 , a sort is no longer needed, and the rows are inserted in the same order by which they returned from the DistinctScanResultSet. Since the insertion order is not guaranteed unless there's an ORDER BY clause (which is not accepted syntax until DERBY-4 is fixed), the difference in the results depending on JVM is OK, and the test should be changed to be independent of the JVM's hash implementation.
          Hide
          Knut Anders Hatlen added a comment -

          The attached patch makes the test more robust against variations in the ordering of the results.

          Show
          Knut Anders Hatlen added a comment - The attached patch makes the test more robust against variations in the ordering of the results.
          Hide
          Knut Anders Hatlen added a comment -

          Committed revision 885726.

          Show
          Knut Anders Hatlen added a comment - Committed revision 885726.
          Hide
          Bryan Pendleton added a comment -

          Does this indicate the need for a release note on DERBY-4442, to warn users about the possible behavior change?

          Show
          Bryan Pendleton added a comment - Does this indicate the need for a release note on DERBY-4442 , to warn users about the possible behavior change?
          Hide
          Knut Anders Hatlen added a comment -

          Since the ordering is unspecified, and it could vary depending on available indexes and which plan was chosen even before DERBY-4442, I don't think a release note is needed. Here's an example with 10.5.3.0 showing that different plans could cause different ordering with this kind of query earlier as well:

          ij> create table dest(x int, y int generated always as identity);
          0 rows inserted/updated/deleted
          ij> insert into dest select distinct x from source;
          5 rows inserted/updated/deleted
          ij> select * from dest;
          X |Y
          -----------------------
          1 |1
          2 |2
          3 |3
          4 |4
          5 |5

          5 rows selected
          ij> delete from dest;
          5 rows inserted/updated/deleted
          ij> create index idx on source(x desc);
          0 rows inserted/updated/deleted
          ij> insert into dest select distinct x from source;
          5 rows inserted/updated/deleted
          ij> select * from dest;
          X |Y
          -----------------------
          1 |10
          2 |9
          3 |8
          4 |7
          5 |6

          5 rows selected

          Show
          Knut Anders Hatlen added a comment - Since the ordering is unspecified, and it could vary depending on available indexes and which plan was chosen even before DERBY-4442 , I don't think a release note is needed. Here's an example with 10.5.3.0 showing that different plans could cause different ordering with this kind of query earlier as well: ij> create table dest(x int, y int generated always as identity); 0 rows inserted/updated/deleted ij> insert into dest select distinct x from source; 5 rows inserted/updated/deleted ij> select * from dest; X |Y ----------------------- 1 |1 2 |2 3 |3 4 |4 5 |5 5 rows selected ij> delete from dest; 5 rows inserted/updated/deleted ij> create index idx on source(x desc); 0 rows inserted/updated/deleted ij> insert into dest select distinct x from source; 5 rows inserted/updated/deleted ij> select * from dest; X |Y ----------------------- 1 |10 2 |9 3 |8 4 |7 5 |6 5 rows selected
          Hide
          Mike Matrigali added a comment -

          reopening issue to backport it to 10.5 to fix some nightly diffs showing up against ibm jvm's after recent backports.

          Show
          Mike Matrigali added a comment - reopening issue to backport it to 10.5 to fix some nightly diffs showing up against ibm jvm's after recent backports.
          Hide
          Mike Matrigali added a comment -

          backported fix #885726 from trunk to 10.5, resolving as fixed, and resetting original owner.

          s105_ibm16:28>svn commit

          Sending java\testing\org\apache\derbyTesting\functionTests\test\lang\DistinctTest.java
          Transmitting file data .
          Committed revision 962152.

          Show
          Mike Matrigali added a comment - backported fix #885726 from trunk to 10.5, resolving as fixed, and resetting original owner. s105_ibm16:28>svn commit Sending java\testing\org\apache\derbyTesting\functionTests\test\lang\DistinctTest.java Transmitting file data . Committed revision 962152.
          Hide
          Knut Anders Hatlen added a comment -

          [bulk update] Close all resolved issues that haven't been updated for more than one year.

          Show
          Knut Anders Hatlen added a comment - [bulk update] Close all resolved issues that haven't been updated for more than one year.

            People

            • Assignee:
              Knut Anders Hatlen
              Reporter:
              Ole Solberg
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development