I don't like LIKE

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, Pennsylvania 19026

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);
CREATE
test=> create index xx on tx(i);
CREATE
test=> select * from tx where i like '3';
i
-
(0 rows)

test=> vacuum;
VACUUM
test=> 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, Pennsylvania 19026

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 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.

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