View does not support varchar concatenation

[GoogleCodeExporter]

What steps will reproduce the problem?
(simple SQL scripts or simple standalone applications are preferred)
1. --Create Simple Table
create table T1 (pid int NOT NULL PRIMARY KEY, firstname varchar(64),
lastname varchar(32));
2. --Insert an Entry
insert into T1(pid, firstname, lastname) values(1, 'John', 'Smith')
3. -- Create a view on Table: Combine firstname and lastname to form full name
create view v1 as (select pid as id, firstname + '.' + lastname as name
from t1);
4.select * from v1

What is the expected output? What do you see instead?
The output should return one row [1, 'John.Smith']
Instead it throws an error: org.h2.jdbc.JdbcSQLException: Feature not
supported: "VARCHAR"; SQL statement:
select * from V1 [50100-135]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:327)
    at org.h2.message.DbException.get(DbException.java:167)
    at org.h2.message.DbException.get(DbException.java:144)
    at org.h2.message.DbException.getUnsupportedException(DbException.java:202)
    at org.h2.value.Value.throwUnsupportedExceptionForType(Value.java:980)
    at org.h2.value.Value.add(Value.java:435)
    at org.h2.expression.Operation.getValue(Operation.java:123)
    at org.h2.expression.Operation.getValue(Operation.java:95)
    at org.h2.expression.Alias.getValue(Alias.java:35)
    at org.h2.command.dml.Select.queryFlat(Select.java:493)
    at org.h2.command.dml.Select.queryWithoutCache(Select.java:554)
    at org.h2.command.dml.Query.query(Query.java:233)
    at org.h2.index.ViewIndex.find(ViewIndex.java:191)
    at org.h2.index.IndexCursor.find(IndexCursor.java:131)
    at org.h2.table.TableFilter.next(TableFilter.java:253)
    at org.h2.command.dml.Select.queryFlat(Select.java:487)
    at org.h2.command.dml.Select.queryWithoutCache(Select.java:554)
    at org.h2.command.dml.Query.query(Query.java:233)
    at org.h2.command.CommandContainer.query(CommandContainer.java:80)
    at org.h2.command.Command.executeQuery(Command.java:132)
    at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:172)
    at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:151)
...

What version of the product are you using? On what operating system, file
system, and virtual machine?
I have tried h2-1.2.135 and h2-1.2.126 (Running in-memory mode)

Do you know a workaround?
No

How important/urgent is the problem for you?
We are considering migrating our code from hsqldb to h2, however we are not
able to port current codebase due to this issue.

In your view, is this a defect or a feature request?

Please provide any additional information below.

Original issue reported on code.google.com by jack...@gmail.com on 17 May 2010 at 4:17

[GoogleCodeExporter]

Hi,

The 'standard' way to concatenate text within SQL is to use ||. Most databases
(except Microsoft SQL Server) don't support + for text. H2 does support it, 
however
only when using the MS SQL Server compatibility mode: 

http://h2database.com/html/features.html#compatibility

To use it, append ;MODE=MSSQLServer to the database URL. Example URL: 
jdbc:h2:~/test;MODE=MSSQLServer

See also: http://h2database.com/html/grammar.html#operand

Regards,
Thomas

Original comment by thomas.t...@gmail.com on 18 May 2010 at 6:23

• Changed state: WontFix