pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #19033: Inconsistency between prepared statement and normal statement when cast bit to integer
3+ messages / 3 participants
[nested] [flat]
* BUG #19033: Inconsistency between prepared statement and normal statement when cast bit to integer
@ 2025-08-27 07:01 PG Bug reporting form <noreply@postgresql.org>
2025-08-27 14:38 ` 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-27 14:54 ` Re: BUG #19033: Inconsistency between prepared statement and normal statement when cast bit to integer Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 3+ messages in thread
From: PG Bug reporting form @ 2025-08-27 07:01 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: 798604270@qq.com
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)
```
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #19033: Inconsistency between prepared statement and normal statement when cast bit to integer
2025-08-27 07:01 BUG #19033: Inconsistency between prepared statement and normal statement when cast bit to integer PG Bug reporting form <noreply@postgresql.org>
@ 2025-08-27 14:38 ` David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: David G. Johnston @ 2025-08-27 14:38 UTC (permalink / raw)
To: 798604270@qq.com <798604270@qq.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #19033: Inconsistency between prepared statement and normal statement when cast bit to integer
2025-08-27 07:01 BUG #19033: Inconsistency between prepared statement and normal statement when cast bit to integer PG Bug reporting form <noreply@postgresql.org>
@ 2025-08-27 14:54 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2025-08-27 14:54 UTC (permalink / raw)
To: 798604270@qq.com; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-08-27 14:54 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-08-27 07:01 BUG #19033: Inconsistency between prepared statement and normal statement when cast bit to integer PG Bug reporting form <noreply@postgresql.org>
2025-08-27 14:38 ` David G. Johnston <david.g.johnston@gmail.com>
2025-08-27 14:54 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox