Thread: Problem with fixed length fields.
Platform:
Postgres 7.4.5, lastest JDBC driver from CVS.
Table:
create table t(a char(2),b char(2));
insert into t values (' ',' ');
Jdbc:
This query finds nothing
PreparedStatement st = db.prepareStatement("select * from t where a=?
and b=?");
st.setString(1," ");
st.setString(2," ");
ResultSet rs = st.executeQuery();
This query works
rs = db.prepareStatement("select * from t where a=' ' and b='
'").executeQuery();
Why?
On Sat, 23 Oct 2004, Dave Smith wrote:
> Platform:
> Postgres 7.4.5, lastest JDBC driver from CVS.
>
> Table:
> create table t(a char(2),b char(2));
> insert into t values (' ',' ');
>
> Jdbc:
>
> This query finds nothing
> PreparedStatement st = db.prepareStatement("select * from t where a=?
> and b=?");
> st.setString(1," ");
> st.setString(2," ");
> ResultSet rs = st.executeQuery();
>
> This query works
> rs = db.prepareStatement("select * from t where a=' ' and b='
> '").executeQuery();
>
This is a problem with the driver because it is typing the ' ' values as
text, not char, so the comparison doesn't work right because these two
types handle trailing spaces differently. Notice:
jurka=# select length(' '::char);
length
--------
0
(1 row)
jurka=# select length(' '::text);
length
--------
1
(1 row)
At the moment setObject(i, str, Types.CHAR) doesn't work, but we could
make that happen, but it just doesn't seem like a good workaround.
Alternatively you could use ?::char in your query or str.trim(), but
that's certainly not intuitive or user friendly. We've got to come up
with something better than this.
Oliver, any ideas?
Kris Jurka
Kris Jurka <books@ejurka.com> writes:
> This is a problem with the driver because it is typing the ' ' values as
> text, not char, so the comparison doesn't work right because these two
> types handle trailing spaces differently.
Can you refrain from assigning a type at all? People writing the
equivalent SQL directly would almost always write 'foo', not
'foo'::text or 'foo'::char. The parser's behavior has been tuned
so that that usually works nicely.
regards, tom lane
Tom Lane wrote: > Kris Jurka <books@ejurka.com> writes: > >>This is a problem with the driver because it is typing the ' ' values as >>text, not char, so the comparison doesn't work right because these two >>types handle trailing spaces differently. > > > Can you refrain from assigning a type at all? People writing the > equivalent SQL directly would almost always write 'foo', not > 'foo'::text or 'foo'::char. The parser's behavior has been tuned > so that that usually works nicely. We've been trying to avoid that as there is currently no statement Describe issued by the driver, so we'd run the risk that the parser would infer something completely unexpected from the driver's point of view. Also, we'd need the Describe to get useful information on when a named statement can be safely reused (the driver currently gets this wrong anyway, but it's fixable without needing a Describe as it stands). Adding the Describe would add a round-trip to every query that involved an unknown type. Pushing on blindly with a Bind without knowing the actual type of the parameters you're sending seems possible but dangerous. It'd preclude using binary-format parameters for unknown types, at a minimum. -O
Oliver Jowett <oliver@opencloud.com> writes:
> Tom Lane wrote:
>> Can you refrain from assigning a type at all?
> Pushing on blindly with a Bind without knowing the actual type of the
> parameters you're sending seems possible but dangerous. It'd preclude
> using binary-format parameters for unknown types, at a minimum.
I had in mind doing this *only* for setString; you would essentially be
assuming that the resolved type was text, varchar, or char, which have
interchangeable binary formats.
It would probably be a good idea to do a Describe Statement to verify
that the parameter got resolved as one of those, but this need not add
a round-trip, since you could issue it with the Parse message.
regards, tom lane
Tom Lane wrote: > Oliver Jowett <oliver@opencloud.com> writes: > >>Tom Lane wrote: >> >>>Can you refrain from assigning a type at all? > > >>Pushing on blindly with a Bind without knowing the actual type of the >>parameters you're sending seems possible but dangerous. It'd preclude >>using binary-format parameters for unknown types, at a minimum. > > > I had in mind doing this *only* for setString; you would essentially be > assuming that the resolved type was text, varchar, or char, which have > interchangeable binary formats. But the driver has no guarantee that the backend will infer one of these particular types, right? > It would probably be a good idea to do a Describe Statement to verify > that the parameter got resolved as one of those, but this need not add > a round-trip, since you could issue it with the Parse message. Currently the driver issues Parse, Bind, Describe(portal), Execute, Sync. If I understand correctly, you're suggesting we do Parse, Describe(statement), Bind, Describe(portal), Execute, Sync. If the backend infers something other than a text-based type in Parse, doesn't Bad Stuff happen? (e.g. inserting random data into your database..) We could certainly notice the type mismatch when the results of the statement Describe come back, but by then it is too late, the query has already been executed. -O
Oliver Jowett <oliver@opencloud.com> writes:
> Tom Lane wrote:
>> It would probably be a good idea to do a Describe Statement to verify
>> that the parameter got resolved as one of those, but this need not add
>> a round-trip, since you could issue it with the Parse message.
> Currently the driver issues Parse, Bind, Describe(portal), Execute,
> Sync. If I understand correctly, you're suggesting we do Parse,
> Describe(statement), Bind, Describe(portal), Execute, Sync.
Oh, I was thinking in terms of setting up a prepared statement. You're
right, it seems risky to forge ahead to the Execute with an unknown
parameter type.
An alternative that might be worth investigating is to specify the type
as varchar instead of text. I am not sure of all the ramifications of
this but in 7.4 and later it seems like it might do what you want.
regards, tom lane
On Sat, 23 Oct 2004, Tom Lane wrote: > An alternative that might be worth investigating is to specify the type > as varchar instead of text. I am not sure of all the ramifications of > this but in 7.4 and later it seems like it might do what you want. > Yes, that seems to work and doesn't seem to break anything else, so that's what I've done. Kris Jurka