Androd Room Embedded Relation ignores where condition

I am an SQL rookie and don’t see why my statement behaves the way it does. It is used in an Android Room DAO and returns unwanted results.

I have this sample data in two tables:

I have this statement:

@Transaction
    @Query("Select Distinct Category.* " +
            "from Category " +
            "inner join Items on Category.ID = Items.Category " +
            "where IsExcluded = 0 " + 
            "order by lower( Category.Name ) asc")
    LiveData<List<CatViewWithItemList>> getCatViewWithItemListGlobal();

the results are received by a simple POJO:

public class CatViewWithItemList  {

    @Embedded
    public Cat myCat;

    @Relation(parentColumn = "ID",
            entityColumn = "Category") public List<ItemS> ItemList;

Problem: I dont understand the results:

Why is Item 5 returned? It should be excluded by the where clause. Is there an error in my statement?

MikeT built a sample app that contains the same bug:

(Edit: I dropped the bool converter by now, Room seems to parse boolean without it) Or could this be caused by my dead simple type converter for booleans? The ones and zeros are actually placeholders for booleans:

@TypeConverter
    public Boolean fromInt(int value) {
        return value == 0 ? false : true;
    }

    @TypeConverter
    public int toInt(Boolean bValue) {
        if (bValue == false) {
            return 0;
        } else {
            return 1;
        }
    }

Thank you for reading!

EDIT: Many thanks to @Angela. Her test suggests that the sql statement is fine and that behaviour is caused by the peculiarities of sqlite, or by the embedded relation in the receiver pojo. Does anyone have advice on this?

EDIT: MikeT explains why what I want to do is seemingly not possible with room relations – at least as long no one prooves different. In his answer, he provides an alternative.

Answer

I believe that it’s either due to your dead simple converter or perhaps how the Items Entity is defined (I’ve used boolean for isexcluded and it appears to work fine without the converter).

That is using my version of the Items Entity :-

@Entity(foreignKeys = @ForeignKey(entity = Category.class,parentColumns = "id", childColumns = "category", onDelete =  CASCADE))
public class Items {
    @PrimaryKey(autoGenerate = true)
    private long id;
    private String category;
    private boolean isexcluded;


    public void setCategory(String category) {
        this.category = category;
    }

    public void setId(long id) {
        this.id = id;
    }

    public void setIsexcluded(boolean isexcluded) {
        this.isexcluded = isexcluded;
    }

    public String getCategory() {
        return this.category;
    }

    public long getId() {
        return this.id;
    }

    public boolean isIsexcluded() {
        return this.isexcluded;
    }
}

along with a Category DAO of :-

@Dao
interface CategoryDAO {
    @Query("SELECT * FROM Category")
    List<Category> getAllcategories();

    @Query("SELECT DISTINCT Category.* " +
            "FROM CATEGORY " +
            "INNER JOIN ITEMS ON category.id = Items.category " +
            "WHERE isexcluded = 0 " +
            "ORDER BY lower(category.name)")
    List<Category> getSpeacial();

    @Query("SELECT DISTINCT Category.* " +
            "FROM CATEGORY " +
            "INNER JOIN ITEMS ON category.id = Items.category " +
            "WHERE isexcluded = 0 " +
            "ORDER BY lower(category.name)")
    List<CatViewWithItemList> getSpeacial2();

    @Insert
    public long[] insertCategory(Category... Category);
}

along with

new Thread(new Runnable() {
            @Override
            public void run() {
                //mRoomDB.categoryDao().insertCategory(initialCategories());
                //mRoomDB.itemsDAO().insertItem(initialItems());
                List<Category> categoryList = mRoomDB.categoryDao().getAllcategories();
                List<Items> itemsList = mRoomDB.itemsDAO().getAllItems();
                for (Category c: categoryList) {
                    Log.d("CATEGORY","Category is " + c.getName() + " refrence is " + c.getId());
                }
                for (Items i: itemsList) {
                    Log.d("ITEM","Item is " + i.getId() + " Category reference is " + i.getCategory() + " Is Excluded is " + Boolean.toString(i.isIsexcluded()));
                }
                List<Category> categoryList2 = mRoomDB.categoryDao().getSpeacial();
                for (Category c: categoryList2) {
                    Log.d("CATEGORY2","Category is " + c.getName() + " reference is " + c.getId());
                }
                List<CatViewWithItemList> catViewWithItemLists = mRoomDB.categoryDao().getSpeacial2();
                for (CatViewWithItemList cvwil: catViewWithItemLists) {
                    Log.d("CATVIEWITEM","Category = " + cvwil.myCat.getId() + " ID = " + cvwil.ItemList.get(0).getId() + " IsExcluded = " + Boolean.toString(cvwil.ItemList.get(0).isIsexcluded()));
                }
            }
        }).start();

and with :-

public class CatViewWithItemList {

    @Embedded
    public Category myCat;

    @Relation(parentColumn = "id",
            entityColumn = "category")
    public List<Items> ItemList;
}

Then the result is (part one the underlying data (oh so I can’t spell third :))):-

