Feature request: Add support for indexes based on an expression

[GoogleCodeExporter]

PostgreSQL supports the following syntax, allowing both a column name or an
expression:

CREATE INDEX name ON table { column | ( expression ) }

According to the documentation, "An index field can be an expression
computed from the values of one or more columns of the table row. This
feature can be used to obtain fast access to data based on some
transformation of the basic data. For example, an index computed on
upper(col) would allow the clause WHERE upper(col) = 'JIM' to use an index."

This is what I'm seeing when I try to create an index with an expression,
using h2-1.2.133.jar on Linux with Sun JDK 1.6:

org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "
CREATE INDEX IDX_FPDATETRUNC
  ON S_DETAILS

  (DATE_TRUNC([*]'day'::TEXT, FIRST_DATE)) "; expected ")"; SQL statement:

CREATE INDEX idx_fpdatetrunc
  ON s_details

  (date_trunc('day'::text, first_date)) [42001-133]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:316)
    at org.h2.message.DbException.get(DbException.java:167)
    at org.h2.message.DbException.getSyntaxError(DbException.java:192)
    at org.h2.command.Parser.read(Parser.java:2547)
[...]
    at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:151)

The only workaround so far is to drop using such indexes. This is not a big
issue, as we use H2 for test cases, not (yet) for large datasets.

Original issue reported on code.google.com by nicolas....@gmail.com on 6 May 2010 at 8:46

[GoogleCodeExporter]

This feature has been added to the roadmap at
http://www.h2database.com/html/roadmap.html under "Issue 196: Function based
indexes". Patches are welcome of course!

Original comment by thomas.t...@gmail.com on 8 May 2010 at 1:17

• Changed state: InRoadmap

[GoogleCodeExporter]

By the way a workaround is to use a computed column:
http://www.h2database.com/html/features.html#computed_columns

Original comment by thomas.t...@gmail.com on 8 May 2010 at 1:18

[GoogleCodeExporter]

One way to implement this would be to copy and modify 
org.h2.index.PageBtreeIndex so that it stored computed column(s).
Actually, it looks like the changes to the optimiser are a lot trickier than 
the changes to the storage classes.

Original comment by noelgrandin on 7 Mar 2011 at 12:01

[GoogleCodeExporter]

I hope this could be added 'on top' of the current features instead of having 
to change the low level index mechanism. 

How to do that: for the index expression, add a hidden "computed column" and 
index this one. About computed columns, see: 
http://www.h2database.com/html/features.html#computed_columns

> the changes to the optimizer are a lot trickier

Yes, the optimizer would need to be improved. The easiest way is probably to 
compare the SQL snipped of the expression with the SQL snipped of the index. 
Such a mechanism is already used in one case: when trying to match ORDER BY 
columns with select expressions, so that the expression only needs to be 
calculated once. It's actually required for one case:

-- legal:
SELECT DISTINCT ID+10 FROM TEST ORDER BY ID+10;

-- illegal:
SELECT DISTINCT ID+10 FROM TEST ORDER BY ID+11;
-- PostgreSQL: for SELECT DISTINCT, ORDER BY expressions must appear in select 
list
-- H2: Order by expression "(ID + 11)" must be in the result list in this case
-- HSQLDB: invalid ORDER BY expression
-- Apache Derby and MySQL allows it, but I think that's a bug

Anyway, that's where H2 already matches sub-expressions (Query.initOrder).

Original comment by thomas.t...@gmail.com on 11 Mar 2011 at 12:30