Re: [BUGS] SQL optimisation dead loop

[I am sending this to the hackers list, and the GEQO author.]

Yikes. This user has a query that causes the GEQO optimizer to really
go into orbit. According to the user, it consumes 135MB of memory
before failing.

Any comments?

On Mon, 13 Jul 1998, you wrote:

That is strange that GEQO is failing on this. I have never heard of
this happening. In fact, geqo was designed for large number of table
joins.

Can you send me a reproducable case that I can test with?

My first mail was containing such a sample. If you loosed it, I've tried to
rewrote (I am at home) it at the end of this mail.

Here is the query:
CREATE TABLE client (nom varchar not null, passwd varchar not null,
peut_creer bool not null, peut_lire bool not null, peut_stat bool not null,
est_admin bool, est_fournisseur bool not null, est_client bool not null,
raison_social varchar, contact varchar, adresse varchar, telephone varchar,
fax varchar, adr_facture varchar);

CREATE TABLE type (nom varchar not null, descr varchar not null);

CREATE TABLE offre (client oid, tipe oid, zone5 oid, dest5 oid, date_creation
datetime, valide_depuis datetime, valide_jusqua datetime, fichier oid,
commission float);

CREATE TABLE a_lut (offre oid, client oid, date_lecture datetime);

CREATE TABLE prix (offre oid, valeur float, nb_jours int, valide_de datetime,
valide_a datetime);

CREATE TABLE zone5 (nom varchar, zone4 oid, prix float);
CREATE TABLE zone4 (nom varchar, zone3 oid, prix float);
CREATE TABLE zone3 (nom varchar, zone2 oid, prix float);
CREATE TABLE zone2 (nom varchar, zone1 oid, prix float);
CREATE TABLE zone1 (nom varchar, prix float);

CREATE TABLE dest5 (nom varchar, dest4 oid);
CREATE TABLE dest4 (nom varchar, dest3 oid);
CREATE TABLE dest3 (nom varchar, dest2 oid);
CREATE TABLE dest2 (nom varchar, dest1 oid);
CREATE TABLE dest1 (nom varchar);

SELECT offre.oid as offre_oid,offre.client as offre_client,
offre.date_creation as offre_date_creation,
offre.valide_depuis as offre_valide_depuis,
offre.valide_jusqua as offre_valide_jusqua,
offre.commission as offre_commission,
type.oid as type_oid, type.nom as type_nom,
dest5.oid as dest5_oid,dest5.nom as dest5_nom,dest4.oid as dest4_oid,
dest4.nom as dest4_nom,dest3.oid as dest3_oid,dest3.nom as dest3_nom,
dest2.oid as dest2_oid,dest2.nom as dest2_nom,dest1.oid as dest1_oid,
dest1.nom as dest1_nom, zone5.oid as zone5_oid,zone5.nom as zone5_nom,
zone4.oid as zone4_oid, zone4.nom as zone4_nom,zone3.oid as zone3_oid,
zone3.nom as zone3_nom, zone2.oid as zone2_oid,zone2.nom as zone2_nom,
zone1.oid as zone1_oid, zone1.nom as zone1_nom FROM
offre,type,dest5,dest4,dest3,dest2,dest1,zone5,zone4,zone3,zone2,zone1
WHERE offre.tipe=type.oid AND
offre.dest5=dest5.oid AND dest5.dest4=dest4.oid AND dest4.dest3=dest3.oid AND
dest3.dest2=dest2.oid AND dest2.dest1=dest1.oid
offre.zone5=zone5.oid AND zone5.zone4=zone4.oid AND zone4.zone3=zone3.oid AND
zone3.zone2=zone2.oid AND zone2.zone1=zone1.oid

BOOM!!!!

best regards.
--
-���) Patrick Valsecchi /\\
_\_v http://dante.urbanet.ch/~patrick/index.html

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)