Strange behavior - Mailing list pgsql-sql

From Christophe Labouisse
Subject Strange behavior
Date
Msg-id m37lqz5g1h.fsf@gabuzo.meunet
Whole thread Raw
Responses Re: [SQL] Strange behavior
List pgsql-sql
[It may be a repost, I had a little mess in my mail last weeks, sorry]

I tried to make a simple select on a single table and I find some
strange results.

First of all I execute the following query :

=> select crit_url from crit where crit_url like 'films/%' order by crit_url;

crit_url
-------------------------------------
films/7-samurai.html

[...]

films/victor-pendant-qu-il.html
films/violent-cop.html
films/wild-things.html
(53 rows)

Now if it try this one :

select crit_url from crit where crit_url like '%films/%' order by crit_url;

crit_url
-------------------------------------
films/7-samurai.html

[...]

films/victor-pendant-qu-il.html
films/violent-cop.html
films/wild-things.html
films/y-aura-t-il-de-la-neige.html
films/you-ve-got-mail.html#ab
films/you-ve-got-mail.html#fvd
films/you-ve-got-mail.html#fvd
films/you-ve-got-mail.html#thb
(58 rows)

As you can notice there is 5 more rows in the second query that in the
first and, as far as my SQL understanding goes, they should have in
the first selection. Since there is an index (partly) on crit_url I
tried to remove it but it does change anything. Any idea ?

lumiere=> \d crit

Table    = crit
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| crit_id                          | int4 not null default nextval('c |     4 |
| films_id                         | int4 not null                    |     4 |
| crit_texte                       | text                             |   var |
| crit_date                        | date                             |     4 |
| crit_url                         | text                             |   var |
+----------------------------------+----------------------------------+-------+
Indices:  crit_crit_id_key
          crit_skey
lumiere=> \d crit_crit_id_key

Table    = crit_crit_id_key
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| crit_id                          | int4                             |     4 |
+----------------------------------+----------------------------------+-------+
lumiere=> \d crit_skey

Table    = crit_skey
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| films_id                         | int4                             |     4 |
| crit_url                         | text                             |   var |
+----------------------------------+----------------------------------+-------+

Christophe Labouisse : Cinéma, typographie, Unix
christophe.labouisse@dial.oleane.com http://www.multimania.com/gabuzo/
Le cinéma en Lumière : http://www.lumiere.org/

pgsql-sql by date:

Previous
From: Michael J Davis
Date:
Subject: RE: [SQL] substring
Next
From: Tom Lane
Date:
Subject: Re: [SQL] Strange behavior