[ https://issues.apache.org/jira/browse/DERBY-6956?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16126715#comment-16126715 ]
Bryan Pendleton commented on DERBY-6956: ---------------------------------------- And in the particular code path tickled by the CREATE TABLE AS SELECT statement, in the code: {code:java} public boolean isUserCreatableType() throws StandardException { switch (typeId.getJDBCTypeId()) { case Types.JAVA_OBJECT: return getTypeId().getBaseTypeId().isAnsiUDT(); case Types.DECIMAL: return (getPrecision() <= typeId.getMaximumPrecision()) && (getScale() <= typeId.getMaximumScale()) && (getMaximumWidth() <= typeId.getMaximumMaximumWidth()); default: break; } return true; } {code} the problem is that getMaximumWidth() returns 33, which is larger than the 31 limit for DECIMAL. So, somehow, the statement thinks it's trying to create a field of width 33, even though the source table is DECIMAL(31,6). And, sure enough, if I change the repro so that the DECIMALCOLUMN column is DECIMAL(29,6), the test passes and the error is not thrown. > Create table as Select cannot copy Decimal columns > -------------------------------------------------- > > Key: DERBY-6956 > URL: https://issues.apache.org/jira/browse/DERBY-6956 > Project: Derby > Issue Type: Bug > Components: JDBC, SQL > Affects Versions: 10.13.1.1 > Environment: Windows 7, DataGrip > Reporter: Murat Cengiz > Labels: newbie > > I create a test table with the following query: > CREATE TABLE "DERBYTEST" ("STRINGCOLUMN" varchar(255), "INTEGERCOLUMN" > integer, "SHORTCOLUMN" varchar(255), "LONGCOLUMN" bigint, "DOUBLECOLUMN" > double, "FLOATCOLUMN" double, "DECIMALCOLUMN" decimal(31, 6), "BOOLEANCOLUMN" > smallint, "DATECOLUMN" timestamp, "DATETIMECOLUMN" timestamp, "ID" integer, > "LASTMODTIME" timestamp, PRIMARY KEY ("ID")) > this query completes successfully > I later try to copy the table with the following query: > CREATE TABLE "DERBYTEST_TEMP" AS SELECT * FROM DERBYTEST WITH NO DATA > This throws the following exception: > [42X71][30000] Invalid data type 'DECIMAL(31, 6)' for column 'DECIMALCOLUMN' > Everything works perfectly fine if I remove the decimal column. -- This message was sent by Atlassian JIRA (v6.4.14#64029)