Thread: coalesce view error
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
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;
}
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;
> }
>
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
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 >