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] Issue Comment Edited: (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:comment-tabpanel&focusedCommentId=12991470#comment-12991470
] 

Dag H. Wanvik edited comment on DERBY-5006 at 2/7/11 5:07 PM:
--------------------------------------------------------------

I think Derby is adhering to the SQL specification here, which does
specify a difference in the way type inference is handled for the VALUES
case and the SELECT case, outlined nelow in some detail for ease of
reference if we decide to look at this. I agree it is possible to relax
this for special cases, though. Unfortunately, fiddling with the type
apparatus of a language as complex as SQL is not trivial, as side
effects must be analyzed and understood in order to keep the language
sub/super-set we support be internally consistent and understandable. So
far, the Derby charter has been to stick closely to the standard where
possible/meaningful, since this relieves us Derby hackers from being
language designers as well ;-)

This is not to say that we would never considering extending or breaking
the standard, but there should be a good reason to do so.

The fact that other databases allow more lenient interpretation of the
standard is, of course, one argument in favor of making such a change.

Also, not that SQL NULL is a different beast than Java's null. In SQL,
it is part of the 3-value logic and basically means "don't know", cf.
http://en.wikipedia.org/wiki/Null_%28SQL%29

Dag

----------
Section 6.5 in SQL 2003, vol 2, only allows an untyped NULL in certain
cases, c.f. the two versions provided by the standard for the "table
value constructor" clause:

a) "Normal", i.e. NULL not allowed in this production

        <table value constructor> ::=
          VALUES <row value expression list>

b) "Special", NULL allowed if type can be inferred from context:

        <contextually typed table value constructor> ::=
          VALUES <contextually typed row value expression list>

   cf this these productions down to NULL:

        <contextually typed row value expression> ::=
           <row value special case>
         | <contextually typed row value constructor>
        
        <contextually typed row value constructor> ::=
                :
                | <contextually typed value specification>
                :
        
        <contextually typed value specification> ::=
            <implicitly typed value specification>
          | <default specification>
                
        <implicitly typed value specification> ::=
           <null specification>
         | <empty specification>
        
        <null specification> ::= NULL
        
The special version is used as part of the INSERT syntax:

        <insert columns and source> ::=
          <from subquery>
        | <from constructor>
        | <from default>

        <from constructor> ::=
          [ <left paren> <insert column list> <right paren> ]
          [ <override clause> ]
          <contextually typed table value constructor>

So, we see that in an insert context, the table value constructor can be
contextually typed, and the CAST can be omitted for NULL.

In the SELECT case (derivable from <from subquery> above), however, SQL
specifies:

        <query specification> ::=
          SELECT [ <set quantifier> ] <select list> <table expression>
        
        <select list> ::=
        <asterisk>
        | <select sublist> [ { <comma> <select sublist> }... ]
        
        <select sublist> ::=
        <derived column>
        | <qualified asterisk>
        
        <derived column> ::= <value expression> [ <as clause> ]
        
        <value expression> ::=
        <common value expression>
        | <boolean value expression>
        | <row value expression>
        
        <row value expression> ::=
        <row value special case>
        | <explicit row value constructor>
        
        <explicit row value constructor> ::=
        <left paren> <row value constructor element> <comma>
        <row value constructor element list> <right paren>
        | ROW <left paren> <row value constructor element list> <right paren>
        | <row subquery>
        
        <row value constructor element list> ::=
        <row value constructor element> [ { <comma> <row value constructor
element> }... ]
        
        <row value constructor element> ::= <value expression>

Note that here it is used "row value constructor element", described in
section "7.1 <row value constructor>". 

*Not* being used is notably the NULL accepting version of this
production, i.e.

        <contextually typed row value constructor element>

which is defined as

        <contextually typed row value constructor element> ::=
          <value expression>
        | <contextually typed value specification>

which, in addition to <value expression> also has the <contextually
typed value specification> we saw above in the VALUES context.

