'where' with 'in' doesn't use index?

[Vlado Kurelec]

I have tables

a (a_id int, a_val varchar(60)) 4000000 rows
b (b_id int, b_val varchar(60)) 800000 rows

and indexes

a0 on a (a_id)
a1 on a (a_val)
b0 on a (b_id)
b1 on a (b_val)

select b_id from b
where b_val=(select min(concat('001',a_id)) from a where a_val like 'Ant%')

uses b1 index

select b_id from b
where b_val in (select min(concat('001',a_id)) from a where a_val like 'Ant%')

doesn't

of course, I want to use

select b_id from b
where b_val=(select concat('001',a_id) from a where a_val like 'Ant%')


I've tried
optimizer_prune_level = 0
force key (b1)
rewriting to join
but had no luck.

I'm porting from DB2 V5, it's visual explain tool shows that first temp table is created based on index a1 and then b1 is used to fetch from b.


Any idea?