Обсуждение: [BUGS] BUG #14853: Parameter type is required even when the query does notneed to know the type

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

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

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

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

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