H2 doesn't follow SQL-standard for string concatenation
Duvel
Today I found out that Oracle didn't follow the SQL-standard for
string concatenation and thus creating unexpected behaviour in our
application. I immediately tested this as well in other databases like
SQL Server, PostgreSQL and H2. Both SQL Server and PostgreSQL work
according the SQL-standard, but H2 also didn't follow the SQL-
standard.
If you concatenate a NULL-value with a string, this should return
NULL. H2 returns in this case a string, which is wrong according the
SQL-standard. It is easy to test with this statement which should
return null and not 'aap':
select null || 'aap'
Some more info about string concatenation in SQL:
http://troels.arvin.dk/db/rdbms/#functions-concat
Kind regards,
Remco Schoen
Thomas Mueller
H2 supports both depending on the compatibility mode. See
http://www.h2database.com/html/features.html#compatibility
"Concatenation with NULL results in NULL. Usually, NULL is treated as
an empty string if only one of the operands is NULL, and NULL is only
returned if both operands are NULL."
To use this mode, add the following to the database URL: ;MODE=HSQLDB
(or PostgreSQL).
Is it this really how Microsoft SQL Server behaves by default? If yes
I will add the flag to the MSSQLServer mode as well.
Regards,
Thomas
Duvel
I'm not sure if it is the current default in MS SQL Server, but
documentation says that it is going to be default in the future. In
all our test-servers it seems to be the default (2000, 2005 and 2008).
Information about the setting:
http://msdn.microsoft.com/en-us/library/ms176056.aspx
And the qoute:
"Important:
In a future version of SQL Server CONCAT_NULL_YIELDS_NULL will always
be ON and any applications that explicitly set the option to OFF will
generate an error. Avoid using this feature in new development work,
and plan to modify applications that currently use this feature."
So it seems they are going to support the ANSI-SQL behaviour always in
the future.
For our use-case we will just note it as a deviation of the SQL-
standard and we will create a work-around for it in our templating
system. Setting it in the url is too user-dependent.
Greetings,
Remco
Nils
ambiguities? Setting compatibility mode to a certain RDBMS
implementation may bring along other, undesirable, effects.
Also, any reason why H2 in its default setting does not strive to be
ANSI SQL compliant?
Thomas Mueller
> Would it be possible in the future to have better granularity on these
> ambiguities?
You are right, I will add a system property to set the default
behavior in the next release.
> Also, any reason why H2 in its default setting does not strive to be
> ANSI SQL compliant?
I didn't know this is the ANSI standard. I will change the default in
version 1.2 (should be beta in about one month). Please tell me if you
find other things that are not ANSI-compliant.
Regards,
Thomas
Nils
wrote:
> behavior in the next release.
NULL to be unique, which I don't think it is by H2 default, so I would
appreciate a system property for that.
Also of interest would be system property (properties?) for the
various ways to produce generated keys. I need generated columns that
are part of the PK, which does not work with IDENTITY, because it
assumes it is the PK.
Thanks for the great work.
Nils
Thomas Mueller
>> You are right, I will add a system property to set the default
>> behavior in the next release.
There is now http://www.h2database.com/javadoc/org/h2/constant/SysProperties.html#h2.nullConcatIsNull
> Also of interest would be system property (properties?) for the
> various ways to produce generated keys. I need generated columns that
> are part of the PK, which does not work with IDENTITY, because it
> assumes it is the PK.
Then use AUTOINCREMENT, or create a sequence and use it as the DEFAULT
clause in the column. I don't see how a system property would help
here.
Regards,
Thomas