Hello folks. Recently I trying to investigate why when the query, i.e. «SELECT * FROM T1 WHERE ID IN (SELECT T1_ID FROM T2), is executed locally, subquery evaluated, even if result is the same. I noticed, that in mentioned in subj (.ConnectionManager#DEFAULT_DB_OPTIONS), parameter OPTIMIZE_REUSE_RESULT explicitly set to 0. This value disable internal H2 optimization (see details here org.h2.command.dml.Query#query(int, org.h2.result.ResultTarget) and lead the mentioned above query to be ineffective.
I cannot understand why this decision was made. Also I don’t find any evidence that setting OPTIMIZE_REUSE_RESULT to 1 could break something. Unfortunatelly, it is impossible to change this behavior per Session without forking H2, because this flag is set in org.h2.engine.Database. Let’s discuss possible caveats in enabling this optimization by default in DEFAULT_DB_OPTIONS.
