Обсуждение: coalesce view error

Поиск
Список
Период
Сортировка

coalesce view error

От
mikeo
Дата:
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

Re: coalesce view error

От
Tom Lane
Дата:
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;
  }

Re: coalesce view error

От
mikeo
Дата:
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;
>  }
>

Re: coalesce view error

От
Tom Lane
Дата:
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

Re: coalesce view error

От
mikeo
Дата:
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
>