Hi

appended below is a simple database schema (which may not
be a candidate for the next Nobel Prize for SQL Database
Design, but represents enough of a production
database to demonstrate the following problem).

And that is:

under 7.3 this statement:
     SELECT foo_id, thingy_name, bar_name
       FROM foo_view, bar
      WHERE bar_id=foo_bar_id

produces the desired results.

This however:
     SELECT foo_id, thingy_name, bar_name
       FROM foo_view
 INNER JOIN bar ON bar_id=foo_bar_id

produces

 ERROR:  ExecEvalExpr: unknown expression type 108

The latter statement does however work in 7.1.3 with no
apparent problems.

Question: what does "unknown expression type 108" mean and
why should it suddenly occur in 7.3? A bit of Googling
reveals the same message occurs when using subselects
in constraints, but that doesn't seem related to this case.


Ian Barwick
barwick@gmx.net


-- sample DB for "unknown expression type 108" error

CREATE TABLE a_thingy (
  a_id INT,
  a_firstname VARCHAR(64),
  a_lastname VARCHAR(64),
  PRIMARY KEY (a_id)
);

CREATE TABLE b_thingy (
  b_id INT,
  b_name VARCHAR(64),
  PRIMARY KEY (b_id)
);

CREATE TABLE bar (
  bar_id INT,
  bar_name varchar(64),
  PRIMARY KEY (bar_id)
);

CREATE TABLE foo (
  foo_id INT,
  foo_a_id INT REFERENCES a_thingy NULL, 
  foo_b_id INT REFERENCES b_thingy NULL,
  foo_bar_id INT REFERENCES bar NOT NULL,
  PRIMARY KEY (foo_id),
  CHECK((foo_a_id IS NOT NULL AND foo_b_id IS NULL) OR
        (foo_b_id IS NOT NULL AND foo_a_id IS NULL))
);

CREATE VIEW foo_view AS
  SELECT *,
         CASE
           WHEN foo_a_id IS NOT NULL THEN 
              (SELECT a_lastname || ', ' || a_firstname
                 FROM a_thingy
                WHERE a_id=foo_a_id
              )
           WHEN foo_b_id IS NOT NULL THEN 
              (SELECT b_name
                 FROM b_thingy
                WHERE b_id=foo_b_id
              )
         END 
           AS thingy_name
    FROM foo;

INSERT INTO a_thingy VALUES
 (1, 'John', 'Doe');

INSERT INTO b_thingy VALUES
 (1, 'Megacorp');

INSERT INTO bar VALUES(1, 'squid');
INSERT INTO bar VALUES(2, 'octopus');

INSERT INTO foo VALUES (1,1,NULL,1);
INSERT INTO foo VALUES (2,NULL,1,2);

-- END


Fri, 6 Dec 2002 00:06:12 +0100
Ian Barwick <barwick@gmx.net>
Q: "unknown expression type 108" ?
Ian Barwick <barwick@gmx.net> writes:
>  ERROR:  ExecEvalExpr: unknown expression type 108

This is fixed as of yesterday --- see thread on (I think) pgsql-general
for the patch.

			regards, tom lane

Fri, 06 Dec 2002 14:50:20 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Q: "unknown expression type 108" ? 
On Friday 06 December 2002 20:50, Tom Lane wrote:
> Ian Barwick <barwick@gmx.net> writes:
> >  ERROR:  ExecEvalExpr: unknown expression type 108
>
> This is fixed as of yesterday --- see thread on (I think) pgsql-general
> for the patch.

Many thanks. Applied and appears to work :-)

For reference:
http://groups.google.com/groups?dq=&hl=en&lr=&ie=UTF-8&selm=2630.1039125066%40sss.pgh.pa.us


Ian Barwick
barwick@gmx.net



Fri, 6 Dec 2002 23:29:33 +0100
Ian Barwick <barwick@gmx.net>
Re: Q: "unknown expression type 108" ?