@zheguang This is a nice optimization and the right direction, thanks.

About these two cases:

ILIKE: column needs to be wrapped in a LOWER function

It only makes sense (in my view) to implement this case if the speedup by the range search is bigger than the slowdown by the LOWER wrapper. Before implementing it, I can recommend testing the speed difference in a synthetic SQL test.

NOT (I)LIKE: add negation to the rewritten range expression

So NOT can be treated by simply inverting the range, right? For example, LT would become GE?

According to your comment, the overall speedup for TPC-H is indeed small but that is expected. ClickBench unfortunately doesn't have queries which would benefit from the optimization, so no speedup is expected there as well. Anyways, I am pretty sure that LIKE 'pattern%' is a common theme in real world use cases, so I would be happy to merge this PR. Will try to find someone who can review further.

Workflow [PR], commit [b28218b]

Summary: ❌

Wouldn't it be simpler to rewrite perfect prefix like as startsWith? We can also extend to endsWith.

Wouldn't it be simpler to rewrite perfect prefix like as startsWith? We can also extend to endsWith.

Thanks for the suggestion. My understanding is a range condition works better with an index, for example currently for primary keys.

LGTM however:

1. I would request the recursion change if possible.
2. I would like to see an example where this brings a noticeable performance benefit to ensure that we are doing everything correctly.

Thanks for the suggestion! So for recursion, I lean keeping. My thinking is, for one, this specific usage is in unit test only, and it's almost identical to one in another unit test, https://github.com/ClickHouse/ClickHouse/pull/85920/files#diff-508f54672d863c147616450f9419522b9a267e3ec04f352674a1085ff220408a. (So I ended up with a minor refactoring). As the test cases appear to have short expressions, I think the depth limited parsing currently in place is sufficient to prevent a stack overflow?

Incidentally I tracked down the query analyzer's InDepthQueryTreeVisitorWithContext which appears to use a similar non-tail-call recursion as well:

Conceptually though, rewriting this unit-test recursion (and also this query analyzer recursion) would entail a stack that grows until a subtree is fully resolved.

@Ergus Could you double-check / review this PR, please?

Still would like to see a simple benchmark comparing performance with and without this optimization enabled

"tests/performance/like_perfect_affix_rewrite.xml" is doing exactly this, no?

  <!-- Test prefix matching rewrite -->
  <query>
      SELECT count() FROM tab
      WHERE str LIKE {prefix_pattern}
      SETTINGS optimize_rewrite_like_perfect_affix=0
  </query>

  <query>
      SELECT count() FROM tab
      WHERE str LIKE {prefix_pattern}
      SETTINGS optimize_rewrite_like_perfect_affix=1
  </query>

@Ergus: Like to review and merge this PR? Thanks!

@Ergus: Like to review and merge this PR? Thanks!

I already approved this

Current failing tests seems to be unrelated opened new issues: #88500, #88501, #88503

Specially #88501 I have already seen it in #87994

Hi @zheguang

I just tried to merge this, but there is a new failure in 03594_like_perfect_affix_rewrite_pass that didn't appear before and is related with LIKE.

2025-10-14 13:56:02 Reason: result differs with reference:  
2025-10-14 13:56:02 --- /home/ubuntu/actions-runner/_work/ClickHouse/ClickHouse/tests/queries/0_stateless/03594_like_perfect_affix_rewrite_pass.reference	2025-10-14 13:39:28.230901005 +0200
2025-10-14 13:56:02 +++ /home/ubuntu/actions-runner/_work/ClickHouse/ClickHouse/tests/queries/0_stateless/03594_like_perfect_affix_rewrite_pass.stdout	2025-10-14 13:56:02.391500809 +0200
2025-10-14 13:56:02 @@ -18,27 +18,27 @@
2025-10-14 13:56:02  2
2025-10-14 13:56:02  -- Test LIKE perfect suffix on FixedString column - should NOT be rewritten
2025-10-14 13:56:02  SELECT count() AS `count()`
2025-10-14 13:56:02  FROM default.tab AS __table1
2025-10-14 13:56:02  WHERE __table1.col_fixedstring LIKE \'%ther\\0\'
2025-10-14 13:56:02  2
2025-10-14 13:56:02  2
2025-10-14 13:56:02  -- Test NOT LIKE with perfect prefix on String column - should be rewritten
2025-10-14 13:56:02  SELECT count() AS `count()`
2025-10-14 13:56:02  FROM default.tab AS __table1
2025-10-14 13:56:02 -WHERE NOT startsWith(__table1.col_string, \'app\')
2025-10-14 13:56:02 +WHERE not(startsWith(__table1.col_string, \'app\'))
2025-10-14 13:56:02  4
2025-10-14 13:56:02  4
2025-10-14 13:56:02  Test NOT LIKE with perfect suffix on String column - should be rewritten
2025-10-14 13:56:02  SELECT count() AS `count()`
2025-10-14 13:56:02  FROM default.tab AS __table1
2025-10-14 13:56:02 -WHERE NOT endsWith(__table1.col_string, \'Test\')
2025-10-14 13:56:02 +WHERE not(endsWith(__table1.col_string, \'Test\'))
2025-10-14 13:56:02  5
2025-10-14 13:56:02  5
2025-10-14 13:56:02  -- Test ILIKE with perfect prefix on String column - should NOT be rewritten
2025-10-14 13:56:02  SELECT count() AS `count()`
2025-10-14 13:56:02  FROM default.tab AS __table1
2025-10-14 13:56:02  WHERE __table1.col_string ILIKE \'APP%\'
2025-10-14 13:56:02  3
2025-10-14 13:56:02  3
2025-10-14 13:56:02  Test ILIKE with perfect suffix on String column - should NOT be rewritten
2025-10-14 13:56:02  SELECT count() AS `count()`
2025-10-14 13:56:02 
2025-10-14 13:56:02 
2025-10-14 13:56:02 
2025-10-14 13:56:02 Database: test_3ufzkfzb

The error looks like a pure format difference. Could you check it please?

Hi @zheguang :

Thank you very much for the quick reply. I will try to merge it today.

Currently failing tests seem unrelated and with history:

00601_kill_running_query: Reported as new issue #88563
test_backup_outer_table: Reported as #88566