pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Christophe Labouisse <labouiss@cybercable.fr>
To: pgsql-sql@postgreSQL.org
Subject: Strange behavior
Date: 26 Apr 1999 21:37:46 +0200
Message-ID: <m37lqz5g1h.fsf@gabuzo.meunet> (raw)


[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/

view thread (9+ messages)  latest in thread

Message-ID: <m37lqz5g1h.fsf@gabuzo.meunet>
Permalink:  ../m37lqz5g1h.fsf@gabuzo.meunet/
Also on:    postgresql.org/message-id/m37lqz5g1h.fsf@gabuzo.meunet

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: labouiss@cybercable.fr
  Subject: Re: Strange behavior
  In-Reply-To: <m37lqz5g1h.fsf@gabuzo.meunet>
  References: <m37lqz5g1h.fsf@gabuzo.meunet>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox