Re: coalesce view error

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(at)spectrumtelecorp(dot)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;
> }
>