Thread: BUG?

BUG?

From
Martin Marques
Date:
Look at the NOTICE:

siprebi=> SELECT max(tprestamo.cantmaxpres) FROM  tprestamo t,usuarios
u,personas p,objetos o, tipodoc td  WHERE t.cateusuario = u.cateusuario AND
u.persona = p.codigo AND p.docnum = 23622139 AND p.tdocu = td.codigo AND
td.nombre = 1 AND t.cateobjeto = o.cateobjeto AND o.ninventario = 29 AND
o.uacademi = 4;
NOTICE:  Adding missing FROM-clause entry for table "tprestamo"

Now, look at the exact same query, only changing the field in max(), which is
the same, only that I use the alias of the table:

siprebi=> SELECT max(t.cantmaxpres) FROM  tprestamo t,usuarios u,personas
p,objetos o, tipodoc td  WHERE t.cateusuario = u.cateusuario AND  u.persona =
p.codigo AND p.docnum = 23622139 AND p.tdocu = td.codigo AND  td.nombre = 1
AND t.cateobjeto = o.cateobjeto AND o.ninventario = 29 AND  o.uacademi = 4;
 max
-----

Looks like a bug.

siprebi=> select version();
                                   version
------------------------------------------------------------------------------
 PostgreSQL 7.3.2 on sparc-unknown-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
(1 row)


--
 10:33:01 up 17 days, 17:55,  3 users,  load average: 0.34, 0.41, 0.41
-----------------------------------------------------------------
Martín Marqués        | select 'mmarques' || '@' || 'unl.edu.ar'
Centro de Telematica  |  DBA, Programador, Administrador
             Universidad Nacional
                  del Litoral
-----------------------------------------------------------------


Re: BUG?

From
Peter Eisentraut
Date:
Martin Marques writes:

> Look at the NOTICE:
>
> siprebi=> SELECT max(tprestamo.cantmaxpres) FROM  tprestamo t,usuarios
> u,personas p,objetos o, tipodoc td  WHERE t.cateusuario = u.cateusuario AND
> u.persona = p.codigo AND p.docnum = 23622139 AND p.tdocu = td.codigo AND
> td.nombre = 1 AND t.cateobjeto = o.cateobjeto AND o.ninventario = 29 AND
> o.uacademi = 4;
> NOTICE:  Adding missing FROM-clause entry for table "tprestamo"

If you assign an alias to a table, the original table name becomes
invisible.  Read the documentation.

--
Peter Eisentraut   peter_e@gmx.net


Re: BUG?

From
Martijn van Oosterhout
Date:
No, it's right. In the first query the only time you refer to table
tprestamo is in the max() clause, all the other places use the alias "t" which
is *not the same thing*. The second refers to the table by the alias always.

So in the first query you are joining the tprestamo table twice, which is
probably not what you want.

Hope this helps,

On Sat, Nov 08, 2003 at 10:35:49AM -0300, Martin Marques wrote:
> Look at the NOTICE:
>
> siprebi=> SELECT max(tprestamo.cantmaxpres) FROM  tprestamo t,usuarios
> u,personas p,objetos o, tipodoc td  WHERE t.cateusuario = u.cateusuario AND
> u.persona = p.codigo AND p.docnum = 23622139 AND p.tdocu = td.codigo AND
> td.nombre = 1 AND t.cateobjeto = o.cateobjeto AND o.ninventario = 29 AND
> o.uacademi = 4;
> NOTICE:  Adding missing FROM-clause entry for table "tprestamo"
>
> Now, look at the exact same query, only changing the field in max(), which is
> the same, only that I use the alias of the table:
>
> siprebi=> SELECT max(t.cantmaxpres) FROM  tprestamo t,usuarios u,personas
> p,objetos o, tipodoc td  WHERE t.cateusuario = u.cateusuario AND  u.persona =
> p.codigo AND p.docnum = 23622139 AND p.tdocu = td.codigo AND  td.nombre = 1
> AND t.cateobjeto = o.cateobjeto AND o.ninventario = 29 AND  o.uacademi = 4;
>  max
> -----
>
> Looks like a bug.
>
> siprebi=> select version();
>                                    version
> ------------------------------------------------------------------------------
>  PostgreSQL 7.3.2 on sparc-unknown-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
> (1 row)
>
>
> --
>  10:33:01 up 17 days, 17:55,  3 users,  load average: 0.34, 0.41, 0.41
> -----------------------------------------------------------------
> Martín Marqués        | select 'mmarques' || '@' || 'unl.edu.ar'
> Centro de Telematica  |  DBA, Programador, Administrador
>              Universidad Nacional
>                   del Litoral
> -----------------------------------------------------------------
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend

--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> "All that is needed for the forces of evil to triumph is for enough good
> men to do nothing." - Edmond Burke
> "The penalty good people pay for not being interested in politics is to be
> governed by people worse than themselves." - Plato

Attachment