Hi,
I completely agree with the appreciation of Rami and the current
implementation generates annoying sql compatibility issues.
In SQL there is a central feature named "Type Coercion" expresed by the
CAST sentence.
In RDBMS you have a set of supported data types and a matrix of valid
conversion between every data type to each other.
Since a column of a ResultSet of any data type can be null, hence the
NULL value-type is a convertible value to any other type.
>From the beginning of SQL was discussed that a column can contain null
values. To support this feature defines NULL as a data type that can
only have a NULL value.
And a column of any type can contain the value NULL to indicate the lack
of data value *but can't be of null data type.*
Google this "sql standard type coercion" and you can see that almost all
databases implement it in standard way.
Next use case in H2 end with column "n" with NULL type and that's wrong
and out of SQL without discussion :
create table tableA as select cast( null as int) as n, cast( 2000
as int) as year ;
Regards,
Dario
> What is your use case? In H2, NULL doesn't have a type. CAST(NULL AS
> INT) currently has no effect. A workaround is:
>
> CREATE TABLE SNAFU(SNAFU_COL INT) AS SELECT CAST(NULL AS INT) AS
> SNAFU_COL FROM FOO;
>
> However this only works for tables, not for views. For views, you
> could use another workaround:
>
> create table dummy(x int) as select null;
> CREATE VIEW BAR AS SELECT (SELECT X FROM DUMMY) AS BAR_COL FROM FOO;
>
> I will add a feature request: "Support NULL with a type: @meta CALL
> CAST(NULL AS INT) should return INT." Patches are welcome. It you want
> to implement it yourself, see the ValueExpression.java (currently it
> returns the type of the value; it could have it's own
> type/precision/scale optionally, for example when created from CAST,
> which is implemented in Function.java).
>
> Regards,
> Thomas
>
> On Wed, May 26, 2010 at 8:48 PM, Rami Ojares <[email protected]> wrote:
>
>> Here's the test case
>>
>> CREATE TABLE FOO(ID INT);
>> CREATE VIEW BAR AS SELECT CAST(NULL AS INT) AS BAR_COL FROM FOO;
>> CREATE TABLE SNAFU AS SELECT CAST(NULL AS INT) AS SNAFU_COL FROM FOO;
>> SELECT * FROM FOO;
>> SELECT * FROM BAR;
>> SELECT * FROM SNAFU;
>>
>> All seems to be ok except that view BAR and table SNAFU report as their type
>> NULL.
>> It should be INT.
>>
>> But there is more.
>> Close the database and open it.
>> Then make ANY select and you will get
>>
>> Cause: org.h2.jdbc.JdbcSQLException: Unknown data type: "NULL"; SQL
>> statement:
>> CREATE CACHED TABLE PUBLIC.SNAFU(
>>
>>
>> You have just managed to make your database UNREADABLE!
>> Reason is the NULL datatype that h2 has erroneously set as the datatype of
>> SNAFU_COL.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
