You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Rick Hillegas (JIRA)" <ji...@apache.org> on 2012/09/06 19:43:07 UTC
[jira] [Created] (DERBY-5918) CREATE TABLE AS SELECT doesn't work
on tables with BOOLEAN columns
Rick Hillegas created DERBY-5918:
------------------------------------
Summary: CREATE TABLE AS SELECT doesn't work on tables with BOOLEAN columns
Key: DERBY-5918
URL: https://issues.apache.org/jira/browse/DERBY-5918
Project: Derby
Issue Type: Bug
Components: SQL
Affects Versions: 10.10.0.0
Reporter: Rick Hillegas
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;
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (DERBY-5918) CREATE TABLE AS SELECT doesn't work
on tables with BOOLEAN columns
Posted by "Bryan Pendleton (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5918?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13477461#comment-13477461 ]
Bryan Pendleton commented on DERBY-5918:
----------------------------------------
I believe DERBY-5953 is a duplicate.
> CREATE TABLE AS SELECT doesn't work on tables with BOOLEAN columns
> ------------------------------------------------------------------
>
> Key: DERBY-5918
> URL: https://issues.apache.org/jira/browse/DERBY-5918
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.10.0.0
> Reporter: Rick Hillegas
> Labels: derby_triage10_10
>
> 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;
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Resolved] (DERBY-5918) CREATE TABLE AS SELECT doesn't work
on tables with BOOLEAN columns
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5918?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas resolved DERBY-5918.
----------------------------------
Resolution: Fixed
Fix Version/s: 10.10.0.0
10.9.1.1
10.8.2.3
10.7.1.4
Ported 1399306 to the following branches:
10.9 at subversion revision 1399310
10.8 at subversion revision 1399312
10.7 at subversion revision 1399313
> CREATE TABLE AS SELECT doesn't work on tables with BOOLEAN columns
> ------------------------------------------------------------------
>
> Key: DERBY-5918
> URL: https://issues.apache.org/jira/browse/DERBY-5918
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.10.0.0
> Reporter: Rick Hillegas
> Assignee: Rick Hillegas
> Labels: derby_triage10_10
> Fix For: 10.7.1.4, 10.8.2.3, 10.9.1.1, 10.10.0.0
>
> Attachments: derby-5918-01-aa-removeHurdle.diff, derby-5918-01-ab-removeHurdle.diff
>
>
> 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;
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Updated] (DERBY-5918) CREATE TABLE AS SELECT doesn't work
on tables with BOOLEAN columns
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5918?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas updated DERBY-5918:
---------------------------------
Attachment: derby-5918-01-aa-removeHurdle.diff
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.
> CREATE TABLE AS SELECT doesn't work on tables with BOOLEAN columns
> ------------------------------------------------------------------
>
> Key: DERBY-5918
> URL: https://issues.apache.org/jira/browse/DERBY-5918
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.10.0.0
> Reporter: Rick Hillegas
> Assignee: Rick Hillegas
> Labels: derby_triage10_10
> Attachments: derby-5918-01-aa-removeHurdle.diff
>
>
> 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;
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Updated] (DERBY-5918) CREATE TABLE AS SELECT doesn't work
on tables with BOOLEAN columns
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5918?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen updated DERBY-5918:
--------------------------------------
Issue & fix info: Repro attached
Urgency: Normal
Labels: derby_triage10_10 (was: )
> CREATE TABLE AS SELECT doesn't work on tables with BOOLEAN columns
> ------------------------------------------------------------------
>
> Key: DERBY-5918
> URL: https://issues.apache.org/jira/browse/DERBY-5918
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.10.0.0
> Reporter: Rick Hillegas
> Labels: derby_triage10_10
>
> 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;
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Comment Edited] (DERBY-5918) CREATE TABLE AS SELECT doesn't
work on tables with BOOLEAN columns
Posted by "David Myers (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5918?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13450465#comment-13450465 ]
David Myers edited comment on DERBY-5918 at 9/7/12 7:45 PM:
------------------------------------------------------------
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
was (Author: dmyers):
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?
> CREATE TABLE AS SELECT doesn't work on tables with BOOLEAN columns
> ------------------------------------------------------------------
>
> Key: DERBY-5918
> URL: https://issues.apache.org/jira/browse/DERBY-5918
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.10.0.0
> Reporter: Rick Hillegas
>
> 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;
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Updated] (DERBY-5918) CREATE TABLE AS SELECT doesn't work
on tables with BOOLEAN columns
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5918?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas updated DERBY-5918:
---------------------------------
Bug behavior facts: Seen in production
Marking this as "Seen in production" because of derby-5953.
> CREATE TABLE AS SELECT doesn't work on tables with BOOLEAN columns
> ------------------------------------------------------------------
>
> Key: DERBY-5918
> URL: https://issues.apache.org/jira/browse/DERBY-5918
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.10.0.0
> Reporter: Rick Hillegas
> Labels: derby_triage10_10
>
> 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;
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (DERBY-5918) CREATE TABLE AS SELECT doesn't work
on tables with BOOLEAN columns
Posted by "David Myers (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5918?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13449944#comment-13449944 ]
David Myers commented on DERBY-5918:
------------------------------------
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
> CREATE TABLE AS SELECT doesn't work on tables with BOOLEAN columns
> ------------------------------------------------------------------
>
> Key: DERBY-5918
> URL: https://issues.apache.org/jira/browse/DERBY-5918
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.10.0.0
> Reporter: Rick Hillegas
>
> 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;
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (DERBY-5918) CREATE TABLE AS SELECT doesn't work
on tables with BOOLEAN columns
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5918?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13450645#comment-13450645 ]
Rick Hillegas commented on DERBY-5918:
--------------------------------------
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.
> CREATE TABLE AS SELECT doesn't work on tables with BOOLEAN columns
> ------------------------------------------------------------------
>
> Key: DERBY-5918
> URL: https://issues.apache.org/jira/browse/DERBY-5918
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.10.0.0
> Reporter: Rick Hillegas
>
> 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;
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Commented] (DERBY-5918) CREATE TABLE AS SELECT doesn't work
on tables with BOOLEAN columns
Posted by "David Myers (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5918?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13450465#comment-13450465 ]
David Myers commented on DERBY-5918:
------------------------------------
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?
> CREATE TABLE AS SELECT doesn't work on tables with BOOLEAN columns
> ------------------------------------------------------------------
>
> Key: DERBY-5918
> URL: https://issues.apache.org/jira/browse/DERBY-5918
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.10.0.0
> Reporter: Rick Hillegas
>
> 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;
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Updated] (DERBY-5918) CREATE TABLE AS SELECT doesn't work
on tables with BOOLEAN columns
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5918?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas updated DERBY-5918:
---------------------------------
Attachment: derby-5918-01-ab-removeHurdle.diff
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
> CREATE TABLE AS SELECT doesn't work on tables with BOOLEAN columns
> ------------------------------------------------------------------
>
> Key: DERBY-5918
> URL: https://issues.apache.org/jira/browse/DERBY-5918
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.10.0.0
> Reporter: Rick Hillegas
> Assignee: Rick Hillegas
> Labels: derby_triage10_10
> Attachments: derby-5918-01-aa-removeHurdle.diff, derby-5918-01-ab-removeHurdle.diff
>
>
> 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;
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Assigned] (DERBY-5918) CREATE TABLE AS SELECT doesn't work
on tables with BOOLEAN columns
Posted by "Rick Hillegas (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-5918?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Rick Hillegas reassigned DERBY-5918:
------------------------------------
Assignee: Rick Hillegas
> CREATE TABLE AS SELECT doesn't work on tables with BOOLEAN columns
> ------------------------------------------------------------------
>
> Key: DERBY-5918
> URL: https://issues.apache.org/jira/browse/DERBY-5918
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.10.0.0
> Reporter: Rick Hillegas
> Assignee: Rick Hillegas
> Labels: derby_triage10_10
>
> 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;
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira