pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
question on diagnostics
7+ messages / 3 participants
[nested] [flat]

* question on diagnostics
@ 2000-06-01 03:12 Alexander H. Iliev <iliev@nimbus.dartmouth.edu>
  2000-06-01 04:37 ` Re: question on diagnostics Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 7+ messages in thread

From: Alexander H. Iliev @ 2000-06-01 03:12 UTC (permalink / raw)
  To: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: question on diagnostics
  2000-06-01 03:12 question on diagnostics Alexander H. Iliev <iliev@nimbus.dartmouth.edu>
@ 2000-06-01 04:37 ` Tom Lane <tgl@sss.pgh.pa.us>
  2000-06-01 05:07   ` Re: question on diagnostics Alexander H. Iliev <iliev@nimbus.dartmouth.edu>
  0 siblings, 1 reply; 7+ messages in thread

From: Tom Lane @ 2000-06-01 04:37 UTC (permalink / raw)
  To: Alexander H. Iliev <iliev@nimbus.dartmouth.edu>; +Cc: pgsql-sql

"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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: question on diagnostics
  2000-06-01 03:12 question on diagnostics Alexander H. Iliev <iliev@nimbus.dartmouth.edu>
  2000-06-01 04:37 ` Re: question on diagnostics Tom Lane <tgl@sss.pgh.pa.us>
@ 2000-06-01 05:07   ` Alexander H. Iliev <iliev@nimbus.dartmouth.edu>
  2000-06-01 05:16     ` Re: question on diagnostics Tom Lane <tgl@sss.pgh.pa.us>
  2000-06-13 08:01     ` Re: question on diagnostics Bruce Momjian <pgman@candle.pha.pa.us>
  2000-10-02 04:17     ` Re: question on diagnostics Bruce Momjian <pgman@candle.pha.pa.us>
  0 siblings, 3 replies; 7+ messages in thread

From: Alexander H. Iliev @ 2000-06-01 05:07 UTC (permalink / raw)
  To: tgl@sss.pgh.pa.us; +Cc: pgsql-sql

> > 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




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: question on diagnostics
  2000-06-01 03:12 question on diagnostics Alexander H. Iliev <iliev@nimbus.dartmouth.edu>
  2000-06-01 04:37 ` Re: question on diagnostics Tom Lane <tgl@sss.pgh.pa.us>
  2000-06-01 05:07   ` Re: question on diagnostics Alexander H. Iliev <iliev@nimbus.dartmouth.edu>
@ 2000-06-01 05:16     ` Tom Lane <tgl@sss.pgh.pa.us>
  2 siblings, 0 replies; 7+ messages in thread

From: Tom Lane @ 2000-06-01 05:16 UTC (permalink / raw)
  To: Alexander H. Iliev <iliev@nimbus.dartmouth.edu>; +Cc: pgsql-sql

"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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: question on diagnostics
  2000-06-01 03:12 question on diagnostics Alexander H. Iliev <iliev@nimbus.dartmouth.edu>
  2000-06-01 04:37 ` Re: question on diagnostics Tom Lane <tgl@sss.pgh.pa.us>
  2000-06-01 05:07   ` Re: question on diagnostics Alexander H. Iliev <iliev@nimbus.dartmouth.edu>
@ 2000-06-13 08:01     ` Bruce Momjian <pgman@candle.pha.pa.us>
  2000-06-13 08:05       ` Re: question on diagnostics Tom Lane <tgl@sss.pgh.pa.us>
  2 siblings, 1 reply; 7+ messages in thread

From: Bruce Momjian @ 2000-06-13 08:01 UTC (permalink / raw)
  To: Alexander H. Iliev <iliev@nimbus.dartmouth.edu>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql

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, Pennsylvania 19026



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: question on diagnostics
  2000-06-01 03:12 question on diagnostics Alexander H. Iliev <iliev@nimbus.dartmouth.edu>
  2000-06-01 04:37 ` Re: question on diagnostics Tom Lane <tgl@sss.pgh.pa.us>
  2000-06-01 05:07   ` Re: question on diagnostics Alexander H. Iliev <iliev@nimbus.dartmouth.edu>
  2000-06-13 08:01     ` Re: question on diagnostics Bruce Momjian <pgman@candle.pha.pa.us>
@ 2000-06-13 08:05       ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 7+ messages in thread

From: Tom Lane @ 2000-06-13 08:05 UTC (permalink / raw)
  To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Alexander H. Iliev <iliev@nimbus.dartmouth.edu>; pgsql-sql

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.



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: question on diagnostics
  2000-06-01 03:12 question on diagnostics Alexander H. Iliev <iliev@nimbus.dartmouth.edu>
  2000-06-01 04:37 ` Re: question on diagnostics Tom Lane <tgl@sss.pgh.pa.us>
  2000-06-01 05:07   ` Re: question on diagnostics Alexander H. Iliev <iliev@nimbus.dartmouth.edu>
@ 2000-10-02 04:17     ` Bruce Momjian <pgman@candle.pha.pa.us>
  2 siblings, 0 replies; 7+ messages in thread

From: Bruce Momjian @ 2000-10-02 04:17 UTC (permalink / raw)
  To: Alexander H. Iliev <iliev@nimbus.dartmouth.edu>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql

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, Pennsylvania 19026



^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2000-10-02 04:17 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-06-01 03:12 question on diagnostics Alexander H. Iliev <iliev@nimbus.dartmouth.edu>
2000-06-01 04:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-06-01 05:07   ` Alexander H. Iliev <iliev@nimbus.dartmouth.edu>
2000-06-01 05:16     ` Tom Lane <tgl@sss.pgh.pa.us>
2000-06-13 08:01     ` Bruce Momjian <pgman@candle.pha.pa.us>
2000-06-13 08:05       ` Tom Lane <tgl@sss.pgh.pa.us>
2000-10-02 04:17     ` Bruce Momjian <pgman@candle.pha.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox