Обсуждение: I don't like LIKE
There seems to be a problem with MakeIndexable(), though I haven't
confirmed that the problem lies there:
postgres=> select * from t1 where i like '2';
ERROR: pg_atoi: error in "2ÿ": can't parse "ÿ"
istm that this query should behave itself, or at least fail in some
other way :(
I'll guess that, even though there isn't a wildcard to pattern match,
MakeIndexable() is adding a trailing \377 to the string?
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
On Tue, 8 Jun 1999, Thomas Lockhart wrote:
> Date: Tue, 08 Jun 1999 13:53:01 +0000
> From: Thomas Lockhart <lockhart@alumni.caltech.edu>
> To: Postgres Hackers List <hackers@postgreSQL.org>
> Subject: [HACKERS] I don't like LIKE
>
> There seems to be a problem with MakeIndexable(), though I haven't
> confirmed that the problem lies there:
>
> postgres=> select * from t1 where i like '2';
> ERROR: pg_atoi: error in "2Ъ": can't parse "Ъ"
>
> istm that this query should behave itself, or at least fail in some
> other way :(
>
> I'll guess that, even though there isn't a wildcard to pattern match,
> MakeIndexable() is adding a trailing \377 to the string?
Just run on fresh cvs:
test=> \d t1
Table = t1
+----------------------------------+----------------------------------+-------+
| Field | Type | Length|
+----------------------------------+----------------------------------+-------+
| id | int4 not null | 4 |
| a | varchar() | 4 |
+----------------------------------+----------------------------------+-------+
Index: id_t1
test=> select * from t1 where id like '2';
id|a
--+---2|at1
(1 row)
It seems it's run ok, because I used --enable-locale option.
Regards,
Oleg
>
> - Thomas
>
> --
> Thomas Lockhart lockhart@alumni.caltech.edu
> South Pasadena, California
>
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
[Charset iso-8859-1 unsupported, filtering to ASCII...] > There seems to be a problem with MakeIndexable(), though I haven't > confirmed that the problem lies there: > > postgres=> select * from t1 where i like '2'; > ERROR: pg_atoi: error in "2_": can't parse "_" > > istm that this query should behave itself, or at least fail in some > other way :( > > I'll guess that, even though there isn't a wildcard to pattern match, > MakeIndexable() is adding a trailing \377 to the string? Please post example. Works here:select * from pg_class where rename like '2'; -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Bruce Momjian wrote: > > [Charset iso-8859-1 unsupported, filtering to ASCII...] > > There seems to be a problem with MakeIndexable(), though I haven't > > confirmed that the problem lies there: > > > > postgres=> select * from t1 where i like '2'; > > ERROR: pg_atoi: error in "2_": can't parse "_" > > > > istm that this query should behave itself, or at least fail in some > > other way :( > > > > I'll guess that, even though there isn't a wildcard to pattern match, > > MakeIndexable() is adding a trailing \377 to the string? > > Please post example. Works here: > > select * from pg_class where rename like '2'; Maybe he meant something like this (using 6.4.2) hannu=> \d t Table = t +----------------------------------+----------------------------------+-------+ | Field | Type | Length| +----------------------------------+----------------------------------+-------+ | i | int4 | 4 | +----------------------------------+----------------------------------+-------+ hannu=> create index indx on t(i); CREATE hannu=> vacuum; VACUUM hannu=> select * from t where i like '1'; ERROR: pg_atoi: error in "1ÿ": can't parse "ÿ" hannu=> --------------------- Hannu
> > Please post example. Works here: > > > > select * from pg_class where rename like '2'; > > Maybe he meant something like this (using 6.4.2) > > hannu=> \d t > > Table = t > +----------------------------------+----------------------------------+-------+ > | Field | Type | > Length| > +----------------------------------+----------------------------------+-------+ > | i | int4 > | 4 | > +----------------------------------+----------------------------------+-------+ > hannu=> create index indx on t(i); > CREATE > hannu=> vacuum; > VACUUM > hannu=> select * from t where i like '1'; > ERROR: pg_atoi: error in "1_": can't parse "_" > hannu=> Can't reproduce:test=> create table tx(i int);CREATEtest=> create index xx on tx(i);CREATEtest=> select * from tx where ilike '3';i-(0 rows)test=> vacuum;VACUUMtest=> select * from tx where i like '3';i-(0 rows) -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
> postgres=> select * from t1 where i like '2';
> ERROR: pg_atoi: error in "2�": can't parse "�"
> I'll guess that, even though there isn't a wildcard to pattern match,
> MakeIndexable() is adding a trailing \377 to the string?
Yup. This is an example of my point the other day: we shouldn't be
adding those index restriction clauses in the parser, but much later
on after type conversions have settled down and we know what we're
dealing with. I don't think there's a good quick-fix, we'll just have
to do it right.
If you use variables to prevent makeIndexable from triggering, you
will find that the system will indeed takeint4 like int4float4 like float4float8 like float8
which I find surprising, seeing as how there are no such operators.
Automatic anything->text conversion, apparently. I wonder whether
this isn't being a little too free with auto conversion.
regards, tom lane
Bruce Momjian <maillist@candle.pha.pa.us> writes:
> Can't reproduce:
> test=> select * from tx where i like '3';
> i
> -
> (0 rows)
If you've built with USE_LOCALE you won't see the failure,
because the parser doesn't add the right-side index qualification
in that case (at least not in 6.5; 6.4 did).
We still need a better solution for non-ASCII locales, too...
regards, tom lane
> If you use variables to prevent makeIndexable from triggering, you
> will find that the system will indeed take
> int4 like int4
> float4 like float4
> float8 like float8
> which I find surprising, seeing as how there are no such operators.
> Automatic anything->text conversion, apparently. I wonder whether
> this isn't being a little too free with auto conversion.
Yeah, maybe. But since there aren't regression tests for it, and no
apparent interest in adding them, it's pretty damn hard to add useful
features without damaging other things, eh?
Hmm, maybe I'd better simmer down about the docs stuff before
answering more mail ;)
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California