Derby
  1. Derby
  2. DERBY-5918

CREATE TABLE AS SELECT doesn't work on tables with BOOLEAN columns

    Details

    • Urgency:
      Normal
    • Issue & fix info:
      Repro attached
    • Bug behavior facts:
      Seen in production

      Description

      You can't use CREATE TABLE AS SELECT from a table with a BOOLEAN column. This limitation was put in when CREATE TABLE AS SELECT was added. At that time you could not create user tables with BOOLEAN columns and we did not want people to be able to subvert that restriction by using CREATE TABLE AS SELECT from a system table with BOOLEAN columns. The following script shows this problem:

      connect 'jdbc:derby:memory:db;create=true';

      create table t1( a int );
      create table t2( a boolean );

      create table t3 as select * from t1 with no data;
      create table t4 as select * from t2 with no data;

        Issue Links

          Activity

          Transition Time In Source Status Execution Times Last Executer Last Execution Date
          Open Open Resolved Resolved
          40d 22h 7m 1 Rick Hillegas 17/Oct/12 15:49
          Resolved Resolved Closed Closed
          240d 1h 23m 1 Rick Hillegas 14/Jun/13 17:12
          Gavin made changes -
          Workflow jira [ 12724262 ] Default workflow, editable Closed status [ 12802056 ]
          Rick Hillegas made changes -
          Status Resolved [ 5 ] Closed [ 6 ]
          Kathey Marsden made changes -
          Fix Version/s 10.9.2.0 [ 12323562 ]
          Fix Version/s 10.9.1.1 [ 12321551 ]
          Kathey Marsden made changes -
          Fix Version/s 10.8.3.0 [ 12323456 ]
          Fix Version/s 10.8.2.3 [ 12318540 ]
          Rick Hillegas made changes -
          Status Open [ 1 ] Resolved [ 5 ]
          Fix Version/s 10.7.1.4 [ 12315902 ]
          Fix Version/s 10.8.2.3 [ 12318540 ]
          Fix Version/s 10.9.1.1 [ 12321551 ]
          Fix Version/s 10.10.0.0 [ 12321550 ]
          Resolution Fixed [ 1 ]
          Hide
          Rick Hillegas added a comment -

          Ported 1399306 to the following branches:

          10.9 at subversion revision 1399310

          10.8 at subversion revision 1399312

          10.7 at subversion revision 1399313

          Show
          Rick Hillegas added a comment - Ported 1399306 to the following branches: 10.9 at subversion revision 1399310 10.8 at subversion revision 1399312 10.7 at subversion revision 1399313
          Rick Hillegas made changes -
          Attachment derby-5918-01-ab-removeHurdle.diff [ 12549519 ]
          Hide
          Rick Hillegas added a comment -

          One test failed: CreateTableFromQueryTest. Attaching a second rev of the patch which fixes this test: derby-5918-01-ab-removeHurdle.diff. Committed at subversion revision 1399306.

          Touches the following additional file:

          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/CreateTableFromQueryTest.java

          Show
          Rick Hillegas added a comment - One test failed: CreateTableFromQueryTest. Attaching a second rev of the patch which fixes this test: derby-5918-01-ab-removeHurdle.diff. Committed at subversion revision 1399306. Touches the following additional file: M java/testing/org/apache/derbyTesting/functionTests/tests/lang/CreateTableFromQueryTest.java
          Rick Hillegas made changes -
          Attachment derby-5918-01-aa-removeHurdle.diff [ 12549500 ]
          Hide
          Rick Hillegas added a comment -

          Attaching derby-5918-01-aa-removeHurdle.diff. This patch removes the false assertion by DataTypeDescriptor that users aren't allowed to create BOOLEAN columns. This fixes the bug. I am running regression tests now.

          Touches the following files:

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

          M java/engine/org/apache/derby/iapi/types/DataTypeDescriptor.java

          Removes the disabling lie.

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

          M java/testing/org/apache/derbyTesting/functionTests/tests/lang/BooleanValuesTest.java

          Adds a test case for this bug.

          Show
          Rick Hillegas added a comment - Attaching derby-5918-01-aa-removeHurdle.diff. This patch removes the false assertion by DataTypeDescriptor that users aren't allowed to create BOOLEAN columns. This fixes the bug. I am running regression tests now. Touches the following files: ------------ M java/engine/org/apache/derby/iapi/types/DataTypeDescriptor.java Removes the disabling lie. ------------ M java/testing/org/apache/derbyTesting/functionTests/tests/lang/BooleanValuesTest.java Adds a test case for this bug.
          Rick Hillegas made changes -
          Assignee Rick Hillegas [ rhillegas ]
          Rick Hillegas made changes -
          Bug behavior facts Seen in production [ 10421 ]
          Hide
          Rick Hillegas added a comment -

          Marking this as "Seen in production" because of derby-5953.

          Show
          Rick Hillegas added a comment - Marking this as "Seen in production" because of derby-5953.
          Knut Anders Hatlen made changes -
          Link This issue is duplicated by DERBY-5953 [ DERBY-5953 ]
          Hide
          Bryan Pendleton added a comment -

          I believe DERBY-5953 is a duplicate.

          Show
          Bryan Pendleton added a comment - I believe DERBY-5953 is a duplicate.
          Knut Anders Hatlen made changes -
          Labels derby_triage10_10
          Urgency Normal [ 10052 ]
          Issue & fix info Repro attached [ 10424 ]
          Hide
          Rick Hillegas added a comment -

          Hi David,

          I agree that the fix for this bug could be backported all the way back to 10.7, the release which introduced the BOOLEAN datatype. Thanks.

          Show
          Rick Hillegas added a comment - Hi David, I agree that the fix for this bug could be backported all the way back to 10.7, the release which introduced the BOOLEAN datatype. Thanks.
          Hide
          David Myers added a comment -

          implementation of Boolean data type

          Show
          David Myers added a comment - implementation of Boolean data type
          David Myers made changes -
          Field Original Value New Value
          Link This issue relates to DERBY-4716 [ DERBY-4716 ]
          Hide
          David Myers added a comment - - edited

          I've just noticed that you have set this for version 10.10

          I found this error in 10.9

          I also note that from Riks comment that the Boolean data type was added fairly recently, I don't know precisely when. Should this bug push an update through to the previously available versions that included the Boolean type?

          Edit: located the 'add boolean data type' jira bug: https://issues.apache.org/jira/browse/DERBY-4716

          It seems the boolean type was first added in 10.6.1

          David

          Show
          David Myers added a comment - - edited I've just noticed that you have set this for version 10.10 I found this error in 10.9 I also note that from Riks comment that the Boolean data type was added fairly recently, I don't know precisely when. Should this bug push an update through to the previously available versions that included the Boolean type? Edit: located the 'add boolean data type' jira bug: https://issues.apache.org/jira/browse/DERBY-4716 It seems the boolean type was first added in 10.6.1 David
          Hide
          David Myers added a comment -

          By way of a work around if this is a required functionality for you use the following process.

          Connect to your DB.

          capture a result set of the source table, and examine the resultSetMetaData for each field one at a time.

          build a <Create Table [tableName].... > by capturing your colum names and type data from the above metaData object.

          If anyone needs it I can post method code that does this... I just don't have it to hand right this instant.

          (only downside is it requires you to write a java class, to hold the connection info and an apropriate method).

          As I say I'll try to attach a couple of java files untill a 'proper' fix is initiated

          Show
          David Myers added a comment - By way of a work around if this is a required functionality for you use the following process. Connect to your DB. capture a result set of the source table, and examine the resultSetMetaData for each field one at a time. build a <Create Table [tableName] .... > by capturing your colum names and type data from the above metaData object. If anyone needs it I can post method code that does this... I just don't have it to hand right this instant. (only downside is it requires you to write a java class, to hold the connection info and an apropriate method). As I say I'll try to attach a couple of java files untill a 'proper' fix is initiated
          Rick Hillegas created issue -

            People

            • Assignee:
              Rick Hillegas
              Reporter:
              Rick Hillegas
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development