sqlite3: “IN” works, “NOT IN” doesn't
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
This query works:
select id from wagtailcore_pagerevision
where id in (select live_revision_id as id from wagtailcore_page)
This query produces no results:
select id from wagtailcore_pagerevision
where id not in (select live_revision_id as id from wagtailcore_page)
(The difference is in vs. not in.)
... but I can very plainly see by inspection of the data that the result of the second query should be many hundreds of rows.
An index does exist on the live_revision_id column, and id of course is a primary key.
sqlite3
asked Nov 29 '18 at 1:17
Mike RobinsonMike Robinson
4,09621022
add a comment |
This query works:
select id from wagtailcore_pagerevision
where id in (select live_revision_id as id from wagtailcore_page)
This query produces no results:
select id from wagtailcore_pagerevision
where id not in (select live_revision_id as id from wagtailcore_page)
(The difference is in vs. not in.)
... but I can very plainly see by inspection of the data that the result of the second query should be many hundreds of rows.
An index does exist on the live_revision_id column, and id of course is a primary key.
sqlite3
asked Nov 29 '18 at 1:17
Mike RobinsonMike Robinson
4,09621022
What do you expect the second one to return? Are there any id values in the pagerevision table that aren't present in that column of the page table?
– Shawn
Nov 29 '18 at 1:19
There are about 600 entries inwagtailcore_pagerevisionand 15 inwagtailcore_page. The first query unsurprisingly returns 15 rows. The second query should return about 585.
– Mike Robinson
Nov 29 '18 at 1:24
add a comment |
This query works:
select id from wagtailcore_pagerevision
where id in (select live_revision_id as id from wagtailcore_page)
This query produces no results:
select id from wagtailcore_pagerevision
where id not in (select live_revision_id as id from wagtailcore_page)
(The difference is in vs. not in.)
... but I can very plainly see by inspection of the data that the result of the second query should be many hundreds of rows.
An index does exist on the live_revision_id column, and id of course is a primary key.
sqlite3
asked Nov 29 '18 at 1:17
Mike RobinsonMike Robinson
4,09621022
This query works:
select id from wagtailcore_pagerevision
where id in (select live_revision_id as id from wagtailcore_page)
This query produces no results:
select id from wagtailcore_pagerevision
where id not in (select live_revision_id as id from wagtailcore_page)
(The difference is in vs. not in.)
... but I can very plainly see by inspection of the data that the result of the second query should be many hundreds of rows.
An index does exist on the live_revision_id column, and id of course is a primary key.
sqlite3
sqlite3
asked Nov 29 '18 at 1:17
Mike RobinsonMike Robinson
4,09621022
asked Nov 29 '18 at 1:17
Mike RobinsonMike Robinson
4,09621022
asked Nov 29 '18 at 1:17
Mike RobinsonMike Robinson
4,09621022
asked Nov 29 '18 at 1:17
Mike RobinsonMike Robinson
4,09621022
asked Nov 29 '18 at 1:17
Mike RobinsonMike Robinson
4,09621022
4,09621022
What do you expect the second one to return? Are there any id values in the pagerevision table that aren't present in that column of the page table?
– Shawn
Nov 29 '18 at 1:19
There are about 600 entries inwagtailcore_pagerevisionand 15 inwagtailcore_page. The first query unsurprisingly returns 15 rows. The second query should return about 585.
– Mike Robinson
Nov 29 '18 at 1:24
add a comment |
What do you expect the second one to return? Are there any id values in the pagerevision table that aren't present in that column of the page table?
– Shawn
Nov 29 '18 at 1:19
There are about 600 entries inwagtailcore_pagerevisionand 15 inwagtailcore_page. The first query unsurprisingly returns 15 rows. The second query should return about 585.
– Mike Robinson
Nov 29 '18 at 1:24
What do you expect the second one to return? Are there any id values in the pagerevision table that aren't present in that column of the page table?
– Shawn
Nov 29 '18 at 1:19
What do you expect the second one to return? Are there any id values in the pagerevision table that aren't present in that column of the page table?
– Shawn
Nov 29 '18 at 1:19
There are about 600 entries in
wagtailcore_pagerevision and 15 in wagtailcore_page. The first query unsurprisingly returns 15 rows. The second query should return about 585.– Mike Robinson
Nov 29 '18 at 1:24
There are about 600 entries in
wagtailcore_pagerevision and 15 in wagtailcore_page. The first query unsurprisingly returns 15 rows. The second query should return about 585.– Mike Robinson
Nov 29 '18 at 1:24
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53530477%2fsqlite3-in-works-not-in-doesnt%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53530477%2fsqlite3-in-works-not-in-doesnt%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
What do you expect the second one to return? Are there any id values in the pagerevision table that aren't present in that column of the page table?
– Shawn
Nov 29 '18 at 1:19
There are about 600 entries in
wagtailcore_pagerevisionand 15 inwagtailcore_page. The first query unsurprisingly returns 15 rows. The second query should return about 585.– Mike Robinson
Nov 29 '18 at 1:24