Horton

Thread

  1. BUG #19033: Inconsistency between prepared statement and normal statement when cast bit to integer

    PG Bug reporting form <noreply@postgresql.org> — 2025-08-27T07:01:27Z

    The following bug has been logged on the website:

Bug reference:      19033
Logged by:          Chi Zhang
Email address:      798604270@qq.com
PostgreSQL version: 18beta3
Operating system:   ubuntu 24.04 with docker
Description:        

Hi,

In the normal statement, casting a bit value to INT is allowed, but in a
prepared statement, it will trigger an error.

In the following test case, the first query will return 15, but the EXECUTE
statement will trigger an error: parameter $1 of type bit cannot be coerced
to the expected type integer

```
SELECT (B'1111')::INT;
 int4
------
   15
(1 row)

PREPARE prepare_query (unknown) AS SELECT $1::INT;
EXECUTE prepare_query(B'1111');
ERROR:  parameter $1 of type bit cannot be coerced to the expected type
integer
```

If I modify the unknown type to bit, then the query can execute successfully
and return 15.
```
PREPARE prepare_query (bit) AS SELECT $1::INT;
EXECUTE prepare_query(B'1111');
 int4
------
   15
(1 row)
```

I also find if I first cast B'1111' to bit and then cast it to integer, it
will become 1. But the correct result should be 15.

```
SELECT ((B'1111')::bit)::INT;
 int4
------
    1
(1 row)
```

  2. Re: BUG #19033: Inconsistency between prepared statement and normal statement when cast bit to integer

    David G. Johnston <david.g.johnston@gmail.com> — 2025-08-27T14:38:11Z

    On Wednesday, August 27, 2025, PG Bug reporting form <noreply@postgresql.org>
wrote:

> The following bug has been logged on the website:
>
> Bug reference:      19033
> Logged by:          Chi Zhang
> Email address:      798604270@qq.com
> PostgreSQL version: 18beta3
> Operating system:   ubuntu 24.04 with docker
> Description:
>
> Hi,
>
> In the normal statement, casting a bit value to INT is allowed, but in a
> prepared statement, it will trigger an error.
>
> In the following test case, the first query will return 15, but the EXECUTE
> statement will trigger an error: parameter $1 of type bit cannot be coerced
> to the expected type integer
>
> ```
> SELECT (B'1111')::INT;
>  int4
> ------
>    15
> (1 row)
>
> PREPARE prepare_query (unknown) AS SELECT $1::INT;
> EXECUTE prepare_query(B'1111');
> ERROR:  parameter $1 of type bit cannot be coerced to the expected type
> integer
> ```
>
> If I modify the unknown type to bit, then the query can execute
> successfully
> and return 15.
> ```
> PREPARE prepare_query (bit) AS SELECT $1::INT;
> EXECUTE prepare_query(B'1111');
>  int4
> ------
>    15
> (1 row)
> ```
>

This doesn’t feel like a bug, using “unknown” is likely to cause typing
issue, but I’m not sure I can explains exactly why.



> I also find if I first cast B'1111' to bit and then cast it to integer, it
> will become 1. But the correct result should be 15.
>
> ```
> SELECT ((B'1111')::bit)::INT;
>  int4
> ------
>     1
> (1 row)
> ```
>

The documentation explicitly contradicts your claim here and explain why
this is correct.

David J.

  3. Re: BUG #19033: Inconsistency between prepared statement and normal statement when cast bit to integer

    Tom Lane <tgl@sss.pgh.pa.us> — 2025-08-27T14:54:55Z

    PG Bug reporting form <noreply@postgresql.org> writes:
> PREPARE prepare_query (unknown) AS SELECT $1::INT;
> EXECUTE prepare_query(B'1111');
> ERROR:  parameter $1 of type bit cannot be coerced to the expected type
> integer

The reason that fails is that EvaluateParams uses assignment-coercion
semantics, which is more limited in what it will allow than an
explicit coercion.  I think this is correct behavior.  The system
should not silently perform cross-type-category casts --- too much
risk of unintended results.

			regards, tom lane