Taewoo Kim created ASTERIXDB-2334: ------------------------------------- Summary: 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
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)