SelectArgs not replacing ? with the actual values in CTE in SQLITE Android

I am using CTE where ? needs to be replaced by the actual values in the query:

val itemCursor: Cursor
                val rdb = openHelper.writableDatabase
                itemCursor = rdb.rawQuery(
                    menuItemsQuery(selection, sortOrder, projection),
                    selectionArgs
                )
                itemCursor.setNotificationUri(context.contentResolver, uri)
                return itemCursor

where menuItemsQuery has my SQL query which contains CTE (Common Table Expression)

Complete QUERY:

WITH cteI AS ( SELECT * , item_group.name AS item_group_name
                 FROM item LEFT JOIN  item AS item2 ON item.item_group_uuid = item2.item_group_uuid AND item.uuid < item2.uuid LEFT JOIN ( SELECT * from item_group JOIN (SELECT
                 item_group_uuid, COUNT(*) AS item_group_items_count FROM item GROUP BY item_group_uuid ) AS group_count ON group_count.item_group_uuid=item_group.uuid ) AS item_group
                 ON item_group.uuid=item.item_group_uuid WHERE item2.uuid IS NULL AND item.hidden IS NOT 1 AND item.price_type != ? ), cteM AS ( SELECT ( SELECT
                 COUNT(*) as count FROM cteI AS c INNER JOIN  menu_item AS m ON c.uuid= m.cos_uuid AND m.menu_id = '9d7cded6-8620-4de4-9112-ae9184e064c1'  ) AS
                 count, m.price, c.*, m.cos_uuid FROM cteI AS c INNER JOIN menu_item AS m ON c.uuid = m.cos_uuid AND m.menu_id = '9d7cded6-8620-4de4-9112-ae9184e064c1'  ) SELECT CASE
                 WHEN cteM.price IS NOT NULL THEN cteM.price ELSE cteI.price END AS price, cteM.* FROM cteI INNER JOIN cteM ON cteI.uuid = cteM.cos_uuid

wanted to bind item.price_type != ? data here which has value in selectionArgs, but it is not binding the data here in ?

I tried replacing ? with @ but no luck

Solution

selection contains ?

It could well be dependant upon WHAT ELSE does selection contain.

Another dependency would, if the ? is being interpreted as a placeholder for a bind, be the "no luck". It does not distinguish between not the expected results and an exception etc.

If placed correctly, the ? placeholder can be used in a CTE. The following demonstrates the successful use: within a CTE-

Demonstration

First consider the following (which caters for ALL variable factors to be determined (your question leaves the majority unresolved)):-

const val DATABASE_NAME = "the_database.db"
const val DATABASE_VERSION = 1
const val TABLE_A = "a"
const val COLUMN_A_ID = "${TABLE_A}_${BaseColumns._ID}"
const val COLUMN_A_C1 = "${TABLE_A}_c1"
const val COLUMN_A_C2 = "${TABLE_A}_c2"
const val TABLE_B = "b"
const val COLUMN_B_ID = "${TABLE_B}_${BaseColumns._ID}"
const val COLUMN_B_C1 = "${TABLE_B}_c1"
const val COLUMN_B_C2 = "${TABLE_B}_c2"
const val COLUMN_B_MAPTO_A_ID = "${TABLE_B}_mapto_$COLUMN_A_ID"

class OpenHelper: SQLiteOpenHelper {
    override fun onCreate(db: SQLiteDatabase?) {
        db!!.execSQL(
            "CREATE TABLE IF NOT EXISTS $TABLE_A (" +
                "${COLUMN_A_ID} INTEGER PRIMARY KEY" +
                ",${COLUMN_A_C1} TEXT " +
                ",${COLUMN_A_C2} TEXT " +
                ");"
        )
        db!!.execSQL(
            "CREATE TABLE IF NOT EXISTS ${TABLE_B} (" +
                    "${COLUMN_B_ID} INTEGER PRIMARY KEY" +
                    ",${COLUMN_B_C1} TEXT" +
                    ",${COLUMN_B_C2} TEXT" +
                    ",${COLUMN_B_MAPTO_A_ID} INTEGER" +
                    ");"
        )
    }

    override fun onUpgrade(db: SQLiteDatabase?, fromVersion: Int, toVersion: Int) {
    }

    constructor(context: Context) : super(context, DATABASE_NAME, null, DATABASE_VERSION) {
    }

    fun getItemCursor(stringToUseInWhereClause: String): Cursor {
        val selection = "?"
        val query = "WITH cte1 AS (SELECT " +
                "* FROM ${TABLE_A} " +
                "JOIN ${TABLE_B} ON ${TABLE_B}.${COLUMN_B_MAPTO_A_ID} = ${TABLE_A}.${COLUMN_A_ID} " +
                "WHERE ${COLUMN_B_C1} LIKE " + selection +
                "||'%'" +
                ")" +
                "SELECT * FROM cte1;"
        Log.d("DBINFO","GIC Query passed to SQLite is:-\n\t$query")
        return this.writableDatabase.rawQuery(query, arrayOf(stringToUseInWhereClause))
    }

    fun getItemCursorOther(stringToUseInWhereClause: String): Cursor {
        val query = "WITH cte1 AS (SELECT " +
                "* FROM ${TABLE_A} " +
                "JOIN ${TABLE_B} ON ${TABLE_B}.${COLUMN_B_MAPTO_A_ID} = ${TABLE_A}.${COLUMN_A_ID} " +
                "WHERE ${COLUMN_B_C1} LIKE ?||'%'" +
                ")" +
                "SELECT * FROM cte1;"
        Log.d("DBINFO","GICO Query passed to SQLite is:-\n\t$query")
        return this.writableDatabase.rawQuery(query, arrayOf(stringToUseInWhereClause))
    }
    fun insertA(id:Long?,c1: String, c2: String): Long {
        val cv = ContentValues()
        if (id != null) {
            cv.put(COLUMN_A_ID,id)
        }
        cv.put(COLUMN_A_C1,c1)
        cv.put(COLUMN_A_C2,c2)
        return this.writableDatabase.insert(TABLE_A,null,cv)
    }
    fun insertB(id: Long?, c1: String, c2: String, mapToAId: Long): Long {
        val cv = ContentValues()
        if (id != null) {
            cv.put(COLUMN_B_ID,id)
        }
        cv.put(COLUMN_B_C1,c1)
        cv.put(COLUMN_B_C2,c2)
        cv.put(COLUMN_B_MAPTO_A_ID,mapToAId)
        return this.writableDatabase.insert(TABLE_B,null,cv)
    }
}

the getItemCursor and the equivalent getItemCursorOther functions having the 1 subtle difference; the first uses the variable selection which contains the placeholder (?), whilst the second hard codes the placeholder.
- Both are within the WHERE clause of the CTE.
the actual term/value to be bound (replace the placeholder) is passed to the functions via stringToUseInWhereClause.
The rest of the code, simply allows the database to actually be used and thus demonstrate.

Obviously the code itself does not prove anything. So to actually demonstrate that the placeholder is replaced some activity code to:-

add some data
extract some data via a CTE
1. for potentially debugging the full query passed to SQLite is also output
show what data has been extracted

The activity code:-

class MainActivity : AppCompatActivity() {
    lateinit var openHelper: OpenHelper
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)
        openHelper = OpenHelper(this)
        val aid = openHelper.insertA(null,"c1blah","c2blah")
        openHelper.insertB(100,"something","something",aid)
        openHelper.insertB(null,"some thing but not really","something but not really",aid)
        openHelper.insertB(null,"somethingelse","somethingelse",aid)
        openHelper.insertB(null,"somethingelse","somethingelse",999)
        val c1 = openHelper.getItemCursor("something")
        DatabaseUtils.dumpCursor(c1)
        val c2 = openHelper.getItemCursorOther("something")
        DatabaseUtils.dumpCursor(c2)
        val c3 = openHelper.getItemCursor("")
        DatabaseUtils.dumpCursor(c3)
        val c4 = openHelper.getItemCursorOther("")
        DatabaseUtils.dumpCursor(c4)
        c1.close()
        c2.close()
        c3.close()
        c4.close()
    }
}

