db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dag H. Wanvik (JIRA)" <j...@apache.org>
Subject [jira] Updated: (DERBY-5006) Handle NULL literals like any other database
Date Mon, 07 Feb 2011 17:07:57 GMT

     [ https://issues.apache.org/jira/browse/DERBY-5006?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Dag H. Wanvik updated DERBY-5006:
---------------------------------

    Bug behavior facts:   (was: [Deviation from standard])

Removing the flag "deviation from standard".


> 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

        

Mime
View raw message