... yeah, me neither.

Hi all, I have an interesting one for you today. I'm writing a new \dd
command (one that actually works), and I have come across the following
situation:

SELECT DISTINCT a.aggname as "Name" FROM pg_aggregate a
  UNION ALL
SELECT DISTINCT p.proname as "Name" FROM pg_proc p
  UNION ALL
SELECT DISTINCT o.oprname as "Name" FROM pg_operator o
  UNION ALL
SELECT DISTINCT t.typname as "Name" FROM pg_type t
  UNION ALL
SELECT DISTINCT c.relname as "Name" FROM pg_class c
;

(It doesn't make much sense as it stands, but I have picked out the
offending parts.)

I get
NOTICE:  equal: don't know whether nodes of type 719 are equal

Actually, I get several of these. Depending on the number of select
clauses, I get 1 for the third, 2 for the 4th, 3 for the 5th, etc. So the
above query gives me 6 notices. A query with only two select clauses gives
me none.

Without the DISTINCTs everything goes fine.

Now this seems to have something to do with a lack of an equal operator
for the type "name", right? Interestingly enough, the type name has oid
19, whereas type 719 is "_circle", or what does the 719 refer to?

Thanks,
	Peter

-- 
Peter Eisentraut                  Sernanders vaeg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden



Sun, 17 Oct 1999 21:44:32 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
don't know whether nodes of type 719 are equal
I think someone changed the database schema.  Try cvs update then
initdb.  Could it be that the row of type circle is causing it?


I don't get that here, and 719 is certainly a strange number to be
getting

> ... yeah, me neither.
> 
> Hi all, I have an interesting one for you today. I'm writing a new \dd
> command (one that actually works), and I have come across the following
> situation:
> 
> SELECT DISTINCT a.aggname as "Name" FROM pg_aggregate a
>   UNION ALL
> SELECT DISTINCT p.proname as "Name" FROM pg_proc p
>   UNION ALL
> SELECT DISTINCT o.oprname as "Name" FROM pg_operator o
>   UNION ALL
> SELECT DISTINCT t.typname as "Name" FROM pg_type t
>   UNION ALL
> SELECT DISTINCT c.relname as "Name" FROM pg_class c
> ;
> 
> (It doesn't make much sense as it stands, but I have picked out the
> offending parts.)
> 
> I get
> NOTICE:  equal: don't know whether nodes of type 719 are equal
> 
> Actually, I get several of these. Depending on the number of select
> clauses, I get 1 for the third, 2 for the 4th, 3 for the 5th, etc. So the
> above query gives me 6 notices. A query with only two select clauses gives
> me none.
> 
> Without the DISTINCTs everything goes fine.
> 
> Now this seems to have something to do with a lack of an equal operator
> for the type "name", right? Interestingly enough, the type name has oid
> 19, whereas type 719 is "_circle", or what does the 719 refer to?
> 
> Thanks,
> 	Peter
> 
> -- 
> Peter Eisentraut                  Sernanders vaeg 10:115
> peter_e@gmx.net                   75262 Uppsala
> http://yi.org/peter-e/            Sweden
> 
> 
> 
> ************
> 


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@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

Sun, 17 Oct 1999 15:57:52 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] don't know whether nodes of type 719 are equal
I also got this message with UNION  and distinct. 
I've tested 6.5.2 and 6.5.3. Current (6.6 or 7.0 ?) works fine

select  distinct a.msg_id, c.status_set_date, c.title
             from Message_Keyword_map a, messages c, keywords d
             where c.status_id =1 and d.name ~* 'sun'  and a.key_id=d.key_id
             and c.msg_id=a.msg_id
         union
         select distinct  a.msg_id, a.status_set_date, a.title
             from messages a where a.status_id = 1 and a.title ~* 'sun';


NOTICE:  equal: don't know whether nodes of type 719 are equal

	Oleg
This  is with postgres 6.5.3
On Sun, 17 Oct 1999, Bruce Momjian wrote:

> Date: Sun, 17 Oct 1999 15:57:52 -0400 (EDT)
> From: Bruce Momjian <maillist@candle.pha.pa.us>
> To: Peter Eisentraut <peter_e@gmx.net>
> Cc: pgsql-hackers@postgreSQL.org
> Subject: Re: [HACKERS] don't know whether nodes of type 719 are equal
> 
> I think someone changed the database schema.  Try cvs update then
> initdb.  Could it be that the row of type circle is causing it?
> 
> 
> I don't get that here, and 719 is certainly a strange number to be
> getting
> 
> > ... yeah, me neither.
> > 
> > Hi all, I have an interesting one for you today. I'm writing a new \dd
> > command (one that actually works), and I have come across the following
> > situation:
> > 
> > SELECT DISTINCT a.aggname as "Name" FROM pg_aggregate a
> >   UNION ALL
> > SELECT DISTINCT p.proname as "Name" FROM pg_proc p
> >   UNION ALL
> > SELECT DISTINCT o.oprname as "Name" FROM pg_operator o
> >   UNION ALL
> > SELECT DISTINCT t.typname as "Name" FROM pg_type t
> >   UNION ALL
> > SELECT DISTINCT c.relname as "Name" FROM pg_class c
> > ;
> > 
> > (It doesn't make much sense as it stands, but I have picked out the
> > offending parts.)
> > 
> > I get
> > NOTICE:  equal: don't know whether nodes of type 719 are equal
> > 
> > Actually, I get several of these. Depending on the number of select
> > clauses, I get 1 for the third, 2 for the 4th, 3 for the 5th, etc. So the
> > above query gives me 6 notices. A query with only two select clauses gives
> > me none.
> > 
> > Without the DISTINCTs everything goes fine.
> > 
> > Now this seems to have something to do with a lack of an equal operator
> > for the type "name", right? Interestingly enough, the type name has oid
> > 19, whereas type 719 is "_circle", or what does the 719 refer to?
> > 
> > Thanks,
> > 	Peter
> > 
> > -- 
> > Peter Eisentraut                  Sernanders vaeg 10:115
> > peter_e@gmx.net                   75262 Uppsala
> > http://yi.org/peter-e/            Sweden
> > 
> > 
> > 
> > ************
> > 
> 
> 
> -- 
>   Bruce Momjian                        |  http://www.op.net/~candle
>   maillist@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
> 
> ************
> 

_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83


Mon, 18 Oct 1999 00:21:48 +0400 (MSD)
Oleg Bartunov <oleg@sai.msu.su>
Re: [HACKERS] don't know whether nodes of type 719 are equal
Peter Eisentraut <peter_e@gmx.net> writes:
> SELECT DISTINCT t.typname as "Name" FROM pg_type t
>   UNION ALL
> SELECT DISTINCT c.relname as "Name" FROM pg_class c
> ;
> (It doesn't make much sense as it stands, but I have picked out the
> offending parts.)

> I get
> NOTICE:  equal: don't know whether nodes of type 719 are equal

(consults include/nodes/nodes.h ... hmm, "SortClause" ...)

This is probably happening because UNION/INTERSECT processing tries
to simplify the node tree using cnfify(), which is really designed
to work on expressions not whole queries.  Ordinarily you can't get a
sort clause into a subclause of a UNION ... but I guess with DISTINCT
you can.  (I bet UNIONing things containing GROUP BY fails too,
since equal() doesn't know about GroupClause nodes either.)

A quick-fix answer is to extend equal(), of course, but I've been
wondering for a while why we are cnfify'ing UNION/INTERSECT trees
at all.  The odds of being able to simplify the tree that way seem
small, and what's worse is that UNION does *not* have the same
semantics as OR (eg, foo UNION foo should *not* be simplified to foo)
but cnfify doesn't know that.

			regards, tom lane

Sun, 17 Oct 1999 17:47:11 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] don't know whether nodes of type 719 are equal 
> (consults include/nodes/nodes.h ... hmm, "SortClause" ...)
> 
> This is probably happening because UNION/INTERSECT processing tries
> to simplify the node tree using cnfify(), which is really designed
> to work on expressions not whole queries.  Ordinarily you can't get a
> sort clause into a subclause of a UNION ... but I guess with DISTINCT
> you can.  (I bet UNIONing things containing GROUP BY fails too,
> since equal() doesn't know about GroupClause nodes either.)
> 
> A quick-fix answer is to extend equal(), of course, but I've been
> wondering for a while why we are cnfify'ing UNION/INTERSECT trees
> at all.  The odds of being able to simplify the tree that way seem
> small, and what's worse is that UNION does *not* have the same
> semantics as OR (eg, foo UNION foo should *not* be simplified to foo)
> but cnfify doesn't know that.

My recollection is that cnfify is not called to simplify, but was
required at one point so you got the right output.  That may no longer
be the case, but I know it was at some point.  Before installed kqso,
the author tried to just skip cnfify, and the query with OR's didn't
work.  Of course, none of us understood cnfify(), so just scratched our
heads.


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@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

Sun, 17 Oct 1999 20:49:53 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] don't know whether nodes of type 719 are equal
> I also got this message with UNION  and distinct. 
> I've tested 6.5.2 and 6.5.3. Current (6.6 or 7.0 ?) works fine

