BUG #14746: sub query's plan not right?

The following bug has been logged on the website:

Bug reference: 14746
Logged by: Zhou Digoal
Email address: digoal(at)126(dot)com
PostgreSQL version: 10beta2
Operating system: CentOS 6.x x64
Description:

HI,

there is something wrong for sub query.

i have two table, a and b, a is the metadata, b is for feed data.

```
create table a (id int primary key);

create table b(aid int, crt_time timestamp, val numeric);
create index idx_b_1 on b(aid, crt_time desc);

insert into a select generate_series(0,100000);
insert into b select random()*100000, clock_timestamp(), random() from
generate_series(1,10000000);
```

wrong example:

```
postgres=# explain select (t.b).aid,(t.b).val,(t.b).crt_time

from
(
select
(
select b from b
where b.aid=a.id
order by crt_time desc limit 1
)
from a
) t
where (t.b).aid is not null;

QUERY PLAN

----------------------------------------------------------------------------------------
Seq Scan on a (cost=0.00..756247.09 rows=99500 width=44)
Filter: (((SubPlan 4)).aid IS NOT NULL)
SubPlan 4
-> Limit (cost=0.57..1.89 rows=1 width=55)
-> Index Scan using idx_b_1 on b b_3 (cost=0.57..946.44
rows=713 width=55)
Index Cond: (aid = a.id)
SubPlan 1
-> Limit (cost=0.57..1.89 rows=1 width=55)
-> Index Scan using idx_b_1 on b (cost=0.57..946.44 rows=713
width=55)
Index Cond: (aid = a.id)
SubPlan 2
-> Limit (cost=0.57..1.89 rows=1 width=55)
-> Index Scan using idx_b_1 on b b_1 (cost=0.57..946.44
rows=713 width=55)
Index Cond: (aid = a.id)
SubPlan 3
-> Limit (cost=0.57..1.89 rows=1 width=55)
-> Index Scan using idx_b_1 on b b_2 (cost=0.57..946.44
rows=713 width=55)
Index Cond: (aid = a.id)
(18 rows)
```

when i use limit for a scan, it's correct

```
postgres=# explain select (t.b).aid,(t.b).val,(t.b).crt_time

from
(
select
(
select b from b
where b.aid=a.id
order by crt_time desc limit 1
)
from a limit 100001
) t
where (t.b).aid is not null;
QUERY PLAN

------------------------------------------------------------------------------------------------
Subquery Scan on t (cost=0.00..191854.32 rows=99500 width=44)
Filter: ((t.b).aid IS NOT NULL)
-> Limit (cost=0.00..190854.32 rows=100000 width=32)
-> Seq Scan on a (cost=0.00..190854.32 rows=100000 width=32)
SubPlan 1
-> Limit (cost=0.57..1.89 rows=1 width=55)
-> Index Scan using idx_b_1 on b (cost=0.57..946.44
rows=713 width=55)
Index Cond: (aid = a.id)
(8 rows)
```

the query run time is alse fast when i use limit for a.

```
postgres=# explain analyze select (t.b).aid,(t.b).val,(t.b).crt_time

from
(
select
(
select b from b
where b.aid=a.id
order by crt_time desc limit 1
)
from a limit 100001
) t
where (t.b).aid is not null;
QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------------------------
Subquery Scan on t (cost=0.00..191854.32 rows=99500 width=44) (actual
time=0.053..814.481 rows=100000 loops=1)
Filter: ((t.b).aid IS NOT NULL)
-> Limit (cost=0.00..190854.32 rows=100000 width=32) (actual
time=0.050..787.891 rows=100000 loops=1)
-> Seq Scan on a (cost=0.00..190854.32 rows=100000 width=32)
(actual time=0.049..780.356 rows=100000 loops=1)
SubPlan 1
-> Limit (cost=0.57..1.89 rows=1 width=55) (actual
time=0.007..0.007 rows=1 loops=100000)
-> Index Scan using idx_b_1 on b (cost=0.57..946.44
rows=713 width=55) (actual time=0.007..0.007 rows=1 loops=100000)
Index Cond: (aid = a.id)
Planning time: 0.145 ms
Execution time: 818.490 ms
(10 rows)

postgres=# explain analyze select (t.b).aid,(t.b).val,(t.b).crt_time

from
(
select
(
select b from b
where b.aid=a.id
order by crt_time desc limit 1
)
from a
) t
where (t.b).aid is not null;
QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on a (cost=0.00..756247.09 rows=99500 width=44) (actual
time=0.081..2290.582 rows=100000 loops=1)
Filter: (((SubPlan 4)).aid IS NOT NULL)
SubPlan 4
-> Limit (cost=0.57..1.89 rows=1 width=55) (actual time=0.007..0.007
rows=1 loops=100000)
-> Index Scan using idx_b_1 on b b_3 (cost=0.57..946.44
rows=713 width=55) (actual time=0.007..0.007 rows=1 loops=100000)
Index Cond: (aid = a.id)
SubPlan 1
-> Limit (cost=0.57..1.89 rows=1 width=55) (actual time=0.005..0.005
rows=1 loops=100000)
-> Index Scan using idx_b_1 on b (cost=0.57..946.44 rows=713
width=55) (actual time=0.004..0.004 rows=1 loops=100000)
Index Cond: (aid = a.id)
SubPlan 2
-> Limit (cost=0.57..1.89 rows=1 width=55) (actual time=0.005..0.005
rows=1 loops=100000)
-> Index Scan using idx_b_1 on b b_1 (cost=0.57..946.44
rows=713 width=55) (actual time=0.004..0.004 rows=1 loops=100000)
Index Cond: (aid = a.id)
SubPlan 3
-> Limit (cost=0.57..1.89 rows=1 width=55) (actual time=0.005..0.005
rows=1 loops=100000)
-> Index Scan using idx_b_1 on b b_2 (cost=0.57..946.44
rows=713 width=55) (actual time=0.004..0.004 rows=1 loops=100000)
Index Cond: (aid = a.id)
Planning time: 0.238 ms
Execution time: 2294.653 ms
(20 rows)
```

is this a bug ? or it can be improved?

thank you, best regards,
digoal