SQLite Left Outer Join not working as expected in Android

Question

I have prepared a left outer join query that should be returning non-null data when a record exists in my BLOB table that matches keys with a record in my INCIDENT table.

Essentially, the data and query looks like this:

Table A
   Key

Table B
   Key
   Blob

and the query I'm running, should show all records from A, and the 'hasb' column should be 1 if there's a record in B with a matching key column, or 0 if there isn't. So the query should look like this:

SELECT A.*, ifnull(A.Key = B.Key, 0) as hasb FROM A
LEFT OUTER JOIN B ON A.Key = B.Key

Ok, so my problem is, this query seems to work everywhere I try to use it EXCEPT on the Android device.

Here's a SQLFiddle with the actual tables and query in question. Note that the query works there.

http://sqlfiddle.com/#!7/89e7d/4

Anyone know why this doesn't work on Android? The device I'm testing with is a Samsung Galaxy S 3 running Android 4.1.1.

Note the left join you provided in your fiddle is not the same as the one in your example. You're joining in 2 conditions, not just one. — Mosty Mostacho, Oct 01 '13 at 17:56

score 2 · Answer 1 · answered Oct 01 '13 at 17:54

I think you're overcomplicating things with the ifnull.

This is the query in your fiddle:

SELECT ifnull( a.userid = b.userid, 0 ) FROM incidentdata AS a
LEFT OUTER JOIN incidentblob AS b
ON ( a.userid = b.userid ) AND ( a.incidenttag = b.incidenttag );

This is the query I would write:

SELECT a.userid, a.incidenttag, b._id, b._id is not null hasb
FROM incidentdata AS a
LEFT JOIN incidentblob AS b
ON a.userid = b.userid AND a.incidenttag = b.incidenttag

Does it work? If it doesn't please, provide the SQLite version you're using. Anyway, I'm pretty sure the issue resides in the following items:

Understanding that null = null does not return TRUE but rather null
null values being present in both b.userid as well as a.incidenttag after performing the left join

score 0 · Answer 2 · answered Oct 02 '13 at 08:51

I know your query "seems to work everywhere".

However, I suggest you an alternative one:

SELECT a.*, b.ROWID IS NOT NULL AS hasb FROM a LEFT JOIN b ON a.key IS b.key;

Differences:

Avoid IFNULL function call;
a.key IS b.key will handle a null key value;
checking b.ROWID is b row data independent.

SQLite Left Outer Join not working as expected in Android

2 Answers2