Subselect fetch examples.txt

Simple Subselect
----------------
select x, a, b from (
  select x, a, b from t1 order by x asc limit 10
  ) t1 
order by x desc 
limit 3

Plan:
  Subplan
    Collect: select _fetchid, x from t order by x asc 
  Projections: 
    TopNProjection: limit 2
  FetchDescription: 
    Outputs: x, a, b
    FetchColumns: a, b
    Status: PENDING
Projections:
  OrderedTopNProjection: order by x desc limit 3
  FetchProjection: substitute(_fetchid, [a, b])
  
  
Simple Subselect with partitioned table
---------------------------------------
t parted by a

select x, a, b from (
  select x, a, b from t1 where a in (1, 2) order by x asc limit 10
  ) t1 
order by x desc 
limit 3

Plan:
  Subplan
    Collect: select x, _fetchid from t where a in (1, 2) order by x asc
  Projections: 
    TopNProjection: limit 10
  FetchDescription: 
    Outputs: x, a, b
    FetchColumns: b
    PartitionColumns: a, value: [1,2]
    Status: PENDING
Projections:
  OrderedTopNProjection: order by x desc limit 3
  FetchProjection: substitude(a, [1,2]), substitute(_fetchid, [b])


JOIN
----
select x, i, k from (
  select x, i from t1 order by x asc limit 10
  ) t1,
  select y, k from t2 order by y asc limit 10
  ) t2 
order by x, y desc 
limit 3

Plan:
  Subplan
    NL:
      Collect: select x, _fetchid from t1 order by x asc (limit 10)
      Collect: select y, _fetchid from t2 order by y asc (limit 10)
  FetchDescription: 
    Outputs: x, i, k
    FetchColumns: i, k
    Status: PENDING
Projections:
  OrderedTopNProjection: order by x desc limit 3
  FetchProjection: substitute(_fetchid, [b])



# FetchDescription t1: FetchColumns[t1]: i, Status: PENDING
# FetchDescription t2: FetchColumns[t2]: k, Status: PENDING
# =>
substitude(t1, _fetchid, [a]), substitute(t2, _fetchid, [b]) (
  NL (select x, _fetchid from t1 order by x asc limit 10,
      select y, _fetchid from t2 order by y asc limit 10
  )
order by x desc)


UNION on subselects
-------------------

SELECT * from (
  select x, i from (
    select x, i from t1 order by x asc limit 10
    ) t1,
  ) order by x des limit 5) rel1
UNION ALL  
  select y, k from (
    select y, k from t2 order by y asc limit 10
    ) t2,
  ) order by y desc limit 5) rel2
order by x, i 
limit 3

# FetchProjection is added by rel1, rel2 and not by the UNION
# FetchDescription t1: FetchColumns: i, Status: PENDING
# FetchDescription t2: FetchColumns: k, Status: PENDING
# =>
substitute(t1, _fetchid, [a]) (
  select x, _fetchid from t1 order by x asc limit 10,
)
UNION ALL
substitute(t2, _fetchid, [b]) (
  select y, _fetchid from t2 order by y asc limit 10,
)
order by x desc)


UNION as subselect
-------------------

SELECT x, i, a from (
  select x, i, a from t1
  UNION ALL
  select y, k, b from t2
  order by x limit 10) a
ORDER by x desc limit 5

#FetchDescription a: FetchColumns[rel1]: i, a Status: PENDING
                     FetchColumns[rel2]: k, b Status: PENDING 
# =>
substitute(rel1, _fetchid, [i, a]), substitute(rel2, _fetchid, [k, b]) (
  select x, _fetchid from t1 order by x asc limit 10
  UNION ALL
  select y, _fetchid from t2 order by y asc limit 10,
)
order by x desc limit 5)
                     
                     
UNION on JOIN
-------------