c1 and c2 will only have rows where column c1 in table b start with something, whilst c3 and c4 will have all rows as per the join as the condition is effectively "%" (anything).

The resultant output to the log being:-

2024-01-17 09:45:29.080 D/DBINFO: GIC Query passed to SQLite is:-
        WITH cte1 AS (SELECT * FROM a JOIN b ON b.b_mapto_a__id = a.a__id WHERE b_c1 LIKE ?||'%')SELECT * FROM cte1;
2024-01-17 09:45:29.080 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@8962cfb
2024-01-17 09:45:29.081 I/System.out: 0 {
2024-01-17 09:45:29.081 I/System.out:    a__id=1
2024-01-17 09:45:29.082 I/System.out:    a_c1=c1blah
2024-01-17 09:45:29.082 I/System.out:    a_c2=c2blah
2024-01-17 09:45:29.082 I/System.out:    b__id=100
2024-01-17 09:45:29.082 I/System.out:    b_c1=something
2024-01-17 09:45:29.082 I/System.out:    b_c2=something
2024-01-17 09:45:29.082 I/System.out:    b_mapto_a__id=1
2024-01-17 09:45:29.082 I/System.out: }
2024-01-17 09:45:29.082 I/System.out: 1 {
2024-01-17 09:45:29.082 I/System.out:    a__id=1
2024-01-17 09:45:29.082 I/System.out:    a_c1=c1blah
2024-01-17 09:45:29.082 I/System.out:    a_c2=c2blah
2024-01-17 09:45:29.082 I/System.out:    b__id=102
2024-01-17 09:45:29.082 I/System.out:    b_c1=somethingelse
2024-01-17 09:45:29.082 I/System.out:    b_c2=somethingelse
2024-01-17 09:45:29.082 I/System.out:    b_mapto_a__id=1
2024-01-17 09:45:29.082 I/System.out: }
2024-01-17 09:45:29.082 I/System.out: <<<<<


2024-01-17 09:45:29.082 D/DBINFO: GICO Query passed to SQLite is:-
        WITH cte1 AS (SELECT * FROM a JOIN b ON b.b_mapto_a__id = a.a__id WHERE b_c1 LIKE ?||'%')SELECT * FROM cte1;
2024-01-17 09:45:29.083 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@ec7d118
2024-01-17 09:45:29.084 I/System.out: 0 {
2024-01-17 09:45:29.084 I/System.out:    a__id=1
2024-01-17 09:45:29.084 I/System.out:    a_c1=c1blah
2024-01-17 09:45:29.084 I/System.out:    a_c2=c2blah
2024-01-17 09:45:29.084 I/System.out:    b__id=100
2024-01-17 09:45:29.084 I/System.out:    b_c1=something
2024-01-17 09:45:29.084 I/System.out:    b_c2=something
2024-01-17 09:45:29.084 I/System.out:    b_mapto_a__id=1
2024-01-17 09:45:29.084 I/System.out: }
2024-01-17 09:45:29.085 I/System.out: 1 {
2024-01-17 09:45:29.085 I/System.out:    a__id=1
2024-01-17 09:45:29.085 I/System.out:    a_c1=c1blah
2024-01-17 09:45:29.085 I/System.out:    a_c2=c2blah
2024-01-17 09:45:29.086 I/System.out:    b__id=102
2024-01-17 09:45:29.086 I/System.out:    b_c1=somethingelse
2024-01-17 09:45:29.086 I/System.out:    b_c2=somethingelse
2024-01-17 09:45:29.086 I/System.out:    b_mapto_a__id=1
2024-01-17 09:45:29.086 I/System.out: }
2024-01-17 09:45:29.086 I/System.out: <<<<<


