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

Поиск
Список
Период
Сортировка
От Sam Mason
Тема Re: BUG #4035: sql table aliases do not work
Дата
Msg-id 20080315140339.GA1653@frubble.xen.chris-lamb.co.uk
обсуждение исходный текст
Ответ на BUG #4035: sql table aliases do not work  ("RGF" <RussFrith@hotmail.com>)
Список pgsql-bugs
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/

В списке pgsql-bugs по дате отправления:

Предыдущее
От: "Alexey"
Дата:
Сообщение: BUG #4038: Problem with locale changing by initdb
Следующее
От: "Daniel Cristian Cruz"
Дата:
Сообщение: Re: BUG #4037: Manual bug: 2.5. Querying a Table