implement cross-type cast tests for all combinations within JSON as_XYZ() operators

[zzzeek]

as_string() should be tested against all of int / bool / float to always return a string on all backends. as_integer() should be tested to always return an int for a JSON int, float as well as a string that contains an integer, using CAST, same for as_float(), as_boolean().

this was never the expected contract for these methods but it is the apparent behavior for most dialects except SQLite right now.

[zzzeek]

see also #11065

[zzzeek]

consider adding a boolean to the methods "force_cast=True" something like that, indicating this is potentially a type cross-cast

[zzzeek]

probably overthinking it though

[zzzeek]

here's a test suite we can start with to see if this is even feasible. these tests pass 100% for PostgreSQL, where we are using CAST most consistently. the suite fails for mysql and sqlite as there are many casting combinations not accommodated:

    @testing.combinations(
        ("string",),
        ("integer",),
        ("float",),
        ("numeric",),
        ("boolean",),
        argnames="cross_cast",
    )
    @testing.combinations(
        ("boolean", True, {"string"}),
        ("boolean", False, {"string"}),
        ("boolean", None, {"all"}),
        ("string", "45", {"integer", "float", "numeric"}),
        ("string", "45.684", {"float", "numeric"}),
        ("string", "some string", {"string"}),
        ("string", None, {"all"}),
        ("string", "réve illé", {"string"}),
        ("integer", 15, {"string", "numeric", "float"}),
        ("integer", 1, {"all"}),
        ("integer", 0, {"all"}),
        ("integer", None, {"all"}),
        ("float", None, {"all"}),
        ("float", 1234567.89, {"string", "numeric"}),
        ("numeric", 1234567.89, {"string", "float"}),
        argnames="datatype, value, allowed_targets",
    )
    def test_index_cross_casts(
        self, datatype, value, allowed_targets, cross_cast
    ):
        data_table = self.tables.data_table
        data_element = {"key1": value}

        with config.db.begin() as conn:
            datatype, compare_value, p_s = self._json_value_insert(
                conn, datatype, value, data_element
            )

            expr = data_table.c.data["key1"]

            if cross_cast == "numeric":
                expr = getattr(expr, "as_%s" % cross_cast)(10, 2)
            else:
                expr = getattr(expr, "as_%s" % cross_cast)()

            if (
                cross_cast != datatype
                and "all" not in allowed_targets
                and cross_cast not in allowed_targets
            ):
                with expect_raises(Exception):
                    conn.scalar(select(expr))
                return
            else:
                roundtrip = conn.scalar(select(expr))

            if value is None:
                eq_(roundtrip, None)
            elif cross_cast == "string":
                assert isinstance(roundtrip, str)
            elif cross_cast == "integer":
                assert isinstance(roundtrip, int)
            elif cross_cast == "float":
                assert isinstance(roundtrip, float)
            elif cross_cast == "numeric":
                assert isinstance(roundtrip, decimal.Decimal)
            elif cross_cast == "boolean":
                assert isinstance(roundtrip, bool)
            else:
                assert False

[CaselIT]

We can probably also add

("string", "true", {"boolean"}),
("string", "false", {"boolean"}),