Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-1304

DROP view does not always completely remove view

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.1.2.1
    • Fix Version/s: 10.1.3.2, 10.2.1.6, 10.3.1.4
    • Component/s: SQL
    • Labels:
      None
    • Environment:
      Windows XP
    • Urgency:
      Normal

      Description

      Execute the following scenario:

      Create a schema
      Create a table
      Create a view

      Drop the view
      Drop the table
      Drop the schema

      The first time this sequence executes OK, However, executing the same sequence again fails on the drop view:
      ERROR X0X05: Table 'view' does not exist.

      When I leave out the create and drop of a view, all goes well, so it seems
      to have something to do with the view.

      I encountered the problem first using org.apache.derby.jdbc.ClientConnectionPoolDataSource, but later I found that the problem is reproducable with a simple ij script as follows:
      – *******************************************************
      – Setup connection
      – *******************************************************
      connect 'jdbc:derby://localhost/TestData;create=true;';
      – *******************************************************
      – The first time
      – *******************************************************
      CREATE SCHEMA TEST_SCHEMA;
      CREATE TABLE TEST_SCHEMA.T1 (TABLE_COLUMN LONG VARCHAR);
      CREATE VIEW TEST_SCHEMA.V1 AS SELECT TABLE_COLUMN AS VIEW_COLUMN FROM
      TEST_SCHEMA.T1;

      DROP VIEW TEST_SCHEMA.V1;
      DROP TABLE TEST_SCHEMA.T1;
      DROP SCHEMA TEST_SCHEMA RESTRICT;
      – *******************************************************
      – The second time
      – *******************************************************
      CREATE SCHEMA TEST_SCHEMA;
      CREATE TABLE TEST_SCHEMA.T1 (TABLE_COLUMN LONG VARCHAR);
      CREATE VIEW TEST_SCHEMA.V1 AS SELECT TABLE_COLUMN AS VIEW_COLUMN FROM
      TEST_SCHEMA.T1;

      DROP VIEW TEST_SCHEMA.V1; – This statement fails
      DROP TABLE TEST_SCHEMA.T1;
      DROP SCHEMA TEST_SCHEMA RESTRICT;
      – *******************************************************
      – Cleanup
      – *******************************************************
      disconnect;
      connect 'jdbc:derby://localhost/TestData;shutdown=true;';

      As an explanation why I would like to be able to create and then drop views: I am trying to interactively create my table setup. When I make mistakes I must drop wrongly defined tables or views.

      I encountered some other very strange behaviour that might be related to this issue (table not found when I just created it and wanted to create a second table with a foreign key referencing the first table), but which I am not yet able to reproduce in a consistent and simple way.

        Attachments

        1. derby1304-trunk-diff01.txt
          4 kB
          Yip Ng
        2. derby1304-trunk-stat01.txt
          0.3 kB
          Yip Ng
        3. derby1304-trunk-diff02.txt
          4 kB
          Yip Ng
        4. derby1304-trunk-stat02.txt
          0.3 kB
          Yip Ng

          Activity

            People

            • Assignee:
              yipng Yip Ng
              Reporter:
              pietblok Piet Blok
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: