diff options
Diffstat (limited to 'docs/sqlglot/optimizer/pushdown_projections.html')
| -rw-r--r-- | docs/sqlglot/optimizer/pushdown_projections.html | 277 |
1 files changed, 141 insertions, 136 deletions
diff --git a/docs/sqlglot/optimizer/pushdown_projections.html b/docs/sqlglot/optimizer/pushdown_projections.html index 6cb1975..54d7932 100644 --- a/docs/sqlglot/optimizer/pushdown_projections.html +++ b/docs/sqlglot/optimizer/pushdown_projections.html @@ -74,7 +74,7 @@ </span><span id="L-13"><a href="#L-13"><span class="linenos"> 13</span></a><span class="n">DEFAULT_SELECTION</span> <span class="o">=</span> <span class="k">lambda</span><span class="p">:</span> <span class="n">alias</span><span class="p">(</span><span class="s2">"1"</span><span class="p">,</span> <span class="s2">"_"</span><span class="p">)</span> </span><span id="L-14"><a href="#L-14"><span class="linenos"> 14</span></a> </span><span id="L-15"><a href="#L-15"><span class="linenos"> 15</span></a> -</span><span id="L-16"><a href="#L-16"><span class="linenos"> 16</span></a><span class="k">def</span> <span class="nf">pushdown_projections</span><span class="p">(</span><span class="n">expression</span><span class="p">,</span> <span class="n">schema</span><span class="o">=</span><span class="kc">None</span><span class="p">):</span> +</span><span id="L-16"><a href="#L-16"><span class="linenos"> 16</span></a><span class="k">def</span> <span class="nf">pushdown_projections</span><span class="p">(</span><span class="n">expression</span><span class="p">,</span> <span class="n">schema</span><span class="o">=</span><span class="kc">None</span><span class="p">,</span> <span class="n">remove_unused_selections</span><span class="o">=</span><span class="kc">True</span><span class="p">):</span> </span><span id="L-17"><a href="#L-17"><span class="linenos"> 17</span></a><span class="w"> </span><span class="sd">"""</span> </span><span id="L-18"><a href="#L-18"><span class="linenos"> 18</span></a><span class="sd"> Rewrite sqlglot AST to remove unused columns projections.</span> </span><span id="L-19"><a href="#L-19"><span class="linenos"> 19</span></a> @@ -87,94 +87,96 @@ </span><span id="L-26"><a href="#L-26"><span class="linenos"> 26</span></a> </span><span id="L-27"><a href="#L-27"><span class="linenos"> 27</span></a><span class="sd"> Args:</span> </span><span id="L-28"><a href="#L-28"><span class="linenos"> 28</span></a><span class="sd"> expression (sqlglot.Expression): expression to optimize</span> -</span><span id="L-29"><a href="#L-29"><span class="linenos"> 29</span></a><span class="sd"> Returns:</span> -</span><span id="L-30"><a href="#L-30"><span class="linenos"> 30</span></a><span class="sd"> sqlglot.Expression: optimized expression</span> -</span><span id="L-31"><a href="#L-31"><span class="linenos"> 31</span></a><span class="sd"> """</span> -</span><span id="L-32"><a href="#L-32"><span class="linenos"> 32</span></a> <span class="c1"># Map of Scope to all columns being selected by outer queries.</span> -</span><span id="L-33"><a href="#L-33"><span class="linenos"> 33</span></a> <span class="n">schema</span> <span class="o">=</span> <span class="n">ensure_schema</span><span class="p">(</span><span class="n">schema</span><span class="p">)</span> -</span><span id="L-34"><a href="#L-34"><span class="linenos"> 34</span></a> <span class="n">referenced_columns</span> <span class="o">=</span> <span class="n">defaultdict</span><span class="p">(</span><span class="nb">set</span><span class="p">)</span> -</span><span id="L-35"><a href="#L-35"><span class="linenos"> 35</span></a> -</span><span id="L-36"><a href="#L-36"><span class="linenos"> 36</span></a> <span class="c1"># We build the scope tree (which is traversed in DFS postorder), then iterate</span> -</span><span id="L-37"><a href="#L-37"><span class="linenos"> 37</span></a> <span class="c1"># over the result in reverse order. This should ensure that the set of selected</span> -</span><span id="L-38"><a href="#L-38"><span class="linenos"> 38</span></a> <span class="c1"># columns for a particular scope are completely build by the time we get to it.</span> -</span><span id="L-39"><a href="#L-39"><span class="linenos"> 39</span></a> <span class="k">for</span> <span class="n">scope</span> <span class="ow">in</span> <span class="nb">reversed</span><span class="p">(</span><span class="n">traverse_scope</span><span class="p">(</span><span class="n">expression</span><span class="p">)):</span> -</span><span id="L-40"><a href="#L-40"><span class="linenos"> 40</span></a> <span class="n">parent_selections</span> <span class="o">=</span> <span class="n">referenced_columns</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="n">scope</span><span class="p">,</span> <span class="p">{</span><span class="n">SELECT_ALL</span><span class="p">})</span> -</span><span id="L-41"><a href="#L-41"><span class="linenos"> 41</span></a> -</span><span id="L-42"><a href="#L-42"><span class="linenos"> 42</span></a> <span class="k">if</span> <span class="n">scope</span><span class="o">.</span><span class="n">expression</span><span class="o">.</span><span class="n">args</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="s2">"distinct"</span><span class="p">):</span> -</span><span id="L-43"><a href="#L-43"><span class="linenos"> 43</span></a> <span class="c1"># We can't remove columns SELECT DISTINCT nor UNION DISTINCT</span> -</span><span id="L-44"><a href="#L-44"><span class="linenos"> 44</span></a> <span class="n">parent_selections</span> <span class="o">=</span> <span class="p">{</span><span class="n">SELECT_ALL</span><span class="p">}</span> -</span><span id="L-45"><a href="#L-45"><span class="linenos"> 45</span></a> -</span><span id="L-46"><a href="#L-46"><span class="linenos"> 46</span></a> <span class="k">if</span> <span class="nb">isinstance</span><span class="p">(</span><span class="n">scope</span><span class="o">.</span><span class="n">expression</span><span class="p">,</span> <span class="n">exp</span><span class="o">.</span><span class="n">Union</span><span class="p">):</span> -</span><span id="L-47"><a href="#L-47"><span class="linenos"> 47</span></a> <span class="n">left</span><span class="p">,</span> <span class="n">right</span> <span class="o">=</span> <span class="n">scope</span><span class="o">.</span><span class="n">union_scopes</span> -</span><span id="L-48"><a href="#L-48"><span class="linenos"> 48</span></a> <span class="n">referenced_columns</span><span class="p">[</span><span class="n">left</span><span class="p">]</span> <span class="o">=</span> <span class="n">parent_selections</span> -</span><span id="L-49"><a href="#L-49"><span class="linenos"> 49</span></a> -</span><span id="L-50"><a href="#L-50"><span class="linenos"> 50</span></a> <span class="k">if</span> <span class="nb">any</span><span class="p">(</span><span class="n">select</span><span class="o">.</span><span class="n">is_star</span> <span class="k">for</span> <span class="n">select</span> <span class="ow">in</span> <span class="n">right</span><span class="o">.</span><span class="n">selects</span><span class="p">):</span> -</span><span id="L-51"><a href="#L-51"><span class="linenos"> 51</span></a> <span class="n">referenced_columns</span><span class="p">[</span><span class="n">right</span><span class="p">]</span> <span class="o">=</span> <span class="n">parent_selections</span> -</span><span id="L-52"><a href="#L-52"><span class="linenos"> 52</span></a> <span class="k">elif</span> <span class="ow">not</span> <span class="nb">any</span><span class="p">(</span><span class="n">select</span><span class="o">.</span><span class="n">is_star</span> <span class="k">for</span> <span class="n">select</span> <span class="ow">in</span> <span class="n">left</span><span class="o">.</span><span class="n">selects</span><span class="p">):</span> -</span><span id="L-53"><a href="#L-53"><span class="linenos"> 53</span></a> <span class="n">referenced_columns</span><span class="p">[</span><span class="n">right</span><span class="p">]</span> <span class="o">=</span> <span class="p">[</span> -</span><span id="L-54"><a href="#L-54"><span class="linenos"> 54</span></a> <span class="n">right</span><span class="o">.</span><span class="n">selects</span><span class="p">[</span><span class="n">i</span><span class="p">]</span><span class="o">.</span><span class="n">alias_or_name</span> -</span><span id="L-55"><a href="#L-55"><span class="linenos"> 55</span></a> <span class="k">for</span> <span class="n">i</span><span class="p">,</span> <span class="n">select</span> <span class="ow">in</span> <span class="nb">enumerate</span><span class="p">(</span><span class="n">left</span><span class="o">.</span><span class="n">selects</span><span class="p">)</span> -</span><span id="L-56"><a href="#L-56"><span class="linenos"> 56</span></a> <span class="k">if</span> <span class="n">SELECT_ALL</span> <span class="ow">in</span> <span class="n">parent_selections</span> <span class="ow">or</span> <span class="n">select</span><span class="o">.</span><span class="n">alias_or_name</span> <span class="ow">in</span> <span class="n">parent_selections</span> -</span><span id="L-57"><a href="#L-57"><span class="linenos"> 57</span></a> <span class="p">]</span> -</span><span id="L-58"><a href="#L-58"><span class="linenos"> 58</span></a> -</span><span id="L-59"><a href="#L-59"><span class="linenos"> 59</span></a> <span class="k">if</span> <span class="nb">isinstance</span><span class="p">(</span><span class="n">scope</span><span class="o">.</span><span class="n">expression</span><span class="p">,</span> <span class="n">exp</span><span class="o">.</span><span class="n">Select</span><span class="p">):</span> -</span><span id="L-60"><a href="#L-60"><span class="linenos"> 60</span></a> <span class="n">_remove_unused_selections</span><span class="p">(</span><span class="n">scope</span><span class="p">,</span> <span class="n">parent_selections</span><span class="p">,</span> <span class="n">schema</span><span class="p">)</span> -</span><span id="L-61"><a href="#L-61"><span class="linenos"> 61</span></a> -</span><span id="L-62"><a href="#L-62"><span class="linenos"> 62</span></a> <span class="c1"># Group columns by source name</span> -</span><span id="L-63"><a href="#L-63"><span class="linenos"> 63</span></a> <span class="n">selects</span> <span class="o">=</span> <span class="n">defaultdict</span><span class="p">(</span><span class="nb">set</span><span class="p">)</span> -</span><span id="L-64"><a href="#L-64"><span class="linenos"> 64</span></a> <span class="k">for</span> <span class="n">col</span> <span class="ow">in</span> <span class="n">scope</span><span class="o">.</span><span class="n">columns</span><span class="p">:</span> -</span><span id="L-65"><a href="#L-65"><span class="linenos"> 65</span></a> <span class="n">table_name</span> <span class="o">=</span> <span class="n">col</span><span class="o">.</span><span class="n">table</span> -</span><span id="L-66"><a href="#L-66"><span class="linenos"> 66</span></a> <span class="n">col_name</span> <span class="o">=</span> <span class="n">col</span><span class="o">.</span><span class="n">name</span> -</span><span id="L-67"><a href="#L-67"><span class="linenos"> 67</span></a> <span class="n">selects</span><span class="p">[</span><span class="n">table_name</span><span class="p">]</span><span class="o">.</span><span class="n">add</span><span class="p">(</span><span class="n">col_name</span><span class="p">)</span> -</span><span id="L-68"><a href="#L-68"><span class="linenos"> 68</span></a> -</span><span id="L-69"><a href="#L-69"><span class="linenos"> 69</span></a> <span class="c1"># Push the selected columns down to the next scope</span> -</span><span id="L-70"><a href="#L-70"><span class="linenos"> 70</span></a> <span class="k">for</span> <span class="n">name</span><span class="p">,</span> <span class="p">(</span><span class="n">_</span><span class="p">,</span> <span class="n">source</span><span class="p">)</span> <span class="ow">in</span> <span class="n">scope</span><span class="o">.</span><span class="n">selected_sources</span><span class="o">.</span><span class="n">items</span><span class="p">():</span> -</span><span id="L-71"><a href="#L-71"><span class="linenos"> 71</span></a> <span class="k">if</span> <span class="nb">isinstance</span><span class="p">(</span><span class="n">source</span><span class="p">,</span> <span class="n">Scope</span><span class="p">):</span> -</span><span id="L-72"><a href="#L-72"><span class="linenos"> 72</span></a> <span class="n">columns</span> <span class="o">=</span> <span class="n">selects</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="n">name</span><span class="p">)</span> <span class="ow">or</span> <span class="nb">set</span><span class="p">()</span> -</span><span id="L-73"><a href="#L-73"><span class="linenos"> 73</span></a> <span class="n">referenced_columns</span><span class="p">[</span><span class="n">source</span><span class="p">]</span><span class="o">.</span><span class="n">update</span><span class="p">(</span><span class="n">columns</span><span class="p">)</span> -</span><span id="L-74"><a href="#L-74"><span class="linenos"> 74</span></a> -</span><span id="L-75"><a href="#L-75"><span class="linenos"> 75</span></a> <span class="k">return</span> <span class="n">expression</span> +</span><span id="L-29"><a href="#L-29"><span class="linenos"> 29</span></a><span class="sd"> remove_unused_selections (bool): remove selects that are unused</span> +</span><span id="L-30"><a href="#L-30"><span class="linenos"> 30</span></a><span class="sd"> Returns:</span> +</span><span id="L-31"><a href="#L-31"><span class="linenos"> 31</span></a><span class="sd"> sqlglot.Expression: optimized expression</span> +</span><span id="L-32"><a href="#L-32"><span class="linenos"> 32</span></a><span class="sd"> """</span> +</span><span id="L-33"><a href="#L-33"><span class="linenos"> 33</span></a> <span class="c1"># Map of Scope to all columns being selected by outer queries.</span> +</span><span id="L-34"><a href="#L-34"><span class="linenos"> 34</span></a> <span class="n">schema</span> <span class="o">=</span> <span class="n">ensure_schema</span><span class="p">(</span><span class="n">schema</span><span class="p">)</span> +</span><span id="L-35"><a href="#L-35"><span class="linenos"> 35</span></a> <span class="n">referenced_columns</span> <span class="o">=</span> <span class="n">defaultdict</span><span class="p">(</span><span class="nb">set</span><span class="p">)</span> +</span><span id="L-36"><a href="#L-36"><span class="linenos"> 36</span></a> +</span><span id="L-37"><a href="#L-37"><span class="linenos"> 37</span></a> <span class="c1"># We build the scope tree (which is traversed in DFS postorder), then iterate</span> +</span><span id="L-38"><a href="#L-38"><span class="linenos"> 38</span></a> <span class="c1"># over the result in reverse order. This should ensure that the set of selected</span> +</span><span id="L-39"><a href="#L-39"><span class="linenos"> 39</span></a> <span class="c1"># columns for a particular scope are completely build by the time we get to it.</span> +</span><span id="L-40"><a href="#L-40"><span class="linenos"> 40</span></a> <span class="k">for</span> <span class="n">scope</span> <span class="ow">in</span> <span class="nb">reversed</span><span class="p">(</span><span class="n">traverse_scope</span><span class="p">(</span><span class="n">expression</span><span class="p">)):</span> +</span><span id="L-41"><a href="#L-41"><span class="linenos"> 41</span></a> <span class="n">parent_selections</span> <span class="o">=</span> <span class="n">referenced_columns</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="n">scope</span><span class="p">,</span> <span class="p">{</span><span class="n">SELECT_ALL</span><span class="p">})</span> +</span><span id="L-42"><a href="#L-42"><span class="linenos"> 42</span></a> +</span><span id="L-43"><a href="#L-43"><span class="linenos"> 43</span></a> <span class="k">if</span> <span class="n">scope</span><span class="o">.</span><span class="n">expression</span><span class="o">.</span><span class="n">args</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="s2">"distinct"</span><span class="p">):</span> +</span><span id="L-44"><a href="#L-44"><span class="linenos"> 44</span></a> <span class="c1"># We can't remove columns SELECT DISTINCT nor UNION DISTINCT</span> +</span><span id="L-45"><a href="#L-45"><span class="linenos"> 45</span></a> <span class="n">parent_selections</span> <span class="o">=</span> <span class="p">{</span><span class="n">SELECT_ALL</span><span class="p">}</span> +</span><span id="L-46"><a href="#L-46"><span class="linenos"> 46</span></a> +</span><span id="L-47"><a href="#L-47"><span class="linenos"> 47</span></a> <span class="k">if</span> <span class="nb">isinstance</span><span class="p">(</span><span class="n">scope</span><span class="o">.</span><span class="n">expression</span><span class="p">,</span> <span class="n">exp</span><span class="o">.</span><span class="n">Union</span><span class="p">):</span> +</span><span id="L-48"><a href="#L-48"><span class="linenos"> 48</span></a> <span class="n">left</span><span class="p">,</span> <span class="n">right</span> <span class="o">=</span> <span class="n">scope</span><span class="o">.</span><span class="n">union_scopes</span> +</span><span id="L-49"><a href="#L-49"><span class="linenos"> 49</span></a> <span class="n">referenced_columns</span><span class="p">[</span><span class="n">left</span><span class="p">]</span> <span class="o">=</span> <span class="n">parent_selections</span> +</span><span id="L-50"><a href="#L-50"><span class="linenos"> 50</span></a> +</span><span id="L-51"><a href="#L-51"><span class="linenos"> 51</span></a> <span class="k">if</span> <span class="nb">any</span><span class="p">(</span><span class="n">select</span><span class="o">.</span><span class="n">is_star</span> <span class="k">for</span> <span class="n">select</span> <span class="ow">in</span> <span class="n">right</span><span class="o">.</span><span class="n">selects</span><span class="p">):</span> +</span><span id="L-52"><a href="#L-52"><span class="linenos"> 52</span></a> <span class="n">referenced_columns</span><span class="p">[</span><span class="n">right</span><span class="p">]</span> <span class="o">=</span> <span class="n">parent_selections</span> +</span><span id="L-53"><a href="#L-53"><span class="linenos"> 53</span></a> <span class="k">elif</span> <span class="ow">not</span> <span class="nb">any</span><span class="p">(</span><span class="n">select</span><span class="o">.</span><span class="n">is_star</span> <span class="k">for</span> <span class="n">select</span> <span class="ow">in</span> <span class="n">left</span><span class="o">.</span><span class="n">selects</span><span class="p">):</span> +</span><span id="L-54"><a href="#L-54"><span class="linenos"> 54</span></a> <span class="n">referenced_columns</span><span class="p">[</span><span class="n">right</span><span class="p">]</span> <span class="o">=</span> <span class="p">[</span> +</span><span id="L-55"><a href="#L-55"><span class="linenos"> 55</span></a> <span class="n">right</span><span class="o">.</span><span class="n">selects</span><span class="p">[</span><span class="n">i</span><span class="p">]</span><span class="o">.</span><span class="n">alias_or_name</span> +</span><span id="L-56"><a href="#L-56"><span class="linenos"> 56</span></a> <span class="k">for</span> <span class="n">i</span><span class="p">,</span> <span class="n">select</span> <span class="ow">in</span> <span class="nb">enumerate</span><span class="p">(</span><span class="n">left</span><span class="o">.</span><span class="n">selects</span><span class="p">)</span> +</span><span id="L-57"><a href="#L-57"><span class="linenos"> 57</span></a> <span class="k">if</span> <span class="n">SELECT_ALL</span> <span class="ow">in</span> <span class="n">parent_selections</span> <span class="ow">or</span> <span class="n">select</span><span class="o">.</span><span class="n">alias_or_name</span> <span class="ow">in</span> <span class="n">parent_selections</span> +</span><span id="L-58"><a href="#L-58"><span class="linenos"> 58</span></a> <span class="p">]</span> +</span><span id="L-59"><a href="#L-59"><span class="linenos"> 59</span></a> +</span><span id="L-60"><a href="#L-60"><span class="linenos"> 60</span></a> <span class="k">if</span> <span class="nb">isinstance</span><span class="p">(</span><span class="n">scope</span><span class="o">.</span><span class="n">expression</span><span class="p">,</span> <span class="n">exp</span><span class="o">.</span><span class="n">Select</span><span class="p">):</span> +</span><span id="L-61"><a href="#L-61"><span class="linenos"> 61</span></a> <span class="k">if</span> <span class="n">remove_unused_selections</span><span class="p">:</span> +</span><span id="L-62"><a href="#L-62"><span class="linenos"> 62</span></a> <span class="n">_remove_unused_selections</span><span class="p">(</span><span class="n">scope</span><span class="p">,</span> <span class="n">parent_selections</span><span class="p">,</span> <span class="n">schema</span><span class="p">)</span> +</span><span id="L-63"><a href="#L-63"><span class="linenos"> 63</span></a> +</span><span id="L-64"><a href="#L-64"><span class="linenos"> 64</span></a> <span class="c1"># Group columns by source name</span> +</span><span id="L-65"><a href="#L-65"><span class="linenos"> 65</span></a> <span class="n">selects</span> <span class="o">=</span> <span class="n">defaultdict</span><span class="p">(</span><span class="nb">set</span><span class="p">)</span> +</span><span id="L-66"><a href="#L-66"><span class="linenos"> 66</span></a> <span class="k">for</span> <span class="n">col</span> <span class="ow">in</span> <span class="n">scope</span><span class="o">.</span><span class="n">columns</span><span class="p">:</span> +</span><span id="L-67"><a href="#L-67"><span class="linenos"> 67</span></a> <span class="n">table_name</span> <span class="o">=</span> <span class="n">col</span><span class="o">.</span><span class="n">table</span> +</span><span id="L-68"><a href="#L-68"><span class="linenos"> 68</span></a> <span class="n">col_name</span> <span class="o">=</span> <span class="n">col</span><span class="o">.</span><span class="n">name</span> +</span><span id="L-69"><a href="#L-69"><span class="linenos"> 69</span></a> <span class="n">selects</span><span class="p">[</span><span class="n">table_name</span><span class="p">]</span><span class="o">.</span><span class="n">add</span><span class="p">(</span><span class="n">col_name</span><span class="p">)</span> +</span><span id="L-70"><a href="#L-70"><span class="linenos"> 70</span></a> +</span><span id="L-71"><a href="#L-71"><span class="linenos"> 71</span></a> <span class="c1"># Push the selected columns down to the next scope</span> +</span><span id="L-72"><a href="#L-72"><span class="linenos"> 72</span></a> <span class="k">for</span> <span class="n">name</span><span class="p">,</span> <span class="p">(</span><span class="n">_</span><span class="p">,</span> <span class="n">source</span><span class="p">)</span> <span class="ow">in</span> <span class="n">scope</span><span class="o">.</span><span class="n">selected_sources</span><span class="o">.</span><span class="n">items</span><span class="p">():</span> +</span><span id="L-73"><a href="#L-73"><span class="linenos"> 73</span></a> <span class="k">if</span> <span class="nb">isinstance</span><span class="p">(</span><span class="n">source</span><span class="p">,</span> <span class="n">Scope</span><span class="p">):</span> +</span><span id="L-74"><a href="#L-74"><span class="linenos"> 74</span></a> <span class="n">columns</span> <span class="o">=</span> <span class="n">selects</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="n">name</span><span class="p">)</span> <span class="ow">or</span> <span class="nb">set</span><span class="p">()</span> +</span><span id="L-75"><a href="#L-75"><span class="linenos"> 75</span></a> <span class="n">referenced_columns</span><span class="p">[</span><span class="n">source</span><span class="p">]</span><span class="o">.</span><span class="n">update</span><span class="p">(</span><span class="n">columns</span><span class="p">)</span> </span><span id="L-76"><a href="#L-76"><span class="linenos"> 76</span></a> -</span><span id="L-77"><a href="#L-77"><span class="linenos"> 77</span></a> -</span><span id="L-78"><a href="#L-78"><span class="linenos"> 78</span></a><span class="k">def</span> <span class="nf">_remove_unused_selections</span><span class="p">(</span><span class="n">scope</span><span class="p">,</span> <span class="n">parent_selections</span><span class="p">,</span> <span class="n">schema</span><span class="p">):</span> -</span><span id="L-79"><a href="#L-79"><span class="linenos"> 79</span></a> <span class="n">order</span> <span class="o">=</span> <span class="n">scope</span><span class="o">.</span><span class="n">expression</span><span class="o">.</span><span class="n">args</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="s2">"order"</span><span class="p">)</span> -</span><span id="L-80"><a href="#L-80"><span class="linenos"> 80</span></a> -</span><span id="L-81"><a href="#L-81"><span class="linenos"> 81</span></a> <span class="k">if</span> <span class="n">order</span><span class="p">:</span> -</span><span id="L-82"><a href="#L-82"><span class="linenos"> 82</span></a> <span class="c1"># Assume columns without a qualified table are references to output columns</span> -</span><span id="L-83"><a href="#L-83"><span class="linenos"> 83</span></a> <span class="n">order_refs</span> <span class="o">=</span> <span class="p">{</span><span class="n">c</span><span class="o">.</span><span class="n">name</span> <span class="k">for</span> <span class="n">c</span> <span class="ow">in</span> <span class="n">order</span><span class="o">.</span><span class="n">find_all</span><span class="p">(</span><span class="n">exp</span><span class="o">.</span><span class="n">Column</span><span class="p">)</span> <span class="k">if</span> <span class="ow">not</span> <span class="n">c</span><span class="o">.</span><span class="n">table</span><span class="p">}</span> -</span><span id="L-84"><a href="#L-84"><span class="linenos"> 84</span></a> <span class="k">else</span><span class="p">:</span> -</span><span id="L-85"><a href="#L-85"><span class="linenos"> 85</span></a> <span class="n">order_refs</span> <span class="o">=</span> <span class="nb">set</span><span class="p">()</span> -</span><span id="L-86"><a href="#L-86"><span class="linenos"> 86</span></a> -</span><span id="L-87"><a href="#L-87"><span class="linenos"> 87</span></a> <span class="n">new_selections</span> <span class="o">=</span> <span class="n">defaultdict</span><span class="p">(</span><span class="nb">list</span><span class="p">)</span> -</span><span id="L-88"><a href="#L-88"><span class="linenos"> 88</span></a> <span class="n">removed</span> <span class="o">=</span> <span class="kc">False</span> -</span><span id="L-89"><a href="#L-89"><span class="linenos"> 89</span></a> <span class="n">star</span> <span class="o">=</span> <span class="kc">False</span> -</span><span id="L-90"><a href="#L-90"><span class="linenos"> 90</span></a> <span class="k">for</span> <span class="n">selection</span> <span class="ow">in</span> <span class="n">scope</span><span class="o">.</span><span class="n">selects</span><span class="p">:</span> -</span><span id="L-91"><a href="#L-91"><span class="linenos"> 91</span></a> <span class="n">name</span> <span class="o">=</span> <span class="n">selection</span><span class="o">.</span><span class="n">alias_or_name</span> -</span><span id="L-92"><a href="#L-92"><span class="linenos"> 92</span></a> -</span><span id="L-93"><a href="#L-93"><span class="linenos"> 93</span></a> <span class="k">if</span> <span class="n">SELECT_ALL</span> <span class="ow">in</span> <span class="n">parent_selections</span> <span class="ow">or</span> <span class="n">name</span> <span class="ow">in</span> <span class="n">parent_selections</span> <span class="ow">or</span> <span class="n">name</span> <span class="ow">in</span> <span class="n">order_refs</span><span class="p">:</span> -</span><span id="L-94"><a href="#L-94"><span class="linenos"> 94</span></a> <span class="n">new_selections</span><span class="p">[</span><span class="n">name</span><span class="p">]</span><span class="o">.</span><span class="n">append</span><span class="p">(</span><span class="n">selection</span><span class="p">)</span> -</span><span id="L-95"><a href="#L-95"><span class="linenos"> 95</span></a> <span class="k">else</span><span class="p">:</span> -</span><span id="L-96"><a href="#L-96"><span class="linenos"> 96</span></a> <span class="k">if</span> <span class="n">selection</span><span class="o">.</span><span class="n">is_star</span><span class="p">:</span> -</span><span id="L-97"><a href="#L-97"><span class="linenos"> 97</span></a> <span class="n">star</span> <span class="o">=</span> <span class="kc">True</span> -</span><span id="L-98"><a href="#L-98"><span class="linenos"> 98</span></a> <span class="n">removed</span> <span class="o">=</span> <span class="kc">True</span> -</span><span id="L-99"><a href="#L-99"><span class="linenos"> 99</span></a> -</span><span id="L-100"><a href="#L-100"><span class="linenos">100</span></a> <span class="k">if</span> <span class="n">star</span><span class="p">:</span> -</span><span id="L-101"><a href="#L-101"><span class="linenos">101</span></a> <span class="n">resolver</span> <span class="o">=</span> <span class="n">Resolver</span><span class="p">(</span><span class="n">scope</span><span class="p">,</span> <span class="n">schema</span><span class="p">)</span> -</span><span id="L-102"><a href="#L-102"><span class="linenos">102</span></a> -</span><span id="L-103"><a href="#L-103"><span class="linenos">103</span></a> <span class="k">for</span> <span class="n">name</span> <span class="ow">in</span> <span class="nb">sorted</span><span class="p">(</span><span class="n">parent_selections</span><span class="p">):</span> -</span><span id="L-104"><a href="#L-104"><span class="linenos">104</span></a> <span class="k">if</span> <span class="n">name</span> <span class="ow">not</span> <span class="ow">in</span> <span class="n">new_selections</span><span class="p">:</span> -</span><span id="L-105"><a href="#L-105"><span class="linenos">105</span></a> <span class="n">new_selections</span><span class="p">[</span><span class="n">name</span><span class="p">]</span><span class="o">.</span><span class="n">append</span><span class="p">(</span> -</span><span id="L-106"><a href="#L-106"><span class="linenos">106</span></a> <span class="n">alias</span><span class="p">(</span><span class="n">exp</span><span class="o">.</span><span class="n">column</span><span class="p">(</span><span class="n">name</span><span class="p">,</span> <span class="n">table</span><span class="o">=</span><span class="n">resolver</span><span class="o">.</span><span class="n">get_table</span><span class="p">(</span><span class="n">name</span><span class="p">)),</span> <span class="n">name</span><span class="p">)</span> -</span><span id="L-107"><a href="#L-107"><span class="linenos">107</span></a> <span class="p">)</span> -</span><span id="L-108"><a href="#L-108"><span class="linenos">108</span></a> -</span><span id="L-109"><a href="#L-109"><span class="linenos">109</span></a> <span class="c1"># If there are no remaining selections, just select a single constant</span> -</span><span id="L-110"><a href="#L-110"><span class="linenos">110</span></a> <span class="k">if</span> <span class="ow">not</span> <span class="n">new_selections</span><span class="p">:</span> -</span><span id="L-111"><a href="#L-111"><span class="linenos">111</span></a> <span class="n">new_selections</span><span class="p">[</span><span class="s2">""</span><span class="p">]</span><span class="o">.</span><span class="n">append</span><span class="p">(</span><span class="n">DEFAULT_SELECTION</span><span class="p">())</span> -</span><span id="L-112"><a href="#L-112"><span class="linenos">112</span></a> -</span><span id="L-113"><a href="#L-113"><span class="linenos">113</span></a> <span class="n">scope</span><span class="o">.</span><span class="n">expression</span><span class="o">.</span><span class="n">select</span><span class="p">(</span><span class="o">*</span><span class="n">flatten</span><span class="p">(</span><span class="n">new_selections</span><span class="o">.</span><span class="n">values</span><span class="p">()),</span> <span class="n">append</span><span class="o">=</span><span class="kc">False</span><span class="p">,</span> <span class="n">copy</span><span class="o">=</span><span class="kc">False</span><span class="p">)</span> +</span><span id="L-77"><a href="#L-77"><span class="linenos"> 77</span></a> <span class="k">return</span> <span class="n">expression</span> +</span><span id="L-78"><a href="#L-78"><span class="linenos"> 78</span></a> +</span><span id="L-79"><a href="#L-79"><span class="linenos"> 79</span></a> +</span><span id="L-80"><a href="#L-80"><span class="linenos"> 80</span></a><span class="k">def</span> <span class="nf">_remove_unused_selections</span><span class="p">(</span><span class="n">scope</span><span class="p">,</span> <span class="n">parent_selections</span><span class="p">,</span> <span class="n">schema</span><span class="p">):</span> +</span><span id="L-81"><a href="#L-81"><span class="linenos"> 81</span></a> <span class="n">order</span> <span class="o">=</span> <span class="n">scope</span><span class="o">.</span><span class="n">expression</span><span class="o">.</span><span class="n">args</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="s2">"order"</span><span class="p">)</span> +</span><span id="L-82"><a href="#L-82"><span class="linenos"> 82</span></a> +</span><span id="L-83"><a href="#L-83"><span class="linenos"> 83</span></a> <span class="k">if</span> <span class="n">order</span><span class="p">:</span> +</span><span id="L-84"><a href="#L-84"><span class="linenos"> 84</span></a> <span class="c1"># Assume columns without a qualified table are references to output columns</span> +</span><span id="L-85"><a href="#L-85"><span class="linenos"> 85</span></a> <span class="n">order_refs</span> <span class="o">=</span> <span class="p">{</span><span class="n">c</span><span class="o">.</span><span class="n">name</span> <span class="k">for</span> <span class="n">c</span> <span class="ow">in</span> <span class="n">order</span><span class="o">.</span><span class="n">find_all</span><span class="p">(</span><span class="n">exp</span><span class="o">.</span><span class="n">Column</span><span class="p">)</span> <span class="k">if</span> <span class="ow">not</span> <span class="n">c</span><span class="o">.</span><span class="n">table</span><span class="p">}</span> +</span><span id="L-86"><a href="#L-86"><span class="linenos"> 86</span></a> <span class="k">else</span><span class="p">:</span> +</span><span id="L-87"><a href="#L-87"><span class="linenos"> 87</span></a> <span class="n">order_refs</span> <span class="o">=</span> <span class="nb">set</span><span class="p">()</span> +</span><span id="L-88"><a href="#L-88"><span class="linenos"> 88</span></a> +</span><span id="L-89"><a href="#L-89"><span class="linenos"> 89</span></a> <span class="n">new_selections</span> <span class="o">=</span> <span class="n">defaultdict</span><span class="p">(</span><span class="nb">list</span><span class="p">)</span> +</span><span id="L-90"><a href="#L-90"><span class="linenos"> 90</span></a> <span class="n">removed</span> <span class="o">=</span> <span class="kc">False</span> +</span><span id="L-91"><a href="#L-91"><span class="linenos"> 91</span></a> <span class="n">star</span> <span class="o">=</span> <span class="kc">False</span> +</span><span id="L-92"><a href="#L-92"><span class="linenos"> 92</span></a> <span class="k">for</span> <span class="n">selection</span> <span class="ow">in</span> <span class="n">scope</span><span class="o">.</span><span class="n">selects</span><span class="p">:</span> +</span><span id="L-93"><a href="#L-93"><span class="linenos"> 93</span></a> <span class="n">name</span> <span class="o">=</span> <span class="n">selection</span><span class="o">.</span><span class="n">alias_or_name</span> +</span><span id="L-94"><a href="#L-94"><span class="linenos"> 94</span></a> +</span><span id="L-95"><a href="#L-95"><span class="linenos"> 95</span></a> <span class="k">if</span> <span class="n">SELECT_ALL</span> <span class="ow">in</span> <span class="n">parent_selections</span> <span class="ow">or</span> <span class="n">name</span> <span class="ow">in</span> <span class="n">parent_selections</span> <span class="ow">or</span> <span class="n">name</span> <span class="ow">in</span> <span class="n">order_refs</span><span class="p">:</span> +</span><span id="L-96"><a href="#L-96"><span class="linenos"> 96</span></a> <span class="n">new_selections</span><span class="p">[</span><span class="n">name</span><span class="p">]</span><span class="o">.</span><span class="n">append</span><span class="p">(</span><span class="n">selection</span><span class="p">)</span> +</span><span id="L-97"><a href="#L-97"><span class="linenos"> 97</span></a> <span class="k">else</span><span class="p">:</span> +</span><span id="L-98"><a href="#L-98"><span class="linenos"> 98</span></a> <span class="k">if</span> <span class="n">selection</span><span class="o">.</span><span class="n">is_star</span><span class="p">:</span> +</span><span id="L-99"><a href="#L-99"><span class="linenos"> 99</span></a> <span class="n">star</span> <span class="o">=</span> <span class="kc">True</span> +</span><span id="L-100"><a href="#L-100"><span class="linenos">100</span></a> <span class="n">removed</span> <span class="o">=</span> <span class="kc">True</span> +</span><span id="L-101"><a href="#L-101"><span class="linenos">101</span></a> +</span><span id="L-102"><a href="#L-102"><span class="linenos">102</span></a> <span class="k">if</span> <span class="n">star</span><span class="p">:</span> +</span><span id="L-103"><a href="#L-103"><span class="linenos">103</span></a> <span class="n">resolver</span> <span class="o">=</span> <span class="n">Resolver</span><span class="p">(</span><span class="n">scope</span><span class="p">,</span> <span class="n">schema</span><span class="p">)</span> +</span><span id="L-104"><a href="#L-104"><span class="linenos">104</span></a> +</span><span id="L-105"><a href="#L-105"><span class="linenos">105</span></a> <span class="k">for</span> <span class="n">name</span> <span class="ow">in</span> <span class="nb">sorted</span><span class="p">(</span><span class="n">parent_selections</span><span class="p">):</span> +</span><span id="L-106"><a href="#L-106"><span class="linenos">106</span></a> <span class="k">if</span> <span class="n">name</span> <span class="ow">not</span> <span class="ow">in</span> <span class="n">new_selections</span><span class="p">:</span> +</span><span id="L-107"><a href="#L-107"><span class="linenos">107</span></a> <span class="n">new_selections</span><span class="p">[</span><span class="n">name</span><span class="p">]</span><span class="o">.</span><span class="n">append</span><span class="p">(</span> +</span><span id="L-108"><a href="#L-108"><span class="linenos">108</span></a> <span class="n">alias</span><span class="p">(</span><span class="n">exp</span><span class="o">.</span><span class="n">column</span><span class="p">(</span><span class="n">name</span><span class="p">,</span> <span class="n">table</span><span class="o">=</span><span class="n">resolver</span><span class="o">.</span><span class="n">get_table</span><span class="p">(</span><span class="n">name</span><span class="p">)),</span> <span class="n">name</span><span class="p">)</span> +</span><span id="L-109"><a href="#L-109"><span class="linenos">109</span></a> <span class="p">)</span> +</span><span id="L-110"><a href="#L-110"><span class="linenos">110</span></a> +</span><span id="L-111"><a href="#L-111"><span class="linenos">111</span></a> <span class="c1"># If there are no remaining selections, just select a single constant</span> +</span><span id="L-112"><a href="#L-112"><span class="linenos">112</span></a> <span class="k">if</span> <span class="ow">not</span> <span class="n">new_selections</span><span class="p">:</span> +</span><span id="L-113"><a href="#L-113"><span class="linenos">113</span></a> <span class="n">new_selections</span><span class="p">[</span><span class="s2">""</span><span class="p">]</span><span class="o">.</span><span class="n">append</span><span class="p">(</span><span class="n">DEFAULT_SELECTION</span><span class="p">())</span> </span><span id="L-114"><a href="#L-114"><span class="linenos">114</span></a> -</span><span id="L-115"><a href="#L-115"><span class="linenos">115</span></a> <span class="k">if</span> <span class="n">removed</span><span class="p">:</span> -</span><span id="L-116"><a href="#L-116"><span class="linenos">116</span></a> <span class="n">scope</span><span class="o">.</span><span class="n">clear_cache</span><span class="p">()</span> +</span><span id="L-115"><a href="#L-115"><span class="linenos">115</span></a> <span class="n">scope</span><span class="o">.</span><span class="n">expression</span><span class="o">.</span><span class="n">select</span><span class="p">(</span><span class="o">*</span><span class="n">flatten</span><span class="p">(</span><span class="n">new_selections</span><span class="o">.</span><span class="n">values</span><span class="p">()),</span> <span class="n">append</span><span class="o">=</span><span class="kc">False</span><span class="p">,</span> <span class="n">copy</span><span class="o">=</span><span class="kc">False</span><span class="p">)</span> +</span><span id="L-116"><a href="#L-116"><span class="linenos">116</span></a> +</span><span id="L-117"><a href="#L-117"><span class="linenos">117</span></a> <span class="k">if</span> <span class="n">removed</span><span class="p">:</span> +</span><span id="L-118"><a href="#L-118"><span class="linenos">118</span></a> <span class="n">scope</span><span class="o">.</span><span class="n">clear_cache</span><span class="p">()</span> </span></pre></div> @@ -202,13 +204,13 @@ <div class="attr function"> <span class="def">def</span> - <span class="name">pushdown_projections</span><span class="signature pdoc-code condensed">(<span class="param"><span class="n">expression</span>, </span><span class="param"><span class="n">schema</span><span class="o">=</span><span class="kc">None</span></span><span class="return-annotation">):</span></span> + <span class="name">pushdown_projections</span><span class="signature pdoc-code condensed">(<span class="param"><span class="n">expression</span>, </span><span class="param"><span class="n">schema</span><span class="o">=</span><span class="kc">None</span>, </span><span class="param"><span class="n">remove_unused_selections</span><span class="o">=</span><span class="kc">True</span></span><span class="return-annotation">):</span></span> <label class="view-source-button" for="pushdown_projections-view-source"><span>View Source</span></label> </div> <a class="headerlink" href="#pushdown_projections"></a> - <div class="pdoc-code codehilite"><pre><span></span><span id="pushdown_projections-17"><a href="#pushdown_projections-17"><span class="linenos">17</span></a><span class="k">def</span> <span class="nf">pushdown_projections</span><span class="p">(</span><span class="n">expression</span><span class="p">,</span> <span class="n">schema</span><span class="o">=</span><span class="kc">None</span><span class="p">):</span> + <div class="pdoc-code codehilite"><pre><span></span><span id="pushdown_projections-17"><a href="#pushdown_projections-17"><span class="linenos">17</span></a><span class="k">def</span> <span class="nf">pushdown_projections</span><span class="p">(</span><span class="n">expression</span><span class="p">,</span> <span class="n">schema</span><span class="o">=</span><span class="kc">None</span><span class="p">,</span> <span class="n">remove_unused_selections</span><span class="o">=</span><span class="kc">True</span><span class="p">):</span> </span><span id="pushdown_projections-18"><a href="#pushdown_projections-18"><span class="linenos">18</span></a><span class="w"> </span><span class="sd">"""</span> </span><span id="pushdown_projections-19"><a href="#pushdown_projections-19"><span class="linenos">19</span></a><span class="sd"> Rewrite sqlglot AST to remove unused columns projections.</span> </span><span id="pushdown_projections-20"><a href="#pushdown_projections-20"><span class="linenos">20</span></a> @@ -221,53 +223,55 @@ </span><span id="pushdown_projections-27"><a href="#pushdown_projections-27"><span class="linenos">27</span></a> </span><span id="pushdown_projections-28"><a href="#pushdown_projections-28"><span class="linenos">28</span></a><span class="sd"> Args:</span> </span><span id="pushdown_projections-29"><a href="#pushdown_projections-29"><span class="linenos">29</span></a><span class="sd"> expression (sqlglot.Expression): expression to optimize</span> -</span><span id="pushdown_projections-30"><a href="#pushdown_projections-30"><span class="linenos">30</span></a><span class="sd"> Returns:</span> -</span><span id="pushdown_projections-31"><a href="#pushdown_projections-31"><span class="linenos">31</span></a><span class="sd"> sqlglot.Expression: optimized expression</span> -</span><span id="pushdown_projections-32"><a href="#pushdown_projections-32"><span class="linenos">32</span></a><span class="sd"> """</span> -</span><span id="pushdown_projections-33"><a href="#pushdown_projections-33"><span class="linenos">33</span></a> <span class="c1"># Map of Scope to all columns being selected by outer queries.</span> -</span><span id="pushdown_projections-34"><a href="#pushdown_projections-34"><span class="linenos">34</span></a> <span class="n">schema</span> <span class="o">=</span> <span class="n">ensure_schema</span><span class="p">(</span><span class="n">schema</span><span class="p">)</span> -</span><span id="pushdown_projections-35"><a href="#pushdown_projections-35"><span class="linenos">35</span></a> <span class="n">referenced_columns</span> <span class="o">=</span> <span class="n">defaultdict</span><span class="p">(</span><span class="nb">set</span><span class="p">)</span> -</span><span id="pushdown_projections-36"><a href="#pushdown_projections-36"><span class="linenos">36</span></a> -</span><span id="pushdown_projections-37"><a href="#pushdown_projections-37"><span class="linenos">37</span></a> <span class="c1"># We build the scope tree (which is traversed in DFS postorder), then iterate</span> -</span><span id="pushdown_projections-38"><a href="#pushdown_projections-38"><span class="linenos">38</span></a> <span class="c1"># over the result in reverse order. This should ensure that the set of selected</span> -</span><span id="pushdown_projections-39"><a href="#pushdown_projections-39"><span class="linenos">39</span></a> <span class="c1"># columns for a particular scope are completely build by the time we get to it.</span> -</span><span id="pushdown_projections-40"><a href="#pushdown_projections-40"><span class="linenos">40</span></a> <span class="k">for</span> <span class="n">scope</span> <span class="ow">in</span> <span class="nb">reversed</span><span class="p">(</span><span class="n">traverse_scope</span><span class="p">(</span><span class="n">expression</span><span class="p">)):</span> -</span><span id="pushdown_projections-41"><a href="#pushdown_projections-41"><span class="linenos">41</span></a> <span class="n">parent_selections</span> <span class="o">=</span> <span class="n">referenced_columns</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="n">scope</span><span class="p">,</span> <span class="p">{</span><span class="n">SELECT_ALL</span><span class="p">})</span> -</span><span id="pushdown_projections-42"><a href="#pushdown_projections-42"><span class="linenos">42</span></a> -</span><span id="pushdown_projections-43"><a href="#pushdown_projections-43"><span class="linenos">43</span></a> <span class="k">if</span> <span class="n">scope</span><span class="o">.</span><span class="n">expression</span><span class="o">.</span><span class="n">args</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="s2">"distinct"</span><span class="p">):</span> -</span><span id="pushdown_projections-44"><a href="#pushdown_projections-44"><span class="linenos">44</span></a> <span class="c1"># We can't remove columns SELECT DISTINCT nor UNION DISTINCT</span> -</span><span id="pushdown_projections-45"><a href="#pushdown_projections-45"><span class="linenos">45</span></a> <span class="n">parent_selections</span> <span class="o">=</span> <span class="p">{</span><span class="n">SELECT_ALL</span><span class="p">}</span> -</span><span id="pushdown_projections-46"><a href="#pushdown_projections-46"><span class="linenos">46</span></a> -</span><span id="pushdown_projections-47"><a href="#pushdown_projections-47"><span class="linenos">47</span></a> <span class="k">if</span> <span class="nb">isinstance</span><span class="p">(</span><span class="n">scope</span><span class="o">.</span><span class="n">expression</span><span class="p">,</span> <span class="n">exp</span><span class="o">.</span><span class="n">Union</span><span class="p">):</span> -</span><span id="pushdown_projections-48"><a href="#pushdown_projections-48"><span class="linenos">48</span></a> <span class="n">left</span><span class="p">,</span> <span class="n">right</span> <span class="o">=</span> <span class="n">scope</span><span class="o">.</span><span class="n">union_scopes</span> -</span><span id="pushdown_projections-49"><a href="#pushdown_projections-49"><span class="linenos">49</span></a> <span class="n">referenced_columns</span><span class="p">[</span><span class="n">left</span><span class="p">]</span> <span class="o">=</span> <span class="n">parent_selections</span> -</span><span id="pushdown_projections-50"><a href="#pushdown_projections-50"><span class="linenos">50</span></a> -</span><span id="pushdown_projections-51"><a href="#pushdown_projections-51"><span class="linenos">51</span></a> <span class="k">if</span> <span class="nb">any</span><span class="p">(</span><span class="n">select</span><span class="o">.</span><span class="n">is_star</span> <span class="k">for</span> <span class="n">select</span> <span class="ow">in</span> <span class="n">right</span><span class="o">.</span><span class="n">selects</span><span class="p">):</span> -</span><span id="pushdown_projections-52"><a href="#pushdown_projections-52"><span class="linenos">52</span></a> <span class="n">referenced_columns</span><span class="p">[</span><span class="n">right</span><span class="p">]</span> <span class="o">=</span> <span class="n">parent_selections</span> -</span><span id="pushdown_projections-53"><a href="#pushdown_projections-53"><span class="linenos">53</span></a> <span class="k">elif</span> <span class="ow">not</span> <span class="nb">any</span><span class="p">(</span><span class="n">select</span><span class="o">.</span><span class="n">is_star</span> <span class="k">for</span> <span class="n">select</span> <span class="ow">in</span> <span class="n">left</span><span class="o">.</span><span class="n">selects</span><span class="p">):</span> -</span><span id="pushdown_projections-54"><a href="#pushdown_projections-54"><span class="linenos">54</span></a> <span class="n">referenced_columns</span><span class="p">[</span><span class="n">right</span><span class="p">]</span> <span class="o">=</span> <span class="p">[</span> -</span><span id="pushdown_projections-55"><a href="#pushdown_projections-55"><span class="linenos">55</span></a> <span class="n">right</span><span class="o">.</span><span class="n">selects</span><span class="p">[</span><span class="n">i</span><span class="p">]</span><span class="o">.</span><span class="n">alias_or_name</span> -</span><span id="pushdown_projections-56"><a href="#pushdown_projections-56"><span class="linenos">56</span></a> <span class="k">for</span> <span class="n">i</span><span class="p">,</span> <span class="n">select</span> <span class="ow">in</span> <span class="nb">enumerate</span><span class="p">(</span><span class="n">left</span><span class="o">.</span><span class="n">selects</span><span class="p">)</span> -</span><span id="pushdown_projections-57"><a href="#pushdown_projections-57"><span class="linenos">57</span></a> <span class="k">if</span> <span class="n">SELECT_ALL</span> <span class="ow">in</span> <span class="n">parent_selections</span> <span class="ow">or</span> <span class="n">select</span><span class="o">.</span><span class="n">alias_or_name</span> <span class="ow">in</span> <span class="n">parent_selections</span> -</span><span id="pushdown_projections-58"><a href="#pushdown_projections-58"><span class="linenos">58</span></a> <span class="p">]</span> -</span><span id="pushdown_projections-59"><a href="#pushdown_projections-59"><span class="linenos">59</span></a> -</span><span id="pushdown_projections-60"><a href="#pushdown_projections-60"><span class="linenos">60</span></a> <span class="k">if</span> <span class="nb">isinstance</span><span class="p">(</span><span class="n">scope</span><span class="o">.</span><span class="n">expression</span><span class="p">,</span> <span class="n">exp</span><span class="o">.</span><span class="n">Select</span><span class="p">):</span> -</span><span id="pushdown_projections-61"><a href="#pushdown_projections-61"><span class="linenos">61</span></a> <span class="n">_remove_unused_selections</span><span class="p">(</span><span class="n">scope</span><span class="p">,</span> <span class="n">parent_selections</span><span class="p">,</span> <span class="n">schema</span><span class="p">)</span> -</span><span id="pushdown_projections-62"><a href="#pushdown_projections-62"><span class="linenos">62</span></a> -</span><span id="pushdown_projections-63"><a href="#pushdown_projections-63"><span class="linenos">63</span></a> <span class="c1"># Group columns by source name</span> -</span><span id="pushdown_projections-64"><a href="#pushdown_projections-64"><span class="linenos">64</span></a> <span class="n">selects</span> <span class="o">=</span> <span class="n">defaultdict</span><span class="p">(</span><span class="nb">set</span><span class="p">)</span> -</span><span id="pushdown_projections-65"><a href="#pushdown_projections-65"><span class="linenos">65</span></a> <span class="k">for</span> <span class="n">col</span> <span class="ow">in</span> <span class="n">scope</span><span class="o">.</span><span class="n">columns</span><span class="p">:</span> -</span><span id="pushdown_projections-66"><a href="#pushdown_projections-66"><span class="linenos">66</span></a> <span class="n">table_name</span> <span class="o">=</span> <span class="n">col</span><span class="o">.</span><span class="n">table</span> -</span><span id="pushdown_projections-67"><a href="#pushdown_projections-67"><span class="linenos">67</span></a> <span class="n">col_name</span> <span class="o">=</span> <span class="n">col</span><span class="o">.</span><span class="n">name</span> -</span><span id="pushdown_projections-68"><a href="#pushdown_projections-68"><span class="linenos">68</span></a> <span class="n">selects</span><span class="p">[</span><span class="n">table_name</span><span class="p">]</span><span class="o">.</span><span class="n">add</span><span class="p">(</span><span class="n">col_name</span><span class="p">)</span> -</span><span id="pushdown_projections-69"><a href="#pushdown_projections-69"><span class="linenos">69</span></a> -</span><span id="pushdown_projections-70"><a href="#pushdown_projections-70"><span class="linenos">70</span></a> <span class="c1"># Push the selected columns down to the next scope</span> -</span><span id="pushdown_projections-71"><a href="#pushdown_projections-71"><span class="linenos">71</span></a> <span class="k">for</span> <span class="n">name</span><span class="p">,</span> <span class="p">(</span><span class="n">_</span><span class="p">,</span> <span class="n">source</span><span class="p">)</span> <span class="ow">in</span> <span class="n">scope</span><span class="o">.</span><span class="n">selected_sources</span><span class="o">.</span><span class="n">items</span><span class="p">():</span> -</span><span id="pushdown_projections-72"><a href="#pushdown_projections-72"><span class="linenos">72</span></a> <span class="k">if</span> <span class="nb">isinstance</span><span class="p">(</span><span class="n">source</span><span class="p">,</span> <span class="n">Scope</span><span class="p">):</span> -</span><span id="pushdown_projections-73"><a href="#pushdown_projections-73"><span class="linenos">73</span></a> <span class="n">columns</span> <span class="o">=</span> <span class="n">selects</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="n">name</span><span class="p">)</span> <span class="ow">or</span> <span class="nb">set</span><span class="p">()</span> -</span><span id="pushdown_projections-74"><a href="#pushdown_projections-74"><span class="linenos">74</span></a> <span class="n">referenced_columns</span><span class="p">[</span><span class="n">source</span><span class="p">]</span><span class="o">.</span><span class="n">update</span><span class="p">(</span><span class="n">columns</span><span class="p">)</span> -</span><span id="pushdown_projections-75"><a href="#pushdown_projections-75"><span class="linenos">75</span></a> -</span><span id="pushdown_projections-76"><a href="#pushdown_projections-76"><span class="linenos">76</span></a> <span class="k">return</span> <span class="n">expression</span> +</span><span id="pushdown_projections-30"><a href="#pushdown_projections-30"><span class="linenos">30</span></a><span class="sd"> remove_unused_selections (bool): remove selects that are unused</span> +</span><span id="pushdown_projections-31"><a href="#pushdown_projections-31"><span class="linenos">31</span></a><span class="sd"> Returns:</span> +</span><span id="pushdown_projections-32"><a href="#pushdown_projections-32"><span class="linenos">32</span></a><span class="sd"> sqlglot.Expression: optimized expression</span> +</span><span id="pushdown_projections-33"><a href="#pushdown_projections-33"><span class="linenos">33</span></a><span class="sd"> """</span> +</span><span id="pushdown_projections-34"><a href="#pushdown_projections-34"><span class="linenos">34</span></a> <span class="c1"># Map of Scope to all columns being selected by outer queries.</span> +</span><span id="pushdown_projections-35"><a href="#pushdown_projections-35"><span class="linenos">35</span></a> <span class="n">schema</span> <span class="o">=</span> <span class="n">ensure_schema</span><span class="p">(</span><span class="n">schema</span><span class="p">)</span> +</span><span id="pushdown_projections-36"><a href="#pushdown_projections-36"><span class="linenos">36</span></a> <span class="n">referenced_columns</span> <span class="o">=</span> <span class="n">defaultdict</span><span class="p">(</span><span class="nb">set</span><span class="p">)</span> +</span><span id="pushdown_projections-37"><a href="#pushdown_projections-37"><span class="linenos">37</span></a> +</span><span id="pushdown_projections-38"><a href="#pushdown_projections-38"><span class="linenos">38</span></a> <span class="c1"># We build the scope tree (which is traversed in DFS postorder), then iterate</span> +</span><span id="pushdown_projections-39"><a href="#pushdown_projections-39"><span class="linenos">39</span></a> <span class="c1"># over the result in reverse order. This should ensure that the set of selected</span> +</span><span id="pushdown_projections-40"><a href="#pushdown_projections-40"><span class="linenos">40</span></a> <span class="c1"># columns for a particular scope are completely build by the time we get to it.</span> +</span><span id="pushdown_projections-41"><a href="#pushdown_projections-41"><span class="linenos">41</span></a> <span class="k">for</span> <span class="n">scope</span> <span class="ow">in</span> <span class="nb">reversed</span><span class="p">(</span><span class="n">traverse_scope</span><span class="p">(</span><span class="n">expression</span><span class="p">)):</span> +</span><span id="pushdown_projections-42"><a href="#pushdown_projections-42"><span class="linenos">42</span></a> <span class="n">parent_selections</span> <span class="o">=</span> <span class="n">referenced_columns</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="n">scope</span><span class="p">,</span> <span class="p">{</span><span class="n">SELECT_ALL</span><span class="p">})</span> +</span><span id="pushdown_projections-43"><a href="#pushdown_projections-43"><span class="linenos">43</span></a> +</span><span id="pushdown_projections-44"><a href="#pushdown_projections-44"><span class="linenos">44</span></a> <span class="k">if</span> <span class="n">scope</span><span class="o">.</span><span class="n">expression</span><span class="o">.</span><span class="n">args</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="s2">"distinct"</span><span class="p">):</span> +</span><span id="pushdown_projections-45"><a href="#pushdown_projections-45"><span class="linenos">45</span></a> <span class="c1"># We can't remove columns SELECT DISTINCT nor UNION DISTINCT</span> +</span><span id="pushdown_projections-46"><a href="#pushdown_projections-46"><span class="linenos">46</span></a> <span class="n">parent_selections</span> <span class="o">=</span> <span class="p">{</span><span class="n">SELECT_ALL</span><span class="p">}</span> +</span><span id="pushdown_projections-47"><a href="#pushdown_projections-47"><span class="linenos">47</span></a> +</span><span id="pushdown_projections-48"><a href="#pushdown_projections-48"><span class="linenos">48</span></a> <span class="k">if</span> <span class="nb">isinstance</span><span class="p">(</span><span class="n">scope</span><span class="o">.</span><span class="n">expression</span><span class="p">,</span> <span class="n">exp</span><span class="o">.</span><span class="n">Union</span><span class="p">):</span> +</span><span id="pushdown_projections-49"><a href="#pushdown_projections-49"><span class="linenos">49</span></a> <span class="n">left</span><span class="p">,</span> <span class="n">right</span> <span class="o">=</span> <span class="n">scope</span><span class="o">.</span><span class="n">union_scopes</span> +</span><span id="pushdown_projections-50"><a href="#pushdown_projections-50"><span class="linenos">50</span></a> <span class="n">referenced_columns</span><span class="p">[</span><span class="n">left</span><span class="p">]</span> <span class="o">=</span> <span class="n">parent_selections</span> +</span><span id="pushdown_projections-51"><a href="#pushdown_projections-51"><span class="linenos">51</span></a> +</span><span id="pushdown_projections-52"><a href="#pushdown_projections-52"><span class="linenos">52</span></a> <span class="k">if</span> <span class="nb">any</span><span class="p">(</span><span class="n">select</span><span class="o">.</span><span class="n">is_star</span> <span class="k">for</span> <span class="n">select</span> <span class="ow">in</span> <span class="n">right</span><span class="o">.</span><span class="n">selects</span><span class="p">):</span> +</span><span id="pushdown_projections-53"><a href="#pushdown_projections-53"><span class="linenos">53</span></a> <span class="n">referenced_columns</span><span class="p">[</span><span class="n">right</span><span class="p">]</span> <span class="o">=</span> <span class="n">parent_selections</span> +</span><span id="pushdown_projections-54"><a href="#pushdown_projections-54"><span class="linenos">54</span></a> <span class="k">elif</span> <span class="ow">not</span> <span class="nb">any</span><span class="p">(</span><span class="n">select</span><span class="o">.</span><span class="n">is_star</span> <span class="k">for</span> <span class="n">select</span> <span class="ow">in</span> <span class="n">left</span><span class="o">.</span><span class="n">selects</span><span class="p">):</span> +</span><span id="pushdown_projections-55"><a href="#pushdown_projections-55"><span class="linenos">55</span></a> <span class="n">referenced_columns</span><span class="p">[</span><span class="n">right</span><span class="p">]</span> <span class="o">=</span> <span class="p">[</span> +</span><span id="pushdown_projections-56"><a href="#pushdown_projections-56"><span class="linenos">56</span></a> <span class="n">right</span><span class="o">.</span><span class="n">selects</span><span class="p">[</span><span class="n">i</span><span class="p">]</span><span class="o">.</span><span class="n">alias_or_name</span> +</span><span id="pushdown_projections-57"><a href="#pushdown_projections-57"><span class="linenos">57</span></a> <span class="k">for</span> <span class="n">i</span><span class="p">,</span> <span class="n">select</span> <span class="ow">in</span> <span class="nb">enumerate</span><span class="p">(</span><span class="n">left</span><span class="o">.</span><span class="n">selects</span><span class="p">)</span> +</span><span id="pushdown_projections-58"><a href="#pushdown_projections-58"><span class="linenos">58</span></a> <span class="k">if</span> <span class="n">SELECT_ALL</span> <span class="ow">in</span> <span class="n">parent_selections</span> <span class="ow">or</span> <span class="n">select</span><span class="o">.</span><span class="n">alias_or_name</span> <span class="ow">in</span> <span class="n">parent_selections</span> +</span><span id="pushdown_projections-59"><a href="#pushdown_projections-59"><span class="linenos">59</span></a> <span class="p">]</span> +</span><span id="pushdown_projections-60"><a href="#pushdown_projections-60"><span class="linenos">60</span></a> +</span><span id="pushdown_projections-61"><a href="#pushdown_projections-61"><span class="linenos">61</span></a> <span class="k">if</span> <span class="nb">isinstance</span><span class="p">(</span><span class="n">scope</span><span class="o">.</span><span class="n">expression</span><span class="p">,</span> <span class="n">exp</span><span class="o">.</span><span class="n">Select</span><span class="p">):</span> +</span><span id="pushdown_projections-62"><a href="#pushdown_projections-62"><span class="linenos">62</span></a> <span class="k">if</span> <span class="n">remove_unused_selections</span><span class="p">:</span> +</span><span id="pushdown_projections-63"><a href="#pushdown_projections-63"><span class="linenos">63</span></a> <span class="n">_remove_unused_selections</span><span class="p">(</span><span class="n">scope</span><span class="p">,</span> <span class="n">parent_selections</span><span class="p">,</span> <span class="n">schema</span><span class="p">)</span> +</span><span id="pushdown_projections-64"><a href="#pushdown_projections-64"><span class="linenos">64</span></a> +</span><span id="pushdown_projections-65"><a href="#pushdown_projections-65"><span class="linenos">65</span></a> <span class="c1"># Group columns by source name</span> +</span><span id="pushdown_projections-66"><a href="#pushdown_projections-66"><span class="linenos">66</span></a> <span class="n">selects</span> <span class="o">=</span> <span class="n">defaultdict</span><span class="p">(</span><span class="nb">set</span><span class="p">)</span> +</span><span id="pushdown_projections-67"><a href="#pushdown_projections-67"><span class="linenos">67</span></a> <span class="k">for</span> <span class="n">col</span> <span class="ow">in</span> <span class="n">scope</span><span class="o">.</span><span class="n">columns</span><span class="p">:</span> +</span><span id="pushdown_projections-68"><a href="#pushdown_projections-68"><span class="linenos">68</span></a> <span class="n">table_name</span> <span class="o">=</span> <span class="n">col</span><span class="o">.</span><span class="n">table</span> +</span><span id="pushdown_projections-69"><a href="#pushdown_projections-69"><span class="linenos">69</span></a> <span class="n">col_name</span> <span class="o">=</span> <span class="n">col</span><span class="o">.</span><span class="n">name</span> +</span><span id="pushdown_projections-70"><a href="#pushdown_projections-70"><span class="linenos">70</span></a> <span class="n">selects</span><span class="p">[</span><span class="n">table_name</span><span class="p">]</span><span class="o">.</span><span class="n">add</span><span class="p">(</span><span class="n">col_name</span><span class="p">)</span> +</span><span id="pushdown_projections-71"><a href="#pushdown_projections-71"><span class="linenos">71</span></a> +</span><span id="pushdown_projections-72"><a href="#pushdown_projections-72"><span class="linenos">72</span></a> <span class="c1"># Push the selected columns down to the next scope</span> +</span><span id="pushdown_projections-73"><a href="#pushdown_projections-73"><span class="linenos">73</span></a> <span class="k">for</span> <span class="n">name</span><span class="p">,</span> <span class="p">(</span><span class="n">_</span><span class="p">,</span> <span class="n">source</span><span class="p">)</span> <span class="ow">in</span> <span class="n">scope</span><span class="o">.</span><span class="n">selected_sources</span><span class="o">.</span><span class="n">items</span><span class="p">():</span> +</span><span id="pushdown_projections-74"><a href="#pushdown_projections-74"><span class="linenos">74</span></a> <span class="k">if</span> <span class="nb">isinstance</span><span class="p">(</span><span class="n">source</span><span class="p">,</span> <span class="n">Scope</span><span class="p">):</span> +</span><span id="pushdown_projections-75"><a href="#pushdown_projections-75"><span class="linenos">75</span></a> <span class="n">columns</span> <span class="o">=</span> <span class="n">selects</span><span class="o">.</span><span class="n">get</span><span class="p">(</span><span class="n">name</span><span class="p">)</span> <span class="ow">or</span> <span class="nb">set</span><span class="p">()</span> +</span><span id="pushdown_projections-76"><a href="#pushdown_projections-76"><span class="linenos">76</span></a> <span class="n">referenced_columns</span><span class="p">[</span><span class="n">source</span><span class="p">]</span><span class="o">.</span><span class="n">update</span><span class="p">(</span><span class="n">columns</span><span class="p">)</span> +</span><span id="pushdown_projections-77"><a href="#pushdown_projections-77"><span class="linenos">77</span></a> +</span><span id="pushdown_projections-78"><a href="#pushdown_projections-78"><span class="linenos">78</span></a> <span class="k">return</span> <span class="n">expression</span> </span></pre></div> @@ -290,6 +294,7 @@ <ul> <li><strong>expression (sqlglot.Expression):</strong> expression to optimize</li> +<li><strong>remove_unused_selections (bool):</strong> remove selects that are unused</li> </ul> <h6 id="returns">Returns:</h6> |