SQLite doesn't cast 1/0 to true and false

[cllns]

Describe the bug

I'm trying to select_append a boolean from a subquery (here just rendered as TRUE for simplicity) in my app, which is built on SQLite.

For background, SQLite doesn't have a boolean type (nor true nor false), and the convention is to use 1 and 0 instead. I used bool::cast to wrap the subquery, but it's still returning 1 or 0.

To Reproduce

require "rom"

Types = Dry.Types()
module Structs
  class ArticleWithParamsBoolVisible < ROM::Struct
    attribute :title, Types::String
    attribute :visible, Types::Params::Bool
  end

  class ArticleWithRegularBoolVisible < ROM::Struct
    attribute :title, Types::String
    attribute :visible, Types::Bool
  end
end

rom = ROM.container(:sql, "sqlite::memory") do |conf|
  conf.default.create_table(:articles) do
    primary_key :id
    column :title, String, null: false
  end

  conf.relation(:articles) do
    schema(infer: true)
    auto_struct(true)

    def with_visible
      select_append { bool.cast(`TRUE`).as(:visible) }
    end
  end
end

rom.relations[:articles].insert(title: "Test title")

p rom.relations[:articles].with_visible.to_a # 'visible' is 1 on auto_struct
p rom.relations[:articles].with_visible.map_to(Structs::ArticleWithParamsBoolVisible).to_a # visible is true
# This fails (ideally should have visible as true)
# p rom.relations[:articles].with_visible.map_to(Structs::ArticleWithRegularBoolVisible).to_a

Expected behavior

I would expect that when I use bool.cast, I'd get a boolean back, regardless of which database is used. Even the result, with auto_struct set to true, I'd expect I'd get visible=true.

I'm not sure if this should be fixed in the casting code (as part of function) or implemented as an extension for SQLite.

A workaround that I found, shown above, is to use Types::Params::Bool, which maps 1 to true and 0 to false.

That works in this simple example, but in my app that's not working either. In fact, both Types::Bool and Types::Params::Bool "work" but instead of raising a Dry::Struct::Error, they just work with visible=1, in violation of the specified types that says they should be bools.

My environment

• Rom-sql 3.6, Rom 5.3

• Affects my production application: No

• Ruby version: 3.3

• OS: macOS 14.4.1