Ability to use '+' for string concatenation

[GoogleCodeExporter]

For a compatibilty with the MS SQL Server and HSQLDB it is required to
support a string concatenations using + sign, such as 'a' + 'b'. Attached
see a patch, which aim is to enable this behaviour when mode MSSQLServer or
HSQLDB is set. Not sure about the instanceof operator in
Operation.getValue() method though.
Best Regards,
Stepan

Original issue reported on code.google.com by stepan1...@gmail.com on 4 Mar 2010 at 11:35

Attachments:

• h2-svn.patch

[GoogleCodeExporter]

Unfortunately the above patch works only partly, it does not work for jdbc 
prepared
statements like 
"(SUBSTRING ( 'abcdefghefghjkl' , 1 , 8 ) + SUBSTRING ( 'abcdefghefghjkl' , 10 
, 3) )
= ?"
Do someone have a clue where could I find the part handling '+' in the h2 
source code
for jdbc prepared statements?
Stepan

Original comment by stepan1...@gmail.com on 5 Mar 2010 at 1:25

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

Added more tests, last two are failing

Original comment by stepan1...@gmail.com on 15 Mar 2010 at 7:31

Attachments:

• TestCompatibility.java.patch

[GoogleCodeExporter]

Thanks! 

The patch (h2-svn.patch) uses "instanceof ValueString", which is not very object
oriented. A better approach is to add a method ValueString.add(). The side 
effect is
that SUM(<string expression>) would concatenate all names, which may or may not 
be
desired. Also, there would be no way to use Mode.allowPlusForStringConcat.

About ?+?: Is this an important use case for you? Does it work with MS SQL 
Server? It
does somewhat work for a few databases (for PostgreSQL and MySQL when using 
decimal
values), but for example Apache Derby will throw the exception "It is not 
allowed for
both operands of '+' to be ? parameters." HSQLDB throws the exception "data 
type cast
needed for parameter or null literal".

The test (SUBSTRING ( 'abcdefghefghjkl' , 1 , 8 ) + SUBSTRING ( 
'abcdefghefghjkl' ,
10 , 3) ) fails because the precision of the PLUS operation is 
max(precision(left),
precision(right)). Maybe it's better to change the operation type from PLUS to
CONVERT once the parser detects that it's a concatenation.

Original comment by thomas.t...@gmail.com on 20 Mar 2010 at 11:57

[GoogleCodeExporter]

Here is my patch:
http://h2database.com/p.html#abe857c772a62fa849dbc225ce29ff0d

Original comment by thomas.t...@gmail.com on 20 Mar 2010 at 12:04

[GoogleCodeExporter]

Hi,

Your patch is now committed to the trunk. I made a few changes however.

Regards,
Thomas

Original comment by thomas.t...@gmail.com on 20 Mar 2010 at 3:53

• Changed state: Accepted

[GoogleCodeExporter]

Implemented in version 1.2.132.

Original comment by thomas.t...@gmail.com on 21 Mar 2010 at 3:12

• Changed state: Fixed