My case is this:
If all the columns of select do not have a clear and definate type then
tables and views based
on that select do not have clear and definate types and we are all
heading for disaster.
So the basic problem to fix in here is to require that all columns
produced by any select have a
clear and definate type which is not null, because null is not a type.
Null value does not have a type and that's why we should use expression
cast(null as type) to give
the column (and the null value) a definate type.
> I will add a feature request: "Support NULL with a type: @meta CALL
> CAST(NULL AS INT) should return INT." Patches are welcome.
> see the ValueExpression.java
Thanks for these kind of pointers.
Even if I don't have time now to fix this, these kind of pointers stay
in the mail
and will encourage fixing when the time presents itself.
- rami
On 28.5.2010 19:45, Thomas Mueller wrote:
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.
