Обсуждение: BUG #14268: NULL parameter conversion
VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJz aXRlOgoKQnVnIHJlZmVyZW5jZTogICAgICAxNDI2OApMb2dnZWQgYnk6ICAg ICAgICAgIEpvcmRhbiBHaWdvdgpFbWFpbCBhZGRyZXNzOiAgICAgIGNvbGFk aWN0QGdtYWlsLmNvbQpQb3N0Z3JlU1FMIHZlcnNpb246IDkuMy4xMwpPcGVy YXRpbmcgc3lzdGVtOiAgIFVidW50dSAxNC4wNApEZXNjcmlwdGlvbjogICAg ICAgIAoKV2hlbiBzZXR0aW5nIGEgcGFyYW1ldGVyIGluIGEgcHJlcGFyZWQg c3RhdGVtZW50IHRvIE5VTEwgdGhyb3VnaCBKREJDICh0aGUKcHJvYmxlbSBp cyBub3QgdGhlcmUsIEkgd2VudCB0aHJvdWdoIHRoYXQgY29kZSksIGlmIHRo ZSBwYXJhbWV0ZXIgdHlwZSAob3IKT2lkIGFzIGl0IHNlZW1zIHRvIGJlIGNh bGxlZCBpbiB5b3VyIGNvZGUpIGlzIGRpZmZlcmVudCwgdGhhbiB0aGUgY29s dW1uCnR5cGUgaXQgY2F1c2VzIGEgdHlwZWNhc3RpbmcgZXJyb3IuIFdoZW4g dGhlIHZhbHVlIGlzIE5VTEwsIHRoZSB0eXBlCnNob3VsZG4ndCBtYXR0ZXIu DQoNClRoZXJlIGlzIG5vIHN0YW5kYXJkIHdheSB0byBleHBsaWNpdGx5IHNw ZWNpZnkgdGhlIHR5cGUgd2hlbiBzZXR0aW5nIGEKcGFyYW1ldGVyIHRvIG51 bGwgaW4gSmF2YSBQZXJzaXN0ZW5jZSBBUEksIHRodXMgdGhlIG9ubHkgb3Ro ZXIgd2F5IHRvIGF2b2lkCnRoZSBlcnJvciB3b3VsZCBiZSB0byBoYXZlIGEg Y29tcGxldGUgYW5hbHlzaXMgb2YgdGhlIHF1ZXJ5IGFuZCBtZXRhbW9kZWwK YmVmb3JlIHNlbmRpbmcgaXQgdG8gdGhlIGRhdGFiYXNlLg0KDQpUaGUgSlBB IHByb3ZpZGVyIEknbSB1c2luZyBleHBsaWNpdGx5IHNraXBzIFBvc3RncmVT UUwgaW4gaXQncyB0ZXN0cyBmb3IKTlVMTCBwYXJhbWV0ZXJzIGluIHByZXBh cmVkIHN0YXRlbWVudHMsIGFuZCBubyBvdGhlciBkYXRhYmFzZS4NCg0KQSBj b25zb2xlIGV4YW1wbGUgb2YgaG93IHRvIHRyaWdnZXIgdGhlIGVycm9yOg0K DQpDUkVBVEUgVEFCTEUgbXlfYXJyYXlfdGFibGUoaWQgc2VyaWFsIE5PVCBO VUxMLCBzb21lZGF0YSBiaWdpbnRbXSwgUFJJTUFSWQpLRVkoaWQpKTsNCklO U0VSVCBJTlRPIG15X2FycmF5X3RhYmxlKHNvbWVkYXRhKSBWQUxVRVMgKE5V TEwpOw0KSU5TRVJUIElOVE8gbXlfYXJyYXlfdGFibGUoc29tZWRhdGEpIFZB TFVFUyAoTlVMTDo6aW50ZWdlcltdKTsNCklOU0VSVCBJTlRPIG15X2FycmF5 X3RhYmxlKHNvbWVkYXRhKSBWQUxVRVMgKE5VTEw6OmJ5dGVhKTsNCg0KSSBy ZWNvZ25pemUgdGhhdCB0aGUgSkRCQyBkcml2ZXIgZXhwbGljaXRseSBzZW5k cyB0aGUgcGFyYW1ldGVyIHR5cGUgaW4gYQpwcmVwYXJlZCBzdGF0ZW1lbnQs IGJ1dCBJIHRoaW5rIGl0IHNob3VsZCBiZSBpZ25vcmVkIHdoZW4gdGhlIHZh bHVlIGlzCk5VTEwuDQoNCkFzIGJlc3QgSSBjYW4gdGVsbCwgdGhlIHBsYWNl IGZvciB0aGF0IGNoZWNrIGlzIGluCmJhY2tlbmQvcGFyc2VyL3BhcnNlX2Nv ZXJjZS5jIC0+IGNvZXJjZV90b190YXJnZXRfdHlwZSgpIGJlZm9yZSBvciBh bG9uZwp3aXRoIHRoZSBjYWxsIHRvIGNhbl9jb2VyY2VfdHlwZSgpLg0KQnV0 IEknbSBvdXQgb2YgcHJhY3RpY2Ugd2l0aCBDLCBhbmQgbm90IGRlZXAgZW5v dWdoIGluIHlvdXIgcHJvamVjdCB0byBzYXkKdGhlcmUgaXNuJ3QgYSBiZXR0 ZXIgc29sdXRpb24uCgo=
On Thu, Jul 28, 2016 at 9:30 AM, <coladict@gmail.com> wrote: > The following bug has been logged on the website: > > Bug reference: 14268 > Logged by: Jordan Gigov > Email address: coladict@gmail.com > PostgreSQL version: 9.3.13 > Operating system: Ubuntu 14.04 > Description: =E2=80=8B[...]=E2=80=8B > > When the value is NULL, the type > shouldn't matter. > =E2=80=8BAccording to what authority? =E2=80=8B[...] > INSERT INTO my_array_table(somedata) VALUES (NULL::bytea); > =E2=80=8BIf you are saying the above should work I'd say that is arguable a= t best.=E2=80=8B 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. > =E2=80=8BAs 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. =E2=80=8BThe JDBC API defines "setNull(int parameterIndex, int=E2=80=8B sql= Type); 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.
So, you're saying SQL is too sane a language for you and you'd rather have 30+ non-convertible types of null? 2016-07-28 16:56 GMT+03:00 David G. Johnston <david.g.johnston@gmail.com>: > On Thu, Jul 28, 2016 at 9:30 AM, <coladict@gmail.com> wrote: > >> The following bug has been logged on the website: >> >> Bug reference: 14268 >> Logged by: Jordan Gigov >> Email address: coladict@gmail.com >> PostgreSQL version: 9.3.13 >> Operating system: Ubuntu 14.04 >> Description: > > =E2=80=8B[...]=E2=80=8B > >> >> > When the value is NULL, the type >> shouldn't matter. >> > > =E2=80=8BAccording to what authority? > > =E2=80=8B[...] >> > INSERT INTO my_array_table(somedata) VALUES (NULL::bytea); >> > > =E2=80=8BIf you are saying the above should work I'd say that is arguable= at > best.=E2=80=8B 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. >> > > =E2=80=8BAs 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. > > =E2=80=8BThe JDBC API defines "setNull(int parameterIndex, int=E2=80=8B s= qlType); 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. B= ut > I can say this isn't a bug. PostgreSQL is operating as expected given ho= w > it handles NULL. > > David J. > > >
On Thu, Jul 28, 2016 at 3:54 PM, Jordan Gigov <coladict@gmail.com> wrote: > So, you're saying SQL is too sane a language for you and you'd rather hav= e > 30+ non-convertible types of null? > =E2=80=8BSELECT 1::int =3D '1'::text; -- ERROR: operator does not exist: in= teger =3D text David J.
I don't see how that helps your argument that NULLs shouldn't be convertible. 2016-07-28 23:01 GMT+03:00 David G. Johnston <david.g.johnston@gmail.com>: > On Thu, Jul 28, 2016 at 3:54 PM, Jordan Gigov <coladict@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? >> > > =E2=80=8BSELECT 1::int =3D '1'::text; -- ERROR: operator does not exist: = integer =3D > text > > David J. > >
=E2=80=8BPlease don't top-post. On Thu, Jul 28, 2016 at 4:16 PM, Jordan Gigov <coladict@gmail.com> wrote: > I don't see how that helps your argument that NULLs shouldn't be > convertible. > =E2=80=8BYour previous response didn't help your argument that they should = be convertible.=E2=80=8B =E2=80=8BNULLs aren't convertible - and while you can argue for a better po= licy 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-general@postgresql.org David J. =E2=80=8B
2016-07-28 23:36 GMT+03:00 David G. Johnston <david.g.johnston@gmail.com>: > =E2=80=8BPlease don't top-post. > > On Thu, Jul 28, 2016 at 4:16 PM, Jordan Gigov <coladict@gmail.com> wrote: > >> I don't see how that helps your argument that NULLs shouldn't be >> convertible. >> > > =E2=80=8BYour previous response didn't help your argument that they shoul= d be > convertible.=E2=80=8B > > =E2=80=8BNULLs 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 an= y > 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-general@postgresql.org > > David J. > =E2=80=8B > > While I don't plan on spending 180=E2=82=AC for the active ISO 9075 specifi= cations, 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.
>>>>> "Jordan" =3D=3D Jordan Gigov <coladict@gmail.com> writes: Jordan> While I don't plan on spending 180=E2=82=AC 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. --=20 Andrew (irc:RhodiumToad)