default value when not specifying it, column metadata COLUMN_DEF

[tsukasa]

Hi, if I do:

create table test
( id integer default null, name varchar(250) )

when using the ResultSetMetaData it returns an empty string for the
COLUMN_DEF value of the name column.
Normally when the default value of a column is NULL, h2 returns NULL,
and when the default value is an empty string, h2 returns '' (two
colons, not an empty string). Am sorry my ignorance, but wouldn't be
better to return NULL? (it behaves like null anyway when I insert.)
Thank you.

[tsukasa]

I mean.. DatabaseMetaData, off course. This is with h2 1.0.63.

[Thomas Mueller]

Hi,

The H2 Console can display DatabaseMetaData, however it is not documented. This script works in the H2 Console only, and will call DatabaseMetaData.getColumns:

DROP TABLE IF EXISTS TEST;
CREATE TABLE TEST(
A VARCHAR(255) DEFAULT '',
B VARCHAR(255) DEFAULT 'X',
C VARCHAR(255) DEFAULT NULL);
@LIST @COLUMNS null null TEST;

The result is:

H2:
Column Data
Row # 1
COLUMN_DEF ''
Row # 2
COLUMN_NAME B
COLUMN_DEF 'X'
Row # 3
COLUMN_NAME C
COLUMN_DEF NULL

PostgreSQL:
@LIST @COLUMNS null null test;
Column Data
Row # 1
COLUMN_NAME a
COLUMN_DEF ''::character varying
Row # 2
COLUMN_NAME b
COLUMN_DEF 'X'::character varying
Row # 3
COLUMN_NAME c
COLUMN_DEF null

MySQL:
Column Data
Row # 1
COLUMN_NAME A
COLUMN_DEF
Row # 2
COLUMN_NAME B
COLUMN_DEF X
Row # 3
COLUMN_NAME C
COLUMN_DEF null

So MySQL does what you suggest (display the raw value, without quotes), however what about computed expressions? H2 supports this as well (other databases don't):

DROP TABLE IF EXISTS TEST;
CREATE TABLE TEST(A VARCHAR(255) DEFAULT 'X' || RAND());
@LIST @COLUMNS null null TEST;

Column Data
Row # 1
COLUMN_NAME A
COLUMN_DEF ('X' || RAND())

So I think it makes sense to keep the current behavior.

Regards,
Thomas

[tsukasa]

I agree, am sorry what I meant was

DROP TABLE IF EXISTS TEST;
CREATE TABLE TEST(
A VARCHAR(255) DEFAULT '',

  B VARCHAR(255),

  C VARCHAR(255) DEFAULT NULL);
@LIST @COLUMNS null null TEST;

(default value for B is not specified)

The result is:

H2:
Column          Data  
Row #   1
COLUMN_DEF      ''
Row #   2
COLUMN_NAME     B
COLUMN_DEF      

Row #   3
COLUMN_NAME     C
COLUMN_DEF      NULL

Here COLUMN_DEF for the second column is empty.
If I insert a row like this INSERT INTO TEST (A) VALUES (''), the
value for column B gets a null value. I just supossed it would be
better for H2 to automatically put a NULL in the metadata (the default
default-value) but really I don't know if that would be what you
really should do, as even the behaviour of hsqldb is similar, normally
if the default value is null, it returns NULL for the column_def, and
if the default value is not defined with DDL, it returns null (well H2
gives an empty string instead of null that's the only difference).
This is not very important anyway. Thanks.

[Thomas Mueller]

Hi,

You are right, all other databases return SQL 'null' if there is no
column default defined. The next release of H2 will do the same.

Regards,
Thomas