Can't find a column that exists

I've created a table with a column called "IsMine" but when i try to return, my query won't work:

 01-08 15:00:06.148: W/System.err(12453): android.database.sqlite.SQLiteException: no
such column: m.IsMine (code 1): , while compiling: SELECT r.RoomId, m.MessageContentId, 
m.Message, m.MsgDate, m.Sender, m.Receiver, m.IsMine FROM Room r LEFT JOIN ( SELECT 
 m1.MessageContentId, m1.RoomId, m1.Message, m1.MsgDate, m1.Sender, m1.Receiver FROM 
MessageContent m1 JOIN ( SELECT r1.RoomId, max(m2.MsgDate) maxMsgDate from Room r1 LEFT JOIN 
MessageContent m2 on r1.RoomId = m2.RoomId GROUP BY r1.RoomId) mf ON m1.RoomId =  mf.RoomId 
 AND m1.MsgDate = mf.maxMsgDate) m ON r.RoomId = m.RoomId

This is my onCreate() function and the function that i use to return my objects:

@Override
public void onCreate(SQLiteDatabase database) {
    String queryMessageContent = "CREATE TABLE MessageContent ( MessageContentId INTEGER PRIMARY KEY AUTOINCREMENT, RoomId INTEGER, Message TEXT, MsgDate DEFAULT CURRENT_TIMESTAMP, Sender TEXT, Receiver TEXT, IsMine INTEGER, FOREIGN KEY(RoomId) REFERENCES Room(RoomId))";
    database.execSQL(queryMessageContent);
}

public List<Room> getLastMessages() throws ParseException{
    List<Room> rooms = new ArrayList<Room>();
    StringBuilder query = new StringBuilder("SELECT r.RoomId, m.MessageContentId, m.Message, ");
    query.append("m.MsgDate, m.Sender, m.Receiver, m.IsMine ");
    query.append("FROM Room r ");
    query.append("LEFT JOIN ( ");
    query.append("SELECT m1.MessageContentId, m1.RoomId, m1.Message, m1.MsgDate, m1.Sender, m1.Receiver ");
    query.append("FROM MessageContent m1 ");
    query.append("JOIN ( ");
    query.append("SELECT r1.RoomId, max(m2.MsgDate) maxMsgDate from Room r1 ");
    query.append("LEFT JOIN MessageContent m2 on r1.RoomId = m2.RoomId GROUP BY r1.RoomId) mf ");
    query.append("ON m1.RoomId =  mf.RoomId AND m1.MsgDate = mf.maxMsgDate) m ");
    query.append("ON r.RoomId = m.RoomId ");
    SQLiteDatabase database = this.getWritableDatabase();


    Cursor cursor = database.rawQuery(query.toString(), null);
    if(cursor.moveToFirst()){
        do {
            Room room = new Room();
            MessageContent messageContent = new MessageContent();
            room.setId(cursor.getLong(0));
            messageContent.setMessageId(cursor.getLong(1));
            messageContent.setMessage(cursor.getString(2));
            messageContent.setDate(new SimpleDateFormat("dd/MM/yyyy HH:mm:ss").
                    parse(cursor.getString(3)));
            messageContent.setSender(cursor.getString(4));
            messageContent.setReceiver(cursor.getString(5));
            messageContent.setMine(Boolean.valueOf(cursor.getString(6)));
            room.getContent().add(messageContent);
            rooms.add(room);
        }while(cursor.moveToNext());
    }
    return rooms;

How can I avoid this error? Anyway that i can see my table (like a DESC )?

Answers


You are selecting the column IsMine from 'm' which is an in-line table created within your query but that in-line table doesn't select IsMine so it doesn't exist. It's easier to see with your SQL laid out:

SELECT
r.RoomId,
m.MessageContentId, 
m.Message,
m.MsgDate,
m.Sender,
m.Receiver,
m.IsMine    <-- Selecting it here from 'm'
FROM Room r LEFT JOIN
 ( SELECT 
   m1.MessageContentId,
   m1.RoomId,
   m1.Message,
   m1.MsgDate,
   m1.Sender,
   m1.Receiver    <-- But it isn't here in the columns returned for 'm'
   ,m1.IsMine  <-- Is that what you need?
   FROM MessageContent m1 JOIN 
     ( SELECT
       r1.RoomId,
       max(m2.MsgDate) maxMsgDate
       FROM Room r1
       LEFT JOIN MessageContent m2 ON r1.RoomId = m2.RoomId
       GROUP BY r1.RoomId
     ) mf ON m1.RoomId =  mf.RoomId 
         AND m1.MsgDate = mf.maxMsgDate
 ) m ON r.RoomId = m.RoomId

Your query never actually defines an m.IsMine. As "m" is defined in your joins, I believe you need to SELECT it into "m" before you can reference it. This would need to occur either inside your first (m1) or second (r1/m2) JOIN (depending on your table structure/logic).

Edit: Like I said, I'm not sure on your logic, but the query should be revised to effect of something like this:

SELECT r.RoomId, m.MessageContentId, m.Message, m.MsgDate, m.Sender, m.Receiver, m.IsMine
FROM Room r
LEFT JOIN
(
    SELECT m1.MessageContentId, m1.RoomId, m1.Message, m1.MsgDate, m1.Sender, m1.Receiver, m1.IsMine
    FROM MessageContent m1 
    JOIN
    ( 
        SELECT r1.RoomId, max(m2.MsgDate) maxMsgDate 
        FROM Room r1 
        LEFT JOIN MessageContent m2 on r1.RoomId = m2.RoomId GROUP BY r1.RoomId
    ) mf
    ON m1.RoomId =  mf.RoomId AND m1.MsgDate = mf.maxMsgDate
) m
ON r.RoomId = m.RoomId

Wild Guess: onCreate vs onUpgrade

And could it be that you've been working with this same database with a previously different table definition?

This is a very wild guess but I can see you create the table in the onCreate method. Just so you know this method is only called when you try to access that database and it doesn't exist yet. So if you had another definition there before you'll have to remove the app resources (on the android device) or use the onUpgrade method instead to get the new definition active.

General tip: SQL verification

edit: Also when working with SQL see if you can execute the statements in a standalone sqlite database. That way you can verify your SQL without having to run the Android app.

The easiest way to do so is using the command line tools from sqlite. Check the sqlite docs for more information.


Need Your Help

C: Why am i getting Segmentation fault in one case and not in the other?

c segmentation-fault

i am new to C so i believe there is a rookie mistake somewhere in my code due to lack of fundamentals in pointers and memory allocation.