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

["Taewoo Kim (JIRA)" <ji...@apache.org>]

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)