[BUGS] BUG #14268: NULL parameter conversion

[cola...@gmail.com]

The following bug has been logged on the website:

Bug reference: 14268
Logged by: Jordan Gigov
Email address: cola...@gmail.com
PostgreSQL version: 9.3.13
Operating system: Ubuntu 14.04
Description:

When setting a parameter in a prepared statement to NULL through JDBC (the
problem is not there, I went through that code), if the parameter type (or
Oid as it seems to be called in your code) is different, than the column
type it causes a typecasting error. When the value is NULL, the type
shouldn't matter.

There is no standard way to explicitly specify the type when setting a
parameter to null in Java Persistence API, thus the only other way to avoid
the error would be to have a complete analysis of the query and metamodel
before sending it to the database.

The JPA provider I'm using explicitly skips PostgreSQL in it's tests for
NULL parameters in prepared statements, and no other database.

A console example of how to trigger the error:

CREATE TABLE my_array_table(id serial NOT NULL, somedata bigint[], PRIMARY
KEY(id));
INSERT INTO my_array_table(somedata) VALUES (NULL);
INSERT INTO my_array_table(somedata) VALUES (NULL::integer[]);
INSERT INTO my_array_table(somedata) VALUES (NULL::bytea);

I recognize that the JDBC driver explicitly sends the parameter type in a
prepared statement, but I think it should be ignored when the value is
NULL.

As best I can tell, the place for that check is in
backend/parser/parse_coerce.c -> coerce_to_target_type() before or along
with the call to can_coerce_type().
But I'm out of practice with C, and not deep enough in your project to say
there isn't a better solution.

--
Sent via pgsql-bugs mailing list (pgsql...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

[David G. Johnston]

On Thu, Jul 28, 2016 at 9:30 AM, <cola...@gmail.com> wrote:

The following bug has been logged on the website:

Bug reference:      14268
Logged by:          Jordan Gigov
Email address:      cola...@gmail.com
PostgreSQL version: 9.3.13
Operating system:   Ubuntu 14.04
Description:

​[...]​

 

When the value is NULL, the type
shouldn't matter.

​According to what authority?

​[...]

INSERT INTO my_array_table(somedata) VALUES (NULL::bytea);

​If you are saying the above should work I'd say that is arguable at best.​  All values, even NULL, are typed in PostgreSQL.

 

I recognize that the JDBC driver explicitly sends the parameter type in a
prepared statement, but I think it should be ignored when the value is
NULL.

​As your first example shows if the NULL remains unknown it will be auto-cast according to the context in which it is used.  Its not PostgreSQL's place to discard type information.

​The JDBC API defines "setNull(int parameterIndex, int​ sqlType); which makes me inclined to say your complaint should be directed at JPA and not either PostgreSQL itself or JDBC.

Now, as I am lacking knowledge about the specific problem, JPA, and how other databases function, I am unable to meaningfully comment further.  But I can say this isn't a bug.  PostgreSQL is operating as expected given how it handles NULL.

David J.

[Jordan Gigov]

So, you're saying SQL is too sane a language for you and you'd rather have 30+ non-convertible types of null?

[David G. Johnston]

On Thu, Jul 28, 2016 at 3:54 PM, Jordan Gigov <cola...@gmail.com> wrote:

So, you're saying SQL is too sane a language for you and you'd rather have 30+ non-convertible types of null?

​SELECT 1::int = '1'::text; -- ERROR: operator does not exist: integer = text

David J.

[Jordan Gigov]

I don't see how that helps your argument that NULLs shouldn't be convertible.

[David G. Johnston]

​Please don't top-post.

On Thu, Jul 28, 2016 at 4:16 PM, Jordan Gigov <cola...@gmail.com> wrote:

I don't see how that helps your argument that NULLs shouldn't be convertible.

​Your previous response didn't help your argument that they should be convertible.​

​NULLs aren't convertible - and while you can argue for a better policy this one isn't wrong - I don't actually need to make arguments supporting that fact.  The underlying point, though, is the ability for types to be converted between one another is a property of the type itself and not any specific value that type may take on.

In any case - if you wish to turn this into a discussion I'd recommend putting together a more detailed argument and posting in on pgsql-...@postgresql.org

David J.

​

 

[Jordan Gigov]

While I don't plan on spending 180€ for the active ISO 9075 specifications, I did find a working draft of 9075-2 where under section 6.13 <cast specification> in the general rules it says:
"If the <cast operand> specifies NULL, then the result of CS is the null value and no further General Rules of this Subclause are applied."

You asked for an authority. This is the closest I can get, without unnecessary (at least for me, as someone who isn't implementing an SQL database) expenses.

I got it from http://www.wiscorp.com/SQLStandards.html the link that says "SQL:20nn Working Draft Documents". It doesn't have one of those red markers that I assume are for proposed and not finalized changes.

[Andrew Gierth]

>>>>> "Jordan" == Jordan Gigov <cola...@gmail.com> writes:

Jordan> While I don't plan on spending 180€ for the active ISO 9075
Jordan> specifications, I did find a working draft of 9075-2 where
Jordan> under section 6.13 <cast specification> in the general rules it
Jordan> says:

Jordan> "If the <cast operand> specifies NULL, then the result of CS is
Jordan> the null value and no further General Rules of this Subclause
Jordan> are applied."

"specifies NULL" is not the same thing as "has the null value" (see 2c).
By "specifies NULL" it means that the <cast operand> is an <implicitly
typed value expression> which is a <null specification> (which is the
literal token NULL). The effect of this is that CAST(NULL AS T) works
for any type T and returns the null value of that type. (The spec only
allows the typeless literal NULL in contexts from which a type can be
inferred for it.)

Nothing about this supports the idea that an expression of _known_ type
that simply happens to have the value NULL can be converted to some
other type. For example, a <value expression> which happens to have the
null value satisfies general rule 2c, but in order to get that far it
must first satisfy all of the syntax rules, including syntax rule 6
which specifies which data types are convertible. What this means is
that if x is some value (column, parameter, whatever) which happens to
be null (and its type is known, since in the spec the type of all value
expressions are known), then CAST(x AS T) is valid and returns the null
value of type T if and only if the type of x is convertible to T.

More to the point, where the type of parameter x is both known and not
assignable to the column C, then the statement

insert into T(C) values (x);

needs to generate an error _before the value of x is known_.

Note also that in pg it is legal to pass parameters of "unknown" type
such that their actual expected type is deduced from context; if one
sends a Parse for

insert into T(C) values ($1);

without specifying a known type oid for $1, then it will be deduced as
being of the type of T.C, and if the passed value is in fact the null
value then no conversion error will occur.

--
Andrew (irc:RhodiumToad)