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

Tue, 08 Jun 1999 13:53:01 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
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


Tue, 8 Jun 1999 18:19:13 +0400 (MSD)
Oleg Bartunov <oleg@sai.msu.su>
Re: [HACKERS] I don't like LIKE
[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

Tue, 8 Jun 1999 13:08:46 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] I don't like LIKE
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

Tue, 08 Jun 1999 23:58:01 +0300
Hannu Krosing <hannu@trust.ee>
Re: [HACKERS] I don't like LIKE
> > 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

Tue, 8 Jun 1999 17:17:17 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] I don't like LIKE
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

Tue, 08 Jun 1999 18:18:12 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] I don't like LIKE 
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

Tue, 08 Jun 1999 18:32:32 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] I don't like LIKE 
> 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

Wed, 09 Jun 1999 01:58:03 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: [HACKERS] I don't like LIKE