BUG #14853: Parameter type is required even when the query does not need to know the type

On Fri, Oct 13, 2017 at 7:03 AM, <edpeur@gmail.com> wrote:

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.

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

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>:

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.

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:

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';
^
Regards

Pavel

On Sun, Oct 15, 2017 at 10:40 PM, Eduardo Pérez Ureta <edpeur@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.

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:

On Mon, Oct 16, 2017 at 8:24 AM, Eduardo Pérez Ureta <edpeur@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 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?

​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.

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 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?

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.

Regards

Pavel

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

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 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.
See: https://github.com/pgjdbc/pgjdbc/pull/969

On Oct 16, 2017 5:49 PM, "Pavel Stehule" <pavel.stehule@gmail.com> wrote:

On Oct 16, 2017 6:33 PM, "Tomas Vondra" <tomas.vondra@2ndquadrant.com>
wrote:

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.

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

On Mon, Oct 16, 2017 at 10:39 AM, Eduardo Pérez Ureta <edpeur@gmail.com>
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.

​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.

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