SQLite User Forum

What don't I understand about set theory?
Login
Chronological

What don't I understand about set theory?

(1) By Deon Brewis (deonb) on 2021-01-27 01:23:53 [source] 

SELECT Foo.Id FROM Foo INNER JOIN FooBarLink ON FooBarLink.SourceHash = Foo.Hash AND FooBarLink.TargetResourceHash=x'a0267eaf1cf9e72861f5688876a2211426d5bd00' 

1
2
3
4
5

vs. (Just adding the IN at the end)

SELECT Foo.Id FROM Foo INNER JOIN FooBarLink ON FooBarLink.SourceHash = Foo.Hash AND FooBarLink.TargetResourceHash=x'a0267eaf1cf9e72861f5688876a2211426d5bd00' WHERE Foo.Id IN (1,2,3,4,5)

1
2
3
5

Where'd 4 go in the second query?

SQLITE 3.34.1

I am not able to simplify it, but I have a small .db file (4mb) available that repros this.

(2) By anonymous on 2021-01-27 04:07:55 in reply to 1 [link] [source]

Add typeof(foo.id) to the first query.

(5) By Deon Brewis (deonb) on 2021-01-27 07:36:28 in reply to 2 [link] [source] 

1 | integer
2 | integer
3 | integer
4 | integer
5 | integer

(3) By anonymous on 2021-01-27 06:58:15 in reply to 1 [link] [source]

try:

SELECT Foo.Id
FROM Foo
INNER JOIN FooBarLink
	ON FooBarLink.SourceHash = Foo.HASH
WHERE FooBarLink.TargetResourceHash = x 'a0267eaf1cf9e72861f5688876a2211426d5bd00'
      AND Foo.Id IN (1,2,3,4,5);

The ON predicate specifies the link of one table to another. The WHERE clause filters the results.

(4) By Deon Brewis (deonb) on 2021-01-27 07:35:36 in reply to 3 [link] [source]

Didn't help. (Same result.)

(6) By anonymous on 2021-01-27 07:48:40 in reply to 4 [link] [source]

When you run

SELECT Foo.Id,Foo.Hash
FROM Foo
INNER JOIN FooBarLink
	ON FooBarLink.SourceHash = Foo.HASH
WHERE Foo.ID = 4;

What is the value of Hash? Is it x'a0267eaf1cf9e72861f5688876a2211426d5bd00' ?

(7) By anonymous on 2021-01-27 07:56:06 in reply to 4 [link] [source]

I think you have multiple rows in Foo with ID = 4:

try

SELECT Foo.Id
FROM Foo
INNER JOIN FooBarLink
	ON FooBarLink.SourceHash = Foo.HASH
WHERE FooBarLink.TargetResourceHash = x 'a0267eaf1cf9e72861f5688876a2211426d5bd00'
      OR Foo.Id IN (1,2,3,4,5);

` Note

OR Foo.Id IN (1,2,3,4,5)

(8) By Gunter Hick (gunter_hick) on 2021-01-27 08:02:13 in reply to 1 [link] [source] 

Just some general debugging tips:

- use the explain query plan feature to see what adding the IN constraint does to the query plan
- look at the records/fields involved on both sides of the join
- change the hash fields to TEXT and store them as strings instead of BLOBs until you figure out the problem
- note that TEXT and BLOB fields behave differently for data with embedded 0 characters
- check your schema to make sure you are comparing fields with identical declared types
- check your data to make sure your stored values match the declared types

(9) By Richard Hipp (drh) on 2021-01-27 11:11:16 in reply to 1 [link] [source]

How do I get the test database?

(11) By Deon Brewis (deonb) on 2021-01-27 14:59:12 in reply to 9 [link] [source]

I put a copy of the database on:

https://www.dropbox.com/s/xf532pu5x3yaa2y/Mylo.mylodb?dl=0

The actual query is:

SELECT Media.Id FROM Media INNER JOIN MediaAlbumLink MAL ON MAL.SourceResourceHash = Media.UniqueHash AND MAL.TargetResourceHash=x'a0267eaf1cf9e72861f5688876a2211426d5bd00' WHERE Media.Id IN (10,11,107,162,225)

NOTE: This query works fine on SQLITE 3.33 on that same database file. It does NOT work on SQLITE 3.34.0 and 3.34.1

Let me know if you need to run the extension code. I can reproduce the issue without the extensions loaded though, but you won't e.g. be able to rebuild the indexes. I've however done a reindex, analyze, vacuum and pragma integrity check against the file already.

(13.1) By Richard Hipp (drh) on 2021-01-27 16:08:57 edited from 13.0 in reply to 11 [link] [source]

The problem bisects to this optimization attempt. We are working on it now. Ticket ee51301f316c09e9af9.

(15) By Richard Hipp (drh) on 2021-01-27 19:24:33 in reply to 13.1 [link] [source]

Please rebuild using the latest Prerelease Snapshot and try again. Let us know if it works better for you.

(10) By Dan Kennedy (dan) on 2021-01-27 11:14:00 in reply to 1 [link] [source]

What does running:

    PRAGMA integrity_check

against the db file show?

(12) By Deon Brewis (deonb) on 2021-01-27 15:01:28 in reply to 10 [link] [source]

PRAGMA integrity_check, analyze, reindex and vacuum all shows good.

NOTE: It also works fine against SQLITE 3.33, just not 3.34.

(14) By anonymous on 2021-01-27 17:28:03 in reply to 12 [link] [source]

Using 3.34.1

sqlite> .open ./db/Mylo.mylodb
sqlite> vacuum;
Error: no such function: KindCode
sqlite>