Setcontent with checkbox field return empty

[Virprince]

Details

Question	Answer
Relevant Bolt Version	4.1.22
Install type	Composer install
PHP version	7.4
Web server	Built-in

Reproduction

Bug summary

Trying to use in twig {% setcontent characters = 'personnages' where {'character_validate' : true } %} return empty.
I tried true, 'true', 1, '1', it always return empty.

printquery

SELECT content FROM Bolt\Entity\Content content 
LEFT JOIN content.fields fields_character_validate 
LEFT JOIN fields_character_validate.translations translations_character_validate 
WHERE content.contentType = :ct0 
AND ((JSON_EXTRACT(translations_character_validate.value, '$[0]') = :character_validate_1 AND fields_character_validate.parent IS NULL AND fields_character_validate.name = :field_character_validate) AND content.status = :status_1) 
ORDER BY content.publishedAt DESC
ct0: personnages
character_validate_1: 1
status_1: published
field_character_validate: character_validate

maybe related to #2547

Steps to reproduce

Contents mostly created with form submission using bolt/form, few contents are "manually" created.
Part of contenttype.yaml:

personnages:
  name: Personnages
  singular_name: Personnage
  fields:
    character_validate:
          type: checkbox
          default: '0' # Only way to have unchecked value at creation
          label: Le personnage est validé
    #[ ... many fields ] 
  default_status: published
  record_template: characters_sheet.twig
  listing_template: listing_characters.twig
  title_format: '{discord_id} - {character_name}'
  listing_records: 24
  listing_sort : -dateCreated
  records_per_page: 20

in twig, {% setcontent characters = 'personnages' where {'character_validate' : true } %}

Expected result

All content with character_validate : true in my characters var so i can use pager.

Actual result

dump of characters

Pagerfanta\Pagerfanta {#2994 ▼
  -adapter: Pagerfanta\Adapter\DoctrineORMAdapter {#2997 ▶}
  -allowOutOfRangePages: false
  -normalizeOutOfRangePages: false
  -maxPerPage: 20
  -currentPage: 1
  -nbResults: 0
  -maxNbPages: null
  -currentPageResults: null
}

dump of {% setcontent characters = 'personnages' %}
(about half are character_validate : true)

Pagerfanta\Pagerfanta {#4167 ▼
  -adapter: Pagerfanta\Adapter\DoctrineORMAdapter {#3361 ▶}
  -allowOutOfRangePages: false
  -normalizeOutOfRangePages: false
  -maxPerPage: 20
  -currentPage: 1
  -nbResults: 57
  -maxNbPages: null
  -currentPageResults: null
}

[bobdenotter]

Well, that's quite the rabbit hole you sent me down on. I've traced the origins of this but to here, where it was introduced:

#1541

As coincidence would have it, it is also the cause of another bug reported yesterday on Slack:

[I-Valchev]

Phew! Nice that you found and fixed it, folks!