[jira] Created: (DERBY-4187) Convert altertable.sql to JUnit

Convert altertable.sql to JUnit
-------------------------------

                 Key: DERBY-4187
                 URL: https://issues.apache.org/jira/browse/DERBY-4187
             Project: Derby
          Issue Type: Test
          Components: Test
    Affects Versions: 10.4.2.1
            Reporter: Eranda Sooriyabandara
            Priority: Minor
             Fix For: 10.5.1.2


Converting altertable.sql harness test to JUnit

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

     [ https://issues.apache.org/jira/browse/DERBY-4187?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Eranda Sooriyabandara updated DERBY-4187:
-----------------------------------------

    Attachment: AlterTable.java
                AlterTable.diff

I convert altertable.sql to AlterTable.junit under Bryan Pendletons
supervision and I attach both AlterTable.java and AlterTable.diff files
here.

> Convert altertable.sql to JUnit
> -------------------------------
>
>                 Key: DERBY-4187
>                 URL: https://issues.apache.org/jira/browse/DERBY-4187
>             Project: Derby
>          Issue Type: Test
>          Components: Test
>    Affects Versions: 10.4.2.1
>            Reporter: Eranda Sooriyabandara
>            Priority: Minor
>             Fix For: 10.5.1.2
>
>         Attachments: AlterTable.diff, AlterTable.java
>
>   Original Estimate: 486.08h
>  Remaining Estimate: 486.08h
>
> Converting altertable.sql harness test to JUnit

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

    [ https://issues.apache.org/jira/browse/DERBY-4187?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12702875#action_12702875 ]

Knut Anders Hatlen commented on DERBY-4187:
-------------------------------------------

Hi Eranda,

I haven't performed a complete review of your patch, but I have some high-level comments:

- The new file needs an Apache license header. http://www.apache.org/legal/src-headers.html

- The AlterTable class is in package stack, whereas it should be in package org.apache.derbyTesting.functionTests.tests.lang

- By convention, we normally add a Test suffix to the class names when we write JUnit tests, so the class name of the new test would be AlterTableTest

- There are some parts of the test that haven't been converted, marked with [**:: UNCONVERTED ::**]. I think we should try to find a way to convert those parts before we disable the old test

- the old test used to set some properties, see derbyTesting/functionTests/tests/lang/altertable_derby.properties

- altertable.sql is removed, but the old regression test suite still attempts to run it, so it should be removed from derbyTesting/functionTests/suites/derbylang.runall too. Also, the files functionTests/master/altertable.out and functionTests/tests/lang/altertable_derby.properties should be removed when the test is removed

> Convert altertable.sql to JUnit
> -------------------------------
>
>                 Key: DERBY-4187
>                 URL: https://issues.apache.org/jira/browse/DERBY-4187
>             Project: Derby
>          Issue Type: Test
>          Components: Test
>    Affects Versions: 10.4.2.1
>            Reporter: Eranda Sooriyabandara
>            Priority: Minor
>             Fix For: 10.5.1.2
>
>         Attachments: AlterTable.diff, AlterTable.java
>
>   Original Estimate: 486.08h
>  Remaining Estimate: 486.08h
>
> Converting altertable.sql harness test to JUnit

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

     [ https://issues.apache.org/jira/browse/DERBY-4187?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Knut Anders Hatlen updated DERBY-4187:
--------------------------------------

    Derby Info:   (was: [Existing Application Impact])

> Convert altertable.sql to JUnit
> -------------------------------
>
>                 Key: DERBY-4187
>                 URL: https://issues.apache.org/jira/browse/DERBY-4187
>             Project: Derby
>          Issue Type: Test
>          Components: Test
>    Affects Versions: 10.4.2.1
>            Reporter: Eranda Sooriyabandara
>            Priority: Minor
>             Fix For: 10.5.1.2
>
>         Attachments: AlterTable.diff, AlterTable.java
>
>   Original Estimate: 486.08h
>  Remaining Estimate: 486.08h
>
> Converting altertable.sql harness test to JUnit

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

     [ https://issues.apache.org/jira/browse/DERBY-4187?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Eranda Sooriyabandara updated DERBY-4187:
-----------------------------------------

    Attachment: AlterTableTest.diff

 Hi knut,
Thanks for corrections on making a patch.
As a beginning I did,

1.AlterTable.java -->AlterTableTest.java
2. add Apache license header to the java file
3.remove derbyTesting/functionTests/tests/lang/altertable_derby.properties
4.altertable.sql is removed from the old regression test suite

Can you please make a comment on how it could be done unconverted parts
converted.

> Convert altertable.sql to JUnit
> -------------------------------
>
>                 Key: DERBY-4187
>                 URL: https://issues.apache.org/jira/browse/DERBY-4187
>             Project: Derby
>          Issue Type: Test
>          Components: Test
>    Affects Versions: 10.4.2.1
>            Reporter: Eranda Sooriyabandara
>            Priority: Minor
>             Fix For: 10.5.1.2
>
>         Attachments: AlterTable.diff, AlterTable.java, AlterTableTest.diff
>
>   Original Estimate: 486.08h
>  Remaining Estimate: 486.08h
>
> Converting altertable.sql harness test to JUnit

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

    [ https://issues.apache.org/jira/browse/DERBY-4187?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12703089#action_12703089 ]

Knut Anders Hatlen commented on DERBY-4187:
-------------------------------------------

To convert the unconverted parts, I think you'll need to find some way to do the same thing as the IJ commands via JDBC, possibly using DatabaseMetaData.

For instance this code

+        /*[**:: UNCONVERTED ::**]  describe renc_vw_1

+        

+        [**:: UNCONVERTED ::**] COLUMN_NAME         |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&

+------------------------------------------------------------------------------

+V1                  |VARCHAR  |NULL|NULL|10    |NULL      |20        |YES

+V2                  |DOUBLE   |NULL|2   |52    |NULL      |NULL      |YES

+

+        */


could perhaps be replaced with a call to DatabaseMetaData.getColumns() and verify that the table has the expected columns.

I guess that those looking like this could be removed:

+        [**:: UNCONVERTED ::**] Issue the 'help' command for general information on IJ command syntax.

+Any unrecognized commands are treated as potential SQL commands and executed directly.

+Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.

+

+        */

> Convert altertable.sql to JUnit
> -------------------------------
>
>                 Key: DERBY-4187
>                 URL: https://issues.apache.org/jira/browse/DERBY-4187
>             Project: Derby
>          Issue Type: Test
>          Components: Test
>    Affects Versions: 10.4.2.1
>            Reporter: Eranda Sooriyabandara
>            Priority: Minor
>             Fix For: 10.5.1.2
>
>         Attachments: AlterTable.diff, AlterTable.java, AlterTableTest.diff
>
>   Original Estimate: 486.08h
>  Remaining Estimate: 486.08h
>
> Converting altertable.sql harness test to JUnit

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

    [ https://issues.apache.org/jira/browse/DERBY-4187?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12704813#action_12704813 ]

Eranda Sooriyabandara commented on DERBY-4187:
----------------------------------------------

Hi Knut,
Instead of having one GIANT test in AlterTableTest.java, if
we had about 5-6 individual test cases, as in

 testAddColumn()
 testAddConstraint()
 testDropColumn()
 testRenameColumn()
 testAlterIdentityColumn()

and so forth will be better. If we separate them in to individual test
how can we
identify whether which part of code is in which test.
Thanks
Eranda

> Convert altertable.sql to JUnit
> -------------------------------
>
>                 Key: DERBY-4187
>                 URL: https://issues.apache.org/jira/browse/DERBY-4187
>             Project: Derby
>          Issue Type: Test
>          Components: Test
>    Affects Versions: 10.4.2.1
>            Reporter: Eranda Sooriyabandara
>            Priority: Minor
>             Fix For: 10.5.1.2
>
>         Attachments: AlterTable.diff, AlterTable.java, AlterTableTest.diff
>
>   Original Estimate: 486.08h
>  Remaining Estimate: 486.08h
>
> Converting altertable.sql harness test to JUnit

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

     [ https://issues.apache.org/jira/browse/DERBY-4187?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Eranda Sooriyabandara updated DERBY-4187:
-----------------------------------------

    Attachment: AlterTableTest.diff

Hi Knut,
I replace unconverted cord as you say by
DatabaseMetaData.getColumns(). Though it didn't show compile errors(on
DatabaseMetaData.getColumns()) in the the netbeans IDE when compile
using ant it shows 21 compile errors in it.

As I search for DatabaseMetadata.getColumns() method it shows that,
-public ResultSet getColumns(String catalog,String
schemaPattern,String tableNamePattern,String columnNamePattern) throws
-SQLException
has 4 parameters and we did not use them. Can it be the causing of error?

Here I attach the changed code.
Thanks
Eranda

> Convert altertable.sql to JUnit
> -------------------------------
>
>                 Key: DERBY-4187
>                 URL: https://issues.apache.org/jira/browse/DERBY-4187
>             Project: Derby
>          Issue Type: Test
>          Components: Test
>    Affects Versions: 10.4.2.1
>            Reporter: Eranda Sooriyabandara
>            Priority: Minor
>             Fix For: 10.5.1.2
>
>         Attachments: AlterTable.diff, AlterTable.java, AlterTableTest.diff, AlterTableTest.diff
>
>   Original Estimate: 486.08h
>  Remaining Estimate: 486.08h
>
> Converting altertable.sql harness test to JUnit

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

    [ https://issues.apache.org/jira/browse/DERBY-4187?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12704852#action_12704852 ]

Bryan Pendleton commented on DERBY-4187:
----------------------------------------

Hi Eranda, thanks for the updated test.

Here's the section of section of altertable.out where the conversion program was having
problems, because it doesn't know how to convert the "describe" and "show indexes"
statements.

In this code, I think that the "describe" and "show indexes" statements were present only
in order to show that the view and the index were not changed by the statements which
tried to rename the column in the view or the index.

I think that we can just remove these unconverted sections; it is sufficient just to test
for the syntax error on the RENAME statements which reference the view and the index.

ij> rename column renc_vw_1.v2 to v3;
ERROR 42Y62: 'RENAME COLUMN' is not allowed on '"APP"."RENC_VW_1"' because it is a view.
ij> describe renc_vw_1;
COLUMN_NAME         |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
------------------------------------------------------------------------------
V1                  |VARCHAR  |NULL|NULL|10    |NULL      |20        |YES
V2                  |DOUBLE   |NULL|2   |52    |NULL      |NULL      |YES
ij> -- attempt to rename a column in an index, should fail:
create index renc_idx_1 on renc_1 (c, d);
0 rows inserted/updated/deleted
ij> show indexes from renc_1;
TABLE_NAME          |COLUMN_NAME         |NON_U&|TYPE|ASC&|CARDINA&|PAGES
----------------------------------------------------------------------------
RENC_1              |C                   |true  |3   |A   |NULL    |NULL
RENC_1              |D                   |true  |3   |A   |NULL    |NULL
ij> rename column renc_idx_1.d to d_new;
ERROR 42Y55: 'RENAME COLUMN' cannot be performed on 'RENC_IDX_1' because it does not exist.
ij> show indexes from renc_1;
TABLE_NAME          |COLUMN_NAME         |NON_U&|TYPE|ASC&|CARDINA&|PAGES
----------------------------------------------------------------------------
RENC_1              |C                   |true  |3   |A   |NULL    |NULL
RENC_1              |D                   |true  |3   |A   |NULL    |NULL

> Convert altertable.sql to JUnit
> -------------------------------
>
>                 Key: DERBY-4187
>                 URL: https://issues.apache.org/jira/browse/DERBY-4187
>             Project: Derby
>          Issue Type: Test
>          Components: Test
>    Affects Versions: 10.4.2.1
>            Reporter: Eranda Sooriyabandara
>            Priority: Minor
>             Fix For: 10.5.1.2
>
>         Attachments: AlterTable.diff, AlterTable.java, AlterTableTest.diff, AlterTableTest.diff
>
>   Original Estimate: 486.08h
>  Remaining Estimate: 486.08h
>
> Converting altertable.sql harness test to JUnit

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

    [ https://issues.apache.org/jira/browse/DERBY-4187?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12705334#action_12705334 ]

Eranda Sooriyabandara commented on DERBY-4187:
----------------------------------------------

Hi Bryan/Knut,
I tested some of the function include in AlterTableTest.java after
remove unconverted cords.
Here are my testings,
ij> connect 'jdbc:derby:Movies;create=true';
ij> CREATE TABLE Persons ( P_Id int, LastName varchar(255), FirstName
varchar(255), Address varchar(
255), City varchar(255) );
0 rows inserted/updated/deleted
ij> rename table Persons to Studentdetails;
0 rows inserted/updated/deleted
ij> ALTER TABLE Studentdetails DROP LastName;
0 rows inserted/updated/deleted
ij> select *from Studentdetails;
P_ID       |FIRSTNAME        |ADDRESS        |CITY
----------------------------------------------------------------------------------------------------
0 rows selected
ij> ALTER TABLE Studentdetails ADD COLUMN LastName varchar(255);
0 rows inserted/updated/deleted
ij> select *from Studentdetails;
P_ID       |FIRSTNAME    |ADDRESS   |CITY       |L ASTNAME
----------------------------------------------------------------------------------------------------
0 rows selected
ij> ALTER TABLE Studentdetails CHANGE address address1 varchar(255);
ERROR 42X01: Syntax error: Encountered "CHANGE" at line 1, column 28.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and
executed directly.
Consult your DBMS server reference documentation for details of the
SQL syntax supported by your ser
ver.
After testing I can say that, in alter table
        Functions like Rename table, Drop column, Add column, Add constraint,
Drop constraint are working properly but “Change Column name” does
not.
Thanks
Eranda

> Convert altertable.sql to JUnit
> -------------------------------
>
>                 Key: DERBY-4187
>                 URL: https://issues.apache.org/jira/browse/DERBY-4187
>             Project: Derby
>          Issue Type: Test
>          Components: Test
>    Affects Versions: 10.4.2.1
>            Reporter: Eranda Sooriyabandara
>            Priority: Minor
>             Fix For: 10.5.1.2
>
>         Attachments: AlterTable.diff, AlterTable.java, AlterTableTest.diff, AlterTableTest.diff
>
>   Original Estimate: 486.08h
>  Remaining Estimate: 486.08h
>
> Converting altertable.sql harness test to JUnit

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

    [ https://issues.apache.org/jira/browse/DERBY-4187?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12705370#action_12705370 ]

Bryan Pendleton commented on DERBY-4187:
----------------------------------------

Hi Eranda,

That is correct. To rename a column you have to use the RENAME COLUMN statement:
http://db.apache.org/derby/docs/10.5/ref/rrefsqljrenamecolumnstatement.html

So you could do:
RENAME COLUMN Studentdetails.address to address1;

> Convert altertable.sql to JUnit
> -------------------------------
>
>                 Key: DERBY-4187
>                 URL: https://issues.apache.org/jira/browse/DERBY-4187
>             Project: Derby
>          Issue Type: Test
>          Components: Test
>    Affects Versions: 10.4.2.1
>            Reporter: Eranda Sooriyabandara
>            Priority: Minor
>             Fix For: 10.5.1.2
>
>         Attachments: AlterTable.diff, AlterTable.java, AlterTableTest.diff, AlterTableTest.diff
>
>   Original Estimate: 486.08h
>  Remaining Estimate: 486.08h
>
> Converting altertable.sql harness test to JUnit

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

     [ https://issues.apache.org/jira/browse/DERBY-4187?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Eranda Sooriyabandara updated DERBY-4187:
-----------------------------------------

    Attachment: AlterTableTest.diff

hi Bryan,

I attach the patch here.
Here the message I get as the out put while  I run the
AlterTableTest.java with 2 failures.

D:\Repositories\trunk>java junit.textui.TestRunner
org.apache.derbyTesting.functionTests.tests.lang.
AlterTableTest
.F.F
Time: 11.952
There were 2 failures:
1) test_altertable(org.apache.derbyTesting.functionTests.tests.lang.AlterTableTest)junit.framework.A
ssertionFailedError: Expected warning but found none
        at org.apache.derbyTesting.functionTests.tests.lang.AlterTableTest.test_altertable(AlterTabl
eTest.java:77)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
        at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:105)
        at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
        at junit.extensions.TestSetup$1.protect(TestSetup.java:23)
        at junit.extensions.TestSetup.run(TestSetup.java:27)
        at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)
2) test_altertable(org.apache.derbyTesting.functionTests.tests.lang.AlterTableTest)junit.framework.A
ssertionFailedError: Expected error(s) ' X0X95' but no error was thrown.
        at org.apache.derbyTesting.junit.BaseJDBCTestCase.assertStatementError(BaseJDBCTestCase.java
:976)
        at org.apache.derbyTesting.junit.BaseJDBCTestCase.assertStatementError(BaseJDBCTestCase.java
:1002)
        at org.apache.derbyTesting.functionTests.tests.lang.AlterTableTest.test_altertable(AlterTabl
eTest.java:206)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
        at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:105)
        at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
        at junit.extensions.TestSetup$1.protect(TestSetup.java:23)
        at junit.extensions.TestSetup.run(TestSetup.java:27)
        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:23)
        at junit.extensions.TestSetup.run(TestSetup.java:27)
        at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
        at junit.extensions.TestSetup$1.protect(TestSetup.java:23)
        at junit.extensions.TestSetup.run(TestSetup.java:27)
        at org.apache.derbyTesting.junit.BaseTestSetup.run(BaseTestSetup.java:57)

FAILURES!!!
Tests run: 2,  Failures: 2,  Errors: 0

Thanks
Eranda

> Convert altertable.sql to JUnit
> -------------------------------
>
>                 Key: DERBY-4187
>                 URL: https://issues.apache.org/jira/browse/DERBY-4187
>             Project: Derby
>          Issue Type: Test
>          Components: Test
>    Affects Versions: 10.4.2.1
>            Reporter: Eranda Sooriyabandara
>            Priority: Minor
>             Fix For: 10.5.1.2
>
>         Attachments: AlterTable.diff, AlterTable.java, AlterTableTest.diff, AlterTableTest.diff, AlterTableTest.diff
>
>   Original Estimate: 486.08h
>  Remaining Estimate: 486.08h
>
> Converting altertable.sql harness test to JUnit

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

     [ https://issues.apache.org/jira/browse/DERBY-4187?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Bryan Pendleton updated DERBY-4187:
-----------------------------------

    Attachment: AlterTableTest.java

Hi Eranda, thank for the updated AlterTableTest patch.

It seems that you are making very good progress.

I took your patch, and I made some changes to AlterTableTest.java,
and am attaching the version that I modified.

There are going to be a number of small aspects to consider.

Perhaps you can have a look at the AlterTableTest.java that I attached,
and diff it against yours, and see some of the changes that I made?

Some changes are because the automatic conversion program does
not get everything exactly right, and so we have to fix up the test program
slightly, such as when it gets the column names in the result set wrong.

Some other changes are because there are behavior differences between
embedded mode and client/server mode, and we'll have to study those
each in detail to understand them (such as the case where the ALTER TABLE
is supposed to behave differently when a cursor is open on the table).

Please have a look at the revised AlterTableTest that I attached and let me
know your reaction to the changes that I made.

If the changes make sense to you, perhaps you can continue in this mode,
making additional changes as necessary similar to the ones I made.

> Convert altertable.sql to JUnit
> -------------------------------
>
>                 Key: DERBY-4187
>                 URL: https://issues.apache.org/jira/browse/DERBY-4187
>             Project: Derby
>          Issue Type: Test
>          Components: Test
>    Affects Versions: 10.4.2.1
>            Reporter: Eranda Sooriyabandara
>            Priority: Minor
>             Fix For: 10.5.1.2
>
>         Attachments: AlterTable.diff, AlterTable.java, AlterTableTest.diff, AlterTableTest.diff, AlterTableTest.diff, AlterTableTest.java
>
>   Original Estimate: 486.08h
>  Remaining Estimate: 486.08h
>
> Converting altertable.sql harness test to JUnit

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

    [ https://issues.apache.org/jira/browse/DERBY-4187?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12707490#action_12707490 ]

Eranda Sooriyabandara commented on DERBY-4187:
----------------------------------------------

Hi Bryan,
Here I have the test result for updated AlterTableTest.java
D:\Repositories\trunk>java junit.textui.TestRunner
org.apache.derbyTesting.functionTests.tests.lang.
AlterTableTest
.F.F
Time: 20.083
There were 2 failures:
1) test_altertable(org.apache.derbyTesting.functionTests.tests.lang.AlterTableTest)junit.framework.C
omparisonFailure: Column names do not match: expected:<[&]> but was:<[TYPE]>
        at org.apache.derbyTesting.junit.JDBC.assertColumnNames(JDBC.java:681)
        at org.apache.derbyTesting.functionTests.tests.lang.AlterTableTest.test_altertable(AlterTabl
eTest.java:2460)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
        at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:105)
        at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
        at junit.extensions.TestSetup$1.protect(TestSetup.java:23)
        at junit.extensions.TestSetup.run(TestSetup.java:27)
        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:23)
        at junit.extensions.TestSetup.run(TestSetup.java:27)
        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:23)
        at junit.extensions.TestSetup.run(TestSetup.java:27)
        at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
        at junit.extensions.TestSetup$1.protect(TestSetup.java:23)
        at junit.extensions.TestSetup.run(TestSetup.java:27)
2) test_altertable(org.apache.derbyTesting.functionTests.tests.lang.AlterTableTest)junit.framework.A
ssertionFailedError: Unexpected column count: expected:<2> but was:<1>
        at org.apache.derbyTesting.junit.JDBC.assertColumnNames(JDBC.java:676)
        at org.apache.derbyTesting.functionTests.tests.lang.AlterTableTest.test_altertable(AlterTabl
eTest.java:282)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
        at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:105)
        at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
        at junit.extensions.TestSetup$1.protect(TestSetup.java:23)
        at junit.extensions.TestSetup.run(TestSetup.java:27)
        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:23)
        at junit.extensions.TestSetup.run(TestSetup.java:27)
        at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
        at junit.extensions.TestSetup$1.protect(TestSetup.java:23)
        at junit.extensions.TestSetup.run(TestSetup.java:27)
        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:23)
        at junit.extensions.TestSetup.run(TestSetup.java:27)
        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:23)
        at junit.extensions.TestSetup.run(TestSetup.java:27)
        at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
        at junit.extensions.TestSetup$1.protect(TestSetup.java:23)
        at junit.extensions.TestSetup.run(TestSetup.java:27)

