android.database.sqlite.SQLiteException: near "AS": syntax error (code 1):

Question

I'm using room database, and I have a query that is working fine on android version 21 and above, but the problem is the same query is not working on android version 4.4.2 (API 19), and it gives me an error

this my Query:

@Transaction
@Query(
        "WITH " +
                "movieByPackageId AS " +
                "(" +
                " SELECT * FROM MovieDetailEntity " +
                " WHERE " +
                "( " +

                "(:rating == 0 OR MovieDetailEntity.rating >= :rating AND MovieDetailEntity.rating < (:rating +1))" +

                " AND " +

                "(:packageId < 0 OR MovieDetailEntity.packageId == :packageId) " +
                ")), "

                + "filteredCategory AS " +
                "( " +
                " SELECT * FROM MovieCategoryCrossRef WHERE " +
                "( " +
                ":categoryId < 0 OR categoryId=:categoryId " +
                ") GROUP BY movieId) " +

                "SELECT * " +
                "FROM movieByPackageId INNER JOIN filteredCategory ON " +
                "movieByPackageId.movieId=filteredCategory.movieId " +
                "ORDER BY movieByPackageId.serverIndex  ASC"

)
public abstract DataSource.Factory<Integer, MovieEntity> getFilteredMoviesWithoutYearsSortedByNON(int rating, int packageId, int categoryId);

and this is the ERROR:

 android.database.sqlite.SQLiteException: near "AS": syntax error (code 1): , while compiling: SELECT COUNT(*) FROM ( WITH movieByPackageId AS ( SELECT * FROM MovieDetailEntity  WHERE ( (? == 0 OR MovieDetailEntity.rating >= ? AND MovieDetailEntity.rating < (? +1)) AND (? < 0 OR MovieDetailEntity.packageId == ?) )), filteredCategory AS (  SELECT * FROM MovieCategoryCrossRef WHERE ( ? < 0 OR categoryId=? ) GROUP BY movieId) SELECT * FROM movieByPackageId INNER JOIN filteredCategory ON movieByPackageId.movieId=filteredCategory.movieId ORDER BY movieByPackageId.serverIndex  ASC )
    at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
    at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
    at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
    at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
    at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
    at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
    at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
    at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1314)
    at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1289)
    at androidx.sqlite.db.framework.FrameworkSQLiteDatabase.query(FrameworkSQLiteDatabase.java:161)
    at androidx.room.RoomDatabase.query(RoomDatabase.java:328)
    at androidx.room.RoomDatabase.query(RoomDatabase.java:311)
    at androidx.room.paging.LimitOffsetDataSource.countItems(LimitOffsetDataSource.java:87)
    at androidx.room.paging.LimitOffsetDataSource.loadInitial(LimitOffsetDataSource.java:119)
    at androidx.paging.WrapperPositionalDataSource.loadInitial(WrapperPositionalDataSource.java:58)
    at androidx.paging.PositionalDataSource.dispatchLoadInitial(PositionalDataSource.java:286)
    at androidx.paging.TiledPagedList.<init>(TiledPagedList.java:107)
    at androidx.paging.PagedList.create(PagedList.java:229)
    at androidx.paging.PagedList$Builder.build(PagedList.java:388)
    at androidx.paging.LivePagedListBuilder$1.compute(LivePagedListBuilder.java:206)
    at androidx.paging.LivePagedListBuilder$1.compute(LivePagedListBuilder.java:171)
    at androidx.lifecycle.ComputableLiveData$2.run(ComputableLiveData.java:101)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1112)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:587)
    at java.lang.Thread.run(Thread.java:841)

Answer 1

等待大神答复