Thread: [BUGS] BUG #14853: Parameter type is required even when the query does notneed to know the type
[BUGS] BUG #14853: Parameter type is required even when the query does notneed to know the type
From
edpeur@gmail.com
Date:
The following bug has been logged on the website: Bug reference: 14853 Logged by: Eduardo Perez Email address: edpeur@gmail.com PostgreSQL version: Unsupported/Unknown Operating system: All Description: The query: SELECT ? IS NULL should work even when the parameter type is unknown, as there is no need to know the type in that query. For reference: http://www.postgresql-archive.org/Regression-Problems-with-Timestamp-arguments-td5770255.html This patch fixes the issue: --- postgres/src/backend/tcop/postgres.c +++ postgres/src/backend/tcop/postgres.c @@ -1361,20 +1361,6 @@ ¶mTypes, &numParams); - /* - * Check all parameter types got determined. - */ - for (i = 0; i < numParams; i++) - { - Oid ptype = paramTypes[i]; - - if (ptype == InvalidOid || ptype == UNKNOWNOID) - ereport(ERROR, - (errcode(ERRCODE_INDETERMINATE_DATATYPE), - errmsg("could not determine data type of parameter $%d", - i + 1))); - } - if (log_parser_stats) ShowUsage("PARSE ANALYSIS STATISTICS"); -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #14853: Parameter type is required even when the querydoes not need to know the type
From
"David G. Johnston"
Date:
The following bug has been logged on the website:
Bug reference: 14853
Logged by: Eduardo Perez
Email address: edpeur@gmail.com
PostgreSQL version: Unsupported/Unknown
Operating system: All
Description:
The query:
SELECT ? IS NULL
should work even when the parameter type is unknown, as there is no need to
know the type in that query.
While your statement is correct the behavior that all parameters must have a type is not buggy. As I'm not in a position to comprehend just how much could go wrong by removing that restriction (and making it work only in cases where type doesn't matter, like IS NULL, is unappealing) I'll forgo much speculation but will say that given that the error is both immediate and obvious the likelihood of changing this is quite low.
The PostgreSQL project has intentionally made a number of changes in the past that tighten up things in the area of types (unknowns and casting) with full awareness that those changes may break existing applications. It was felt that, on the whole, the benefit to future coders outweighed the inconvenience of a subset of the existing code.
David J.
Re: [BUGS] BUG #14853: Parameter type is required even when the querydoes not need to know the type
From
Eduardo Pérez Ureta
Date:
I understand that not sending the type for a parameter (when it is not null) may not make much sense.
But, currently PostgreSQL accepts parameters with unknown types in statements like:
INSERT INTO t1 (col1) VALUES (?)
SELECT * FROM t1 WHERE col1 = ?
But, currently PostgreSQL accepts parameters with unknown types in statements like:
INSERT INTO t1 (col1) VALUES (?)
SELECT * FROM t1 WHERE col1 = ?
where the column can be different types like VARCHAR or TIMESTAMP
What are the rules? When a parameter type is required and when it is not required? Do these rules come from the SQL standard or are PostgreSQL own?
It seems odd to me that there is no test coverage for this code, so this change cannot be accepted as it may break something else, that nobody currently knows.
I think PostgreSQL should be more consistent and either require types for non-null parameters or not require types for non-null parameters (and let the actual function or operator decide if the type is needed or not). This incoherency causes these problems.
Is anybody interested in fixing this issue?
2017-10-13 15:38 GMT+00:00 David G. Johnston <david.g.johnston@gmail.com>:
The following bug has been logged on the website:
Bug reference: 14853
Logged by: Eduardo Perez
Email address: edpeur@gmail.com
PostgreSQL version: Unsupported/Unknown
Operating system: All
Description:
The query:
SELECT ? IS NULL
should work even when the parameter type is unknown, as there is no need to
know the type in that query.While your statement is correct the behavior that all parameters must have a type is not buggy. As I'm not in a position to comprehend just how much could go wrong by removing that restriction (and making it work only in cases where type doesn't matter, like IS NULL, is unappealing) I'll forgo much speculation but will say that given that the error is both immediate and obvious the likelihood of changing this is quite low.The PostgreSQL project has intentionally made a number of changes in the past that tighten up things in the area of types (unknowns and casting) with full awareness that those changes may break existing applications. It was felt that, on the whole, the benefit to future coders outweighed the inconvenience of a subset of the existing code.David J.
Re: [BUGS] BUG #14853: Parameter type is required even when the querydoes not need to know the type
From
"David G. Johnston"
Date:
On Sunday, October 15, 2017, Eduardo Pérez Ureta <edpeur@gmail.com> wrote:
I understand that not sending the type for a parameter (when it is not null) may not make much sense.
But, currently PostgreSQL accepts parameters with unknown types in statements like:
INSERT INTO t1 (col1) VALUES (?)
SELECT * FROM t1 WHERE col1 = ?where the column can be different types like VARCHAR or TIMESTAMP
col1 has a type and so the type of the unspecified variable can be inferred. Your is null example cannot have its typed inferred.
David J.
Re: [BUGS] BUG #14853: Parameter type is required even when the querydoes not need to know the type
From
Eduardo Pérez Ureta
Date:
My example is even better!
There is no need to infer the type as it is not needed!
PostgreSQL should be able to infer that no type is needed.
On Oct 15, 2017 8:23 PM, "David G. Johnston" <david.g.johnston@gmail.com> wrote:
On Sunday, October 15, 2017, Eduardo Pérez Ureta <edpeur@gmail.com> wrote:I understand that not sending the type for a parameter (when it is not null) may not make much sense.
But, currently PostgreSQL accepts parameters with unknown types in statements like:
INSERT INTO t1 (col1) VALUES (?)
SELECT * FROM t1 WHERE col1 = ?where the column can be different types like VARCHAR or TIMESTAMPcol1 has a type and so the type of the unspecified variable can be inferred. Your is null example cannot have its typed inferred.David J.
Re: [BUGS] BUG #14853: Parameter type is required even when the querydoes not need to know the type
From
Pavel Stehule
Date:
Hi
2017-10-16 7:40 GMT+02:00 Eduardo Pérez Ureta <edpeur@gmail.com>:
My example is even better!There is no need to infer the type as it is not needed!PostgreSQL should be able to infer that no type is needed.
PostgreSQL try to by type strict software. Sometimes the types can be detected from context, sometimes not. Somewhere this missing information is solved by type UNKNOWN, somewhere is raised a exception. Unfortunately there is not 100% consistency - some API is very strict, some less, some construct are very tolerant.
When you use a operator =, then unknown value should be casted to left side type.
postgres=# select 1=1;
?column?
----------
t
(1 row)
postgres=# select 1='1';
?column?
----------
t
(1 row)
postgres=# select 1='a';
ERROR: invalid input syntax for integer: "a"
LINE 1: select 1='a';
^
?column?
----------
t
(1 row)
postgres=# select 1='1';
?column?
----------
t
(1 row)
postgres=# select 1='a';
ERROR: invalid input syntax for integer: "a"
LINE 1: select 1='a';
^
Regards
Pavel
On Oct 15, 2017 8:23 PM, "David G. Johnston" <david.g.johnston@gmail.com> wrote:On Sunday, October 15, 2017, Eduardo Pérez Ureta <edpeur@gmail.com> wrote:I understand that not sending the type for a parameter (when it is not null) may not make much sense.
But, currently PostgreSQL accepts parameters with unknown types in statements like:
INSERT INTO t1 (col1) VALUES (?)
SELECT * FROM t1 WHERE col1 = ?where the column can be different types like VARCHAR or TIMESTAMPcol1 has a type and so the type of the unspecified variable can be inferred. Your is null example cannot have its typed inferred.David J.
Re: [BUGS] BUG #14853: Parameter type is required even when the querydoes not need to know the type
From
"David G. Johnston"
Date:
My example is even better!There is no need to infer the type as it is not needed!PostgreSQL should be able to infer that no type is needed.
It could - but since SQL is a strongly typed language it doesn't have that luxury.
The original thread you pointed to complained about the regression from protocol v2 to protocol v3. Is that your complaint too or do you have some other use case?
David J.
Re: [BUGS] BUG #14853: Parameter type is required even when the querydoes not need to know the type
From
Eduardo Pérez Ureta
Date:
I have an application that I am trying to make it work in PostgreSQL and PostgreSQL seems to be the only database that does not support queries like:
SELECT 1 WHERE ? IS NULL
with a Java setTimestamp parameter.
You say PostgreSQL is strongly typed, but an unknown type is accepted in the cases I presented before. Do you mean that PostgreSQL is not following the SQL standard?
On Oct 16, 2017 4:03 PM, "David G. Johnston" <david.g.johnston@gmail.com> wrote:
My example is even better!There is no need to infer the type as it is not needed!PostgreSQL should be able to infer that no type is needed.It could - but since SQL is a strongly typed language it doesn't have that luxury.The original thread you pointed to complained about the regression from protocol v2 to protocol v3. Is that your complaint too or do you have some other use case?David J.
Re: [BUGS] BUG #14853: Parameter type is required even when the querydoes not need to know the type
From
"David G. Johnston"
Date:
I have an application that I am trying to make it work in PostgreSQL and PostgreSQL seems to be the only database that does not support queries like:SELECT 1 WHERE ? IS NULLwith a Java setTimestamp parameter.You say PostgreSQL is strongly typed, but an unknown type is accepted in the cases I presented before. Do you mean that PostgreSQL is not following the SQL standard?
When PostgreSQL is done parsing a statement every externally visible element of that statement needs to have a type. For prepared statements that means output columns and input parameters. Data types for both either need to be explicitly stated or inferred from the context of the query as parsed.
I cannot speak to the SQL standard or other databases. I'm also not well-versed in the implementation details here - just the observed behavior. I do suspect some possibility for improvement here but someone would have to expend considerable time and effort and the lack of comments from hackers does not bode well for that at this moment (i.e., I don't think your recommended change works but I'm not experienced enough to say for certain). You are welcome to compile your own fork with that change incorporated and run both the PostgreSQL test suite and your application's test suite. Favorable results there might result in provoking interest from others.
David J.
Re: [BUGS] BUG #14853: Parameter type is required even when the querydoes not need to know the type
From
Pavel Stehule
Date:
2017-10-16 17:24 GMT+02:00 Eduardo Pérez Ureta <edpeur@gmail.com>:
this case is one, where datatype cannot be detected from context, and Postgres requires it.
I have an application that I am trying to make it work in PostgreSQL and PostgreSQL seems to be the only database that does not support queries like:SELECT 1 WHERE ? IS NULLwith a Java setTimestamp parameter.You say PostgreSQL is strongly typed, but an unknown type is accepted in the cases I presented before. Do you mean that PostgreSQL is not following the SQL standard?
There is another question if there is not some issue on Java maybe JDBC side.
Regards
Pavel
On Oct 16, 2017 4:03 PM, "David G. Johnston" <david.g.johnston@gmail.com> wrote:My example is even better!There is no need to infer the type as it is not needed!PostgreSQL should be able to infer that no type is needed.It could - but since SQL is a strongly typed language it doesn't have that luxury.The original thread you pointed to complained about the regression from protocol v2 to protocol v3. Is that your complaint too or do you have some other use case?David J.
Re: [BUGS] BUG #14853: Parameter type is required even when the querydoes not need to know the type
From
Tomas Vondra
Date:
Hi, On 10/16/2017 05:24 PM, Eduardo Pérez Ureta wrote: > I have an application that I am trying to make it work in PostgreSQL and > PostgreSQL seems to be the only database that does not support queries like: > SELECT 1 WHERE ? IS NULL > with a Java setTimestamp parameter. > So you're passing a timestamp value to PostgreSQL only to find out if it's NULL? I don't want to be rude, but that seems a bit ... strange. > > You say PostgreSQL is strongly typed, but an unknown type is accepted > in the cases I presented before. > As David already pointed out before, these examples are not equal. In the other cases PostgreSQL can easily infer the data type from other parts of the query (e.g. target column in an INSERT). But that's not the case here. > > Do you mean that PostgreSQL is not following the SQL standard? > Can you kindly point us to the part where SQL Standard requires the behavior you're requesting? thanks -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #14853: Parameter type is required even when the querydoes not need to know the type
From
Eduardo Pérez Ureta
Date:
I ran the PostgreSQL test suite (make check) and all the tests passed, so, my change should not break anything.
Maybe PostgreSQL should be changed to not require a type in this case.
On Oct 16, 2017 5:39 PM, "David G. Johnston" <david.g.johnston@gmail.com> wrote:
I have an application that I am trying to make it work in PostgreSQL and PostgreSQL seems to be the only database that does not support queries like:SELECT 1 WHERE ? IS NULLwith a Java setTimestamp parameter.You say PostgreSQL is strongly typed, but an unknown type is accepted in the cases I presented before. Do you mean that PostgreSQL is not following the SQL standard?When PostgreSQL is done parsing a statement every externally visible element of that statement needs to have a type. For prepared statements that means output columns and input parameters. Data types for both either need to be explicitly stated or inferred from the context of the query as parsed.I cannot speak to the SQL standard or other databases. I'm also not well-versed in the implementation details here - just the observed behavior. I do suspect some possibility for improvement here but someone would have to expend considerable time and effort and the lack of comments from hackers does not bode well for that at this moment (i.e., I don't think your recommended change works but I'm not experienced enough to say for certain). You are welcome to compile your own fork with that change incorporated and run both the PostgreSQL test suite and your application's test suite. Favorable results there might result in provoking interest from others.David J.
Re: [BUGS] BUG #14853: Parameter type is required even when the querydoes not need to know the type
From
Eduardo Pérez Ureta
Date:
I do not see why PostgreSQL cannot infer that no type is needed.
Maybe pgjdbc is the one that needs fixing but the lack of strong typing makes it difficult to make an acceptable patch.
On Oct 16, 2017 5:49 PM, "Pavel Stehule" <pavel.stehule@gmail.com> wrote:
2017-10-16 17:24 GMT+02:00 Eduardo Pérez Ureta <edpeur@gmail.com>:I have an application that I am trying to make it work in PostgreSQL and PostgreSQL seems to be the only database that does not support queries like:SELECT 1 WHERE ? IS NULLwith a Java setTimestamp parameter.You say PostgreSQL is strongly typed, but an unknown type is accepted in the cases I presented before. Do you mean that PostgreSQL is not following the SQL standard?this case is one, where datatype cannot be detected from context, and Postgres requires it.There is another question if there is not some issue on Java maybe JDBC side.RegardsPavelOn Oct 16, 2017 4:03 PM, "David G. Johnston" <david.g.johnston@gmail.com> wrote:My example is even better!There is no need to infer the type as it is not needed!PostgreSQL should be able to infer that no type is needed.It could - but since SQL is a strongly typed language it doesn't have that luxury.The original thread you pointed to complained about the regression from protocol v2 to protocol v3. Is that your complaint too or do you have some other use case?David J.
Re: [BUGS] BUG #14853: Parameter type is required even when the querydoes not need to know the type
From
Eduardo Pérez Ureta
Date:
On Oct 16, 2017 6:33 PM, "Tomas Vondra" <tomas.vondra@2ndquadrant.com> wrote:
Yes, it is a bit strange, but I am trying to not modify a working application.
I still do not see why PostgreSQL cannot infer that the type is not required.
Hi,
On 10/16/2017 05:24 PM, Eduardo Pérez Ureta wrote:
> I have an application that I am trying to make it work in PostgreSQL and
> PostgreSQL seems to be the only database that does not support queries like:
> SELECT 1 WHERE ? IS NULL
> with a Java setTimestamp parameter.
>
So you're passing a timestamp value to PostgreSQL only to find out if
it's NULL? I don't want to be rude, but that seems a bit ... strange.
Yes, it is a bit strange, but I am trying to not modify a working application.
>
> You say PostgreSQL is strongly typed, but an unknown type is accepted
> in the cases I presented before.
>
As David already pointed out before, these examples are not equal. In
the other cases PostgreSQL can easily infer the data type from other
parts of the query (e.g. target column in an INSERT). But that's not the
case here.
I still do not see why PostgreSQL cannot infer that the type is not required.
>
> Do you mean that PostgreSQL is not following the SQL standard?
>
Can you kindly point us to the part where SQL Standard requires the
behavior you're requesting?
I do not have access to the SQL standard. I was just asking.
Re: [BUGS] BUG #14853: Parameter type is required even when the querydoes not need to know the type
From
Tomas Vondra
Date:
On 10/16/2017 07:39 PM, Eduardo Pérez Ureta wrote: > I ran the PostgreSQL test suite (make check) and all the tests passed, > so, my change should not break anything. > Maybe PostgreSQL should be changed to not require a type in this case. > Absence of evidence is not evidence of absence, unfortunately. In other words, the fact that "make check" passes may easily be just due to an omission in the regression tests. Apparently there's no test that we actually produce the error when the type can't be inferred. That being said, I don't know if this is a correct change or not. Perhaps it is, perhaps it isn't - not sure. Consider adding it to the next commitfest [https://commitfest.postgresql.org/15/] where you'll get more feedback. You'll need to submit it to pgsql-hackers though. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #14853: Parameter type is required even when the querydoes not need to know the type
From
"David G. Johnston"
Date:
I ran the PostgreSQL test suite (make check) and all the tests passed, so, my change should not break anything.Maybe PostgreSQL should be changed to not require a type in this case.
I would say that the restriction that I describe is not tested/enforced then. But even if it was the problem would be that no where else in the test suite would queries with un-typed input parameters be used since they have been disallowed until now. That is just the nature of programming to a strict constraint and then attempting to loosen it up in the future - there will be no code that actually exercises the loosened constraints. That's why I suggested running your application test suite - right now it is getting errors; if you change only PostgreSQL does the JDBC driver and your application begin to work correctly with the changed server behavior?
I'd suggest you simply accept that, right now, PostgreSQL refuses allow an outcome of "no type is needed". You are welcome to spend as much time as you'd like convincing others that doing so is wrong and that not doing so is both useful and safe. I suppose you've already done that and it is now up to someone more qualified to explain exactly why what you propose will not work. That someone is not me.
David J.
Re: [BUGS] BUG #14853: Parameter type is required even when the query does not need to know the type
From
Tom Lane
Date:
Tomas Vondra <tomas.vondra@2ndquadrant.com> writes:
> In other words, the fact that "make check" passes may easily be just due
> to an omission in the regression tests. Apparently there's no test that
> we actually produce the error when the type can't be inferred.
> That being said, I don't know if this is a correct change or not.
The patch as presented (ie, just remove the check that all types
got determined) has no chance whatsoever of being accepted. The
reason that check exists is that client-side code isn't necessarily
going to cope with being told that a parameter it is supposed to supply
is of type "unknown". psql doesn't really care (in fact I don't think
it uses parameters at all), which is why the core regression tests pass.
But other clients such as JDBC have considerably more logic that depends
on the types of parameters. We're not going to risk breaking them for
this sort of dubious-in-any-case feature.
It's possible that we'd accept a patch that resolves the parameter
as type text if it's in a context where the type doesn't matter.
(Are there any such contexts other than IS [NOT] NULL?)
Arguably that's more consistent with the fact that we now resolve
"select $1" as being type text. But I'm not sure if that breaks any
cases that work today. The obvious counterexample is something like
prepare foo as select $1 is not null and $1 > 42;
where the parameter would need to be resolved as some other type later.
But that presently fails with "could not determine data type", so I don't
see a very good reason why "operator does not exist: text > integer" is
a worse outcome. The sticking point would be whether there are related
cases that succeed but such a patch would cause them to fail (or
silently change behavior, which is likely worse).
> You'll need to submit it to pgsql-hackers though.
Yes, this is certainly not a bug but a RFE.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs