[ https://issues.apache.org/jira/browse/ASTERIXDB-2334?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16411711#comment-16411711 ]
Chen Luo commented on ASTERIXDB-2334: ------------------------------------- In this case, the SELECT operator should not be needed because 'startdate' is the last composite key, and it just works as a normal range search after determining the prefix. There might be something wrong when during composite search. > A range-search on a composite index doesn't work as expected. > ------------------------------------------------------------- > > Key: ASTERIXDB-2334 > URL: https://issues.apache.org/jira/browse/ASTERIXDB-2334 > Project: Apache AsterixDB > Issue Type: Bug > Reporter: Taewoo Kim > Assignee: Dmitry Lychagin > Priority: Critical > > A range-search query on a composite primary-index doesn't work as expected. > > The DDL and INSERT statments > {code:java} > DROP DATAVERSE earthquake IF EXISTS; > CREATE DATAVERSE earthquake; > USE earthquake; > CREATE TYPE QzExternalTypeNew AS { > stationid: string, > pointid: string, > itemid: string, > samplerate: string, > startdate: string, > obsvalue: string > }; > CREATE DATASET qz9130all(QzExternalTypeNew) PRIMARY KEY > stationid,pointid,itemid,samplerate,startdate; > INSERT INTO qz9130all( > {"stationid":"01","pointid":"5","itemid":"9130","samplerate":"01","startdate":"20080509","obsvalue":"9"} > ); > INSERT INTO qz9130all( > {"stationid":"01","pointid":"5","itemid":"9130","samplerate":"01","startdate":"20080510","obsvalue":"9"} > ); > INSERT INTO qz9130all( > {"stationid":"01","pointid":"5","itemid":"9130","samplerate":"01","startdate":"20080511","obsvalue":"9"} > ); > INSERT INTO qz9130all( > {"stationid":"01","pointid":"5","itemid":"9130","samplerate":"01","startdate":"20080512","obsvalue":"9"} > ); > INSERT INTO qz9130all( > {"stationid":"01","pointid":"5","itemid":"9130","samplerate":"01","startdate":"20080513","obsvalue":"9"} > ); > INSERT INTO qz9130all( > {"stationid":"01","pointid":"5","itemid":"9130","samplerate":"01","startdate":"20080514","obsvalue":"9"} > ); > INSERT INTO qz9130all( > {"stationid":"01","pointid":"5","itemid":"9130","samplerate":"01","startdate":"20080515","obsvalue":"9"} > ); > {code} > > The query > {code:java} > SELECT startdate > FROM qz9130all > WHERE samplerate='01' and stationid='01' and pointid='5' and itemid='9130' > and startdate >= '20080510' and startdate < '20080513' > ORDER BY startdate;{code} > > The result > {code:java} > { "startdate": "20080510" } > { "startdate": "20080511" } > { "startdate": "20080512" } > { "startdate": "20080513" }{code} > > The last row should be filtered. As the following plan shows, there's no > SELECT operator. The optimizer thinks that the primary-index search can > generate the final answer. But, it doesn't. There are false positive results. > {code:java} > distribute result [$$25] > -- DISTRIBUTE_RESULT |PARTITIONED| > exchange > -- ONE_TO_ONE_EXCHANGE |PARTITIONED| > project ([$$25]) > -- STREAM_PROJECT |PARTITIONED| > assign [$$25] <- [{"startdate": $$32}] > -- ASSIGN |PARTITIONED| > exchange > -- SORT_MERGE_EXCHANGE [$$32(ASC) ] |PARTITIONED| > order (ASC, $$32) > -- STABLE_SORT [$$32(ASC)] |PARTITIONED| > exchange > -- ONE_TO_ONE_EXCHANGE |PARTITIONED| > project ([$$32]) > -- STREAM_PROJECT |PARTITIONED| > exchange > -- ONE_TO_ONE_EXCHANGE |PARTITIONED| > unnest-map [$$28, $$29, $$30, $$31, $$32, $$qz9130all] <- > index-search("qz9130all", 0, "earthquake", "qz9130all", FALSE, FALSE, 5, > $$38, $$39, $$40, $$41, $$42, 5, $$43, $$44, $$45, $$46, $$47, TRUE, TRUE, > TRUE) > -- BTREE_SEARCH |PARTITIONED| > exchange > -- ONE_TO_ONE_EXCHANGE |PARTITIONED| > assign [$$38, $$39, $$40, $$41, $$42, $$43, $$44, $$45, > $$46, $$47] <- ["01", "5", "9130", "01", "20080510", "01", "5", "9130", "01", > "20080513"] > -- ASSIGN |PARTITIONED| > empty-tuple-source > -- EMPTY_TUPLE_SOURCE |PARTITIONED|{code} > > > -- This message was sent by Atlassian JIRA (v7.6.3#76005)