bug(expr): lpad never returns

[xiangjinwu]

Describe the bug

No response

To Reproduce

SELECT lpad('hi', 5, '');

Expected behavior

In PostgreSQL:

test=# SELECT lpad('hi', 5, '');
 lpad 
------
 hi
(1 row)

test=# SELECT length(lpad('hi', 5, ''));
 length 
--------
      2
(1 row)

Additional context

found by regress test

[TennyZhuang]

#8767
GPT wrote an infinite loop bug and passed our review 😨

[BugenZhao]

More severely, constant-folding this expression causes the frontend unable to establish new connections for further requests, since tokio tends to move the IO scheduler to the same worker thread with this session, and an infinite loop starves the IO scheduler so that the main thread listening to the connection cannot be woken again! 😨

Although we should definitely fix this issue, it's also possible that the optimizer becomes more complicated and CPU-intensive. This might be relieved by using a separate runtime for frontend sessions (like #4756), but the stuck is inevitable when worker threads are exhausted (note that CPU-intensive tasks cannot be canceled). Or, we need spawn_blocking or block_in_place in every possible blocking code.

cc @liurenjie1024 @wangrunji0408

[wangrunji0408]

GPT wrote an infinite loop bug and passed our review 😨

This case is not documented so it's not surprising that GPT was not aware of it. 🥲