Re: coalesce view error - Mailing list pgsql-general

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