Thread: confusing error message
I'm having trouble parsing this: ERROR: aggregate functions are not allowed in FROM clause of their own query level The example in the regression tests is: -- LATERAL can be used to put an aggregate into the FROM clause of its query select 1 from tenk1 a, lateral (select max(a.unique1) from int4_tbl b) ss; ERROR: aggregate functions are not allowed in FROM clause of their own query level LINE 1: select 1 from tenk1 a, lateral (select max(a.unique1) from i... ^ I think the "own query level" of the max aggregate function in this case is the subquery "ss", and so it's not in the FROM clause of its own query level. It's understandable why this is not allowed, but I don't think the error message explains it. Could we come up with a better wording?
Peter Eisentraut <peter_e@gmx.net> writes:
> I'm having trouble parsing this:
> ERROR: aggregate functions are not allowed in FROM clause of their own query level
> The example in the regression tests is:
> -- LATERAL can be used to put an aggregate into the FROM clause of its query
> select 1 from tenk1 a, lateral (select max(a.unique1) from int4_tbl b) ss;
> ERROR: aggregate functions are not allowed in FROM clause of their own query level
> LINE 1: select 1 from tenk1 a, lateral (select max(a.unique1) from i...
> ^
> I think the "own query level" of the max aggregate function in this case
> is the subquery "ss", and so it's not in the FROM clause of its own
> query level.
No. The max() aggregate function is on a column of table "a", so it
belongs to the outer query level, and it is within the FROM clause of
that level. (Don't blame me, this is per SQL spec ...)
> It's understandable why this is not allowed, but I don't think the error
> message explains it. Could we come up with a better wording?
I'm not sure either. The main point here is that an aggregate's semantic
level is normally the natural level of its argument expression, not the
textual location of the call. The "natural level" is the level of the
lowest-level variable used in the argument. But what if the argument is
variable-free? Then you *do* take the textual location as the semantic
level. This is all sufficiently bizarre that I don't know if there's an
easy explanation.
regards, tom lane
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> This is all sufficiently bizarre that I don't know if there's an
> easy explanation.
It occurs to me that users, when faced with complex error messages, are
very likely to go to their favorite search engine with it and rarely
does that lead them to any documentation on the subject but instead they
get mailing list archives or q-and-a type of websites.
Perhaps we should add an area to our documentation which provides more
information about the specific error messages which PostgreSQL returns?
That's not a terribly exciting bit of documentation to write, but
perhaps it would be very useful for our users.
Thoughts?
Thanks,
Stephen
On 08/10/2013 12:09 AM, Stephen Frost wrote: > Perhaps we should add an area to our documentation which provides > more information about the specific error messages which PostgreSQL > returns? That's not a terribly exciting bit of documentation to > write, but perhaps it would be very useful for our users. PG00204 Error reading control file More seriously, with interpolated strings for relation names etc it can be hard to know which chunks to search for, and search engines aren't always good at having the whole message thrown at them. I'm not actually proposing in-text message identifiers ... I'm sure enough people have ignored all sanity and reason and parsed message strings that this would cause breakage all over the place. It'd also be a nightmare for translators and would easily be confused for an SQLSTATE. A separate field in the structured messages would be saner, but of course most clients would not then display it so the user would never know it was there, much the same way few people seem to realise you can get the function, source file and line from error messages already. After all, how many people do you think run with VERBOSITY=verbose in psql? -- Craig Ringer http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
I seem to remember somebody proposed an errurl() macro so that we could add URLs to certain particularly confusing error reports. [searches the archives] Bah, that was me, and some other ideas were proposed: http://www.postgresql.org/message-id/48CA9D5F.6060304@esilo.com -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
* Craig Ringer (craig@2ndquadrant.com) wrote:
> More seriously, with interpolated strings for relation names etc it can
> be hard to know which chunks to search for, and search engines aren't
> always good at having the whole message thrown at them.
It's not perfect, but if the searches are getting to mailing list
archive messages then it's clearly possible for us to do better. Having
an embedded code or URL or what-have-you might be an option too, but we
need the documentation first in any case, so we could do these other
things later..
Thanks,
Stephen
On 08/10/2013 02:43 AM, Stephen Frost wrote: > * Craig Ringer (craig@2ndquadrant.com) wrote: >> More seriously, with interpolated strings for relation names etc >> it can be hard to know which chunks to search for, and search >> engines aren't always good at having the whole message thrown at >> them. > > It's not perfect, but if the searches are getting to mailing list > archive messages then it's clearly possible for us to do better. > Having an embedded code or URL or what-have-you might be an option > too, but we need the documentation first in any case, so we could > do these other things later.. Well said; you're quite right. I complain myself that a focus on perfection can prevent progress. Lets start with "helpful". My first two would be: "No pg_hba.conf entry for ..." and "fe_sendauth: no password supplied" as both seem to confuse new users endlessly. -- Craig Ringer http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services