Details

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

      Description

      Conversion of the outerjoin test into JUnit

      1. OuterJoinTest.diff
        192 kB
        Eranda Sooriyabandara
      2. OuterJoinTest.diff
        193 kB
        Eranda Sooriyabandara
      3. ASF.LICENSE.NOT.GRANTED--OuterJoinTest.diff
        184 kB
        Eranda Sooriyabandara
      4. ASF.LICENSE.NOT.GRANTED--OuterJoinTest.diff
        190 kB
        Eranda Sooriyabandara

        Activity

        Hide
        Eranda Sooriyabandara added a comment -

        Hi Bryan,

        I faced a problem with fix the error in convented code of,

        ij> select max(name), max(resdate) from inventory join timeslots on
        inventory.capacity is not null

        left outer join reservations on inventory.itemno = reservations.itemno and
        reservations.slot = timeslots.slot;

        1

        2

        ---------------------------------------------------------------------------------------------------------------

        Yang

        2000-07-13

        WARNING 01003: Null values were eliminated from the argument of a column
        function.

        Because the converted code by the DERBY-2151 tool for the "warning
        message(WARNING
        01003)" was,

        if (usingEmbedded())

        { if ((sqlWarn == null) && (st != null)) sqlWarn = st.getWarnings(); if (sqlWarn == null) sqlWarn = getConnection().getWarnings(); assertNotNull("Expected warning but found none", sqlWarn); assertSQLState("01003", sqlWarn); sqlWarn = null; }

        which give me a error in "assertNotNull("Expected warning but found none",
        sqlWarn);" saying that no warning was found.

        I thought thats because of the statement returns result set and the warning
        message must associates with the result set. I change the statement(st) with
        the resultset(rs). But it give me the error message "Operation getWarning
        not permitted. Verify that autocommit is off".

        Can you help me to figure it out.

        Here I am attaching the patch file with this.

        Thanks

        Show
        Eranda Sooriyabandara added a comment - Hi Bryan, I faced a problem with fix the error in convented code of, ij> select max(name), max(resdate) from inventory join timeslots on inventory.capacity is not null left outer join reservations on inventory.itemno = reservations.itemno and reservations.slot = timeslots.slot; 1 2 --------------------------------------------------------------------------------------------------------------- Yang 2000-07-13 WARNING 01003: Null values were eliminated from the argument of a column function. Because the converted code by the DERBY-2151 tool for the "warning message(WARNING 01003)" was, if (usingEmbedded()) { if ((sqlWarn == null) && (st != null)) sqlWarn = st.getWarnings(); if (sqlWarn == null) sqlWarn = getConnection().getWarnings(); assertNotNull("Expected warning but found none", sqlWarn); assertSQLState("01003", sqlWarn); sqlWarn = null; } which give me a error in "assertNotNull("Expected warning but found none", sqlWarn);" saying that no warning was found. I thought thats because of the statement returns result set and the warning message must associates with the result set. I change the statement(st) with the resultset(rs). But it give me the error message "Operation getWarning not permitted. Verify that autocommit is off". Can you help me to figure it out. Here I am attaching the patch file with this. Thanks
        Hide
        Eranda Sooriyabandara added a comment -

        Hi Bryan,
        Here is the patch file.

        Show
        Eranda Sooriyabandara added a comment - Hi Bryan, Here is the patch file.
        Hide
        Bryan Pendleton added a comment -

        Hi Eranda, thanks for working on this test.

        The handling of the warning message is a little bit delicate, and I had to fiddle with it a bit.
        Basically, the problem is that the warning is not generated in embedded mode until after
        you call rs.next(), but if you wait until you have processed the entire result set, then the
        warning is cleared when the result set is closed.

        So I think you can only get this warning after calling rs.next(), but before calling rs.close(),
        and since the call to JDBC.assertFullResultSet() was doing both the rs.next() and the rs.close()
        for you, you weren't able to see the warning in your test code.

        So I re-wrote this section of the test a little bit and was able to get the
        warning to appear in a way that the test could check it.

        Here's the section of the test after I fiddled with the test code a bit. Please try putting
        this code into the test and see if it now works for you.

        rs = st.executeQuery(
        " select max(name), max(resdate) from inventory join "
        + "timeslots on inventory.capacity is not null "
        + "left outer join reservations on inventory.itemno = "
        + "reservations.itemno and reservations.slot = timeslots.slot");

        rs.next(); // This causes the warning to be generated now.
        if (usingEmbedded())

        { SQLWarning sqlWarn = rs.getWarnings(); assertNotNull("Expected warning but found none", sqlWarn); assertSQLState("01003", sqlWarn); }

        assertEquals("Yang", rs.getString(1));
        assertEquals("2000-07-13", rs.getString(2));

        I also deleted the declaration of the "sqlWarn" variable from the top of
        the test method and moved it to this area because this was the only
        place that variable was used and so it seemed a little easier to read that way.

        Show
        Bryan Pendleton added a comment - Hi Eranda, thanks for working on this test. The handling of the warning message is a little bit delicate, and I had to fiddle with it a bit. Basically, the problem is that the warning is not generated in embedded mode until after you call rs.next(), but if you wait until you have processed the entire result set, then the warning is cleared when the result set is closed. So I think you can only get this warning after calling rs.next(), but before calling rs.close(), and since the call to JDBC.assertFullResultSet() was doing both the rs.next() and the rs.close() for you, you weren't able to see the warning in your test code. So I re-wrote this section of the test a little bit and was able to get the warning to appear in a way that the test could check it. Here's the section of the test after I fiddled with the test code a bit. Please try putting this code into the test and see if it now works for you. rs = st.executeQuery( " select max(name), max(resdate) from inventory join " + "timeslots on inventory.capacity is not null " + "left outer join reservations on inventory.itemno = " + "reservations.itemno and reservations.slot = timeslots.slot"); rs.next(); // This causes the warning to be generated now. if (usingEmbedded()) { SQLWarning sqlWarn = rs.getWarnings(); assertNotNull("Expected warning but found none", sqlWarn); assertSQLState("01003", sqlWarn); } assertEquals("Yang", rs.getString(1)); assertEquals("2000-07-13", rs.getString(2)); I also deleted the declaration of the "sqlWarn" variable from the top of the test method and moved it to this area because this was the only place that variable was used and so it seemed a little easier to read that way.
        Hide
        Knut Anders Hatlen added a comment -

        Does anyone know what this warning is supposed to tell us?

        One small concern I have with the patch, is that I'm not sure it preserves the intention of the runtime statistics tests. The new test only checks that the plans contained a table scan and did not contain a distinct scan. The comments in the original test seem to indicate that the intention was to test certain properties of the predicates. Unfortunately, it's difficult to know exactly which properties of the predicates to check, since the comments are rather vague. One of the comments is more specific, though, and says "make sure predicates are null tolerant", but the new test only checks the results from the query. I think "null tolerant" means we should check that the predicates have the property "Ordered nulls: false".

        Show
        Knut Anders Hatlen added a comment - Does anyone know what this warning is supposed to tell us? One small concern I have with the patch, is that I'm not sure it preserves the intention of the runtime statistics tests. The new test only checks that the plans contained a table scan and did not contain a distinct scan. The comments in the original test seem to indicate that the intention was to test certain properties of the predicates. Unfortunately, it's difficult to know exactly which properties of the predicates to check, since the comments are rather vague. One of the comments is more specific, though, and says "make sure predicates are null tolerant", but the new test only checks the results from the query. I think "null tolerant" means we should check that the predicates have the property "Ordered nulls: false".
        Hide
        Eranda Sooriyabandara added a comment -

        Hi Bryan, Knut,

        The warning was WARNING 01003: Null values were eliminated from the argument
        of a column function.

        I think the "make sure predicates are null tolerant" means that prove that
        predicate can be null.
        But also don't see how the runtime statistics are make sure that. Here is
        the part which relevant to this.

        ij> – make sure predicates are null tolerant

        select * from tt1 left outer join tt2 on tt1.c1 = tt2.c2

        where char(tt2.c2) is null;

        C1 |C2 |C3 |C1 |C2 |C3

        -----------------------------------------------------------------------

        1 |2 |3 |NULL |NULL |NULL

        ij> – where java.lang.Integer::toString(tt2.c2) = '2';

        values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

        1

        ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

        Statement Name:

        null

        Statement Text:

        – make sure predicates are null tolerant

        select * from tt1 left outer join tt2 on tt1.c1 = tt2.c2

        where char(tt2.c2) is null

        Parse Time: 0

        Bind Time: 0

        Optimize Time: 0

        Generate Time: 0

        Compile Time: 0

        Execute Time: 0

        Begin Compilation Timestamp : null

        End Compilation Timestamp : null

        Begin Execution Timestamp : null

        End Execution Timestamp : null

        Statement Execution Plan Text:

        Project-Restrict ResultSet (4):

        Number of opens = 1

        Rows seen = 3

        Rows filtered = 2

        restriction = true

        projection = false

        constructor time (milliseconds) = 0

        open time (milliseconds) = 0

        next time (milliseconds) = 0

        close time (milliseconds) = 0

        restriction time (milliseconds) = 0

        projection time (milliseconds) = 0

        Source result set:

        Hash Left Outer Join ResultSet:

        Number of opens = 1

        Rows seen from the left = 3

        Rows seen from the right = 2

        Empty right rows returned = 0

        Rows filtered = 0

        Rows returned = 3

        constructor time (milliseconds) = 0

        open time (milliseconds) = 0

        next time (milliseconds) = 0

        close time (milliseconds) = 0

        Left result set:

        Table Scan ResultSet for TT1 at read committed isolation level
        using instantaneous share row locking chosen by the optimizer

        Number of opens = 1

        Rows seen = 3

        Rows filtered = 0

        Fetch Size = 16

        constructor time (milliseconds) = 0

        open time (milliseconds) = 0

        next time (milliseconds) = 0

        close time (milliseconds) = 0

        next time in milliseconds/row = 0

        scan information:

        Bit set of columns fetched=All

        Number of columns fetched=3

        Number of pages visited=1

        Number of rows qualified=3

        Number of rows visited=8

        Scan type=heap

        start position:

        null

        stop position:

        null

        qualifiers:

        None

        Right result set:

        Hash Scan ResultSet for TT2 at read committed isolation level
        using instantaneous share row locking:

        Number of opens = 3

        Hash table size = 3

        Hash key is column number 1

        Rows seen = 2

        Rows filtered = 0

        constructor time (milliseconds) = 0

        open time (milliseconds) = 0

        next time (milliseconds) = 0

        close time (milliseconds) = 0

        next time in milliseconds/row = 0

        scan information:

        Bit set of columns fetched=All

        Number of columns fetched=3

        Number of pages visited=1

        Number of rows qualified=3

        Number of rows visited=8

        Scan type=heap

        start position:

        null

        stop position:

        null

        scan qualifiers:

        None

        next qualifiers:

        Column[0][0] Id: 1

        Operator: =

        Ordered nulls: false

        Unknown return value: false

        Negate comparison result: false

        Also I have a problem with,

        assertStatementError("42972", st,
        "select * from t1 a left outer join t2 b "
        + "on a.c1 = b.c1 and a.c1 = (select c1 from t1 where "
        + "a.c1 = t1.c1 and a.c1 = 1)");

        because this sql statement doesn't give the error 42972.

        Here I am attaching the diff file with this.

        Show
        Eranda Sooriyabandara added a comment - Hi Bryan, Knut, The warning was WARNING 01003: Null values were eliminated from the argument of a column function. I think the "make sure predicates are null tolerant" means that prove that predicate can be null. But also don't see how the runtime statistics are make sure that. Here is the part which relevant to this. ij> – make sure predicates are null tolerant select * from tt1 left outer join tt2 on tt1.c1 = tt2.c2 where char(tt2.c2) is null; C1 |C2 |C3 |C1 |C2 |C3 ----------------------------------------------------------------------- 1 |2 |3 |NULL |NULL |NULL ij> – where java.lang.Integer::toString(tt2.c2) = '2'; values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: – make sure predicates are null tolerant select * from tt1 left outer join tt2 on tt1.c1 = tt2.c2 where char(tt2.c2) is null Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Project-Restrict ResultSet (4): Number of opens = 1 Rows seen = 3 Rows filtered = 2 restriction = true projection = false constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Hash Left Outer Join ResultSet: Number of opens = 1 Rows seen from the left = 3 Rows seen from the right = 2 Empty right rows returned = 0 Rows filtered = 0 Rows returned = 3 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Table Scan ResultSet for TT1 at read committed isolation level using instantaneous share row locking chosen by the optimizer Number of opens = 1 Rows seen = 3 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched=All Number of columns fetched=3 Number of pages visited=1 Number of rows qualified=3 Number of rows visited=8 Scan type=heap start position: null stop position: null qualifiers: None Right result set: Hash Scan ResultSet for TT2 at read committed isolation level using instantaneous share row locking: Number of opens = 3 Hash table size = 3 Hash key is column number 1 Rows seen = 2 Rows filtered = 0 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched=All Number of columns fetched=3 Number of pages visited=1 Number of rows qualified=3 Number of rows visited=8 Scan type=heap start position: null stop position: null scan qualifiers: None next qualifiers: Column [0] [0] Id: 1 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false Also I have a problem with, assertStatementError("42972", st, "select * from t1 a left outer join t2 b " + "on a.c1 = b.c1 and a.c1 = (select c1 from t1 where " + "a.c1 = t1.c1 and a.c1 = 1)"); because this sql statement doesn't give the error 42972. Here I am attaching the diff file with this.
        Hide
        Bryan Pendleton added a comment -

        Hi Eranda, thanks for the updated patch! I will try to have a look at it this weekend.

        Show
        Bryan Pendleton added a comment - Hi Eranda, thanks for the updated patch! I will try to have a look at it this weekend.
        Hide
        Eranda Sooriyabandara added a comment -

        Hi Bryan,

        I found the reason for the error I previously mentioned. This is because of
        the

        The reason for the error was more than one object table includes same name
        columns.

        After solve the issue https://issues.apache.org/jira/browse/DERBY-4380

        the scope of the ON clauses makes it clear which table they belong to in
        each case, so they should not fail.
        The patch file was,

        http://issues.apache.org/jira/secure/attachment/12425239/on_subquery_v2.diff

        Show
        Eranda Sooriyabandara added a comment - Hi Bryan, I found the reason for the error I previously mentioned. This is because of the The reason for the error was more than one object table includes same name columns. After solve the issue https://issues.apache.org/jira/browse/DERBY-4380 the scope of the ON clauses makes it clear which table they belong to in each case, so they should not fail. The patch file was, http://issues.apache.org/jira/secure/attachment/12425239/on_subquery_v2.diff
        Hide
        Bryan Pendleton added a comment -

        Hi Eranda, thanks for attaching the updated patch with the license granted.

        The patch applies and builds cleanly in my environment, but when I run
        the test, I get an error.

        Can you see if you can figure out what might be making this test case fail?

        thanks,

        bryan

        ...........E...............
        Time: 30.233
        There was 1 error:
        1) testBug2897(org.apache.derbyTesting.functionTests.tests.lang.OuterJoinTest)java.lang.NullPointerException
        at org.apache.derbyTesting.junit.RuntimeStatisticsParser.<init>(RuntimeStatisticsParser.java:49)
        at org.apache.derbyTesting.functionTests.tests.lang.OuterJoinTest.testBug2897(OuterJoinTest.java:2293)
        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:22)
        at junit.extensions.TestSetup$1.protect(TestSetup.java:19)
        at junit.extensions.TestSetup.run(TestSetup.java:23)
        at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)

        FAILURES!!!
        Tests run: 26, Failures: 0, Errors: 1

        Show
        Bryan Pendleton added a comment - Hi Eranda, thanks for attaching the updated patch with the license granted. The patch applies and builds cleanly in my environment, but when I run the test, I get an error. Can you see if you can figure out what might be making this test case fail? thanks, bryan ...........E............... Time: 30.233 There was 1 error: 1) testBug2897(org.apache.derbyTesting.functionTests.tests.lang.OuterJoinTest)java.lang.NullPointerException at org.apache.derbyTesting.junit.RuntimeStatisticsParser.<init>(RuntimeStatisticsParser.java:49) at org.apache.derbyTesting.functionTests.tests.lang.OuterJoinTest.testBug2897(OuterJoinTest.java:2293) 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:22) at junit.extensions.TestSetup$1.protect(TestSetup.java:19) at junit.extensions.TestSetup.run(TestSetup.java:23) at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57) FAILURES!!! Tests run: 26, Failures: 0, Errors: 1
        Hide
        Bryan Pendleton added a comment -

        Copying a comment that was raised on the derby-dev list. I think
        the comment makes a good point, though I don't have a good
        idea about what other naming convention to use.

        I think that as long as we are consistent about using DERBY-NNNN
        in test comments to refer to JIRA-hosted Deby issues, then it will
        be fairly clear that other bug number references refer to pre-JIRA
        bug tracking systems.

        bryan
        =====================================================
        > > 1) testBug2897(org.apache.derbyTesting.functionTests.tests.lang.OuterJoinTest)java.lang.NullPointerException

        Btw, the number here, 2897, refers to pre-JIRA days. Should we make up
        some systematic way of referring to such old error number so as not
        to confuse them with JIRA numbering? I imagine we don't want to remove
        the old number information since I have seen the IBM folks sometimes make
        use of the old numbers.. Maybe it's enough that we always refer to
        JIRA errors as DERBY-nnnn in comments and some adapted string for
        identifiers, e.g. [dD]erbyNNNN.

        Dag

        Show
        Bryan Pendleton added a comment - Copying a comment that was raised on the derby-dev list. I think the comment makes a good point, though I don't have a good idea about what other naming convention to use. I think that as long as we are consistent about using DERBY-NNNN in test comments to refer to JIRA-hosted Deby issues, then it will be fairly clear that other bug number references refer to pre-JIRA bug tracking systems. bryan ===================================================== > > 1) testBug2897(org.apache.derbyTesting.functionTests.tests.lang.OuterJoinTest)java.lang.NullPointerException Btw, the number here, 2897, refers to pre-JIRA days. Should we make up some systematic way of referring to such old error number so as not to confuse them with JIRA numbering? I imagine we don't want to remove the old number information since I have seen the IBM folks sometimes make use of the old numbers.. Maybe it's enough that we always refer to JIRA errors as DERBY-nnnn in comments and some adapted string for identifiers, e.g. [dD] erbyNNNN. Dag
        Hide
        Eranda Sooriyabandara added a comment -

        Hi Bryan,
        I am sorry for bit late to submit the patch. Here I did some changes.
        Break into some more tests which I thought it would be clear if they are separated.
        I use the name the old jira issues according to the Dag.
        Now the test build and runs successful in my machine. I hope you get the same results and I think now you can commit it to the trunk.
        Eranda

        Show
        Eranda Sooriyabandara added a comment - Hi Bryan, I am sorry for bit late to submit the patch. Here I did some changes. Break into some more tests which I thought it would be clear if they are separated. I use the name the old jira issues according to the Dag. Now the test build and runs successful in my machine. I hope you get the same results and I think now you can commit it to the trunk. Eranda
        Hide
        Bryan Pendleton added a comment -

        Thanks Eranda for the updated patch! I will try to have a look soon.

        Show
        Bryan Pendleton added a comment - Thanks Eranda for the updated patch! I will try to have a look soon.
        Hide
        Bryan Pendleton added a comment -

        Hi Eranda,

        In general, we don't put "@author" tags into Apache code, as a matter of Apache policy.

        Is it OK with you if I remove the "@author" tag from OuterJoinTest prior to commit?

        Show
        Bryan Pendleton added a comment - Hi Eranda, In general, we don't put "@author" tags into Apache code, as a matter of Apache policy. Is it OK with you if I remove the "@author" tag from OuterJoinTest prior to commit?
        Hide
        Eranda Sooriyabandara added a comment -

        Hi Bryan,
        Yes its perfectly ok.
        That tag I used when I demonstrate the code. I forgot to remove it.
        Thanks

        Show
        Eranda Sooriyabandara added a comment - Hi Bryan, Yes its perfectly ok. That tag I used when I demonstrate the code. I forgot to remove it. Thanks
        Hide
        Bryan Pendleton added a comment -

        Thanks Eranda for all the work on this patch!

        The latest test looks fine, and it runs successfully in my environment.

        Committed to the subversion trunk as revision 919250.

        Show
        Bryan Pendleton added a comment - Thanks Eranda for all the work on this patch! The latest test looks fine, and it runs successfully in my environment. Committed to the subversion trunk as revision 919250.
        Hide
        Eranda Sooriyabandara added a comment -

        Hi Bryan,
        Thanks for committing to the trunk.
        I am closing this issue.

        Show
        Eranda Sooriyabandara added a comment - Hi Bryan, Thanks for committing to the trunk. I am closing this issue.

          People

          • Assignee:
            Eranda Sooriyabandara
            Reporter:
            Eranda Sooriyabandara
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development