Re: BUG #4035: sql table aliases do not work

On Fri, Mar 14, 2008 at 08:53:08PM +0000, RGF wrote:
> PostgreSQL version: latest

do you mean 8.2.6, 8.3.0 or a latest version of some other series?
please try a little harder next time!!

> Description: sql table aliases do not work

they do whenever I use them! by the looks of your SQL you're not even
using them where you should be:

> Context: SQL statement "update tonodes set tonodes.cost = case when
> tonodes.cost is NULL then fromnodes.cost + paths.cost when fromnodes.cost +
> paths.cost < tonodes.cost then fromnodes.cost + paths.cost else tonodes.cost
> end, tonodes.pathid = paths.pathid from nodes as fromnodes inner join paths
> on paths.fromnodeid = fromnodes.nodeid inner join tonodes on tonodes.nodeid
> = paths.tonodeid where fromnodes.nodeid = $1 and (tonodes.cost is NULL or
> fromnodes.cost + paths.cost < tonodes.cost) and tonodes.calculated = 0"
> PL/pgSQL function "dijkstra_resolve" line 53 at SQL statement

If we rewrite this to be somewhat readable:

update tonodes set
tonodes.cost = case
when tonodes.cost is NULL
then fromnodes.cost + paths.cost
when fromnodes.cost + paths.cost < tonodes.cost
then fromnodes.cost + paths.cost
else tonodes.cost end,
tonodes.pathid = paths.pathid
from nodes as fromnodes
inner join paths on paths.fromnodeid = fromnodes.nodeid
inner join tonodes on tonodes.nodeid = paths.tonodeid
where fromnodes.nodeid = $1
and (tonodes.cost is NULL or fromnodes.cost + paths.cost < tonodes.cost)
and tonodes.calculated = 0;

You refer to "tonodes" but never actually say that it's an alias for
nodes (I assume, you've not actually said this anywhere).

> The tables referenced (nodes and paths) exist and have data. The SQL works
> in MS SQL Server 2000

The PG manual[1] has this to say:

Some other database systems offer a FROM option in which the target
table is supposed to be listed again within FROM. That is not how
PostgreSQL interprets FROM. Be careful when porting applications that
use this extension.

I'd guess this is what MS SQL does. That said, it's easy to rewrite
your query to use PG syntax. I've also noticed that your CASE statement
is somewhat redundant so I've removed it (it's cases are exactly the
same as the WHERE clause).

UPDATE nodes f SET cost = f.cost + p.cost, pathid = p.pathid
FROM nodes t, paths p
WHERE (p.fromnodeid,p.tonodeid) = (f.nodeid,t.nodeid)
AND (t.cost IS NULL OR f.cost + p.cost < t.cost)
AND t.calculated = 0
AND f.nodeid = $1;

Which, to me, is even more readable. For future reference, the
pgsql-general mailing list[2] is more appropiate for questions like
this.

As a side note, do you have exactly one path from each node to another
node, or do you run this code several times until it converges on the
minimum? In the latter case you'd probably be better off using an
aggregation to find the shortest path in a single pass.

Sam

[1] http://www.postgresql.org/docs/8.3/static/sql-update.html#AEN61013
[2] http://archives.postgresql.org/pgsql-general/