[ASTERIXDB-2334] A range-search on a composite index doesn't work as expected.

• Type: Bug
• Status: Resolved
• Priority: Critical
• Resolution: Fixed
• Assignee: Chen Luo
• Reporter: Taewoo Kim
• Created: 20/Mar/18 21:52
• Updated: 03/Aug/18 17:02
• Resolved: 03/Aug/18 17:02

Description

A range-search query on a composite primary-index doesn't work as expected.

 

The DDL and INSERT statments

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"} );

 

The query

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;

 

The result

{ "startdate": "20080510" }
{ "startdate": "20080511" }
{ "startdate": "20080512" }
{ "startdate": "20080513" }

 

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.

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|