2024-01-17 09:45:29.086 D/DBINFO: GIC Query passed to SQLite is:-
        WITH cte1 AS (SELECT * FROM a JOIN b ON b.b_mapto_a__id = a.a__id WHERE b_c1 LIKE ?||'%')SELECT * FROM cte1;
2024-01-17 09:45:29.086 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@d598771
2024-01-17 09:45:29.087 I/System.out: 0 {
2024-01-17 09:45:29.088 I/System.out:    a__id=1
2024-01-17 09:45:29.088 I/System.out:    a_c1=c1blah
2024-01-17 09:45:29.088 I/System.out:    a_c2=c2blah
2024-01-17 09:45:29.088 I/System.out:    b__id=100
2024-01-17 09:45:29.088 I/System.out:    b_c1=something
2024-01-17 09:45:29.088 I/System.out:    b_c2=something
2024-01-17 09:45:29.088 I/System.out:    b_mapto_a__id=1
2024-01-17 09:45:29.088 I/System.out: }
2024-01-17 09:45:29.088 I/System.out: 1 {
2024-01-17 09:45:29.088 I/System.out:    a__id=1
2024-01-17 09:45:29.088 I/System.out:    a_c1=c1blah
2024-01-17 09:45:29.089 I/System.out:    a_c2=c2blah
2024-01-17 09:45:29.089 I/System.out:    b__id=101
2024-01-17 09:45:29.089 I/System.out:    b_c1=some thing but not really
2024-01-17 09:45:29.091 I/System.out:    b_c2=something but not really
2024-01-17 09:45:29.091 I/System.out:    b_mapto_a__id=1
2024-01-17 09:45:29.091 I/System.out: }
2024-01-17 09:45:29.091 I/System.out: 2 {
2024-01-17 09:45:29.092 I/System.out:    a__id=1
2024-01-17 09:45:29.092 I/System.out:    a_c1=c1blah
2024-01-17 09:45:29.092 I/System.out:    a_c2=c2blah
2024-01-17 09:45:29.092 I/System.out:    b__id=102
2024-01-17 09:45:29.092 I/System.out:    b_c1=somethingelse
2024-01-17 09:45:29.093 I/System.out:    b_c2=somethingelse
2024-01-17 09:45:29.093 I/System.out:    b_mapto_a__id=1
2024-01-17 09:45:29.093 I/System.out: }
2024-01-17 09:45:29.093 I/System.out: <<<<<


2024-01-17 09:45:29.093 D/DBINFO: GICO Query passed to SQLite is:-
        WITH cte1 AS (SELECT * FROM a JOIN b ON b.b_mapto_a__id = a.a__id WHERE b_c1 LIKE ?||'%')SELECT * FROM cte1;
2024-01-17 09:45:29.094 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@8c94956
2024-01-17 09:45:29.095 I/System.out: 0 {
2024-01-17 09:45:29.095 I/System.out:    a__id=1
2024-01-17 09:45:29.095 I/System.out:    a_c1=c1blah
2024-01-17 09:45:29.095 I/System.out:    a_c2=c2blah
2024-01-17 09:45:29.095 I/System.out:    b__id=100
2024-01-17 09:45:29.096 I/System.out:    b_c1=something
2024-01-17 09:45:29.096 I/System.out:    b_c2=something
2024-01-17 09:45:29.096 I/System.out:    b_mapto_a__id=1
2024-01-17 09:45:29.096 I/System.out: }
2024-01-17 09:45:29.096 I/System.out: 1 {
2024-01-17 09:45:29.096 I/System.out:    a__id=1
2024-01-17 09:45:29.096 I/System.out:    a_c1=c1blah
2024-01-17 09:45:29.096 I/System.out:    a_c2=c2blah
2024-01-17 09:45:29.096 I/System.out:    b__id=101
2024-01-17 09:45:29.096 I/System.out:    b_c1=some thing but not really
2024-01-17 09:45:29.096 I/System.out:    b_c2=something but not really
2024-01-17 09:45:29.096 I/System.out:    b_mapto_a__id=1
2024-01-17 09:45:29.096 I/System.out: }
2024-01-17 09:45:29.096 I/System.out: 2 {
2024-01-17 09:45:29.097 I/System.out:    a__id=1
2024-01-17 09:45:29.097 I/System.out:    a_c1=c1blah
2024-01-17 09:45:29.097 I/System.out:    a_c2=c2blah
2024-01-17 09:45:29.097 I/System.out:    b__id=102
2024-01-17 09:45:29.097 I/System.out:    b_c1=somethingelse
2024-01-17 09:45:29.097 I/System.out:    b_c2=somethingelse
2024-01-17 09:45:29.097 I/System.out:    b_mapto_a__id=1
2024-01-17 09:45:29.097 I/System.out: }
2024-01-17 09:45:29.097 I/System.out: <<<<<

