Обсуждение: Ambiguous error message

In PostgreSQL 7.4, the following select:

select texten, total
from (select protocolid, count(*) as total) from ips where catid=1     group by protocolid order by protocolid) as c
innerjoin protocols using (protocolid);
 

gives the error message:

ERROR:  subquery in FROM must have an alias
HINT:  For example, FROM (SELECT ...) [AS] foo.

Why isn't the "as c" considered as an alias?
 Sam
-- 
Samuel Tardieu -- sam@rfc1149.net -- http://www.rfc1149.net/sam

Hi Sam,

I'm not quite sure what you want the query to return, but you've got a 
problem with your parentheses. You've got two FROM clauses and an INNER 
JOIN, which together aren't arranged properly. I've rearranged your 
query a little, but I haven't changed anything. Perhaps this'll make it 
a little clearer to you.

On Jan 2, 2004, at 10:49 AM, Samuel Tardieu wrote:

> In PostgreSQL 7.4, the following select:
>
> select texten, total
> from (select protocolid, count(*) as total)
> from ips
> where catid=1
> group by protocolid order by protocolid) as c
> inner join protocols using (protocolid);
 If you explain a little more what your query is trying to return, I 
might be able to help more.

Michael Glaesemann
grzm myrealbox com

Samuel Tardieu <sam@rfc1149.net> writes:
> In PostgreSQL 7.4, the following select:
> select texten, total
> from (select protocolid, count(*) as total) from ips where catid=1
>       group by protocolid order by protocolid) as c
>   inner join protocols using (protocolid);

> gives the error message:

> ERROR:  subquery in FROM must have an alias
> HINT:  For example, FROM (SELECT ...) [AS] foo.

> Why isn't the "as c" considered as an alias?

It's complaining about this part:

from (select protocolid, count(*) as total) from ips where    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

It hasn't gotten as far as noticing your mismatched parentheses and
two top-level FROM keywords yet ;-).

It would probably be helpful if this message included a cursor location...
        regards, tom lane

On  2/01, Michael Glaesemann wrote:

| I'm not quite sure what you want the query to return, but you've got a 
| problem with your parentheses.

I can't believe this :) I got hit by the "inner query needs to be aliased"
message and added it to the wrong place and them munged the query a lot.

The correct one was of course:
# select texten, total from (select protocolid, count(*) as total from ips where catid=1 group by protocolid order by
protocolid)as c inner join protocols using (protocolid);
 

(PostgreSQL is used for the will-spam-for-food black-list, seehttp://www.rfc1149.net/wsff)
  Sam

On  3/01, Samuel Tardieu wrote:

| On  2/01, Michael Glaesemann wrote:
| 
| | I'm not quite sure what you want the query to return, but you've got a 
| | problem with your parentheses.
| 
| I can't believe this :) I got hit by the "inner query needs to be aliased"
| message and added it to the wrong place and them munged the query a lot.
| 
| The correct one was of course:
| # select texten, total from (select protocolid, count(*) as total from ips
|   where catid=1 group by protocolid order by protocolid) as c
|   inner join protocols using (protocolid);

(with the "order by" part moved at the end of the query to avoid losing the
order during the inner join)
 Sam

On Fri, Jan 02, 2004 at 05:49:46PM +0100, Samuel Tardieu wrote:
> In PostgreSQL 7.4, the following select:
> 
> select texten, total
> from (select protocolid, count(*) as total) from ips where catid=1
>       group by protocolid order by protocolid) as c
>   inner join protocols using (protocolid);
> 
> gives the error message:
> 
> ERROR:  subquery in FROM must have an alias
> HINT:  For example, FROM (SELECT ...) [AS] foo.
> 
> Why isn't the "as c" considered as an alias?

The query has a right parenthesis after "as total" that looks like
it shouldn't be there.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

On Fri, Jan 02, 2004 at 05:49:46PM +0100, Samuel Tardieu wrote:
> 
> select texten, total
> from (select protocolid, count(*) as total) from ips where catid=1                                           ^
                                  +--- There
 
>       group by protocolid order by protocolid) as c
>   inner join protocols using (protocolid);
> 
> gives the error message:
> 
> ERROR:  subquery in FROM must have an alias
> HINT:  For example, FROM (SELECT ...) [AS] foo.
> 
> Why isn't the "as c" considered as an alias?
That's maybe due to the highlighted spurious parenthesis.

-- 
%!PS
297.6 420.9 translate 90 rotate 0 setgray gsave 0 1 1{pop 0 180 moveto 100
180 170 100 170 -10 curveto 180 -9 180 -9 190 -10 curveto 190 100 100 180
0 180 curveto fill 180 rotate}for grestore/Bookman-LightItalic findfont
240 scalefont setfont -151.536392 -63.7998886 moveto (bp)show showpage