CODEWITHSUNDEEP
androidsqlsqliteandroid-cursor
ghempton
ghempton

Reputation: 7947

Android SQLite negative values with ORDER BY

I have a very basic situation:

I have a table with around 5k rows:

CREATE TABLE "words" ("id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , "name" TEXT NOT NULL , "def" TEXT NOT NULL, "rand" INTEGER)

Which I periodically update using "UPDATE words SET rand=random()"

In android, when I create a cursor using rawQuery() using the following:

SELECT w.id, w.name, w.def, w.rand FROM words w ORDER BY w.rand ASC;

The returned cursor does not iterate in the correct order. E.g. it will output columns with rand values in the following order:

-1298882092
-2138143484
-1115732861
118839193
...

Does anyone know whats going on here? Shouldn't this work? If I run the exact same query in SQLiteManager it returns the results in the correct order, so this seems to be android/cursor specific.

UPDATE:

Here is the code in android, I have tried multiple ways:

Attempt 1:

Cursor cursor = db.rawQuery("SELECT w.id, w.name, w.def, w.rand FROM words w ORDER BY w.rand ASC", new String[]{});

Attempt 2:

Cursor cursor = db.query("words", new String[]{"id", "name", "def", "rand"},
            null, null, null, null, "rand ASC");

In both cases I iterate like the following:

while(cursor.moveToNext()) {
    ...
    Log.i("Test", cursor.getInt(3));
    ...
}

Upvotes: 7

Views: 12967

Answers (2)

jedi
jedi

Reputation: 859

I had a problem with android cursor, rawquery and group by. The result was not ordered.

This is the table:

public static final String I_ID = "iid";
public static final String I_QID = "qid";
public static final String I_STATUS = "status";
public static final String I_PDATE = "pub_date";
public static final String I_TYPE = "tipo";
public static final String I_TITLE = "titolo";
public static final String I_LINK = "link";
public static final String I_QR = "qrcode";
public static final String I_DESC = "descrizione";

private static final String C_ITEM_T = 
        "CREATE TABLE \""+T_ITEM+"\" ("+
        "\""+I_ID+"\" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"+
        "\""+I_QID+"\" INTEGER NOT NULL REFERENCES "+T_QUERY+"("+Q_ID+") ,"+
        "\""+I_STATUS+"\" INTEGER NOT NULL,"+
        "\""+I_PDATE+"\" TEXT NOT NULL,"+
        "\""+I_TYPE+"\" TEXT NOT NULL,"+
        "\""+I_TITLE+"\" TEXT NOT NULL,"+
        "\""+I_LINK+"\" TEXT UNIQUE NOT NULL,"+
        "\""+I_QR+"\" TEXT UNIQUE NOT NULL,"+
        "\""+I_DESC+"\" TEXT"+
        ");";

public static final String A_ID = "aid";
public static final String A_IID = "iid";
public static final String A_SIZE = "grandezza";
public static final String A_DURATION = "durata";
public static final String A_CHANNELS = "canali";
public static final String A_RATE = "sampleRate";
public static final String A_TORRENT = "torrent";
public static final String A_DOWNLOAD = "download";
public static final String A_LICENSE_URL = "licenza_url";
public static final String A_TAGS = "tags";
public static final String A_CREATOR = "creatore";

private static final String C_ATTR_T = 
        "CREATE TABLE \""+T_ATTR+"\" ("+
        "\""+A_ID+"\" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"+
        "\""+A_IID+"\" INTEGER NOT NULL REFERENCES "+T_ITEM+"("+I_ID+") ,"+
        "\""+A_SIZE+"\" LONG NOT NULL,"+
        "\""+A_DURATION+"\" LONG NOT NULL,"+
        "\""+A_CHANNELS+"\" TEXT NOT NULL,"+
        "\""+A_RATE+"\" TEXT NOT NULL,"+
        "\""+A_TORRENT+"\" TEXT NOT NULL,"+
        "\""+A_DOWNLOAD+"\" TEXT NOT NULL,"+
        "\""+A_LICENSE_URL+"\"TEXT,"+
        "\""+A_TAGS+"\" TEXT,"+
        "\""+A_CREATOR+"\" TEXT,"+
        "UNIQUE ("+A_ID+","+A_IID+")"+
        ");";

And this was mine query:

public static final String FILL_GRID = "SELECT * "+
        "FROM "+ Db.T_ITEM + " i " +
        "LEFT OUTER JOIN "+Db.T_ATTR+" a ON ( i."+Db.I_ID+"=a."+Db.A_IID+ ")"+ 
        " WHERE i."+Db.I_QID+" = ? AND i."+Db.I_STATUS+" <> "+Db.STATUS_DELETED +
                    " GROUP BY ? ";

and here how i use it:

Cursor c = db.rawQuery(FILL_GRID, new String[]{String.valueOf(qid), order});

Where i am sure that qid is an integer, and order i.titolo ASC or DESC

The only workaround that worked for me is modify the query moving the group by clausole in the rawquery call method:

public static final String FILL_GRID = "SELECT * "+
        "FROM "+ Db.T_ITEM + " i " +
        "LEFT OUTER JOIN "+Db.T_ATTR+" a ON ( i."+Db.I_ID+"=a."+Db.A_IID+ ")"+ 
        " WHERE i."+Db.I_QID+" = ? AND i."+Db.I_STATUS+" <> "+Db.STATUS_DELETED;

Cursor c = db.rawQuery(FILL_GRID + " ORDER BY " + order, new String[]{String.valueOf(qid)});

Upvotes: 1

ghempton
ghempton

Reputation: 7947

I solved it, was a simple oversight. The random() function returns a value which can be larger than a java int data type. This was producing overflow. Switched to getLong() and everything works fine. The cursor was iterating correctly all along.

Upvotes: 6

Related Questions