Why the sql statement is wrong and the room does not report an error

Question

Dao

@Query("SELECT * FROM t_user ORDER BY :order DESC")
    fun pagingSource(order: String): PagingSource<Int, User>

I now know that sql statements cannot take columnName as a parameter. But why Room didn't report an error, still succeeded in querying the results.It's just that they are not arranged in reverse order according to id, but in order.

The log print is also the correct sql statement.

// AppDatabase
Room.databaseBuilder(...).setQueryCallback({ sqlQuery, bindArgs ->
                Log.d("Android Room", "SQL Query: $sqlQuery SQL Args: $bindArgs")
            }, 
...

// logcat
SQL Query: SELECT * FROM ( SELECT * FROM t_user ORDER BY ? DESC ) LIMIT 60 OFFSET 0 SQL Args: [id]

Can you tell me how Room handles this sql statement?

Answer 1

I now know that sql statements cannot take columnName as a parameter

Correct, but in your case the parameter is not interpreted as the name of a column, but as a literal string, like 'id' and since literal strings and expressions are valid and accepted in the ORDER BY clause, your query is parsed and interpreted as:

select * from t_user order by 'id';

which is not syntactically wrong, but of course does not perform the desired ordering.

See the demo.