Thread: Regression in 8.3?
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi, just compiled a fresh 8.3 on my Ubuntu Gutsy, but there's one thing which seems to have changed though I cannot get any hint about that in the docs: Given a table bla(a bigint,b text) select * from bla where a like '8%' works in <=8.2, but not on 8.3 Same happens with date columns, too. I know I can workaround by casting to text, but I thought the server would be intelligent enough to use a simple string conversion in that case on its own (like it did all the years before already...) tia, Patric -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) Comment: GnuPT 2.5.2 iD8DBQFHN5RyfGgGu8y7ypARAvF+AJ9Z+BMFi/KrYzNQ4H1vW//x5gtE4ACguISW JB3IsHmd0hqldhafEGFcRDQ= =orlu -----END PGP SIGNATURE-----
On Nov 11, 2007, at 18:46 , Patric Bechtel wrote: > Given a table bla(a bigint,b text) > > select * from bla where a like '8%' > > works in <=8.2, but not on 8.3 > > Same happens with date columns, too. The server is now more strict and has fewer implicit type casts in 8.3, so this is working as intended. If you want to treat a bigint or a date as a string, explicitly cast it. Michael Glaesemann grzm seespotcode net
Attachment
On Mon, Nov 12, 2007 at 12:46:58AM +0100, Patric Bechtel wrote:
> select * from bla where a like '8%'
Patrick, I'm not sure what you expect to come back:
8 ? 0.08 ?
8% of each of bla.a's values ?
do 7% or 9% count, too ? ("a like '8%'" seems to say "well,
about 8% of a, or close, anyways").
IOW, it is not entirely clear and thus the server forces us
to make a choice and not second-guess our intentions.
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Hi,
On Montag, 12. November 2007, Karsten Hilbert wrote:
| On Mon, Nov 12, 2007 at 12:46:58AM +0100, Patric Bechtel wrote:
| > select * from bla where a like '8%'
|
| Patrick, I'm not sure what you expect to come back:
|
| 8 ? 0.08 ?
|
| 8% of each of bla.a's values ?
|
| do 7% or 9% count, too ? ("a like '8%'" seems to say "well,
| about 8% of a, or close, anyways").
|
| IOW, it is not entirely clear and thus the server forces us
| to make a choice and not second-guess our intentions.
The %-sign in conjunction with a "like" operator in a string doesn't
leave much room for interpretation IMO, regardless if the "a" column
is of type "bigint" or not.
Ciao,
Thomas
--
Thomas Pundt <thomas.pundt@rp-online.de> ---- http://rp-online.de/ ----
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Karsten Hilbert schrieb am 12.11.2007 13:07:
> On Mon, Nov 12, 2007 at 12:46:58AM +0100, Patric Bechtel wrote:
>
>> select * from bla where a like '8%'
>
> Patrick, I'm not sure what you expect to come back:
>
> 8 ? 0.08 ?
>
> 8% of each of bla.a's values ?
>
> do 7% or 9% count, too ? ("a like '8%'" seems to say "well,
> about 8% of a, or close, anyways").
>
> IOW, it is not entirely clear and thus the server forces us
> to make a choice and not second-guess our intentions.
>
> Karsten
Just guess: We have bigint id's through the system, so I want to give
the users the convenience of typing only the last 4-5 digits (which most
of the time is enough). So the query we issue really is
... like "%$userinput"
Makes sense, or?
Patric
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: GnuPT 2.5.2
iD8DBQFHOFLZfGgGu8y7ypARAn0sAJ968gHCSICQtHH6ZkNHCXbvR2fTvACg6pU4
Bf9HiTiyB5UEvLbun4kGARs=
=YbyY
-----END PGP SIGNATURE-----
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Thomas Pundt schrieb am 12.11.2007 13:18:
> Hi,
>
> On Montag, 12. November 2007, Karsten Hilbert wrote:
> | On Mon, Nov 12, 2007 at 12:46:58AM +0100, Patric Bechtel wrote:
> | > select * from bla where a like '8%'
> |
> | Patrick, I'm not sure what you expect to come back:
> |
> | 8 ? 0.08 ?
> |
> | 8% of each of bla.a's values ?
> |
> | do 7% or 9% count, too ? ("a like '8%'" seems to say "well,
> | about 8% of a, or close, anyways").
> |
> | IOW, it is not entirely clear and thus the server forces us
> | to make a choice and not second-guess our intentions.
>
> The %-sign in conjunction with a "like" operator in a string doesn't
> leave much room for interpretation IMO, regardless if the "a" column
> is of type "bigint" or not.
>
> Ciao,
> Thomas
>
Second that. A like operator is for Strings. So I was, well *cough*,
surprised as it didn't. I mean it's unambiguous as it can be.
Patric
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: GnuPT 2.5.2
iD8DBQFHOFNWfGgGu8y7ypARAlrdAKCUyLWH2IfIO5ckGItL8NjHBup32wCcDQmy
Rqn5BtzSClTziJbjhubCm5Y=
=felz
-----END PGP SIGNATURE-----
On Mon, Nov 12, 2007 at 02:19:21PM +0100, Patric Bechtel wrote: > Just guess: We have bigint id's through the system, so I want to give > the users the convenience of typing only the last 4-5 digits (which most > of the time is enough). So the query we issue really is > ... like "%$userinput" > Makes sense, or? where id % 1000 = $userinput; (for 4-digit-long-userinput) will do the same, and at the very least will not bail out on 8.3 on the other hand. while i know and understand why there can't be "=" operator for text and int, i think that "like" could be readded as it is really clear about how it works. depesz -- quicksil1er: "postgres is excellent, but like any DB it requires a highly paid DBA. here's my CV!" :) http://www.depesz.com/ - blog dla ciebie (i moje CV)
On Mon, Nov 12, 2007 at 02:19:21PM +0100, Patric Bechtel wrote: > Just guess: We have bigint id's through the system, so I want to give > the users the convenience of typing only the last 4-5 digits (which most > of the time is enough). So the query we issue really is > ... like "%$userinput" > > Makes sense, or? It surely makes sense - in your environment - but it's not the only interpretation so PG tries to be impartial and makes both of us say clearly what we want. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
On Mon, Nov 12, 2007 at 02:46:50PM +0100, Karsten Hilbert wrote: > On Mon, Nov 12, 2007 at 02:19:21PM +0100, Patric Bechtel wrote: > > > Just guess: We have bigint id's through the system, so I want to give > > the users the convenience of typing only the last 4-5 digits (which most > > of the time is enough). So the query we issue really is > > ... like "%$userinput" > > > > Makes sense, or? > > It surely makes sense - in your environment - but it's not > the only interpretation so PG tries to be impartial and > makes both of us say clearly what we want. If people want it they can add the automatic cast back in, it just isn't dfault anymore. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Those who make peaceful revolution impossible will make violent revolution inevitable. > -- John F Kennedy
Attachment
Martijn van Oosterhout <kleptog@svana.org> writes:
> On Mon, Nov 12, 2007 at 02:46:50PM +0100, Karsten Hilbert wrote:
>> It surely makes sense - in your environment - but it's not
>> the only interpretation so PG tries to be impartial and
>> makes both of us say clearly what we want.
> If people want it they can add the automatic cast back in, it just
> isn't dfault anymore.
I wouldn't recommend that, as it'd re-open all the gotchas that we took
out the implicit cast to prevent.
However, if you want the behavior for LIKE only, you can make an operator:
regression=# select 84 like '8%';
ERROR: operator does not exist: integer ~~ unknown
LINE 1: select 84 like '8%';
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
regression=# create function anylike(anyelement, text) returns bool as $$
regression$# select $1::text like $2
regression$# $$ language sql;
CREATE FUNCTION
regression=# create operator ~~ ( procedure = anylike,
regression(# leftarg = anyelement, rightarg = text );
CREATE OPERATOR
regression=# select 84 like '8%';
?column?
----------
t
(1 row)
regression=#
regards, tom lane
On Nov 12, 2007, at 10:18 AM, Tom Lane wrote: > Martijn van Oosterhout <kleptog@svana.org> writes: >> On Mon, Nov 12, 2007 at 02:46:50PM +0100, Karsten Hilbert wrote: >>> It surely makes sense - in your environment - but it's not >>> the only interpretation so PG tries to be impartial and >>> makes both of us say clearly what we want. > >> If people want it they can add the automatic cast back in, it just >> isn't dfault anymore. > > I wouldn't recommend that, as it'd re-open all the gotchas that we > took > out the implicit cast to prevent. > > However, if you want the behavior for LIKE only, you can make an > operator: > > regression=# select 84 like '8%'; > ERROR: operator does not exist: integer ~~ unknown > LINE 1: select 84 like '8%'; > ^ > HINT: No operator matches the given name and argument type(s). You > might need to add explicit type casts. > regression=# create function anylike(anyelement, text) returns bool > as $$ > regression$# select $1::text like $2 > regression$# $$ language sql; > CREATE FUNCTION > regression=# create operator ~~ ( procedure = anylike, > regression(# leftarg = anyelement, rightarg = text ); > CREATE OPERATOR > regression=# select 84 like '8%'; > ?column? > ---------- > t > (1 row) Does this change in implicit conversions also apply to what I've previously seen recommended on the lists wrt filtering dates? i.e WHERE date_field LIKE '2007-11-12%'? Just curious... Erik Jones Software Developer | Emma® erik@myemma.com 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com
hubert depesz lubaczewski <depesz@depesz.com> writes:
> on the other hand. while i know and understand why there can't be "="
> operator for text and int, i think that "like" could be readded as it is
> really clear about how it works.
Really?
regression=# select '8.12345678901234567890' ~~ '%67890' ;
?column?
----------
t
(1 row)
regression=# select '8.12345678901234567890'::numeric ~~ '%67890' ;
?column?
----------
t
(1 row)
regression=# select '8.12345678901234567890'::float8 ~~ '%67890' ;
?column?
----------
f
(1 row)
regression=# select '8.12345678901234567890'::char(50) ~~ '%67890' ;
?column?
----------
f
(1 row)
Yup, the behavior of LIKE before 8.3 was just as datatype-independent
as could be.
regards, tom lane
On Mon, Nov 12, 2007 at 11:26:09AM -0500, Tom Lane wrote: > hubert depesz lubaczewski <depesz@depesz.com> writes: > > on the other hand. while i know and understand why there can't be "=" > > operator for text and int, i think that "like" could be readded as it is > > really clear about how it works. > Really? yes. i still hold my opinion: *int* like *text* is clear. depesz -- quicksil1er: "postgres is excellent, but like any DB it requires a highly paid DBA. here's my CV!" :) http://www.depesz.com/ - blog dla ciebie (i moje CV)
Martijn van Oosterhout <kleptog@svana.org> writes:
> > On Mon, Nov 12, 2007 at 02:46:50PM +0100, Karsten Hilbert wrote:
> >> It surely makes sense - in your environment - but it's not
> >> the only interpretation so PG tries to be impartial and
> >> makes both of us say clearly what we want.
> >
> > If people want it they can add the automatic cast back in, it just
> > isn't dfault anymore.
>
Any recommendations on how one might go about generating a list of all of the
removed implicit casts?
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
hubert depesz lubaczewski <depesz@depesz.com> writes:
> On Mon, Nov 12, 2007 at 11:26:09AM -0500, Tom Lane wrote:
>> hubert depesz lubaczewski <depesz@depesz.com> writes:
>>> on the other hand. while i know and understand why there can't be "="
>>> operator for text and int, i think that "like" could be readded as it is
>>> really clear about how it works.
>> Really?
> yes. i still hold my opinion: *int* like *text* is clear.
regression=# select '00123'::text like '0%';
?column?
----------
t
(1 row)
regression=# select '00123'::int4 like '0%';
?column?
----------
f
(1 row)
regards, tom lane
Robert Treat <xzilla@users.sourceforge.net> writes:
> Any recommendations on how one might go about generating a list of all of the
> removed implicit casts?
Compare 8.2 and 8.3 contents of pg_cast?
regards, tom lane
On Mon, Nov 12, 2007 at 05:18:28PM -0500, Tom Lane wrote: > regression=# select '00123'::text like '0%'; > ?column? > ---------- > t > (1 row) > regression=# select '00123'::int4 like '0%'; > ?column? > ---------- > f > (1 row) i think it's definitelly ok - '00123'::text is *not equal* to '00123'::int4. so result of "like'ing" it is not necessarily the same. what's more - in case patric showed - when he had int column there was no "danger" of '00123'::int4. best regards, depesz -- quicksil1er: "postgres is excellent, but like any DB it requires a highly paid DBA. here's my CV!" :) http://www.depesz.com/ - blog dla ciebie (i moje CV)