Re: coalesce view error - Mailing list pgsql-general

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