[ https://issues.apache.org/jira/browse/ASTERIXDB-2798?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Michael J. Carey reassigned ASTERIXDB-2798: ------------------------------------------- Assignee: Dmitry Lychagin > 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 > Assignee: Dmitry Lychagin > Priority: Major > > 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)