Q: "unknown expression type 108" ?

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_viewINNER 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_idINT REFERENCES bar NOT NULL, PRIMARY KEY (foo_id), CHECK((foo_a_id IS NOT NULL AND foo_b_id IS NULL) OR
(foo_b_idIS 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_idIS 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