i.e. as anticipated the first two only extract 2 rows where b_c1 starts with something.
- where b_c1=some thing but not really the space between some and thing means that it does not match something and hence those rows are not selected.
- the b1_c1 columns with somethingelse will not selected as the b1_mapt0_a__id have a value that does not map/reference a row in the a table and hence will not be JOINed.
the last two both extract all 3 rows that match the JOIN.

Therefore the ? placeholder must be being replaced with the value from the selectionArgs parameter and therefore the issue you have is not due to ? not being able to be replaced with a value from the selectionArgs, but is elsewhere (which due to the vagueness of the question is virtually impossible to determine). However, the demonstration, does include some debugging code that may be useful to incorporate when trying to ascertain the actual issue.

Additional re Comment

Tried but still no luck in this. Have edited the question and added the complete query. The data is not getting bind in ?.

What exactly does "No Luck" mean? Assuming that it is not an exception and that the "No Luck" actually means no data as expected, or something that is not an exception AND that to fully test and or determine such an issue it would be wise to not assume an issue (aka CTE's don't bind placeholders) but to determine the actual issue.

In regard to placeholder binding the answer above shows that this is probably not an issue (as would no exception being raised which would be the case say that somehow the ? placeholder were ignored as it would then be an issue because the ? was not enclosed and would likely the raise a column not found error (exception)).

So a tack could be to narrow the issue down to between being the query and the subsequent determination of "No Luck" (if not already done).

Adding a dump of the Cursor (very easy to do and it retains the state of the Cursor), running and viewing the log will show whether or not the SQL extracted any data. If so then the issue is probably 1) subsequent processing of the data in the Cursor; otherwise 2) the issue is with the SQL.

There is no way of determining such an issue from what is included in the question. You would need to follow the path of debugging that subsequent code.
One approach could to to progressively trim the SQL commenting out or other wise nullifying the comparisons in the WHERE clauses (noting that commenting out the placeholder(s) would need care as just commenting out e.g. /*?*/ would require the values passed to be omitted from the String Array passed to the rawQuery (or other code changes)). When data is extracted then work from then on to understand why there is no data extracted.
- again dumping the cursor requires no changes/expectation of columns whatever is output to the Cursor will be dumped to the Log.
  - Quite frequently the data is extracted but then the subsequent handling is at fault.
1. it may require extending this to doing with the other comparisons, the ON clause(s). This however is more complex to achieve (see below re using an SQLite Tool where you could have multiple SQL snippets).
An alternative approach could be to use an SQLite tool (such as DBeaver, Navicat for SQLite, DB Browser, SQLite Studio ....). You could export the database via Android Studio using Device Explorer connect/open it in such a tool and copy and paste the SQL and test it with real data and perhaps determine the issue.
Another alternative would be to edit the question and make it MRE compliant - see https://stackoverflow.com/help/minimal-reproducible-example (some SQLite Tools allow you export and entire data as SQL e.g. DB Browser (allowing the data to be reproduced in the question)).

Please check the complete query now. TIA

That would entail a great deal of work and many assumptions. e.g. if the issue is that no data is extracted the result, if the great deal of work were undertaken, then the data would probably be tailored to work and thus result telling you little other than your selection criteria is likely incorrect. Hence an example of the importance or an MRE.