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