[jira] [Created] (ASTERIXDB-2798) Limit pushdown not working when SQL++ functions are involved

["Gift Sinthong (Jira)" <ji...@apache.org>]

Gift Sinthong created ASTERIXDB-2798:
----------------------------------------

             Summary: Limit pushdown not working when SQL++ functions are involved
                 Key: ASTERIXDB-2798
                 URL: https://issues.apache.org/jira/browse/ASTERIXDB-2798
             Project: Apache AsterixDB
          Issue Type: Bug
          Components: COMP - Compiler, SQL - Translator SQL++
    Affects Versions: 0.9.6
            Reporter: Gift Sinthong


I created a function below.

 
{code:java}
CREATE OR REPLACE FUNCTION drop_columns(r){ (SELECT VALUE OBJECT_REMOVE(t, 'nickname') FROM (SELECT VALUE OBJECT_REMOVE(t, 'gender') FROM (SELECT VALUE t FROM to_array(r) AS t) t) t)[0]};
{code}
Apply it to a dataset.

 

 
{code:java}
SELECT VALUE drop_columns(t) FROM (SELECT VALUE t FROM TinySocial.GleambookUsers t) t LIMIT 5;
{code}
Here is the resulting query plan. We don't get limit pushdown here.

 
{code:java}
distribute result [$$65]
-- DISTRIBUTE_RESULT  |UNPARTITIONED|
  exchange
  -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
    limit 5
    -- STREAM_LIMIT  |UNPARTITIONED|
      exchange
      -- RANDOM_MERGE_EXCHANGE  |PARTITIONED|
        project ([$$65])
        -- STREAM_PROJECT  |PARTITIONED|
          assign [$$65] <- [get-item($$63, 0)]
          -- ASSIGN  |PARTITIONED|
            limit 5
            -- STREAM_LIMIT  |PARTITIONED|
              project ([$$63])
              -- STREAM_PROJECT  |PARTITIONED|
                subplan {
                          aggregate [$$63] <- [listify($$62)]
                          -- AGGREGATE  |LOCAL|
                            assign [$$62] <- [object-remove(object-remove($$t, "gender"), "nickname")]
                            -- ASSIGN  |LOCAL|
                              unnest $$t <- scan-collection($$57)
                              -- UNNEST  |LOCAL|
                                nested tuple source
                                -- NESTED_TUPLE_SOURCE  |LOCAL|
                       }
                -- SUBPLAN  |PARTITIONED|
                  project ([$$57])
                  -- STREAM_PROJECT  |PARTITIONED|
                    assign [$$57] <- [to-array($$t)]
                    -- ASSIGN  |PARTITIONED|
                      project ([$$t])
                      -- STREAM_PROJECT  |PARTITIONED|
                        exchange
                        -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                          data-scan []<-[$$66, $$t] <- TinySocial.GleambookUsers
                          -- DATASOURCE_SCAN  |PARTITIONED|
                            exchange
                            -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                              empty-tuple-source
                              -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
{code}
Instead, if we do the query below we do get limit pushdown.
{code:java}
SELECT VALUE OBJECT_REMOVE(t, 'nickname') FROM (SELECT VALUE OBJECT_REMOVE(t, 'gender') FROM (SELECT VALUE t FROM TinySocial.GleambookUsers AS t) t) t LIMIT 5;
{code}
Here is that query plan.
{code:java}
distribute result [$$37]
-- DISTRIBUTE_RESULT  |UNPARTITIONED|
  exchange
  -- ONE_TO_ONE_EXCHANGE  |UNPARTITIONED|
    limit 5
    -- STREAM_LIMIT  |UNPARTITIONED|
      project ([$$37])
      -- STREAM_PROJECT  |PARTITIONED|
        exchange
        -- SORT_MERGE_EXCHANGE [$$38(ASC) ]  |PARTITIONED|
          project ([$$38, $$37])
          -- STREAM_PROJECT  |PARTITIONED|
            assign [$$37] <- [object-remove(object-remove($$t, "gender"), "nickname")]
            -- ASSIGN  |PARTITIONED|
              limit 5
              -- STREAM_LIMIT  |PARTITIONED|
                exchange
                -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                  data-scan []<-[$$38, $$t] <- TinySocial.GleambookUsers limit 5
                  -- DATASOURCE_SCAN  |PARTITIONED|
                    exchange
                    -- ONE_TO_ONE_EXCHANGE  |PARTITIONED|
                      empty-tuple-source
                      -- EMPTY_TUPLE_SOURCE  |PARTITIONED|
{code}
 

 



--
This message was sent by Atlassian Jira
(v8.3.4#803005)