Re: [jira] [Commented] (DERBY-6956) Create table as Select cannot copy Decimal columns
I've completed all the work I intend for this. Thanks for the quick work on this! +1 to resolve and include in this fall's release.
[jira] [Commented] (DERBY-6956) Create table as Select cannot copy Decimal columns
[
https://issues.apache.org/jira/browse/DERBY-6956?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16134254#comment-16134254
]
Rick Hillegas commented on DERBY-6956:
--
Hi Bryan,
Thanks for the additional tests. I don't plan to backport this fix to any other
codelines. I think that the issue can be resolved. What are your thoughts?
> 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
> Attachments: derby-6956-01-aa-removeSpuriousCheck.diff, withTest.diff
>
>
> 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][3] 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)
[jira] [Commented] (DERBY-6956) Create table as Select cannot copy Decimal columns
[
https://issues.apache.org/jira/browse/DERBY-6956?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16131574#comment-16131574
]
ASF subversion and git services commented on DERBY-6956:
Commit 1805356 from [~bryanpendleton] in branch 'code/trunk'
[ https://svn.apache.org/r1805356 ]
DERBY-6956: CREATE TABLE AS SELECT cannot copy DECIMAL(31,6)
This change adds several test tests to serve as regression tests.
> 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
> Attachments: derby-6956-01-aa-removeSpuriousCheck.diff, withTest.diff
>
>
> 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][3] 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)
[jira] [Commented] (DERBY-6956) Create table as Select cannot copy Decimal columns
[
https://issues.apache.org/jira/browse/DERBY-6956?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16129738#comment-16129738
]
Rick Hillegas commented on DERBY-6956:
--
Thanks, Bryan. I have committed the derby-6956-01-aa-removeSpuriousCheck.diff
patch.
> 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
> Attachments: derby-6956-01-aa-removeSpuriousCheck.diff, withTest.diff
>
>
> 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][3] 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)
[jira] [Commented] (DERBY-6956) Create table as Select cannot copy Decimal columns
[
https://issues.apache.org/jira/browse/DERBY-6956?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16129737#comment-16129737
]
ASF subversion and git services commented on DERBY-6956:
Commit 1805249 from [~rhillegas] in branch 'code/trunk'
[ https://svn.apache.org/r1805249 ]
DERBY-6956: Commit patch derby-6956-01-aa-removeSpuriousCheck.diff, removing a
needless restriction on the kind of DECIMAL types which can be column types in
a CREATE TABLE AS SELECT statement.
> 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
> Attachments: derby-6956-01-aa-removeSpuriousCheck.diff, withTest.diff
>
>
> 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][3] 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)
[jira] [Commented] (DERBY-6956) Create table as Select cannot copy Decimal columns
[
https://issues.apache.org/jira/browse/DERBY-6956?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16128149#comment-16128149
]
Bryan Pendleton commented on DERBY-6956:
Thanks for the patch, Rick. I agree with your assessment.
+1 to commit from me.
If I have some spare time in the next week or two, I'll take the repro case
from the issue description, as well as a couple variants (e.g., DECIMAL(29,6),
etc.) and drop them into our regression suite, just to close the loop and
satisfy myself.
> 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
> Attachments: derby-6956-01-aa-removeSpuriousCheck.diff
>
>
> 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][3] 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)
[jira] [Commented] (DERBY-6956) Create table as Select cannot copy Decimal columns
[
https://issues.apache.org/jira/browse/DERBY-6956?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16127293#comment-16127293
]
Bryan Pendleton commented on DERBY-6956:
It's not clear to me why we make this check for CREATE TABLE AS SELECT, but not
for simple CREATE TABLE.
Anyway, two possibilities occur to me:
# Increase the maximum width for DECIMAL to 33, since clearly we desire to
allow 31 digits, an optional sign, and an optional decimal point
# Remove the check against maximum width, if, as Rick suggested, we aren't sure
why we're making that check.
> 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][3] 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)
[jira] [Commented] (DERBY-6956) Create table as Select cannot copy Decimal columns
[
https://issues.apache.org/jira/browse/DERBY-6956?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16126786#comment-16126786
]
Bryan Pendleton commented on DERBY-6956:
To summarize: my opinion is that DERBY-836 and DERBY-2605 interacted to create
this problem.
DERBY-836 means that sometimes the maximum width is "illegal", and DERBY-2605
added the check that we should not create a new table with a column of
"illegal" maximum width.
Rick, what do you think?
> 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][3] 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)
[jira] [Commented] (DERBY-6956) Create table as Select cannot copy Decimal columns
[
https://issues.apache.org/jira/browse/DERBY-6956?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16126784#comment-16126784
]
Bryan Pendleton commented on DERBY-6956:
I suspect that this code in sqlgrammar.jj is the source of the extra 2 bytes in
the maximum width:
{code:java}
/*
** If we have a decimal point, need to count it
** towards maxwidth. Max width needs to account
** for the possible leading '0' and '-' and the
** decimal point. e.g., DEC(1,1) has a maxwidth
** of 4 (to handle "-0.1").
*/
maxWidth = DataTypeUtilities.computeMaxWidth( precision, scale);
{code}
This is further confirmed by looking at this code in DataTypeUtilities.java:
{code:java}
public static int computeMaxWidth( int precision, int scale)
{
// There are 3 possible cases with respect to finding the correct max
// width for DECIMAL type.
// 1. If scale = 0, only sign should be added to precision.
// 2. scale=precision, 3 should be added to precision for sign, decimal and
an additional char '0'.
// 3. precision > scale > 0, 2 should be added to precision for sign and
decimal.
return (scale ==0) ? (precision +1) : ((scale == precision) ? (precision +
3) : (precision + 2));
}
{code}
And that code in DataTypeUtilities, in turn, goes back to DERBY-836.
> 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][3] 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)
[jira] [Commented] (DERBY-6956) Create table as Select cannot copy Decimal columns
[
https://issues.apache.org/jira/browse/DERBY-6956?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16126781#comment-16126781
]
Bryan Pendleton commented on DERBY-6956:
The stack trace at the moment that CREATE TABLE AS SELECT decides to create a
new decimal type of maximum width 33 is:
{code:java}
at
org.apache.derby.catalog.types.TypeDescriptorImpl.(TypeDescriptorImpl.java:88)
at
org.apache.derby.iapi.types.DataTypeDescriptor.(DataTypeDescriptor.java:450)
at
org.apache.derby.impl.sql.compile.SQLParser.getDataTypeServices(SQLParser.java:203)
at
org.apache.derby.impl.sql.compile.SQLParser.exactNumericType(SQLParser.java:3855)
at
org.apache.derby.impl.sql.compile.SQLParser.numericType(SQLParser.java:3763)
at
org.apache.derby.impl.sql.compile.SQLParser.dataTypeCommon(SQLParser.java:3445)
at
org.apache.derby.impl.sql.compile.SQLParser.dataTypeDDL(SQLParser.java:3381)
at
org.apache.derby.impl.sql.compile.SQLParser.columnDefinition(SQLParser.java:3247)
at
org.apache.derby.impl.sql.compile.SQLParser.tableElement(SQLParser.java:3212)
at
org.apache.derby.impl.sql.compile.SQLParser.tableElementList(SQLParser.java:3199)
at
org.apache.derby.impl.sql.compile.SQLParser.tableDefinition(SQLParser.java:10274)
at
org.apache.derby.impl.sql.compile.SQLParser.createStatements(SQLParser.java:2029)
at
org.apache.derby.impl.sql.compile.SQLParser.StatementPart(SQLParser.java:1923)
at
org.apache.derby.impl.sql.compile.SQLParser.Statement(SQLParser.java:1823)
at
org.apache.derby.impl.sql.compile.ParserImpl.parseStatementOrSearchCondition(ParserImpl.java:170)
at
org.apache.derby.impl.sql.compile.ParserImpl.parseStatement(ParserImpl.java:130)
at
org.apache.derby.impl.sql.GenericStatement.prepMinion(GenericStatement.java:359)
at
org.apache.derby.impl.sql.GenericStatement.prepare(GenericStatement.java:99)
at
org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(GenericLanguageConnectionContext.java:1114)
at
org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:689)
at
org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:637)
at org.apache.derby.impl.tools.ij.ij.executeImmediate(ij.java:372)
at org.apache.derby.impl.tools.ij.utilMain.doCatch(utilMain.java:533)
at
org.apache.derby.impl.tools.ij.utilMain.runScriptGuts(utilMain.java:375)
at org.apache.derby.impl.tools.ij.utilMain.go(utilMain.java:251)
at org.apache.derby.impl.tools.ij.Main.go(Main.java:229)
at org.apache.derby.impl.tools.ij.Main.mainCore(Main.java:184)
at org.apache.derby.impl.tools.ij.Main.main(Main.java:75)
at org.apache.derby.tools.ij.main(ij.java:59)
{code}
> 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][3] 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)
[jira] [Commented] (DERBY-6956) Create table as Select cannot copy Decimal columns
[
https://issues.apache.org/jira/browse/DERBY-6956?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16126720#comment-16126720
]
Rick Hillegas commented on DERBY-6956:
--
The CREATE TABLE AS SELECT statement fails because we flunk the following
check...
(getMaximumWidth() <= typeId.getMaximumMaximumWidth());
at line 1859 in DataTypeDescriptor.java. That, in turn is because the maximum
width accounts for a sign byte and a decimal point byte in addition to the
maximum number of digits in the decimal. I don't know yet why that check is
necessary.
> 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][3] 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)
[jira] [Commented] (DERBY-6956) Create table as Select cannot copy Decimal columns
[
https://issues.apache.org/jira/browse/DERBY-6956?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=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][3] 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)
[jira] [Commented] (DERBY-6956) Create table as Select cannot copy Decimal columns
[
https://issues.apache.org/jira/browse/DERBY-6956?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16126710#comment-16126710
]
Bryan Pendleton commented on DERBY-6956:
DataTypeDescriptor.isUserCreatableType was added by DERBY-2605, and dealt with
the problem described here:
http://article.gmane.org/gmane.comp.apache.db.derby.devel/40881
> 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][3] 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)
[jira] [Commented] (DERBY-6956) Create table as Select cannot copy Decimal columns
[
https://issues.apache.org/jira/browse/DERBY-6956?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16126706#comment-16126706
]
Bryan Pendleton commented on DERBY-6956:
Reproduces for me with the head of trunk.
The stack trace is:
{code:java}
Mon Aug 14 18:35:53 PDT 2017 Thread[main,5,main] (XID = 168), (SESSIONID = 1),
(DATABASE = memory:test), (DRDAID = null)
, Failed Statement is: CREATE TABLE "DERBYTEST_TEMP" AS SELECT * FROM DERBYTEST
WITH NO DATA^M
ERROR 42X71: Invalid data type 'DECIMAL(31, 6)' for column 'DECIMALCOLUMN'.
at
org.apache.derby.iapi.error.StandardException.newException(StandardException.java:290)
at
org.apache.derby.iapi.error.StandardException.newException(StandardException.java:285)
at
org.apache.derby.impl.sql.compile.CreateTableNode.bindStatement(CreateTableNode.java:338)
at
org.apache.derby.impl.sql.GenericStatement.prepMinion(GenericStatement.java:401)
at
org.apache.derby.impl.sql.GenericStatement.prepare(GenericStatement.java:99)
at
org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(GenericLanguageConnectionContext.java:1114)
at
org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:689)
at
org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:637)
at org.apache.derby.impl.tools.ij.ij.executeImmediate(ij.java:372)
at org.apache.derby.impl.tools.ij.utilMain.doCatch(utilMain.java:533)
at org.apache.derby.impl.tools.ij.utilMain.runScriptGuts(utilMain.java:375)
at org.apache.derby.impl.tools.ij.utilMain.go(utilMain.java:251)
at org.apache.derby.impl.tools.ij.Main.go(Main.java:229)
at org.apache.derby.impl.tools.ij.Main.mainCore(Main.java:184)
at org.apache.derby.impl.tools.ij.Main.main(Main.java:75)
at org.apache.derby.tools.ij.main(ij.java:59)
{code}
And line 338 of CreateTableNode.java is this "if" statement:
{code:java}
DataTypeDescriptor dtd =
rc.getExpression().getTypeServices();
if ((dtd != null) && !dtd.isUserCreatableType())
{
throw StandardException.newException(
SQLState.LANG_INVALID_COLUMN_TYPE_CREATE_TABLE,
dtd.getFullSQLTypeName(),
rc.getName());
}
{code}
This appears to indicate that dtd.isUserCreatableType() returned false for the
DECIMAL(31,6) data type column.
> 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][3] 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)
[jira] [Commented] (DERBY-6956) Create table as Select cannot copy Decimal columns
[
https://issues.apache.org/jira/browse/DERBY-6956?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16125830#comment-16125830
]
klement commented on DERBY-6956:
Urgent! I'm facing the same issue.
> 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][3] 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)
