You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@asterixdb.apache.org by "ASF subversion and git services (JIRA)" <ji...@apache.org> on 2017/07/11 17:56:00 UTC
[jira] [Commented] (ASTERIXDB-1959) Wrong results from select query
with UNENFORCED INDEX
[ https://issues.apache.org/jira/browse/ASTERIXDB-1959?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16082651#comment-16082651 ]
ASF subversion and git services commented on ASTERIXDB-1959:
------------------------------------------------------------
Commit 443e2dd2a64b618796ef53588e4581542e5e5a40 in asterixdb's branch refs/heads/master from [~dlychagin-cb]
[ https://git-wip-us.apache.org/repos/asf?p=asterixdb.git;h=443e2dd ]
[ASTERIXDB-1959][COMP] Fixed wrong result with non-enforced index
- user model changes: no
- storage format changes: no
- interface changes: no
Details:
- Fixed issue when data is not inserted into a non-enforced secondary
index during ingestion if multiple non-enforced secondary indexes
are defined on the same field but with different indexed types
Change-Id: I0fe3c9c8045411939d3ad946f6e3b6105ebe1f5a
Reviewed-on: https://asterix-gerrit.ics.uci.edu/1872
Sonar-Qube: Jenkins <je...@fulliautomatix.ics.uci.edu>
Tested-by: Jenkins <je...@fulliautomatix.ics.uci.edu>
BAD: Jenkins <je...@fulliautomatix.ics.uci.edu>
Integration-Tests: Jenkins <je...@fulliautomatix.ics.uci.edu>
Reviewed-by: Yingyi Bu <bu...@gmail.com>
> 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
> Components: *DB - AsterixDB, IDX - Indexes, SQL - Translator SQL++
> 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)