FAILURES!!!
Tests run: 2,  Failures: 2,  Errors: 0

As it shows that the 1st fail occurs in 2460th row. So I look in to
the code & in the altertable.out I found that there are two coloumns
with the same name,
CONSTRAINTID |TABLEID |CONSTRAINTNAME |&|SCHEMAID |&|REFERENCEC&
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 xxxxFILTERED-UUIDxxxx|xxxxFILTERED-UUIDxxxx|ATDC_CONSTRAINT_1
 & and &.
Is it possible to have two columns that have same name in single table?
What is this Error says that, expected:<[&]> but was:<[TYPE]> How can
I get through that?
Thanks
Eranda

> Convert altertable.sql to JUnit
> -------------------------------
>
>                 Key: DERBY-4187
>                 URL: https://issues.apache.org/jira/browse/DERBY-4187
>             Project: Derby
>          Issue Type: Test
>          Components: Test
>    Affects Versions: 10.4.2.1
>            Reporter: Eranda Sooriyabandara
>            Priority: Minor
>             Fix For: 10.5.1.2
>
>         Attachments: AlterTable.diff, AlterTable.java, AlterTableTest.diff, AlterTableTest.diff, AlterTableTest.diff, AlterTableTest.java
>
>   Original Estimate: 486.08h
>  Remaining Estimate: 486.08h
>
> Converting altertable.sql harness test to JUnit

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

    [ https://issues.apache.org/jira/browse/DERBY-4187?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12707544#action_12707544 ]

Bryan Pendleton commented on DERBY-4187:
----------------------------------------

> What is this Error says that, expected:<[&]> but was:<[TYPE]>

Hi Eranda. The '&' symbol is actually NOT the name of the column. Rather, it
is a result of running the old test in "ij". The "ij" tool has some special rules about
column names, and if the column name is a reserved word, "ij" prints "&" instead
of printing the column name. And if the column name is long, ij truncates the column
name and prints "&" at the point where it did the truncation.

You can see the actual column names in this case by looking in the Derby
reference manual. Here, we can see in altertable.out that we have just done a
'select * from sys.sysconstraints', so we just need to look up SYSCONSTRAINTS
in the reference manual at:
http://db.apache.org/derby/docs/10.5/ref/rrefsistabs23241.html
and there you can see that the actual column names are:
CONSTRAINTID, TABLEID, CONSTRAINTNAME, TYPE, SCHEMAID, STATE, REFERENCECOUNT

So to fix this, all you need to do is to change the ALterTableTest.java at line 2460 to
use the correct column names instead of the names with the '&' in them.

There will be a number of places in AlterTableTest.java where you have to make this
change, but it's pretty mechanical, so hopefully once you see how to make the change
in one location, you can make the similar change throughout the test program.

> Convert altertable.sql to JUnit
> -------------------------------
>
>                 Key: DERBY-4187
>                 URL: https://issues.apache.org/jira/browse/DERBY-4187
>             Project: Derby
>          Issue Type: Test
>          Components: Test
>    Affects Versions: 10.4.2.1
>            Reporter: Eranda Sooriyabandara
>            Priority: Minor
>             Fix For: 10.5.1.2
>
>         Attachments: AlterTable.diff, AlterTable.java, AlterTableTest.diff, AlterTableTest.diff, AlterTableTest.diff, AlterTableTest.java
>
>   Original Estimate: 486.08h
>  Remaining Estimate: 486.08h
>
> Converting altertable.sql harness test to JUnit

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

    [ https://issues.apache.org/jira/browse/DERBY-4187?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12707717#action_12707717 ]

Eranda Sooriyabandara commented on DERBY-4187:
----------------------------------------------

Hi Bryan,

Here I post the AlterTableTest.java row 277-290

// select * prepared statements do see added columns after alter table

        rs = pSt.executeQuery();
        expColNames = new String [] {"C1", "C2"};
        JDBC.assertColumnNames(rs, expColNames);

        expRS = new String [][]
        {
            {"1", null},
            {"2", null}
        };

        JDBC.assertFullResultSet(rs, expRS, true);

It gives a failure ,

test_altertable(org.apache.derbyTesting.functionTests.tests.lang.AlterTableTest)junit.framework.A
ssertionFailedError: Unexpected column count: expected:<2> but was:<1>
        at org.apache.derbyTesting.junit.JDBC.assertColumnNames(JDBC.java:676)
        at org.apache.derbyTesting.functionTests.tests.lang.AlterTableTest.test_altertable(AlterTabl
eTest.java:282).

I change the code to,

// select * prepared statements do "not" see added columns after alter table

        rs = pSt.executeQuery();
        expColNames = new String [] {"C1"};
        JDBC.assertColumnNames(rs, expColNames);

        expRS = new String [][]
        {
            {"1"},
            {"2"}
        };

        JDBC.assertFullResultSet(rs, expRS, true);

as a testing.

Then the Failure in that row is not there when I was testing.
It means that <select * prepared statements do "not" see added columns
after alter table>
or another failure in some where else that causing to the result change.

But some how altertable.out shows two columns,

ij> -- select * prepared statements do see added columns after alter table
execute p1;
C1         |C2
-----------------------
1          |NULL
2          |NULL

Thanks
Eranda

> Convert altertable.sql to JUnit
> -------------------------------
>
>                 Key: DERBY-4187
>                 URL: https://issues.apache.org/jira/browse/DERBY-4187
>             Project: Derby
>          Issue Type: Test
>          Components: Test
>    Affects Versions: 10.4.2.1
>            Reporter: Eranda Sooriyabandara
>            Priority: Minor
>             Fix For: 10.5.1.2
>
>         Attachments: AlterTable.diff, AlterTable.java, AlterTableTest.diff, AlterTableTest.diff, AlterTableTest.diff, AlterTableTest.java
>
>   Original Estimate: 486.08h
>  Remaining Estimate: 486.08h
>
> Converting altertable.sql harness test to JUnit

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

    [ https://issues.apache.org/jira/browse/DERBY-4187?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12708054#action_12708054 ]

Bryan Pendleton commented on DERBY-4187:
----------------------------------------

Hi Eranda,

I think this is interesting. I think that perhaps this was a mistake in the current altertable.sql test.

The sequence of operations is:
1) Prepare ("select * from t2")
2) Execute prepared statement, sees only column c1
3) alter table t2 add column c2
4) Execute prepared statement, STILL sees only column c1, though the IJ version saw both c1 AND c2

