[ASTERIXDB-1959] Wrong results from select query with UNENFORCED INDEX - ASF JIRA

Details

Type: Bug
Status: Resolved
Priority: Critical
Resolution: Fixed
Affects Version/s: None
Fix Version/s: None
Component/s: *DB - AsterixDB, IDX - Indexes, SQL - Translator SQL++
Labels:
None
Environment:
Mac and latest open source *DB snapshot

Description

Consider the following index (created as part of the repro scenario below):

CREATE INDEX gbUserSinceIdx2 ON GleambookUsers(userSince: int?);

Here are several queries that tickle the index (and its friends in the setup below) in various ways:

SELECT VALUE user
FROM GleambookUsers user
WHERE user.userSince >= datetime('2010-07-22T00:00:00')
AND user.userSince <= datetime('2012-07-29T23:59:59');

SELECT VALUE user
FROM GleambookUsers user
WHERE user.userSince = "yesterday";

SELECT VALUE user
FROM GleambookUsers user
WHERE user.userSince > 900;

The first two work fine, but the last one should return:

{ "id": 777, "alias": "Hillery", "name": "HillaryClinton", "userSince": 1000 }

but it doesn't if the above index is present. (It does fine w/o the index.) You can see this by running the following:

SELECT VALUE user
FROM GleambookUsers user
WHERE user.userSince > 900;

DROP INDEX GleambookUsers.gbUserSinceIdx2;

SELECT VALUE user
FROM GleambookUsers user
WHERE user.userSince > 900;

-------------------------------------------------------------------------------------------

HERE'S A BIG HINT: If the CREATE INDEX statements happen before the INSERT statements, the bug appears. If they are done AFTER the INSERT statements, the correct answer is produced. This leads me to believe that the bug is on the INSERT path - it's not the SELECT query that's the problem, it's the INSERT of the data. (I.e., LOAD works, INSERT doesn't work?) Just a hunch.

-------------------------------------------------------------------------------------------

Here's my little test data setup (adapted from the SQL++ Primer) for repro purposes - cut and paste from here to see the problem.

DROP DATAVERSE TinySocial IF EXISTS;
CREATE DATAVERSE TinySocial;
USE TinySocial;

CREATE TYPE ChirpUserType AS {
screenName: string
};

CREATE TYPE ChirpMessageType AS {
chirpId: string
};

CREATE TYPE GleambookUserType AS {
id: int
};

CREATE TYPE GleambookMessageType AS {
messageId: int
};

CREATE DATASET GleambookUsers(GleambookUserType)
PRIMARY KEY id;

CREATE DATASET GleambookMessages(GleambookMessageType)
PRIMARY KEY messageId;

CREATE DATASET ChirpUsers(ChirpUserType)
PRIMARY KEY screenName;

CREATE DATASET ChirpMessages(ChirpMessageType)
PRIMARY KEY chirpId;

CREATE INDEX gbUserSinceIdx ON GleambookUsers(userSince: datetime?);

CREATE INDEX gbUserSinceIdx2 ON GleambookUsers(userSince: int?);

CREATE INDEX gbAuthorIdx ON GleambookMessages(authorId: int?) TYPE btree;

INSERT INTO ChirpUsers
([

{"screenName":"NathanGiesen@211","lang":"en","friendsCount":18,"statusesCount":473,"name":"Nathan Giesen","followersCount":49416}

,

{"screenName":"ColineGeyer@63","lang":"en","friendsCount":121,"statusesCount":362,"name":"Coline Geyer","followersCount":17159}

,

{"screenName":"NilaMilliron_tw","lang":"en","friendsCount":445,"statusesCount":164,"name":"Nila Milliron","followersCount":22649}

,

{"screenName":"ChangEwing_573","lang":"en","friendsCount":182,"statusesCount":394,"name":"Chang Ewing","followersCount":32136}

]);

INSERT INTO ChirpMessages
([
{"chirpId":"1","user":