Skip site navigation (1) Skip section navigation (2)

Re: coalesce view error

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: mikeo <mikeo(at)spectrumtelecorp(dot)com>
Cc: pgsql-general(at)postgresql(dot)org, pgsql-patches(at)postgresql(dot)org
Subject: Re: coalesce view error
Date: 2000-06-16 04:57:00
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-generalpgsql-patches
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;

In response to


pgsql-patches by date

Next:From: Hiroshi InoueDate: 2000-06-16 05:35:21
Subject: RE: Big 7.1 open items
Previous:From: Hiroshi InoueDate: 2000-06-16 03:57:44
Subject: RE: Big 7.1 open items

pgsql-general by date

Next:From: Louis-David MitterrandDate: 2000-06-16 05:50:15
Subject: using max() aggregate
Previous:From: BillDate: 2000-06-16 02:25:31
Subject: large data values through ODBC?

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group