I think this is OK behavior. I'm not really sure why there is a behavior difference
between the IJ-based test and the pure JDBC Junit test, but I think it's acceptable
for Derby to have either behavior.

Does anybody else have a theory as to why we see this behavior change when we
convert this test from an IJ script to a JUnit JDBC test?

For the time being, I think you should leave AlterTableTest checking for JUST column c1,
put a comment into the test indicating that when this test is run in IJ, the behavior is
different, and let's move on to resolve any other problems in the test.

> Convert altertable.sql to JUnit
> -------------------------------
>
>                 Key: DERBY-4187
>                 URL: https://issues.apache.org/jira/browse/DERBY-4187
>             Project: Derby
>          Issue Type: Test
>          Components: Test
>    Affects Versions: 10.4.2.1
>            Reporter: Eranda Sooriyabandara
>            Priority: Minor
>             Fix For: 10.5.1.2
>
>         Attachments: AlterTable.diff, AlterTable.java, AlterTableTest.diff, AlterTableTest.diff, AlterTableTest.diff, AlterTableTest.java
>
>   Original Estimate: 486.08h
>  Remaining Estimate: 486.08h
>
> Converting altertable.sql harness test to JUnit

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

     [ https://issues.apache.org/jira/browse/DERBY-4187?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Eranda Sooriyabandara updated DERBY-4187:
-----------------------------------------

    Attachment: AlterTableTest.diff

Hi Bryan,
Here I attaching the patch up to now.

> Convert altertable.sql to JUnit
> -------------------------------
>
>                 Key: DERBY-4187
>                 URL: https://issues.apache.org/jira/browse/DERBY-4187
>             Project: Derby
>          Issue Type: Test
>          Components: Test
>    Affects Versions: 10.4.2.1
>            Reporter: Eranda Sooriyabandara
>            Priority: Minor
>             Fix For: 10.5.1.2
>
>         Attachments: AlterTable.diff, AlterTable.java, AlterTableTest.diff, AlterTableTest.diff, AlterTableTest.diff, AlterTableTest.diff, AlterTableTest.java
>
>   Original Estimate: 486.08h
>  Remaining Estimate: 486.08h
>
> Converting altertable.sql harness test to JUnit

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

     [ https://issues.apache.org/jira/browse/DERBY-4187?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Eranda Sooriyabandara updated DERBY-4187:
-----------------------------------------

    Attachment: AlterTableTest.diff

Hi Bryan,

Thanks for your motivation and I am glad to say that now It's one
error on the test. Here's results displayed.

D:\Repositories\trunk>java junit.textui.TestRunner
org.apache.derbyTesting.functionTests.tests.lang.
AlterTableTest
..F
Time: 36.547
There was 1 failure:
1) test_altertable(org.apache.derbyTesting.functionTests.tests.lang.AlterTableTest)junit.framework.A
ssertionFailedError: Expected error(s) ' X0X95' but no error was thrown.
        at org.apache.derbyTesting.junit.BaseJDBCTestCase.assertStatementError(BaseJDBCTestCase.java
:976)
        at org.apache.derbyTesting.junit.BaseJDBCTestCase.assertStatementError(BaseJDBCTestCase.java
:1002)
        at org.apache.derbyTesting.functionTests.tests.lang.AlterTableTest.test_altertable(AlterTabl
eTest.java:2117)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
        at org.apache.derbyTesting.junit.BaseTestCase.runBare(BaseTestCase.java:105)
        at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
        at junit.extensions.TestSetup$1.protect(TestSetup.java:23)
        at junit.extensions.TestSetup.run(TestSetup.java:27)
        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:23)
        at junit.extensions.TestSetup.run(TestSetup.java:27)
        at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
        at junit.extensions.TestSetup$1.protect(TestSetup.java:23)
        at junit.extensions.TestSetup.run(TestSetup.java:27)
        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:23)
        at junit.extensions.TestSetup.run(TestSetup.java:27)
        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:23)
        at junit.extensions.TestSetup.run(TestSetup.java:27)
        at junit.extensions.TestDecorator.basicRun(TestDecorator.java:24)
        at junit.extensions.TestSetup$1.protect(TestSetup.java:23)
        at junit.extensions.TestSetup.run(TestSetup.java:27)

