Re: "could not determine data type of parameter" with timestamp

In fact, I'm afraid there is a bug that is not yet solved. See the message
of Denis Tazhkenov:
https://www.postgresql.org/message-id/DUB128-W530EEFFF5B2450BD29CD9EAB5B0%40phx.gbl
.

Denis proposed two workarounds:
1. change the prepared statement to add the suffix "::uuid" to the
parameter name
2. when the parameter is known to be null, set any type, for instance
Types.VARCHAR

None of these workarounds can be applied to our case because:
1. our prepared statements are build earlier and we really don't want to
parse them to replace some "? is null" with "?::uuid is null"
2. our expressions are always similar to "(:param is null) or (col =
:param)" and using an arbitrary type obviously won't work for the second
part of the expression

Any other workaround?
Any hope to have a fix for this issue?

Rémi

Le mar. 27 mars 2018 à 17:17, Rémi Aubel <remi(dot)aubel(at)gmail(dot)com> a écrit :

> Hi all,
>
> I'm coming back to this "? is null" problem.
> I have understood that it is important to "always use typed nulls" in
> prepared statements. So, finally, we are adapting our code.
>
> But I'm stuck with UUID. The UUID type does not exist in java.sql.Types.
> If I ask to the Portgres JDBC driver the type of a UUID column (for
> instance using the ResultSet.getJdbcColumnType() method), I get 1111
> (OTHER).
> But if I use this type for a statement with "? is null", the driver keeps
> on raising the "ERROR: could not determine data type of parameter $1".
>
> Which SQL type should I use for UUID?
>
> Rémi
>
> Le mer. 31 janv. 2018 à 05:41, Brad DeJong <bpd0018(at)gmail(dot)com> a écrit :
>
>> Dave and Vladimir - please jump in if I've misunderstood how pgJDBC works
>> in these cases.
>>
>> Paraphrasing what Rémi wrote:
>>
>> > Why is the driver not able to ignore the parameter type when we just
>> want to compare it to null?
>>
>> The pgJDBC driver can ignore the parameter type. Technically it passes
>> the type as unspecified (0) and it passes the value as a string, but the
>> effect is to ignore the parameter type.
>>
>> The PostgreSQL backend cannot ignore the parameter type because it needs
>> to know which RECEIVE function to call in order to interpret the value that
>> is sent to it over the wire. (see
>> https://www.postgresql.org/docs/current/static/sql-createtype.html) Because
>> your program is using a PreparedStatement and because a PreparedStatement
>> can be executed multiple times with different parameters every time, the
>> backend must be ready to handle both null and non-null values. The
>> PostgreSQL wire level v3 protocol allows pgJDBC to pass the parameter type
>> as 0 (org.postgresql.core.Oid.UNSPECIFIED). However, setting the
>> parameter type to 0 tells the PostgreSQL backend to determine the type of
>> the parameter from the statement context.
>>
>> In the case of the "where ? is null" query, there isn't enough context to
>> determine the type and PostgreSQL itself sends the "ERROR: could not
>> determine data type of parameter $1" message to pgJDBC.
>>
>> The same error would happen with an ODBC driver or libpq or any other
>> driver.
>>
>> When your program calls ps.setNull(1, Types.VARCHAR), pgJDBC sends the
>> parameter type as Oid.VARCHAR and the PostgreSQL backend doesn't have to
>> figure it out from the context. It parses the query as "where ?::varchar is
>> null" and is perfectly happy. It would be just as happy with ps.setNull(1,
>> Types.DATE) which would be interpreted as "where ?::date is null" or some
>> other type. (If PGProperty.STRING_TYPE is set to "unspecified" at
>> connect time and you call rs.setNull(1, Types.VARCHAR), pgJDBC sends the
>> null value as Oid.UNSPECIFIED and your workaround would fail.)
>>
>> The flip side of that is that you can explicitly pass values as untyped
>> parameters by using Types.OTHER and let PostgreSQL figure out the type for
>> you. For example, "insert into mytable (myjsoncolumn) values (?)" works
>> when you call ps.setObject(1, "{ \"name\":\"John\" }", Types.OTHER).
>> With the same statement, ps.setString(1, "{ \"name\":\"John\" }") would
>> fail with a "ERROR: column " myjsoncolumn" is of type json but
>> expression is of type character varying" message. And ps.setObject(1, "This
>> is not JSON.", Types.OTHER) fails with "ERROR: invalid input syntax for
>> type json". In all 3 cases, the backend knows that myjsoncolumn is a JSON
>> column and deals with the parameter types/values accordingly.
>>
>> > with "where birth_date > ?" ... ps.setNull(1, Types.VARCHAR) does not
>> work
>>
>> This is an entirely different issue. In this case the PostgreSQL knows
>> the type of the birth_date column is date and expects the parameter to
>> match or for an implicit conversion between the parameter type and date to
>> exist. That is why ps.setNull(1, Types.VARCHAR) fails with "ERROR:
>> operator does not exist: date > character varying". For this case, you can
>> tell pgJDBC to pass the parameter type as 0 by calling ps.setNull(1,
>> Types.NULL) or you can call "ParameterMetaData pmeta =
>> ps.getParameterMetaData()" to get the meta data for the parameters and then
>> call "ps.setNull(1, pmeta.getParameterType(1))" to set the null with the
>> type that PostgreSQL expects. pmeta.getParameterType(int) is generally the
>> better option because it avoids the performance issues that Vladimir
>> mentions in his presentation.
>>
>> Note that getParameterMetaData() gets the parameter type information by
>> asking the PostgreSQL backend to determine the types from the statement
>> context. This means that calling getParameterMetaData() for the "where ? is
>> null" case will fail with the "ERROR: could not determine data type of
>> parameter $1" message just like executing it with an unspecified null.
>>
>>
>>
>>
>> On Wed, Dec 6, 2017 at 7:38 AM, Dave Cramer <pg(at)fastcrypt(dot)com> wrote:
>>
>>>
>>>
>>>
>>>
>>> On 6 December 2017 at 08:26, Rémi Aubel <remi(dot)aubel(at)gmail(dot)com> wrote:
>>>
>>>> Yes, ps.setNull(1, Types.TIMESTAMP) works.
>>>> But, once again, with the way my application works, when I bind the
>>>> parameters, the query is already built. So, if my parameter is null, I have
>>>> no way to guess its type.
>>>> I tried ps.setNull(1, Types.VARCHAR) as a fallback, and it works with
>>>> a condition like "where ? is null". It does not work with a condition
>>>> like "where birth_date > ?".
>>>> Maybe we could parse the query to use ps.setNull(1, Types.VARCHAR)
>>>> when we have something like "? is null" (and a null bound parameter),
>>>> but it seems to be weak (and a little bit dirty).
>>>>
>>>
>>> We would really like to avoid parsing the query.
>>>
>>>
>>>> Why is the driver not able to ignore the parameter type when we just
>>>> want to compare it to null?
>>>>
>>>> This is the way the extended protocol with PostgreSQL works.
>>>
>>> Not much help for you but the api has contemplated this problem as there
>>> is the above mentioned method.
>>>
>>> Dave Cramer
>>>
>>> davec(at)postgresintl(dot)com
>>> www.postgresintl.com
>>>
>>>> Rémi.
>>>>
>>>> Le mer. 6 déc. 2017 à 01:58, Dave Cramer <pg(at)fastcrypt(dot)com> a écrit :
>>>>
>>>>> So ps.setNull(1, Types.TIMESTAMP);
>>>>>
>>>>> Doesn't work ?
>>>>>
>>>>> Dave Cramer
>>>>>
>>>>> davec(at)postgresintl(dot)com
>>>>> www.postgresintl.com
>>>>>
>>>>> On 5 December 2017 at 15:42, David G. Johnston <
>>>>> david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>>>>>
>>>>>> On Tue, Dec 5, 2017 at 8:11 AM, Rémi Aubel <remi(dot)aubel(at)gmail(dot)com>
>>>>>> wrote:
>>>>>>
>>>>>>> Hello,
>>>>>>>
>>>>>>> I know my problem has already been answered in this list (
>>>>>>> https://www.postgresql.org/message-id/CA%2BxsaB0EMDaNek0Oky9c17_8UX3-epWVP11%2BvTNgrAPoY2s9FA%40mail.gmail.com)
>>>>>>> but it was a long time ago and I would like to know if any other solution
>>>>>>> exists now.
>>>>>>>
>>>>>>
>>>>>> ​Not that I am aware.​
>>>>>>
>>>>>>
>>>>>>> But this workaround is not really useful for me, because my
>>>>>>> application uses generated (dynamic) queries and targets multiple database
>>>>>>> types (not only PosgreSQL). So, when I know the targeted database, I do not
>>>>>>> know my parameter types anymore.
>>>>>>>
>>>>>>
>>>>>> ​I'd probably perform the null test in Java and pass the true/false
>>>>>> boolean result along to the query:
>>>>>>
>>>>>> SELECT * FROM my_table WHERE ?::bool;
>>>>>> ps.setBoolean(1, tsvar = null); //my Java is rusty but you get the
>>>>>> idea.
>>>>>>
>>>>>> That should work in any database.
>>>>>>
>>>>>> David J.
>>>>>> ​
>>>>>>
>>>>>
>>>>> --
>>>>
>>>>
>>>>
>> --
>
>
> --