[
https://issues.apache.org/jira/browse/ASTERIXDB-1959?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Michael J. Carey updated ASTERIXDB-1959:
----------------------------------------
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":{"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;
was:
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;
> 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
> Assignee: Dmitry Lychagin
> 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 (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":{"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)
