title: Why does the polybase pushdown filter for join not work?
body: <p>Recently put polybase into production and have noticed that pushdown isn't working for joins.  Take the following query:</p>
  <pre><code>CREATE TABLE #VendIDs
  (VendorAN int primary key)
  
  INSERT INTO #VendIDs(VendorAN)
  VALUES (1),(89980),(89993),(90002),(90003),(90008),(90004),(90015),(90018),(97140),(97139),(97138)
  ,(97137),(97136),(97135),(97134),(97059),(97058),(97057),(97056),(97055),(97054),(97053),(97052)
  
  SELECT VW.VendorAN, [Type], Code, [Name],Address1, Address2, City,State, Zip,Country, Phone, 
  ShipAddress1, ShipAddress2, ShipCity, ShipState, ShipZip,Latitude, Longitude
  FROM vwVendor VW
  JOIN #VendIDs FV ON VW.VendorAN = FV.VendorAN
  </code></pre>
  <p>The execution plan shows 22k rows from the 'remote query', which just happens to match the number of rows in the external table.  After enabling trace flag 6408, it shows 22k records on the external side.</p>
  <p>If I do a simply where vendorAN = XXXXXX, I can clearly see 1 row being returned via the remote query and the filtering be done on the remote side.</p>
  <p>Anyone have a thought on why I'm not seeing pushdown filtering on the join as shown above?  Makes no sense to me based upon what I've read to date.</p>
  <p>Referencing this article for how to know if pushdown filtering is occurring: <a href="https://docs.microsoft.com/en-us/sql/relational-databases/polybase/polybase-how-to-tell-pushdown-computation?view=sql-server-ver15#--use-tf6408" rel="nofollow noreferrer">https://docs.microsoft.com/en-us/sql/relational-databases/polybase/polybase-how-to-tell-pushdown-computation?view=sql-server-ver15#--use-tf6408</a></p>