SQLiteDatabase.rawQuery doesn't return correct results using prepared statement

I have a strange and inexplicable error with my prepared SQL statement, it doesn't deliver the same results as the noprepared statement.

Shouldn't these two versions return the same results?

Prepared version (with wrong results):

sb.append(" WHERE c.deck_id=? AND c.next_date < ? AND c.next_date > 0 AND c.active > 0 AND c.deck_level=?");
...
return db.rawQuery(sb.toString(), new String[] { Long.toString(deckId)
, Long.toString(now), Long.toString(level)});

Nonprepared version (works as expected with correct results):

sb.append(" WHERE c.deck_id=").append(deckId)
.append(" AND c.next_date<").append(now)
.append(" AND c.next_date > 0 AND c.active > 0 AND c.deck_level=").append(level);
...
return db.rawQuery(sb.toString(), null);

Where is the erro开发者_C百科r in the preparted statement version?

Update I made additional logs for both versions.

Log.d(TAG, "selectionArgs1:" + Long.toString(deckId) + ""+Long.toString(now)+ ""+Long.toString(level)+ "");

Output: selectionArgs1:*5*1294429481330*5* (SO: doesn't display * in the source?)

StringBuffer d = new StringBuffer("selectionArgs2:");
d.append(deckId).append("").append(now).append("").append(level).append("");
Log.d(TAG, d.toString());

Output: selectionArgs2:*5*1294429481330*5*

---

I suspect its something to do with type affinity but I've not been able to reproduce the issue exactly as you describe.

Affinity behaviour can affect comparison expressions, so the 'unprepared'

next_date < 12345678

might not be not equivalent to the 'prepared':

next_date < '12345678'

Note the implied string data type applied by 'rawQuery' binding - the rawQuery* javadoc says:

The values will be bound as Strings.

I tried to cook up a test case with a next_date INTEGER field to no avail. But, if the field is left untyped, as in:

CREATE TABLE cards ( next_date );

INSERT INTO cards SELECT 1234;
INSERT INTO cards SELECT '1234';

SELECT 'int', typeof( next_date ), next_date FROM cards WHERE next_date < 1234;
SELECT 'str', typeof( next_date ), next_date FROM cards WHERE next_date < '1234';

Then the different affinity behaviour can be seen - the result is:

str|integer|1234

(Perhaps this is a comment, but it's easier to read formatted as an answer.)

*http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#rawQuery(java.lang.String,%20java.lang.String[])