[
https://issues.apache.org/jira/browse/DERBY-2605?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12496022
]
A B commented on DERBY-2605:
----------------------------
> I have however found a couple of interesting things with the
> precision/scale/maximum width check.
Thank you for pointing these out! I forgot to mention that I didn't do any
extensive testing of the incomplete patch that I posted.
> 1) Given table foo (x double precision), the check fails for: <snip query>
Good catch. I think that in this case there is an implicit scale/precision
"check" because of the multiplication operation. For example, the following
currently works (even though precision is > 31):
ij> values 12345678901234567890123456789012345678901234;
but if we make that part of another Derby operation--say multiplication--it
fails:
ij> values 12345678901234567890123456789012345678901234 * 1.0;
ERROR 22003: The resulting value is outside the range for the data type
DECIMAL/NUMERIC(31,1).
The failure occurs as part of normal Derby processing for arithmetic operation.
So I wonder if we only have to worry about cases where we have a standalone
constant? If that's true then we could add a check for ConstantNode to the
logic in CreateTableNode. Something like:
DataTypeDescriptor dtd =
(rc.getExpression() instanceof ConstantNode)
? rc.getExpressionType()
: null;
if ((dtd != null) && !dtd.isUserCreatableType())
<error>
I haven't tested that approach, but I wonder if it might solve the 1st problem
that you discovered.
> 2) The check fails for: create table bar as select javaclassname from
> sys.sysaliases with no data
> because dtd.getMaximumWidth() returns 2147483647. [...] The data type of
> javaclassname is
> LONG VARCHAR. I would expect the maximum length to be 32700.
Hmm, I would expect a max length of 32700, as well.
> I do not know if this is a bug or was intentional but the resulting maximum
> length certainly
> exceeds the documented value for a LONG VARCHAR.
Sounds like a bug to me, though I do not know the history of this system table.
There are two other system tables that have LONG VARCHAR columns: SYSTRIGGERS
and SYSSTATEMENTS. The latter (SYSSTATEMENTS) uses TypeId.LONGVARCHAR_MAXWIDTH
as the max width for the column, which I think is correct (that value is
32700). SYSTRIGGERS uses Integer.MAX_VALUE just like SYSALIASES, but I do not
think that's correct.
> I am doing some more investigation. I should be able to upload an updated
> patch in a couple of days.
I appreciate your time with this. If you'd like to post an incremental patch
which just addresses the type problem (ex. BOOLEAN) for now, I think that would
be fine. You could then look into the scale/precision problem as a follow-up
patch, if that makes things easier. Or you can just post a single
patch--whichever you prefer.
> You can create BOOLEAN columns in 10.3
> --------------------------------------
>
> Key: DERBY-2605
> URL: https://issues.apache.org/jira/browse/DERBY-2605
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.3.0.0
> Reporter: Rick Hillegas
> Assigned To: James F. Adams
> Attachments: d2605_inc_v1.patch, d2605_v1.patch, d2605_v1.stat
>
>
> The work on DERBY-64 seems to have opened up a wormhole by which you can
> create user tables with BOOLEAN columns. The following script shows how to do
> this:
> drop table foo;
> create table foo
> as select systemalias from sys.sysaliases with no data;
> rename column foo.systemalias to boolcol;
> alter table foo
> alter column boolcol null;
> select c.columndatatype
> from sys.syscolumns c, sys.systables t
> where t.tableid=c.referenceid
> and t.tablename='FOO';
> insert into foo( boolcol )
> values
> ( 0 ),
> ( 1 ),
> ( cast (null as int) )
> ;
> select * from foo;
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
