refactor(binder): simplify type inference logic related to comparison

[xiangjinwu]

... I feel that maintaining special rules and general rules seperately will make this logic more complicate. Suppose I need to debug cmp expressions, I will first need to understand that they are scattered around two different places in the code. I think there could be some better way to implement this.

Originally posted by @neverchanje in #5670 (review)

[github-actions]

This issue has been open for 60 days with no activity. Could you please update the status? Feel free to continue discussion or close as not planned.

[xiangjinwu]

Another shortcoming of splitting comparison between infer_type_for_special (array & struct) and infer_type_name is that ScalarArrayOp (eg t.selected < ALL(t.candidates)) cannot work over array of struct/array, because it relies solely on infer_type_name. But we also note that PostgreSQL does not support this for multi-dimensional array either, as its static typing does not contain dimension information.