Thread

1. coalesce view error

   mikeo <mikeo@spectrumtelecorp.com> — 2000-06-15T20:48:37Z

   hi, i have this view defined as:
   
   create view mikeotest as
          select
   coalesce(topflow_application.rpt_name,topflow_application.tfap_name) AS
   ipd_desc,
          graphics_03.ipd_date,
          graphics_03.day,
          graphics_03.gr_bill_amt_total,
          graphics_03.gr_byte_qty_total,
          graphics_03.gr_ipd_sessions
   FROM graphics_03, topflow_application
   WHERE (graphics_03.ct_key = topflow_application.ib_ct_key
      or  graphics_03.ct_key = topflow_application.ob_ct_key);
   
   which postgres generates this from:
   
   SELECT CASE WHEN (topflow_application.rpt_name NOTNULL) 
               THEN topflow_application.rpt_name 
               WHEN (topflow_application.tfap_name NOTNULL) 
               THEN topflow_application.tfap_name 
               ELSE NULL::unknown 
          END AS ipd_desc, 
          graphics_03.ipd_date, graphics_03."day",
   graphics_03.gr_bill_amt_total, 
          graphics_03.gr_byte_qty_total, graphics_03.gr_ipd_sessions 
     FROM graphics_03, topflow_application 
    WHERE ((graphics_03.ct_key = topflow_application.ib_ct_key) 
       OR (graphics_03.ct_key = topflow_application.ob_ct_key));
   
   when i try to run this query against this view, i get the following:
   
   select sum(gr_bill_amt_total), ipd_desc from mikeotest group by ipd_desc;
   ERROR:  Unable to identify an equality operator for type 'unknown'
   
   
   i've played with the postgres definition changing NULL::unknown to 'stuff'
   and other variations without success.  any help would be appreciated.
   
   the underlying table looks like this and has 2200+/- rows in it:
   
               Table "graphics_03"
        Attribute     |    Type     | Modifier
   -------------------+-------------+----------
    ct_key            | float8      | not null
    ipd_date          | date        | not null
    day               | varchar(10) | not null
    ipd_sessions      | float8      |
    ipd_bill_amt      | float8      |
    ipd_byte_qty      | float8      |
    gr_ipd_sessions   | float8      |
    gr_bill_amt_total | float8      |
    gr_byte_qty_total | float8      |
   
   thanks,
   
   mikeo
   
   

2. Re: coalesce view error

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-06-16T04:57:00Z

   mikeo <mikeo@spectrumtelecorp.com> writes:
   > hi, i have this view defined as:
   > create view mikeotest as select
   > coalesce(topflow_application.rpt_name,topflow_application.tfap_name) AS
   > ipd_desc,
   > [etc]
   > when i try to run this query against this view, i get the following:
   > select sum(gr_bill_amt_total), ipd_desc from mikeotest group by ipd_desc;
   > ERROR:  Unable to identify an equality operator for type 'unknown'
   
   Ugh.  You've dug up a pretty nasty bug.  If you write out the equivalent
   query as a single statement, it works fine --- it only fails when the
   CASE expression (expanded from COALESCE) is embedded in a view.
   
   The reason is that the representation of CaseExpr nodes stored in rules
   is a few bricks shy of a load --- it doesn't store the datatype field
   of the node!  So when the view's select rule is used to form a new
   query, the necessary type information is missing.  Somebody blew this
   pretty badly.
   
   The proper and straightforward fix is to expand the stored
   representation of CaseExpr to include the 'casetype' field.
   Unfortunately that breaks all existing stored rules that contain
   case expressions, so under the project's release rules I can't fix it
   that way before 7.1.  We don't change stored data representations in
   minor releases.
   
   As a short-term workaround I offer the attached hack.  I won't call
   it a patch, because I don't trust it very far, but it passes regress
   tests and it seems to fix your problem.  Give it a shot if this bug
   is getting in your way...
   
   			regards, tom lane
   
   
   *** src/backend/nodes/readfuncs.c.orig	Tue May 30 00:26:44 2000
   --- src/backend/nodes/readfuncs.c	Fri Jun 16 00:26:11 2000
   ***************
   *** 32,37 ****
   --- 32,39 ----
     #include "nodes/relation.h"
     #include "utils/lsyscache.h"
     
   + #include "parser/parse_expr.h"	/* short-term kluge only */
   + 
     /* ----------------
      *		node creator declarations
      * ----------------
   ***************
   *** 765,770 ****
   --- 767,781 ----
     	local_node->args = nodeRead(true);
     	token = lsptok(NULL, &length);		/* eat :default */
     	local_node->defresult = nodeRead(true);
   + 
   + 	/* KLUGE --- use parser's transformExpr to set casetype correctly.
   + 	 * Hold your nose and stand back at least ten paces...
   + 	 *
   + 	 * This is just a workaround until we can change the stored
   + 	 * representation of CaseExpr nodes in the next major release.
   + 	 * What bozo omitted storing casetype in the first place?
   + 	 */
   + 	transformExpr(NULL, (Node *) local_node, EXPR_COLUMN_FIRST);
     
     	return local_node;
     }
   
   

