🪲 selection pushdown error

[joocer]

SELECT name, Mission_Status, Mission 
FROM $astronauts CROSS JOIN UNNEST (missions) AS mission_names 
INNER JOIN $missions ON Mission = mission_names 
WHERE Mission = 'Apollo 11'

returns this error: TypeError: object of type 'NoneType' has no len()

AFTER COST OPTIMIZATION
└─ EXIT
   └─ PROJECT (Mission_Status, Mission, name)
      └─ INNER JOIN ($missions.Mission = $unnest-8fde.mission_names)
         ├─ SCAN ($missions) [Mission, Mission_Status]
         └─ FILTER (Mission = 'Apollo 11')
            └─ CROSS JOIN
               ├─ SCAN ($astronauts) [name, missions]
               └─ UNNEST (missions AS $unnest-8fde)

We're FILTERing Mission before the SCAN to read it.

The SCAN and the UNNEST are in the wrong order too ... this may be a problem with the tree drawing and a different error with the filter

[joocer]

if we change the filter to be mission_names the query works... we appear to know that Mission and mission_names is the same data, but not which one to use when we push the filter past the JOIN

[joocer]

we current drop all of the predicates before we CROSS JOIN - this means we don't fail to push the predicate and put it back where we picked it up from. We should only drop the predicate if we know all of the columns.

[joocer]

after fix, this is the optimized plan

AFTER COST OPTIMIZATION
└─ EXIT
   └─ PROJECT (Mission_Status, mission_names, name)
      └─ INNER JOIN ($missions.Mission = $unnest-93df.mission_names)
         ├─ CROSS JOIN
         │  ├─ SCAN ($astronauts) [name, missions]
         │  └─ UNNEST (missions AS $unnest-93df)
         └─ FILTER (Mission = 'Apollo 11')
            └─ SCAN ($missions) [Mission, Mission_Status]

We now correctly push the Mission filter to the $missions SCAN

[joocer]

a regression test now fails

SELECT name, mission 
FROM $astronauts CROSS JOIN UNNEST(missions) AS mission 
WHERE mission = 'Apollo 11'

LOGICAL PLAN
└─ EXIT
   └─ PROJECT (name, mission)
      └─ FILTER (mission = 'Apollo 11')
         └─ CROSS JOIN
            ├─ SCAN ($astronauts)
            └─ UNNEST (missions AS $unnest-38da)

AFTER COST OPTIMIZATION
└─ EXIT
   └─ PROJECT (mission, name)
      └─ CROSS JOIN
         ├─ SCAN ($astronauts) [name, missions]
         └─ FILTER (mission = 'Apollo 11')
            └─ UNNEST (missions AS $unnest-38da)

[joocer]

I've fixed the UNNEST display in the plan to show the column name not the virtual relation - haven't fixed the bug though

[joocer]

fixed, need to dump predicates that reference the unnest target too, pushing them past the unnest is impossible