opt: desired scalar types should propagate into subqueries

[jordanlewis]

Currently, types in comparisons and other contexts don't propagate properly if the RHS of the comparison is a subquery. For example, this query should work because the right hand numeric literal should be able to notice that it needs to be a decimal:

root@127.0.0.1:51930/defaultdb> select 3::decimal in (select 1);
pq: unsupported comparison operator: <decimal> IN <tuple{int}>

The root cause of this problem is that subqueries are built without the type context of their surroundings in the optimizer (and HP too).

@rytaft and I discussed the problem for a bit and think that the missing link is propagating the desired scalar types into replaceSubquery, which is the method that ultimately calls buildStmt and begins the optbuilder recursion for a subquery. buildStmt passes nil for desired types into buildSelect, which needs to instead be the desired scalar types.

The problem is that replaceSubquery happens too early, before types of the surrounding expressions are available. One solution might be to delay calling buildStmt until later, when the types are available, in the buildMultiRowSubquery and buildSingleRowSubquery methods.

Fixing this would resolve #14554 and a couple of other similar issues, so it would be great to get done if not too tricky.