Обсуждение: question on diagnostics
Hi all,
does anyone have a clue what this diagnostic from psql (v 7.0) means:
ERROR: aggregate function in qual must be argument of boolean operator
I got it from this query:
SELECT *
FROM last_payment NATURAL INNER JOIN admin_info
WHERE date_part ('month', age(last_payment.date, timestamp 'now')) >= 3 AND balance > 0;
last_payment is a view of a natural join of 3 tables with an aggregation:
CREATE VIEW last_payment(ssn, cust_name, cust_addr, balance, date) AS
SELECT customer.ssn, cust_name, cust_addr, balance, MAX(date)
FROM customer, journal, payment_info
WHERE customer.ssn = journal.ssn AND journal.entry_num = payment_info.entry_num
GROUP BY customer.ssn, cust_name, cust_addr, balance;
oh, btw this select refused to use an SQL natural join among the 3 relations -
the server gives up and disconnects without warning.
admin_info is a normal table.
the idea being to get tuples timestamped 3 or more months ago. I get the
impression both 'age' and 'date_part' functions are causing the problem - just a
date comparison without them works fine.
thanks for any ideas
cheers
alex
"Alexander H. Iliev" <iliev@nimbus.dartmouth.edu> writes:
> does anyone have a clue what this diagnostic from psql (v 7.0) means:
> ERROR: aggregate function in qual must be argument of boolean operator
Aggregates and GROUP BY in views have a lot of restrictions at the
moment, and this is one of them: the rewriter has a problem with
figuring out how to substitute "MAX(date)" for "last_payment.date"
in the context of your age() function call.
We're planning a thorough rewrite of the view-supporting code for 7.2,
and most of these problems should go away at that point. In the short
run you would be well advised to avoid grouped views except in the
simplest contexts. I think this example could be rewritten with no
view, or you could push the "date_part(...)" call into the view as
another column.
> oh, btw this select refused to use an SQL natural join among the 3
> relations - the server gives up and disconnects without warning.
That sounds like a garden-variety bug. I'd be willing to look at it
if I had a complete example to follow, but I don't want to try to
reverse-engineer your table definitions...
regards, tom lane
> > oh, btw this select refused to use an SQL natural join among the 3 > > relations - the server gives up and disconnects without warning. > > That sounds like a garden-variety bug. I'd be willing to look at it > if I had a complete example to follow, but I don't want to try to > reverse-engineer your table definitions... a join with 3 tables never seems to work: test=# create table a(a int); CREATE test=# create table b(a int); CREATE test=# create table c(a int); CREATE test=# insert into a values (1); INSERT 23734 1 test=# insert into b values (1); INSERT 23744 1 test=# insert into c values (1); INSERT 23736 1 test=# select * test-# from a, b, c test-# where a.a = b.a AND test-# b.a = c.a;a | a | a ---+---+---1 | 1 | 1 (1 row) test=# select * from a natural inner join b natural inner join c; pqReadData() -- backend closed the channel unexpectedly. This probably means the backend terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. alex
"Alexander H. Iliev" <iliev@nimbus.dartmouth.edu> writes:
> test=# select * from a natural inner join b natural inner join c;
> pqReadData() -- backend closed the channel unexpectedly.
Yup, I see it too. I had fixed some problems in SQL join syntax
since 7.0 release, but evidently not this one :-(. Will look
into it. Thanks for the example.
regards, tom lane
I can confirm this is still a bug. > > > oh, btw this select refused to use an SQL natural join among the 3 > > > relations - the server gives up and disconnects without warning. > > > > That sounds like a garden-variety bug. I'd be willing to look at it > > if I had a complete example to follow, but I don't want to try to > > reverse-engineer your table definitions... > > a join with 3 tables never seems to work: > > test=# create table a(a int); > CREATE > test=# create table b(a int); > CREATE > test=# create table c(a int); > CREATE > test=# insert into a values (1); > INSERT 23734 1 > test=# insert into b values (1); > INSERT 23744 1 > test=# insert into c values (1); > INSERT 23736 1 > test=# select * > test-# from a, b, c > test-# where a.a = b.a AND > test-# b.a = c.a; > a | a | a > ---+---+--- > 1 | 1 | 1 > (1 row) > test=# select * from a natural inner join b natural inner join c; > pqReadData() -- backend closed the channel unexpectedly. > This probably means the backend terminated abnormally > before or while processing the request. > The connection to the server was lost. Attempting reset: Failed. > > > alex > > -- Bruce Momjian | http://www.op.net/~candle pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I can confirm this is still a bug.
Yeah, it's a parser problem. I punted it in Lockhart's direction, but
he's been out of town...
regards, tom lane
>>>>>> oh, btw this select refused to use an SQL natural join among the 3
>>>>>> relations - the server gives up and disconnects without warning.
>>>>
>>>> That sounds like a garden-variety bug. I'd be willing to look at it
>>>> if I had a complete example to follow, but I don't want to try to
>>>> reverse-engineer your table definitions...
>>
>> a join with 3 tables never seems to work:
>>
>> test=# create table a(a int);
>> CREATE
>> test=# create table b(a int);
>> CREATE
>> test=# create table c(a int);
>> CREATE
>> test=# insert into a values (1);
>> INSERT 23734 1
>> test=# insert into b values (1);
>> INSERT 23744 1
>> test=# insert into c values (1);
>> INSERT 23736 1
>> test=# select *
>> test-# from a, b, c
>> test-# where a.a = b.a AND
>> test-# b.a = c.a;
>> a | a | a
>> ---+---+---
>> 1 | 1 | 1
>> (1 row)
>> test=# select * from a natural inner join b natural inner join c;
>> pqReadData() -- backend closed the channel unexpectedly.
>> This probably means the backend terminated abnormally
>> before or while processing the request.
>> The connection to the server was lost. Attempting reset: Failed.
I can confirm that this is now fixed, and will appear in 7.1 when it is released. test=> select * from a natural inner join b natural inner join c;a ---1 (1 row) > > > oh, btw this select refused to use an SQL natural join among the 3 > > > relations - the server gives up and disconnects without warning. > > > > That sounds like a garden-variety bug. I'd be willing to look at it > > if I had a complete example to follow, but I don't want to try to > > reverse-engineer your table definitions... > > a join with 3 tables never seems to work: > > test=# create table a(a int); > CREATE > test=# create table b(a int); > CREATE > test=# create table c(a int); > CREATE > test=# insert into a values (1); > INSERT 23734 1 > test=# insert into b values (1); > INSERT 23744 1 > test=# insert into c values (1); > INSERT 23736 1 > test=# select * > test-# from a, b, c > test-# where a.a = b.a AND > test-# b.a = c.a; > a | a | a > ---+---+--- > 1 | 1 | 1 > (1 row) > test=# select * from a natural inner join b natural inner join c; > pqReadData() -- backend closed the channel unexpectedly. > This probably means the backend terminated abnormally > before or while processing the request. > The connection to the server was lost. Attempting reset: Failed. > > > alex > > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026