[GitHub] kkhatua commented on issue #1608: DRILL-6960: Auto Limit Wrapping should not apply to non-select query

[GitBox]

kkhatua commented on issue #1608: DRILL-6960: Auto Limit Wrapping should not 
apply to non-select query
URL: https://github.com/apache/drill/pull/1608#issuecomment-459920386
 
 
   @ihuzenko 
   With the Calcite `SqlNode` modification approach that you've suggested, I'm 
not able to apply limits for queries with order by.
   Looking at Calcite's `SqlKind` sets, I see that I need to be able to apply 
this for all entries of `SqlKind.QUERY` collection
   ```
      SELECT
      ORDER_BY
      WITH
      UNION
      EXCEPT
      INTERSECT
      VALUES
      EXPLICIT_TABLE
   ```
   However, the `setFetch()` method is only available for `SELECT` and I cannot 
do this for select queries with an ORDER_BY!
   I see the same challenge for `UNION`, etc. Recursively retrieving the 
SqlNode to arrive at a SELECT instance will make the query verbose.
   
   Looking at the test queries I used, the original approach of wrapping the 
query seems the least complex. The only modification I would have to make is to 
check if the sqlKind belongs to the `SqlKind.QUERY` collection to decide if the 
query can be wrapped. So, far, the following queries worked with this check:
   ```sql
   --SqlKind: WITH
   with X1 (r_regionkey, r_name, n_nationkey, n_name) as 
     (select r.r_regionkey as r_regionkey, r.r_name as r_name, n.n_nationkey as 
n_nationkey, n.n_name as n_name from dfs.par100.region r, dfs.par100.nation n 
where r.r_regionkey=n.n_regionkey)
    select X1.n_name as nation, X1.r_name as region, count(*) as tally 
     from X1, dfs.par100.customer c 
     where X1.n_nationkey = c.c_nationkey
     group by X1.n_name, X1.r_name
     order by tally
   
   --SqlKind: ORDER_BY 
   select r.r_name as region, n.n_name as nation
    , count(*) as tally 
     from dfs.par100.region r, dfs.par100.nation n 
     where r.r_regionkey=n.n_regionkey
     group by n.n_name, r.r_name
     order by tally
   
   -- SqlKind: UNION 
   select r_name as name, r_regionkey as code from dfs.par100.region
   UNION
   select n_name as name, n_nationkey as code from dfs.par100.nation 
   
   -- Simple Join
   select r.r_regionkey as r_regionkey, r.r_name as r_name, n.n_nationkey as 
n_nationkey, n.n_name as n_name from dfs.par100.region r, dfs.par100.nation n 
where r.r_regionkey=n.n_regionkey
   
   -- Simple Join with existing Limit 11 less than autolimit value of 13. 
Results were limited to 11
   select r.r_regionkey as r_regionkey, r.r_name as r_name, n.n_nationkey as 
n_nationkey, n.n_name as n_name from dfs.par100.region r, dfs.par100.nation n 
where r.r_regionkey=n.n_regionkey limit 11
   
   -- Simple Join with existing Limit 22 exceeding autolimit value of 13. 
Results were limited to 13
   select r.r_regionkey as r_regionkey, r.r_name as r_name, n.n_nationkey as 
n_nationkey, n.n_name as n_name from dfs.par100.region r, dfs.par100.nation n 
where r.r_regionkey=n.n_regionkey limit 22
   ```
   
   I will be adding a new commit that incorporates most of your review comments 
except for the `applyAutoLimit()` method and will continue in favor of 
`wrapWithAutoLimit()` with a check for `SqlKind` property of the `SqlNode`. 
   

----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on GitHub and use the
URL above to go to the specific comment.
 
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org


With regards,
Apache Git Services