Derby
  1. Derby
  2. DERBY-3296

Importing to table in default schema fails when another table with the same name exists in a different schema

    Details

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

      Description

      I discovered this problem while trying to get suites.All to run on the phoneME advanced platform, but it is easy to reproduce also with Java SE.

      The problem is that importing a table fails if there exists a table with the same name in a different schema. The error is:

      ERROR 42X04: Column 'COLUMN2' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'COLUMN2' is not a column in the target table.

      If the table in the other schema is removed, the import command succeeds. If the import command is executed with an explicit schema name, instead of relying on "null" to give the default schema, the command also succeeds.

      I believe this is a bug.

      The following sequence of commands reproduces the problem:

      ij version 10.4
      ij> connect 'jdbc:derby:MyDbTest;create=true';
      ij> create table t1 (a int);
      0 rows inserted/updated/deleted
      ij> insert into t1 values 1,2,3;
      3 rows inserted/updated/deleted
      ij> call SYSCS_UTIL.SYSCS_EXPORT_TABLE(null, 'T1', 't1.txt', null, null, null);
      0 rows inserted/updated/deleted
      ij> create schema iep;
      0 rows inserted/updated/deleted
      ij> create table iep.t1 (b int);
      0 rows inserted/updated/deleted
      ij> call SYSCS_UTIL.SYSCS_IMPORT_TABLE(null, 'T1', 't1.txt', null, null, null, 0);
      ERROR 38000: The exception 'java.sql.SQLException: Column 'COLUMN2' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'COLUMN2' is not a column in the target table.' was thrown while evaluating an expression.
      ERROR 42X04: Column 'COLUMN2' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'COLUMN2' is not a column in the target table.

      1. d3296-2.diff
        2 kB
        Knut Anders Hatlen
      2. d3296.diff
        2 kB
        Knut Anders Hatlen

        Issue Links

          Activity

          Hide
          Knut Anders Hatlen added a comment -

          The problem here is that ColumnInfo uses DatabaseMetaData.getColumns() to find all the columns in the table. Since the schema is not specified (null), getColumns() returns the columns for all tables with the specified name in all schemas. This works fine as long as there's only one table with that name, but it breaks down when there's another table with the same name in another schema.

          The solution is simple, just get the current schema from the connection object if no schema is specified. The attached patch makes that change and adds a test case. The tools tests run cleanly with the patch. I'll start the full regression test suite now.

          Show
          Knut Anders Hatlen added a comment - The problem here is that ColumnInfo uses DatabaseMetaData.getColumns() to find all the columns in the table. Since the schema is not specified (null), getColumns() returns the columns for all tables with the specified name in all schemas. This works fine as long as there's only one table with that name, but it breaks down when there's another table with the same name in another schema. The solution is simple, just get the current schema from the connection object if no schema is specified. The attached patch makes that change and adds a test case. The tools tests run cleanly with the patch. I'll start the full regression test suite now.
          Hide
          Knut Anders Hatlen added a comment -

          All the regression tests passed.

          Show
          Knut Anders Hatlen added a comment - All the regression tests passed.
          Hide
          Knut Anders Hatlen added a comment -

          Here's an updated patch (d3296-2.diff). The only change is that the test now verifies that the rows were imported to the correct table.

          Show
          Knut Anders Hatlen added a comment - Here's an updated patch (d3296-2.diff). The only change is that the test now verifies that the rows were imported to the correct table.
          Hide
          Knut Anders Hatlen added a comment -

          Committed to trunk with revision 779681.

          Show
          Knut Anders Hatlen added a comment - Committed to trunk with revision 779681.
          Hide
          Knut Anders Hatlen added a comment -

          Merged to 10.5 and committed revision 783087.

          Show
          Knut Anders Hatlen added a comment - Merged to 10.5 and committed revision 783087.
          Hide
          Ole Solberg added a comment -

          Not seen since commit, so closing.

          Show
          Ole Solberg added a comment - Not seen since commit, so closing.
          Hide
          Eric Vergnaud added a comment -

          Hi,

          this is broken again in 10.8.2.2 using EmbeddedDriver40 and Java 1.6 on MacOSX, even when specifying the schema

          I have a simple unit test running this code:
          public void csvImport(String csvFilePath) throws SQLException {
          final String sql = "CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE ('PANDORA','COUNTRIES','" + csvFilePath + "',NULL,NULL,'UTF-8',0)";
          Connection cn = ds.getConnection();
          try {
          Statement st = cn.createStatement();
          try

          { st.execute(sql); }

          finally

          { st.close(); }

          } finally

          { cn.close(); }

          }

          which throws the following exception:

          java.sql.SQLException: The exception 'java.sql.SQLException: Column 'COLUMN2' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'COLUMN2' is not a column in the target table.' was thrown while evaluating an expression.
          at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
          at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source)
          at org.apache.derby.impl.jdbc.Util.seeNextException(Unknown Source)
          at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
          at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
          at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
          at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
          at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)
          at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
          at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
          at pandora.CountryDAO.csvImport(CountryDAO.java:313)
          at pandora.TestCountryDAO.testImport(TestCountryDAO.java:99)
          at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
          at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
          at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
          at java.lang.reflect.Method.invoke(Method.java:597)
          at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:44)
          at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15)
          at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:41)
          at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20)
          at org.junit.runners.BlockJUnit4ClassRunner.runNotIgnored(BlockJUnit4ClassRunner.java:79)
          at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:71)
          at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:49)
          at org.junit.runners.ParentRunner$3.run(ParentRunner.java:193)
          at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:52)
          at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:191)
          at org.junit.runners.ParentRunner.access$000(ParentRunner.java:42)
          at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:184)
          at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:28)
          at org.junit.runners.ParentRunner.run(ParentRunner.java:236)
          at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)
          at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
          at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
          at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
          at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
          at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)
          Caused by: java.sql.SQLException: The exception 'java.sql.SQLException: Column 'COLUMN2' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'COLUMN2' is not a column in the target table.' was thrown while evaluating an expression.
          at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
          at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
          ... 36 more
          Caused by: java.sql.SQLException: Column 'COLUMN2' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'COLUMN2' is not a column in the target table.
          at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
          at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
          at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
          at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
          at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
          at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
          at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
          at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
          at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source)
          at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown Source)
          at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown Source)
          at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.<init>(Unknown Source)
          at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown Source)
          at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
          at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
          at org.apache.derby.impl.load.Import.performImport(Unknown Source)
          at org.apache.derby.impl.load.Import.importTable(Unknown Source)
          at org.apache.derby.catalog.SystemProcedures.SYSCS_IMPORT_TABLE(Unknown Source)
          at org.apache.derby.exe.acdf1940dax0136x67bdx26aex0000041f37b01.g0(Unknown Source)
          at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
          at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
          at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
          at java.lang.reflect.Method.invoke(Method.java:597)
          at org.apache.derby.impl.services.reflect.ReflectMethod.invoke(Unknown Source)
          at org.apache.derby.impl.sql.execute.CallStatementResultSet.open(Unknown Source)
          at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(Unknown Source)
          at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)
          ... 29 more
          Caused by: ERROR 42X04: Column 'COLUMN2' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'COLUMN2' is not a column in the target table.
          at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
          at org.apache.derby.impl.sql.compile.ColumnReference.bindExpression(Unknown Source)
          at org.apache.derby.impl.sql.compile.ResultColumn.bindExpression(Unknown Source)
          at org.apache.derby.impl.sql.compile.ResultColumnList.bindExpressions(Unknown Source)
          at org.apache.derby.impl.sql.compile.SelectNode.bindExpressions(Unknown Source)
          at org.apache.derby.impl.sql.compile.SelectNode.bindExpressionsWithTables(Unknown Source)
          at org.apache.derby.impl.sql.compile.DMLStatementNode.bindExpressionsWithTables(Unknown Source)
          at org.apache.derby.impl.sql.compile.DMLStatementNode.bindResultSetsWithTables(Unknown Source)
          at org.apache.derby.impl.sql.compile.InsertNode.bindStatement(Unknown Source)
          at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
          at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
          at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source)
          ... 48 more

          Show
          Eric Vergnaud added a comment - Hi, this is broken again in 10.8.2.2 using EmbeddedDriver40 and Java 1.6 on MacOSX, even when specifying the schema I have a simple unit test running this code: public void csvImport(String csvFilePath) throws SQLException { final String sql = "CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE ('PANDORA','COUNTRIES','" + csvFilePath + "',NULL,NULL,'UTF-8',0)"; Connection cn = ds.getConnection(); try { Statement st = cn.createStatement(); try { st.execute(sql); } finally { st.close(); } } finally { cn.close(); } } which throws the following exception: java.sql.SQLException: The exception 'java.sql.SQLException: Column 'COLUMN2' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'COLUMN2' is not a column in the target table.' was thrown while evaluating an expression. at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source) at org.apache.derby.impl.jdbc.Util.newEmbedSQLException(Unknown Source) at org.apache.derby.impl.jdbc.Util.seeNextException(Unknown Source) at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source) at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source) at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source) at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source) at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source) at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source) at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source) at pandora.CountryDAO.csvImport(CountryDAO.java:313) at pandora.TestCountryDAO.testImport(TestCountryDAO.java:99) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:44) at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15) at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:41) at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20) at org.junit.runners.BlockJUnit4ClassRunner.runNotIgnored(BlockJUnit4ClassRunner.java:79) at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:71) at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:49) at org.junit.runners.ParentRunner$3.run(ParentRunner.java:193) at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:52) at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:191) at org.junit.runners.ParentRunner.access$000(ParentRunner.java:42) at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:184) at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:28) at org.junit.runners.ParentRunner.run(ParentRunner.java:236) at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50) at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197) Caused by: java.sql.SQLException: The exception 'java.sql.SQLException: Column 'COLUMN2' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'COLUMN2' is not a column in the target table.' was thrown while evaluating an expression. at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source) at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source) ... 36 more Caused by: java.sql.SQLException: Column 'COLUMN2' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'COLUMN2' is not a column in the target table. at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source) at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source) at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source) at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source) at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source) at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source) at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source) at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source) at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source) at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown Source) at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown Source) at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.<init>(Unknown Source) at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown Source) at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source) at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source) at org.apache.derby.impl.load.Import.performImport(Unknown Source) at org.apache.derby.impl.load.Import.importTable(Unknown Source) at org.apache.derby.catalog.SystemProcedures.SYSCS_IMPORT_TABLE(Unknown Source) at org.apache.derby.exe.acdf1940dax0136x67bdx26aex0000041f37b01.g0(Unknown Source) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at org.apache.derby.impl.services.reflect.ReflectMethod.invoke(Unknown Source) at org.apache.derby.impl.sql.execute.CallStatementResultSet.open(Unknown Source) at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(Unknown Source) at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source) ... 29 more Caused by: ERROR 42X04: Column 'COLUMN2' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'COLUMN2' is not a column in the target table. at org.apache.derby.iapi.error.StandardException.newException(Unknown Source) at org.apache.derby.impl.sql.compile.ColumnReference.bindExpression(Unknown Source) at org.apache.derby.impl.sql.compile.ResultColumn.bindExpression(Unknown Source) at org.apache.derby.impl.sql.compile.ResultColumnList.bindExpressions(Unknown Source) at org.apache.derby.impl.sql.compile.SelectNode.bindExpressions(Unknown Source) at org.apache.derby.impl.sql.compile.SelectNode.bindExpressionsWithTables(Unknown Source) at org.apache.derby.impl.sql.compile.DMLStatementNode.bindExpressionsWithTables(Unknown Source) at org.apache.derby.impl.sql.compile.DMLStatementNode.bindResultSetsWithTables(Unknown Source) at org.apache.derby.impl.sql.compile.InsertNode.bindStatement(Unknown Source) at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source) at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source) at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source) ... 48 more
          Hide
          Knut Anders Hatlen added a comment -

          Hi Eric,

          Please file a new JIRA issue for the problem you're seeing, and post the steps needed to reproduce it there so that we can look into it.

          Thanks.

          Show
          Knut Anders Hatlen added a comment - Hi Eric, Please file a new JIRA issue for the problem you're seeing, and post the steps needed to reproduce it there so that we can look into it. Thanks.

            People

            • Assignee:
              Knut Anders Hatlen
              Reporter:
              Vemund Østgaard
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development