[ https://issues.apache.org/jira/browse/DERBY-5006?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12991470#comment-12991470 ]
Dag H. Wanvik commented on DERBY-5006:
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.
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
b) "Special", NULL allowed if type can be inferred from context:
cf this these productions down to NULL:
| ::= NULL
The special version is used as part of the INSERT syntax:
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 above), however, SQL
SELECT [ ]