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(at)candle(dot)pha(dot)pa(dot)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)