H2 user-defined function is called many times

Question

I'm using the h2 v1.3.176.
I have user-defined function which execute RECURSIVE query.

public static ResultSet getChildCategories(Connection connection, long categoryId) throws SQLException {
    String sql = 
            "WITH RECURSIVE r(CATEGORY_ID, PARENT_ID) AS (\n" +
            "    SELECT   CATEGORY_ID\n" +
            "            ,PARENT_ID\n" +
            "    FROM     CATEGORY\n" +
            "    WHERE    CATEGORY_ID = " + categoryId + "\n" +
            "    UNION ALL\n" +
            "    SELECT   CATEGORY.CATEGORY_ID\n" +
            "            ,CATEGORY.PARENT_ID\n" +
            "    FROM     CATEGORY, r\n" +
            "    WHERE    CATEGORY.PARENT_ID = r.CATEGORY_ID\n" +
            ")\n" +
            "SELECT CATEGORY_ID FROM r";
    ResultSet resultSet = connection.createStatement().executeQuery(sql);
    SimpleResultSet rs = new SimpleResultSet();
    rs.addColumn("CATEGORY_ID", Types.INTEGER, 12, 0);
    try {
        while(resultSet.next()) {
            rs.addRow(resultSet.getLong(1));
        }
    } finally {
        resultSet.close();
    }
    return rs;
}

I have registered this function by following SQL.

create alias GET_CHILD_CATEGORIES for "com.myapp.db.function.Functions.getChildCategories";

My problem is the getChildCategories function will be called many times when I execute the following query.

SELECT DISTINCT  B.BOOK_ID
                ,B.SERIES_ID
                ,B.TITLE
                ,B.ISBN
                ,B.VOLUME
                ,(
                    SELECT  MAX(SAME_SERIES.VOLUME)
                    FROM    BOOK SAME_SERIES
                    WHERE   SAME_SERIES.SERIES_ID = B.SERIES_ID
                    AND     SAME_SERIES.VOLUME IS NOT NULL
                ) AS VOLUME_COUNT
                ,B.PAGE_COUNT
                ,B.FILE_PATH
                ,B.SORTABLE_FILE_NAME
                ,B.SIZE
                ,B.HASH
                ,B.COVER_IMAGE_TYPE
                ,B.COVER_PAGE_NO
                ,B.COVER_LARGE_IMAGE_URL
                ,B.COVER_SMALL_IMAGE_URL
                ,B.COVER_CROP_COORD
                ,B.IS_ENCRYPT
                ,B.PUBLISHER_ID
                ,B.PUBLISHED_DATE
                ,B.CREATION_TIME
                ,B.LAST_MODIFIED_TIME
                ,B.NOTE
                ,B.IS_ISBN_SEARCH
                ,S.CATEGORY_ID
                ,S.TITLE
                ,BA.AUTHOR_ID
                ,BT.TAG_ID
FROM             BOOK AS B
INNER JOIN       SERIES AS S ON S.SERIES_ID = B.SERIES_ID
LEFT OUTER JOIN  BOOK_TAG AS BT ON BT.BOOK_ID = B.BOOK_ID
LEFT OUTER JOIN  BOOK_AUTHOR AS BA ON BA.BOOK_ID = B.BOOK_ID
WHERE 
(
    S.CATEGORY_ID IN (SELECT CATEGORY_ID FROM GET_CHILD_CATEGORIES(106))
    And 
    S.IS_COMPLETION = 1
)
ORDER BY  BA.AUTHOR_ID

Why do many times would be called this function?

Answer 1

Extracted from H2 documentation

A function that returns a result set can be used like a table. However, in this case the function is called at least twice: first while parsing the statement to collect the column names (with parameters set to null where not known at compile time). And then, while executing the statement to get the data (maybe multiple times if this is a join). If the function is called just to get the column list, the URL of the connection passed to the function is jdbc:columnlist:connection. Otherwise, the URL of the connection is jdbc:default:connection.

The first calls are only to retrieve the resultset column types. Then you have to check if the connection url is "jdbc:columnlist:connection". If true you have to return an empty result set with column list.

The connection url test is:

connection.getMetaData().getURL().equals("jdbc:columnlist:connection");