Actually, the selection can reuse the hash only when mitosis is not active. Perhaps this makes sense.
sql>set optimizer='sequential_pipe';
operation successful (0.830ms)
sql>select value from obj_string where value = 'apple' limit 1;
+-------+
| value |
+=======+
| apple |
+-------+
1 tuple (570.217ms)
sql>select value from obj_string where value = 'apple' limit 1;
+-------+
| value |
+=======+
| apple |
+-------+
1 tuple (1.991ms)
But still, this isn't happening with joins, even with sequential pipe.
One difference I noticed is that subselect takes both the tid and the persistent bat as direct inputs, while subjoin takes the leftfetchjoin between the same tid and persistent bat as input. Can that be the reason?