Boolean document property filtering in LiveTable/Live Data only works on some database engines

Steps to reproduce:

Create a page with the following content:

{{velocity}}
#set($collist = ['doc.name', 'doc.space', 'doc.date', 'doc.author', 'doc.hidden'])

#set($colprops = {
  'doc.name' : { 'type' : 'text' , 'size' : 30, 'link' : 'view' },
  'doc.space' : { 'type' : 'text', 'link' : 'space' },
  'doc.date' : { 'type' : 'date' },
  'doc.author' : { 'type' : 'text', 'link' : 'author' }
  })

#set($options = {
  'translationPrefix' : 'xe.index.',
  'className' : 'XWiki.XWikiUsers',
  'rowCount' : 15,
  'description' : 'This table lists all users on this wiki. The columns can be sorted and some can be filtered.' 
})

#livetable('userdocs' $collist $colprops $options)
{{/velocity}}

{{liveData id="usersLiveData" limit="20" properties="doc.name,doc.space,doc.date,doc.author,doc.hidden" source="liveTable" sourceParameters="translationPrefix=platform.index.&className=XWiki.XWikiUsers"}}{{/liveData}}
 

Filter the "hidden" column to only show non-hidden pages (select "No" / "False" in the last column).

Expected result:

All users are still displayed in both cases.

Actual result:

The result depends on the database engine: With HSQLDB and PostgreSQL, it works as expected in Live Data while with MariaDB, MySQL and Oracle, it works as expected in LiveTable. In the other case, no results are displayed.

The reason for this is that the LiveTable filtering logic uses a condition

upper(str(doc.hidden)) like upper(?)

with the parameter value being the value specified in the UI with % on both sides. Live Data uses true/false as value while LiveTable uses 1/0.

The problem is that different databases handle boolean values differently:

• HSQLDB/PostgreSQL: Store booleans as native boolean types (true/false)
• MySQL/MariaDB: Store booleans as TINYINT (1 for true, 0 for false)
• Oracle: Stores booleans as NUMBER(1) (1 for true, 0 for false)

When using str() on a boolean column and then doing a LIKE comparison with %true%, this works in HSQLDB/PostgreSQL because str(true) returns "true", but in MySQL/MariaDB/Oracle, str(1) returns "1", not "true".