2018-12-19 21:47:05.376 2109-2125/? D/CATEGORY: Category is firstname refrence is a
2018-12-19 21:47:05.377 2109-2125/? D/CATEGORY: Category is secondname refrence is b
2018-12-19 21:47:05.377 2109-2125/? D/CATEGORY: Category is thridname refrence is c
2018-12-19 21:47:05.377 2109-2125/? D/ITEM: Item is 1 Category reference is a Is Excluded is false
2018-12-19 21:47:05.377 2109-2125/? D/ITEM: Item is 2 Category reference is c Is Excluded is false
2018-12-19 21:47:05.377 2109-2125/? D/ITEM: Item is 3 Category reference is null Is Excluded is false
2018-12-19 21:47:05.377 2109-2125/? D/ITEM: Item is 4 Category reference is b Is Excluded is false
2018-12-19 21:47:05.377 2109-2125/? D/ITEM: Item is 5 Category reference is b Is Excluded is true
2018-12-19 21:47:05.378 2109-2125/? D/ITEM: Item is 6 Category reference is null Is Excluded is true

and then the two sets of results :-

2018-12-19 21:47:05.379 2109-2125/? D/CATEGORY2: Category is firstname reference is a
2018-12-19 21:47:05.380 2109-2125/? D/CATEGORY2: Category is secondname reference is b
2018-12-19 21:47:05.380 2109-2125/? D/CATEGORY2: Category is thridname reference is c


2018-12-19 21:47:05.382 2109-2125/? D/CATVIEWITEM: Category = a ID = 1 IsExcluded = false
2018-12-19 21:47:05.382 2109-2125/? D/CATVIEWITEM: Category = b ID = 4 IsExcluded = false
2018-12-19 21:47:05.382 2109-2125/? D/CATVIEWITEM: Category = c ID = 2 IsExcluded = false

Additional

I believe that your issue is not with the query but with the CatViewItemList in that you are filling the list of Items with all Items related to the category irrespective/after of the query (aka I think, embedding the list of items into the category). This did confuse me as you only really want the specific related Item (I just grabbed 1st in my code ) e.g. cvwil.ItemList.get(0).isIsexcluded() and luck was that 4 comes before 5 so the isexcluded appears as false.

I believe that you should perhaps approach this from the Items, joining the Category e.g. something like

SELECT * 
FROM Items 
JOIN Category ON Items.category = category.id
WHERE Items.isexcluded = 0 
ORDER BY category.name ASC;

I’be updated the code on gitHub to do this (note that I’ve changed the Category table to have a unique column name of categoryid rather than id.)

results are now (note added another Category and 2 more items reversed order i.e. first Item (id 7) has isexcluded as true, then id 8 has false) :-

2018-12-20 21:39:56.971 9136-9160/so53839431.so53839431roomrelationship D/CATEGORY: Category is firstname Category ID is a
2018-12-20 21:39:56.971 9136-9160/so53839431.so53839431roomrelationship D/CATEGORY: Category is secondname Category ID is b
2018-12-20 21:39:56.971 9136-9160/so53839431.so53839431roomrelationship D/CATEGORY: Category is thirdname Category ID is c
2018-12-20 21:39:56.971 9136-9160/so53839431.so53839431roomrelationship D/CATEGORY: Category is fourthname Category ID is d
2018-12-20 21:39:56.971 9136-9160/so53839431.so53839431roomrelationship D/ITEM: Item is 1 Category reference is a Is Excluded is false
2018-12-20 21:39:56.971 9136-9160/so53839431.so53839431roomrelationship D/ITEM: Item is 2 Category reference is c Is Excluded is false
2018-12-20 21:39:56.971 9136-9160/so53839431.so53839431roomrelationship D/ITEM: Item is 3 Category reference is null Is Excluded is false
2018-12-20 21:39:56.971 9136-9160/so53839431.so53839431roomrelationship D/ITEM: Item is 4 Category reference is b Is Excluded is false
2018-12-20 21:39:56.971 9136-9160/so53839431.so53839431roomrelationship D/ITEM: Item is 5 Category reference is b Is Excluded is true
2018-12-20 21:39:56.971 9136-9160/so53839431.so53839431roomrelationship D/ITEM: Item is 6 Category reference is null Is Excluded is true
2018-12-20 21:39:56.971 9136-9160/so53839431.so53839431roomrelationship D/ITEM: Item is 7 Category reference is d Is Excluded is true
2018-12-20 21:39:56.971 9136-9160/so53839431.so53839431roomrelationship D/ITEM: Item is 8 Category reference is d Is Excluded is false
2018-12-20 21:39:56.972 9136-9160/so53839431.so53839431roomrelationship D/ITEMWITHCAT: Item id =1 Category reference is a Is Excluded is false Referenced Category is a Referenced Category name is firstname
2018-12-20 21:39:56.972 9136-9160/so53839431.so53839431roomrelationship D/ITEMWITHCAT: Item id =2 Category reference is c Is Excluded is false Referenced Category is c Referenced Category name is thirdname
2018-12-20 21:39:56.972 9136-9160/so53839431.so53839431roomrelationship D/ITEMWITHCAT: Item id =4 Category reference is b Is Excluded is false Referenced Category is b Referenced Category name is secondname
2018-12-20 21:39:56.972 9136-9160/so53839431.so53839431roomrelationship D/ITEMWITHCAT: Item id =8 Category reference is d Is Excluded is false Referenced Category is d Referenced Category name is fourthname