BUG #15658: Window Function in a left join using AS or alias for the cloumn name
The following bug has been logged on the website:
Bug reference: 15658
Logged by: Justin G
Email address: zzzzz.graf@gmail.com
PostgreSQL version: 11.2
Operating system: Debian 9
Description:
Hello postgresql developers
I believe I found an obscure bug with the window function
Postgresql version 11.2
OS Debian 9
PG Admin 4.1
Have 2 basic tables with a Parent child relationship, a One to Many
relationship. The child table also makes reference to a 3rd table which can
have a many to many relationship. This query returns count(*) of the 3rd
table’s keys in the child table grouped by Parent Table ID.
The base SQL statement works:
select calprorules_id cid , count(*) over
(PARTITION BY calprorules_calprorange_id, calprorules_calprohd_id ) as
howmany
from mcal.calprorules
The error occurs when I put the query into a LEFT JOIN:
select counts.count,
caldetail.*, calprorules_desired_value, calprorules_stdpreceision,
calprorules_mutpreceision ,
calprorange_description, calprorange_id, calprorange_from, calprorange_to
from mcal.caldetail
left join mcal.calprorules on calprorules_id = caldetail_calprorules_id
left join (select calprorules_id as cid , count(*) over
(PARTITION BY calprorules_calprorange_id, calprorules_calprohd_id ) as
howmany
from mcal.calprorules) counts
on counts.cid = caldetail_calprorules_id
left join mcal.calprorange on calprorange_id = calprorules_calprorange_id
where caldetail_calhead_id = 179
order by calprorange_description, caldetails_seqence
Postgresql returns
ERROR: column "caldetail.caldetail_id" must appear in the GROUP BY clause or
be used in an aggregate function LINE 2: caldetail.*,
calprorules_desired_value, calprorules_stdprec...
SQL state: 42803 Character: 24
Delete the AS howmany; it works.
I realized after writing this that I should have written the SQL like this,
deleting the LEFT JOIN and moving the window function into the select
statement:
select caldetail.*, calprorules_desired_value, calprorules_stdpreceision,
calprorules_mutpreceision ,
calprorange_description, calprorange_id, calprorange_from, calprorange_to
,
count(*) over (PARTITION BY calprorules_calprorange_id,
calprorules_calprohd_id )
from mcal.caldetail
left join mcal.calprorules on calprorules_id =
caldetail_calprorules_id
left join mcal.calprorange on calprorange_id =
calprorules_calprorange_id
where caldetail_calhead_id = 179
order by calprorange_description, caldetails_seqence
Clearly the second SQL statement is better, but i do not believe the window
function should error when put into a join
PG Bug reporting form <noreply@postgresql.org> writes:
I believe I found an obscure bug with the window function
Hm, could we see a self-contained example, ie with the underlying
table/view definitions too? Probably don't need any sample data
for this type of problem, but I'm unexcited about trying to reverse-
engineer your table definitions enough to reproduce the problem.
The error occurs when I put the query into a LEFT JOIN:
select counts.count,
caldetail.*, calprorules_desired_value, calprorules_stdpreceision,
I'm wondering why this didn't already fail at "counts.count", because with
the "AS howmany" in place, that sub-select isn't returning any column
named "count". I kind of suspect user error due to ambiguous column
names, but the details aren't obvious.
regards, tom lane
Hello Tom
attached is the SQL file for the 4 tables. if you need data not a problem
the data in the table is junk/test entries
also attached is the screen shot of the error in pgadmin then the same
command run without the AS
[image: image.png]
[image: image.png]
On Tue, Feb 26, 2019 at 5:56 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Show quoted text
PG Bug reporting form <noreply@postgresql.org> writes:
I believe I found an obscure bug with the window function
Hm, could we see a self-contained example, ie with the underlying
table/view definitions too? Probably don't need any sample data
for this type of problem, but I'm unexcited about trying to reverse-
engineer your table definitions enough to reproduce the problem.The error occurs when I put the query into a LEFT JOIN:
select counts.count,
caldetail.*, calprorules_desired_value, calprorules_stdpreceision,I'm wondering why this didn't already fail at "counts.count", because with
the "AS howmany" in place, that sub-select isn't returning any column
named "count". I kind of suspect user error due to ambiguous column
names, but the details aren't obvious.regards, tom lane
"Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:
The error occurs when I put the query into a LEFT JOIN:
select counts.count,
caldetail.*, calprorules_desired_value, calprorules_stdpreceision,
Tom> I'm wondering why this didn't already fail at "counts.count",
Because counts.count resolves as count(counts), obviously. That makes
the query an aggregation query with implied GROUP BY (), hence the error.
Justin: the problem is nothing to do with the join, but it _is_ to do
with the AS alias. For historical compatibility reasons, PostgreSQL
tries to treat x.y and y(x) as though they were somewhat equivalent; so
you can do (under some conditions) x.function or columnname(table).
Needless to say actually _using_ this facility is a very bad idea.
So in this example, if you have a column called "count", then
counts.count resolves to that column. But if there's no column called
"count", then counts.count is resolved as count(counts) (which works
because count() is one of the few functions that can take any parameter
type), and since count() is an aggregate function, that forces the query
to behave as if there were an implied GROUP BY (), just as doing
something like select count(*) from table; does.
So this is not a bug, just a historical landmine.
--
Andrew (irc:RhodiumToad)
Hi Andrew
I think I understand what your stating. I realized after re-reading Tom's
responds and playing with the SQL statement i see my mistake.
I just did not catch it when i was writing the SQL statement.
I figured out a work around pretty quickly and later realized i was being
an idiot putting the window function in a JOIN for this query
Then i wrote the bug report.. Thank you
On Tue, Feb 26, 2019 at 8:56 PM Andrew Gierth <andrew@tao11.riddles.org.uk>
wrote:
Show quoted text
"Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:
The error occurs when I put the query into a LEFT JOIN:
select counts.count,
caldetail.*, calprorules_desired_value, calprorules_stdpreceision,Tom> I'm wondering why this didn't already fail at "counts.count",
Because counts.count resolves as count(counts), obviously. That makes
the query an aggregation query with implied GROUP BY (), hence the error.Justin: the problem is nothing to do with the join, but it _is_ to do
with the AS alias. For historical compatibility reasons, PostgreSQL
tries to treat x.y and y(x) as though they were somewhat equivalent; so
you can do (under some conditions) x.function or columnname(table).
Needless to say actually _using_ this facility is a very bad idea.So in this example, if you have a column called "count", then
counts.count resolves to that column. But if there's no column called
"count", then counts.count is resolved as count(counts) (which works
because count() is one of the few functions that can take any parameter
type), and since count() is an aggregate function, that forces the query
to behave as if there were an implied GROUP BY (), just as doing
something like select count(*) from table; does.So this is not a bug, just a historical landmine.
--
Andrew (irc:RhodiumToad)