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