One part of this issue, making Derby handle "LIKE 'ab' || '%'" the same way as it handles "LIKE 'ab%'", should be possible to solve using the constant folding mechanism added in
I've attached a patch (fold-constants.diff) that implements an override of evaluateConstantExpressions() for ConcatentationOperatorNode. Unfortunately, this change alone didn't make LIKE use indexes, because the constant folding happens right before the statement optimization phase. LIKE decides whether to add extra scan predicates earlier than that, during the bind phase. I therefore made ConcatenationOperatorNode's bindExpression() method invoke the constant folding as well, and then it seems to be happening early enough so that LikeEscapeOperatorNode is able to pick it up.
The patch also contains a test case that verifies that indexes are used. I haven't run any other tests on the patch yet.