Me too. Current works fine, but 6.5.2 not.
---
Tatsuo Ishii

Mon, 18 Oct 1999 10:22:27 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
Re: [HACKERS] don't know whether nodes of type 719 are equal 
> > I also got this message with UNION  and distinct. 
> > I've tested 6.5.2 and 6.5.3. Current (6.6 or 7.0 ?) works fine
> 
> Me too. Current works fine, but 6.5.2 not.

Better than 6.5.* working and current failing.  :-)

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@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

Sun, 17 Oct 1999 22:03:36 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] don't know whether nodes of type 719 are equal
Bruce Momjian <maillist@candle.pha.pa.us> writes:
> My recollection is that cnfify is not called to simplify, but was
> required at one point so you got the right output.  That may no longer
> be the case, but I know it was at some point.

For ordinary qual expressions, the only thing cnfify does that is
actually *necessary* for downstream processing is that it changes
the top-level boolean condition into an implicitly-ANDed list of
clauses.  That is, (AND A B ...) becomes (A B ...), anything else
becomes a singleton list ((X)).  So you could replace cnfify with
make_ands_implicit() and things would still work.  (I believe
Peter Andrews is presently getting useful work done with cnfify
lobotomized in more or less that fashion --- he's using queries
that expand unpleasantly with normal cnfify.)

I am not sure whether this is true for UNION/INTERSECT processing
though.  There are some really ugly kluges in UNION/INTERSECT, and
I don't think I understand all of its dependencies.

			regards, tom lane

Mon, 18 Oct 1999 00:16:12 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] don't know whether nodes of type 719 are equal 
> I am not sure whether this is true for UNION/INTERSECT processing
> though.  There are some really ugly kluges in UNION/INTERSECT, and
> I don't think I understand all of its dependencies.

Yes, that code was not our finest hour.  :-)

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@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

Mon, 18 Oct 1999 00:25:19 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] don't know whether nodes of type 719 are equal
Tatsuo Ishii <t-ishii@sra.co.jp> writes:
>> I also got this message with UNION  and distinct. 
>> I've tested 6.5.2 and 6.5.3. Current (6.6 or 7.0 ?) works fine

> Me too. Current works fine, but 6.5.2 not.

No, it's still there in current:

regression=> explain select distinct * from tenk1
regression-> union select distinct * from tenk1;
NOTICE:  equal: don't know whether nodes of type 719 are equal
NOTICE:  QUERY PLAN:
... etc ...

It might be a little harder to get in current.  I think that in
a fit of code beautification I rearranged _equalQuery so that the
sort/group clauses are tested later than they used to be.  You
won't see this notice if _equalQuery discovers that the query
nodes are non-identical before it gets to the sort specification.
Thus:

regression=> explain select distinct * from tenk1 t1      
regression-> union select distinct * from tenk1 t2; 
NOTICE:  QUERY PLAN:
... etc ...

This entirely equivalent query has different refnames in the rangetables
of the two subselects, which means equal() considers the nodes
non-identical; and the rangetable is checked by equalQuery before it
gets to the sort clause.  So the sort clauses are never compared.
Bingo, no message.

Being harder to get doesn't make it any less a bug, of course.
But I'm not especially concerned about it --- the query works,
the message is just noise; so I think we can live with it until
we get around to doing the major querytree redesign that we need
to do for subselects in FROM as well as some less pressing problems
like this one...

			regards, tom lane

Mon, 18 Oct 1999 01:10:56 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] don't know whether nodes of type 719 are equal 