FAILURES!!!
Tests run: 2,  Failures: 1,  Errors: 0
Here I am attaching  the patch with this.

> Convert altertable.sql to JUnit
> -------------------------------
>
>                 Key: DERBY-4187
>                 URL: https://issues.apache.org/jira/browse/DERBY-4187
>             Project: Derby
>          Issue Type: Test
>          Components: Test
>    Affects Versions: 10.4.2.1
>            Reporter: Eranda Sooriyabandara
>            Priority: Minor
>             Fix For: 10.5.1.2
>
>         Attachments: AlterTable.diff, AlterTable.java, AlterTableTest.diff, AlterTableTest.diff, AlterTableTest.diff, AlterTableTest.diff, AlterTableTest.diff, AlterTableTest.java
>
>   Original Estimate: 486.08h
>  Remaining Estimate: 486.08h
>
> Converting altertable.sql harness test to JUnit

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

     [ https://issues.apache.org/jira/browse/DERBY-4187?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Bryan Pendleton updated DERBY-4187:
-----------------------------------

    Attachment: AlterTableTest.java

Hi Eranda, thanks for the updated test. I made two additional changes:
1) Added "if (usingEmbedded())" in front of line 2117, and
2) Changed JDBC.assertFullResultSet to JDBC.assertUnorderedResultSet on line 3036
and then the test ran successfully for me!

I've attached AlterTableTest.java with my modifications; can you confirm that
the test now passes for you?

> Convert altertable.sql to JUnit
> -------------------------------
>
>                 Key: DERBY-4187
>                 URL: https://issues.apache.org/jira/browse/DERBY-4187
>             Project: Derby
>          Issue Type: Test
>          Components: Test
>    Affects Versions: 10.4.2.1
>            Reporter: Eranda Sooriyabandara
>            Priority: Minor
>             Fix For: 10.5.1.2
>
>         Attachments: AlterTable.diff, AlterTable.java, AlterTableTest.diff, AlterTableTest.diff, AlterTableTest.diff, AlterTableTest.diff, AlterTableTest.diff, AlterTableTest.java, AlterTableTest.java
>
>   Original Estimate: 486.08h
>  Remaining Estimate: 486.08h
>
> Converting altertable.sql harness test to JUnit

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.