[
https://issues.apache.org/jira/browse/DERBY-5006?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12991393#comment-12991393
]
Rick Hillegas commented on DERBY-5006:
--------------------------------------
Thanks for raising this issue, Lukas. I agree that Derby should do a better job
of inferring types for nulls. The discrepancy you found between SELECT and
VALUES behavior looks goofy. As part of your work on JOOQ, you may end up
compiling a list of these annoying type inference problems. It would be helpful
if you could attach that list to this issue. Thanks.
> Handle NULL literals like any other database
> --------------------------------------------
>
> Key: DERBY-5006
> URL: https://issues.apache.org/jira/browse/DERBY-5006
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 10.7.1.1
> Reporter: Lukas Eder
> Priority: Minor
> Labels: null, typesystem
>
> Most RDMBS allow for typeless NULL values / literals just about anywhere in a
> SQL statement. I am not aware of SQL standards explicitly encouraging the
> enforcement of casts on NULL values and literals. Even DB2, the "mother of
> strongly typed databases" has finally given up type enforcement on NULL
> values / literals in version 9.7:
> http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com.ibm.db2.luw.wn.doc/doc/i0054263.html
> I wonder, whether this would be a nice way to go for Derby as well? I am
> asking this from the perspective of a database abstraction library developer
> (http://jooq.sourceforge.net). I find it extremely difficult to cast NULL
> values to the correct type in every occasion, as in Java, null does not have
> a type. A good example for this is:
> SELECT cast(null as ...) FROM SYSIBM.SYSDUMMY1
> From the Java application's point of view, any type is OK, because it won't
> matter. But then, why would it matter to Derby?
> Another good example to me is this:
> INSERT INTO X (ID, NAME, NUMBER)
> SELECT (10, null, null)
> FROM SYSIBM.SYSDUMMY1
> This won't work. I'll need to rewrite it to something like this:
> INSERT INTO X (ID, NAME, NUMBER)
> SELECT (10, CAST(null AS VARCHAR(100)), CAST(null AS INTEGER))
> FROM SYSIBM.SYSDUMMY1
> When actually Derby could infer the types of null. Type inference is done
> sometimes. This will work:
> INSERT INTO X (ID, NAME, NUMBER)
> VALUES (10, null, null)
> What do you guys think?
--
This message is automatically generated by JIRA.
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
