pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17051: Incorrect params inferred on PREPARE
5+ messages / 4 participants
[nested] [flat]
* BUG #17051: Incorrect params inferred on PREPARE
@ 2021-06-08 09:56 PG Bug reporting form <noreply@postgresql.org>
2021-06-08 13:05 ` Re: BUG #17051: Incorrect params inferred on PREPARE David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: PG Bug reporting form @ 2021-06-08 09:56 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: arthur.mcgibbon@gmail.com
The following bug has been logged on the website:
Bug reference: 17051
Logged by: Arthur McGibbon
Email address: arthur.mcgibbon@gmail.com
PostgreSQL version: 13.3
Operating system: Windows 10 + WSL2 + Docker
Description:
Using the table...
CREATE TABLE testSchema.testTable (timestampCol timestamp);
...and preparing the query...
PREPARE testQuery (unknown) AS
UPDATE testSchema.testTable
SET timestampCol = CASE WHEN timestampCol IS NULL THEN $1 ELSE NULL END;
...results in an error...
ERROR: column "timestampcol" is of type timestamp without time zone but
expression is of type text
LINE 3: set timestampCol = case when timestampCol is null then $1 ...
^
HINT: You will need to rewrite or cast the expression.
SQL state: 42804
Character: 80
Specifying the parameter as timestamp works without error...
PREPARE testQuery (timestamp) AS
UPDATE testSchema.testTable
SET timestampCol = CASE WHEN timestampCol IS NULL THEN $1 ELSE NULL END;
I'd hope that PostgreSQL would infer a "timestamp" type here or reply with
an error that it couldn't infer the parameter type.
Why does it default to type "text" and then effectively say the query is
invalid?
I can cast my parameter in the query to get around this but am submitting
the bug because inferring "text" seems wrong.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #17051: Incorrect params inferred on PREPARE
2021-06-08 09:56 BUG #17051: Incorrect params inferred on PREPARE PG Bug reporting form <noreply@postgresql.org>
@ 2021-06-08 13:05 ` David G. Johnston <david.g.johnston@gmail.com>
2021-06-08 13:37 ` Re: BUG #17051: Incorrect params inferred on PREPARE Arthur McGibbon <arthur.mcgibbon@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: David G. Johnston @ 2021-06-08 13:05 UTC (permalink / raw)
To: arthur.mcgibbon@gmail.com <arthur.mcgibbon@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On Tuesday, June 8, 2021, PG Bug reporting form <noreply@postgresql.org>
wrote:
>
> I'd hope that PostgreSQL would infer a "timestamp" type here or reply with
> an error that it couldn't infer the parameter type.
> Why does it default to type "text" and then effectively say the query is
> invalid?
Because no one has gotten a patch approved (not sure when/if there was a
last attempt for this specific situation) to make the system “smarter”.
>
> I can cast my parameter in the query to get around this but am submitting
> the bug because inferring "text" seems wrong.
>
>
Maybe, but the failure to be more intelligent in this area is not itself a
bug, and the rules for unknown literal resolution defaulting to text are
documented.
David J.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #17051: Incorrect params inferred on PREPARE
2021-06-08 09:56 BUG #17051: Incorrect params inferred on PREPARE PG Bug reporting form <noreply@postgresql.org>
2021-06-08 13:05 ` Re: BUG #17051: Incorrect params inferred on PREPARE David G. Johnston <david.g.johnston@gmail.com>
@ 2021-06-08 13:37 ` Arthur McGibbon <arthur.mcgibbon@gmail.com>
2021-06-08 13:48 ` Re: BUG #17051: Incorrect params inferred on PREPARE David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Arthur McGibbon @ 2021-06-08 13:37 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Thank you for the quick reply.
Could you point me to the documentation - I could only find
https://www.postgresql.org/docs/current/sql-prepare.html and it doesn't
mention how unknown params are handled.
On Tue, 8 Jun 2021 at 14:05, David G. Johnston <david.g.johnston@gmail.com>
wrote:
> On Tuesday, June 8, 2021, PG Bug reporting form <noreply@postgresql.org>
> wrote:
>
>>
>> I'd hope that PostgreSQL would infer a "timestamp" type here or reply with
>> an error that it couldn't infer the parameter type.
>> Why does it default to type "text" and then effectively say the query is
>> invalid?
>
>
> Because no one has gotten a patch approved (not sure when/if there was a
> last attempt for this specific situation) to make the system “smarter”.
>
>>
>> I can cast my parameter in the query to get around this but am submitting
>> the bug because inferring "text" seems wrong.
>>
>>
> Maybe, but the failure to be more intelligent in this area is not itself a
> bug, and the rules for unknown literal resolution defaulting to text are
> documented.
>
> David J.
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #17051: Incorrect params inferred on PREPARE
2021-06-08 09:56 BUG #17051: Incorrect params inferred on PREPARE PG Bug reporting form <noreply@postgresql.org>
2021-06-08 13:05 ` Re: BUG #17051: Incorrect params inferred on PREPARE David G. Johnston <david.g.johnston@gmail.com>
2021-06-08 13:37 ` Re: BUG #17051: Incorrect params inferred on PREPARE Arthur McGibbon <arthur.mcgibbon@gmail.com>
@ 2021-06-08 13:48 ` David G. Johnston <david.g.johnston@gmail.com>
2021-06-08 16:35 ` Re: BUG #17051: Incorrect params inferred on PREPARE Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: David G. Johnston @ 2021-06-08 13:48 UTC (permalink / raw)
To: Arthur McGibbon <arthur.mcgibbon@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
On Tuesday, June 8, 2021, Arthur McGibbon <arthur.mcgibbon@gmail.com> wrote:
>
>
> Could you point me to the documentation - I could only find
> https://www.postgresql.org/docs/current/sql-prepare.html and it doesn't
> mention how unknown params are handled.
>
https://www.postgresql.org/docs/current/typeconv-union-case.html
And more generally
https://www.postgresql.org/docs/current/typeconv.html
David J.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: BUG #17051: Incorrect params inferred on PREPARE
2021-06-08 09:56 BUG #17051: Incorrect params inferred on PREPARE PG Bug reporting form <noreply@postgresql.org>
2021-06-08 13:05 ` Re: BUG #17051: Incorrect params inferred on PREPARE David G. Johnston <david.g.johnston@gmail.com>
2021-06-08 13:37 ` Re: BUG #17051: Incorrect params inferred on PREPARE Arthur McGibbon <arthur.mcgibbon@gmail.com>
2021-06-08 13:48 ` Re: BUG #17051: Incorrect params inferred on PREPARE David G. Johnston <david.g.johnston@gmail.com>
@ 2021-06-08 16:35 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2021-06-08 16:35 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Arthur McGibbon <arthur.mcgibbon@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Tuesday, June 8, 2021, Arthur McGibbon <arthur.mcgibbon@gmail.com> wrote:
>> Could you point me to the documentation - I could only find
>> https://www.postgresql.org/docs/current/sql-prepare.html and it doesn't
>> mention how unknown params are handled.
> https://www.postgresql.org/docs/current/typeconv-union-case.html
Yeah, the point here is that the type of the parameter symbol is
guessed in the context of resolving the CASE construct. There's
no mechanism for applying external knowledge about what that CASE
ought to yield. While maybe we could do something in this
specific context, examples that are only slightly more complex
would really be quite impossible. For example,
... SET timestampCol = foo(CASE WHEN timestampCol IS NULL THEN $1 ELSE NULL END);
There's no way to tell which foo() function is meant until we've
identified a result type for the CASE, so the function context is
pretty much a blocker.
Roughly speaking, we only get to use one syntactic level of context
to guess the type of a parameter symbol. Doing better would really
be a research project.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2021-06-08 16:35 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-06-08 09:56 BUG #17051: Incorrect params inferred on PREPARE PG Bug reporting form <noreply@postgresql.org>
2021-06-08 13:05 ` David G. Johnston <david.g.johnston@gmail.com>
2021-06-08 13:37 ` Arthur McGibbon <arthur.mcgibbon@gmail.com>
2021-06-08 13:48 ` David G. Johnston <david.g.johnston@gmail.com>
2021-06-08 16:35 ` 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