Michael J. Carey created ASTERIXDB-1959:
-------------------------------------------
Summary: Wrong results from select query with UNENFORCED INDEX
Key: ASTERIXDB-1959
URL: https://issues.apache.org/jira/browse/ASTERIXDB-1959
Project: Apache AsterixDB
Issue Type: Bug
Environment: Mac and latest open source *DB snapshot
Reporter: Michael J. Carey
Priority: Critical
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 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":{"screenName":"NathanGiesen@211","lang":"en","friendsCount":39339,"statusesCount":473,"name":"Nathan
Giesen","followersCount":49416},"senderLocation":point("47.44,80.65"),"sendTime":datetime("2008-04-26T10:10:00"),"referredTopics":{{"product-z","customization"}},"messageText":"
love product-z its customization is good:)"},
{"chirpId":"2","user":{"screenName":"ColineGeyer@63","lang":"en","friendsCount":121,"statusesCount":362,"name":"Coline
Geyer","followersCount":17159},"senderLocation":point("32.84,67.14"),"sendTime":datetime("2010-05-13T10:10:00"),"referredTopics":{{"ccast","shortcut-menu"}},"messageText":"
like ccast its shortcut-menu is awesome:)"},
{"chirpId":"3","user":{"screenName":"NathanGiesen@211","lang":"en","friendsCount":39339,"statusesCount":473,"name":"Nathan
Giesen","followersCount":49416},"senderLocation":point("29.72,75.8"),"sendTime":datetime("2006-11-04T10:10:00"),"referredTopics":{{"product-w","speed"}},"messageText":"
like product-w the speed is good:)"},
{"chirpId":"4","user":{"screenName":"NathanGiesen@211","lang":"en","friendsCount":39339,"statusesCount":473,"name":"Nathan
Giesen","followersCount":49416},"senderLocation":point("39.28,70.48"),"sendTime":datetime("2011-12-26T10:10:00"),"referredTopics":{{"product-b","voice-command"}},"messageText":"
like product-b the voice-command is mind-blowing:)"},
{"chirpId":"5","user":{"screenName":"NathanGiesen@211","lang":"en","friendsCount":39339,"statusesCount":473,"name":"Nathan
Giesen","followersCount":49416},"senderLocation":point("40.09,92.69"),"sendTime":datetime("2006-08-04T10:10:00"),"referredTopics":{{"product-w","speed"}},"messageText":"
can't stand product-w its speed is terrible:("},
{"chirpId":"6","user":{"screenName":"ColineGeyer@63","lang":"en","friendsCount":121,"statusesCount":362,"name":"Coline
Geyer","followersCount":17159},"senderLocation":point("47.51,83.99"),"sendTime":datetime("2010-05-07T10:10:00"),"referredTopics":{{"x-phone","voice-clarity"}},"messageText":"
like x-phone the voice-clarity is good:)"},
{"chirpId":"7","user":{"screenName":"ChangEwing_573","lang":"en","friendsCount":182,"statusesCount":394,"name":"Chang
Ewing","followersCount":32136},"senderLocation":point("36.21,72.6"),"sendTime":datetime("2011-08-25T10:10:00"),"referredTopics":{{"product-y","platform"}},"messageText":"
like product-y the platform is good"},
{"chirpId":"8","user":{"screenName":"NathanGiesen@211","lang":"en","friendsCount":39339,"statusesCount":473,"name":"Nathan
Giesen","followersCount":49416},"senderLocation":point("46.05,93.34"),"sendTime":datetime("2005-10-14T10:10:00"),"referredTopics":{{"product-z","shortcut-menu"}},"messageText":"
like product-z the shortcut-menu is awesome:)"},
{"chirpId":"9","user":{"screenName":"NathanGiesen@211","lang":"en","friendsCount":39339,"statusesCount":473,"name":"Nathan
Giesen","followersCount":49416},"senderLocation":point("36.86,74.62"),"sendTime":datetime("2012-07-21T10:10:00"),"referredTopics":{{"ccast","voicemail-service"}},"messageText":"
love ccast its voicemail-service is awesome"},
{"chirpId":"10","user":{"screenName":"ColineGeyer@63","lang":"en","friendsCount":121,"statusesCount":362,"name":"Coline
Geyer","followersCount":17159},"senderLocation":point("29.15,76.53"),"sendTime":datetime("2008-01-26T10:10:00"),"referredTopics":{{"ccast","voice-clarity"}},"messageText":"
hate ccast its voice-clarity is OMG:("},
{"chirpId":"11","user":{"screenName":"NilaMilliron_tw","lang":"en","friendsCount":445,"statusesCount":164,"name":"Nila
Milliron","followersCount":22649},"senderLocation":point("37.59,68.42"),"sendTime":datetime("2008-03-09T10:10:00"),"referredTopics":{{"x-phone","platform"}},"messageText":"
can't stand x-phone its platform is terrible"},
{"chirpId":"12","user":{"screenName":"OliJackson_512","lang":"en","friendsCount":445,"statusesCount":164,"name":"Oli
Jackson","followersCount":22649},"senderLocation":point("24.82,94.63"),"sendTime":datetime("2010-02-13T10:10:00"),"referredTopics":{{"product-y","voice-command"}},"messageText":"
like product-y the voice-command is amazing:)"}
]);
INSERT INTO GleambookUsers
([
{"id":1,"alias":"Margarita","name":"MargaritaStoddard","nickname":"Mags","userSince":datetime("2012-08-20T10:10:00"),"friendIds":{{2,3,6,10}},"employment":[{"organizationName":"Codetechno","startDate":date("2006-08-06")},{"organizationName":"geomedia","startDate":date("2010-06-17"),"endDate":date("2010-01-26")}],"gender":"F"},
{"id":2,"alias":"Isbel","name":"IsbelDull","nickname":"Izzy","userSince":datetime("2011-01-22T10:10:00"),"friendIds":{{1,4}},"employment":[{"organizationName":"Hexviafind","startDate":date("2010-04-27")}]},
{"id":3,"alias":"Emory","name":"EmoryUnk","userSince":datetime("2012-07-10T10:10:00"),"friendIds":{{1,5,8,9}},"employment":[{"organizationName":"geomedia","startDate":date("2010-06-17"),"endDate":date("2010-01-26")}]},
{"id":4,"alias":"Nicholas","name":"NicholasStroh","userSince":datetime("2010-12-27T10:10:00"),"friendIds":{{2}},"employment":[{"organizationName":"Zamcorporation","startDate":date("2010-06-08")}]},
{"id":5,"alias":"Von","name":"VonKemble","userSince":datetime("2010-01-05T10:10:00"),"friendIds":{{3,6,10}},"employment":[{"organizationName":"Kongreen","startDate":date("2010-11-27")}]},
{"id":6,"alias":"Willis","name":"WillisWynne","userSince":datetime("2005-01-17T10:10:00"),"friendIds":{{1,3,7}},"employment":[{"organizationName":"jaydax","startDate":date("2009-05-15")}]},
{"id":7,"alias":"Suzanna","name":"SuzannaTillson","userSince":datetime("2012-08-07T10:10:00"),"friendIds":{{6}},"employment":[{"organizationName":"Labzatron","startDate":date("2011-04-19")}]},
{"id":8,"alias":"Nila","name":"NilaMilliron","userSince":datetime("2008-01-01T10:10:00"),"friendIds":{{3}},"employment":[{"organizationName":"Plexlane","startDate":date("2010-02-28")}]},
{"id":9,"alias":"Woodrow","name":"WoodrowNehling","nickname":"Woody","userSince":datetime("2005-09-20T10:10:00"),"friendIds":{{3,10}},"employment":[{"organizationName":"Zuncan","startDate":date("2003-04-22"),"endDate":date("2009-12-13")}]},
{"id":10,"alias":"Bram","name":"BramHatch","userSince":datetime("2010-10-16T10:10:00"),"friendIds":{{1,5,9}},"employment":[{"organizationName":"physcane","startDate":date("2007-06-05"),"endDate":date("2011-11-05")}]},
{"id":666,"alias":"RealDonaldTrump","name":"DonaldTrump","userSince":"yesterday"},
{"id":777,"alias":"Hillery","name":"HillaryClinton","userSince":1000}
]);
INSERT INTO GleambookMessages
([
{"messageId":1,"authorId":3,"inResponseTo":2,"senderLocation":point("47.16,77.75"),"message":"
love product-b its shortcut-menu is awesome:)"},
{"messageId":2,"authorId":1,"inResponseTo":4,"senderLocation":point("41.66,80.87"),"message":"
dislike x-phone its touch-screen is horrible"},
{"messageId":3,"authorId":2,"inResponseTo":4,"senderLocation":point("48.09,81.01"),"message":"
like product-y the plan is amazing"},
{"messageId":4,"authorId":1,"inResponseTo":2,"senderLocation":point("37.73,97.04"),"message":"
can't stand acast the network is horrible:("},
{"messageId":5,"authorId":6,"inResponseTo":2,"senderLocation":point("34.7,90.76"),"message":"
love product-b the customization is mind-blowing"},
{"messageId":6,"authorId":2,"inResponseTo":1,"senderLocation":point("31.5,75.56"),"message":"
like product-z its platform is mind-blowing"},
{"messageId":7,"authorId":5,"inResponseTo":15,"senderLocation":point("32.91,85.05"),"message":"
dislike product-b the speed is horrible"},
{"messageId":8,"authorId":1,"inResponseTo":11,"senderLocation":point("40.33,80.87"),"message":"
like ccast the 3G is awesome:)"},
{"messageId":9,"authorId":3,"inResponseTo":12,"senderLocation":point("34.45,96.48"),"message":"
love ccast its wireless is good"},
{"messageId":10,"authorId":1,"inResponseTo":12,"senderLocation":point("42.5,70.01"),"message":"
can't stand product-w the touch-screen is terrible"},
{"messageId":11,"authorId":1,"inResponseTo":1,"senderLocation":point("38.97,77.49"),"message":"
can't stand acast its plan is terrible"},
{"messageId":12,"authorId":10,"inResponseTo":6,"senderLocation":point("42.26,77.76"),"message":"
can't stand product-z its voicemail-service is OMG:("},
{"messageId":13,"authorId":10,"inResponseTo":4,"senderLocation":point("42.77,78.92"),"message":"
dislike x-phone the voice-command is bad:("},
{"messageId":14,"authorId":9,"inResponseTo":12,"senderLocation":point("41.33,85.28"),"message":"
love acast its 3G is good:)"},
{"messageId":15,"authorId":7,"inResponseTo":11,"senderLocation":point("44.47,67.11"),"message":"
like x-phone the voicemail-service is awesome"}
]);
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;
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
