Support for expressions inside Create Index

[lukasz-jarocki-sonarsource]

Hi,

this issue has been previously discussed here: https://github.com/sagarswathi/h2database/issues/196

In short, this kind of index can be created in other popular databases: CREATE INDEX my_index_name ON my_table (COALESCE( column1,column2),column2)

However on H2 it fails with the syntax error:

Syntax error in SQL statement "CREATE INDEX my_index_name ON my_table (COALESCE[*]( column1,column2),column2)"; expected "ASC, DESC, NULLS, ,, )"; SQL statement:
CREATE INDEX my_index_name ON my_table (COALESCE( column1,column2),column2) [42001-210]

Are there any plans to implement expressions inside create index?

[katzyn]

Actually it was discussed 12 years ago on the closed Google Code service, there is an archive copy with replaced usernames: https://code.google.com/archive/p/h2database/issues/196

H2 doesn't have a roadmap any more, but contributions are welcome, as usual.

This feature is complex enough. It isn't that hard to include these expressions into index, but it is only a small part of the task. When index condition are created, expressions and their parts in the query need to take them into account. Index-sorted optimization need changes too. And query planner/optimizer also may need some changes.

The usual workaround is to create a generated column:

ALTER TABLE MY_TABLE ADD G1 GENERATED ALWAYS AS (COALESCE(COLUMN1, COLUMN2));
CREATE INDEX MY_INDEX_NAME ON MY_TABLE(G1, COLUMN2);

Now queries like

SELECT * FROM MY_TABLE WHERE G1 = ? AND COLUMN2 = ?

will be able to use this index. It is important to use G1 instead of its expression to achieve this optimization.