3. Re: coalesce view error

   mikeo <mikeo@spectrumtelecorp.com> — 2000-06-16T14:10:23Z

   hi tom,
     thanks for the patch.  one of my coworkers came up with a working
     solution:
   
   >Hi Mike, try adding ::text before the AS...
   >
   >create view mikeotest as
   >       select
   >coalesce(topflow_application.rpt_name,topflow_application.tfap_name)::text AS
   >ipd_desc,
   >       graphics_03.ipd_date,
   >       graphics_03.day,
   >       graphics_03.gr_bill_amt_total,
   >       graphics_03.gr_byte_qty_total,
   >       graphics_03.gr_ipd_sessions
   >FROM graphics_03, topflow_application
   >WHERE (graphics_03.ct_key = topflow_application.ib_ct_key
   >   or  graphics_03.ct_key = topflow_application.ob_ct_key);
   
   which results in the generated create statement:
   
   SELECT (CASE WHEN (topflow_application.rpt_name NOTNULL) 
                      THEN topflow_application.rpt_name 
                WHEN (topflow_application.tfap_name NOTNULL) 
                      THEN topflow_application.tfap_name 
            ELSE NULL::unknown END)::text AS ipd_desc, 
            graphics_03.ipd_date, graphics_03."day",
   graphics_03.gr_bill_amt_total, 
            graphics_03.gr_byte_qty_total, graphics_03.gr_ipd_sessions 
     FROM graphics_03, topflow_application 
    WHERE ((graphics_03.ct_key = topflow_application.ib_ct_key) 
       OR  (graphics_03.ct_key = topflow_application.ob_ct_key));
   
   this doesn't return that ERROR:  
        Unable to identify an equality operator for type 'unknown'
   
   my problem was that i was trying to cast the resulting case statement
   instead of
   the coalesce in my intial view definition.  casting the coalesce works fine. 
   
   mikeo 
   
   At 12:57 AM 6/16/00 -0400, Tom Lane wrote:
   >mikeo <mikeo@spectrumtelecorp.com> writes:
   >> hi, i have this view defined as:
   >> create view mikeotest as select
   >> coalesce(topflow_application.rpt_name,topflow_application.tfap_name) AS
   >> ipd_desc,
   >> [etc]
   >> when i try to run this query against this view, i get the following:
   >> select sum(gr_bill_amt_total), ipd_desc from mikeotest group by ipd_desc;
   >> ERROR:  Unable to identify an equality operator for type 'unknown'
   >
   >Ugh.  You've dug up a pretty nasty bug.  If you write out the equivalent
   >query as a single statement, it works fine --- it only fails when the
   >CASE expression (expanded from COALESCE) is embedded in a view.
   >
   >The reason is that the representation of CaseExpr nodes stored in rules
   >is a few bricks shy of a load --- it doesn't store the datatype field
   >of the node!  So when the view's select rule is used to form a new
   >query, the necessary type information is missing.  Somebody blew this
   >pretty badly.
   >
   >The proper and straightforward fix is to expand the stored
   >representation of CaseExpr to include the 'casetype' field.
   >Unfortunately that breaks all existing stored rules that contain
   >case expressions, so under the project's release rules I can't fix it
   >that way before 7.1.  We don't change stored data representations in
   >minor releases.
   >
   >As a short-term workaround I offer the attached hack.  I won't call
   >it a patch, because I don't trust it very far, but it passes regress
   >tests and it seems to fix your problem.  Give it a shot if this bug
   >is getting in your way...
   >
   >			regards, tom lane
   >
   >
   >*** src/backend/nodes/readfuncs.c.orig	Tue May 30 00:26:44 2000
   >--- src/backend/nodes/readfuncs.c	Fri Jun 16 00:26:11 2000
   >***************
   >*** 32,37 ****
   >--- 32,39 ----
   >  #include "nodes/relation.h"
   >  #include "utils/lsyscache.h"
   >  
   >+ #include "parser/parse_expr.h"	/* short-term kluge only */
   >+ 
   >  /* ----------------
   >   *		node creator declarations
   >   * ----------------
   >***************
   >*** 765,770 ****
   >--- 767,781 ----
   >  	local_node->args = nodeRead(true);
   >  	token = lsptok(NULL, &length);		/* eat :default */
   >  	local_node->defresult = nodeRead(true);
   >+ 
   >+ 	/* KLUGE --- use parser's transformExpr to set casetype correctly.
   >+ 	 * Hold your nose and stand back at least ten paces...
   >+ 	 *
   >+ 	 * This is just a workaround until we can change the stored
   >+ 	 * representation of CaseExpr nodes in the next major release.
   >+ 	 * What bozo omitted storing casetype in the first place?
   >+ 	 */
   >+ 	transformExpr(NULL, (Node *) local_node, EXPR_COLUMN_FIRST);
   >  
   >  	return local_node;
   >  }
   >
   
   

4. Re: coalesce view error

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-06-16T14:22:46Z

   mikeo <mikeo@spectrumtelecorp.com> writes:
   >   thanks for the patch.  one of my coworkers came up with a working
   >   solution:
   
   >> Hi Mike, try adding ::text before the AS...
   
   Hmm, offhand I do not see why that would work.  The two fields you're
   coalescing are both type text, no?  In that case the cast ought to be
   dropped out of the expression as redundant.
   
   If the fields are varchar or bpchar, then casting to text would work
   (at least in 7.0.*) because the parser would include an explicit
   RelabelType node in the stored expression...
   
   			regards, tom lane
   
   

5. Re: coalesce view error

   mikeo <mikeo@spectrumtelecorp.com> — 2000-06-16T15:27:56Z

   the underlying table fields are varchars...
   
   At 10:22 AM 6/16/00 -0400, Tom Lane wrote:
   >mikeo <mikeo@spectrumtelecorp.com> writes:
   >>   thanks for the patch.  one of my coworkers came up with a working
   >>   solution:
   >
   >>> Hi Mike, try adding ::text before the AS...
   >
   >Hmm, offhand I do not see why that would work.  The two fields you're
   >coalescing are both type text, no?  In that case the cast ought to be
   >dropped out of the expression as redundant.
   >
   >If the fields are varchar or bpchar, then casting to text would work
   >(at least in 7.0.*) because the parser would include an explicit
   >RelabelType node in the stored expression...
   >
   >			regards, tom lane
   >