BUG?

[Martin Marques]

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

[Peter Eisentraut]

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 pe*****@gmx.net
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Martijn van Oosterhout]

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.cateusuarioAND
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 <kl*****@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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE/rPilY5Twig3Ge+YRAjmJAJ9cIRAswwBFl/nrxEoTrYI4BJUSpQCeNnPo
FEF8rs6cI4BOWig+8bZVZuo=
=hMXO
-----END PGP SIGNATURE-----