Hi, 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( > SNAFU_COL NULL > ) [50004-134] > at org.h2.message.DbException.getJdbcSQLException(DbException.java:316) > at org.h2.message.DbException.get(DbException.java:167) > at org.h2.message.DbException.get(DbException.java:144) > at org.h2.command.Parser.parseColumn(Parser.java:3411) > at org.h2.command.Parser.parseColumnForTable(Parser.java:3265) > at org.h2.command.Parser.parseCreateTable(Parser.java:4725) > at org.h2.command.Parser.parseCreate(Parser.java:3517) > at org.h2.command.Parser.parsePrepared(Parser.java:314) > at org.h2.command.Parser.parse(Parser.java:278) > at org.h2.command.Parser.parse(Parser.java:250) > at org.h2.command.Parser.prepare(Parser.java:199) > at org.h2.engine.Session.prepare(Session.java:405) > at org.h2.engine.Session.prepare(Session.java:392) > at org.h2.engine.MetaRecord.execute(MetaRecord.java:56) > at org.h2.engine.Database.open(Database.java:584) > at org.h2.engine.Database.openDatabase(Database.java:204) > at org.h2.engine.Database.<init>(Database.java:199) > at org.h2.engine.Engine.openSession(Engine.java:56) > at org.h2.engine.Engine.openSession(Engine.java:141) > at org.h2.engine.Engine.getSession(Engine.java:120) > at org.h2.server.TcpServerThread.run(TcpServerThread.java:118) > at java.lang.Thread.run(Thread.java:619) > > 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. > > Cheers, > - Rami > > -- > 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. > > -- 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.