The consequence is that a NULL appearing in a select list would not be
implicitly typed.  In conclusion, I think Derby toes the
standard in this case.


      was (Author: dagw):
    I think Derby is adhering to the SQL specification here, which does
specify a difference in the way type inference is handled for the VALUES
case and the SELECT case, outlined nelow in some detail for ease of
reference if we decide to look at this. I agree it is possible to relax
this for special cases, though. Unfortunately, fiddling with the type
apparatus of a language as complex as SQL is not trivial, as side
effects must be analyzed and understood in order to keep the language
sub/super-set we support be internally consistent and understandable. So
far, the Derby charter has been to stick closely to the standard where
possible/meaningful, since this relieves us Derby hackers from being
language designers as well ;-)

This is not to say that we would never considering extending or breaking
the standard, but there should be a good reason to do so.

The fact that other databases allow more lenient interpretation of the
standard is, of course, one argument in favor of making such a change.

Also, not that SQL NULL is a different beast than Java's null. In SQL,
it is part of the 3-value logic and basically means "don't know", cf.
http://en.wikipedia.org/wiki/Null_%28SQL%29

Dag

----------
Section 6.5 in SQL 2003, vol 2, only allows an untyped NULL in certain
cases, c.f. the two versions provided by the standard for the "table
value constructor" clause:

a) "Normal", i.e. NULL not allowed in this production

	<table value constructor> ::=
	  VALUES <row value expression list>

b) "Special", NULL allowed if type can be inferred from context:

	<contextually typed table value constructor> ::=
	  VALUES <contextually typed row value expression list>

   cf this these productions down to NULL:

	<contextually typed row value expression> ::=
	   <row value special case>
	 | <contextually typed row value constructor>
	
	<contextually typed row value constructor> ::=
	        :
		| <contextually typed value specification>
	        :
	
	<contextually typed value specification> ::=
	    <implicitly typed value specification>
	  | <default specification>
		
	<implicitly typed value specification> ::=
	   <null specification>
	 | <empty specification>
	
	<null specification> ::= NULL
	
The special version is used as part of the INSERT syntax:

	<insert columns and source> ::=
	  <from subquery>
	| <from constructor>
	| <from default>

	<from constructor> ::=
	  [ <left paren> <insert column list> <right paren> ]
	  [ <override clause> ]
	  <contextually typed table value constructor>

So, we see that in an insert context, the table value constructor can be
contextually typed, and the CAST can be omitted for NULL.

In the SELECT case (derivable from <from subquery> above), however, SQL
specifies:

	<query specification> ::=
	  SELECT [ <set quantifier> ] <select list> <table expression>
	
	<select list> ::=
	<asterisk>
	| <select sublist> [ { <comma> <select sublist> }... ]
	
	<select sublist> ::=
	<derived column>
	| <qualified asterisk>
	
	<derived column> ::= <value expression> [ <as clause> ]
	
	<value expression> ::=
	<common value expression>
	| <boolean value expression>
	| <row value expression>
	
	<row value expression> ::=
	<row value special case>
	| <explicit row value constructor>
	
	<explicit row value constructor> ::=
	<left paren> <row value constructor element> <comma>
	<row value constructor element list> <right paren>
	| ROW <left paren> <row value constructor element list> <right paren>
	| <row subquery>
	
	<row value constructor element list> ::=
	<row value constructor element> [ { <comma> <row value constructor element>
}... ]
	
	<row value constructor element> ::= <value expression>

Note that here it is used "row value constructor element", described in
section "7.1 <row value constructor>". 

*Not* being used is notably the NULL accepting version of this
production, i.e.

	<contextually typed row value constructor element>

which is defined as

	<contextually typed row value constructor element> ::=
	  <value expression>
	| <contextually typed value specification>

which, in addition to <value expression> also has the <contextually
typed value specification> we saw above in the VALUES context.

The consequence is that a NULL appearing in a select list would not be
implicitly typed.  In conclusion, I think Derby toes the
standard in this case.

  
> 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